Novinky ze světa MySQL

MySQL Cluster users - Check out MyQuery 3.3.2 Fri, 10 Sep 2010 11:16:00 +0000 In MyQuery 3.3, where I just released version 3.3.2 which includes some MySQL Cluster NB specific fixes, there is some support for accessing MySQL Cluster / NDB beyond what is available as ndbinfo table and such things. MyQuery is a Windows based MySQL GUI, if you didn't know that already, and just because you run this tool on Windows (it is only supported on Windows), doesn't mean you cannot monitor a Cluster that runs on, say, Linux. Or Windows, should you so wish.There are 2 predefined tools that support MySQL Cluster there, one that uses the ability to predefine a SQL statement that runs in a non-modal dialog and is optionally updated and one that uses the feature that allows a DLL, using a sepcfic MyQuery API, to run as a tool, and in this case, this tool is linked up with the NDBAPI to allow monitoring and a certain amount of control overthe Cluster.Monitoring NDB Cluster memory usageIn MySQL Cluster / NDB 7 and up, there are tables in a schema specific to NDB, called ndbinfo that includes a bunch of tables with NDB specific information.Defining a tool in MyQuery that monitors memory usage, like this tool. by selecting from a table is real easy. The Memory usage monitor is included with MyQuery 3.3 and up, so you can just download MyQuery and then connect to the Cluster and use it, but possibly you want to create tools of your own for other ndbinfo tables or something completely different.Assuming we didn't have the NDB Menory tool already, and wated to create it, this is how you wold do that:First, test the SQL statement you want to use in the MyQuery editor. Always use fully qualified table names (i.e. prefixing them with the database name), unless you have a specific reason not to do this. In this case, we end up with this statement:select * from ndbinfo.memoryusageThe next step is to create the tool. Select the Settings -> Configure user tools menu option, and you will get a dialog that shows all the tools that are currently defined. We are to create a new tool, so click the new button.In the dialog that pops up, give the tool an appropriate name and select a type. In this case, the type is SQL SELECT. Then click OK.A new tool is now created for you, but it will not be saved until we have added some required info, in this case teh SQL statement. In this case, if you have the SQL text in the editor, click the Copy button and select Copy from editor, else just tpe in the SELECT statement you want to run.We probably want to tool to be shown in the Tool menu, and hence be accessible, only when it will work properly, in this case, the ndbinfo plugin is required. For this, select the Enabled for plugin radio button and then select the ndbinfo plugin in the listbox.The last thing we want to do is select the connection to use. There are three options here: Use the connection used to execute normal SQL in MyQuery (Use execute connection), use the connection normally used by MyQuery tools (if MyQuery isn't connected with dual connections, then these two options are the same of course), which is the default (Use main connection) or use a completely separate connection. In our case, just use the default.For tools like this, you would usually not enable the Modal dialog option. This is an option only used in certain special cases.Finally, you might want to add some Help text.That's it, now we have a new tool! Click OK and you are done!Now, in the Tools menu, your newly created tool should appear. Select it, and a non-Modal dialog will appear, just like the one for the NDB Memory info tool. It should look something like this:As you can see, there is the ability to refresh the contents automatically, to refresh every 2 seconds, just check the Autimatic refresh checkbox and set the time to 2 and you are all set. Let me know if you find out any really cools ways of using this feature for tools, and if they are generic enough, I will include them in a future release of MyQuery.Happy Clustering!/Karlsson
Import text file dialog with new features Fri, 10 Sep 2010 11:11:32 +0000 Issue #2134 reported that there are users which do not have the privilege to fire a LOAD DATA command, which rendered HeidiSQL's "import textfile" feature useless for them. So, I recently implemented a client-side parser for CSV files. The new parser does its best to detect the right values while avoiding any slow loop code. On huge files it's slightly slower than a LOAD DATA command processed on the server but I think that's very ok, especially if there is no alternative to a client-side parser.Another new useful thing is the option "Input file contains local formatted numbers", which basically converts the local decimal and thousand separators to those chars MySQL expects. This allows fixing wrong numbers being inserted into your database, e.g. in cases of german numbers of let's say 12.345,6789 which is parsed by the server as 12.345 . The new checkbox option solves this problem for the client parser as well as for the LOAD DATA method. Thanks to bas_vu for the hints!
Converting myisam tables to innodb Fri, 10 Sep 2010 09:03:00 +0000 Why should you convert myisam tables to innodb ? For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb. The simple query which does the trick is Alter table myisam_table_name engine =
Opportunities to talk MariaDB/MySQL in Manila, Philippines Fri, 10 Sep 2010 08:28:01 +0000 I will be in Manila, Philippines from September 13 – 17 2010. If you want to learn more about MariaDB, talk about MySQL, or just talk about open source in general, there are several opportunities for this: Y4iT 2010 (event is September 14-17): Thursday, September 16 2010, 1.50pm, the talk I will give is titled: MariaDB: The new M in LAMP. It was a popular attention grabber during OSCON, where we had a MariaDB booth, and this is a completely new talk, so should be fun to give in front of a huge audience. MySQL/MariaDB Meetup on September 16 2010, 4-6pm, at Bahay ng Alumni. This is a meetup, and will be informal, so good for Q&A. Manila doesn’t have a regular MySQL Meetup group, and we’re hoping to kick something off here. Much thanks to Rom Feria, who helped putting everyone together to have this organised! DevCon at Y4iT, September 15 2010, 4.30-7.30pm, there will be a lightning talk on MariaDB. These talks are not 5 minutes long, but 10 minutes. If you’d like to meetup anytime during the week, don’t hesitate to drop me a message via email (colin[at]montyprogram[dot]com) or @reply me on Twitter – I’m @bytebot. I will have my mobile with me, so don’t hesitate to drop me an SMS if required. Related posts:MySQL at the Sun Tech Days, Philippines Ubuntu 10.04 LTS released, MariaDB 5.1.44/5.2-BETA VM’s available Recently in MariaDB #1
MariaDB going places: Drupal, Zend Framework Fri, 10 Sep 2010 06:56:04 +0000 MariaDB just released MariaDB 5.1.50. Linux, Solaris and Windows (32-bit) are supported. Go give it a twirl. Its also worth noting that today when you go install Drupal, and check out the documentation for requirements, you’ll see that MariaDB is now a recommended option. Via the new news page at Monty Program’s newly designed website, I also found out that MariaDB is now recommended in the documentation for the next release of the Zend Framework. See the changelog. Related posts:MariaDB 5.1.44 released VirtualBox images for MariaDB MariaDB 5.1.42 released!
MariaDB 5.1.50 Released Fri, 10 Sep 2010 06:37:32 +0000 Dear MariaDB users, MariaDB 5.1.50, a branch of the MySQL database which includes all major open source storage engines, myriad bug fixes, and many community patches, has been released. MariaDB: Community developed, feature enhanced, backward compatible. For an overview of what’s new in MariaDB 5.1.42, please see the release notes. Highlights include MariaDB 5.1.50 based on MySQL 5.1.50, includes XtraDB 5.1.49-12, and have some bug fixes in the Aria storage engine. For finer grained details, please view the changelog. MariaDB is available in source and binary form for a variety of platforms and is available from the download pages. Enjoy!
MyQuery 3.3.1 releases - Fixes MyQuery 3.3.0 platform issues Thu, 09 Sep 2010 21:10:00 +0000 I have released MyQuery 3.3.1 now. This is a minor release in many ways, but still significant in others. MyQuery 3.3.0 had issues on platforms where the runtime library I used wasn't installed. Also, there was a minor (but crashing) bug that caused Ndb Monitor to fail. All this, and a few other minor things, are fixed now.I will eventually blog a bit more on the new features in MyQuery 3.3, such as the plugins, the InnoDB Lock monitor and how to easily define your own monitor dialogs with a simple SELECT statement.Til then, download MyQuery 3.3 from Sourceforge.Cheers/KarlssonAnd by the way, I'm sorry that I failed some QA with version 3.3.0, but it's just me coding away here, and I have a limited amount of machines and above all, time.
Oracle Certified MySQL Certifications Thu, 09 Sep 2010 19:50:00 +0000 I may no longer be in charge of MySQL Certification but it does not mean I am not looking out for those of us with those certs. I am still moderating the MySQL Certified Professionals Group on LinkedIn and running the North Texas MySQL USers Group. But I missed Oracle University re-branding the Sun certifications as Oracle certifications earlier this month. This includes the MySQL certifications that were re-branded as Sun. And for those wanting to take the certification exams, they retain the Prometric numbers set up by Sun. But you will find them listed as:310-810 - Oracle Certified MySQL 5.0 Database Administrator Part 1 310-811 - Oracle Certified MySQL 5.0 Database Administrator Part 2 310-812 - Oracle Certified MySQL 5.0 Developer Part 1310-813 - Oracle Certified MySQL 5.0 Developer Part 2310-815 - Oracle Certified MySQL 5.1 Cluster Database Administrator310-814 - Oracle Certified MySQL Associate
The best MySQL SSL Cipher? Thu, 09 Sep 2010 19:29:07 +0000 This is more of a question than me sharing knowledge, but I'm testing the effects of MySQL SSL on replication delay. Interestingly, my tests show faster replication when I enable SSL, versus disable. The slave_compressed_protocol has an effect too, and I get the best result when I use SSL and slave compression in a non-CPU bound test. The question came up, however, of what SSL Cipher I'm using. I'm not specifying one in my CHANGE MASTER statement, so I'm guessing I'm getting the same default cipher as if I connect with the regular mysql command line client with SSL, which is: read more
Easy MySQL: transaction isolation and ACID, the simple explanation Thu, 09 Sep 2010 18:45:50 +0000 Clients often ask what the differences are between the various InnoDB isolation levels, or what ACID means. Here are some simple explanations for those that have not yet read the manual and committed it to memory. READ UNCOMMITTED Every select operates without locks so you don’t get consistency and might have dirt reads, which are potentially earlier versions of data. So, no ACID support here. READ COMMITTED Has consistent reads without locks. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ The InnoDB default isolation level for ACID compliance. All reads within the same transaction will be consistent between each other – ie, the C in ACID. All writes will be durable, etc etc. SERIALIZABLE Same as REPEATABLE READ but MySQL converts regular select statements with preface of LOCK IN SHARED MODE when autocommit is enabled. If it’s disabled then each select is started in a separate transaction which will always make sure that reads are consistent. It also, uh, allows for XA distributed transactions support. You have to be using SERIALIZABLE to correctly use XA transactions. =========================================================================== ATOMICITY All transactions fail or no transactions fail. Basically that if a transaction fails because of a hardware issue, connection issue, etc – that partial changes won’t commit. It’s 100% or 0% operation. CONSISTENCY Data being read by a select is all at the same state. So when you use a transaction you’re getting the most current and consistent data available. This is related to MVCC (multi version concurrency control) ISOLATION Nothing that’s being read is actively being changed by another transaction. Your connection or transaction’s read is not going to be changed by another transaction while you’re dealing with that data. DURABILITY Changes to the database persist – basically that means that if a transaction is committed and the DB fails or server crashes your changes will be there – which is why innodb uses transaction log files (where data is kept before being written to disk. The engine will read the logs on next startup and commit any remaining transactions in the logs that did not make to disk based tables.)

