2

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.

3

Put backticks around partition like `partition`. It's a reserved word since version 5.6.2. See 9.3 Keywords and Reserved Words.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.