Capturing Erroneous Queries with MySQL Proxy

The MySQL General Query Log can be very beneficial as it records all queries sent to the server. However, there are times you want more specific information, which can be hard, if not impossible, to filter from this log.

For instance, say you want to capture only queries that cause an error. The errors are not included in the general query log, so filtering based on that is not possible. However, we can easily capture this information using MySQL Proxy with a simple Lua script.

To implement this, the key is to examine the query status in the returned packet from the server to see if there is an error or not. Should the query status be that of an error, then we have what we want, and all that remains is to output whatever relevant information we’d like to see.

Code-wise, we want to examine the returned packet in function read_query_result(), and then check the query status to see if it returns an error (i.e., proxy.MYSQLD_PACKET_ERR). So, we have the following:

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    -- here we have query that returns an error
    print(...)
  end
end

And that is basically all there is to it.

Here is the full script, so you can see it in its entirety:

function read_query( packet )		
  if string.byte(packet) == proxy.COM_QUERY then
    proxy.queries:append(1, packet, {resultset_is_needed = true} )
    return proxy.PROXY_SEND_QUERY
  end
end

function read_query_result (inj)
  local res = assert(inj.resultset)	
  if res.query_status == proxy.MYSQLD_PACKET_ERR then
    local out_string = 	
      "Time Stamp = " .. os.date('%Y-%m-%d %H:%M:%S') .. "n" ..
      "Query      = " .. inj.query .. "n" ..
      "Error Code = " .. res.raw:byte(2)+(res.raw:byte(3)*256) .. "n" ..
      "SQL State  = " .. string.format("%q", res.raw:sub(5, 9)) .. "n" ..
      "Err Msg    = " .. string.format("%q", res.raw:sub(10)) .. "n" ..
      "Default DB = " .. proxy.connection.client.default_db .. "n" ..
      "Username   = " .. proxy.connection.client.username .. "n" ..
      "Address    = " .. proxy.connection.client.src.name .. "n" ..
      "Thread ID  = " .. proxy.connection.server.thread_id .. "n"
    print(out_string .. "n")
  end
end

Notes:

  1. In function read_query(), when I append the query, I also append the “{resultset_is_needed = true}” flag, as you need to set this to “true” in order to get the resultset returned to the proxy.
  2. In function read_query_result(), I assign all information to a variable, and then simply “print()” the variable. This does add a line of code, but in the end, it makes it easier to write this to an output file instead of to the terminal (or to both), if you desire.

Setting up MySQL Proxy as a Service on Windows

In the newer versions of MySQL Proxy (0.7.2 and newer), it is now possible to create a Windows service for MySQL Proxy due to the inclusion of “mysql-proxy-svc.exe”.

Basically, you could use “mysql-proxy-svc.exe” in lieu of “mysql-proxy.exe” from the command line and you would not notice a difference. However, just invoking this will not create a service for MySQL Proxy. Furthermore, you cannot invoke it with the “–install” option as you can with mysqld.

But, you can create a service for MySQL Proxy using the “sc” command with “mysql-proxy-svc.exe”.

Say I want to create a service that starts automatically when my system is started/rebooted. I also want it to load a certain lua script, say capture-errors.lua. Given this, I would use the following command:

sc create "proxy" DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 
--proxy-lua-script=capture-errors.lua"

Here is the session from my console:

C:Program FilesMySQLmysql-proxy-0.7.2bin>sc create "proxy" 
DisplayName= "MySQL Proxy" start= "auto" 
binPath= "C:Program FilesMySQLmysql-proxy-0.7.2binmysql-proxy-svc.exe 
--proxy-lua-script=capture-errors.lua"
[SC] CreateService SUCCESS

Once you have this set-up, you can easily start-up or shutdown the proxy. For instance, the following two commands will start and stop the proxy, respectively:

net start proxy
net stop proxy

Now, a couple of notes about the “sc create” statement:

  1. This will create the service with the name of “proxy”.
  2. I used a “start” type of “auto”. This will mean it will start up automatically when you reboot your system. If you remove that option, it will default to “manual”.
  3. You need a space after each equal sign (=).
  4. I had to use double-quotes (“) around each parameter value, as well as the service name, otherwise it wouldn’t create the service properly on my machine.
  5. The lua script, capture-errors.lua, is located in the proxy basedir (C:Program FilesMySQLmysql-proxy-0.7.2) and so it can be read from that location without a full path. If you have this in a different location, you would need to specify the full path.

If for some reason you want to delete this service, you can issue the following command (where “proxy” is the name of the service that you gave it using the above command):

sc delete proxy

And for more details on “sc”, please refer to the following Microsoft KB article:

http://support.microsoft.com/kb/251192

How to Recover Data using the InnoDB Recovery Tool

