MySQL: all stored procedures deleted

MySQLstored-procedures

Started seeing some strange errors from dependent processes this morning. After some digging it appears that the file /mysql/proc.MYD is set to zero length. The file /mysql/db.MYD appears to be truncated as well.

What's even more interesting is that this isn't the case on the replicated slave.

What would cause this?
Why wouldn't it show up on the slave server?
If I copy the files /mysql/proc* from the slave back to the master – will this restore the procedures?

Nearly every file in the /mysql folder was touched at the same time. Some were changed while many were not. My first thought is that it's a failure in the file system but it appears to be localized to this one folder.

EDIT: Pertinent details:

RHEL 5.x
MySQL 5.5

I brought down both MySQL instances and am attempting to get the MySQL folder off of tape. I would run fsck on the volume but the man page has some pretty scary warnings.

Best Answer

The safest course of action is to stop MySQL on both machines and copy the table files you need from the mysql schema from slave to master, saving copies of the files. As long as they are MyISAM tables (not InnoDB), this is a legitimate operation.

If you can't shut down the machines, then connect to both of them and run FLUSH TABLES WITH READ LOCK; wait for the prompt to return, and then leave the connections open (to hold the locks) while copying the files. Then UNLOCK TABLES; to release the global read lock. MyISAM tables are pretty flexible this way... you need to copy the MYD and MYI files for the tables, together in pairs, and you should probably copy the matching .frm files on the off chance that they aren't identical (though they should be)... just save copies of anything you overwrite, and don't try this with InnoDB tables.

This should restore the procedures; you might need to restart the server for them to be recognized by all threads (but I don't think so).

If changes to tables in mysql normally replicate in your setup, that tends to suggest underlying system trouble ... or someone else with access to the system not quite knowing with they are doing ... or a potential security breach, and, unfortunately, none of these things really jumps out to me as more likely than any other, though my instinct is to be "suspicious of malicious."

Since you do have binary logging enabled, you should review the binlogs from around the period of time the timestamps suggest using mysqlbinlog --verbose --base64-output=decode-rows, since even though the slave didn't change its tables, that doesn't mean there couldn't be something interesting in the binlog that changed something else that you haven't yet discovered, or that did not have the same impact on the slave for any number of other reasons, like replicate-ignore-db.

I would check all of the grant table contents along with everything else in the mysql schema. I would also reconfigure the servers with log_warnings = 2, which logs access denied errors, and I might even be tempted to turn on the general query log for a while if your system has the I/O capacity to support that without performance penalty. I would further configure the local machine to forward a copy of its syslog messages to another system with tightly restricted access, and thoroughly audit the firewall... all of this is a little bit alarmist-sounding, but without some other evidence of filesystem problems, that certainly can't be considered the most likely thing to have happened.

Related Solutions

Sql-server – Verify all Stored Procedures

No, there is no built-in way to do this within SQL Server. As Marian stated, you could recompile all of your procedures to be sure they're still valid, but this doesn't prove they'll still work (and don't forget that deferred name resolution makes this validation less than stellar anyway). However you can use other tools such as SQL Server Data Tools (SSDT) to help facilitate unit testing.

Just like changes to your application, if you change your schema, you need to test those changes. How automated you make your unit testing depends on the complexity of your schema, the consuming application(s), and the number of permutations possible for stored procedure outcomes (including both explicit input of different parameters or different parameter values, and implicit input such as time of day, state of the system, specific data at rest, etc.). You have to keep in mind that in some cases you will expect the stored procedure output to stay the same, but in others you will actually expect it to be different.

Some potentially helpful links (a couple culled from a question on SO geared to testing a single stored procedure):

Mysql slow slave

Slave lag is a very common problem, the reason usually, as you have checked, is not the bandwidth or latency in the communication between servers (needed by the IO/Thread), but the other thread involved, the one that applies its own relay logs to the server: the SQL Thread.

Until recently, and with the default configuration, the only way to apply committed transactions from the master on the slave is using a single thread, which means that, if the master has an average concurrency > 1 or if there are some long-running transactions on the master (ALTER TABLE, UPDATEs of multiple rows, etc.), the slave may lag.

There are several things that you can do to avoid this problem:

Change binlog_format = STATEMENT on the master to binlog_format = ROW. This will minimize the latency of query applying, as it will only write the row almost always using the primary key with point writes. The main disadvantage is that if you modify lots of rows per query, the bandwidth used will be higher- also, it is a bit less flexible. On the slave, the binlog is technically not necessary, unless you want to perform point-in-time recovery on that server or you will promote it to a master in the future. In that case, you can disable the binlog to gain some write performance.
If you are using several schemas and MySQL 5.6, you can enable the parallel processing of transaction with the slave_parallel_workers to >1. Please note that only 5.7 will allow real parallelism -5.6 requires changes being done on separate databases in order to take advantage of this feature.
Reduce the write overhead of the slave- set innodb_flush_log_at_trx_commit = 2 (or 0) this will reduce the durability of the slave in case of a crash, but will improve significantly the write rate, and you already have the master providing full durability. There are more things that you can do regarding the write performance of the slave, such as disabling the binlog and the slow log, making sure you are using group commit, or setting the transaction log on its separate drive, etc.

However, there are other things that are very suspicious of causing bad performance (both on the master and the slave), such as:

innodb_thread_concurrency       = 4
innodb_commit_concurrency       = 4
innodb_support_xa               = true
innodb_buffer_pool_size         = 128M

But that requires a deeper understanding of your system, application and hardware.

Best Answer

Related Solutions

Sql-server – Verify all Stored Procedures

Mysql slow slave

Related Question