User Comments

Posted by Frigid Solutions on December 15 2008 11:52pm

[Delete] [Edit]

The client group of the config file does not seem to be read at all unless MYSQL_READ_DEFAULT_GROUP is used.

If you wish that the client group is read, but don't want to specify a default group, include the following:

mysql_options(mysql, MYSQL_READ_DEFAULT_GROUP, "client");

Posted by francisco santana on December 21 2010 10:15pm

[Delete] [Edit]

Be aware that connection wait_timeout may drop temporary tables generating a replication error at the slave ("table not found").
To prevent temporary table drops on long processes try to increase timeout for the client.

Posted by Guy Baconniere on October 13 2011 2:55pm

[Delete] [Edit]

This is a script I made to dump GRANTs of MySQL
excluding root@localhost and Debian's debian-sys-maint@localhost users.

It also feature the ability to Dump hidden GRANTs
which are in mysql.db and not declared in mysql.user
for example USAGE grant to [email protected] in mysql.user
but SELECT grant to test@% in mysql.db (without test@% in mysql.user)
such kind of manual rights SHOW GRANTS cannot handle
even in MySQL version 5.1.

### ------8<----CUT-HERE-----8<--------###
#!/bin/bash
# Copyright (c) 2011 Guy Baconniere

USAGE="$0 <MYSQLIP> [ <USERNAME> ] [ <PASSWORD> ]"

MASTERIP=${1:?$USAGE}
USERNAME=${2}
PASSWORD=${3}

if [ -z "$USERNAME" ]; then
AUTHENTIFICATION="--host=$MASTERIP"
else
AUTHENTIFICATION="--user=$USERNAME --password=$PASSWORD --host=$MASTERIP"
fi

# Dump all
( \
cat <<EOT | \
mysql \
$AUTHENTIFICATION \
--batch \
--silent \
--quick | \
mysql \
$AUTHENTIFICATION \
--batch \
--silent \
--quick | \
awk '/^GRANT/ { printf("%s;\n",$0); } END { printf("FLUSH PRIVILEGES;\n"); }'

SELECT
DISTINCT CONCAT('SHOW GRANTS FOR ''',user,'''@''',host,''';') AS query
FROM mysql.user
WHERE
NOT (user IN ('root','debian-sys-maint') AND host IN ('localhost'));

EOT
for table in db tables_priv columns_priv; do \
mysqldump \
$AUTHENTIFICATION \
--single-transaction \
--skip-extended-insert \
--skip-comments \
--complete-insert \
--compatible=ansi \
--quick \
--where="CONCAT(user,'@',host) NOT IN (SELECT CONCAT(user,'@',host) FROM mysql.user)" \
mysql \
db; \
done | \
awk '/^INSERT/ { gsub("INSERT INTO \"","REPLACE INTO \"mysql.",$0); print $0; } END { printf("FLUSH PRIVILEGES;\n"); }' \
)
### ------8<----CUT-HERE-----8<--------###

Posted by Timok Khan on May 23 2013 3:27pm

[Delete] [Edit]

If you pass only one value to the query with a command looking like

mycursor.execute(query, value)

, you will get a ProgrammingError.

The right syntax will be

mycursor.execute(query, (value, ))

Posted by Chao Yang on September 12 2013 4:19pm

[Delete] [Edit]

For BENCHMARK's expr statement, only assign operator (:=) and not the equal sign (=) which also acts as assign operator will work.

The example below shows both operators and only := works in incrementing session variable @b by 1 ten times:

mysql> set @b = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|    1 |
+------+

1 row in set (0.00 sec)

mysql> select benchmark(10, @b = @b + 1);

+----------------------------+
| benchmark(10, @b = @b + 1) |
+----------------------------+
|                          0 |
+----------------------------+

1 row in set (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|    1 |
+------+

1 row in set (0.00 sec)

mysql> select benchmark(10, @b := @b + 1);

+-----------------------------+
| benchmark(10, @b := @b + 1) |
+-----------------------------+
|                           0 |
+-----------------------------+

1 row in set (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|   11 |
+------+

1 row in set (0.00 sec)

mysql>

Posted by Chao Yang on September 12 2013 4:20pm

[Delete] [Edit]

For BENCHMARK(), only := assign operator works for session variable increment:

mysql> set @b = 1;
Query OK, 0 rows affected (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|    1 |
+------+

1 row in set (0.00 sec)

mysql> select benchmark(10, @b = @b + 1);

+----------------------------+
| benchmark(10, @b = @b + 1) |
+----------------------------+
|                          0 |
+----------------------------+

1 row in set (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|    1 |
+------+

1 row in set (0.00 sec)

mysql> select benchmark(10, @b := @b + 1);

+-----------------------------+
| benchmark(10, @b := @b + 1) |
+-----------------------------+
|                           0 |
+-----------------------------+

1 row in set (0.00 sec)

mysql> select @b;

+------+
| @b   |
+------+
|   11 |
+------+

1 row in set (0.00 sec)

mysql>

Add your own comment.

Top / Table of Contents