Recently we came uncomfortably close to running out of auto increment values in a MySQL table. The following is a description of how it happened and what we did about it.

Cause

We thought the table had been created with plenty of auto increment values. We used an unsigned int for the auto increment primary key, so it had a maximum value of about 4 billion. We happen to use an auto_increment_increment of 4 in this particular environment, so I figured the table could accomodate about 1 billion rows before we ran out of values. Six years of data in that table totalled 100 Million rows, and given the nature of the data the row count will not increase much faster than that even if our business grows dramatically, so I wasn't worried about running out of auto increment values in this table for at least 20 years.

Those assumptions are fine as long as we're only inserting data into the table and never deleting from …

Recently we came uncomfortably close to running out of auto increment values in a MySQL table. The following is a description of how it happened and what we did about it.

Cause

We thought the table had been created with plenty of auto increment values. We used an unsigned int for the auto increment primary key, so it had a maximum value of about 4 billion. We happen to use an auto_increment_increment of 4 in this particular environment, so I figured the table could accomodate about 1 billion rows before we ran out of values. Six years of data in that table totalled 100 Million rows, and given the nature of the data the row count will not increase much faster than that even if our business grows dramatically, so I wasn't worried about running out of auto increment values in this table for at least 20 years.

Those assumptions are fine as long as we're only inserting data into the table and never deleting from …

Assuming you have a backup and recovery strategy in place, how secure is your data? Does a hacker need to obtain access to your production system bypassing all the appropriate security protection you have in place, or just the unencrypted data on the backup server?

Encryption with zNcrypt

The following steps demonstrate how I setup a mysqldump encrypted backup with zNcrypt, a product from Gazzang. You can request a free trial evaluation of the software from http://gazzang.com/request-a-trial. I asked for a AWS EC2 instance, and was able to provide my bootstrap instructions for OS and MySQL installation. Following installation and configuration, the first step is to verify the zNcrypt process is running:

$ sudo ezncrypt-service status
  ezncrypt | Checking system dependencies
** ezncrypt system is UP and …

MySQL Enterprise 5.5 (trial version available here) includes MySQL PAM authentication plugin. In this post I will show how to configure it with the OpenLDAP and Active Directory.

MySQL PAM authentication uses Linux pam_ldap library to send the calls. To configure MySQL LDAP authentication we will need to configure pam_ldap on linux.

OpenLDAP Linux configuration

• Make sure that libpam-ldap/openldap is installed. If not, on RedHat/CentOS use commands:

# yum install openldap openldap-clients

• Configure /etc/ldap.conf. Sample configuration:

debug 10 # set debug level only during the initial configuration
base dc=corp,dc=company_name,dc=com
binddn cn=service_account,OU=Service Accounts,OU=US Security,DC=corp,DC=company_name,DC=com
bindpw <password>
timelimit 120 …

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read More »

You often see databases with huge dynamic text fields, such as VARCHAR(255), TEXT, or as I recently was allowed to see the blanket use of LONGTEXT (max 4GiB) in order to be invulnerable from all contingencies. Things getting even worse when an index is used over such columns, because hey, there is an index. It makes things fast :-) Okay, jokes aside. Often you can save a lot of space and time, MySQL spends traversing the index when using a proper column type and index size.

Read More »

Here are the commands I use and work quite well for me. Assumes you use CentOS and MySQL 5.5 64-bit.

What are the steps?

Install the Yum repository stop mysql remove mysql server 5.5 install percona server 5.5 + percona toolkit, xtrabackup and php/python/perl connectors start percona server

The actual commands

rpm -Uhv http://www.percona.com/downloads/percona-release/percona-release-0.0-1.

We found a simple XA transaction that crashes MySQL 5.5 replication. This simple transaction inserts a row into an InnoDB table and a TokuDB table. The bug was caused by a flaw in the logging code exposed by the transaction’s use of two XA storage engines (TokuDB and InnoDB). This bug was fixed in the TokuDB 6.0.1 release.

Here are some details.  Suppose that a database contains the following tables.

create table t1 (a int) engine=InnoDB
create table t2 (a int) engine=TokuDB

 The following transaction

begin
insert into t1 values (1)
insert into t2 values (2)
commit

causes the replication slave to crash.

The crash occurs when mysqld tries to dereference a NULL pointer.

#4  0x000000000088e203 in MYSQL_BIN_LOG::log_and_order (this=0x14b8640, thd=0x7f7758000af0, xid=161, all=true, need_prepare_ordered=false, need_commit_ordered=true) at …

Learn to design and implement modern applications, such as web or cloud applications, by following the MySQL for Developers Training Course which will teach you about essential SQL statements for data design, querying, and programming.

This 5 day instructor-led course is available via the following training methods:

• Live-Virtual Class: Follow this live class from your own office or home machine through one of the over 700 events currently on the schedule. To find the event that suits you go to http://oracle.com/education and click on MySQL.
• In-Class Teach: Travel to an Oracle classroom to follow this class. A sample of the schedule events is shown below:

Back in May I explained how to create MySQL striped views with session variables. A couple folks wanted to know how to implement them through PHP programs. The trick is sharing the same connection between a call to the function before a query against the striped view.

I’ve updated the MySQL example beyond the Hobbit analogy from the prior post. It now uses the following database components:

• An APPLICATION_USER table
• A striped AUTHORIZED_USER view
• A FUNCTION_QUERY view to optimize function calls
• A SET_LOGIN function
• A GET_LOGIN_ID function
• A GET_USER_ID function

The definition of the APPLICATION_USER table is:

CREATE TABLE application_user
( user_id …