Feed aggregator

The slides are here: http://www.slideshare.net/blueskarlsson/my-sq-lonaws101 and if you don't know it already, tomorrow friday April 26 is SkySQL Solutions Day. Registration and Beer is free! I'll be talking on MySQL on JSON but that are many other interesting talks, The venue is the same as the Percona Live conference!

/Karlsson

Yes, Percona Live ends on Thursday, but on friday. at the same location, there is SkySQL Solutions day. Attendance is free, and among the highlights yours truly will be speaking! Register here for this free event: http://info.skysql.com/roadshow2013-0 Another highlight of this is that it ends with a Biergarten. Free!

/Karlsson

I'm in Santa Clara for Percona Live now, and things are looking good! The announceent on the merger of Monto Program and SkySQL is a good one! If you are around, I'll be speaking on MySQL on AWS on Thursday at 1:50 PM in Ballroom F. On Friday at SkySQL Solutions day (if you are at Percona Live and don't know about this, registration is free! Come see us, the program is here: http://www.skysql.com/content/mysql-cloud-database-solutions-day-schedule) I'll be talking about MySQL and MariaDB with JSON at 11:15 AM in Grand Ballroom B!

I'm also about to release a new version of my MySQL JSON tools real soon!

/Karlsson

I mentioned in a recent post (and another recent, related post) I was going to try Visual Studio 2012 Express (as opposed to VS 2010 Express) for building MySQL 5.6 and MariaDB 10.0. on Windows.

I followed the exact same steps, but MySQL 5.6.10 would not build without fatal errors in 2012 (whereas it worked fine with VS 2010). Unfortunately, the fail errors were not too descriptive, so not quite sure what to do yet to fix it. Here is a brief snippet for those interested:

...
Done Building Project "C:\...\bld\sql\udf_example.vcxproj" (default targets).
Done Building Project "C:\...\bld\ALL_BUILD.vcxproj" (default targets) -- FAILED.
Done Building Project "c:\...\bld\package.vcxproj" (default targets) -- FAILED.
...
Build FAILED.
...

"c:\mysql\mysql-5.6.10\bld\package.vcxproj" (default target) (1) ->
"C:\mysql\mysql-5.6.10\bld\ALL_BUILD.vcxproj" (default target) (2) ->
"C:\mysql\mysql-5.6.10\bld\plugin\audit_null\audit_null.vcxproj" (default target) (18) ->
"C:\mysql\mysql-5.6.10\bld\sql\mysqld.vcxproj" (default target) (19) ->
(Link target) ->
  mysqld.exp : error LNK2001: unresolved external symbol
  _xmm@7fffffffffffffff7fffffffffffffff [C:\mysql\mysql-5.6.10\bld\sql\mysqld.vcxproj]
  C:\mysql\mysql-5.6.10\bld\sql\RelWithDebInfo\mysqld.exe : fatal error LNK1120:
  1 unresolved externals [C:\mysql\mysql-5.6.10\bld\sql\mysqld.vcxproj]

    6 Warning(s)
    2 Error(s)

Time Elapsed 00:01:06.67

On the plus side, MariaDB 10.0.1 compiled just fine with both Visual Studio 2012 Express and Visual Studio 2010 Express! So I was really happy about that.

I thought I could just keep both VSs installed, but they don’t seem to want to play nice, even when specifying the following for the MySQL 5.6 build (perhaps that’s just me though):

cmake .. -G "Visual Studio 10"

So, if you are building your own on Windows, then you may want to keep the above in mind (my tests have been on Windows 7 64-bit, fwiw).

I’ll continue to test, and post anything useful if there’s something worth mentioning.

 

Related Build Links:

• Building MySQL and MariaDB on Windows using Visual Studio 2012 Express versus Visual Studio 2010 Express:
  http://www.chriscalender.com/?p=1245

• How to Build MySQL 5.6 on Windows from Source Code:
  http://www.chriscalender.com/?p=1233

• How to Build MariaDB 10.0 on Windows from Source Code:
  http://www.chriscalender.com/?p=1224

• How to Build MariaDB 5.5 on Windows from Source Code:
  http://www.chriscalender.com/?p=1087

• How to Build MySQL 5.5 on Windows from Source Code:
  http://www.chriscalender.com/?p=689

• Building MariaDB 5.1 on Windows:
  http://www.chriscalender.com/?p=736

• Building MariaDB 5.3 on Windows:
  http://www.chriscalender.com/?p=864

• MySQL 5.5 Windows Build Appendix : Full Outputs and Common Errors from Building MySQL 5.5 on Windows:
  http://www.chriscalender.com/?p=720

• Maintaining (and Building) your MySQL Source Tree on Windows:
  http://www.chriscalender.com/?p=759

• Resolving the bison.exe m4 Invalid argument Error when building MySQL/MariaDB/XtraDB on Windows:
  http://www.chriscalender.com/?p=798

• Building XtraDB on Windows:
  http://www.chriscalender.com/?p=820

 

I just wanted to take a moment and thank, notice, what ever you want to call it, but share some love with all those and all things MySQL.

I read the post Let’s Celebrate MySQL 5.6 GA! – MySQL Community Reception by Oracle by Oracle MySQL Group and it got me to thinking of how proud I am of (and proud to be a part of) the whole MySQL Ecosystem.

We *should* all celebrate MySQL 5.6 GA! I well remember the 3.22 and 3.23 days, and there were many folks before me already using MySQL!!!

I love to see how it has continued to grow, the ecosystem and all things MySQL, that is. MySQL is better than ever. MariaDB is better than ever. Percona Server is better than ever. You have great Support options with MySQL/Oracle, SkySQL/MariaDB, and Percona as well – not to mention numerous others. I just love the fact that everyone is flourishing, and improving, and the technology continues to advance and most importantly, everyone is working together to provide a great software and service to all MySQL/MariaDB/InnoDB/XtraDB/etc-related users out there.

Not to mention the monitoring softwares, the various plugins and connectors, the numerous storage engines, the number of 3rd party apps & helpful scripts, User Conferences, local meet-ups, books, tutorials, the number of Community bloggers who freely and spiritedly share their knowledge, time, advice, ideas, concerns, and so on. It’s just a great Community to be a part of, and I’m thrilled to see how it’s grown, and continues to grow over the years. And that’s just a testament of the quality of all you great people out there involved and helping.

I won’t get too mushy, but it is great to see the MySQL Ecosystem and all of those in it flourish, and may it continue for many, many years to come!

Big thanks to everyone out there contributing! “It takes a village, and we are that village”, as my wife always says.

 

I'll be speaking at the MySQL Conference and Expo on April 22-25 in Santa Clara. On April 25 at 1:50 PM I'll be talking about using MySQL in the Amazon AWS cloud but to be honest, I hevn't done much formal preparations. I will prepare some slides here, but fact is that most of this session will be practical, hands-on stuff. Largely, I'll show stuff that I used when I was Database Architect and Admin for a reasonably large AWS installation.

Anyone telling you that Amazon AWS is just like any other environment, except that disk-I/O is slower, doesn't get it, there is much more to AWS than that. By using the services that comes as part of AWS there are loads on things you as a DBA or Devops can do to simplify and automate everyday tasks. Backups, slave provisioning, availability are things that can make really good use of AWS. So armed with an AWS account and some MySQL instances running there, I'll be showing you some real world examples.
Also, you may ask why I haven't been blogging much recently? If this worries you, I think you should get a day job. Jokes aside, I was testing MySQL replication in real life, but it sort of failed on me, and I ended up with twins alright, but one boy and one girl. These two has taken a lot of my time recently, and the joys of blogging and writing code in spare time was changed to exercising the joy of changing dipers on these little babies:
I will do some more blogging now again though, I have promised myself to do that, but I have loads of other things to do also.
/Karlsson

I just set up a new Windows 7 machine, and built MySQL 5.6 for the first time, so I wanted to share my steps for anyone interested.

** Prerequisites **
1. Install Microsoft Visual Studio (I installed VS 2010 Express – free)
2. Install cmake
3. Install bison (make sure no spaces in path)

** Build Instructions **

cd c:\mysql\mysql-5.6.10
mkdir bld
cd bld
cmake ..
cmake --build . --config relwithdebinfo --target package

(You don’t technically need the ‘bld’ dir, but I prefer it to keep the main dir ‘clean’. Then if you need to re-build, you can quickly just delete the whole ‘bld’ directory, and start fresh.)

Here is my actual output for those interested:

c:\>cd c:\mysql\mysql-5.6.10
c:\mysql\mysql-5.6.10>mkdir bld
c:\mysql\mysql-5.6.10>cd bld
c:\mysql\mysql-5.6.10\bld>cmake ..
-- Building for: Visual Studio 10
-- Running cmake version 2.8.10.2
-- The C compiler identification is MSVC 16.0.30319.1
-- The CXX compiler identification is MSVC 16.0.30319.1
-- Check for working C compiler using: Visual Studio 10
-- Check for working C compiler using: Visual Studio 10 -- works
-- Detecting C compiler ABI info
-- Detecting C compiler ABI info - done
-- Check for working CXX compiler using: Visual Studio 10
-- Check for working CXX compiler using: Visual Studio 10 -- works
-- Detecting CXX compiler ABI info
-- Detecting CXX compiler ABI info - done
-- Check size of void *
-- Check size of void * - done
-- MySQL 5.6.10
-- Packaging as: mysql-5.6.10-win32
-- Looking for include file sasl/sasl.h
-- Looking for include file sasl/sasl.h - not found
-- Looking for include file sys/devpoll.h
-- Looking for include file sys/devpoll.h - not found
-- Looking for include file signal.h
-- Looking for include file signal.h - found
-- Looking for include file sys/devpoll.h
-- Looking for include file sys/devpoll.h - not found
-- Looking for include file sys/epoll.h
-- Looking for include file sys/epoll.h - not found
-- Looking for include file sys/event.h
-- Looking for include file sys/event.h - not found
-- Looking for include file sys/queue.h
-- Looking for include file sys/queue.h - not found
-- Looking for TAILQ_FOREACH
-- Looking for TAILQ_FOREACH - not found
-- Looking for include file pthread.h
-- Looking for include file pthread.h - not found
-- Found Threads: TRUE
-- Looking for _aligned_malloc
-- Looking for _aligned_malloc - found
-- Looking for _aligned_free
-- Looking for _aligned_free - found
-- Looking for fedisableexcept
-- Looking for fedisableexcept - not found
-- Looking for posix_memalign
-- Looking for posix_memalign - not found
-- Looking for ntohll
-- Looking for ntohll - not found
-- Looking for clock_gettime
-- Looking for clock_gettime - not found
-- Looking for epoll_create
-- Looking for epoll_create - not found
-- Looking for epoll_ctl
-- Looking for epoll_ctl - not found
-- Looking for inet_ntop
-- Looking for inet_ntop - not found
-- Looking for kqueue
-- Looking for kqueue - not found
-- Looking for kqueue
-- Looking for kqueue - not found
-- Looking for signal
-- Looking for signal - found
-- Looking for timeradd
-- Looking for timeradd - not found
-- Looking for timerclear
-- Looking for timerclear - not found
-- Looking for timercmp
-- Looking for timercmp - not found
-- Looking for timerisset
-- Looking for timerisset - not found
-- Check size of time_t
-- Check size of time_t - done
-- Performing Test TIME_T_UNSIGNED
-- Performing Test TIME_T_UNSIGNED - Failed
-- Looking for sched_getcpu
-- Looking for sched_getcpu - not found
-- Looking for asprintf
-- Looking for asprintf - not found
-- Using cmake version 2.8.10.2
-- Not building NDB
-- Googlemock was not found. gtest-based unit tests will be disabled. You can run cmake . -DENABLE_DOWNLOADS=1 to automatically download and build required components from source.
-- If you are inside a firewall, you may need to use an http proxy: export http_proxy=http://foo.bar.com:80
-- Cannot find wix 3, installer project will not be generated
-- Configuring done
-- Generating done
-- Build files have been written to: C:/mysql/mysql-5.6.10

c:\mysql\mysql-5.6.10\bld>cmake --build . --config relwithdebinfo --target package
...
... tons of build/compilation output ...
...
    406 Warning(s)
    0 Error(s)

Time Elapsed 00:16:46.65

There you go!

The zip package (mysql-5.6.10-win32.zip in this case) will be located in bld/.

I will say that is a lot of warnings, though I’m fairly used to them on Windows. However, I plan to try VS 2012 Express to see if that makes any difference.

Hope this helps.

 

I just wanted to share my steps for building MariaDB 10.0 on Windows (10.0.1 in this case):

cd maria-10.0.1
mkdir bld
cd bld
cmake .. -G "Visual Studio 10"
cmake --build . --config relwithdebinfo --target package

That’s it! The zip file is created right there in bld/.

Hope this helps.

 

As the press release says: “Come have a (free) beer with Monty: SkySQL & MariaDB’s 2nd Annual MySQL & Cloud Database Solutions Day”!

“Are you planning on being in Santa Clara for Percona Live: MySQL Conference and Expo in April? If so, don’t let the learning and networking stop on Thursday!

We invite you to join SkySQL & MariaDB for our 2nd Annual MySQL® & Cloud Database Solutions Day, Friday, April 26, 2013 at the Hyatt Regency Santa Clara. This event will be taking place directly after the Percona Live event.

During this free one-day event, you will receive hands-on training and consulting on MySQL and MariaDB database solutions from the experts at SkySQL, MariaDB, and their partners.”

Speakers will include Big Data, Analytics and Cloud experts, as well as SkySQL customers and MariaDB users. Including:

Monty Widenius – MariaDB
Yoshinori Matsunobu – Facebook
Seppo Jaakola – Codership
Kyle MacDonald – OpenStack Foundation
Full Speaker List

“The event will feature two technical tracks: one focused on the MySQL database and related technologies, and the second on best practices for deploying database applications in the Cloud.”

To learn more, take a look at our event schedule, featured speakers, and venue and please register today:

Register Today
Full Schedule

 

I enabled the InnoDB Lock Monitor in my MariaDB 5.5 instance (using XtraDB+ as the InnoDB – which is the default in MariaDB) and noticed that while the SHOW ENGINE INNODB STATUS was being logged to the error log, it wasn’t logging the “additional” lock information – it just looked like the plain ‘ole INNODB STATUS.

Long story short, Percona added a new variable so one has better control over what gets logged:

innodb_show_verbose_locks

If off (default), then the InnoDB Lock Monitor logs the normal INNODB STATUS, and if enabled, then it logs it with the extended lock information.

They also created another variable that goes along with this one (and the InnoDB Lock Monitor), which is:

innodb_show_locks_held

This variable indicates the number of locks to print that are held for each InnoDB transaction (the default is 10, max is 1000).

For reference, these 2 options are discussed further in Percona’s manual.

Hope this helps.

 

Caution: This is just a fun post.

I was performing some testing on Windows the other day and encountered the following error in the error log:

130313  9:40:03 [ERROR] Error writing file 'G:\mysql\log.txt' (errno: 28)

Quickly, I ran “perror 28″ from the command line:

c:\>perror 28
Win32 error code 28: The printer is out of paper.

Hehe, really, Windows?!?

In fact, the drive was out of space. No more could be written to the general log, hence the error.

Fwiw, the Linux OS code 28 reports what you would expect:

OS error code  28:  No space left on device

So, if you ever run into “The printer is out of paper” error, you’ll know what to do.

 

As some of you may or may not know, I’ve maintained a list of all InnoDB Plugin versions as they’ve historically contained a different version (entirely different numbering scheme) than the MySQL distribution they were included with.

This list was most helpful for troubleshooting various InnoDB issues when the plugin may (or may not) have been involved, and/or for benchmarking, etc. And it’s fair to say it was more useful when the InnoDB plugin was not the mainstream, which it is now.

However, with the latest releases, in MySQL and MariaDB, the “InnoDB Version” simply matches the “MySQL Version”. These “latest releases” include: MySQL 5.6.10, MySQL 5.5.30, MySQL 5.1.68, and MariaDB 5.5.30

Of course this isn’t the most newsworthy story, but having maintained this “list” the past couple/few years, I was happy to see the change, and at least wanted to mention it.

There won’t be a need for me to further update the page, but it is up-to-date now, so if you happen to need to know what version of InnoDB Plugin an older version of MySQL or MariaDB is using, then this page will still be there for you (it’s also in the right-hand column of my site under “Pages” titled “InnoDB Plugin Versions“).

Hope this helps.

 

I just wrote an article about “Troubleshooting ‘Waiting for table metadata lock’ Errors for both MyISAM and InnoDB Tables” and then ran into a new, different metadata locking issue right after I posted it, and so I thought I’d share that too, just in case anyone ever encounters a similar situation.

In this case, mysqld kept crashing on restart, reporting “out of memory” errors:

/opt/app/mysql/product/mysql/bin//mysqld: Out of memory (Needed 840 bytes)
stack_bottom = 7fb4ebaeae58 thread_stack 0x40000
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
mysqld: /mysql/mysys/my_new.cc:52: int __cxa_pure_virtual():
  Assertion `! "Aborted: pure virtual method called."' failed.
Fatal signal 6 while backtracing

There is a recent bug fixed (in 5.5.29) that mentions the same assert above, but that is not the same problem here, as that bug always reports “InnoDB: Failing assertion: page_get_n_recs(page) > 1″.

Looking further into these repeated crashes, I noticed the following some of the stack traces always showed this:

stack_bottom = 7f2cd227ce58 thread_stack 0x40000
/opt/app/mysql/product/mysql/bin//mysqld(my_print_stacktrace+0x35)[0x79e2d5]
/opt/app/mysql/product/mysql/bin//mysqld(handle_fatal_signal+0x403)[0x66e233]
/lib64/libpthread.so.0(+0xf7c0)[0x7f46041577c0]
/../mysqld(_ZN10MDL_ticket6createEP11MDL_context13enum_mdl_type+0x26)[0x61a3e6]
/../mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0xa2)[0x61c4c2]
/../mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0x63)[0x61cb53]
/../mysqld[0x539d76]
/../mysqld(_Z10open_tableP3THDP10TABLE_LISTP11st_mem_rootP18Open_table_context+0x1b1)[0x541591]
/../mysqld(_Z11open_tablesP3THDPP10TABLE_LISTPjjP19Prelocking_strategy+0x42b)[0x542d2b]
/../mysqld(_Z30open_normal_and_derived_tablesP3THDP10TABLE_LISTj+0x48)[0x543678]
/../mysqld(_ZN18Prepared_statement7prepareEPKcj+0xc5e)[0x58a99e]
/../mysqld(_Z19mysqld_stmt_prepareP3THDPKcj+0xae)[0x58bbee]
/../mysqld(_Z16dispatch_command19enum_server_commandP3THDPcj+0xc21)[0x57c4e1]
/../mysqld(_Z24do_handle_one_connectionP3THD+0x167)[0x613447]
/../mysqld(handle_one_connection+0x54)[0x6134b4]
/../mysqld(pfs_spawn_thread+0x5b)[0x7f6abb]
/lib64/libpthread.so.0(+0x77b6)[0x7f460414f7b6]
/lib64/libc.so.6(clone+0x6d)[0x7f46033e7c5d]

At first glance, it’s kind of hard to read. However, if you focus in, you can extract some useful information.

Looking just before the crash (handle_fatal_signal), we see these 3 lines:

/../mysqld(_ZN10MDL_ticket6createEP11MDL_context13enum_mdl_type+0x26)[0x61a3e6]
/../mysqld(_ZN11MDL_context21try_acquire_lock_implEP11MDL_requestPP10MDL_ticket+0xa2)[0x61c4c2]
/../mysqld(_ZN11MDL_context12acquire_lockEP11MDL_requestm+0x63)[0x61cb53]

The keys parts of that, in reverse order are:

acquire_lock
try_acquire_lock_impl
enum_mdl_type

These 2 functions and variable, respectively, are only found in mdl.cc (and mdl.h) – the metadata lock files.

And after examining the MySQL source code, this stack trace tells us that there is an issue with the metadata locks. Specifically, it is trying to acquire a metadata lock (try_acquire_lock_impl), but it is failing (enum_mdl_type is set when try_acquire_lock_impl() fails, an assert is thrown, and mysqld crashes).

I’ve searched the bugs database for anything similar to this, but didn’t find any existing ones specific to try_acquire_lock_impl. That’s not to say there isn’t some yet-to-be-detected-bug here, but in this case, the machine was testing a custom application, so I strongly suspected the custom app causing the contention, which was the case.

So if you do happen to encounter this crash, then hopefully this will help you track down the problem.

Hope this helps, and happy troubleshooting.

 

With the introduction of metadata locking in MySQL 5.5, it is much more common to see queries stuck in the “Waiting for table metadata lock” state.

If you see a query stuck in the “Waiting for table metadata lock” state, waiting for a MyISAM table, then it is because this table has been included as part of some uncommitted transaction (whether intentionally or not).

In fact, it could be as simple as issuing SET AUTOCOMMIT=0 followed by a SELECT against a MyISAM table (a transaction can be started 3 ways: START TRANSACTION, BEGIN, and SET AUTOCOMMIT=0). After that, the table will be locked to other DDL statements until the transaction is committed (or ended).

This metadata lock is meant for DDL statements, and so that is what it blocks. However, once a DDL statement is blocked and waiting, then all queries to that table (including SELECTs) are blocked. Here is how you can easliy reproduce it:

Connection #1:

create table t1 (id int) engine=myisam;
set @@autocommit=0;
select * from t1;

Connection #2:

alter table t1 rename to t2; <-- Hangs

You can see it hanging here (as well as a subsequent SELECT from a 3rd connection):

mysql> show full processlist;
+----+---+-------+---------------------------------+-----------------------------+
| Id |...| Time  | State                           | Info                        |
+----+---+-------+---------------------------------+-----------------------------+
|  1 |...|  1653 |                                 | NULL                        |
|  2 |...|     0 | NULL                            | show full processlist       |
|  3 |...| 18012 | Waiting for table metadata lock | alter table t1 rename to t2 |
|  4 |...| 17983 | Waiting for table metadata lock | select * from t1            |
+----+---+-------+---------------------------------+-----------------------------+

This is the expected behavior, but can be a little tricky to diagnose if it's happening to you, and you don't see any other queries currently running in SHOW FULL PROCESSLIST (and/or SHOW ENGINE INNODB STATUS). Furthermore, you cannot track this with any of the known lock tracking methods, such as InnoDB Lock Monitor, InnoDB Status, mysqladmin debug output, INFORMATION_SCHEMA, etc.), not to mention one generally doesn't associate transactions with MyISAM tables anyway.

You can also run into this same error in InnoDB, which one would not necessarily expect because of InnoDB's row-level locking.

However, when you introduce triggers, then it becomes easy to reproduce a similar situation.

create table t3 (id int) engine=innodb;
create table t4 (id int) engine=innodb;
delimiter |
CREATE TRIGGER t3_trigger AFTER INSERT ON t3
  FOR EACH ROW BEGIN
    INSERT INTO t4 SET id = NEW.id;
  END;
|
delimiter ;

Connection #1:

begin;
insert into t3 values (1);

Connection #2:

drop trigger if exists t3_trigger; <-- Hangs

So, if you see this with an InnoDB table, then check your SHOW ENGINE INNODB STATUS and look for prior transactions that hold locks and are still running. Those would be your likely culprits for what is blocking the DROP TRIGGER command (in this case).

Hope this helps and happy troubleshooting.

 

Ever see an error like this in your console or error log, respectively, and wonder where it originated from?

Console (5.5.30):

ERROR 1017 (HY000): Can't find file:
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

Error Log (5.5.30):

130307 23:22:04 [ERROR] C:\Program Files\MySQL\MySQL Server 5.5\bin\mysqld: 
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

(Fwiw, they are harmless, unless it is indicative of some sql injection – but you may want to sanitize your data better, unless it’s just a one-time user error. Also, if you’re seeing it from the command line, then you’ll likely realize what you did immediately, and thus no need to ‘figure out’ what happened. However, when you *only* see this entry in the error log, and you want to know how that happened after the fact, then this is more relevant.)

At any rate, after searching the code, one can see that “can’t fild file” can originate from several locations, two such being in ./sql/sql_show.cc and ./sql/sql_rename.cc (the code for SHOW CREATE TABLE and RENAME TABLE, respectively).

So, working backwards, I tried both a SHOW CREATE TABLE and RENAME TABLE (with bogus table names), and was able to reproduce the error (because I was wondering how this ended up in an error log I was analyzing):

mysql> SHOW CREATE TABLE `select * from t1`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22)

mysql> RENAME TABLE `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2)

