PlanetMysql.ru — информация о СУБД MySQL

I recently noticed both MariaDB and XtraDB (not MySQL yet) have a (newer) variable query_cache_strip_comments.

This variable is great for those who want to append comments to various queries, but still want the query cache to be able to serve such queries. Unfortunately, with MySQL, this is not currently possible.

In the past, I wrote a post on using MySQL Proxy which described a technique of monitoring queries through the proxy by appending IP addresses to the queries so one could track where they originated from. However, one pitfall to that was the MySQL query cache *does not* ignore the comment and treats them all as different queries (see the user comments for further discussion). (I did subsequently enhance that functionality implementing the SHOW PROXY PROCESSLIST command (often used in the Proxy Admin module), in large part because of this limitation.)

To enable it (in MariaDB 5.3+ and XtraDB), just add query_cache_strip_comments under the [mysqld] section in your my.cnf file and restart mysqld.

Alternatively, you can also set it dynamically:

mysql> set @@global.query_cache_strip_comments=1;
Query OK, 0 rows affected (0.04 sec)

mysql> show global variables like 'query_cache_strip_comments';
+----------------------------+-------+
| Variable_name              | Value |
+----------------------------+-------+
| query_cache_strip_comments | ON    |
+----------------------------+-------+

mysql> select version();
+---------------+
| version()     |
+---------------+
| 5.3.3-MariaDB |
+---------------+

Fwiw, from examining the source code, both implementations seemed to differ, but the end result is the same, and it’s a welcome addition, if you ask me.

The MySQL Forums seem to be have been around forever. There are groups for announcements, performance, InnoDB, Cluster, Replication, Certification, Events, questions in German, partitioning, and just under a hundred MySQL related subjects in total. Most folks in the MySQLverse seem to regularly lurk in one or more of the forums. There area great source of information and sometimes humor. But are you using the forums to help build your MySQL mental muscle? Ask yourself how would you solve the problem or how would you guide a junior DBA or developer to fix an issue posted in a forum.

Try reading the groups that are not in your regular reading list. Is there a question in there you can not answer even after a peek at the documentation?

After running MySQL for a few years, it is easy to skip over the problems of novices found in the Newbie section. But like the old story of the Emperor’s New Clothes, a new set of eyes can point out things we do not notice. Recently someone in the Newbie group wanted to set up a round robin for inserts over eight disks in an attempt to get 8x performance gain. Hmmm, if that did scale would it scale linearly(1) or close to it? Too bad I do not have the hardware or spare cycles to give it a try.

Or question on how to remove a ‘width=xxx’ pattern from a text field. Hmm, the old GIGO rules would dictate that some sort of ETL tool or script remove the unwanted chaff before it goes into the database. Do you use regular expressions in a PHP, Perl, or Java hack? Could a column oriented editor let you WYSIWYG the data? Maybe a trigger on insert that culls the unwanted width data? Hey, does your data have anything hidden in a text filed that should not be there and how best to get rid of it?

So read through a few forums and use them to exercise you mind. And if you see something in a question that you can answer, please do so.

1. Nothing scales linearly that you need to scale linearly. At least not on your budget and or schedule.

Accidentially I came across the statement “SHOW GRANTS requires the SELECT privilege for the mysql database.” in MySQL  documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).

It is not quite true. Any user can “SHOW GRANTS [FOR himself]” with no privileges at all. But more important: SELECT priviege is requried on database-level,  Privilege to the privileges tables is not enough.  See

SHOW GRANTS;
/*returns

Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO ‘me’@'%’
GRANT SELECT ON `mysql`.`user` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`tables_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`procs_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`db` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`columns_priv` TO ‘me’@'%’*/

SHOW GRANTS FOR root@localhost;
/*returns

Error Code: 1044
Access denied for user ‘me’@'%’ to database ‘mysql’ */

Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths)  there is no option to specify another database for the log tables.

I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like

UPDATE `identity`SET  `bank_account_no` =  ….. WHERE social_security_id = ….. ;

You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.

I don’t claim all applications would send such statements (but I believe that some will do).  You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don’t need to ‘ping-pong’ data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).

