Editing MySQL system table

Question

Hi,

After modifying mysql user table (mysql.user) by adding a new field

ALTER TABLE mysql.user ADD id integer(11) unsigned FIRST;

and restarting the mysql daemon, it is now impossible to log in as any user.

My question is what mecanism does mysql uses that prevents its core system tables from being altered? I can understand that it will not like me if I edit the fields definitions or worse, if I delete some fields, but I find it rather strange that it simply does not accept that I add new fields. What is the problem?

Answer 1

I'm a bit surprised there wasn't already a field named id.

Several ideas come to mind, but they're all speculation:

• The code expects 37 fields in mysql.user; the addition of a 38th caused a data structure overflow
• id is internally special and adding it caused an unexpected condition
• system integrity and sanity checks identified an unexpected change and shut down protectively