As you may or may not know, there is a tool called the InnoDB Recovery Tool which can allow you to recover data from InnoDB tables that you cannot otherwise get the data from.

“This set of tools could be used to check InnoDB tablespaces and to recover data from damaged tablespaces or from dropped/truncated InnoDB tables.”

http://code.google.com/p/innodb-tools/

This is a very handy tool, however, the documentation on how to use it is a bit limited when it comes to actually recovering the data, so I thought I’d post a step-by-step tutorial on how to use this tool.

1. Download the InnoDB Recovery Tool (latest version is 0.3)

2. Unpack the download to the location of your choice

3. Create your table_defs.h file using the create_defs.pl script. Note the below creates a table_defs.h file based on only one table, t1, from the database named test:

cd innodb-recovery-tool-0.3/
./create_defs.pl --user=root --password=mysql --db=test --table=t1 > table_defs.h

4. Copy the newly created table_defs.h fle to the innodb-recovery-tool-0.3/include/ directory.

5. Now is time to build/compile the InnoDB Recovery Tool

cd innodb-recovery-tool-0.3/mysql-source/
./configure
cd ..
make

At this point, you’re almost ready to begin to recover the data. However, let me point out a couple items at this stage. The InnoDB Recovery Tool documentation says you can use the page_parser program to split up the tablespace. Since this page_parser program is now created (after compilation and make), you can use this to break apart the tablespace. However, in my case, I did this, but the page_parser didn’t work as well as I expected. This could be due to the corruption in the tablespace files (ibdata1 and ibdata2). So, I simply tried to recover based off the entire ibdata files. I found that I recovered much more data by just running the recovery against the ibdata files, rather than against the split-up pages. If you opt for this method, then you can skip steps 6, 7, and 8.

6. Should you want to use the page_parser, here is how you run it:

cd innodb-recovery-tool-0.3/
./page_parser -f /home/chris/Desktop/test/ibdata1 /home/chris/Desktop/test/ibdata2 -5

Note that the -f indicates the file(s) to use, and the -5 indicates the ibdata files are from MySQL version 5.0.

7. Should you use the page_parser, you must also load the ibdata file(s) and capture the InnoDB tablespace monitor output. This part is described on the InnoDB Tools how-to.

8. After running the above, you’ll want to capture all of the primary key index positions for each table you want to recover. For instance, you might see something like “0 135” for the index position of a primary key. This will correspond to the folder named “0-135” that is created by page_parser.

9. Now you are ready to recover the data for the first table.

(Note that you could create a table_defs.h file based on all of the tables you want to recover. And then you can recover all of the data at once, however, the problem with this is the data is all mis-matched into one big file. So you might have a row for one table followed by a row from another table. If you’re good with sed/awk, this might not be a problem for you, as you can then split it apart. However, it might be easier to create a single table_defs.h file for each table, and then recover the data table-by-table.)

If you want to recover the data based on the page_parser output, then you would use the following command:

./constraints_parser -f /home/chris/Desktop/innodb-recovery-0.3/pages-1239037839/0-135/50-00000050.page -5 -V

Note that the -V is for verbose mode. It is best to use this initially to make sure the data being recovered looks to be correct. Once you’ve verified it looks correct, then simply run the above command without the -V and pipe the output to a text file.

Should you not want to use the page_parser, and just run constraints_parser directly against the ibdata file(s), then issue the following command instead:

./constraints_parser -f /home/chris/Desktop/test/ibdata1 /home/chris/Desktop/test/ibdata2 -5 > output.txt

As for the recovered data itself, note that this data is displayed in a tab-delimited text format that the InnoDB Recovery tool dumps it in (default, not configurable yet).

For instance, here is a sample of data recovered for the t1 table:

t1 128992703 84118144 301989888 224000 33558272 268435456 ""
t1 0 0 34796032 0 530 838926338 ""
t1 1886545261 268455808 256 497 880803840 2949392 ""
t1 1398034253 1953654117 1952672116 2037609569 1952801647 1970173042 ""
t1 402667648 755047491 1431524431 1296388657 825372977 825308725 "5"
t1 536884352 755050563 1431524431 1296388658 842150450 842162531 "t"
t1 671103872 755053635 1431524431 1296388663 926365495 926365495 "77"
t1 524288 0 755056707 1431524431 1296388705 1668573558 ""
t1 524288 0 755059779 1431524431 1296388705 1668573558 ""
t1 524288 0 755062851 1431524431 1296388705 1668573558 ""
t1 525312 0 755065923 1431524431 1296388705 1668573558 ""
t1 524288 0 755068995 1431524431 1296388705 1668573558 ""
t1 524288 0 755072067 1431524431 1296388705 1668573558 ""
t1 524288 0 755075139 1431524431 1296388705 1668573558 ""
t1 525312 0 755078211 1431524431 1296388705 1668573558 ""
t1 524288 0 755081283 1431524431 1296388705 1668573558 ""
t1 524288 0 755084355 1431524431 1296388705 1668573558 ""
t1 524288 0 755047491 1431524431 1296388705 1668573558 ""
t1 524288 0 755047491 1431524431 1296388705 1668573558 ""
t1 0 0 0 0 0 0 ""
t1 0 0 0 0 0 0 ""
t1 0 0 0 0 0 0 ""
t1 0 0 0 0 0 0 ""
t1 0 0 0 0 0 0 ""