Note the first *does* get logged to the error log, whereas the second one does not. I’ve filed a bug report about this behavior:

http://bugs.mysql.com/bug.php?id=68618

The only difference between the two errors are the errno values are different for each (22 vs. 2). For reference, here are the meanings for codes 22 and 2 respectively:

C:\Windows\system32>perror 2
OS error code   2:  No such file or directory
Win32 error code 2: The system cannot find the file specified.

C:\Windows\system32>perror 22
OS error code  22:  Invalid argument
Win32 error code 22: The device does not recognize the command.

I will say that in 5.6, the error message is a bit more descriptive by mentioning the text of the “errno”:

Console (5.6.10):

mysql> show create table `select * from t1`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 22 - Invalid argument)
mysql> rename table `select * from t1` to `t2`;
ERROR 1017 (HY000): Can't find file: 
'.\test\select@0020@002a@0020from@0020t1.frm' (errno: 2 - No such file or directory)

Error Log (5.6.10):

2013-03-09 10:33:38 6340 [ERROR]
C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqld:
Can't find file: '.\test\select@0020@002a@0020from@0020t1.frm'
(errno: 22 - Invalid argument)

Hope this helps.

 

In the same time of passionate debat of the best distribution MySQL vs MariaDB on a 120K Euro server(here), i wanted to share the story of drop of InnoDB insert performance for one of our client having a tables between 10 to 100 Billions rows.

