db_sync doesn't work with sql_mode = 'TRADITIONAL'

[Alexandru]

Hi

The keystone-manage db_sync command fails with the following error :

2016-07-14 16:13:17.670 19170 ERROR keystone DBError: (_mysql_exceptions.ProgrammingError) (1064, 'You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near \'"keystone"\' at line 1') [SQL: 'SHOW FULL TABLES FROM "keystone"']

OS: Debian 8.5
Keystone ver : 2:9.0.0-2~bpo8+1
Mysql: Server version: 5.5.44-MariaDB-log MariaDB Server

The problem seems to be related with :
cfg.StrOpt('mysql_sql_mode',
               default='TRADITIONAL',

from /usr/lib/python2.7/dist-packages/oslo_db/options.py
It seems that on MariaDB if you set:
 set session sql_mode = 'TRADITIONAL';
the query :
show full tables from "keystone"
fails

I've solve the problem by adding ANSI to default sql mode:
cfg.StrOpt('mysql_sql_mode',
               default='TRADITIONAL,ANSI',

[Lance Bragstad (lbragstad)]

Hi Alexandru,

I'm unable able to recreate this using Ubuntu 15.10, keystone's master branch (64fbbeda58050c8248540c0f33a6753a43a42845) and mysql 5.6.25-0ubuntu0.15.04.1. I was able to migrate successfully using mysql_sql_mode=TRADITIONAL.

[Lance Bragstad (lbragstad)]

I also checked out stable/mitaka to try and recreate this (keystone - 9.1.1.dev3) and was able successfully sync my keystone database with mysql_sql_mode=TRADITIONAL.

I wonder if this is only an issue with older versions of sql?

[Alexandru (aldavx)]

Hi Lance

As I said in the description, the behaviour can be reproduced with MariaDB server (not with the mysql-server)

Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 43
Server version: 10.0.25-MariaDB-0+deb8u1 (Debian)

Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> set session sql_mode = 'TRADITIONAL';
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> SHOW FULL TABLES FROM "keystone";
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near '"keystone"' at line 1
MariaDB [(none)]>

[Alexandru (aldavx)]

I found my self a way out of this. It turns out that there is a mysql_sql_mode param in the keystone.conf.

[Alexandru (aldavx)]

setting mysql_sql_mode in kesytone.conf doesn't seems to have any effect

[Launchpad Janitor (janitor)]

[Expired for OpenStack Identity (keystone) because there has been no activity for 60 days.]

[OpenStack Infra (hudson-openstack)]

This issue was fixed in the openstack/kolla-ansible 6.0.0.0b3 development milestone.

[Colleen Murphy (krinkle)]

Alexandru, you marked this as "Fix Committed" for keystone, can you point to which commit fixed the issue?