I'm looking into migrating some DBs from Mysql 5.5 to 5.7.
I've dumped and restored a DB from 5.5 into a 5.7 and now a simple query does not work anymore.
Here below the table desc:
mysql> desc compute_currency_cluster_weights;
+------------+----------+------+-----+---------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------+----------+------+-----+---------------------+-------+
| FK_fcl_id | char(16) | NO | PRI | NULL | |
| partition | char(16) | NO | PRI | | |
| valore | double | NO | | 0 | |
| min_cpu | int(11) | NO | | 1 | |
| min_valore | double | NO | | 0 | |
| max_cpu | int(11) | NO | | 1 | |
| max_valore | double | NO | | 0 | |
| datestamp | datetime | NO | PRI | 0000-00-00 00:00:00 | |
+------------+----------+------+-----+---------------------+-------+
8 rows in set (0.01 sec)
On the old 5.5 server:
mysql> select * FROM compute_currency_cluster_weights WHERE partition='high';
+-----------+-----------+--------+---------+------------+---------+------------+---------------------+
| FK_fcl_id | partition | valore | min_cpu | min_valore | max_cpu | max_valore | datestamp |
+-----------+-----------+--------+---------+------------+---------+------------+---------------------+
| daint | high | 2 | 1 | 2 | 1 | 2 | 2017-01-17 11:11:39 |
+-----------+-----------+--------+---------+------------+---------+------------+---------------------+
1 row in set (0.00 sec)
On the new 5.7
mysql> select * FROM compute_currency_cluster_weights WHERE partition='high';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'partition='high'' at line 1
On both mysql server I can anyway successfully perform this query:
mysql> select * FROM compute_currency_cluster_weights WHERE FK_fcl_id='daint';
+-----------+-----------+----------------------+---------+----------------------+---------+----------------------+---------------------+
| FK_fcl_id | partition | valore | min_cpu | min_valore | max_cpu | max_valore | datestamp |
+-----------+-----------+----------------------+---------+----------------------+---------+----------------------+---------------------+
| daint | | 1 | 1 | 1 | 1 | 1 | 2014-04-01 00:00:00 |
| daint | high | 2 | 1 | 2 | 1 | 2 | 2017-01-17 11:11:39 |
| daint | wlcg | 0.013888888888888888 | 1 | 0.013888888888888888 | 1 | 0.013888888888888888 | 2017-07-18 00:00:00 |
+-----------+-----------+----------------------+---------+----------------------+---------+----------------------+---------------------+
3 rows in set (0.00 sec)
It looks like the issue is with the "partition" field.
I'm not a DBA (as you may see) and I've no clue where to look.
Thanks.
Best Answer
Put backticks around
partition
like`partition`
. It's a reserved word since version 5.6.2. See 9.3 Keywords and Reserved Words.