In most case this size of table is just a no go in relationnel database.

So why it used to work so far  ?

c1 int, c2 tinyint, c3 mediumint, c4 binary(1), c5 float .
c2 avg cardinality 1000
c3 avg cardinality 10000
Very simple log table with optimized column type :

• Partition by range on c1 where c1 increase with loading date
• InnoDB compression 8K block size  

A first good metric when inserting 1 row

• Into a 1G partition we touch about 400 block in memory 
• Into a brain fresh partition we touch 7 block 

So partition really help by limiting memory required to keep the index hot 
The issue appear because :

1. The data have been reloaded not on a long period but on a short period of time not enabling the data to be spread into multiple partitions and  the index partition was reaching 600G for a much much smaller buffer pool. 
2. The loading process was slow down from InnoDB compression, 3 times slower then without compression in this case. 

Partition by loading data date not good ? what else :

1. Creation date not a solution: old data could be load after a long time
2. Hash of c1 not a solution as load and read are concurrent and touch many blocks in concurrent partitions
3. We lose drop partition feature 

Life in consulting can be hard but now the solution:

In a first step we upgrade to MariaDB 5.5 
Create a table c1 int, c3 mediumint, c4 blob, c5 blob .We use c2 as indice of Dynamic Column as it get less then 65K values
https://kb.askmonty.org/en/dynamic-columns
https://mariadb.atlassian.net/browse/MDEV-377
And insert data like :INSERT INTO t VALUES (c1, c2 , CREATE_COLUMN(c4,c2(row1) as BINARY(1), 'c4(row1)',  ,c2(row2) as BINARY(1), 'c4(row2)' ...... ) , CREATE_COLUMN(c2(row1) as DECIMAL 'c5(row1)',  ,c2(row2) as DECIMAL , 'c5(row2)' .........) 
With that trick we divide by 1000 the number of rows in the table 
What are the consequences and the big surprise here 

• Load data is just faster as we remove the compression time 
• Innodb table size is smaller then the compressed version 

Why the table is smaller ? I can just guess here  ! 

• InnoDB as a fill factor space that may be removed from beeing stored in a blob  
• InnoDB have record overhead that is removed by Dynamic Column 

In a futur step we could upgrade to MariaDB 10 and store those blob in a cassandra cluster . 
Advantages :

• We get free sharding 
• Security is fine because meta data stay in the DB and who can figure out what a bunch of float  really mean :)