MySQL Cluster users - Check out MyQuery 3.3.2
Fri, 10 Sep 2010 11:16:00 +0000

In MyQuery 3.3, where I just released version 3.3.2 which includes some MySQL Cluster NB specific fixes, there is some support for accessing MySQL Cluster / NDB beyond what is available as ndbinfo table and such things. MyQuery is a Windows based MySQL GUI, if you didn't know that already, and just because you run this tool on Windows (it is only supported on Windows), doesn't mean you cannot monitor a Cluster that runs on, say, Linux. Or Windows, should you so wish.There are 2 predefined tools that support MySQL Cluster there, one that uses the ability to predefine a SQL statement that runs in a non-modal dialog and is optionally updated and one that uses the feature that allows a DLL, using a sepcfic MyQuery API, to run as a tool, and in this case, this tool is linked up with the NDBAPI to allow monitoring and a certain amount of control overthe Cluster.Monitoring NDB Cluster memory usageIn MySQL Cluster / NDB 7 and up, there are tables in a schema specific to NDB, called ndbinfo that includes a bunch of tables with NDB specific information.Defining a tool in MyQuery that monitors memory usage, like this tool. by selecting from a table is real easy. The Memory usage monitor is included with MyQuery 3.3 and up, so you can just download MyQuery and then connect to the Cluster and use it, but possibly you want to create tools of your own for other ndbinfo tables or something completely different.Assuming we didn't have the NDB Menory tool already, and wated to create it, this is how you wold do that:First, test the SQL statement you want to use in the MyQuery editor. Always use fully qualified table names (i.e. prefixing them with the database name), unless you have a specific reason not to do this. In this case, we end up with this statement:select * from ndbinfo.memoryusageThe next step is to create the tool. Select the Settings -> Configure user tools menu option, and you will get a dialog that shows all the tools that are currently defined. We are to create a new tool, so click the new button.In the dialog that pops up, give the tool an appropriate name and select a type. In this case, the type is SQL SELECT. Then click OK.A new tool is now created for you, but it will not be saved until we have added some required info, in this case teh SQL statement. In this case, if you have the SQL text in the editor, click the Copy button and select Copy from editor, else just tpe in the SELECT statement you want to run.We probably want to tool to be shown in the Tool menu, and hence be accessible, only when it will work properly, in this case, the ndbinfo plugin is required. For this, select the Enabled for plugin radio button and then select the ndbinfo plugin in the listbox.The last thing we want to do is select the connection to use. There are three options here: Use the connection used to execute normal SQL in MyQuery (Use execute connection), use the connection normally used by MyQuery tools (if MyQuery isn't connected with dual connections, then these two options are the same of course), which is the default (Use main connection) or use a completely separate connection. In our case, just use the default.For tools like this, you would usually not enable the Modal dialog option. This is an option only used in certain special cases.Finally, you might want to add some Help text.That's it, now we have a new tool! Click OK and you are done!Now, in the Tools menu, your newly created tool should appear. Select it, and a non-Modal dialog will appear, just like the one for the NDB Memory info tool. It should look something like this:As you can see, there is the ability to refresh the contents automatically, to refresh every 2 seconds, just check the Autimatic refresh checkbox and set the time to 2 and you are all set. Let me know if you find out any really cools ways of using this feature for tools, and if they are generic enough, I will include them in a future release of MyQuery.Happy Clustering!/Karlsson

