CODEWITHSUNDEEP
mysqlmariadb
clarkk
clarkk

Reputation: 27709

migration - mysql 5.6 -> mariadb 10.1 -> sql errors

After migrating from Mysql 5.6 to MariaDB 10.1 I get these sql errors

SQL 1

SELECT dv.id,IF(de.id IS NULL,0,1) is_attached,dv.time,dv.label,dv.is_pdf_scan,dv.is_pic_low_resolution,dv.file_sha1,dv.file_ext,dv.file_ext_thumb,dv.file_size,dv.file_size_original,dv.file_name,u.name from_name,dv.from_email
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
LEFT JOIN `user` u ON u.id=dv.user_id
WHERE IF(de.id IS NULL,0,1)=0 && dv.time>=1420070400 && dv.time<=1451606399 && dv.client_id=1 && dv.block_id=4544 && dv.is_ready=1 && dv.is_deleted=0
GROUP BY dv.id
ORDER BY dv.time DESC,dv.id DESC
LIMIT 0,25

error 1

#1055 - 'dynaccount.de.id' isn't in GROUP BY

SQL 2

SELECT dv.id,dv.time,dv.label,dv.is_pdf_scan,dv.file_sha1,dv.file_ext,dv.file_ext_thumb
FROM `data_voucher` dv
LEFT JOIN `data_voucher_enclosure` de ON de.data_voucher_id=dv.id
WHERE de.enclosure_id=270729 && dv.client_id=1 && dv.block_id=4431 && dv.is_ready=1 && dv.is_deleted=0
GROUP BY dv.id
ORDER BY dv.time DESC,dv.id DESC

error 2

#1055 'dynaccount.dv.time' isn't in GROUP BY

UPDATE

In phpmyadmin I get a similar error when clicking the databases tab

SQL

SELECT s.SCHEMA_NAME,s.DEFAULT_COLLATION_NAME
FROM `information_schema`.SCHEMATA s
GROUP BY BINARY s.SCHEMA_NAME
ORDER BY BINARY `SCHEMA_NAME` ASC

error

#1055 - 'information_schema.s.SCHEMA_NAME' isn't in GROUP BY

Upvotes: 2

Views: 417

Answers (1)

hartmut holzgraefe
hartmut holzgraefe

Reputation: 11

Your SQL_MODE system variable seems to contain the ONLY_FULL_GROUP_BY mode.

ONLY_FULL_GROUP_BY enforces strict ANSI SQL behavior and disables the MySQL specific GROUP BY extensions.

See also, and note that this mode is the default in MySQL 5.7 so you would run into the same sort of problems when moving from MySQL 5.6 to MySQL 5.7, too

Upvotes: 1

Related Questions