SkySQL Roadshow is coming to Stockholm on Feb 7, come by and meet us. I'll be ending the day with a talk on Big Data, which will be a more generic Big Data talk with some MySQL relevance, but with the focus on Big Data in general.

I haven't blogging much recently, but that has some reasons. I am since Dec 1 the proud father of twins, a little boy and a little girl. I have yet to teahc them to write proper SQL, the have particular issues with subqueries, but we'll get there. In order to create the usual mess of things and to make sure things are at the brink of running out of control, we decided to renovate our flat in the middle of all this. But I'll get there, and once we have a new kitchen installed, I'll do some more blogging, I have some things piled up to write about.

/Karlsson

A user of mysqlimport. Josh Baird, reminded me of a feature which I should have added from teh start, but which was forgotten about. The deal is that when you put a bunch of JSON objects in a file, you have a couple of options on how to do this.

The most obvious is maybe to export as a JSON array of objects, like this:
[
{"id":1, "name": "Geraint Watkins"},
{"id":2, "name": "Kim Wilson"}
]
But this is not what mysqljsoninport supported and this is not how, say, MongoDB exports JSON by default. The reason is that for large amount if data this is cumbersome, as what is in the file is actually one big JSON object containing all the data. This is difficult to parse, requires that a lot of data is read and that the object in whole is kept in memory, unless some clever processing is done. And if we are clever, this is still not effective. Rather, what was supported by mysqljsonimport and how MongoDB exports to JSON is as multiple objects without separators, i.e. you read an object, processit, and then you read some optional blankspace until you reach another object, like this:

