PlanetMysql.ru – информация о СУБД MySQL

Lets paint the picture:

Scenario part 1 : Migrating a couple thousand stored procedures from database technology X to mysql
Scenario part 2 : Legacy system where the people who wrote it left a long time ago
Scenario part 3 : Developers sure can get real creative and invent all kinds of ways to get data (eg: having a stored proc which formulates a big query using concat after going through a bunch of conditions (fair enough), BUT the parts making up the different queries are stored in a table on a database rather than within the stored proc itself) … talk about KIS – Keep it simple!!
Scenario part 4 : This stored proc references 18 tables, 4 views, and another two stored procedures on 5 databases

Now close your eyes and try to imagine that for a few seconds, nah kidding don’t want you to hurt yourself.

I wonder, who’s gonna cover my health insurance if i go crazy? :)

mysql 02:55:47 DEV > call storedprocblahbla(‘I’,'am’,'going’,'crazy’);
ERROR 1052 (23000): Column ‘state_of_mind’ in field list is ambiguous

Sure thats REALLY REALLY helpful thanks :), you know what, lets just mysqldump -d -R -B db1 db2 db3 db4 db5 > /wherever/you/like and grep for column ‘state_of_mind’.

I love a challenge but facing migration of so many stored procs I’d really love to have something “GOOD” to debug them with.

The solutions I found were:
1. Illatis eclipse plugin for $40
works on linux, mac and windows but does show me a lot of:
“An error has occurred when activating this view
com/illatis/parser/lib/k”
2. mydebugger for $50
works on linux and mac via wine
3. dbForge for MySQL for $50
works only on windows due to .net framework (kinda crap because I `hate` using the former)

any other ideas are very welcome, especially opensource ones but I can’t find anything.

I can always try setting up debugging using GDB but would rather try to avoid that.

Yes, you read the title correctly — there are three editions of MySQL available, according to http://www.mysql.com/products/enterprise/server.html. Well, that page names two, and then of course there is the community edition….

From the manual page:

MySQL Enterprise Server is available in the following editions:

* MySQL Enterprise Server – Pro is the world’s most popular open source database that enables you to rapidly deliver high performance and scalable Online Transaction Processing (OLTP) applications.
* MySQL Enterprise Server – Advanced is the most comprehensive edition of MySQL. It provides all the benefits of MySQL Enterprise Server Pro and adds horizontal table and index partitioning for improving the performance and management of VLDBs (Very Large Databases).

How is “horizontal table and index partitioning” different from the regular partitioning available in MySQL 5.1?

Those of us that have been around for the past 3 or so years know that there was a point in time where there were two different editions of MySQL available, back when MySQL Enterprise and MySQL Community were actually different. But that experiment was a complete failure, and the code is now the same. MySQL Enterprise does package the software in a way that is not available to the community, specifically the quarterly service pack (QSP) releases. But the actual code….the same.

The pricing page at http://globalspecials.sun.com/store/mysql/ContentTheme/pbPage.categoryEnterprise shows that the Advanced server can be acquired for $3k (Gold) or $5k (Platinum) per year. The fee is worth it for the support MySQL will give you, but why is MySQL muddying the waters by having more “editions”, which very likely are not even different code?

(Special thanks to Aaron Macks for pointing out the existence of mysql-advanced, which was the impetus for this blog post.)

Are you doing some datetime manipulation or maybe you are migrating from some database technology to MySQL or possibly using milliseconds?
Here is an example on how to go about it:

Say you have the following date: MAR 16 2008 09:12:51:893AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893AM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 2008031691251.893000


What if its PM rather than AM
SELECT DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f'); --> 20080316211251.893000


Ok so this is just simple string manipulation where:
%M is the month name
%d is day number
%Y is the year
%h is the hour
%i is the minute
%s is the second
%f is the microsecond
%p is the period: ante or post meridiem
In the DATE_FORMAT part we se a %k which is in 24hr format in order to loose the period.

A more detailed list is found here

Here is a demo:

mysql Tue Jun 15 12:32:37 2010 > CREATE TABLE test.abc(a DECIMAL(17,3)) ENGINE=MYISAM;
Query OK, 0 rows affected (0.03 sec)

mysql Tue Jun 15 12:32:45 2010 > INSERT INTO abc VALUES ( DATE_FORMAT(STR_TO_DATE('MAR 16 2008 09:12:51:893PM','%M %d %Y %h:%i:%s:%f%p'),'%Y%m%d%k%i%s.%f') );
Query OK, 1 row affected (0.01 sec)

mysql Tue Jun 15 12:32:51 2010 > SELECT * FROM abc;
+--------------------+
| a                  |
+--------------------+
| 20080316211251.893 |
+--------------------+
1 row in set (0.00 sec)

