New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Mysql module alter_db quotation missing #48519

Closed
Lutseslav opened this Issue Jul 11, 2018 · 8 comments

Comments

Projects
None yet
3 participants
@Lutseslav
Contributor

Lutseslav commented Jul 11, 2018

Description

When mysql schema has special chars, e.g. dash "-", sql request fails with
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 '-partners CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1

Setup

E. g. salt state

awesome-db_mysqldb:
  mysql_database.present:
    - name: awesome-db
    - character_set: utf8
    - collate: utf8_general_ci

Steps to Reproduce Issue

2018-07-11 12:56:40,667 [salt.loaded.int.render.yaml:68  ][DEBUG   ][18693] Results of YAML rendering: 
OrderedDict([(u'awesome-db_mysqldb', OrderedDict([(u'mysql_database.present', [OrderedDict([(u'name', u'awesome-db')]), OrderedDict([(u'character_set', u'utf8')]), OrderedDict([(u'collate', u'utf8_general_ci')])])])), (u'awesome-db_user', OrderedDict([(u'mysql_user.present', [OrderedDict([(u'name', u'awesome-db')]), OrderedDict([(u'host', u'localhost')]), OrderedDict([(u'password', u'AWESOME_PASSWD')])])])), (u'awesome-db_awesome-db_fullaccess', OrderedDict([(u'mysql_grants.present', [OrderedDict([(u'database', u'awesome-db.*')]), OrderedDict([(u'user', u'awesome-db')]), OrderedDict([(u'host', u'localhost')]), OrderedDict([(u'grant', u'all privileges')])])]))])
2018-07-11 12:56:40,668 [salt.template    :26  ][PROFILE ][18693] Time (in seconds) to render '/var/cache/salt/minion/files/gnt/mysql.sls' using 'yaml' renderer: 0.00781297683716
2018-07-11 12:56:40,671 [salt.utils.lazy  :100 ][DEBUG   ][18693] LazyLoaded config.option
2018-07-11 12:56:40,681 [salt.utils.lazy  :100 ][DEBUG   ][18693] LazyLoaded mysql.db_exists
2018-07-11 12:56:40,682 [salt.utils.lazy  :100 ][DEBUG   ][18693] LazyLoaded mysql_database.present
2018-07-11 12:56:40,682 [salt.state       :1789][INFO    ][18693] Running state [awesome-db] at time 12:56:40.682877
2018-07-11 12:56:40,683 [salt.state       :1822][INFO    ][18693] Executing state mysql_database.present for [awesome-db]
2018-07-11 12:56:40,687 [salt.loaded.int.module.mysql:547 ][DEBUG   ][18693] Doing query: SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=%(dbname)s; args: {u'dbname': u'awesome-db'} 
2018-07-11 12:56:40,689 [salt.loaded.int.states.mysql_database:60  ][DEBUG   ][18693] character set differes from utf8 : latin1
2018-07-11 12:56:40,689 [salt.loaded.int.states.mysql_database:64  ][DEBUG   ][18693] collate set differs from utf8_general_ci : latin1_swedish_ci
2018-07-11 12:56:40,692 [salt.loaded.int.module.mysql:547 ][DEBUG   ][18693] Doing query: SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=%(dbname)s; args: {u'dbname': u'awesome-db'} 
2018-07-11 12:56:40,692 [salt.loaded.int.module.mysql:544 ][DEBUG   ][18693] Doing query: ALTER DATABASE awesome-db CHARACTER SET utf8 COLLATE utf8_general_ci;
2018-07-11 12:56:40,695 [salt.state       :310 ][ERROR   ][18693] An exception occurred in this state: Traceback (most recent call last):
  File "/usr/lib/python2.7/dist-packages/salt/state.py", line 1905, in call
    **cdata['kwargs'])
  File "/usr/lib/python2.7/dist-packages/salt/loader.py", line 1830, in wrapper
    return f(*args, **kwargs)
  File "/usr/lib/python2.7/dist-packages/salt/states/mysql_database.py", line 69, in present
    **connection_args)
  File "/usr/lib/python2.7/dist-packages/salt/modules/mysql.py", line 960, in alter_db
    _execute(cur, qry, args)
  File "/usr/lib/python2.7/dist-packages/salt/modules/mysql.py", line 545, in _execute
    return cur.execute(qry)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/cursors.py", line 226, in execute
    self.errorhandler(self, exc, value)
  File "/usr/lib/python2.7/dist-packages/MySQLdb/connections.py", line 36, in defaulterrorhandler
    raise errorvalue