So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)

I posted this bug report: http://bugs.mysql.com/bug.php?id=64215

Accidentially I came across the statement “SHOW GRANTS requires the SELECT privilege for the mysql database.” in MySQL  documentation (http://dev.mysql.com/doc/refman/5.1/en/show-grants.html).

It is not quite true. Any user can “SHOW GRANTS [FOR himself]” with no privileges at all. But more important: SELECT priviege is requried on database-level,  Privilege to the privileges tables is not enough.  See

SHOW GRANTS;
/*returns

Grants for me@%
—————————————————–
GRANT USAGE ON *.* TO ‘me’@'%’
GRANT SELECT ON `mysql`.`user` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`tables_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`procs_priv` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`db` TO ‘me’@'%’
GRANT SELECT ON `mysql`.`columns_priv` TO ‘me’@'%’*/

SHOW GRANTS FOR root@localhost;
/*returns

Error Code: 1044
Access denied for user ‘me’@'%’ to database ‘mysql’ */

Anybody having SELECT privilege to the mysql database can read logs if you use log tables. And unlike when logging to files (where you can specify logfile paths)  there is no option to specify another database for the log tables.

I think it is a serious security flaw. The reason is that a log may contain data. That may be private data (email addresses, bank account numbers .. you name it). Consider a statement like

UPDATE `identity`SET  `bank_account_no` =  ….. WHERE social_security_id = ….. ;

You should have SELECT privilege to the `identity` table to see those data. But if you can read logs you need not.

I don’t claim all applications would send such statements (but I believe that some will do).  You can avoid it to some extent by using user variables, hashes, or by writing complex statements with JOINs and SUBQUERIES so that you don’t need to ‘ping-pong’ data from the server to the application and back (but it may conflict with performance in particular if SUBQUERIES are used).

So you should be careful with logging to tables. Not only is performance not good, but there are security concerns. Who should be allowed to see the logs? (but still log tables may be convenient for a test/development scenario though.)

I posted this bug report: http://bugs.mysql.com/bug.php?id=64215

Collaborate 2012 MySQL Sessions

Please mark you calendars for the MySQL sessions at Collaborate this April in Las Vegas.

http://collaborate12.ioug.org

Double Down at COLLABORATE 12- The IOUG Forum with Two Ways to Save- and a Chance to Win! The user-driven Oracle event of the year is fast-approaching, and IOUG wants you to make youreducational experience a sure bet. Between hundreds of cutting-edge education sessions, workshops and legendary Oracle speakers, you’ll return from Las Vegas with valuable knowledge to transform into immediate results for your business. No need to go all in to attend- IOUG is sweetening the pot with ways for you to save big bucks and even pocket some cash while you’re at it. Register today for your chance to win a $200 American Express Gift Card!The deck is stacked at COLLABORATE 12 – The IOUG Forum in your favor. Register for the conference today with the priority code EM03 and be immediately entered for a chance to add a $200 AmEx Gift Card to your winnings for the week. Treat yourself to some fusion cuisine while in Vegas, catch a show on the strip, or just pocket the prize for a rainy day! Buy-in to the table at COLLABORATE 12-The IOUG Forum has never been more reasonable. Hotel rates are only going up from here on out, so book your room today to take advantage of the final day of ultra-reasonable rates!  How about adding more savings to the mix on your conference registration?Sign up for a seat at the table with IOUG before February 29 and be dealt a winning hand: save up to $500 on conference registration by booking ahead of the early bird, and we’ll even throw in a bonus, extra day of education($599 value)! Don’t miss your chance to to take advantage of these massive savings . Book your COLLABORATE 12 journey today! Guarantee your seat at the table today, and save by registering before the Early Bird Deadline! Registration rates start as low as $1,295* for members of IOUG. Resolve to give yourself a fantastic week of Oracle Education, and happy 2012! * Rate assumes hotel and group discount Boot Camp Information Housing Deep Dive Information IOUG Registration Benefits Exhibit at COLLABORATE 12 Session Schedule Want to sponsor a Deep Dive? Contact Jconlon@ioug.org for more information.