The world's most popular open source database
This is an aggregated feed of various blogs of Sun Microsystems support staff who work and support MySQL customers and users. There is an RSS feed to which you can subscribe.
MySQL Cluster 6.3.28 and 7.0.9 introduce the MySQL server option --ndb-wait-setup. This makes sure that clients can not connect the SQL Node when no Data Nodes are available within, by default, 15 seconds. When the timeout is reached, and no Data Nodes are available, the NDB storage engine will be marked as unavailable.
The following will appear in the MySQL server error log when --ndb-wait-setup=30 has been set:
[Read More]
[Note] NDB:
The MySQL Enterprise Monitor (MEM) is designed to collect data from installations of MySQL and report back to the service manager so that information can be correlated and displayed in an easy to read form on the dashboard. What does become confusing sometimes for people is that the item displayed on the dashboard gives a hostname, but you have multiple databases running on that host?! How does MEM distinguish between all these installs of MySQL versus the hosts they are running on and how the data is collected for each one?
MEM uses the term 'instance' to identify a single, unique item as part of its system. In collecting and defining data, there are essentially three main instances that identify where any data is coming from in the monitoring system. Each instance has a unique identifying number (uuid) to distinguish it from any other instance of data in the MEM
[Read More]First, the back story. One of the MEM developers asked me today about an interesting problem:
We have graphs in MEM that can be generated against an arbitrary time range in the UI - even for a year or more if the data is not purged. Currently MEM does not do any kind of rolling up of the data (in an RRD style), and pulls graph data from each agent/instance on a 1 minute interval. So if you wanted to pull, for instance, the last 3 months worth of data in to a graph - the server back end basically goes back to the database and requests all of the rows - all ~43,829 of them, oh, and that’s for each series - and then calculate deltas on the server side if need be (we store raw values), and stuffs the data in to a graphing library to draw the graph.
Further, graphs are only of a limited
[Read More]This question came up in Freenode's #mysql today
help | how can i truncate all the tables in my database in one go ?
I have had this question a few times from #mysql and from customers. Truth is, there is no simple or official way to do this in MySQL - some third party utilities let you do this - such as phpMyAmin - but one trick I commonly employ is making use of some SQL queries and the INFORMATION_SCHEMA database to generate the commands I need for me.
Within the INFORMATION_SCHEMA database, there is a table called, oddly enoguh, 'TABLES'.. In 'TABLES' we find columns representing the database and table names - as well as some other interesting information such as the engine type. With the use of the mysql command line tool and 'CONCAT' we can exploit this to generate queries for us.
You can read more about
[Read More]The MySQL General Query Log can be very beneficial as it records all queries sent to the server. However, there are times you want more specific information, which can be hard, if not impossible, to filter from this log.
For instance, say you want to capture only queries that cause an error. The errors are not included in the general query log, so filtering based on that is not possible. However, we can easily capture this information using MySQL Proxy with a simple Lua script.
To implement this, the key is to examine the query status in the returned packet from the server to see if there is an error or not. Should the query status be that of an error, then we have what we want, and all that remains is to output whatever relevant information we’d like to see.
Code-wise, we want to examine the returned packet in function
[Read More]In the newer versions of MySQL Proxy (0.7.2 and newer), it is now possible to create a Windows service for MySQL Proxy due to the inclusion of “mysql-proxy-svc.exe”.
Basically, you could use “mysql-proxy-svc.exe” in lieu of “mysql-proxy.exe” from the command line and you would not notice a difference. However, just invoking this will not create a service for MySQL Proxy. Furthermore, you cannot invoke it with the “–install” option as you can with mysqld.
But, you can create a service for MySQL Proxy using the “sc” command with “mysql-proxy-svc.exe”.
Say I want to create a service that starts automatically when my system is started/rebooted. I also want it to load a certain lua script, say capture-errors.lua. Given this, I would use the following
[Read More]For the DIY folks out there!
You're installing MySQL using the Generic Linux Binary tar ball? If you do, you'll need to create a system user and group using useradd and groupadd. However, you would like to use the standard system IDs. No problem, Red Hat defines their standard system users and groups in their manual respectively as 27 and 27.
Here are the commands to create the system user mysql and its group mysql:
shell> groupadd -r -g 27 mysql
shell> useradd -u 27 -g 27 -r -d /var/lib/mysql -s /bin/bash mysql
To me a LONG would be an integer. MySQL, however, defines it as a synonym for MEDIUMTEXT. So careful porting those projects to or from MySQL!
mysql> CREATE TABLE a (id LONG);
mysql> SHOW CREATE TABLE a\G
..
Create Table: CREATE TABLE `a` (
`id` mediumtext
) ENGINE=MyISAM DEFAULT CHARSET=latin1
A bug has been opened for documenting, but it is actually documented here.
This weekend I've been busy porting MySQL Connector/Python to Python 3.1 in a new experimental branch. It is quite a challenge and I was advised to actually rewrite the whole thing for Py3K. However, after crawling through the TypeErrors and other exceptions I figured I did good with porting the code. It helps to re-factor some ancient, weird lines of code and learns me the new goodies coming with Python v3.1.
Status: examples are somehow working, but not all unittests. So we are like 60% away from having MySQL under Python 3.1.
I must say that I love Python 3.1! Especially the Text and
[Read More]October, 8 I did master-class about catching error in SQL application at PHPConf 2009. Who speaks Russian can get slides here: pdf and odp.
There were several things which were interesting for me during my talk.
First is interaction with audience of different educational (or better to say MySQL practice) level. I should make a note for feature how to solve moments when 1/3 of audience listens carefully and is just right for the talk, another 1/3 knows things I am talking at the moment already and last 1/3 needs explanations of basic things such as what is difference between table-level and row-level
Past weekend we got a customer request asking what the execution numbers shown in the statusbar of MySQL Query Browser really mean. Digging in the source and checking with developers, we got it explained and now hopefully documented for future generations.
Lets execute a SELECT-statement using MySQL Query Browser (in Windows):
SELECT * FROM django_admin_log d;
2664 rows fetched in 0,0659s (0,0016s)
N rows fetched in F sec (Q sec), where
F will
[Read More]Replication channel failover is discussed in the MySQL manual and it is (amazingly) easy to do. When the primary replication channel fails, all you need to do is some SQL queries, do a CHANGE MASTER TO on the new slave, START SLAVE and it should be replicating again.
Lots of MySQL Cluster users have probably already figured out this is also very useful if you need to do a rolling upgrade of your Master MySQL Cluster, or upgrading or rebooting the machine it is running on. For these operations you do not need to completely change the replication channel or move to another slave. It's possible, but why work more if you can do with less?
The MySQL manual describes what to do, but here it is
[Read More]Today I have been updating the MySQL entry on the Python wiki. It wasn't incorrect, but very, very outdated and gave a false impression about MySQL's (or Sun's..) DBMS. It is more inline with the PostgreSQL entry now, and I think also fair. Maybe the Firebird folks might update their wiki page too?
This is a call to other projects out there. If you have a MySQL entry page in your wiki, please be sure to reflect the features that were added during the past years.
In my previous blog post I explained how to enable Python in the MySQL Workbench GRT Shell. This blog explains how to run a simple Python script using Workbench. You don't need to change any preferences to make this work.
Save the following little script to a file called wbscript.py. It will print the tables in the first schema of the first model opened.
import grt
model = grt.root.wb.doc.physicalModels[0]
for tab in model.catalog.schemata[0].tables:
print "Table: `%s`" % (tab.name)
To see the output of the script make sure that:
Yesterday, 7 Oct 2009, MySQL Workbench 5.2.4-alpha was released. What interests me in particular is that Workbench 5.2 brings support for writing plugins using Python. Before it was only possible using LUA.
Workbench comes with GRT (Generic RunTime) shell which allows you to access objects at runtime. It's pretty neat, but how you enable it to use Python? Here is how:
It's a bit of a
[Read More]Today we had a bug report that one of the tests failed. The as-keyword was used in exception handling, which is only supported since Python v2.6.
Currently, MySQL Connector/Python should work with Python v2.4 and greater, but not Python v3.x. The Connector/Python README said we supported v2.3, but that's not the case (anymore) since decorators are used, e.g. @classmethod.
I'll have to pull the trigger, but I think it's safe to say that we're only going to support Python v2.4 and greater. For Python v3 we'll probably have to make an additional branch and release later on. I still have to check what [Read More]
This weekend I'm pushing more code which makes MySQL Connector/Python more and ultimately fully compliant with the Python Database API v2.0 specification, aka PEP 249. I wrote my own unittests for this, but later I stumbled upon already made cases form which I'm stealing some ideas.
shell> ./unittests.py -t pep249
Connection object has close()-method ... ok
Connection object has commit()-method ... ok
Connection object has cursor()-method ... ok
Connection object has rollback()-method ... ok
..
----------------------------------------------------------------------
Ran 45 tests in 0.314s
Tests are great!
Bug reports are very much
[Read More]
Recommended Servers for MySQL