Drizzle supports one character set (unless you include binary) which is UTF8. It is the character set used by most of the web and supporting many different character sets can lead to complications. That is not to say that there are not advantages of supporting many different types of character sets like MySQL does, but more care is needed when using them.
As an example of this, a new Drizzle user came online today saying that drizzledump's MySQL to Drizzle conversion was turning 'è' to 'è'. When drizzledump connects to a MySQL server it sets the connection to UTF8 so that the dump output is compatible with Drizzle. After a bit of discussion it was discovered that the user's table was latin1 and connection was latin1 (PHP does this by default) but they were storing and retrieving UTF8 data. Essentially their data was getting mangled but it happened to work. The problem came when telling MySQL to export this data as UTF8, it was effectively doing a double UTF8 conversion of the data.
With this in mind we have added a new option to drizzledump so that it stops setting the character set for the connection in these situations, '--my-data-is-mangled'.
The /dev/null ramblings of a Linux Jedi
Showing posts with label migration. Show all posts
Showing posts with label migration. Show all posts
Friday, 28 January 2011
Friday, 1 October 2010
Migrating from MySQL to Drizzle
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.
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.
Wednesday, 29 September 2010
Drizzle7 Beta Released! (now with MySQL migration!)
Today is a big day for us working on the Drizzle project. Our first official Beta! We have come a long way since the original MySQL 6.0 fork and we couldn't have done it without the many community contributors involved, so many thanks to everyone who has helped us to get here (you know who you are).
One feature I have been working on recently is a MySQL migration tool. This is basically a large modification to drizzledump which can do two extra things:
Firstly, it will automatically detect whether it is connecting to a MySQL or Drizzle server. When connecting to a MySQL server it will automatically convert the table structures and data to create a Drizzle compatible dump file.
On top of this there are new options so that drizzledump can connect straight to a Drizzle server at the same time as a MySQL server and pipe the data over, converting on-the-fly, without any intermediate dump file!
To help read up about how to use this, another new feature is the integration of Sphinx Documentation Generator. We are still in the process of writing documentation but we already have information on how to use the drizzledump migration options available.
The Drizzle server can already talk the MySQL protocol, so in many cases there will be little to no application modifications needed to switch from MySQL to Drizzle, you can even still use libmysqlclient!
Please test this and let us know what you think, we are always open to feedback.
One feature I have been working on recently is a MySQL migration tool. This is basically a large modification to drizzledump which can do two extra things:
Firstly, it will automatically detect whether it is connecting to a MySQL or Drizzle server. When connecting to a MySQL server it will automatically convert the table structures and data to create a Drizzle compatible dump file.
On top of this there are new options so that drizzledump can connect straight to a Drizzle server at the same time as a MySQL server and pipe the data over, converting on-the-fly, without any intermediate dump file!
To help read up about how to use this, another new feature is the integration of Sphinx Documentation Generator. We are still in the process of writing documentation but we already have information on how to use the drizzledump migration options available.
The Drizzle server can already talk the MySQL protocol, so in many cases there will be little to no application modifications needed to switch from MySQL to Drizzle, you can even still use libmysqlclient!
Please test this and let us know what you think, we are always open to feedback.
Subscribe to:
Posts (Atom)