mysql upgrade (5.0.45 to 5.1.42) error

Question

I am getting following error when we are upgrading 5.0.45 to 5.1.42. Do I need to use mysql_upgrade or mysqlcheck ?

Caused by: java.sql.SQLException: Column count of mysql.proc is wrong. Expected 20, found 16. Created with MySQL 50045, now running 50142. Please use mysql_upgrade to fix this error.

    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:1055)
    at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:956)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3515)
    at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3447)
    at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:1951)
    at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2101)
    at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2554)
    at com.mysql.jdbc.PreparedStatement.executeInternal(PreparedStatement.java:1761)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:2046)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1964)
    at com.mysql.jdbc.PreparedStatement.executeUpdate(PreparedStatement.java:1949)
    at org.apache.commons.dbcp.DelegatingPreparedStatement.executeUpdate(DelegatingPreparedStatement.java:102)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:792)
    at org.springframework.jdbc.core.JdbcTemplate$2.doInPreparedStatement(JdbcTemplate.java:1)
    at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:586)

Answer 1

If you're running on a XAMPP setup on ubuntu, navigate to:

/opt/lampp/bin

Then run

./mysql_upgrade

It should run a check through all your existing databases, at the end it may give you this error message:

Could not create the upgrade info file '/opt/lampp/var/mysql/mysql_upgrade_info' in the MariaDB Servers datadir, errno: 13

Not to worry, you can now import your db.sql script without errors.

In case you want to access your MariaDB shell, from the same location:

If you haven't set up a password:

./mysql -u root

If you've set up a password:

./mysql -u root -p

Answer 2

in mysql 5.1 4 additional columns were added for procedures. Seems that somehow your upgrade skipped that step and now you are getting error while importing data.

MySQL upgrade should help you solve that problem.