Linux Admin Zone

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 forget to add variable with new value in your my.cnf.

Here is the situation: We are doing replication of only 1 table from a database (live) to other host (backup). so we expect that updates which are on others DBs/tables except on this table should not replicate. Wrong! they got replicated.

We shifted one DB say DB1 from live env to Backup DB host and then removed that DB1 from Live. Even replication was only for a single table of a particular DB say DB5, DB1 got removed from Backup host as well, living us in vaccum

MySQL stopped default inclusion of InnoDB in latest 5.1.x, so if you need InnoDB, you have to compile it from source. I have done following steps in CentOS 5.4 server to compile MySQL and optimize it later for a heavy site:

1. Remove earlier installation of MySQL, if any and download source rpm from MySQL site. I’ve also removed earlier installation of PHP here to upgrade it:

$ rpm -e php-mysql mysql-server php php-devel php-pear

Download the latest source rpm from MySQL download site, you will get source rpm like below and install it. Installation of source rpm will put it as tar file in /usr/src/redhat/SOURCES, make sure to create /usr/src/redhat/SOURCES directory before installation:

Recently I was in need to convert all databases of a MySQL server from MyISAM engine to InnoDB. They are facing severe issues due to table level locking in MyISAM and wanted move to InnoDB. This task can be a mammoth work but if you work little smarter, all you need to execute few statements and look at the progress while having cup of coffee. Also note that you can convert in both side, means from MyISAM to InnoDB and from InnoDB to MyISAM using these commands.

Sometimes in large deployments, there are cases when MySQL server, setup by you long time back which has been in use by multiple teams in your organization, needs some change or update or intrupption in its service and you are in need to know how many clients are there which connects to this server.

One way to know is to check user table in ‘mysql’ database, you might have created individual users/databases for your clients/users. But over the time, unless taking care seriously, we tend to forget the exactly how many users/scripts/machines are there making connections. May be you have used same read-only user in various scripts scattered over many machines which you’ve created just for a single client.