{"id":1, "name": "Geraint Watkins"}
{"id":2, "name": "Kim Wilson"}

The latter is more effective, but often the former is used also. So mysqljsonimport now supports both formats, and mysqlexport can optionally export as a single JSON array of objects in a file.

Download the most recent version from Sourcefore: mysqlimport 1.5 and mysqlexport 1.2

Cheers
/Karlsson

High Availability is one of the hottest topics for MySQL DBAs. As a matter of fact, when we (SkySQL) are called by users and customers, the top two questions for our PS team are:

1. How can I make my MySQL database scalable?
2. How can I make my MySQL database highly available

Since MySQL is the most used online database, these two questions are totally appropriate. Although the two concepts walk together, they should not be confused.

We refer to scalability when we define that a system must sustain a specific workload, measured in different ways – response time, latency, transactions or operations per second to name few, or a combination of them. So, we define the level of scalability we want to achieve and we design a system that can scale to that level.

We refer to high availability when we define that a system must be available, within the boundaries of the scalability mentioned above, also in case of malfunctions of one or more of its components. The latter, as many of you know, is called fault tolerance.

Developers and administrators tend to confuse scalability and availability and, even worse, approach the availability and the scalability of their systems all in once. This is wrong for the simple reason that the techniques to adopt to achieve high availability are different to the once to achieve scalability. What is true though, is that these techniques must be ultimately work together and a good system design is a constant reiteration of these techniques. up to the fine tuning of all the aspects that are linked together.