Import text file dialog with new features
Fri, 10 Sep 2010 11:11:32 +0000

Issue #2134 reported that there are users which do not have the privilege to fire a LOAD DATA command, which rendered HeidiSQL's "import textfile" feature useless for them. So, I recently implemented a client-side parser for CSV files. The new parser does its best to detect the right values while avoiding any slow loop code. On huge files it's slightly slower than a LOAD DATA command processed on the server but I think that's very ok, especially if there is no alternative to a client-side parser.Another new useful thing is the option "Input file contains local formatted numbers", which basically converts the local decimal and thousand separators to those chars MySQL expects. This allows fixing wrong numbers being inserted into your database, e.g. in cases of german numbers of let's say 12.345,6789 which is parsed by the server as 12.345 . The new checkbox option solves this problem for the client parser as well as for the LOAD DATA method. Thanks to bas_vu for the hints!

Converting myisam tables to innodb
Fri, 10 Sep 2010 09:03:00 +0000

Why should you convert myisam tables to innodb ? For the perfectly simple reason that innodb tables do not get locked by concurrent selects & inserts. So if you find that your myisam table is suffering for too many locks - due to concurrent selects and inserts, it is time for you to covert the table to innodb. The simple query which does the trick is Alter table myisam_table_name engine =

Opportunities to talk MariaDB/MySQL in Manila, Philippines
Fri, 10 Sep 2010 08:28:01 +0000

