theMySQLGuy Podcast

Hello Everyone!

I have finished my move to the Bay Area (Silicon Valley) and now have more time to return to the blog.  I have learned a few new tricks as well I want to share.

So…..

I am relaunching the site.  There will be a new format too.  The podcast episodes will still be published for more high level discussions, then supported by a series of blog posts.

I think this will be a more productive format, since most users want to hear the high level discussion and then go to the site and refer to the material.

Thanks for all the fish!

Christos

Podcast: Play in new window | Download

Recently we ran into a wall in one of my customers’ sites. They built an application that processed EDI documents. Each document, contained a list of transactions. Their application would launch a thread for each transaction in the document. On the surface this sounds good and the multi-threaded approach would speed up processing of a document.

InnoDB is the only built-in transactional storage engine and unfortunately has some limitations.

TX1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
TX2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
TX1 START TRANSACTION
TX2 START TRANSACTION
TX1 INSERT INTO child
TX2 INSERT INTO child (with same parent)
TX1 UPDATE parent
TX2 UPDATE parent (same parent row)

ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

We solved this issue using a third party storage engine; solidDB.

Other storage engines available with MySQL

Podcast: Play in new window | Download

Federated Storage Engine (FSE) allows you to connect to a remote server and “mount” a table on your local server, which links to the data on the remote server, for read-only access.

$ mysql -u root -ppassword -h remote

mysql> CREATE DATABASE test;

mysql> use test;

mysql> CREATE TABLE drivers (id INT,name VARCHAR(100));

mysql> INSERT INTO drivers (id, name) VALUES (1, ‘Chris’);

mysql> INSERT INTO drivers (id, name) VALUES (2, ‘Sheeri’);

mysql> INSERT INTO drivers (id, name) VALUES (3, ‘Elie’);

mysql> select * from drivers;

+------+--------+
| id   | name   |
+------+--------+
|    1 | Chris  |
|    2 | Sheeri |
|    3 | Elie   |
+------+--------+
3 rows in set (0.08 sec)

mysql> exit;

$ mysql -u root -ppassword -h local

mysql> CREATE DATABASE test;

mysql> use test;

mysql> CREATE TABLE drivers (id INT,name VARCHAR(100)) ENGINE=FEDERATED
CONNECTION=’mysql://root:password@remote:3306/test/drivers’;

mysql> select * from drivers;

+------+--------+
| id   | name   |
+------+--------+
|    1 | Chris  |
|    2 | Sheeri |
|    3 | Elie   |
+------+--------+
3 rows in set (0.14 sec)

mysql> exit;

Enjoy FSE tables!

Bye! Thanks for all the fish!

christos@themysqlguy.com

Podcast: Play in new window | Download