In a previous post about different output for the same query there were 3 databases (MySQL, PostgreSQL and SQLite) and 3 different results. I attended the " Fixed in Drizzle: No more GOTCHA's " talk during Percona Live London. The talk was full of issues which I've encountered many times and which were all fixed. So I wondered whether or not this is already fixed in Drizzle. Here is the results for Drizzle: drizzle> select version(); +------------+ | version() | +------------+ | 2011.03.13 | +------------+ 1 row in set (0.000418 sec) drizzle> create database test; Query OK, 1 row affected (0.000622 sec) drizzle> use test; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Schema changed drizzle> create table t1 (id serial, t time); Query OK, 0 rows affected (0.001479 sec) drizzle> insert into t1(t) values ('00:05:10'); Query OK, 1 row affected (0.001717 se...

The SQL standard leaves a lot of room for different implementations. This is a little demonstration of one of such differences. SQLite  3.7.4 sqlite> create table t1 (id serial, t time); sqlite> insert into t1(t) values ('00:05:10'); sqlite> select t,t*1.5 from t1; 00:05:10|0.0 MySQL 5.6.4-m5 mysql> create table t1 (id serial, t time); Query OK, 0 rows affected (0.01 sec) mysql> insert into t1(t) values ('00:05:10'); Query OK, 1 row affected (0.00 sec) mysql> select t,t*1.5 from t1; +----------+-------+ | t        | t*1.5 | +----------+-------+ | 00:05:10 |   765 | +----------+-------+ 1 row in set (0.00 sec) PostgreSQL 9.0.3 test=# create table t1 (id serial, t time); NOTICE:  CREATE TABLE will create implicit sequence "t1_id_seq" for serial column "t1.id" CREATE TABLE test=# insert into t1(t) values ('00:05:10'); INSERT 0 1 test=# select t,t*1.5 from t1;     t ...

