Showing entries 1 to 10 of 42
10 Older Entries »
Displaying posts with tag: mysql administration (reset)
How to get rid of orphaned tables in MySQL?

This post is about situation where comments in BUG reports are really valuable in term of learning and getting some internal view of going process.
Let’s take a look at ->

The report is about how you can not drop general tablespace if it was a crash of MySQL instant while running alter statement.

mysql> create tablespace ts1 add datafile '/var/lib/mysql_tablespaces/ts1.ibd' engine=innodb;
Query OK, 0 rows affected (0,02 sec)

Kill -9 process while altering:

mysql> alter table sbtest1 tablespace ts1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL …

[Read more]
How to get confused about buffer pool size allocation?

How it feels when you realize that, something going to be scary in term of question – “Wait, why? how?”
Let’s see:

I am trying to allocate 9G buffer pool size with 64 instances:


From error log it said that:

InnoDB: Initializing buffer pool, total size = 16G, instances = 64, chunk size = 128M

“Wait, why? how?” N1

From select it is also allocated 16G:

mysql> select @@innodb_buffer_pool_size/1024/1024/1024;
| @@innodb_buffer_pool_size/1024/1024/1024 |
| 16.000000000000 |

Just continue to increase pool size:

If you give 17G:

[Note] InnoDB: Initializing buffer pool, …

[Read more]
The magical abandoned .ibd files after crash recovery with MySQL 5.7.10

If you are running ‘OPTIMIZE TABLE’ or altering ‘TABLESPACE’ just be careful not to kill MySQL service while running these commands
Here is a test case:
We have a table:

CREATE TABLE `sbtest1` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`k` int(10) unsigned NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT '',
KEY `k_1` (`k`)

After a while we want to ‘OPTIMIZE’ this table:

mysql> optimize table sbtest1;
ERROR 2013 (HY000): Lost connection to MySQL server during query

Uups MySQL process was killed/crashed.

[root@ps-5 mysql]# ls -l dbtest/
total 1092792
-rw-r-----. 1 mysql mysql 65 фев 4 03:43 db.opt
-rw-r-----. 1 mysql mysql …

[Read more]
How to lose General Tablespace in MySQL 5.7.10

If you have not be careful with General Tablespaces in MySQL 5.7.10, you must be at least from now.
I have reported several bugs related to General Tablespaces for eg.:

* #77814 -> Altering table twice,with same tablespace will increase general tablespace size (verfied)

* #77556 -> Segmentation fault while creating general tablespace in disk full Linux (fixed/closed)

* #77830 -> “ALGORITHM=INPLACE is not supported” no error with tablespace alter (open)

* #80070 -> allocated_size and file_size differ if create general tablespace outside datadir (verified)

* …

[Read more]
Installing Percona Server with TokuDB and TokuBackup from source, in CentOS 7 minimal

Today i want to share experience with latest Percona Server where TokuDB and TokuBakcup plugins have been added in main source.
So there is no other steps needed here to get and started.
But in recent discussion in our local community, there was a question: “How about installing MySQL on CentOS minimal”.
The reason was clear, because somebody got too many errors in fresh CentOS 7 minimal.

I want to share here full steps.
Please follow numbers:

1. Getting Source package:

[root@testing-host1 ~]# wget

tar -xvf percona-server-5.6.27-75.0.tar.gz

2. Compiling using CMAKE:

If you have read the documentation:

[Read more]
Getting SIGABRT -> Assertion `is_binlog_empty()’ failed with MySQL 5.7.7-rc

What about inserting data to MySQL 5.7.7-rc-debug with GTID/binary log enabled. It is OK until you hit disk full condition while inserting data. It will crash on the way and likely you will lose MySQL. Error log indicates related problem:

2015-07-29T07:20:05.865708Z 2 [ERROR] InnoDB: posix_fallocate(): Failed to preallocate data for file ./sls2/sales.ibd, desired size 4194304 bytes. Operating system error number 28. Check that the disk is not full or a disk quota exceeded. Make sure the file system supports this function. Some operating system error numbers are described at
2015-07-29T07:20:05.895529Z 2 [Warning] InnoDB: 1048576 bytes should have been written. Only 557056 bytes written. Retrying again to write the remaining bytes.
2015-07-29T07:20:05.905149Z 2 [Warning] InnoDB: 491520 bytes should have been written. Only 106496 bytes written. Retrying again to write the remaining …
[Read more]
Altering tablespace of table – new in MySQL 5.7

Dear community, maybe you have already tested, there are new added features in MySQL 5.7. See previous post -> About 5.7.7-rc BUGs

“General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options. Today’s topic is altering table to use this “General Tablespace”.

Our sample table structure:

mysql> show create table t1;

    | Table | Create Table 
    | t1    | CREATE TABLE `t1` (
      `id` int(11) DEFAULT NULL,
      `c1` varchar(85) DEFAULT NULL
    ) /*!50100 TABLESPACE innodb_file_per_table */ ENGINE=InnoDB DEFAULT CHARSET=utf8 

Note that there is a comment about tablespace of our table -> /*!50100 TABLESPACE …

[Read more]
Testing MySQL 5.7.7-rc with memory and disc full conditions

With MySQL 5.7 there will be many improvements and changes. The most valuable ones are:

1. “General Tablespace” feature -> From now you can place tables to separate tablespaces. Prior, on our hands there were only “System TableSpace” and “InnoDB file per table” options.

Check for further reading: General Tablespaces Create statement: Create TableSpace

2. Resizing InnoDB Buffer Pool online -> for changing innodb_buffer_pool_size variable restart is required prior to MySQL 5.7, from now we can change buffer pool size without restart dynamically. Read Documentation ->

[Read more]
VALIDATE PASSWORD PLUGIN with mysql_secure_installation in 5.7.7-rc

While testing installation steps with MySQL 5.7.7-rc, surely you will find much more improvements, changes, deprecated functionality and etc.

The interesting one is activating VALIDATE PASSWORD PLUGIN via mysql_secure_installation script. Which we use by mean “securing” MySQL installations.

I will write a separate topic about MySQL 5.7.7-rc installation steps from source, with related BUG reports.

So after first run:

[root@centos7_vm mysql]# bin/mysql_secure_installation --socket=/opt/mysql/datadir/mysqld-new.sock
Securing the MySQL server deployment.
Connecting to MySQL server using password in '/root/.mysql_secret'

If you notice, now script trying to connect to MySQL using a temporary password which is generated for root@localhost and logged into hidden .mysql_secret file.

VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of …
[Read more]
Comprehensive guide to installing PXC on CentOS 7

Recently want to install Percona XtraDB Cluster + HAProxy + KeepAlived on CentOS 7, but could not find any all-in-one guide. So decided to write down all steps necessary for getting started and running HA solution using Open Source projects. Nowadays high availability is one of the main concerns faced by big and small companies.Minimum wasting of time (downtime per year), sustainably working of infrastructure and etc. is the main mission for all companies that they are trying to achieve.There are different approaches to this mission. One of them as large companies did, to buy expensive software and support from vendors. But small companies could not go through these steps. The true power of Open Source comes up at these moments.You can build your own High Availability solution using free but yet powerful Open Source projects. In this article we will show you how to achieve Database Clustering and Load Balancing. We will use:

[Read more]
Showing entries 1 to 10 of 42
10 Older Entries »