mysql Tue Jun 15 12:32:56 2010 > SELECT TIMESTAMP(a) FROM abc;
+----------------------------+
| TIMESTAMP(a)               |
+----------------------------+
| 2008-03-16 21:12:51.893000 |
+----------------------------+
1 row in set (0.00 sec)

I have been upgrading more MySQL database instances recently and have found a few more potential gotchas, which if you are not careful, can potentially be rather nasty. These are not documented explicitly by MySQL, so it may be handy for you to know if you have not come across this type of thing before.

Most of the issues are those related to upgrading MySQL instances which are replicated, either the master servers or the slaves. Some seem specific to the rpm packages I am using (MySQL enterprise or MySQL advanced rpms), though others are not.

Take care upgrading a 5.0 master when you have 5.1 slaves

It is not a good idea to run a mixed major version of mysql in a replicated environment so why would I be doing this? If you work in a replicated environment and have several slaves then it is recommended that you upgrade the slaves first. I work with quite a few slaves so the process of upgrading them all takes longer than you would think. Quite a long time in fact, while different systems are tested and upgraded. Along came a newer version of 5.0 and I thought of upgrading the master which had been giving a few issues, and one of them was resolved in the lastest 5.0.89. At least when using rpm packages, the package upgrade uses /usr/bin/mysql_install_db –rpm –user=mysql as part of the package upgrade procedure. This ensures that the mysql db is up to date but also writes to the binlog if one is configured. running on a master normally this would be the case. Of course these are 5.0 install commands and they are not really understood by the slaves which try to interpret them too. End result. Broken replication and you need to skip several transactions. If you have several slaves this can be rather painful.

Note: this is the cause of replicating the mysql database which I do as it is a good, quick and clean way to distribute GRANT information on to all slaves. As far as I know MySQL does not discourage this though perhaps many sites do not replicate their MySQL database.

If you upgrade a master server from 5.o to 5.1 by running mysql_upgrade after upgrading the binaries you may have a similar issue as the mysql_upgrade script determines that some new columns or tables are needed on the master and so adds them.  Again this gets written to the binlog which is good for point in time recovery on the server itself, but again bad from a replication point of view if the slave does not run the same version of MySQL.

In fact the binlog has these 2 uses: (a) writing changes for point in time recovery, and (b) writing the changes for replication to slaves.  Once could argue that for (a) the changes should not be written to the binlog but I think that is wrong if you have to recover just after an upgrade. So I would suggest that the upgrade changes should probably flagged specially in the binlog, allowing the slave to probably ignore them in the normal situation but also be able to recognise them, allowing you to stop the slave and perform the same binary upgrade, and then continue the upgrade with the new binaries exactly in the way this had been executed on the master. This behaviour should be controlled by a runtime flag which can be dynamically configured.

RPM Installs/Upgrades always start the server

rpm(8) is good but some of the design decisions in the MySQL rpms are questionable. One of these is that the current MySQL rpms are designed to always start when either doing a fresh install or doing an upgrade. Currently it’s not good practice to do rpm -Uvh MySQL-server-advanced-gpl…rpm (5.1 rpm) if you are running a MySQL-server-enterprise-gpl…rpm (5.0 rpm) so normally I stop MySQL, remove the enterprise rpm and install the advanced rpm. That starts the server and the slave on a box which is not completely “stable”. Solution is to add slave-skip-start to /etc/my.cnf but that should not be necessary as immediately after running mysql_upgrade you need to remove the value again.

Also during the upgrade process it is a good thing to avoid external client access so sometimes I also set the bind-address to 127.0.0.1 during the period of the upgrade.  That may not keep clients away if they are running on the local server but helps in many cases.

Conclusions

All this leads me to a simple conclusion: if you can, upgrade one box the slow way and then clone the other slaves from that. Cloning is simple and requires no thought so is a good idea.

The other conclusion based on the first one is that if you can: build a new master. That is clone a slave to make a new master. This will be the new master. Configure it to write it’s own binlogs and then you can move the existing slaves underneath the new one. Once all slaves are underneath the new master (left as the only slave of the original master) then you can simply point clients to the new master instead of the old one. That keeps down time to a minimum and avoids many problems.

As far as I know none of the comments I have made above are in the current 5.1 upgrade documentation. I have opened quite a few tickets requesting the documentation be improved and I guess that will happen slowly. For many people the points I have mentioned may seem irrelevant for their situation but for me they have caused a few problems. If you do not need to worry, then you can skip the documentation, otherwise if this were documented then you would be saved a few tears when least expecting problems.