Re: Fixing corrupt mysql table
On Sun, Nov 27, 2011 at 05:26:52PM -0500, Robert S wrote:
> I recently rebooted my server and mysql didn't start - it reported password
> problems. I have reset the root password but there are other errors:
>
> # /etc/init.d/mysql start
> Starting MySQL database server: mysqld.
> Checking for corrupt, not cleanly closed and upgrade needing tables..
> ERROR 1045 (28000): Access denied for user 'debian-sys-maint'@'localhost'
> (using password: YES)
As Bob mentioned, this is most likely because you deleted the
debian-sys-maint user. Set up the user e.g. with:
GRANT ALL PRIVILEGES ON *.* TO 'debian-sys-maint'@'localhost'
IDENTIFIED BY 'xxxxxx';
and make sure that the password mentioned in /etc/mysql/debian.cnf is valid.
> When I get into phpmyadmin and go to the "Privileges" tab I get the
> following:
>
> SQL query:
> (SELECT `User`, `Host`, `Select_priv`, `Insert_priv`, `Update_priv`,
> `Delete_priv`, `Create_priv`, `Drop_priv`, `Grant_priv`, `Index_priv`,
> `Alter_priv`, `References_priv`, `Create_tmp_table_priv`,
> `Lock_tables_priv`, `Create_view_priv`, `Show_view_priv`,
> `Create_routine_priv`, `Alter_routine_priv`, `Execute_priv`, `Event_priv`,
> `Trigger_priv`, `Db` FROM `mysql`.`db` WHERE 'contacts' LIKE `Db` AND NOT
> (`Select_priv` = 'N' AND `Insert_priv` = 'N' AND `Update_priv` = 'N' AND
> `Delete_priv` = 'N' AND `Create_priv` = 'N' AND `Drop_priv` = 'N' AND
> `Grant_priv` = 'N' AND `References_priv` = 'N' AND `Create_tmp_table_priv` =
> 'N' AND `Lock_tables_priv` = 'N' AND `Create_view_priv` = 'N' AND
> `Show_view_priv` = 'N' AND `Create_routine_priv` = 'N' AND
> `Alter_routine_priv` = 'N' AND `Execute_priv` = 'N' AND `Event_priv` = 'N'
> AND `Trigger_priv` = 'N')) UNION (SELECT `User`, `Host`, `Select_priv`,
> `Insert_priv`, `Update_priv`, `Delete_priv`, `Create_priv`, `Drop_priv`,
> `Grant_priv`, `Index_priv`, `A[...]
>
> MySQL said:
> #1054 - Unknown column 'Event_priv' in 'field list'
Sounds like phpmyadmin is looking for the EVENT privilege, which only
exists in later versions of MySQL. Are you sure it did not give this
error before? If so, then this is a wild hering chase.
If it is a new error, try forcing a database upgrade using the
"mysql_upgrade" command - this should upgrade the internal MySQL
tables.
> I have run myisamchk and myisamchk -r on all of the databases.
If the debian-sys-maint had not been broken user, this would probably
have been unnecessary, as MySQL (at least in the Debian packaged
version) will do this automagically on startup anyway.
>
> Will it be necessary to rebuild everything from scratch?
>
> I've got a lot of it running temporarily by using the root user and password
> in a few scripts but how can I restore the system?
Backups? At the very least, you should be able to restore the "mysql"
database from a backup. Or you can just re-create the users (with
corresponding passwords and privileges) from the installation
instructions of the apps you use.
--
Karl E. Jorgensen
Reply to: