The total size of all fields in the table is more than the limit, 65535, that’s why you are getting this error. You should use text type instead of varchar for long strings. Replace all varchar(8000) with text, and it should work. Or, even better, use appropriate data types instead of the “too large” ones. …
OP almost got it right. GROUP_CONCAT should be wrapping the columns in the subquery and not the complete subquery (I’m dismissing the separator because comma is the default): SELECT i.*, (SELECT GROUP_CONCAT(userid) FROM favourites f WHERE f.itemid = i.id) AS idlist FROM items i WHERE i.id = $someid This will yield the desired result and …
You can do it by using subqueries, one subquery for each tableCount : SELECT (SELECT COUNT(*) FROM table1 WHERE someCondition) as table1Count, (SELECT COUNT(*) FROM table2 WHERE someCondition) as table2Count, (SELECT COUNT(*) FROM table3 WHERE someCondition) as table3Count
The ANSI means is to use COALESCE: SELECT COALESCE(a2.date, a1.date) AS `date` … The MySQL native syntax is IFNULL: SELECT IFNULL(a2.date, a1.date) AS `date` … Unlike COALESCE, IFNULL is not portable to other databases. Another ANSI syntax, the CASE expression, is an option: SELECT CASE WHEN a2.date IS NULL THEN a1.date ELSE a2.date END AS …
You can do this with RIGHT(str,len) function. Returns the rightmost len characters from the string str, Like below: SELECT RIGHT(columnname,5) as yourvalue FROM tablename
After a lot of searching, I was able to fix the “PID file cannot be found” issue on my machine. I’m on OS X 10.9.3 and installed mysql via Homebrew. First, I found my PID file here: /usr/local/var/mysql/{username}.pid Next, I located my my.cnf file here: /usr/local/Cellar/mysql/5.6.19/my.cnf Finally, I added this line to the bottom of …
Here’s how to check which columns are the wrong collation: SELECT table_schema, table_name, column_name, character_set_name, collation_name FROM information_schema.columns WHERE collation_name=”latin1_general_ci” ORDER BY table_schema, table_name,ordinal_position; And here’s the query to fix it: ALTER TABLE tbl_name CONVERT TO CHARACTER SET latin1 COLLATE ‘latin1_swedish_ci’; Link
One somewhat hacky way to do it is to post-process the result of GROUP_CONCAT: substring_index(group_concat(s.title SEPARATOR ‘,’), ‘,’, 3) as skills Of course this assumes that your skill names don’t contain commas and that their amount is reasonably small. fiddle A feature request for GROUP_CONCAT to support an explicit LIMIT clause is unfortunately still not …
The accepted answer by RandomSeed could take a long time! Importing the table (just to drop it later) could be very wasteful depending on size. For a file created using mysqldump -u user -ppasswd –opt –routines DBname > DBdump.sql I currently get a file about 7GB, 6GB of which is data for a log table …
I had the same problem when I updated the mysql server from 5.5 to 5.7 in Debian 8 (jessie). In rare cases, it probably happens if you update directly bypassing the sequences of versions. (Many people do this, but such upgrades are not officially supported). In my case, it worked fine when I executed the …