I will be in Manila, Philippines from September 13 – 17 2010. If you want to learn more about MariaDB, talk about MySQL, or just talk about open source in general, there are several opportunities for this: Y4iT 2010 (event is September 14-17): Thursday, September 16 2010, 1.50pm, the talk I will give is titled: MariaDB: The new M in LAMP. It was a popular attention grabber during OSCON, where we had a MariaDB booth, and this is a completely new talk, so should be fun to give in front of a huge audience. MySQL/MariaDB Meetup on September 16 2010, 4-6pm, at Bahay ng Alumni. This is a meetup, and will be informal, so good for Q&A. Manila doesn’t have a regular MySQL Meetup group, and we’re hoping to kick something off here. Much thanks to Rom Feria, who helped putting everyone together to have this organised! DevCon at Y4iT, September 15 2010, 4.30-7.30pm, there will be a lightning talk on MariaDB. These talks are not 5 minutes long, but 10 minutes. If you’d like to meetup anytime during the week, don’t hesitate to drop me a message via email (colin[at]montyprogram[dot]com) or @reply me on Twitter – I’m @bytebot. I will have my mobile with me, so don’t hesitate to drop me an SMS if required. Related posts:MySQL at the Sun Tech Days, Philippines Ubuntu 10.04 LTS released, MariaDB 5.1.44/5.2-BETA VM’s available Recently in MariaDB #1

