Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

Our host upgraded our mysql install without us knowing from roughly 5.1 to 5.5.

This caused a problem because the table definition of mysql.proc is different as discussed here:

http://bugs.mysql.com/bug.php?id=50183

Basically, our stored procedure won't run because one column changed (comment) between 5.1 and 5.5 My question is, can I safely run this command to fix the problem:

alter table mysql.proc modify comment text;

I don't want to run mysql_upgrade as that will lock every table in the database on our production server.

share|improve this question
add comment

2 Answers

up vote 1 down vote accepted

Here is a suggestion: do a bait-and-switch.

ALTER TABLE mysql.proc RENAME mysql.proc_orig;
CREATE TABLE mysql.proc LIKE mysql.proc_orig;
ALTER TABLE mysql.proc MODIFY comment TEXT;
INSERT INTO mysql.proc SELECT * FROM mysql.proc_orig;
FLUSH TABLES;

or

CREATE TABLE mysql.proc_orig LIKE mysql.proc;
ALTER TABLE mysql.proc_orig MODIFY comment TEXT;
INSERT INTO mysql.proc_orig SELECT * FROM mysql.proc;
ALTER TABLE mysql.proc RENAME mysql.proc_bckp;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;
ALTER TABLE mysql.proc_bckp RENAME mysql.proc_orig;
FLUSH TABLES;

If the Stored Procedures work, you can drop mysql.proc_orig or keep it as a backup.

If not, undo everything as follows:

DROP TABLE mysql.proc;
ALTER TABLE mysql.proc_orig RENAME mysql.proc;

Give it a Try !!!

CAVEAT : The table INFORMATION_SCHEMA.ROUTINES for MySQL 5.5 should have ROUTINE_COMMENT as LONGTEXT. Make sure the contents of mysql.proc and INFORMATION_SCHEMA.ROUTINES match.

Since you cannot manipulate INFORMATION_SCHEMA.ROUTINES, If you want to feel safe about this, just restart MySQL.

share|improve this answer
    
This seems as drastic as what I'm proposing. My concern is will I corrupt anything in the process or are mysql tables just like any other table? –  fregas Jul 10 '13 at 15:42
    
mysql.proc is a MyISAM table. You can manipulate it as a MyISAM tables. Switching the table around is faster that locking it and doing DDL. –  RolandoMySQLDBA Jul 10 '13 at 15:45
    
Would it be better to just make a backup of the table and then change the original: CREATE TABLE mysql.proc_bak LIKE mysql.proc; INSERT INTO mysql.proc_bak SELECT * FROM mysql.proc; ALTER TABLE mysql.proc MODIFY comment TEXT; –  fregas Jul 10 '13 at 15:51
    
That's what two suggestions are doing. The backup will be called mysql.proc_orig; –  RolandoMySQLDBA Jul 10 '13 at 15:52
    
Sure the result is the same, but what I'm proposing doesn't make the mysql.proc table disappear for a second between when the original is renamed for a backup and when the new version is created. –  fregas Jul 10 '13 at 15:57
show 2 more comments

The mysql_upgrade utility has an --upgrade-system-tables (only) option.

I tested it on a local machine that had already been done, but judging from the output and the documentation, it seems to be what you're looking for.

Of course, be sure you use the version of mysql_upgrade that exactly matches whatever version they have upgraded you to (e.g., 5.5.30).

sqlbot@dev:/usr/local/mysql$ bin/mysql_upgrade --upgrade-system-tables
Looking for 'mysql' as: bin/mysql
The --upgrade-system-tables option was used, databases won't be touched.
This installation of MySQL is already upgraded to 5.5.30, use --force if you still need to run mysql_upgrade
sqlbot@dev:/usr/local/mysql$
share|improve this answer
    
how safe is this? Could I end up bringing down all my sites? –  fregas Jul 10 '13 at 19:22
1  
There's always the potential for something to go wrong during an upgrade, but this should at least be no less safe than running the normal mysql_upgrade on the full system (which always needs to be done after an upgrade, and you will need to do that eventually to avoid problems down the road), but nothing is 100% safe. –  Michael - sqlbot Jul 10 '13 at 20:59
    
I am giving this +1 because mysql_upgrade is the more standard and stable way to do this than my answer. –  RolandoMySQLDBA Jul 11 '13 at 15:13
    
I will probably eventually do the mysql_upgrade after hours, but I want to know what the risks are to a production server first. –  fregas Jul 11 '13 at 15:18
    
You have already been exposed to substantial risk by your service provider performing an incomplete upgrade of your server. That, to me, was wholly unacceptable. The risk of upgrading your privilege tables to the structures your running server expects is arguably lower than what you are risking now, running with tables that are not fully compatible. If I were in your position, and I didn't have a live replication slave, I'd set one up, be prepared to cut the application over to it if needed, yet fully expecting that action to be unnecessary... and then do the upgrade. –  Michael - sqlbot Jul 12 '13 at 3:41
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.