At SkySQL, one of the main goals is to design with our customers scalable and highly available systems. I have seen many presentations on the HA subject and I think most of them are really good – see the one that Henrik has prepared, for example, highly recommended – but I wanted to give you our perspective and show you the various options that we have investigated at SkySQL and that we evaluate for community users and for our customers. This is the reason why I have created this presentation, HA Reloaded.

HA Reloaded is a refresh of all the latest options available for MySQL in terms of HA solutions. I have presented a version of it in Seattle, in San Francisco and in Boston in December, next week I will land in New York City. People who know me, also know that I never do the same presentation twice, so if you are around NYC on Mon 23rd and you want to join us, you will find something completely new, even if you have already attended to my preso.

Here are all the details:

• The slides of my presentation so far are on slideshare, available here.
• The Meetup in NYC will start on Monday 23rd at 7pm – the venue is:
  General Assembly NY
  902 Broadway, 4th Floor
  New York, NY 10010
• The URL to the MySQL Meetup in NYC is here: http://www.databasemonth.com/database/high-availability

 

For those of you who are attending the User Conference – no, I have not submitted this speech. As I said, there are already so many HA presos, I hope Henrik Ingo will do a refresh of his presentation and I am sure that our friends at Percona will talk a lot about their new solutions. But enough of this, please come and poke me at the meetup!

/iz