ProgrammingError: (1064, "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 '-partners CHARACTER SET utf8 COLLATE utf8_general_ci' at line 1")

Versions Report

Salt Version:
           Salt: 2018.3.2
 
Dependency Versions:
           cffi: 1.5.2
       cherrypy: 3.5.0
       dateutil: 2.4.2
      docker-py: Not Installed
          gitdb: 0.6.4
      gitpython: 1.0.1
          ioflo: Not Installed
         Jinja2: 2.8
        libgit2: 0.24.0
        libnacl: Not Installed
       M2Crypto: Not Installed
           Mako: 1.0.3
   msgpack-pure: Not Installed
 msgpack-python: 0.4.6
   mysql-python: Not Installed
      pycparser: 2.14
       pycrypto: 2.6.1
   pycryptodome: Not Installed
         pygit2: 0.24.0
         Python: 2.7.12 (default, Nov 19 2016, 06:48:10)
   python-gnupg: 0.3.8
         PyYAML: 3.11
          PyZMQ: 15.2.0
           RAET: Not Installed
          smmap: 0.9.0
        timelib: Not Installed
        Tornado: 4.2.1
            ZMQ: 4.2.1
 
System Versions:
           dist: Ubuntu 16.04 xenial
         locale: UTF-8
        machine: x86_64
        release: 4.4.0-93-generic
         system: Linux
        version: Ubuntu 16.04 xenial

Lutseslav pushed a commit to Lutseslav/salt that referenced this issue Jul 11, 2018

Луценко Станислав
Add quotes to schema name in ALTER DATABASE.
Fixes saltstack#48519
Driven by sql request error when schema contains dashes and some other special symbols.

@garethgreenaway garethgreenaway added this to the Approved milestone Jul 11, 2018

@garethgreenaway

This comment has been minimized.

Member

garethgreenaway commented Jul 11, 2018

@Lutseslav Thanks for the report and the PR!

@Lutseslav

This comment has been minimized.

Contributor

Lutseslav commented Jul 11, 2018

@garethgreenaway Not a problem =) Can we merge it? Do you know when 2018.3.4 or later release is scheduled?

@garethgreenaway

This comment has been minimized.

Member

garethgreenaway commented Jul 11, 2018

@Lutseslav Yup! The PR looks good, just waiting for the automated tests to finish and it should be good to go. @rallytime Can you comment on when we're planning for 2018.3.4?

@Lutseslav

This comment has been minimized.

Contributor

Lutseslav commented Jul 11, 2018

@garethgreenaway It looks like some are finished and failed. That's why I am concerned.

@garethgreenaway

This comment has been minimized.

Member

garethgreenaway commented Jul 11, 2018

@Lutseslav The failures look unrelated to your PR.

@Lutseslav

This comment has been minimized.

Contributor

Lutseslav commented Jul 11, 2018

@garethgreenaway Yes, they are. So, it's normal for merge, got it =) Ok, deb pkg with this change in your repo would be great.
Thanks for your quick actions accepting PR

@Lutseslav

This comment has been minimized.

Contributor

Lutseslav commented Jul 11, 2018

@garethgreenaway I previously asked about 2018.3.4 - I meant 2018.3.3. Anyway it's about the next version

@rallytime

This comment has been minimized.

Contributor

rallytime commented Jul 11, 2018

Closed via #48520.

This fix will be in the 2018.3.3 release of Salt.

@rallytime rallytime closed this Jul 11, 2018

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment