As the maximum storage size for a LONGBLOB in MySQL is 4GB and the maximum max_allowed_packet size is 1GB I was wondering how it is possible to use the full LONGBLOB. So I started testing this. I wrote a Python script with MySQL Connector/Python and used MySQL Sandbox to bring up an instance of MySQl 5.6.25. One of the first settings I had to change was max_allowed_packet, which was expected. I set it to 1GB. The next setting was less expected, it was innodb_log_file_size. The server enforces that the transaction has to fit in 10% of the InnoDB log files. So I had to set it to 2 files of 5G to be able to insert one record of (almost) 1GB. So that worked for a row of a bit less that 1GB, this is because there is some overhead in the packet and the total has to fit in 1GB. For the next step (>1GB) I switched from Python to C so I could use mysql_stmt_send_long_data() which allows you to upload data in multiple chunks. I expected that to work, but it didn't. This is bec...

Today we released The CIS Oracle MySQL Community Server 5.6 Benchmark v1.0.0 This is a benchmark which can be used to secure your MySQL installation.

So assume you just uploaded the certificate you use to identify yourself to the MySQL server to Github or some other place it doesn't belong... and there is no undelete . First: Don't panic . Often a password is required besides a certificate to connect to the server. So someone with the certificate can't use it without the password. The certificate itself might be protected by a password, but that's really rare. Also access to MySQL and/or your account should be limited to certain IP's. The next step is to revoke the certificate. This is possible since MySQL 5.6.3 by using a Certificate Revocation List (CRL). A CRL is a list of the serials of the revoked certificates and signed by the CA. So this will only work if the certificates have unique serials. To get the serial of a certificate with OpenSSL: $ openssl x509 -in client-cert.pem -noout -text | grep 'Serial Number'  Serial Number: 3 (0x3) To get the serial of a certificate with GnuTLS: $ ce...

In the previous blog posts I've talked about transactions which block other transactions but don't do anything and about some possible solutions. In this post I will show you how to get even more information about what is locked by a transaction. As you might have noticed the information_schema.innodb_locks table doesn't show all locks. This is what the documentation says: "The INNODB_LOCKS table contains information about each lock that an InnoDB transaction has requested but not yet acquired, and each lock that a transaction holds that is blocking another transaction." So if would like to know all locks held by a transaction this doesn't help. There is lots of information in the output of " SHOW ENGINE INNODB STATUS\G " in the section about TRANSACTIONS. ------------ TRANSACTIONS ------------ Trx id counter 8991 Purge done for trx's n:o < 8991 undo n:o < 0 state: running but idle History list length 50 ...

In my previous post I talked about a transaction which blocked other transactions without doing anything. I talked about finding data from the blocking transaction using SYS and performance_schema. But what are the possible solutions? The first solution is to (automatically) kill the blocking transactions. Long running transactions can also stall the purging in InnoDB. See this blog post by Mark Leith about a possible solution. The second solution would be make the application end the transaction sooner and/or to commit more often. Depending on your application this might or might not work. I consider this the best solution. The third solution is to change the transaction isolation level of the blocking transaction to READ COMMITTED. mysql [test] > set transaction isolation level read committed; Query OK, 0 rows affected (0.00 sec) mysql [test] > start transaction; Query OK, 0 rows affected (0.00 sec) mysql [test] > insert into t2 select * from t1; Query OK, ...

When I queried information_schema.innodb_trx (introduced in 5.1 with the InnoDB Plugin) I noticed there were a few transactions in LOCK WAIT state. Example: mysql [information_schema] > select trx_id,trx_state  -> from information_schema.innodb_trx; +--------+-----------+ | trx_id | trx_state | +--------+-----------+ | 7974 | LOCK WAIT | | 7962 | RUNNING | +--------+-----------+ 2 rows in set (0.00 sec) Then I made a query to join a few information_schema and performance_schema tables to find out which query was blocking my transactions. It turned out that the blocking transaction had a trx_query=NONE. So my query was block by a transaction doing nothing. That's not really helpful. Let's try to recreate the situation and see exactly what happened. I'll use two sessions for the transactions and a third to monitor the server. The first session: mysql [test] > create table t1 (id int); Query OK, 0 rows affected (0.01 sec) mysql [test] > insert...

It is possible to disallow users from using old_passwords=1. This can be done by adding ' maximum-old_passwords=0 ' to your my.cnf This prevents users from generating passwords hashes in pre-4.1 format. In MySQL 5.7 old_passwords=1 is already disabled, so this is only useful on 5.6 and earlier. Be aware that this also restricts old_passwords=2 which is needed for sha256 hashes. mysql> select @@old_passwords; +-----------------+ | @@old_passwords | +-----------------+ | 0 | +-----------------+ 1 row in set (0.00 sec) mysql> set old_passwords=1; Query OK, 0 rows affected, 1 warning (0.00 sec) mysql> show warnings; +---------+------+----------------------------------------------+ | Level | Code | Message | +---------+------+----------------------------------------------+ | Warning | 1292 | Truncated incorrect old_passwords value: '1' | +---------+------+----------------------------------------------+ 1 r...

