View Full Version : MySQL 5.0
MySQL 5.0 is finally the generally recommended release and of course as an early adopter of new languages and iterations, MySQL will be coming in two weeks time to all the servers.
Proposed Roadmap:
* 11/6/2005 - 11/12/2005: Test it out on the local development boxes
* 11/13/2005 - 11/19/2005: Upgrade to MySQL 5.0 on Vector
* 11/20/2005 - onward: Contingent on it functioning at maximum performance with minimal changes, MySQL 5.0 will trickle down to its sister servers.
New Features:
MySQL 5.0 is closing in on PostgreSQL's haunches with its latest version. New features in 5.0 include:
Stored procedures (http://dev.mysql.com/doc/refman/5.0/en/stored-procedures.html) -- yes, you can embed custom functions within queries now
Views (http://dev.mysql.com/doc/refman/5.0/en/views.html) -- join columns from different tables into its own pseudo-table called a view. It's different from temporary tables in that it's always there even after the MySQL session ends
Triggers (http://dev.mysql.com/doc/refman/5.0/en/triggers.html) -- ability to modify records whenever an event happens, e.g. UPDATE/INSERT/DELETE. They're not just limited to InnoDB now.
Information Schema (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) -- probably won't be too interested in the new representation of metadata, so we'll leave it at that
Archive Storage Engine (http://dev.mysql.com/doc/refman/5.0/en/archive-storage-engine.html) -- keeps records compressed, ideal for large articles, especially when the MySQL disk usage counts towards your quota
And for those yearning to learn more, check out MySQL's dev pages (http://dev.mysql.com/) for in-depth information about the great new features in 5.0.
Building and upgrading to MySQL 5.0 at this time on Vector. The preliminary tests on the development servers have worked flawlessly thus far.
At this time MySQL 5.0 is running on Vector. The upgrade happened without a noticable problem. Downtime did occur for about four minutes as the old MySQL server was brought down for the new one to be installed.
In the event any errors may arise though, all databases were backed up prior to upgrade. Should you have any issue with data integrity in your database, please file a trouble ticket immediately.
Otherwise, everything looks great and will propagate to the other servers next week.
All right folks, it's been nearly 24 hours since MySQL 5.0 so now it's time to recap the aftermath:
Good news, there wasn't a whole lot.
There has been one minor change that impacts Invision Power Board and vBulletin, parsing precedence of SELECT values FROM table_name1, table_name2 [LEFT|INNER|RIGHT|OUTER] JOIN table_name3 [ON|USING] (table_name1.value = table_name2.value) …
Normally the query is evaluated such that the tables in the FROM clause are evaluated first and then a JOIN is performed on the corresponding table, in this case table_name3. To make MySQL 5 more ANSI SQL-compliant, that has been changed so that table_name2 [LEFT|INNER|RIGHT|OUTER] JOIN table_name3 [ON|USING] (table_name1.value = table_name2.value) has the higher precedence in the query and as a result, is evaluated before hand. A quick fix for this change is to force evaluation of the FROM table_name1, table_name2 by surrounding it with parentheses, such that it changes to: FROM (table_name1, table_name2) or you can rewrite the query by switching the place of the two tables, i.e.: FROM table_name2, table_name1. Either modification will work.
All instances of vBulletin and IPB have been patched on Vector. Once MySQL is rolled out onto its siblings next week, those will be automatically patched as well.
Here are the individual fixes:
vBulletin (usercp.php):
line 271:
FROM " . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "subscribethread AS subscribethread
should be modified to:
FROM (" . TABLE_PREFIX . "thread AS thread, " . TABLE_PREFIX . "subscribethread AS subscribethread)
IPB (Boards.php):
line 70:
FROM ibf_forums f, ibf_categories c
should be changed to:
FROM (ibf_forums f, ibf_categories c)
Lastly, if you use PHP-Nuke, make sure you update the driver in config.php to mysql4 and not MySQL (that is for traditional 3.2x support that hasn't been running on the servers for three years now).
PHP-Nuke (config.php):
line 37:
$dbtype = "MySQL";
should be:
$dbtype = "mysql4";
As with IPB and vBulletin, if you use PHP-Nuke and you happen to be using the 3.2x driver support, that too has been automatically updated on Vector. Once MySQL 5.0 is rolled out onto the other servers, that change will be made as well.
osCommerce MS 2.2:
There's an update (http://www.oscommerce.com/about/news,121) from November 12th that incorporates MySQL 5.0 support into osCommerce. All users running osCommerce are urged to upgrade or at least use the patch provided. Upload the patch to the base directory of your osCommerce installation, i.e. the directory with the catalog/ directory in it and the rest of your store front, and run the patch after decompressing it with the command from the shell:
patch -b -p0 < oscommerce-051112.diff
If you do not have shell access or are afraid of running that command, file a trouble ticket to request the update.
Otherwise if you want to patch it manually, the only MySQL 5.0 incompatibility I have run across is with the main page under catalog/index.php
Around line 170, replace all of the information, beginning from //show the products of a specified manufacturer with:
// show the products of a specified manufacturer
if (isset($HTTP_GET_VARS['manufacturers_id'])) {
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only a specific category
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "'";
} else {
// We show them all
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m where p.products_status = '1' and pd.products_id = p.products_id and pd.language_id = '" . (int)$languages_id . "' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['manufacturers_id'] . "'";
}
} else {
// show the products in a given categorie
if (isset($HTTP_GET_VARS['filter_id']) && tep_not_null($HTTP_GET_VARS['filter_id'])) {
// We are asked to show only specific catgeory
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS . " p left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_MANUFACTURERS . " m, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.manufacturers_id = m.manufacturers_id and m.manufacturers_id = '" . (int)$HTTP_GET_VARS['filter_id'] . "' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
} else {
// We show them all
$listing_sql = "select " . $select_column_list . " p.products_id, p.manufacturers_id, p.products_price, p.products_tax_class_id, IF(s.status, s.specials_new_products_price, NULL) as specials_new_products_price, IF(s.status, s.specials_new_products_price, p.products_price) as final_price from " . TABLE_PRODUCTS_DESCRIPTION . " pd, " . TABLE_PRODUCTS . " p left join " . TABLE_MANUFACTURERS . " m on p.manufacturers_id = m.manufacturers_id left join " . TABLE_SPECIALS . " s on p.products_id = s.products_id, " . TABLE_PRODUCTS_TO_CATEGORIES . " p2c where p.products_status = '1' and p.products_id = p2c.products_id and pd.products_id = p2c.products_id and pd.language_id = '" . (int)$languages_id . "' and p2c.categories_id = '" . (int)$current_category_id . "'";
New update, there's a bug with 5.0.15 in the INSERT DELAYED query that causes the MySQL thread processing the query to crash. A patch submitted (http://bugs.mysql.com/bug.php?id=13707) for inclusion with 5.0.16 has been applied against the current 5.0.15 version running on Vector. At this time MySQL appears to be running fine without a crash for the last three hours.
The crash impacted solely those running scripts from the shell that used localhost as the hostname of the MySQL server. For those accessing MySQL from a script on the Web server, e.g. forums, CMS, blog -- those were unaffected by the segfaults as MySQL respawned the crashed thread immediately afterwards.
MySQL 5.0 has been installed on Eigen as of 2:10 PM EST (-0500 GMT). Should this upgrade go without any further issues, other than those already addressed and automatically patched, then MySQL 5.0 will be rolled out onto the remaining servers this weekend.
What file is the vBulletin edit in? I am upgrading to the latest version of vBulletin right now and I'd like to edit the files myself.
What file is the vBulletin edit in? I am upgrading to the latest version of vBulletin right now and I'd like to edit the files myself.
Adding the affected file names might be a good idea. :) The only fix for vBulletin is in usercp.php in the aforementioned line range.
beerinator
11-27-2005, 04:50 PM
I'm guessing that some of the MySQL updates took place today? I've had some issues pop up with many of the features of my phpnuke site.
In order to fix many of them, I had to reverse the change that is posted above. Ending up using MySQL in config.php instead of mysql4. This didn't fix all of the problems that cropped up, but it did fix the majority of the issues. I'm not exactly sure which box I'm on, but my url is http://beerinator.com
I know I'm behind on my updates so I'll work on that and see if it fixes the errors. But I'm just posting to let you know that I had to reverse the change listed above to make most of the blocks and modules on my site work.
MySQL 5.0 has been rolled out on all the servers now. There should be no known outstanding issues at this time. If you encounter anything out of the ordinary, please file a trouble ticket immediately to have the problem resolved.
I'm guessing that some of the MySQL updates took place today? I've had some issues pop up with many of the features of my phpnuke site.
In order to fix many of them, I had to reverse the change that is posted above. Ending up using MySQL in config.php instead of mysql4. This didn't fix all of the problems that cropped up, but it did fix the majority of the issues. I'm not exactly sure which box I'm on, but my url is http://beerinator.com
I know I'm behind on my updates so I'll work on that and see if it fixes the errors. But I'm just posting to let you know that I had to reverse the change listed above to make most of the blocks and modules on my site work.
You're on Kernel. What pages are throwing errors at this time? Using mysql4 instead of mysql as the driver should have fixed the bulk of the problems that I've witnessed personally.
Would you mind if I switched the driver back to mysql4 temporarily and dive a little deeper into the problem?
beerinator
11-27-2005, 04:58 PM
You're on Kernel. What pages are throwing errors at this time? Using mysql4 instead of mysql as the driver should have fixed the bulk of the problems that I've witnessed personally.
Would you mind if I switched the driver back to mysql4 temporarily and dive a little deeper into the problem?
Knock yourself out! :)
I also noticed 14 new tables in my database. I didn't touch them, figured they were part of the new package?
edit: I forgot to answer your first question. Currently if you look at the front page, there is a "event countdown" section at the top, there are two scrolling blocks on the right. All three of these were blank, and they were having "language file" errors (as well as all my admin pages). When I switched the config.php back to MySQL it fixed all the admin page errors and the language errors on two of the three blocks. It appeared to me at first that some files might be missing or database files were gone, but I checked that first and everything seems to be where it should be.
Knock yourself out! :)
I also noticed 14 new tables in my database. I didn't touch them, figured they were part of the new package?
All right I switched the driver to mysql4 in your configuration, and there are two things that look like they're broken. The first is the scrolling list of the last 20 forum messages and the other is the shoutbox. Changing the driver back to 3.23 fixed the shout box :confused:. I changed the code around for the "Last 20 forum posts" ticker under blocks/ from FROM ".$prefix."_bbtopics t, ".$prefix."_bbforums f
to FROM (".$prefix."_bbtopics t, ".$prefix."_bbforums f) There's something busted in the 4.0 driver you're using that is producing some odd results.
If I change the driver to mysql4 with the fix in place, the data for the ticker is properly retrieved, but not stored properly once the time comes to store it to the output variable. Anyway though, head scratching aside, the temporary fix there is to switch back to the 3.23 driver and everything on the front page looks fine now. Let me know if you encounter any further problems.
Edit: Also regarding the 14 new tables, all I see are nuke_* tables, so those would have been created from installing another third-party module for PHP Nuke or upgrading it. Those are unrelated to the MySQL 5.0 upgrade.
beerinator
11-27-2005, 05:34 PM
Wow thanks that was fast.
My phpnuke install is a bit of a frankenstein mess. I dread upgrading simply because it breaks everything and I have to put hours into trying to get back to where I was. I really really should look for something else, but I dread that too. :(
Edit: Also regarding the 14 new tables, all I see are nuke_* tables, so those would have been created from installing another third-party module for PHP Nuke or upgrading it. Those are unrelated to the MySQL 5.0 upgrade.
I just looked, and I was wrong. I don't have 14 new tables, but I do have a new database to choose from (it has the 14 tables I saw) called "information_schema". I haven't seen it before today, but I really wasn't looking for it either.
Thanks for the quick responses!
Wow thanks that was fast.
I just looked, and I was wrong. I don't have 14 new tables, but I do have a new database to choose from (it has the 14 tables I saw) called "information_schema". I haven't seen it before today, but I really wasn't looking for it either.
Thanks for the quick responses!
That's the new metadata database (http://dev.mysql.com/doc/refman/5.0/en/information-schema.html) in MySQL 5.0.
vBulletin® v3.7.2, Copyright ©2000-2009, Jelsoft Enterprises Ltd.