MariaDB going places: Drupal, Zend Framework
Fri, 10 Sep 2010 06:56:04 +0000

MariaDB just released MariaDB 5.1.50. Linux, Solaris and Windows (32-bit) are supported. Go give it a twirl. Its also worth noting that today when you go install Drupal, and check out the documentation for requirements, you’ll see that MariaDB is now a recommended option. Via the new news page at Monty Program’s newly designed website, I also found out that MariaDB is now recommended in the documentation for the next release of the Zend Framework. See the changelog. Related posts:MariaDB 5.1.44 released VirtualBox images for MariaDB MariaDB 5.1.42 released!

MariaDB 5.1.50 Released
Fri, 10 Sep 2010 06:37:32 +0000

Dear MariaDB users, MariaDB 5.1.50, a branch of the MySQL database which includes all major open source storage engines, myriad bug fixes, and many community patches, has been released. MariaDB: Community developed, feature enhanced, backward compatible. For an overview of what’s new in MariaDB 5.1.42, please see the release notes. Highlights include MariaDB 5.1.50 based on MySQL 5.1.50, includes XtraDB 5.1.49-12, and have some bug fixes in the Aria storage engine. For finer grained details, please view the changelog. MariaDB is available in source and binary form for a variety of platforms and is available from the download pages. Enjoy!

MyQuery 3.3.1 releases - Fixes MyQuery 3.3.0 platform issues
Thu, 09 Sep 2010 21:10:00 +0000