You can see each line is pre-pended with the table name (followed by a tab).

You can also see at the end (of the above output) there are a number of empty rows. These are just garbage rows, and can be deleted before you import or afterwards. You’ll see similar such rows in most of the recovered tables’ data as well. However, don’t just delete from the end of the file, as actual data rows are scattered all throughout the files.

I’d also suggest creating some temporary tables using the same CREATE TABLE commands but without any keys or indexes. This will allow you to import the data easier, and then you can clean it up with simple SQL commands. And after that, then you could simply add back your primary keys, indexes, and referential keys.

Should you follow my approach and do this per-table, then you just need to create your new table_defs.h file, re-compile and make, then re-run the constraints_parser just as you did above. Since it is built with the new table_defs.h file, it will now extract the data for this table, so no other changes need to be made.

10. Format the dump file(s) so that it can be imported into the appropriate table(s).

11. Import the data, and clean up the garbage rows.

12. Re-create any needed indexes and/or referential keys.

SHOW PROXY PROCESSLIST : Another Proxy Tutorial Revisited

In a previous post, Another Proxy Tutorial, I outlined how you could find and display the originating IP address of a particular query from within MySQL Proxy.

This was a nice illustration of how to accomplish the above, however, in practice, there were a couple of limitations. For one, the queries were “tagged” with the originating IP address, so the query cache would only work for the same query coming from the same IP address. So if you’re using the query cache, then this is not an optimal solution. Secondly, you could only see the IP address for a current query, and not for an idle connection. Thus when issuing a SHOW PROCESSLIST command, one would only see the IP address only for actual queries that are being executed, and not for any of the other idle connections.

This was bothersome to me, so I worked up a better and more efficient solution that addresses both of these concerns.

In this new solution, I implement a new command called SHOW PROXY PROCESSLIST. This leaves SHOW PROCESSLIST output unchanged, and instead, if you want to see the originating IP address of the every connection listed in the SHOW PROCESSLIST output, then simply issue a SHOW PROXY PROCESSLIST command.

Here is an example of the output when using 3 different connections from differing IP addresses:

mysql> SHOW PROXY PROCESSLIST; SHOW PROCESSLIST;
+------+---------------------+-------------------+
| Id   | IP Address          | Time              |
+------+---------------------+-------------------+
| 2    | 127.0.0.1:49528     | 04/15/09 13:22:14 |
| 3    | 192.168.1.100:1322  | 04/15/09 13:22:23 |
| 4    | 192.168.1.103:59374 | 04/15/09 13:22:44 |
+------+---------------------+-------------------+
3 rows in set (0.00 sec)

+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
|  1 | root | localhost:49526 | NULL | Sleep   |   13 |       | NULL             |
|  2 | root | localhost:49529 | NULL | Query   |    0 | NULL  | SHOW PROCESSLIST |
|  3 | root | localhost:49533 | NULL | Sleep   |   50 |       | NULL             |
|  4 | root | localhost:49547 | NULL | Sleep   |   18 |       | NULL             |
+----+------+-----------------+------+---------+------+-------+------------------+
4 rows in set (0.00 sec)

Here, you can see I simply called both commands in a single call.

The first output is the SHOW PROXY PROCESSLIST output. In it you can see the “Id” which corresponds to the “Id” in the SHOW PROCESSLIST output. You can also see the corresponding originating IP address along with the client source port, as well as the time stamp of when the connection was created.

To implement this, I simply capture and store the client address information in an internal table. I’m able to capture this from within the read_auth_result() function.

function read_auth_result( auth )
	local state = auth.packet:byte()
	if state == proxy.MYSQLD_PACKET_OK then
		initialize_process_table()
		table.insert( proxy.global.process[proxy.connection.server["thread_id"]], 
			{ ip = proxy.connection.client["address"], ts = os.time() } )	
	end
end

So when within the function read_auth_result(), if state == proxy.MYSQLD_PACKET_OK, then we know the connection was auth’ed, and hence I log this information at this point.

Note that I’m storing the value of proxy.connection.client[“address”] which is the original IP address along with the client source port (e.g., 192.168.1.100:1322). The nice thing about this is you can track down the port information too on the original machine.