I went to EuroBSDcon in Maarssen, the Netherlands. It was a great conference and I might write another blog about it. I the conference bag there was a copy of the dutch Linux Magazine . The magazine is very nice and covers a broad range of topics. One article about LogicalDOC caught my attention. The LogicalDOC software uses a MySQL database. and the magazine listed some SQL code about how to create the database user: create user logicaldoc; set password FOR logicaldoc@´%´= PASSWORD('wachtwoord´); grant all privileges on logical- doc.* to logicaldoc@´%´ identified by  'wachtwoord´; create database logicaldoc; flush privileges; These statements won't work as some of the quotes are wrong. But let's ignore that. After executing the first line the grant is like this: GRANT USAGE ON *.* TO 'logicaldoc'@'%' So the user logicaldoc is allowed to connect from ANY host WITHOUT password. And yes that does work. After the second statement th...

For MyISAM one of the most important variables is the Key Buffer.  The Key Buffer is sometimes called the Key Cache. It's used as a buffer for the indices of MyISAM tables. There is some overhead in the buffer depending on the configured key block size. The official way to calculate the key buffer usage as documented in the MySQL Reference manual: 1 - ((Key_blocks_unused * key_cache_block_size) / key_buffer_size)  This will return the factor, so you have to multiply it with 100 to get the percentage. The Key_blocks_unused is used instead of the more obvious Key_blocks_used. This is due to the fact that Key_blocks_used is the maximum number of key blocks ever used. It will not return to 0 after a FLUSH TABLES. This calculation does not take the overhead in account. The key buffer efficiency can be calculated if the key buffer is empty or (has been) completely full. If the the key buffer is full: key_buffer_coefficient = key_cache_block_size/(key_buffer_size/Key_block...

Use DNS directly from your database mysql> SELECT lookup('localhost'); +---------------------+ | lookup('localhost') | +---------------------+ | 127.0.0.1 | +---------------------+ 1 row in set (0.00 sec) mysql> SELECT reverse_lookup('127.0.0.1'); +-----------------------------+ | reverse_lookup('127.0.0.1') | +-----------------------------+ | localhost | +-----------------------------+ 1 row in set (0.00 sec) This is how you install these functions. Build udf_example.so which is in your mysql source. ( make udf_example.so ) Copy the udf_example.so file from your build directory to your plugin_dir. Create the lookup and reverse_lookup functions mysql> CREATE FUNCTION lookup RETURNS STRING SONAME 'udf_example.so'; Query OK, 0 rows affected (0.00 sec) mysql> CREATE FUNCTION reverse_lookup RETURNS STRING SONAME 'udf_example.so'; Query OK, 0 rows affected (0.00 sec) I've created a feature ...

For MySQL it's a best practice to store IP addresses in a INT column rather than in a VARCHAR. This allows for more compact storage. This best practice is only for MySQL as PostgreSQL does have data types like inet and cidr . MySQL is equiped with the INET_ATON() and INET_NTOA() functions since version 3.23.15. In MySQL 5.6.3 the INET6_ATON() and INET6_NTOA() functions were addes to finaly add IPv6 address support. To make it easier to work with IPv4 and IPv6 addresses the IS_IPV4_COMPAT(), IS_IPV4_MAPPED(), IS_IPV4() and IS_IPV6() functions were added. The difference between the new and old functions is that the old functions use INT UNSIGNED to store IP addresses and the new function uses VARBINARY(16) for IPv6 addresses and VARBINARY(4) for IPv4 addresses. Here are some examples about how you could do nifty things with IP addresses in MySQL. Store hosts and networks in MySQL CREATE TABLE `hosts` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `hostname` varchar(255) DEFA...

This is a response on MySQL security: inconsistencies and Less known facts about MySQL user grants . As far as I know the privilege to grant PROXY privileges is also not very well understood. I blogged about that some time ago. In addion to the already highlighted issues with GRANT replication and grants can very well create an unwanted situation: master> SHOW GRANTS FOR 'user'@'host'\G *************************** 1. row *************************** Grants for user@host: GRANT USAGE ON *.* TO 'user'@'host' IDENTIFIED BY PASSWORD '*4994A78AFED55B0F529C11C436F85458C1F8D4C2' *************************** 2. row *************************** Grants for user@host: GRANT SELECT, INSERT, UPDATE, DELETE ON `somedb`.* TO 'user'@'host' 2 rows in set (0.00 sec) master> GRANT SELECT,INSERT,UPDATE,DELETE ON anotherdb.* TO 'user'@'host'; Query OK, 0 rows affected (0.00 sec) master> SHOW GRANTS FOR 'user'@'host...

This is a reply on Regularly flushing the MySQL Query Cache . The original acticle is about regulary flushing the MySQL Query Cache as it will fragment over time. There are some drawbacks for the cron method for flushing the query cache: It will only work on UNIX like platforms as MS Windows uses the task scheduler to schedule tasks. It needs credentials to login to the database. It's not included in your database backup There is another method, which is native to MySQL: the event scheduler . Step 1: Enable the event scheduler: mysql> SET GLOBAL event_scheduler=ON; Query OK, 0 rows affected (0.00 sec) And don't forget to set/change this in your my.cnf or my.ini Step 2: Create the event: mysql> CREATE EVENT flush_query_cache ON SCHEDULE EVERY 1 HOUR DO FLUSH QUERY CACHE; Query OK, 0 rows affected (0.00 sec) mysql> SHOW EVENTS\G *************************** 1. row *************************** Db: test Name: flush_query_cache Definer: msandbox@localhos...

What will be first? The new and updated MySQL certification or the new OSS-DB exam which is announced by LPI in Japan? The OSS-DB is only for PostgreSQL for now, but will cover more opensource databases in the future. There seem to be two levels: Silver: Management consulting engineers who can improve large-scale database Gold: Engineers who can design, development, implementation and operation of the database The google translate version can be found here . I found this info on Tatsuo Ishii's blog

This is a reply on A bit on SANs and system dependencies by Eric Bergen. Lets first start by making a difference between entry level, midrange and high-end SAN's. Entry level: This is basically a bunch of disks with a network connection. The Oracle/Sun/StorageTek 2540 is an example for this category. This storage is aimed at lowcost shared storage. Midrange: This kind of storage generally features replication and more disks than entry level. HP EVA is what comes to mind for this category. This storage is aimed at a good price/performance. High-End: This is mainframe style storage which is made usable for open systems. Hitachi Data Systems VSP is a good example of this category. This category is for extreme performance and reliability. Please make sure to not (mis-)judge High-End SAN by the experiences you had with entry-level storage. Why should we use SAN/NAS storage? SAN's can offer more reliable storage than local storage SAN's offer all kinds of extra services like re...

According to the Oracle website the following certifications are available for MySQL: Oracle Certified Associate, MySQL 5.0/5.1/5.5 Oracle Certified Professional, MySQL 5.0 Developer Oracle Certified Professional, MySQL 5.0 Database Administrator Oracle Certified Expert, MySQL 5.1 Cluster Database Administrator There were two new versions released since MySQL 5.0. MySQL 5.0 was released in 2005, that's more than 5 years ago. And "MySQL 5.1 Cluster Database"? According to wikipedia the NDB release in the 5.1 source tree is old and not maintained. And Cluster 7.1 has many new features like multithreaded data nodes and disk based data. So Oracle, please update the certifications.

 Just like I did in 2007 and 2009 , this is my updated whishlist for MySQL. My 2007 List: Per user and/or per database quota I guess that this will be implemented together with catalog support.   External Authentication  Got it in 5.5! Thanks a lot! And the new MySQL Cluster even has support for sharing user credentials .   Database Locator There is still no TNSnames like support. Saving extra metadata about the database. Using the comment field of tables for things like svn release, customer number and more still just feels wrong. And a database still can't have a comment... Using I_S is possible, but it's not supported to create FK's for that, so consistency is not guaranteed. better protection against run-away queries With mk-kill this is now easy. restore manager I still have to use thinks like awk for this... My 2009 List: SNMP for statistics no changes   SNMP for alerting MySQL Enterprise Manager is quite good at this. Auditi...

In a default MySQL 5.5.8 installation there is one PROXY privilege: GRANT PROXY ON ''@'' TO 'root'@'localhost' WITH GRANT OPTION What this does is: If USER() and CURRENT_USER() don't match root is still allowed to grant the proxy privilege. So if you connect using someuser@localhost using LDAP and LDAP tells you're root then you're still allowed to grant proxy privileges. This will only work if your user has the privilege to proxy to root. The documentation for PROXY is here .

It's the second point release of MySQL Enterprise Monitor 2.3. And there some nice new features and bugfixes. It's now possible to enable or disable event blackout for specific servers easily. It should now graph InnoDB with multiple buffer pools correctly. It won't work for 5.5.8 however, see Bug #60777 and Bug #60831 for more info on that. Read the complete changelogs for all the details. The Advisors/Rules were also updated. But there is still room for improvement: Bug #60828 Add rule to detect duplicate foreign keys Bug #60697 False Positives for Root Account Can Login Remotely Bug #60695 False positives for tables w/o keys for 5.5 with perf schema Bug #60677 "User Has Rights To Database That Does Not Exist" gives false positives Bug #60676 Add rule to monitor if the timezone info is loaded Bug #60587 Advice for Root Account Without Password is not correct Bug #60586 key buffer size check gives fals...

The MySQL Server from Oracle comes in a two different flavours: Community Edition and Enterprise Edition. The first one is under the GPLv2 license and the later is under the GPLv2 or Commercial license. The Enterprise Edition was always available from https://enterprise.mysql.com (which now has an expired SSL certificate) under the GPLv2 license. This download page was restricted to paying customers. Since the Enterprise downloads were moved to https://edelivery.oracle.com the downloads are available for everyone (as long as it's not restricted by export regulations and accept the trial license agreement). The license is now 'Commercial'. The download be named V24071-01.zip or something like that, which is annoying. The latest version for the Enterprise release on edelivery is 5.5.8 while the latest Community version is 5.5.11. Previously there were two enterprise releases: Advanced (With partitioning) and Professional (Without partitioning). If you use https://support.o...

For MySQL Enterprise Monitor an agent is required. I've downloaded V23981-01.zip from https://edelivery.oracle.com which contains mysqlmonitoragent-2.3.1.2044-linux-glibc2.3-x86-64bit-installer.rpm When I was testing the email alerting for my backup script I got an error: /etc/mail/submit.cf: line 544: fileclass: cannot open '/etc/mail/trusted-users': Group writable directory Luckily RPM was able to tell me that mysqlmonitoragent had set / to the wrong permissions.     # rpm -qf / filesystem-2.4.0-3.el5 mysqlmonitoragent-2.3.1.2044-0 # rpm -qvl mysqlmonitoragent-2.3.1.2044-0 | head -1 drwxrwxr-x 2 root root 0 Nov 25 01:51 / # rpm -qvl filesystem-2.4.0-3.el5 | head -1 drwxr-xr-x 2 root root 0 Oct 1 2009 / # echo test | mail -s test user@example.com # /etc/mail/submit.cf: line 544: fileclass: cannot open '/etc/mail/trusted-users': Group writable directory I've filed Bug #60752 for this. So watch out for this bug...

How old is your timezone info? As the timezone database gets updated frequently, it's important to update the internal timezone database in MySQL. This is needed for the CONVERT_TZ() function for work properly. It's not easy to determine if the data in mysql is older than the data from the tzdata database. It get's a bit better if you use this hack before importing the zone database: ALTER TABLE mysql.time_zone_transition ADD COLUMN `Last_Update` TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP; I've created Bug #60675 to get the system database updated. Which database serves still need timezone info? Use MySQL Enterprise Monitor with custom rules . 1. Add data query Open /opt/mysql/enterprise/agent/share/mysql-monitor-agent/items/custom.xml in an editor and add this: <class> <namespace>mysql</namespace> <classname>tzdata_available</classname> <query><!--[CDATA[SELECT CONVERT_TZ(NOW(),'G...

For those who didn't know it already: MySQL.com was "hacked" last weekend. There also was a report about SQL injection on 2 Sun.com domains. I haven't seen an announcement from Oracle yet. More info on: http://seclists.org/fulldisclosure/2011/Mar/309 http://tinkode27.baywords.com/sun-com-sun-mycrosystems-vulnerable-sql-injection/

So you've got a database dump of a few gigabytes and you wat to restore all databases except one. Using vi won't work as the file is too large to be easily edited. But luckily an awk oneliner does do the job. gawk '/^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ {if (db != "mysql") { print }}' original_dump.sql > original_dump_wo_mysql.sql The same awk code , but now formatted for readability: /^CREATE DATABASE.*/ { db=substr($7,2); db=substr(db,1,length(db)-1) } /^/ { if (db != "mysql") { print } } Of course you could pipe it directly into mysql saving some space on the filesystem. And you could also do the same with perl/python.

Since MySQL 4.0 it's possible to use SSL to secure the connections to MySQL. Unfortunately this is not used very often. It can be used for cient-server connections and for replication . It will encrypt your traffic and prevent man-in-the-middle attacks if you're using ssl-verify-server-cert when connecting using mysql. Securing database communication can be required to be compliant to some regulations. There are other means of preventing anyone from snooping your database traffic like VPN, SSH Tunneling or stunnel. As usual the documentation is quite good. The only issue with the docs is that the verification step is missing. ( Bug #59628 ). I'ts quite hard to debug as the OpenSSL messages are not reported in MySQL's errors. Bug luckily that's changing as Bug #21287 does have a patch under SCA/OCA. And you should check your my.cnf for typo's when it's not working as mysqld won't tell you when it can't find the ssl-ca file. ( Bug #59630 ). I'v...

Yesterday I blogged about some issues with MySQL Workbench. The problem was that CentOS 5.5 is not supported. So I installed RHEL6, one of the best known Enterprise Linux distributions. It's a 30 day evaluation, but that should be sufficient. After some minor issues with VirtualBox I had a working installation. So I tried to install MySQL Workbench again. I used 'sudo yum localinstall mysql-workbench-gpl-5.2.31a-2el6.x86_64.rpm' which failed because of 2 unmet dependencies: pexpect and libzip.so.1 There is already a bug report in RHEL for the missing pexpect: # 652659 I used the RHEL6 installation DVD to install pexpect. There is a closed/wont-fix bug ( #54403 ) for the libzip.so.1 dependency. It turns out that libzip is in the "RHEL Server Optional" channel. RHN should be used to enable more channels, but apparently the optional channel is not part of the evaluation license, so I used the web interface to download and install the libzip RPM. The wo...

Every once in a while I use MySQL Workbench. First is was only for Windows, then it was unstable and now it is very well usable. Or at least that is the situation when you run on a recent Ubuntu machine. I'm now using CenOS 5.5 in a VirualBox VM to do some testing. Unfortunately the download site for MySQL Workbench has RPMs for RedHat 6 and Fedora Core 14 and some other platforms. No CentOS, No RHEL5, only the latest Fedora en RHEL. So I downloaded the RHEL6 RPMs and tried to install them with yum localinstall, but that failed. So they are not backwards compatible with 5.5. Turns out that CentOS/RHEL 5 doesn't have the required libraries to support MySQL Workbench. And unfortunately CentOS 5.5 is still the latest release. So an upgrade to CenOS 6 is not yet possible.