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 …

Pluggable M3 (Monitis Monitor Manager) Framework

Who needs an introduction about M3? – Perhaps no one!
After gaining some reputation with M3, providing extra-easy integration of any monitor into Monitis it was time to take it to the next level.

Generally speaking, the work flow of M3 was described in detail in this article.

After some thought and design, we’ve decided it’d be best if M3 was pluggable. Pluggable in terms of being able to easily add execution and parsing plugins.
The interface and behavior of M3 …