2

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.

Improve this question

2 Answers 2

Reset to default
2

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.

Improve this answer
7
  • 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
    Commented Jul 10, 2013 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
    Commented Jul 10, 2013 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
    Commented Jul 10, 2013 at 15:51
  • That's what two suggestions are doing. The backup will be called mysql.proc_orig;
    – RolandoMySQLDBA
    Commented Jul 10, 2013 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
    Commented Jul 10, 2013 at 15:57
3

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$
Improve this answer
6
  • how safe is this? Could I end up bringing down all my sites?
    – fregas
    Commented Jul 10, 2013 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
    Commented Jul 10, 2013 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
    Commented Jul 11, 2013 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
    Commented Jul 11, 2013 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
    Commented Jul 12, 2013 at 3:41

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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