Last week I had to downgrade from MySQL 5.6 to 5.5. The reason for this was that the application used a very old Connector/J and that's incompatible with MySQL 5.6 because the removal of SET OPTION syntax. We're now planning to upgrade Connector/J to be able to upgrade to 5.6 again. There are two methods of downgrading: Dump/Restore with mysqldump. This is easy and reliable, but can take more time. In place (replace binaries, don't change data). This fast, but won't work if file formats have changed. As expected this is documented in the MySQL Reference Manual. I went for the in place method. I expected this to work without many issues as this database was not using the fancy new features like fulltext indexes for InnoDB. All tables used the A ntelope format. As both MySQL versions support A ntelope and B arracuda this shoud be fine. I don't know why Oracle didn't introduce a new C heeta format with the introduction of the fulltext indexes. The i...

Oracle has released the Critical Patch Update (CPU) advisory for January 2014 . The affected MySQL products are: Oracle MySQL Enterprise Monitor, versions 2.3, 3.0  Oracle MySQL Server, versions 5.1, 5.5, 5.6 So this means that you should consider updating MySQL. For MySQL Enterprise the updates should be available on My Oracle Support and for the Community version the new versions are on the regular download locations . I guess the official repositories are already updated. For MySQL 5.6 you should upgrade to 5.6.15 For MySQL 5.5 you should upgrade to 5.5.35 For MySQL 5.1 you should upgrade to 5.1.73 If you use the MySQL release from your distribution you should ask them to fix the CVE numbers fixed in these releases (if they didn't do that already). The highest CVSS score for MEM is 10 and for MySQL the highest score is 6.8. Update: for more information see this blog post by Dave Stokes.

The procedure for using the PAM authentication plugin as documented doesn't work flawlessly on Ubuntu. So here is how it works on Ubuntu (and probably also on other Debian based systems). Please note that the PAM authentication plugin is an enterprise feature. 1. Make sure the plugin is loaded This can be done by adding the following to the mysqld section of my.cnf (Don't forget to restart). You could also use INSTALL PLUGIN to load it without restart. plugin-load=authentication_pam.so 2.  Add a user which will use the plugin mysql> CREATE USER 'dveeden'@'localhost' IDENTIFIED WITH authentication_pam; Query OK, 0 rows affected (0.00 sec) 3. Add a pam config file for 'mysql': Create /etc/pam.d/mysql with the following contents: @include common-auth @include common-account @include common-session-noninteractive 4. Login with the user mysql -p --enable-cleartext-plugin 5. Verify if you're really connected as the correct user. mysql...

I was a bit wrong in my previous post . MySQL 5.6 does allow you to supply a fsp with CURRENT_TIMESTAMP (thanks Roy). mysql> SELECT CURRENT_TIMESTAMP,CURRENT_TIMESTAMP(6); +---------------------+----------------------------+ | CURRENT_TIMESTAMP | CURRENT_TIMESTAMP(6) | +---------------------+----------------------------+ | 2013-10-27 10:38:59 | 2013-10-27 10:38:59.182530 | +---------------------+----------------------------+ 1 row in set (0.00 sec) It however feels a bit weird to me as the CURRENT_TIMESTAMP is often used without () and doesn't look like a function. So when I tried to use a CURRENT_TIMESTAMP with a fsp of 6 it was not behaving how I expected it to be: mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(6)); ERROR 1067 (42000): Invalid default value for 'ts' mysql> CREATE TABLE t1 (ts TIMESTAMP DEFAULT CURRENT_TIMESTAMP(0)); Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO t1 VALUES(CURRENT_TIMESTAMP(6)); Query OK,...

MySQL 5.6 includes support for microsecode timestamp resolution , which is a great new feature. To get the current timestamp in MySQL 5.5 you could use NOW(), SYSDATE() or CURRENT_TIMESTAMP. mysql_5.5> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP; +---------------------+---------------------+---------------------+ | NOW() | SYSDATE() | CURRENT_TIMESTAMP | +---------------------+---------------------+---------------------+ | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | 2013-10-26 15:46:24 | +---------------------+---------------------+---------------------+ 1 row in set (0.01 sec) If we run the same statement in MySQL 5.6 the output is the same. This is great for compatibility, but what if we want those microsecond timestamps? mysql_5.6> SELECT NOW(),SYSDATE(),CURRENT_TIMESTAMP; +---------------------+---------------------+---------------------+ | NOW() | SYSDATE() | CURRENT_TIMESTAMP | +---------------------+----------------...

I've registered for the MySQL 5.6 beta exams, just like many more on Planet MySQL have done. One of the topics for the MySQL 5.6 Developer exam is: "Write a basic .NET application that uses MySQL". I have used Connector/Net a few times the last few years, but the last time I looked at some code involving Connector/Net was when I took the MySQL 5 Developer exam, which was long ago. I'm using Linux on both my laptop and on servers for most projects. So I don't use or see many .NET code. So I need a short refresh. To use Connector/Net on Linux you will need to install Mono. The base for .NET is CIL, which exists in at least 4 versions: 1.0, 2.0 and 4.0. To compile C# code with mono you need the command which correspondends with the CIL version you are using. CIL 1.0: mcs CIL 2.0: gmcs CIL 4.0: dmcs For Ubuntu you'll need these packages: libmono-system-data4.0-cil libmysql-cil-dev mono-runtime mono-dmcs pkg-config So now we should be able...

Some bugs can have a high impact if it causes data corruption, security issues or simply causes MySQL to crash. But what about small bugs and small usability issues? This entry from the MySQL 5.6.8 release notes is an interesting one: InnoDB: On startup, MySQL would not start if there was a mismatch between the value of the innodb_log_file_size configuration option and the actual size of the ib_logfile* files that make up the redo log . This behavior required manually removing the redo log files after changing the value of innodb_log_file_size . The fix causes MySQL to write all dirty pages to disk and re-create the redo log files during startup if it detects a size mismatch. (Bug #14596550) So previously you had to shutdown MySQL without fast shutdown enabled, then move the ib_logfile's away and start MySQL again. This is not too hard or complicated. But I have seen some seriou...