Several months ago I suggested having lightning talks at Percona Live MySQL Conference and Expo 2012, and I also offered to help.

Then I forgot about that for a while, until I saw the announcement that there was a call for Lightning Talks. Great! I submitted two proposals, and asked my colleagues to do the same, and also encouraged many good speakers I know to submit something.

The deadline for lightning talks submission passed, and I was told that my offer to help had been accepted, and I was in charge of lightning talks! OK. I would have preferred being told before the CfP, but an offer to help is an offer to help, and thus I went through the motions of evaluating the talks, sending notices to the winners, consoling the losers, and giving hope to the few brave ones who will replace the winners if they don't show up.

The talks that you will …

I have used tools like MySQL Migration Toolkit (*) and Bullzip’s Access To MySQL utility, both do a excellent job but without relationships. We can spend a lot of hours to identifying and creating relationships until now:

I have write a VBA script to identify MS-Access relationships and create MySQL code with SQL-CREATE sentences, it will be useful after migration process using any free tool:

'Put this function in new/existing MS-Access module.
'
' Version History:
'
' 2014-02-09 - Seamus Casey
' a modification to Ivan's handy Access to MySQL relationship/constraint generator
'
' changes include:
'  1) skip Access system tables (TableDefAttributeEnum.dbSystemObjec)
'  2) add support for cascading updates/deletes
'
 
Public Sub printRelations()
    Dim sql, fk As String
    Dim I, J As Integer
    Dim db As Database
    Dim Table As TableDef
    Dim TableName As String
 
    ' grab a reference to this once, otherwise when we retrieve a table …

I just finished a database modification, a new foreign key creation  shouldn’t be take more than 5 mins, but I spent 2 hours because MySQL still have some useless error messages.

There is a way to create a new foreign key:

-- Create two tables foo and bar
CREATE TABLE foo (
        id INTEGER NOT NULL PRIMARY KEY,
        bar_id INT NOT NULL    -- foreign key
);
CREATE TABLE bar (
        id INTEGER NOT NULL PRIMARY KEY
);
-- Try to create a foreign key on `foo`
ALTER TABLE foo
        ADD FOREIGN KEY(bar_id) REFERENCES bar(SOME_FIELD) ;

The last sentence returns a generic error message:

Error Code: 1005. Can't create table 'temp.#sql-4bd7_11' (errno: 150)

Everything would have been easier if I had noticed that wrong field name bar(SOME_FIELD), sometimes happens,  but if MySQL would have shown a different message like "field bar.SOME_FIELD don't exists" I would …

Tungsten Replicator 2.0.5 was released this week-end. The release notes have quite a long list of bug fixes. Thanks to all the ones who have submitted bug reports, and fixes! There are a couple of new features as well. The replicator includes now a slave prefetch service. Unlike parallel replication, this feature works fine with a single database, and provides performance improvements that in many cases solve the slave lagging problems. This was a bitch of a feature to get right. Many have tried it, many have experienced various degrees of success, and several failures. We started with the bold assertiveness of the brave after an exciting talk at Percona Live in October, and I was …

Do you get write laziness in the command line everything what you need to connect to a MySQL server every time?

It may take less than minute, but sometimes one minute is vital (especially if we’re near the end of the world):

ivancp@ubuntu$ mysql -u root -p -h mysqlhost database

When we are hurry, these commands often fail several times per minute.

The solution: we can create shortcuts with bash alias commands in file ~/.bashrc :

# File ~ /. Bashrc
 
# Command "my" to connect to a local server
alias my='mysql -u root -p'
 
# Command "my2" to connect to a remote server
alias my2='mysql -u root -h 192.168.1.56 -p'

Next time if you want to access the local server just type the command my [database name] , there only ask for database password. You can use any command aliases, I prefer  “my” and “my2” they are short and useful.

But if you have several servers comes …

The auto_increment for MySQL fields are very useful but what about if I need a custom auto-increment like a custom format like 001-000033,

001-000034
001-000035
001-000036
...

To make it possible we have an option based on past article MySQL Sequences:

1. Create the table to store the current sequence values:

   CREATE TABLE _sequence
   (
       seq_name VARCHAR(50) NOT NULL PRIMARY KEY,
       seq_group VARCHAR(10) NOT NULL,
       seq_val INT UNSIGNED NOT NULL
   );

2. Create a function to get and increment the current value:

   delimiter //
   DROP FUNCTION IF EXISTS getNextCustomSeq//
   CREATE FUNCTION getNextCustomSeq
   (
       sSeqName VARCHAR(50),
       sSeqGroup VARCHAR(10)
   ) RETURNS VARCHAR(20)
   BEGIN
       DECLARE nLast_val INT; 
    
       SET nLast_val =  (SELECT seq_val …

From command line we have the entire MySQL server on hands (if we have privileges too of course) but we don’t have a overall overview, at this point the show table status command is every useful, or not?.

This is what we get when run show table status in a standard 80×25 terminal screen:

We can maximize the terminal window and decrease font size, but not all the time we need that lots of info. Some time ago I develop a stored procedure to get a global overview including functions and stored procedures. The result is pretty comprehensible:

call tools.sp_status(database());
+----------------------------+--------+-------+---------+-----------------+
| Table Name                 | Engine | Rows  | Size    | Collation       | …

MySQL Cluster has long been used in telecommunications network services for Subscriber Data Management (HLR/HSS), Service Delivery Platforms and Value-Added Services, and has also been deployed in certain parts of general web infrastructure.

Following the announcements of MySQL Cluster 7.2 General Availability, including new benchmarks demonstrating MySQL Cluster delivering 1 Billion Queries per Minute, I thought it might be worthwhile to highlight examples of use cases for MySQL Cluster .

Web-Based Payment & Financial Services Platforms

MySQL Cluster can be deployed across a range of applications including payment gateways, trading systems and …

Those who have used PostgresSQL usually gets lazy when write the SQL code to auto-numeric fields, but we miss that feature when we are in a MySQL database.

As we know, MySQL have auto-increment fields, that are very useful too, but we miss that feature when we are in a PostgreSQL database.

I will try to explain how to simulate sequences like PostgreSQL in MySQL. But in what situations we would need sequences in MySQL? some ideas:

• When we need more than one auto-numeric field in the same table.
• When we need a global counter to use in more than one table, procedures, etc.
• I can’t think more situations, but I’m sure at some point we can found one.

OK let’s start:

We need to create a table to store the current sequence value (if this table is created inside mysql database  is not bad idea)

create table _sequence
( …

Replication just stopped in one Slave server with error: The table xtable is full which means no more records are permitted to insert in this table by MySQL and hence this has broken the replication.

I checked that xtable is having storage engine as Memory. In such tables, the max. no. of records you can insert is controlled by variable max_heap_table_size. When checking the size of this variable, I found that this is having default value:

mysql> show variables like 'max_heap_table_size';
+---------------------+----------+
| Variable_name       | Value    |
+---------------------+----------+
| max_heap_table_size | 16777216 |
+---------------------+----------+
1 row in set (0.00 sec)

So we need to increase the value of this variable and then issue Alter Table command to make it effective. Also do not …