Personal blog of Yzmir Ramirez

 Enterprise software company Atlassian, which filed a month ago to go public on NASDAQ, has disclosed how much its shares will cost. In a SEC filing, the company said its 20 million shares will be priced between $16.50 and $18.50. Read More

Recently, I was working on a MySQL support ticket where a customer was facing an issue while transporting tablespace from MySQL 5.6 to MySQL 5.7.
After closely reviewing the situation, I saw that while importing tablespace they were getting errors such as:

ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x10 and the meta-data file has 0x1)

After some research, I found that there is a similar bug reported to MySQL for this issue (https://bugs.mysql.com/bug.php?id=76142), but no solution is mentioned. I tested the scenario locally and found a solution that I will detail in this post.

First, I created a table on MySQL 5.6

nilnandan.joshi@bm-support01:~$ mysql -uroot -p --socket=/tmp/mysql_sandbox5624.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 35
Server version: 5.6.24 MySQL Community Server (GPL)
...
mysql> create database nil;
Query OK, 1 row affected (0.02 sec)
mysql> use nil;
Database changed
mysql> create table nil(id int, name varchar(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into nil values (1, 'nilnandan'),(2, 'niljoshi'),(3, 'njoshi'),(4,'joshi');
Query OK, 4 rows affected (0.10 sec)
Records: 4 Duplicates: 0 Warnings: 0
mysql> select * from nil;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
| 2 | niljoshi |
| 3 | njoshi |
| 4 | joshi |
+------+-----------+
4 rows in set (0.02 sec)

Then took the backup for the nil database and exported it.

nilnandan.joshi@bm-support01:~$ innobackupex --defaults-file=/home/njoshi/sandboxes/msb_5_6_24/my.sandbox.cnf --user=root --password=msandbox --databases="nil" /home/njoshi/backup/
151127 01:29:14 innobackupex: Starting the backup operation
....
151127 01:29:16 Backup created in directory '/home/njoshi/backup//2015-11-27_01-29-14'
151127 01:29:16 [00] Writing backup-my.cnf
151127 01:29:16 [00] ...done
151127 01:29:16 [00] Writing xtrabackup_info
151127 01:29:16 [00] ...done
xtrabackup: Transaction log of lsn (1695477) to (1695477) was copied.
151127 01:29:16 completed OK!
nilnandan.joshi@bm-support01:~$
nilnandan.joshi@bm-support01:~$ innobackupex --apply-log --export backup/2015-11-27_01-29-14
151127 01:32:25 innobackupex: Starting the apply-log operation
...
xtrabackup: export option is specified.
xtrabackup: export metadata of table 'nil/nil' to file `./nil/nil.exp` (1 indexes)
xtrabackup: name=GEN_CLUST_INDEX, id.low=31, page=3
...
InnoDB: Starting shutdown...
InnoDB: Shutdown completed; log sequence number 1706518
151127 01:32:28 completed OK!
nilnandan.joshi@bm-support01:~$
nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ ll
total 140
drwx------ 2 nilnandan.joshi percona 4096 Nov 27 01:32 ./
drwx------ 3 nilnandan.joshi percona 4096 Nov 27 01:32 ../
-rw-r----- 1 nilnandan.joshi percona 65 Nov 27 01:29 db.opt
-rw-r--r-- 1 nilnandan.joshi percona 421 Nov 27 01:32 nil.cfg
-rw-r--r-- 1 nilnandan.joshi percona 16384 Nov 27 01:32 nil.exp
-rw-r----- 1 nilnandan.joshi percona 8586 Nov 27 01:29 nil.frm
-rw-r----- 1 nilnandan.joshi percona 98304 Nov 27 01:29 nil.ibd
nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$

Now on MySQL 5.7, I create a nil table, discarded tablespace, copied the .cfg and .ibd files from backup to the datadir, and set proper permissions.

nilnandan.joshi@bm-support01:~/backup/2015-11-27_01-29-14/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 16
Server version: 5.7.9-log MySQL Community Server (GPL)
...
mysql> create database nil;
Query OK, 1 row affected (0.04 sec)
mysql> use nil
Database changed
mysql> create table nil(id int, name varchar(10));
Query OK, 0 rows affected (0.04 sec)
mysql> ALTER TABLE nil DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.cfg .
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.ibd .
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ mysql -uroot -p --socket=/tmp/mysql_sandbox13454.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 17
Server version: 5.7.9-log MySQL Community Server (GPL)
...
mysql> use nil
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> show tables;
+---------------+
| Tables_in_nil |
+---------------+
| nil |
+---------------+
1 row in set (0.00 sec)
mysql> ALTER TABLE nil IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table flags don't match, server table has 0x5 and the meta-data file has 0x1)

After all that, I got the same error but I didn’t find any specific error message about the problem. When I deleted the .cfg file and tried again I got the exact error message.

mysql> ALTER TABLE nil IMPORT TABLESPACE;
ERROR 1808 (HY000): Schema mismatch (Table has ROW_TYPE_DYNAMIC row format, .ibd file has ROW_TYPE_COMPACT row format.)

So, I dropped the table on MySQL 5.7, created the same table with the  “row_format=compact” option, copied both .cfg and .ibd files again, and this time it worked.

mysql> drop table nil;
Query OK, 0 rows affected (0.00 sec)
mysql> create table nil(id int, name varchar(10)) row_format=compact;
Query OK, 0 rows affected (0.00 sec)
mysql> ALTER TABLE nil DISCARD TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> quit
Bye
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.ibd .
nilnandan.joshi@bm-support01:/home/nilnandan.joshi/sandboxes/rsandbox_mysql-5_7_9/master/data/nil$ sudo cp /home/njoshi/backup/2015-11-27_01-29-14/nil/nil.cfg .
mysql> ALTER TABLE nil IMPORT TABLESPACE;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from nil;
+------+-----------+
| id | name |
+------+-----------+
| 1 | nilnandan |
| 2 | niljoshi |
| 3 | njoshi |
| 4 | joshi |
+------+-----------+
4 rows in set (0.01 sec)

I’m not sure if the same issue occurs each time, but one thing is sure: removing the .cfg file gives you the exact cause of the problem and and lets you resolve it.

Why does moving tablespace from MySQL 5.6 to MySQL 5.7 give you this error? The answer is because the default innodb_file_format is changed in MySQL 5.7 from Antelope to Barracuda.

Important Change; InnoDB: The following changes were made to InnoDB configuration parameter default values:

• The innodb_file_format default value was changed to Barracuda. The previous default value was Antelope. This change allows tables to use Compressed or Dynamic row formats.

https://dev.mysql.com/doc/relnotes/mysql/5.7/en/news-5-7-7.html

I’ve already submitted bug report to MySQL for this error and inappropriate error message.  https://bugs.mysql.com/bug.php?id=79469

BTW: here the the link for how we can transport tablespace with Xtrabackup.
https://www.percona.com/doc/percona-xtrabackup/2.3/howtos/recipes_ibkx_partition.html

The post Transporting tablespace from MySQL 5.6 to MySQL 5.7 (case study) appeared first on MySQL Performance Blog.

 The Mozilla Foundation looks like it’s about to take another step in its bid to sharpen its focus on development around its Firefox browser. Mozilla now wants to once and for all hive off support for Thunderbird, the free email, chat and news client it first developed in 2004 but effectively stopped directly updating in in 2012. The plans were revealed in a company-wide memo penned… Read More

 Meet Sylaps, a voice-over-IP startup that lets you start phone calls without giving out your phone number. Sylaps is all about making it as seamless as possible to start a phone call with people you don’t know. Read More

 Dash, a driving app we’ve previously described as a “Fitbit for cars”, has announced it is expanding beyond consumer-facing products with Dash XL and Dash IQ, two new products designed for enterprise customers. As a refresher, the company is a combination of a hardware dongle and software app that provides drivers with real-time analytics on your driving habits, car health,… Read More

 Microsoft today launched PoweApps into preview. PowerApps is a new Office-like service that makes it easy for virtually any employee in a company to build basic mobile and web-based business apps — and for IT departments to enable them to do so. Read More

 Long restricted to the academic domain, Li-Fi, a light-based data delivery method is suddenly getting all sorts of attention. An Estonian startup Velmenni recently tested an commercial implementation and found it to be superior to Wi-Fi in almost every way – except as something you and I will probably ever use. Velmenni’s technology, called Jungru, uses an LED bulb and transmits… Read More

 When the news broke in early October that Dell was planning on buying EMC for a whopping $67 billion, more than a few jaws dropped (including mine), but in the weeks since, reports have surfaced about multiple problems from tax issues to VMware’s plunging stock price and the post-deal creation of Virtustream. It’s too soon to say the deal is in jeopardy, but there are enough… Read More

Introduction
In my last blog post, Internet of Things, Messaging and MySQL, I have showed how to start your own Internet of Things with Particle Photon board. That implementation is great, but requires constant internet (wi-fi) access as the Particle Photon board does not have any local storage. If you do not have a reliable network access (i.e. in some remote places) or need something really small to store your data you can now use Intel Edison. I’ve even install MySQL on Edison, which makes it the smallest (in size) MySQL server in the world! Other options include:

• Raspberry Pi (my colleague Roel installed Percona Server on the Raspberry Pi)
• Android based system (i.e. Nexus 7: Vadim installed Percona Server on Nexus 7)

MySQL Configuration

Intel Edison is a tiny computer based on 22 nm Silvermont dual-core Intel Atom CPU, 500MHz, running Linux (Ubuntu based distribution called Yocto). To program the Edison we will need a breakout board. Options include Arduino compatible breakout board (which includes SD card) and a small Intel breakout board.

The installation and configuration is straightforward. I’ve used the Get Started with Yocto Project on the Intel Edison Board guide to setup and configure the board. First we need to connect to Edison via serial connection and configure sshd and Wi-Fi; when it is done we can connect to Edison using SSH.

The MySQL installation is relatively easy as Linux generic binaries are compatible with Yocto Linux (so you do not have to compile anything). There are 2 challenges though:

• By default the Yocto linux (as well as the official repository) does not include libraries needed for MySQL: libaio1, libcrypto, libssl
• The internal storage is tiny and MySQL 5.7 binaries did not even fit into any partition. I had to remove some “tests” and other stuff I do not need. For the real installation one can use SD card (SD slot is available on some boards).

To install the libraries I’ve used the un-official Edison repositories following the excellent guide: Edison package repo configuration. Setup is simple:

To configure your Edison to fetch packages from this repo, replace anything you have in /etc/opkg/base-feeds.conf with the following (other opkg config files don’t need any changes):

src/gz all http://repo.opkg.net/edison/repo/all
		src/gz edison http://repo.opkg.net/edison/repo/edison
		src/gz core2-32 http://repo.opkg.net/edison/repo/core2-32

Then we will need to setup the libraries:

# opkg install libaio1_0.3 libcrypto1.0.0 libssl1.0.0

Finally we can download Percona Server 5.6 and place it somewhere (use basedir in my.cnf to point to the installation path):

# wget https://www.percona.com/downloads/Percona-Server-5.6/Percona-Server-5.6.25-73.1/binary/tarball/Percona-Server-5.6.25-rel73.1-Linux.i686.ssl100.tar.gz

Please note that the latest Percona Server 5.6 depends on the Numa library and there is no such library for Yocto (does not make sense for Edison). So 5.6.25 is the latest Percona Server you can install here.

The simple (and rather useless) benchmark on Intel Edison:

root@edison:/usr/local/mysql# cat /proc/cpuinfo
processor : 0
vendor_id : GenuineIntel
cpu family : 6
model : 74
model name : Genuine Intel(R) CPU 4000 @ 500MHz
...
mysql> SELECT BENCHMARK(10000000,ENCODE('hello','goodbye'));
+-----------------------------------------------+
| BENCHMARK(10000000,ENCODE('hello','goodbye')) |
+-----------------------------------------------+
|                                             0 |
+-----------------------------------------------+
1 row in set (18.77 sec)

Can MySQL make you toast?

The famous MySQL Bug#2, submitted 12 Sep 2002, states that “MySQL Connector/J doesn’t make toast”. With Intel Edison and Arduino compatible breakout board it is now trivial to fix this bug: not only MySQL Connector/J but also MySQL server itself can make you a toast! This can be done via UDF or, in MySQL 5.7, with Query Rewrite Plugins, so you can execute MySQL query:

mysql> make toast;

For the actual implementation you can either “hack” an existing toaster to interface with breakout board pins or use a Arduino compatible Robotic Arm. Ok, MySQL, make me toast!

The post Smallest MySQL Server in the World (under $60) which can even make you toast while fixing MySQL bug #2 appeared first on MySQL Performance Blog.

 When Slack’s IPO window opens, it wants to be able to quickly spring into the public markets. Otherwise it could see sour conditions or botched IPOs by other tech companies shut the window before it rings the opening bell. That’s why Slack CEO Stewart Butterfield says it’s already getting its ducks in a row so its poised to IPO when the time comes. Read More