/
mysql.py
2218 lines (1815 loc) · 66.3 KB
/
mysql.py
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
806
807
808
809
810
811
812
813
814
815
816
817
818
819
820
821
822
823
824
825
826
827
828
829
830
831
832
833
834
835
836
837
838
839
840
841
842
843
844
845
846
847
848
849
850
851
852
853
854
855
856
857
858
859
860
861
862
863
864
865
866
867
868
869
870
871
872
873
874
875
876
877
878
879
880
881
882
883
884
885
886
887
888
889
890
891
892
893
894
895
896
897
898
899
900
901
902
903
904
905
906
907
908
909
910
911
912
913
914
915
916
917
918
919
920
921
922
923
924
925
926
927
928
929
930
931
932
933
934
935
936
937
938
939
940
941
942
943
944
945
946
947
948
949
950
951
952
953
954
955
956
957
958
959
960
961
962
963
964
965
966
967
968
969
970
971
972
973
974
975
976
977
978
979
980
981
982
983
984
985
986
987
988
989
990
991
992
993
994
995
996
997
998
999
1000
# -*- coding: utf-8 -*-
'''
Module to provide MySQL compatibility to salt.
:depends: - MySQLdb Python module
.. note::
On CentOS 5 (and possibly RHEL 5) both MySQL-python and python26-mysqldb
need to be installed.
:configuration: In order to connect to MySQL, certain configuration is required
in /etc/salt/minion on the relevant minions. Some sample configs might look
like::
mysql.host: 'localhost'
mysql.port: 3306
mysql.user: 'root'
mysql.pass: ''
mysql.db: 'mysql'
mysql.unix_socket: '/tmp/mysql.sock'
mysql.charset: 'utf8'
You can also use a defaults file::
mysql.default_file: '/etc/mysql/debian.cnf'
.. versionchanged:: 2014.1.0
\'charset\' connection argument added. This is a MySQL charset, not a python one.
.. versionchanged:: 0.16.2
Connection arguments from the minion config file can be overridden on the
CLI by using the arguments defined :mod:`here <salt.states.mysql_user>`.
Additionally, it is now possible to setup a user with no password.
'''
# Import python libs
from __future__ import absolute_import, print_function, unicode_literals
import hashlib
import time
import logging
import re
import sys
import shlex
import os
# Import salt libs
import salt.utils.data
import salt.utils.files
import salt.utils.stringutils
# Import third party libs
from salt.ext import six
# pylint: disable=import-error
from salt.ext.six.moves import range, zip # pylint: disable=no-name-in-module,redefined-builtin
try:
# Try to import MySQLdb
import MySQLdb
import MySQLdb.cursors
import MySQLdb.converters
from MySQLdb.constants import FIELD_TYPE, FLAG
HAS_MYSQLDB = True
except ImportError:
try:
# MySQLdb import failed, try to import PyMySQL
import pymysql
pymysql.install_as_MySQLdb()
import MySQLdb
import MySQLdb.cursors
import MySQLdb.converters
from MySQLdb.constants import FIELD_TYPE, FLAG
HAS_MYSQLDB = True
except ImportError:
# No MySQL Connector installed, return False
HAS_MYSQLDB = False
log = logging.getLogger(__name__)
# TODO: this is not used anywhere in the code?
__opts__ = {}
__grants__ = [
'ALL PRIVILEGES',
'ALTER',
'ALTER ROUTINE',
'CREATE',
'CREATE ROUTINE',
'CREATE TABLESPACE',
'CREATE TEMPORARY TABLES',
'CREATE USER',
'CREATE VIEW',
'DELETE',
'DROP',
'EVENT',
'EXECUTE',
'FILE',
'GRANT OPTION',
'INDEX',
'INSERT',
'LOCK TABLES',
'PROCESS',
'REFERENCES',
'RELOAD',
'REPLICATION CLIENT',
'REPLICATION SLAVE',
'SELECT',
'SHOW DATABASES',
'SHOW VIEW',
'SHUTDOWN',
'SUPER',
'TRIGGER',
'UPDATE',
'USAGE'
]
__ssl_options_parameterized__ = [
'CIPHER',
'ISSUER',
'SUBJECT'
]
__ssl_options__ = __ssl_options_parameterized__ + [
'SSL',
'X509'
]
r'''
DEVELOPER NOTE: ABOUT arguments management, escapes, formats, arguments and
security of SQL.
A general rule of SQL security is to use queries with _execute call in this
code using args parameter to let MySQLdb manage the arguments proper escaping.
Another way of escaping values arguments could be '{0!r}'.format(), using
__repr__ to ensure things get properly used as strings. But this could lead
to three problems:
* In ANSI mode, which is available on MySQL, but not by default, double
quotes " should not be used as a string delimiters, in ANSI mode this is an
identifier delimiter (like `).
* Some rare exploits with bad multibytes management, either on python or
MySQL could defeat this barrier, bindings internal escape functions
should manage theses cases.
* Unicode strings in Python 2 will include the 'u' before the repr'ed string,
like so:
Python 2.7.10 (default, May 26 2015, 04:16:29)
[GCC 5.1.0] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> u'something something {0!r}'.format(u'foo')
u"something something u'foo'"
So query with arguments should use a paramstyle defined in PEP249:
http://www.python.org/dev/peps/pep-0249/#paramstyle
We use pyformat, which means 'SELECT * FROM foo WHERE bar=%(myval)s'
used with {'myval': 'some user input'}
So far so good. But this cannot be used for identifier escapes. Identifiers
are database names, table names and column names. Theses names are not values
and do not follow the same escape rules (see quote_identifier function for
details on `_ and % escape policies on identifiers). Using value escaping on
identifier could fool the SQL engine (badly escaping quotes and not doubling
` characters. So for identifiers a call to quote_identifier should be done and
theses identifiers should then be added in strings with format, but without
__repr__ filter.
Note also that when using query with arguments in _execute all '%' characters
used in the query should get escaped to '%%' fo MySQLdb, but should not be
escaped if the query runs without arguments. This is managed by _execute() and
quote_identifier. This is not the same as escaping '%' to '\%' or '_' to '\%'
when using a LIKE query (example in db_exists), as this escape is there to
avoid having _ or % characters interpreted in LIKE queries. The string parted
of the first query could become (still used with args dictionary for myval):
'SELECT * FROM {0} WHERE bar=%(myval)s'.format(quote_identifier('user input'))
Check integration tests if you find a hole in theses strings and escapes rules
Finally some examples to sum up.
Given a name f_o%o`b'a"r, in python that would be """f_o%o`b'a"r""". I'll
avoid python syntax for clarity:
The MySQL way of writing this name is:
value : 'f_o%o`b\'a"r' (managed by MySQLdb)
identifier : `f_o%o``b'a"r`
db identifier in general GRANT: `f\_o\%o``b'a"r`
db identifier in table GRANT : `f_o%o``b'a"r`
in mySQLdb, query with args : `f_o%%o``b'a"r` (as identifier)
in mySQLdb, query without args: `f_o%o``b'a"r` (as identifier)
value in a LIKE query : 'f\_o\%o`b\'a"r' (quotes managed by MySQLdb)
And theses could be mixed, in a like query value with args: 'f\_o\%%o`b\'a"r'
'''
def __virtual__():
'''
Only load this module if the mysql libraries exist
'''
if HAS_MYSQLDB:
return True
return (False, 'The mysql execution module cannot be loaded: neither MySQLdb nor PyMySQL is available.')
def __mysql_hash_password(password):
_password = hashlib.sha1(password).digest()
_password = '*{0}'.format(hashlib.sha1(_password).hexdigest().upper())
return _password
def __check_table(name, table, **connection_args):
dbc = _connect(**connection_args)
if dbc is None:
return {}
cur = dbc.cursor(MySQLdb.cursors.DictCursor)
s_name = quote_identifier(name)
s_table = quote_identifier(table)
# identifiers cannot be used as values
qry = 'CHECK TABLE {0}.{1}'.format(s_name, s_table)
_execute(cur, qry)
results = cur.fetchall()
log.debug(results)
return results
def __repair_table(name, table, **connection_args):
dbc = _connect(**connection_args)
if dbc is None:
return {}
cur = dbc.cursor(MySQLdb.cursors.DictCursor)
s_name = quote_identifier(name)
s_table = quote_identifier(table)
# identifiers cannot be used as values
qry = 'REPAIR TABLE {0}.{1}'.format(s_name, s_table)
_execute(cur, qry)
results = cur.fetchall()
log.debug(results)
return results
def __optimize_table(name, table, **connection_args):
dbc = _connect(**connection_args)
if dbc is None:
return {}
cur = dbc.cursor(MySQLdb.cursors.DictCursor)
s_name = quote_identifier(name)
s_table = quote_identifier(table)
# identifiers cannot be used as values
qry = 'OPTIMIZE TABLE {0}.{1}'.format(s_name, s_table)
_execute(cur, qry)
results = cur.fetchall()
log.debug(results)
return results
def __password_column(**connection_args):
dbc = _connect(**connection_args)
if dbc is None:
return 'Password'
cur = dbc.cursor()
qry = ('SELECT column_name from information_schema.COLUMNS '
'WHERE table_schema=%(schema)s and table_name=%(table)s '
'and column_name=%(column)s')
args = {
'schema': 'mysql',
'table': 'user',
'column': 'Password'
}
_execute(cur, qry, args)
if int(cur.rowcount) > 0:
return 'Password'
else:
return 'authentication_string'
def _connect(**kwargs):
'''
wrap authentication credentials here
'''
connargs = dict()
def _connarg(name, key=None, get_opts=True):
'''
Add key to connargs, only if name exists in our kwargs or,
if get_opts is true, as mysql.<name> in __opts__ or __pillar__
If get_opts is true, evaluate in said order - kwargs, opts
then pillar. To avoid collision with other functions,
kwargs-based connection arguments are prefixed with 'connection_'
(i.e. 'connection_host', 'connection_user', etc.).
'''
if key is None:
key = name
if name in kwargs:
connargs[key] = kwargs[name]
elif get_opts:
prefix = 'connection_'
if name.startswith(prefix):
try:
name = name[len(prefix):]
except IndexError:
return
val = __salt__['config.option']('mysql.{0}'.format(name), None)
if val is not None:
connargs[key] = val
# If a default file is explicitly passed to kwargs, don't grab the
# opts/pillar settings, as it can override info in the defaults file
if 'connection_default_file' in kwargs:
get_opts = False
else:
get_opts = True
_connarg('connection_host', 'host', get_opts)
_connarg('connection_user', 'user', get_opts)
_connarg('connection_pass', 'passwd', get_opts)
_connarg('connection_port', 'port', get_opts)
_connarg('connection_db', 'db', get_opts)
_connarg('connection_conv', 'conv', get_opts)
_connarg('connection_unix_socket', 'unix_socket', get_opts)
_connarg('connection_default_file', 'read_default_file', get_opts)
_connarg('connection_default_group', 'read_default_group', get_opts)
# MySQLdb states that this is required for charset usage
# but in fact it's more than it's internally activated
# when charset is used, activating use_unicode here would
# retrieve utf8 strings as unicode() objects in salt
# and we do not want that.
#_connarg('connection_use_unicode', 'use_unicode')
connargs['use_unicode'] = False
_connarg('connection_charset', 'charset')
# Ensure MySQldb knows the format we use for queries with arguments
MySQLdb.paramstyle = 'pyformat'
if connargs.get('passwd', True) is None: # If present but set to None. (Extreme edge case.)
log.warning('MySQL password of None found. Attempting passwordless login.')
connargs.pop('passwd')
try:
dbc = MySQLdb.connect(**connargs)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return None
dbc.autocommit(True)
return dbc
def _grant_to_tokens(grant):
'''
This should correspond fairly closely to the YAML rendering of a
mysql_grants state which comes out as follows:
OrderedDict([
('whatever_identifier',
OrderedDict([
('mysql_grants.present',
[
OrderedDict([('database', 'testdb.*')]),
OrderedDict([('user', 'testuser')]),
OrderedDict([('grant', 'ALTER, SELECT, LOCK TABLES')]),
OrderedDict([('host', 'localhost')])
]
)
])
)
])
:param grant: An un-parsed MySQL GRANT statement str, like
"GRANT SELECT, ALTER, LOCK TABLES ON `mydb`.* TO 'testuser'@'localhost'"
or a dictionary with 'qry' and 'args' keys for 'user' and 'host'.
:return:
A Python dict with the following keys/values:
- user: MySQL User
- host: MySQL host
- grant: [grant1, grant2] (ala SELECT, USAGE, etc)
- database: MySQL DB
'''
log.debug('_grant_to_tokens entry \'%s\'', grant)
dict_mode = False
if isinstance(grant, dict):
dict_mode = True
# Everything coming in dictionary form was made for a MySQLdb execute
# call and contain a '%%' escaping of '%' characters for MySQLdb
# that we should remove here.
grant_sql = grant.get('qry', 'undefined').replace('%%', '%')
sql_args = grant.get('args', {})
host = sql_args.get('host', 'undefined')
user = sql_args.get('user', 'undefined')
else:
grant_sql = grant
user = ''
# the replace part is for presence of ` character in the db name
# the shell escape is \` but mysql escape is ``. Spaces should not be
# exploded as users or db names could contain spaces.
# Examples of splitting:
# "GRANT SELECT, LOCK TABLES, UPDATE, CREATE ON `test ``(:=saltdb)`.*
# TO 'foo'@'localhost' WITH GRANT OPTION"
# ['GRANT', 'SELECT', ',', 'LOCK', 'TABLES', ',', 'UPDATE', ',', 'CREATE',
# 'ON', '`test `', '`(:=saltdb)`', '.', '*', 'TO', "'foo'", '@',
# "'localhost'", 'WITH', 'GRANT', 'OPTION']
#
# 'GRANT SELECT, INSERT, UPDATE, CREATE ON `te s.t\'"sa;ltdb`.`tbl ``\'"xx`
# TO \'foo \' bar\'@\'localhost\''
# ['GRANT', 'SELECT', ',', 'INSERT', ',', 'UPDATE', ',', 'CREATE', 'ON',
# '`te s.t\'"sa;ltdb`', '.', '`tbl `', '`\'"xx`', 'TO', "'foo '", "bar'",
# '@', "'localhost'"]
#
# "GRANT USAGE ON *.* TO 'user \";--,?:&/\\'@'localhost'"
# ['GRANT', 'USAGE', 'ON', '*', '.', '*', 'TO', '\'user ";--,?:&/\\\'',
# '@', "'localhost'"]
lex = shlex.shlex(grant_sql)
lex.quotes = '\'`'
lex.whitespace_split = False
lex.commenters = ''
lex.wordchars += '\"'
exploded_grant = list(lex)
grant_tokens = []
multiword_statement = []
position_tracker = 1 # Skip the initial 'GRANT' word token
database = ''
phrase = 'grants'
for token in exploded_grant[position_tracker:]:
if token == ',' and phrase == 'grants':
position_tracker += 1
continue
if token == 'ON' and phrase == 'grants':
phrase = 'db'
position_tracker += 1
continue
elif token == 'TO' and phrase == 'tables':
phrase = 'user'
position_tracker += 1
continue
elif token == '@' and phrase == 'pre-host':
phrase = 'host'
position_tracker += 1
continue
if phrase == 'grants':
# Read-ahead
if exploded_grant[position_tracker + 1] == ',' \
or exploded_grant[position_tracker + 1] == 'ON':
# End of token detected
if multiword_statement:
multiword_statement.append(token)
grant_tokens.append(' '.join(multiword_statement))
multiword_statement = []
else:
grant_tokens.append(token)
else: # This is a multi-word, ala LOCK TABLES
multiword_statement.append(token)
elif phrase == 'db':
# the shlex splitter may have split on special database characters `
database += token
# Read-ahead
try:
if exploded_grant[position_tracker + 1] == '.':
phrase = 'tables'
except IndexError:
break
elif phrase == 'tables':
database += token
elif phrase == 'user':
if dict_mode:
break
else:
user += token
# Read-ahead
if exploded_grant[position_tracker + 1] == '@':
phrase = 'pre-host'
elif phrase == 'host':
host = token
break
position_tracker += 1
try:
if not dict_mode:
user = user.strip("'")
host = host.strip("'")
log.debug(
'grant to token \'%s\'::\'%s\'::\'%s\'::\'%s\'',
user,
host,
grant_tokens,
database
)
except UnboundLocalError:
host = ''
return dict(user=user,
host=host,
grant=grant_tokens,
database=database)
def quote_identifier(identifier, for_grants=False):
r'''
Return an identifier name (column, table, database, etc) escaped for MySQL
This means surrounded by "`" character and escaping this character inside.
It also means doubling the '%' character for MySQLdb internal usage.
:param identifier: the table, column or database identifier
:param for_grants: is False by default, when using database names on grant
queries you should set it to True to also escape "_" and "%" characters as
requested by MySQL. Note that theses characters should only be escaped when
requesting grants on the database level (`my\_\%db`.*) but not for table
level grants (`my_%db`.`foo`)
CLI Example:
.. code-block:: bash
salt '*' mysql.quote_identifier 'foo`bar'
'''
if for_grants:
return '`' + identifier.replace('`', '``').replace('_', r'\_') \
.replace('%', r'\%%') + '`'
else:
return '`' + identifier.replace('`', '``').replace('%', '%%') + '`'
def _execute(cur, qry, args=None):
'''
Internal wrapper around MySQLdb cursor.execute() function
MySQLDb does not apply the same filters when arguments are used with the
query. For example '%' characters on the query must be encoded as '%%' and
will be restored as '%' when arguments are applied. But when there're no
arguments the '%%' is not managed. We cannot apply Identifier quoting in a
predictable way if the query are not always applying the same filters. So
this wrapper ensure this escape is not made if no arguments are used.
'''
if args is None or args == {}:
qry = qry.replace('%%', '%')
log.debug('Doing query: %s', qry)
return cur.execute(qry)
else:
log.debug('Doing query: %s args: %s ', qry, repr(args))
return cur.execute(qry, args)
def query(database, query, **connection_args):
'''
Run an arbitrary SQL query and return the results or
the number of affected rows.
CLI Example:
.. code-block:: bash
salt '*' mysql.query mydb "UPDATE mytable set myfield=1 limit 1"
Return data:
.. code-block:: python
{'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
CLI Example:
.. code-block:: bash
salt '*' mysql.query mydb "SELECT id,name,cash from users limit 3"
Return data:
.. code-block:: python
{'columns': ('id', 'name', 'cash'),
'query time': {'human': '1.0ms', 'raw': '0.001'},
'results': ((1L, 'User 1', Decimal('110.000000')),
(2L, 'User 2', Decimal('215.636756')),
(3L, 'User 3', Decimal('0.040000'))),
'rows returned': 3L}
CLI Example:
.. code-block:: bash
salt '*' mysql.query mydb 'INSERT into users values (null,"user 4", 5)'
Return data:
.. code-block:: python
{'query time': {'human': '25.6ms', 'raw': '0.02563'}, 'rows affected': 1L}
CLI Example:
.. code-block:: bash
salt '*' mysql.query mydb 'DELETE from users where id = 4 limit 1'
Return data:
.. code-block:: python
{'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
Jinja Example: Run a query on ``mydb`` and use row 0, column 0's data.
.. code-block:: jinja
{{ salt['mysql.query']('mydb', 'SELECT info from mytable limit 1')['results'][0][0] }}
'''
# Doesn't do anything about sql warnings, e.g. empty values on an insert.
# I don't think it handles multiple queries at once, so adding "commit"
# might not work.
# The following 3 lines stops MySQLdb from converting the MySQL results
# into Python objects. It leaves them as strings.
orig_conv = MySQLdb.converters.conversions
conv_iter = iter(orig_conv)
conv = dict(zip(conv_iter, [str] * len(orig_conv)))
# some converters are lists, do not break theses
conv_mysqldb = {'MYSQLDB': True}
if conv_mysqldb.get(MySQLdb.__package__.upper()):
conv[FIELD_TYPE.BLOB] = [
(FLAG.BINARY, str),
]
conv[FIELD_TYPE.STRING] = [
(FLAG.BINARY, str),
]
conv[FIELD_TYPE.VAR_STRING] = [
(FLAG.BINARY, str),
]
conv[FIELD_TYPE.VARCHAR] = [
(FLAG.BINARY, str),
]
connection_args.update({'connection_db': database, 'connection_conv': conv})
dbc = _connect(**connection_args)
if dbc is None:
return {}
cur = dbc.cursor()
start = time.time()
log.debug('Using db: %s to run query %s', database, query)
try:
affected = _execute(cur, query)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return False
results = cur.fetchall()
elapsed = (time.time() - start)
if elapsed < 0.200:
elapsed_h = str(round(elapsed * 1000, 1)) + 'ms'
else:
elapsed_h = str(round(elapsed, 2)) + 's'
ret = {}
ret['query time'] = {'human': elapsed_h, 'raw': str(round(elapsed, 5))}
select_keywords = ["SELECT", "SHOW", "DESC"]
select_query = False
for keyword in select_keywords:
if query.upper().strip().startswith(keyword):
select_query = True
break
if select_query:
ret['rows returned'] = affected
columns = ()
for column in cur.description:
columns += (column[0],)
ret['columns'] = columns
ret['results'] = results
return ret
else:
ret['rows affected'] = affected
return ret
def file_query(database, file_name, **connection_args):
'''
Run an arbitrary SQL query from the specified file and return the
the number of affected rows.
.. versionadded:: 2017.7.0
database
database to run script inside
file_name
File name of the script. This can be on the minion, or a file that is reachable by the fileserver
CLI Example:
.. code-block:: bash
salt '*' mysql.file_query mydb file_name=/tmp/sqlfile.sql
salt '*' mysql.file_query mydb file_name=salt://sqlfile.sql
Return data:
.. code-block:: python
{'query time': {'human': '39.0ms', 'raw': '0.03899'}, 'rows affected': 1L}
'''
if any(file_name.startswith(proto) for proto in ('salt://', 'http://', 'https://', 'swift://', 's3://')):
file_name = __salt__['cp.cache_file'](file_name)
if os.path.exists(file_name):
with salt.utils.files.fopen(file_name, 'r') as ifile:
contents = salt.utils.stringutils.to_unicode(ifile.read())
else:
log.error('File "%s" does not exist', file_name)
return False
query_string = ""
ret = {'rows returned': 0, 'columns': [], 'results': [], 'rows affected': 0, 'query time': {'raw': 0}}
for line in contents.splitlines():
if re.match(r'--', line): # ignore sql comments
continue
if not re.search(r'[^-;]+;', line): # keep appending lines that don't end in ;
query_string = query_string + line
else:
query_string = query_string + line # append lines that end with ; and run query
query_result = query(database, query_string, **connection_args)
query_string = ""
if query_result is False:
# Fail out on error
return False
if 'query time' in query_result:
ret['query time']['raw'] += float(query_result['query time']['raw'])
if 'rows returned' in query_result:
ret['rows returned'] += query_result['rows returned']
if 'columns' in query_result:
ret['columns'].append(query_result['columns'])
if 'results' in query_result:
ret['results'].append(query_result['results'])
if 'rows affected' in query_result:
ret['rows affected'] += query_result['rows affected']
ret['query time']['human'] = six.text_type(round(float(ret['query time']['raw']), 2)) + 's'
ret['query time']['raw'] = round(float(ret['query time']['raw']), 5)
# Remove empty keys in ret
ret = {k: v for k, v in six.iteritems(ret) if v}
return ret
def status(**connection_args):
'''
Return the status of a MySQL server using the output from the ``SHOW
STATUS`` query.
CLI Example:
.. code-block:: bash
salt '*' mysql.status
'''
dbc = _connect(**connection_args)
if dbc is None:
return {}
cur = dbc.cursor()
qry = 'SHOW STATUS'
try:
_execute(cur, qry)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return {}
ret = {}
for _ in range(cur.rowcount):
row = cur.fetchone()
ret[row[0]] = row[1]
return ret
def version(**connection_args):
'''
Return the version of a MySQL server using the output from the ``SELECT
VERSION()`` query.
CLI Example:
.. code-block:: bash
salt '*' mysql.version
'''
dbc = _connect(**connection_args)
if dbc is None:
return ''
cur = dbc.cursor()
qry = 'SELECT VERSION()'
try:
_execute(cur, qry)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return ''
try:
return cur.fetchone()[0]
except IndexError:
return ''
def slave_lag(**connection_args):
'''
Return the number of seconds that a slave SQL server is lagging behind the
master, if the host is not a slave it will return -1. If the server is
configured to be a slave for replication but slave IO is not running then
-2 will be returned. If there was an error connecting to the database or
checking the slave status, -3 will be returned.
CLI Example:
.. code-block:: bash
salt '*' mysql.slave_lag
'''
dbc = _connect(**connection_args)
if dbc is None:
return -3
cur = dbc.cursor(MySQLdb.cursors.DictCursor)
qry = 'show slave status'
try:
_execute(cur, qry)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return -3
results = cur.fetchone()
if cur.rowcount == 0:
# Server is not a slave if master is not defined. Return empty tuple
# in this case. Could probably check to see if Slave_IO_Running and
# Slave_SQL_Running are both set to 'Yes' as well to be really really
# sure that it is a slave.
return -1
else:
if results['Slave_IO_Running'] == 'Yes':
return results['Seconds_Behind_Master']
else:
# Replication is broken if you get here.
return -2
def free_slave(**connection_args):
'''
Frees a slave from its master. This is a WIP, do not use.
CLI Example:
.. code-block:: bash
salt '*' mysql.free_slave
'''
slave_db = _connect(**connection_args)
if slave_db is None:
return ''
slave_cur = slave_db.cursor(MySQLdb.cursors.DictCursor)
slave_cur.execute('show slave status')
slave_status = slave_cur.fetchone()
master = {'host': slave_status['Master_Host']}
try:
# Try to connect to the master and flush logs before promoting to
# master. This may fail if the master is no longer available.
# I am also assuming that the admin password is the same on both
# servers here, and only overriding the host option in the connect
# function.
master_db = _connect(**master)
if master_db is None:
return ''
master_cur = master_db.cursor()
master_cur.execute('flush logs')
master_db.close()
except MySQLdb.OperationalError:
pass
slave_cur.execute('stop slave')
slave_cur.execute('reset master')
slave_cur.execute('change master to MASTER_HOST=''')
slave_cur.execute('show slave status')
results = slave_cur.fetchone()
if results is None:
return 'promoted'
else:
return 'failed'
# Database related actions
def db_list(**connection_args):
'''
Return a list of databases of a MySQL server using the output
from the ``SHOW DATABASES`` query.
CLI Example:
.. code-block:: bash
salt '*' mysql.db_list
'''
dbc = _connect(**connection_args)
if dbc is None:
return []
cur = dbc.cursor()
qry = 'SHOW DATABASES'
try:
_execute(cur, qry)
except MySQLdb.OperationalError as exc:
err = 'MySQL Error {0}: {1}'.format(*exc)
__context__['mysql.error'] = err
log.error(err)
return []
ret = []
results = cur.fetchall()
for dbs in results:
ret.append(dbs[0])
log.debug(ret)
return ret
def alter_db(name, character_set=None, collate=None, **connection_args):
'''
Modify database using ``ALTER DATABASE %(dbname)s CHARACTER SET %(charset)s
COLLATE %(collation)s;`` query.
CLI Example:
.. code-block:: bash
salt '*' mysql.alter_db testdb charset='latin1'
'''
dbc = _connect(**connection_args)
if dbc is None:
return []
cur = dbc.cursor()
existing = db_get(name, **connection_args)
qry = 'ALTER DATABASE `{0}` CHARACTER SET {1} COLLATE {2};'.format(
name.replace('%', r'\%').replace('_', r'\_'),
character_set or existing.get('character_set'),
collate or existing.get('collate'))
args = {}
_execute(cur, qry, args)
def db_get(name, **connection_args):
'''
Return a list of databases of a MySQL server using the output
from the ``SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM
INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME='dbname';`` query.
CLI Example:
.. code-block:: bash
salt '*' mysql.db_get test
'''
dbc = _connect(**connection_args)
if dbc is None:
return []
cur = dbc.cursor()
qry = ('SELECT DEFAULT_CHARACTER_SET_NAME, DEFAULT_COLLATION_NAME FROM '
'INFORMATION_SCHEMA.SCHEMATA WHERE SCHEMA_NAME=%(dbname)s;')
args = {"dbname": name}
_execute(cur, qry, args)
if cur.rowcount:
rows = cur.fetchall()
return {'character_set': rows[0][0],
'collate': rows[0][1]}
return {}
def db_tables(name, **connection_args):
'''
Shows the tables in the given MySQL database (if exists)