(Also note that in MySQL Proxy 0.7.0, there are even more available ports (variables), so you could add more to capture, say the client destination port, or the server source port. For more on this, pelase see Jan Kneschke’s post about this.)

On the remote machine (192.168.1.100), if I run the following command, I can track this connection via netstat:

netstat -an | grep 1322
tcp 0 0 192.168.1.100:1322 192.168.1.101:4050 ESTABLISHED

There is also another internal function to proxy that I use, which is disconnect_client(). This is called when a client disconnects, so if this function is entered for a particular connection, I simply delete that connection entry from the internal table.

function disconnect_client()
	local connection_id = proxy.connection.server.thread_id
	if connection_id then
		-- client has disconnected, set this to nil
		proxy.global.process[connection_id] = nil
	end
end

The rest of the code is basically to display this information in a nice format.

Here is the full code:

function read_query( packet )		
	if string.byte(packet) == proxy.COM_QUERY then
		if string.sub(packet, 2):lower() == 'select help' then
			return show_process_help()
		end
		if string.sub(packet, 2):lower() == 'show proxy processlist' then
			return show_process_table()
		end
		--debug output
		--dump_process_table()
		proxy.queries:append(1, packet )
		return proxy.PROXY_SEND_QUERY		
	end
end

function make_dataset (header, dataset) 
	proxy.response.type = proxy.MYSQLD_PACKET_OK

	proxy.response.resultset = {
		fields = {},
		rows = {}
	}
	for i,v in pairs (header) do
		table.insert(proxy.response.resultset.fields, {type = proxy.MYSQL_TYPE_STRING, name = v})
	end
	for i,v in pairs (dataset) do
		table.insert(proxy.response.resultset.rows, v )
	end
	return proxy.PROXY_SEND_RESULT
end

function initialize_process_table()
	if proxy.global.process == nil then
		proxy.global.process= {}
	end
	if proxy.global.process [proxy.connection.server["thread_id"] ]== nil then
		proxy.global.process[proxy.connection.server["thread_id"] ]= {}
	end
end

function show_process_table()
	initialize_process_table()
	local dataset = {}
	local header = { 'Id', 'IP Address', 'Time' }
	local rows = {}
	for t_i, t_v in pairs (proxy.global.process) do
		for s_i, s_v in pairs ( t_v ) do
			table.insert(rows, { t_i, s_v.ip, os.date('%c',s_v.ts) })
		end
	end
	return make_dataset(header,rows)
end

function show_process_help()
	local dataset = {}
	local header = { 'command',  'description' }
	local rows = {
		{'SELECT HELP',                 'This command.'},    
		{'SHOW PROXY PROCESSLIST',      'Show all connections and their true IP Address.'},    
	}
	return make_dataset(header,rows)
end

function dump_process_table()
	initialize_process_table()
	print('current contents of process table')
	for t_i, t_v in pairs (proxy.global.process) do
		print ('session id: ', t_i)
		for s_i, s_v in pairs ( t_v ) do
			print ( 't', s_i, s_v.ip, s_v.ts )
		end
	end
	print ('---END PROCESS TABLE---')
end

function read_auth_result( auth )
	local state = auth.packet:byte()
	if state == proxy.MYSQLD_PACKET_OK then
		initialize_process_table()
		table.insert( proxy.global.process[proxy.connection.server["thread_id"]], 
			{ ip = proxy.connection.client["address"], ts = os.time() } )
	end
end

function disconnect_client()
	local connection_id = proxy.connection.server.thread_id
	if connection_id then
		-- client has disconnected, set this to nil
		proxy.global.process[connection_id] = nil
	end
end

If you saved this to a file named show-ips.lua, then you would start MySQL Proxy as follows:

mysql-proxy --proxy-lua-script=share/mysql-proxy/show-ips.lua

And then you would connect to the Proxy as follows:

mysql -u root -pmysql -P4040

After that, you’ll be able to issue the commands implemented here such as SHOW PROXY PROCESSLIST and SELECT HELP:

mysql> SELECT HELP;
+------------------------+-------------------------------------------------+
| command                | description                                     |
+------------------------+-------------------------------------------------+
| SELECT HELP            | This command.                                   |
| SHOW PROXY PROCESSLIST | Show all connections and their true IP Address. |
+------------------------+-------------------------------------------------+
2 rows in set (0.00 sec)

mysql> SHOW PROXY PROCESSLIST;
+------+---------------------+-------------------+
| Id   | IP Address          | Time              |
+------+---------------------+-------------------+
| 5    | 127.0.0.1:51128     | 04/15/09 14:09:42 |
| 6    | 192.168.1.100:2341  | 04/15/09 14:10:22 |
| 8    | 192.168.1.103:52722 | 04/15/09 14:18:49 |
+------+---------------------+-------------------+
3 rows in set (0.00 sec)

