MySQL Error - Use mysql_upgrade

Mysql database has issues and the log file contained following error codes

150924 14:11:17 [ERROR] Missing system table mysql.proxies_priv; please run mysql_upgrade to create it
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_current' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_history' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'events_waits_history_long' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_consumers' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_instruments' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'setup_timers' has the wrong structure
150924 14:11:17 [ERROR] Native table 'performance_schema'.'performance_timers' has the wrong structure

Even though the service shows up and running no database interactions were possible. After searching for 'mysql_upgrade' I was able to find out that this is due to MySQL schema storage is broken. These reasons may be:

• You have broken the database information_schema
• File system corrupted or some bugs in the file system damaged the database.
• MySQL internals broke the schema database due to a bug in MySQL (maybe nobody encountered it before).

[Ref: http://stackoverflow.com/questions/6288103/native-table-performance-schema-has-the-wrong-structure ]

All references were guided to run mysql_upgrade as the root.

mysql_upgrade -u root -p

*Please note that the root user here is the mysql root user and the password, not the system root








Then the system will prompt for the database root user's password.

When the mysql_upgrate executed, it will check for the compatibility of the databases and tables with the current MySQL version and repair if inconsistency is available. After successful command run,  you need to restart the mysql service. Also check the log file for any errors (/var/log/mysqld.log)

Good References:

* https://dev.mysql.com/doc/refman/5.5/en/mysql-upgrade.html

*http://serverfault.com/questions/527422/mysql-upgrade-is-failing-with-no-real-reason-given

NSUPDATE Issues with TSIG

We planned to enable auto-dnssec maintain in the DNS server to allow dynamic updates with DNSSEC. In the process, it is required to implement dns update with TSIG key. We implemented the key pair with following command.

 #dnssec-keygen -r /dev/urandom -a HMAC-MD5 -b 512 -n HOST updatekey.mydomian.lk

It generated following key pair.
Kupdatekey.mydomain.lk.+157+26933.key
Kupdatekey.mydomain.lk.+157+26933.private

and the key pair was included in /var/named as the files can be accessed by named user. The permissions were set to rw by the user named.

The pre-shared key were included in the name.conf (locate in /etc in our case) as follows;

key "updatekey.mydomian.lk." {
        algorithm       hmac-md5;
        secret  "********************************************";
};
where the hidden part is taken from the Kupdatekey.mydomian.private


In the zone section in the named.conf file, the key name was configured as the allows key for zone update;

zone mydomain.lk{
         type master;
         file "mydomain.file";
         .... . . . .
         allow-update {  key updatekey.mydomain.lk.; };
};


Once we need to update the DNS entries in the zone 'mydomain.lk' we tried the following command from the localhost.

nsupdate -k <key_file.private>
>server <serve_ip>
>zone mydomain.lk
>update add/delete <update query>
>send

If there is an issue, the error will be prompted else DNS entries will be updated and if  the zone if configured with
        auto-dnssec maintain;
        dnssec-secure-to-insecure yes;
        key-directory "/var/named/keys"; the new updates will be propagated with dnssec signatures and as IXFR updates to secondaries.

Issues We Found and Solutions
In the initial stage, when generating the key pair we used the domain as the name for the shared key file and the key name given in the named.conf was different to the key file name.
i.e. file generated was Kmydomian.lk.key and Kmydomain.lk.private
and in the named.conf the shared key name was  updatekey.mydomian.lk

We followed the same steps as above the only differences were, the share key name given in the named.conf is different to the Key name;

e.g.
Named conf parameters
key "updatekey.mydomian.lk." {
        algorithm       hmac-md5;
        secret  "**********************************************";
};
Command given for nsupdate
nsupdate -k Kmydomain.lk.private 
>


Once we entered the data for nsupdate, once we confirm the changes with send, it gave the error
TSIG error with server: tsig indicates error

But most interestingly when we used the nsupdate with -y and the key name and pre-shared  string in the named.conf it worked fine.

**Later we understood that the key name should be same as the string that we provided in generating the key with dnssec-keygen -H ..... etc. The key name and the file id should be identical.

Other than that we did not encountered any other issues and we are ready with DNSSEC auto-dnssec maintain with IXFR.

Good reference for TSIG and nsupdate
http://www.crypt.gen.nz/papers/dns_security_1.html