One of Drizzle's great strengths is that it is not afraid to deviate from its MySQL origins. Unfortunately the side-effect of this is that it can make migration from MySQL to Drizzle more difficult. I have previous noted that drizzledump can do the schema migration for you. Now I will go into more details with some other information that may be useful if you are considering migration.
The first thing I should point out is that the default storage engine in Drizzle in InnoDB, MyISAM exists only for temporary tables and we have some other options such as PBXT and BlitzDB. As a kind-of side effect to this, there is no FULLTEXT indexes in Drizzle, so these have to be dropped as part of the migration.
When it comes to integer types we have two, INT and BIGINT. So TINYINT, SMALLINT, etc... need to be changed to INT. We also have no unsigned so an INT UNSIGNED needs to become BIGINT to prevent potential loss of data. Finally INT doesn't have column widths, for example the bit in braces in "INT(11)" so these can go.
There are also several changes to TIME/DATE storage. There is no TIME type in Drizzle, so Drizzledump converts this into an INT of seconds. MySQL accepts a date of 0000-00-00 whereas Drizzle's minimum date is 0001-01-01. So Drizzledump converts 0000-00-00 to NULL. Finally we have no YEAR data type in Drizzle. This should convert to INT using Drizzledump but as I am writing this I have found a bug here which I will fix shortly.
As far as TEXT/BLOB types go there is no tiny/medium/long. This is now just BLOB or TEXT.
We have one single character set in Drizzle, UTF8. So you should make sure data is exported in that character set. Different collations, however, are supported.
When you want to connect up your application to Drizzle, there is good news here. Drizzle supports the MySQL protocol and listens on port 3306! This means, for example, that your PHP app will connect straight to Drizzle thinking it is a MySQL server using the MySQL functions you are familiar with. There is no UNIX socket, so make sure your apps connect to 127.0.0.1 instead of localhost for local connections. There is no username/password by default but you can enable one of the authentication plugin modules if you require them.
The query syntax is pretty much the same as MySQL, there are things we don't have such as stored procedures and triggers. But for the most part this is unchanged.
For what kind of temp tables can MyISAM be used? The implicit ones created during query execution? Or the explicit ones creates by "create temporary table"?
ReplyDeleteDespite its many imperfections and my occasional posts, I am not willing to go without MyISAM. A great way to kill a reporting slave that replicates from a busy OLTP master is to require all reporting queries to put even more load on the InnoDB engine. Similar fun is had when light reporting queries on the master switch from using MyISAM to InnoDB.
While many of these queries can use temp tables some of them require tables that outlive the connection that created them.
I have now fixed that YEAR conversion bug I mentioned in this post, it should be in the 2010-10-11 release.
ReplyDeleteI apologise for not clarifying this, MyISAM can be used for explicit temporary tables (CREATE TEMPORARY TABLE). But you cannot use FULLTEXT indexes on those temporary tables.
ReplyDeleteWe also hope to have temporary tables created during query execution able to use other engines at some point too - the way it's done now (inherited from MySQL) is just a horrible layering and abstraction violation. very, very horrible.
ReplyDeleteLight reporting may be able to use PBXT or BlitzDB if you're using InnoDB as your main engine.
I expect BlitzDB (Tokyo Cabinet based) to be able to handle these sorts of things fairly well.
... and MyISAM could come back... if somebody spends the time to make it work behind our storage engine interface. The problem was it was just jumping back and forth all through the server and not sticking to the interface.
ReplyDelete[...] Hutchings, LinuxJedi, writes a drizzle migration article on his new blog, since joining Rackspace as a Drizzle [...]
ReplyDeleteMysql also accepts 1420-09-00, whereas Drizzle unfortunately doesn't seem to. Why was this changed?
ReplyDeleteI believe it is because there is no day 0 in a calendar month. These changes happened before I joined the Drizzle team.
ReplyDeleteHi!
ReplyDelete"1420-09-00" is not a valid date. MySQL will allow invalid data to be inserted into tables, Drizzle will not. This was a very consciously made decision early on after working with users who had been surprised that the data they had inserted into the database was either invalid or corrupt.
Cheers,
-Brian
Hi!
ReplyDeleteDo you need MyISAM, or do you just need a set of capabilities? For instance we do have blitzdb (http://torum.net/2009/07/blitzdb-primary-key-based-insertion-performance/). I wouldn't call it complete, but it is just a --plugin-add away.
Hacking up a read-only MyISAM would be doable, but someone would have to have the will to want it. I spoke to a group who moved from MyISAM to Innodb for reporting, was it slower?
Yes.
Did it save them time?
Yes, they were no longer dealing with MyISAM corruptions or dealing with its single locking key-cache code.
Cheers,
-Brian
[...] new drizzledump.great job Andrew and Team!. No more dependancy on drizzled/internal/* files.hats off from this freelance porter. I see [...]
ReplyDelete[...] from a MySQL database. For those who don’t know about this, the inimitable Andrew Hutchings enhanced drizzledump so that one could transfer data from MySQL directly to Drizzle without needing any intermediate [...]
ReplyDelete[...] from a MySQL database. For those who don’t know about this, the inimitable Andrew Hutchings enhanced drizzledump so that one could transfer data from MySQL directly to Drizzle without needing any intermediate [...]
ReplyDelete[...] Easy migration from MySQL systems via the Drizzledump tool. [...]
ReplyDelete[...] в виде отдельного плагина. Функции по упрощению миграции с MySQL поддерживаются в штатной утилите drizzledump. Для [...]
ReplyDelete[...] в виде отдельного плагина. Функции по упрощению миграции с MySQL поддерживаются в штатной утилите drizzledump. Для [...]
ReplyDelete[...] Easy migration from MySQL systems via the Drizzledump tool. [...]
ReplyDelete[...] в виде отдельного плагина. Функции по упрощению миграции с MySQL поддерживаются в штатной утилите drizzledump. Для [...]
ReplyDeleteNice post about Migrating from MySQL to Drizzle « LinuxJedi's /dev/null. I am very impressed with the time and effort you have put into writing this story. I will give you a link on my social media blog. All the best!
ReplyDelete[...] of a server to have more interesting tests : ) This will be useful for scenarios like testing Drizzledump migration as we can feed in one (or more) MySQL servers and a Drizzle tree and make sure we can migrate data [...]
ReplyDelete[...] of a server to have more interesting tests : ) This will be useful for scenarios like testing Drizzledump migration as we can feed in one (or more) MySQL servers and a Drizzle tree and make sure we can migrate data [...]
ReplyDelete