Lastly, note that you can easily integrate these functions to the quan.lua script that the Query Analyzer Proxy uses (for MySQL Enterprise Monitor).

Recovering an InnoDB table from only an .ibd file.

Sometime you may need to recover a table when all you have is the .ibd file. In this case, if you try to load it into a new instance, your likely to encounter some errors about the table id not matching. And there is not really a way around this.

However, I’ve found two work-arounds for this:

Note: You will need the .ibd file and the CREATE TABLE statement for each table you want to recover using these methods.

  1. Simulate the internal InnoDB table counter. That is, create work tables (with innodb_file_per_table enabled) until you have the internal pointer of table id equal to (1 – id_of_ibd_table_you_need_to_restore). (See Method #1)
  2. Manually hex edit the .ibd file, changing the table id. (See Method #2)

*Note: There are some internal structures with this meta information, so you’ll need to dump/import that data after you get it loaded, so you avoid unpleasantries that will inevitably show their face.

Method #1 – Create work tables

1. Start up clean/fresh instance of MySQL with innodb_file_per_table enabled.

2. Now, we need to find the table id that MySQL is currently set at, as well as the table id for the table we need to recover.

Note:
Step 2 (2a – 2f) is simply to find the table id that is stored inside of the .ibd file. I’ve written a PHP script to determine this, so using the script can save a bunch of time. See the bottom of this page (under “Associated Files”) for the exact script.

2a. Create a test database:

mysql> CREATE DATABASE test1;
mysql> USE test1;

2b. Issue the create table command for the table:

mysql> CREATE TABLE `product` (
  `PRODUCT_ID` bigint(20) unsigned NOT NULL auto_increment,
  `BRAND_ID` int(10) unsigned default NULL,
  `PRODUCT_TYPE_ID` int(10) unsigned default NULL,
  `GROUP_ID` int(10) unsigned default NULL,
  `PRODUCT_NAME` varchar(500) NOT NULL,
  `DEFAULT_EMAIL_ID` varchar(48) default NULL,
  `PRODUCT_STATUS` tinyint(1) NOT NULL,
  `CLIENT_ID` bigint(20) unsigned default NULL,
  `LAST_MODIFIED_BY` varchar(45) NOT NULL,
  `LAST_MODIFIED_DATE` datetime NOT NULL,
  PRIMARY KEY  (`PRODUCT_ID`)
  ) ENGINE=InnoDB;

2c. Discard the tablespace, which will delete the newly created .ibd file:

mysql> ALTER TABLE product DISCARD TABLESPACE;

2d. Copy the pre-existing .ibd file to the datadir/test1 folder

2e. Import this tablespace:

mysql> ALTER TABLE product IMPORT TABLESPACE;

This should produce the following error (at least this is most likely). The only way it would not is if MySQL’s current table id matched that of the preexisting ibd table id. In which case, you can now dump your table.

ERROR 1030 (HY000): Got error -1 from storage engine

2f. So, now to check the error log (manually). Look for the following entry:

081010 11:47:40  InnoDB: Error: tablespace id in file 
'.test1product.ibd' is 1193, but in the InnoDB
InnoDB: data dictionary it is 1.

So, now we know the internal table id is at 1, and that of the ibd table is 1193.

3. Clean up working database:

3a. Manually move the ibd file from the $datadir to a safe location (as you will need this file again).

3b. Drop this table.

mysql> DROP TABLE product;

Note this does not re-set the internal table counter.

4. You’ll need to create the number of tables you need to increase the internal table id value.

In this case, you’d create 1191 test InnoDB tables (already at 1, and need to leave 1 for the actual table, so 1193-2=1191). Run below in a loop.

for ($1=1; $i<=1191; $1++) {
  CREATE TABLE t# (id int) ENGINE=InnoDB;
}

I accomplished this via a simple php script. See the bottom of this page (under "Associated Files") for the exact script.

5. After these are created, go ahead and drop this database and all tables (as they are not needed).

DROP DB test1;

6. Now, re-perform steps 2a through 2e.

mysql> CREATE DATABASE test1;
mysql> USE test1;
mysql> CREATE TABLE `product` ( ... ) ENGINE=InnoDB;
mysql> ALTER TABLE product DISCARD TABLESPACE;

<--  Here is where you copy back the original ibd file to /$datadir/test1/ -->

mysql> ALTER TABLE product IMPORT TABLESPACE;

Success!

mysql> show tables;
+-----------------+
| Tables_in_test1 |
+-----------------+
| product         |
+-----------------+
1 row in set (0.00 sec)

7. Now, dump the table using mysqldump, and then you can import this to any MySQL instance. Note, you must dump this and re-import it, or you'll run into problems.

However, it's possible to encounter crashes and/or reports of corruption in the logs.

If this happens, try to force innodb recovery (which is most likely), and then dump the table.

Start by setting innodb_force_recovery=1 (and try 2,3,4,5,6) until the dump works.

For this example table, I had to set innodb_force_recovery=5 before the dump would succeed.

The # in the output file name is the value I had innodb_force_recovery set to when trying to perform the dump:

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump1.txt
mysqldump: Couldn't execute 'show table status like 'product'': 
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump2.txt
mysqldump: Couldn't execute 'show table status like 'product'': 
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump3.txt
mysqldump: Couldn't execute 'show table status like 'product'': 
Lost connection to MySQL server during query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump4.txt
mysqldump: Couldn't execute 'SELECT /*!40001 SQL_NO_CACHE */ 
* FROM `product`': Lost connection to MySQL server during 
query (2013)

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump5.txt

C:Program FilesMySQLmysql-5.0.68bin>
mysqladmin -u root -P 3385 shutdown

C:Program FilesMySQLmysql-5.0.68bin>
mysqldump -uroot -P3385 test1 product > product_dump6.txt

In fact, in this case, I could have simply started with 5. This is because the error log stated this:

InnoDB: Error: trying to access update undo rec field 19 
in index PRIMARY of table test1/product
InnoDB: but index has only 12 fields

So, I knew there was a problem trying to look at the undo logs, and from the manual, a setting of 5 says this:

"Do not look at undo logs when starting the database: InnoDB treats even incomplete transactions as committed"

However, it's best to start at 1 and work your way forward so as to prevent as much data loss as possible.

Method #2 - Hex Edit .ibd file

First of all, you'll need to backup everything (ibdata files, ib_logfile(s), data). I'd also perform a mysqldump of everything you currently have, just so you have a mysqldump of it in the event that you need it.

Also, very important, be sure to make a copy of the .ibd file for the specific table in question.

Lastly, get a copy of the CREATE TABLE statement that will recreate this table.

Then, you'll follow the steps #1-5 (but do not perform step #6 yet) outlined on the following page:

http://dev.mysql.com/doc/refman/5.0/en/adding-and-removing.html

Let me post them here for completeness, however:

  1. Use mysqldump to dump all your InnoDB tables.
  2. Stop the server.
  3. Remove all the existing tablespace files, including the ibdata and ib_log files. If you want to keep a backup copy of the information, then copy all the ib* files to another location before the removing the files in your MySQL installation.
  4. Remove any .frm files for InnoDB tables.
  5. Configure a new tablespace.
  6. Restart the server.
  7. Import the dump files.

At this point, MySQL should be running fine with an empty slate (and should have just re-created your new ibdata and log files).

Now, you'll want to recreate the table (just using the CREATE TABLE output from above), and its database to hold it.

Then, you'll basically be following the steps #1-3 outlined on the following page:

http://dev.mysql.com/doc/refman/5.0/en/multiple-tablespaces.html

1. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name DISCARD TABLESPACE;

Caution: This statement deletes the current .ibd file.

2. Put the backup .ibd file back in the proper database directory (the one that you copied above).

3. Issue this ALTER TABLE statement:

ALTER TABLE tbl_name IMPORT TABLESPACE;

Everything should go smoothly until step #3 (above). More than likely, this will produce an error like the following on your console:

"Got error -1 from storage engine"

Now, if you look in the error log, you'll see something like:

"InnoDB: Error: tablespace id in file '.testt2.ibd' is 2, 
but in the InnoDB data dictionary it is 1."

It would not produce the above error and would work fine if the existing table already had a tablespace id of 1. However, this is unlikely.

So, assuming you see the above errors, then you can modify the tablespace id actual ibd file using a hex editor. I would do this on a different copy of the ibd file (other than the original, just in case).

Note that I used "Freeware Hex Editor XVI32" for Windows for this step. Start the program, and then open the .ibd file. You'll see each byte in it's own cell. You can then click on a cell, click edit, and then edit that byte. (http://www.chmaas.handshake.de/delphi/freeware/xvi32/xvi32.htm)

Now, in this file, there are 2 places where this tablespace id is located.

For me, and I assume it should be the same for you, but just look at the values to be sure, I see the tablespace id values listed at position 37 and 41 (positions 25 and 29 in hex). In the actual hex column, if you're previous tablespace id was 2, then in positions 37 and 41, you'd see 02 and 02.

(Note these positions can change. For instance, I tested on a table with an internal id of 1193. This in hex is 04A9. However, when searching the file, for the first instance of the table id, I found the '04' in position 39 and 'A9' in position 40. Then, for the second instance of the table id, the '04' was at position 43 and the 'A9' was at position 44. So, you'll have to convert the table id to hex, and then search for that value, near the beginning of the file.)

Note that this value (02) may vary depending on what your actual tablespace id is.

Then, simply modify both of those fields to 01, and save the file.

Then, re-do the following 3 steps:

1. ALTER TABLE tbl_name DISCARD TABLESPACE;
2. Put the newly saved .ibd file back in the proper database directory
3. ALTER TABLE tbl_name IMPORT TABLESPACE;

This time, step #3 works fine.

It is at this point you should dump/import the data. At least, get a good mysqldump of this table now. You may find that this causes corruption in InnoDB, and you may need to start MySQL using --force-innodb-recovery.

http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html Forcing InnoDB Recovery

Associated Files :: PHP Scripts

Simple PHP script - Used to create a number of InnoDB tables (to increase internal table id counter):

$dbhost = "localhost:3385";
$dbname = "test1";
$dbuser = "root";
$dbpwd  = "";

mysql_connect($dbhost,$dbuser,$dbpwd) or die(mysql_error());

for ($i = 1033; $i <= 1190; $i++) {
   $dbquery = "CREATE TABLE test1.t" . $i . " (id int) ENGINE=InnoDB";
	
   echo "" . $dbquery . "";
	
      $result = mysql_db_query($dbname,$dbquery) or die(mysql_error());
	
      $j = 0;
	
      while($row = mysql_fetch_array($result)) {
         $j++;
         echo $row[0];
      }
}

mysql_close();

PHP Internal Table ID Finder - Used to determine the internal Table ID from the binary .ibd file:

/*
Tested with tables from 4.1.23, 5.0.68, 5.1.28, and 6.0.7.
*/

// Set the filename
$filename = "C:\Users\Chris\Desktop\mysql\working\ibds\z1.ibd";

// Read 2 bytes in at a time
$offset = 2;

// Echo filename and path
echo "filename = $filename

"; // Open the filename - need 'rb' for binary file on Windows $handle = fopen($filename, "rb"); // Define redundant, local variables for possible later functionality and/or checks $ibd_id_bin = 0; $ibd_id_hex = 0; $ibd_id_dec = 0; $ibd_id_bin2 = 0; $ibd_id_hex2 = 0; $ibd_id_dec2 = 0; // Find the filesize (note: below command messes up script) //$filesize = filesize($filename)); // Only loop through first 21 bytes - as table is is in $array[18] and $array[20] for ($z = 0; $z <= 20; $z++) { // Set variable $contents equal to 2 ($offset) bytes of binary data $contents = fread($handle, $offset); // Convert $contents from binary data to hex data $contents2 = bin2hex($contents); // Convert $contents2 from hex data to decimal data $contents3 = hexdec($contents2); // Debug Output //echo "contents[$z] = " . $contents . "
"; //echo "contents2[$z] = " . $contents2 . "

"; //echo "contents3[$z] = " . $contents3 . "

"; // If position 19, array position [18], then store the values if ($z == 18) { $ibd_id_bin = $contents; $ibd_id_hex = $contents2; $ibd_id_dec = $contents3; } // If position 21, array position [20], then store the values if ($z == 20) { $ibd_id_bin2 = $contents; $ibd_id_hex2 = $contents2; $ibd_id_dec2 = $contents3; } } fclose($handle); // More Debug output //echo "

The table id is $ibd_id_dec

"; //echo "

The table id is $ibd_id_dec2

"; // Check to see if both values are equal. If so, then it's // most certain this is the correct value. // If not, then there's a chance the positions are off for // this table (due to versions, etc.). if ($ibd_id_dec == $ibd_id_dec2) { echo "

The table id is $ibd_id_dec

"; } else { echo "The values from positions [18] and [20] did not match,"; echo "so please enable debug output, and check for proper positions."; }

Another MySQL Proxy Tutorial

Since MySQL Proxy 0.7.0 is soon to be released, I thought another brief tutorial would be helpful. Today we ran across a good use case, and so I wanted to pass this along. If you’ve not used Proxy yet, this is a great opportunity to get your feet wet with it and some Lua.

When queries are routed though MySQL Proxy from various servers, the MySQL Server only sees that the query came from the machine Proxy is running on.

So when you are viewing output of SHOW PROCESSLIST, you have no way of telling what server a particular query originated from.

However, this could be very useful information to have, especially to determine which server a particular long-running query is coming from at that particular moment in time.

Output not using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:61252 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | 10.1.10.1:61267 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | 10.1.10.2:62047 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | 10.1.10.3:62050 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | 10.1.10.4:62052 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

In the above, you can easily see the IP address of the server the query originated from.

However, now see the output when using Proxy:

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+------------------+
| Id | User | Host            | db   | Command | Time | State | Info             |
+----+------+-----------------+------+---------+------+-------+------------------+
| 11 | root | localhost:62255 | test | Query   |    0 | NULL  | show processlist |
| 12 | root | localhost:62271 | NULL | Query   |   17 | init  | select sleep(30) |
| 13 | root | localhost:63038 | NULL | Query   |   18 | init  | select sleep(30) |
| 14 | root | localhost:63042 | NULL | Query   |   19 | init  | select sleep(30) |
| 15 | root | localhost:63043 | NULL | Query   |   20 | init  | select sleep(30) |
+----+------+-----------------+------+---------+------+-------+------------------+
5 rows in set (0.00 sec)

Here, every query shows as originating from ‘localhost’. This is accurate, but in this example, we’d like to know the originating server.

So, how do we accomplish this?

A good work-around to this problem is to append the originating host to the query in the form of a query comment. In this way, the comment will get passed through, and will appear in query itself, under the “Info” column of the output. This will also incur very little overhead, as we’re only appending a known value to the query, and then the MySQL parser handles it as it would any other query (which is to execute it while ignoring the comment).

Now, how do we accomplish this with Lua and Proxy?

I started using one of the included sample scripts – tutorial-rewrite.lua.

I chose this file because it already has the stub code included for both the following function:

function read_query( packet )

It is in this function where the query is read, and before it is sent to the MySQL Server. So this is where we can modify the query to append this information.

Here is the resulting function read_query():

function read_query( packet )
  if string.byte(packet) == proxy.COM_QUERY then
    local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)
    print("we got a normal query: " .. query)
    proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
    return proxy.PROXY_SEND_QUERY
  end
end

The first 3 lines are already in that file. However, I did modify the 3rd line to include the comment with the originating server IP.

Here is the new 3rd line:

local query = "/* " .. proxy.connection.client["address"] .. " */ " .. string.sub(packet, 2)

This defines “query” as a local variable, and sets it equal to the comment concatenated with the actual query.

You can see it starts by appending the comment, which is of the form: /* comment */, where comment is the IP address of the server the query came from.

Note that “..” is used to concatenate two strings in Lua.

The IP address can be found in an existing variable, which is:

proxy.connection.client["address"]

And the query itself can be found in an existing variable, which is:

string.sub(packet, 2)

So as long as we’re within function read_query(), then we have access to these variables, and they’ll be properly assigned.

The next statement is the print() statement, which is originally from the sample script, and I left it in for debugging purposes:

print("we got a normal query: " .. query)

And then you just need the following two statements:

proxy.queries:append(1, string.char(proxy.COM_QUERY) .. query )
return proxy.PROXY_SEND_QUERY

The former appends the new query to the query stack to be executed. This must be done so the changes you make to the variable “query” are actually reflected in the packet that gets sent to the server. And in the latter, PROXY_SEND_QUERY is called, which sends the packet (with the modified query) to the MySQL Server to be executed.

So, now we just need to save this function to a file, start up proxy with this script, and then issue a SHOW PROCESSLIST.

I’ll save the above function to a file named ‘retain-hosts.lua’.

In Window #1:

cd "../../Program Files/MySQL/mysql-proxy-0.6.0"
mysql-proxy --proxy-lua-script="C:Program FilesMySQLmysql-proxy-0.6.0retain-hosts.lua"

In Window #2:

mysql -u root -pmysql -h 127.0.0.1 -P 4040 --comments

Note you must pass the –comments option to the MySQL Client, or the comments will get stripped out. This is at least necessary for testing, or if actually passing queries through the command line interface.

Now in Window #2, issue your SHOW PROCESSLIST (note I’ve thrown a few queries at it from some other connections):

mysql> show processlist;
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| Id | User | Host            | db   | Command | Time | State | Info                                   |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
| 11 | root | localhost:62252 | test | Query   |    0 | NULL  | /* 127.0.0.1:11251 */ show processlist |
| 12 | root | localhost:62267 | NULL | Query   |   17 | init  | /* 10.1.10.1:15091 */ select sleep(30) |
| 13 | root | localhost:63047 | NULL | Query   |   18 | init  | /* 10.1.10.2:18166 */ select sleep(30) |
| 14 | root | localhost:63050 | NULL | Query   |   19 | init  | /* 10.1.10.3:18934 */ select sleep(30) |
| 15 | root | localhost:63052 | NULL | Query   |   20 | init  | /* 10.1.10.4:19446 */ select sleep(30) |
+----+------+-----------------+------+---------+------+-------+----------------------------------------+
5 rows in set (0.00 sec)

Voila! Now you can see in the IP address of the server the query originates from in the actual query itself, as a comment.

For more information on MySQL Proxy, please see the following links:

http://dev.mysql.com/downloads/mysql-proxy/index.html
http://forge.mysql.com/wiki/MySQL_Proxy
http://forge.mysql.com/wiki/MySQL_Proxy_Cookbook
http://jan.kneschke.de/projects/mysql/mysql-proxy/
http://datacharmer.blogspot.com/