I have released MyQuery 3.3.1 now. This is a minor release in many ways, but still significant in others. MyQuery 3.3.0 had issues on platforms where the runtime library I used wasn't installed. Also, there was a minor (but crashing) bug that caused Ndb Monitor to fail. All this, and a few other minor things, are fixed now.I will eventually blog a bit more on the new features in MyQuery 3.3, such as the plugins, the InnoDB Lock monitor and how to easily define your own monitor dialogs with a simple SELECT statement.Til then, download MyQuery 3.3 from Sourceforge.Cheers/KarlssonAnd by the way, I'm sorry that I failed some QA with version 3.3.0, but it's just me coding away here, and I have a limited amount of machines and above all, time.

Oracle Certified MySQL Certifications
Thu, 09 Sep 2010 19:50:00 +0000

I may no longer be in charge of MySQL Certification but it does not mean I am not looking out for those of us with those certs. I am still moderating the MySQL Certified Professionals Group on LinkedIn and running the North Texas MySQL USers Group. But I missed Oracle University re-branding the Sun certifications as Oracle certifications earlier this month. This includes the MySQL certifications that were re-branded as Sun. And for those wanting to take the certification exams, they retain the Prometric numbers set up by Sun. But you will find them listed as:310-810 - Oracle Certified MySQL 5.0 Database Administrator Part 1 310-811 - Oracle Certified MySQL 5.0 Database Administrator Part 2 310-812 - Oracle Certified MySQL 5.0 Developer Part 1310-813 - Oracle Certified MySQL 5.0 Developer Part 2310-815 - Oracle Certified MySQL 5.1 Cluster Database Administrator310-814 - Oracle Certified MySQL Associate

The best MySQL SSL Cipher?
Thu, 09 Sep 2010 19:29:07 +0000

This is more of a question than me sharing knowledge, but I'm testing the effects of MySQL SSL on replication delay. Interestingly, my tests show faster replication when I enable SSL, versus disable. The slave_compressed_protocol has an effect too, and I get the best result when I use SSL and slave compression in a non-CPU bound test. The question came up, however, of what SSL Cipher I'm using. I'm not specifying one in my CHANGE MASTER statement, so I'm guessing I'm getting the same default cipher as if I connect with the regular mysql command line client with SSL, which is: read more

Easy MySQL: transaction isolation and ACID, the simple explanation
Thu, 09 Sep 2010 18:45:50 +0000

Clients often ask what the differences are between the various InnoDB isolation levels, or what ACID means. Here are some simple explanations for those that have not yet read the manual and committed it to memory. READ UNCOMMITTED Every select operates without locks so you don’t get consistency and might have dirt reads, which are potentially earlier versions of data. So, no ACID support here. READ COMMITTED Has consistent reads without locks. Each consistent read, even within the same transaction, sets and reads its own fresh snapshot. REPEATABLE READ The InnoDB default isolation level for ACID compliance. All reads within the same transaction will be consistent between each other – ie, the C in ACID. All writes will be durable, etc etc. SERIALIZABLE Same as REPEATABLE READ but MySQL converts regular select statements with preface of LOCK IN SHARED MODE when autocommit is enabled. If it’s disabled then each select is started in a separate transaction which will always make sure that reads are consistent. It also, uh, allows for XA distributed transactions support. You have to be using SERIALIZABLE to correctly use XA transactions. =========================================================================== ATOMICITY All transactions fail or no transactions fail. Basically that if a transaction fails because of a hardware issue, connection issue, etc – that partial changes won’t commit. It’s 100% or 0% operation. CONSISTENCY Data being read by a select is all at the same state. So when you use a transaction you’re getting the most current and consistent data available. This is related to MVCC (multi version concurrency control) ISOLATION Nothing that’s being read is actively being changed by another transaction. Your connection or transaction’s read is not going to be changed by another transaction while you’re dealing with that data. DURABILITY Changes to the database persist – basically that means that if a transaction is committed and the DB fails or server crashes your changes will be there – which is why innodb uses transaction log files (where data is kept before being written to disk. The engine will read the logs on next startup and commit any remaining transactions in the logs that did not make to disk based tables.)

Nabídka školení

Školení a konzultace

Novinky ze světa MySQL

Nabídka školení