Using MEMORY tables in MySQL is very useful; they are stored in memory and they are usually not so big in order to avoid a huge utilization of RAM. They are very fast.

MEMORY tables are often used to store session user data, chat messages and to store small frames of data from a bigger table in order to achieve great performance.

But, if you use them in a replication environment, you can have some problem due to their "volatility"; MEMORY table rows are lost when the server shuts down.

If you restart a slave host for some reason, you will have empty MEMORY tables on it, while the master host will have the correct ones.

How to populate automatically MEMORY tables at slave start up copying data from master host?

We could use something like LOAD TABLE table_name FROM MASTER, but it's deprecated, so we will use something else.

I have developed a stored procedure you have to create on the …

Using MEMORY tables in MySQL is very useful; they are stored in memory and they are usually not so big in order to avoid a huge utilization of RAM. They are very fast.

MEMORY tables are often used to store session user data, chat messages and to store small frames of data from a bigger table in order to achieve great performance.

But, if you use them in a replication environment, you can have some problem due to their “volatility”; MEMORY table rows are lost when the server shuts down.

If you restart a slave host for some reason, you will have empty MEMORY tables on it, while the master host will have the correct ones.

How to populate automatically MEMORY tables at slave start up copying data from master host?

We could use something like LOAD TABLE table_name FROM MASTER, but it’s deprecated, so we will use something else.

I have developed a stored procedure you have to create on …

In my replication environment I had a hardware failure on master host. A 4.5.GB table containing blob images wa corrupted, so I issued a REPAIR TABLE and the effect was to repair the table but also to reduce the number of rows: master table had just 41 rows less than the copies on the slave hosts on a total of a hundred thousand rows.
So, the problem now is how to recover only those rows from a slave and re-populate the master table avoiding to dump the entire table. And how to do that with a single command line instruction?

We can use a couple of mysqldump options.
The first option to use is --where="where_condition", it dumps only rows selected by the given WHERE condition.

So let's try a first instruction.


slave1:/> mysqldump -u root -proot_pwd --where="idimg between 93305 and 93346" mydb images | mysql -h master_host -u root -proot_pwd mydb


But this …

In my replication environment I had a hardware failure on master host. A 4.5.GB table containing blob images wa corrupted, so I issued a REPAIR TABLE and the effect was to repair the table but also to reduce the number of rows: master table had just 41 rows less than the copies on the slave hosts on a total of a hundred thousand rows.
So, the problem now is how to recover only those rows from a slave and re-populate the master table avoiding to dump the entire table. And how to do that with a single command line instruction?

We can use a couple of mysqldump options.
The first option to use is –where=”where_condition”, it dumps only rows selected by the given WHERE condition.

So let’s try a first instruction.


slave1:/> mysqldump -u root -proot_pwd --where="idimg between 93305 and 93346" mydb images | mysql -h master_host -u root -proot_pwd mydb


But …

Answering yesterday to some newsgroup question about MySQL I have answered about the problem of the full-text searches in natural language.

Create and populate of a simple table with a full-text index.

mysql> create table ft_test(
    -> id int not null auto_increment,
    -> string text,
    -> fulltext index(string),
    -> primary key(id));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into ft_test(string) values('forza inter'),('inter'),('viva inter'),('scudetto'),('champions league');
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ft_test;
+----+------------------+
| id | string           |
+----+------------------+
|  1 | forza inter      | 
|  2 | inter            | 
|  3 | viva inter       | 
|  4 | scudetto         | 
|  5 | champions league | 
+----+------------------+

Let's try first a search using the full-text index.

 …

Answering yesterday to some newsgroup question about MySQL I have answered about the problem of the full-text searches in natural language.

Create and populate of a simple table with a full-text index.

mysql> create table ft_test(
    -> id int not null auto_increment,
    -> string text,
    -> fulltext index(string),
    -> primary key(id));
Query OK, 0 rows affected (0.09 sec)

mysql> insert into ft_test(string) values('forza inter'),('inter'),('viva inter'),('scudetto'),('champions league');
Query OK, 5 rows affected (0.10 sec)
Records: 5  Duplicates: 0  Warnings: 0

mysql> select * from ft_test;
+----+------------------+
| id | string           |
+----+------------------+
|  1 | forza inter      |
|  2 | inter            |
|  3 | viva inter       |
|  4 | scudetto         |
|  5 | champions league |
+----+------------------+

Let’s try first a search using the full-text index.

 …

In my last post (see it for details) about stored procedure replication I was wrong.
I'm using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual :-)

But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4\G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

In my last post (see it for details) about stored procedure replication I was wrong.
I’m using 5.0.30 and, in fact, CALL statements are not written to the binlog. My knowledge about SP replication was before 5.0.12. This morning I read carefully the manual
But my problem is real. Using multiple rand() inside a SP cause wrong value replication on the slaves.

Here is the binlog content of my previous post example:

corra@localhost[(none)]> show binlog events in 'veleno-bin.000003' from 18403385 limit 4G
*************************** 1. row ***************************
   Log_name: veleno-bin.000003
        Pos: 18403385
 Event_type: RAND
  Server_id: 1
End_log_pos: 18403420
       Info: rand_seed1=1044525788,rand_seed2=146374381
*************************** 2. row ***************************
   Log_name: veleno-bin.000003
        Pos: …

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865\G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …

If we have a replication architecture and we are using stored procedures, we need to pay attention to the use of rand() inside the SPs.

Usually, single queries replicates correctly rand() values. MySQL writes to binlog the random number seed. You can consider the function execution timestamp and random number seed as implicit inputs that are identical on the master and slave.

You can see that below

mysql> create table prova(a double);
Query OK, 0 rows affected (0.01 sec)

mysql> insert into prova values(rand());
Query OK, 1 row affected (0.00 sec)

mysql> show binlog events in 'mysql-bin.000035' from 865G
*************************** 1. row ***************************
   Log_name: mysql-bin.000035
        Pos: 865
 Event_type: Query
  Server_id: 1
End_log_pos: 957
       Info: use `prova`; create table prova(a double)
*************************** 2. row ***************************
   Log_name: …