Archive for November, 2011

Nasty Regression Bug Seems Fixed in 5.5.18

Wednesday, November 30th, 2011

For those who saw my previous post about the crashing (regression) bug with SELECT COUNT(DISTINCT) on InnoDB with Primary Key (PK), you’ll be interested to know my test case does not crash in 5.5.18 (which was just released).

I’ve only tested my test case thus far, but it seems fine.

Unfortunately, the fix is not mentioned in the 5.5.18 changelogs though.

And there is no mention (yet, anyway) of a fix in the bug report I filed (though it was designated a ‘duplicate’, so it wouldn’t necessarily be updated).

I’m trying to get confirmation from the MySQL Dev Team on this (via the bug report), and will update this post if/when I hear anything.

I’ll also perform some of the other tests on my end to see if they all pass as well.

All in all, at least the initial results look very promising! :)

Using SHOW PROCESSLIST and mysqladmin debug Output in Conjunction with SHOW INNODB STATUS

Saturday, November 19th, 2011

When InnoDB appears hung, I know the natural reaction is to check SHOW ENGINE INNODB STATUS.

In fact, it’s the first thing I check when InnoDB tables are involved.

However, I just want to iterate how valuable SHOW FULL PROCESSLIST and/or mysqladmin debug outputs can be even when it seems mysqld is hung on on InnoDB table.

Two recent cases I’ve encountered illustrate why.

Case #1:

MySQL appeared hung on the following simple, single-row INSERT:

---TRANSACTION 0 2035648699, ACTIVE 76629 sec, process no 9047,
OS thread id 3069426592, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
...
INSERT INTO test (id, parent, text) VALUES (180370, 70122, 'test table')

At least that’s what it seemed per the INNODB STATUS, but unfortunately, there wasn’t any further information to go on.

The next time it occurred, SHOW FULL PROCESSLIST was captured at the time.

Turns out, there was a *very* long SELECT running, but not from the same table, and no foreign keys (FKs) either. Turned out it was some crazy, auto-generated query that self-joined itself 548 times. So there were no locks, per se. This query itself held up everything, and thus also the INSERT.

Case #2:

This was a table that was also hanging on a certain, simple UPDATE. The UPDATE was based on te PK, so only one row was to be updated.

Yet, it hung, and it hung, longer than wait_timeout, interactive_timeout, and innodb_lock_wait_timeout. And there were no other transactions running in the INNODB STATUS.

Turned out, another client had issued a LOCK TABLE command on the table. Since LOCK TABLE is handled outside of the InnoDB storage engine, the lock doesn’t appear in SHOW INNODB STATUS output.

Using mysqladmin debug output, coupled with SHOW PROCESSLIST helped catch this offender.

At any rate, hope this helps, and happy troubleshooting. :)

 
 

Setting Up SSL For MySQL

Friday, November 18th, 2011

I often get asked how to set up and use SSL with MySQL, especially on Windows – I think largely because the manual step-by-step is more geared towards Linux users (though this “how-to” is valid for all – anyone can copy/paste the commands after changing the paths).

So I’ve compiled a concise, yet comprehensive, how-to that I wanted to share with everyone out there who wants to set this up quickly and efficiently.

And for those who haven’t done it, setting up SSL can often seem intimidating, so I want to dispell that myth while I’m at it, and show you how quick and easy it can be to set up SSL for MySQL.

For one, I should mention that I’m using a version where SSL is ‘available’, just ‘DISABLED’ (some versions can be compiled w/out ssl support – so those would not work):

mysql> show global variables like 'have_%ssl';
+---------------+----------+
| Variable_name | Value    |
+---------------+----------+
| have_openssl  | DISABLED |
| have_ssl      | DISABLED |
+---------------+----------+

This is normal, and what you want to see if you’ve not already set up SSL.

Next, I like to store the SSL files I generate in their own directory, so I created a subdir named ‘certs’ in the MySQL $basedir. But by all means, store these wherever you wish.

Third, you need to have OpenSSL installed/available on your machine. Many have this already, certainly most Linux anyway (check with “where openssl” on Win or “which openssl” on Linux). (These latter commands assume you have it in the $PATH too.)

If you do not have it, you can download it from here:

http://www.slproweb.com/…/Win32OpenSSL.html <-- Windows
http://www.openssl.org/source/ <-- Linux

(And note my commands below assume either 'openssl' in the $PATH or that you're in the directory where the 'openssl' binary/exe is located.)

Now that that’s out of the way, you’re basically looking at a 5 step process:

  1. Create CA Certificates (following 2 commands create 2 files: ca-cert.pem and ca-key.pem):
    openssl genrsa 2048 > "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem"
    openssl req -new -x509 -nodes -days 3600 -key
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
  2. Create Server Certificates (following 2 commands create 3 files: server-cert.pem, server-key.pem, and server-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/server-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/server-cert.pem"
  3. Create Client Certificates (following 2 commands create 3 files: client-cert.pem, client-key.pem, and client-req.pem):
    openssl req -newkey rsa:2048 -days 3600 -nodes -keyout
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-key.pem" >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
    openssl x509 -req -in "C:/Program Files/mysql/mysql-5.5.16/certs/client-req.pem"
      -days 3600 -CA "C:/Program Files/mysql/mysql-5.5.16/certs/ca-cert.pem"
      -CAkey "C:/Program Files/mysql/mysql-5.5.16/certs/ca-key.pem" -set_serial 01 >
      "C:/Program Files/mysql/mysql-5.5.16/certs/client-cert.pem"
  4. Create SSL User(s):
    GRANT ALL PRIVILEGES ON *.* TO 'ssluser'@'localhost' IDENTIFIED BY 'ssluser' REQUIRE SSL;
  5. Update my.cnf and restart mysqld (add following 3 options to [mysqld] section of my.cnf file)
    [mysqld]
    ssl-ca		= "C:/Program Files/MySQL/mysql-5.5.16/certs/ca-cert.pem"
    ssl-cert	= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-cert.pem"
    ssl-key		= "C:/Program Files/MySQL/mysql-5.5.16/certs/server-key.pem"

After restart, connect via SSL with your newly created SSL user (this is the minimum you need to specify – note the “–ssl-key=”, as-is .. refer to prev. post and related bug for further discussion on that if interested):

mysql -ussluser -pssluser -P3430 --ssl-key=

Connected!

You can verify with the ‘status’ command once connected (note the SSL “cipher in use is DHE-RSA-AES256-SHA” means SSL is being used):

mysql> status
--------------
mysql  Ver 14.14 Distrib 5.5.16, for Win32 (x86)

Connection id:          11
Current database:
Current user:           ssluser@localhost
SSL:                    Cipher in use is DHE-RSA-AES256-SHA
Using delimiter:        ;
Server version:         5.5.16-log MySQL Community Server (GPL)
Protocol version:       10
Connection:             localhost via TCP/IP
Server characterset:    latin1
Db     characterset:    latin1
Client characterset:    cp850
Conn.  characterset:    cp850
TCP port:               3430
Uptime:                 35 min 26 sec

Threads: 1  Questions: 24  Slow queries: 0  Opens: 33  Flush tables: 1  Open tables: 0
Queries per second avg: 0.011

Comments:

If you’re knowledgeable abut SSL (and MySQL), or if you’ve read my prior post discussing SSL, you’ll know you do not need all of the files I created above (8 in total).

In fact, for the most basic SSL setup, (“gateway SSL”, we’ll say), you only technically need these 3 files: ca-cert.pem, server-cert.pem, and server-key.pem.

But, why not create all you’d ever need, since it’s only a couple commands, and then you’re set in case you want to move beyond the “gateway SSL” setup into an even more secure SSL setup.

If you’re interested in the even-more-secure SSL setups, you’ll want to read up on the REQUIRE X509, REQUIRE ISSUER, REQUIRE SUBJECT, and REQUIRE CIPHER specifications on the following page:

http://dev.mysql.com/doc/refman/5.5/en/grant.html

As always, hope this helps. :)

 
 
 

Manually Deleting file and DISCARD TABLESPACE are not the only ways to encounter the “ibd file does not exist” Error

Friday, November 18th, 2011

I’m sure most of you have seen the following error, and in many cases it’s quite easy to diagnose, as the error message is clear:

111111 11:11:11 [ERROR] MySQL is trying to open a table handle but the .ibd file for
table test/t does not exist.
Have you deleted the .ibd file from the database directory under
the MySQL datadir, or have you used DISCARD TABLESPACE?
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

From the client side, you may see something like:

ERROR 1146 (42S02): Table 'test.t' doesn't exist

However, if you know you (or some backup/archive script) did not delete the file, nor did you run ALTER TABLE table DISCARD TABLESPACE, then what else could cause this?

Well, I recently found a case where it could occur when a truncate table (on an InnoDB table using the plugin with no foreign keys – so fast truncate method is used, and innodb_file_per_table is enabled) is issued on a table.

Then, if a crash occurs in the middle of this (after the .ibd file has been deleted, but before the re-creation process completes), then you can end up with this error (after a restart and a subsequent SELECT on this table).

I expect this is quite rare, but can happen nonetheless, so I wanted to add this for those searching for more possibilities of missing .ibd files.

Fwiw, I did add this as a user comment in the manual where this topic is discussed:

http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting-datadict.html

 
 
 

Advanced InnoDB Deadlock Troubleshooting – What SHOW INNODB STATUS Doesn’t Tell You, and What Diagnostics You Should be Looking At

Thursday, November 10th, 2011

One common cause for deadlocks when using InnoDB tables is from the existence of foreign key constraints and the shared locks (S-lock) they acquire on referenced rows.

The reason I want to discuss them though is because they are often a bit tricky to diagnose, especially if you are only looking at the SHOW ENGINE INNODB STATUS output (which might be a bit counter-intuitive since one would expect it to contain this info).

Let me show a deadlock error to illustrate (below is from SHOW ENGINE INNODB STATUS\g):

------------------------
LATEST DETECTED DEADLOCK
------------------------
111109 20:10:03
*** (1) TRANSACTION:
TRANSACTION 65839, ACTIVE 19 sec, OS thread id 4264 starting index read
mysql tables in use 1, locked 1
LOCK WAIT 6 lock struct(s), heap size 1024, 3 row lock(s), undo log entries 1
MySQL thread id 3, query id 74 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=1
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65839 lock_mode X locks rec but not gap waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) TRANSACTION:
TRANSACTION 65838, ACTIVE 26 sec, OS thread id 768 starting index read,
thread declared inside InnoDB 500
mysql tables in use 1, locked 1
7 lock struct(s), heap size 1024, 4 row lock(s), undo log entries 2
MySQL thread id 4, query id 75 localhost 127.0.0.1 root Updating
UPDATE parent SET age=age+1 WHERE id=2
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 6833 page no 3 n bits 72 index `PRIMARY` of table
`test`.`parent` trx id 65838 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0

*** WE ROLL BACK TRANSACTION (1)

Now, we do see a lot about what caused the deadlock above, but we are only seeing *half* of the picture.

Allow me to explain ..

First of all, note transaction #1 has been running for 19 seconds, while transaction #2 for 26 seconds. So, the output is referring to the newer transaction as #1 and the older as #2 (also somewhat counter-intuitive, but good to be aware of).

Now, what we can see clearly is this:

Transaction #1 (“UPDATE parent .. WHERE id=1″) is waiting on a lock from Transaction #2 (“UPDATE parent .. WHERE id=2″).

Thus TX #2 holds a lock (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 2), but is waiting on (RECORD LOCKS space id 6833 page no 3 n bits 72 index, heap no 3 – held by TX #1).

Clearly the 2 updates should not cause a conflict in and of themselves. Thus we know something must have happened earlier in the transaction(s).

For reference, here is how to reproduce it:

CREATE TABLE `parent` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE TABLE `child` (
`id` INT NOT NULL AUTO_INCREMENT,
`age` INT NOT NULL,
`parent_id` INT NOT NULL,
PRIMARY KEY (`id`),
KEY `parent_id` (`parent_id`),
CONSTRAINT `fk_parent_id` FOREIGN KEY (`parent_id`) REFERENCES `parent` (`id`)
) ENGINE=InnoDB;

INSERT INTO parent (id, age) VALUES (1, 50);
INSERT INTO parent (id, age) VALUES (2, 60);
INSERT INTO child (id, age, parent_id) VALUES (1, 20, 1);
INSERT INTO child (id, age, parent_id) VALUES (2, 20, 1);

Then, open 2 connections (T1 and T2 – note order is opposite compared to what is shown in SHOW INNODB STATUS):

T1:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=1;
UPDATE parent SET age=age+1 WHERE id=1;

T2:

BEGIN;
UPDATE child SET age=age+1, parent_id=2 WHERE id=2;
UPDATE parent SET age=age+1 WHERE id=1;

<-- T2 Hangs

T1:

UPDATE parent SET age=age+1 WHERE id=2;

<-- Deadlock (T1 completes, T2 is rolled back)

But why does this deadlock? Well, it is due to the foreign key. In fact, this example would not deadlock at all if no foreign key was defined on `parent`.`id`.

So then what exactly is preventing T2 from completing?

Here is what is happening behind the scenes, so-to-speak:

T1#1:

Obtains the following 2 locks:

X lock on `child`  where id=1 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK on parent.id

(Note this S lock means other S locks can be obtained on this row, but not X locks -- which is the crux of this issue).

T1#2:

Obtains the following lock:

X lock on `parent` where id=1 <-- due to the actual "UPDATE parent" statement itself

T2#1:

Obtains the following 2 locks:

X lock on `child`  where id=2 <-- due to the actual "UPDATE child" statement itself
S lock on `parent` where id=2 <-- due to the FK (again, this is okay since it is also a S-lock)

T2#2:

Tries to obtain the following, but hangs due to the existing X-lock from T1#2:

X lock on parent where id=1

T1#3:

Tries to obtain the following lock:

X lock on parent where id=2

However, since there are 2 S-locks on this row already (one from T1 and T2), and T1 now wants an X-lock on the same row, then there is a conflict.

Now this would normally just wait for the S-locks to be released, but since T2 is already "hung" waiting on the other lock to be released from T1, we now have the deadlock.

T1 wins the dispute, T2 rolls back thus releasing its locks, and T1 completes.

So all in all, quite a bit is going on there, but you only see about half of this information from the LATEST DETECTED DEADLOCK section of SHOW ENGINE INNODB STATUS output. And had I not posted the SHOW CREATE TABLE status (and prior tx statements), it'd be unclear as to what happened exactly.

Well, so how do you find out exactly what happened when locking problems happen to you?

SHOW INNODB STATUS only tells you so much. Furthermore, once the deadlock occurs, the winner moves on, and the loser is rolled back. *Meaning*, there is no longer any information about these "transactions" in the output as they are in the "past" now.

Therefore, in general, if you're having any locking issues (deadlocks, lock wait timeouts, hangs due to semaphore waits, and so forth), do yourself a favor and capture all of the following outputs at the time, if possible, to give you the best likelihood in tracking down the issue:

  1. SHOW ENGINE INNODB STATUS
    • This is generally very good, but it can get truncated, and simply may not contain every bit of info you need.
  2. Enable InnoDB Lock Monitor (enable the InnODb Lock Monitor by simply creating any innodb table named innodb_lock_monitor)
    • This logs a lot of extra lock information in the SHOW ENGINE INNODB STATUS output, but it can get truncated too.
  3. Run "mysqladmin debug"
    • Logs all lock info to the error log. Great because it logs all locks (i.e., none truncated) and it logs LOCK TABLE locks, which do not appear in SHOW INNODB STATUS even if on an InnoDB table, because LOCK TABLE is external to the InnoDB storage engine. Not so great because a bit cryptic to read, and I wouldn't solely reply on it, as it's often most helpful in conjuntion with other details).
  4. SHOW FULL PROCESSLIST
    • This will show all connected threads. Specifically, when it comes to 'hidden' locks, it would show a user that has been connected, but idle (but who could have issued a LOCK TABLE command).
  5. Error log
    • Of course, always check out the error log for messages and/or anything out of the ordinary. (Not to mention extra data will be logged to it from "mysqladmin debug" and innodb_lock_monitor.)
  6. SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS, INNODB_LOCK_WAITS, and INNODB_TRX tables
  7. SHOW CREATE TABLE outputs for each table involved

 
 
 

Added a Table of Contents

Wednesday, November 9th, 2011

Not a big deal, but I just added a “Table of Contents” page to my blog to make finding older articles much easier.

I noticed most of my posts are quite lengthy, and it can take a bit of searching/clicking to find an older entry. So unless you happen to recall the ‘month/year’ it was published, which I don’t even remember that, then hopefully this will help.

Really simple, and looks just like this:

My hopes are that this will aid in making some posts easier to find (such as ones about InnoDB Recovery, Recovery with an Individual .ibd, Proxy-related articles, Error-related articles, How-to posts, and so forth).

You can see the full “table of contents” here:

http://www.chriscalender.com/?page_id=399

Happy reading :)

 
 
 

Microseconds and MariaDB

Tuesday, November 8th, 2011

Most of you are all aware of the lack of microsecond support in MySQL.

This has been a long-standing issue:

http://bugs.mysql.com/bug.php?id=8523

*cringe* .. sorry, just my normal reaction after seeing that bug, err feature request, again and again over the past 6+ years ..

However, I think many people do not realize that microseconds are supported by MariaDB in version 5.3+, which for all intensive purposes is a drop-in replacement for MySQL.

So if you need microsecond support for MySQL, you should definitely check out MariaDB.

“In MariaDB 5.3, the TIME, DATETIME, and TIMESTAMP types, along with the temporal functions, CAST and dynamic columns, now support microseconds. The datetime precision of a column can be specified when creating the table with CREATE TABLE”

Also:

“Generally, the precision can be specified for any TIME, DATETIME, or TIMESTAMP column, in parentheses, after the type name. The datetime precision specifies number of digits after the decimal dot and can be any integer number from 0 to 6. If no precision is specified it is assumed to be 0, for backward compatibility reasons.”

I like the “backward compatibility” too.

I won’t fully copy/paste their manual page on this, but if interested, you can read more about it here:

http://kb.askmonty.org/en/microseconds-in-mariadb

And for those not too familiar with MariaDB, or unsure if you should use it in place of MySQL, please check out this page:

http://kb.askmonty.org/en/mariadb-versus-mysql

The above discusses how MariaDB is a drop-in replacement for MySQL, but also covers any/all known user level incompatibilities that one should be aware of (note it’s a short list). ;)

 
 
 

Dealing with Assertion failure in log/log0recv.c – !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)

Sunday, November 6th, 2011

This is a somewhat uncommon error, so I wanted to take a moment and post the error and an explanation in order to make it easier for those who run into this in the future.

The main error is this (full pasted below):

InnoDB: Assertion failure in thread 139838283589376 in file log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)

Basically, this is what it can look like after a power outage. In fact, this is the only time I have seen it, but I won’t say that it is the only way you could encounter it.

How do I deal with this, you ask?

Well, the above error shows that the internal InnoDB dictionary is corrupt.

Unfortunately, in this situation, you must recreate the tablespace. And if you don’t have a current backup + binlogs, then that means you will also need to force InnoDB recovery (i.e., set innodb_force_recovery) in order to dump all of your data.

Once you have the dump, you will need to start from scratch with InnoDB, so to speak. This means deleting (or copy to safe location) all ib_logfile*, ibdata*, *.ibd, and *.frm of all InnoDB tables. Then, remove the innodb_force_recovery option and restart mysqld (allowing it to create new ibdata file(s) and ib_logfile files). And finally, reimport the data you obtained from the mysqldump.

Now, I should reiterate that this was due to a power failure. It is possible for InnoDB to handle this in a more graceful fashion (perhaps less/no corruption), but it invloves setting the following two variables (warning: both will have an impact on performance, so best to test thoroughly before implementing in production):

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

For sync_binlog:

“A value of 1 is the safest choice because in the event of a crash you lose at most one statement or transaction from the binary log. However, it is also the slowest choice (unless the disk has a battery-backed cache, which makes synchronization very fast).”

http://dev.mysql.com/doc/refman/5.5/en/replication-options-binary-log.html#sysvar_sync_binlog

For innodb_flush_log_at_trx_commit:

“When the value is 1 (the default), the log buffer is written out to the log file at each transaction commit and the flush to disk operation is performed on the log file. … The default value of 1 is required for full ACID compliance. You can achieve better performance by setting the value different from 1, but then you can lose up to one second worth of transactions in a crash. With a value of 0, any mysqld process crash can erase the last second of transactions. With a value of 2, only an operating system crash or a power outage can erase the last second of transactions. InnoDB’s crash recovery works regardless of the value. … For the greatest possible durability and consistency in a replication setup using InnoDB with transactions, use innodb_flush_log_at_trx_commit=1 and sync_binlog=1 in your master server my.cnf file.”

However, even with both of these set, do note this caution stated in the manual:

“Caution: Many operating systems and some disk hardware fool the flush-to-disk operation. They may tell mysqld that the flush has taken place, even though it has not. Then the durability of transactions is not guaranteed even with the setting 1, and in the worst case a power outage can even corrupt the InnoDB database. Using a battery-backed disk cache in the SCSI disk controller or in the disk itself speeds up file flushes, and makes the operation safer. You can also try using the Unix command hdparm to disable the caching of disk writes in hardware caches, or use some other command specific to the hardware vendor.”

http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_flush_log_at_trx_commit

And for more details on the how “some disk hardware fool the flush-to-disk operation”, then please see the following links:

http://peter-zaitsev.livejournal.com/12639.html
http://brad.livejournal.com/2116715.html?thread=10292331
http://bugs.mysql.com/bug.php?id=29221

For reference, here is the full error log snippet (after starting up mysqld after the outage):

111026 22:44:35 mysqld_safe Starting mysqld daemon with databases from /mysql/data
111026 22:44:35 [Note] PrimeBase XT (PBXT) Engine 1.0.11-7 Pre-GA loaded...
111026 22:44:35 [Note] Paul McCullagh, PrimeBase Technologies GmbH, http://www.primebase.org
111026 22:44:35 [Note] The server was not shutdown correctly, recovery required
InnoDB: The InnoDB memory heap is disabled
InnoDB: Mutexes and rw_locks use GCC atomic builtins
InnoDB: Compressed tables use zlib 1.2.3
111026 22:44:37  InnoDB: highest supported file format is Barracuda.
InnoDB: Log scan progressed past the checkpoint lsn 363485996068
111026 22:44:37  InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
InnoDB: Doing recovery: scanned up to log sequence number 363487670956
111026 22:44:38  InnoDB: Error: page 4 log sequence number 363490119646
InnoDB: is in the future! Current system log sequence number 363487670956.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.
111026 22:44:38  InnoDB: Assertion failure in thread 139838283589376 in file log/log0recv.c line 1094
InnoDB: Failing assertion: !page || (ibool)!!page_is_comp(page) == dict_table_is_comp(index->table)
InnoDB: We intentionally generate a memory trap.
InnoDB: Submit a detailed bug report to http://bugs.mysql.com.
InnoDB: If you get repeated assertion failures or crashes, even
InnoDB: immediately after the mysqld startup, there may be
InnoDB: corruption in the InnoDB tablespace. Please refer to
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: about forcing recovery.
111026 22:44:38 [ERROR] mysqld got signal 6 ;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.
key_buffer_size=0
read_buffer_size=131072
max_used_connections=0
max_threads=202
threads_connected=0
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 1683122 K
bytes of memory
Hope that's ok; if not, decrease some variables in the equation.
thd: 0x0
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
stack_bottom = (nil) thread_stack 0x40000
111026 22:44:38  InnoDB: Error: page 147457 log sequence number 363487671024
InnoDB: is in the future! Current system log sequence number 363487670956.
InnoDB: Your database may be corrupt or you may have copied the InnoDB
InnoDB: tablespace but not the InnoDB log files. See
InnoDB: http://dev.mysql.com/doc/refman/5.1/en/forcing-recovery.html
InnoDB: for more information.
Fatal signal 11 while backtracing
/usr/sbin/mysqld(my_print_stacktrace+0x2e) [0x99199e]
/usr/sbin/mysqld(handle_segfault+0x353) [0x5e9563]
/lib64/libpthread.so.0(+0xf490) [0x7f3514595490]
/lib64/libc.so.6(gsignal+0x35) [0x7f3513881905]
/lib64/libc.so.6(abort+0x175) [0x7f35138830e5]
/usr/sbin/mysqld() [0x89ee6f]
/usr/sbin/mysqld() [0x8a0322]

 

All in all, hope you never encounter this, but if you do, hopefully this helps you get on the right track sooner than later. :)

 
 
 

MySQL Windows Users – Use Grep to Search MySQL Source Code

Wednesday, November 2nd, 2011

For Windows users, if you try searching the MySQL source code for various strings, you know you will often come up empty with the built-in Windows search.

It wasn’t too long ago and I was searching the source for “MALLOC_OVERHEAD”. My Windows search came up empty, and someone suggested I try grep for Windows.

Well, I downloaded it, extracted it to the location I wanted, and added the .exe to the $PATH.

Now I can grep away!

See the new results when searching for “MALLOC_OVERHEAD”:

C:\mysql-5.5\mysql-5.5>grep -r "MALLOC_OVERHEAD" .
./client/mysql.cc:   (longlong) 2*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0},
./client/mysql.cc:   REQUIRED_ARG, 16384, 1024, 512*1024*1024L, MALLOC_OVERHEAD, 1024, 0},
./client/mysqldump.c:   (longlong) 2L*1024L*1024L*1024L, MALLOC_OVERHEAD, 1024, 0},
./client/mysqldump.c:   MALLOC_OVERHEAD-1024, 1024, 0},
./client/mysqltest.cc:    my_malloc(((PC_MALLOC-MALLOC_OVERHEAD)/
./client/mysqltest.cc:    if (!(pa->str= (uchar*) my_malloc((uint) (PS_MALLOC-MALLOC_OVERHEAD),
./client/mysqltest.cc:    pa->max_count=(PC_MALLOC-MALLOC_OVERHEAD)/(sizeof(uchar*)+
./client/mysqltest.cc:    pa->max_length=PS_MALLOC-MALLOC_OVERHEAD;
./client/mysqltest.cc:    len=(PC_MALLOC*pa->array_allocs - MALLOC_OVERHEAD);
./extra/replace.c:        my_malloc(((PC_MALLOC-MALLOC_OVERHEAD)/
./extra/replace.c:    if (!(pa->str= (uchar*) my_malloc((uint) (PS_MALLOC-MALLOC_OVERHEAD),
./extra/replace.c:    pa->max_count=(PC_MALLOC-MALLOC_OVERHEAD)/(sizeof(uchar*)+
./extra/replace.c:    pa->max_length=PS_MALLOC-MALLOC_OVERHEAD;
./extra/replace.c:    pa->max_length=(pa->length+length+MALLOC_OVERHEAD+PS_MALLOC-1)/PS_MALLOC;
./extra/replace.c:    pa->max_length=pa->max_length*PS_MALLOC-MALLOC_OVERHEAD;
./extra/replace.c:    len=(PC_MALLOC*pa->array_allocs - MALLOC_OVERHEAD);
./include/my_global.h:  something like 1024-MALLOC_OVERHEAD bytes
./include/my_global.h:#define MALLOC_OVERHEAD 8
./include/my_global.h:#define ONCE_ALLOC_INIT           (uint) (4096-MALLOC_OVERHEAD)
./include/my_global.h:#define RECORD_CACHE_SIZE (uint) (64*1024-MALLOC_OVERHEAD)
./include/my_sys.h:#define ALLOC_ROOT_MIN_BLOCK_SIZE (MALLOC_OVERHEAD + sizeof(USED_MEM) + 8 )
Binary file ./MySQL.ncb matches
./mysys/array.c:    alloc_increment=max((8192-MALLOC_OVERHEAD)/element_size,16);
./storage/myisam/ha_myisam.cc:  8192*1024, (long) (MIN_SORT_BUFFER + MALLOC_OVERHEAD), ULONG_MAX, 1);
./storage/myisam/myisamchk.c:    GET_ULL, REQUIRED_ARG, USE_BUFFER_INIT, MALLOC_OVERHEAD,
./storage/myisam/myisamchk.c:    SIZE_T_MAX, MALLOC_OVERHEAD,  IO_SIZE, 0},
./storage/myisam/myisamchk.c:    (long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
./storage/myisam/myisamchk.c:    INT_MAX32, (long) MALLOC_OVERHEAD, (long) 1L, 0},
./storage/myisam/myisamchk.c:    (long) READ_BUFFER_INIT, (long) MALLOC_OVERHEAD,
./storage/myisam/myisamchk.c:    INT_MAX32, (long) MALLOC_OVERHEAD, (long) 1L, 0},
./storage/myisam/myisamchk.c:    (long) SORT_BUFFER_INIT, (long) (MIN_SORT_BUFFER + MALLOC_OVERHEAD),
./storage/myisam/myisamchk.c:    ULONG_MAX, (long) MALLOC_OVERHEAD, (long) 1L, 0},
./storage/myisam/myisamdef.h:#define USE_BUFFER_INIT            (((1024L*512L-MALLOC_OVERHEAD)/IO_SIZE)*IO_SIZE)
./storage/myisam/myisamdef.h:#define READ_BUFFER_INIT   (1024L*256L-MALLOC_OVERHEAD)
./storage/myisam/myisamdef.h:#define SORT_BUFFER_INIT   (2048L*1024L-MALLOC_OVERHEAD)
./storage/myisam/myisamdef.h:#define MIN_SORT_BUFFER            (4096-MALLOC_OVERHEAD)
./storage/myisam/myisampack.c:static uint tree_buff_length= 65536 - MALLOC_OVERHEAD;

 

Unlike the Windows search, which simply returns the file name, this shows the file name *and* the full line of code, therefore sometimes answering my question without even needing to examine the source code.

This full search of the source code (which I had stored in C:\mysql-5.5\mysql-5.5) took only about 2 minutes. This is not bad (and certainly not worse than the Windows search), not to mention there’s no need to index your system, nor build/store the resulting several-GB-search-index-file, aka windows.edb).

Personally, I use the ‘GnuWin32′ grep for Windows, available from SourceForge:

http://gnuwin32.sourceforge.net/packages/grep.htm

I’m sure there are others, but I’ve had great success using many of the GnuWin32 utilities available for Windows.

Happy grepping! :)

 
 
 

Proxy Trace Revisited with MySQL Proxy 0.8.2

Tuesday, November 1st, 2011

Back in January 2010, I wrote an article titled Proxy Trace : Tracing the Variables and Functions Available within MySQL Proxy.

That post was basically a lua script I posted which traces the steps of Proxy, outputting comments as it traverses through the various functions, and then outputs the names and values of [most of] the available variables available within Proxy.

That script worked fine with Proxy 0.7.2 and 0.8.0. It also works just fine with Proxy 0.8.1 (though you need to specify your admin settings in 0.8.1) and 0.8.2 (admin values not necessary).

However, I did go ahead and build it out a little further.

I did not add too much, but I did add the following (the full script, trace.lua, is provided at the end of this post):

  • Outputs backends.type variable
  • Expanded the rows returned outputs (returns more detailed row information)
  • Enhanced error reporting
  • Outputs 70 internal constants that Proxy tracks (Server Commands, Types, Backend State, Packet State, and Return State codes)

I still only start the proxy with the following command:

mysql-proxy --proxy-lua-script=trace.lua

And then in my client, I simply connect, issue 1 query, and exit:

shell> mysql -uroot -pmysql -P4040
mysql> select * from world.city where countrycode='usa' limit 3;
mysql> exit

And here is the new trace.lua output from this session:

C:\Program Files\MySQL\mysql-proxy-0.8.2\bin>mysql-proxy --proxy-lua-script=trace.lua
/* Entering function connect_server() */
   When the proxy accepts a connection from a MySQL client, the
   connect_server() function is called.  There are no arguments
   to the function, but you can use and if necessary manipulate
   the information in the proxy.connection table, which is unique
   to each client session.

   INTERNAL CONSTANTS:

   //Proxy Return State Constants:
   proxy.PROXY_SEND_QUERY                       = 1
   proxy.PROXY_SEND_RESULT                      = 2
   proxy.PROXY_IGNORE_RESULT                    = 4

   //Packet State Constants:
   proxy.MYSQLD_PACKET_OK                       = 0
   proxy.MYSQLD_PACKET_ERR                      = 255
   proxy.MYSQLD_PACKET_RAW                      = 250

   //Backend State/Type Constants:
   proxy.BACKEND_STATE_UNKNOWN                  = 0
   proxy.BACKEND_STATE_UP                       = 1
   proxy.BACKEND_STATE_DOWN                     = 2
   proxy.BACKEND_TYPE_UNKNOWN                   = 0
   proxy.BACKEND_TYPE_RW                        = 1
   proxy.BACKEND_TYPE_RO                        = 2

   //Server Command Constants:
   proxy.COM_SLEEP                              = 0
   proxy.COM_QUIT                               = 1
   proxy.COM_INIT_DB                            = 2
   proxy.COM_QUERY                              = 3
   proxy.COM_FIELD_LIST                         = 4
   proxy.COM_CREATE_DB                          = 5
   proxy.COM_DROP_DB                            = 6
   proxy.COM_REFRESH                            = 7
   proxy.COM_SHUTDOWN                           = 8
   proxy.COM_STATISTICS                         = 9
   proxy.COM_PROCESS_INFO                       = 10
   proxy.COM_CONNECT                            = 11
   proxy.COM_PROCESS_KILL                       = 12
   proxy.COM_DEBUG                              = 13
   proxy.COM_PING                               = 14
   proxy.COM_TIME                               = 15
   proxy.COM_DELAYED_INSERT                     = 16
   proxy.COM_CHANGE_USER                        = 17
   proxy.COM_BINLOG_DUMP                        = 18
   proxy.COM_TABLE_DUMP                         = 19
   proxy.COM_CONNECT_OUT                        = 20
   proxy.COM_REGISTER_SLAVE                     = 21
   proxy.COM_STMT_PREPARE                       = 22
   proxy.COM_STMT_EXECUTE                       = 23
   proxy.COM_STMT_SEND_LONG_DATA                = 24
   proxy.COM_STMT_CLOSE                         = 25
   proxy.COM_STMT_RESET                         = 26
   proxy.COM_SET_OPTION                         = 27
   proxy.COM_STMT_FETCH                         = 28
   proxy.COM_DAEMON                             = 29

   //MySQL Type Constants:
   proxy.MYSQL_TYPE_DECIMAL                     = 0
   proxy.MYSQL_TYPE_NEWDECIMAL                  = 246
   proxy.MYSQL_TYPE_TINY                        = 1
   proxy.MYSQL_TYPE_SHORT                       = 2
   proxy.MYSQL_TYPE_LONG                        = 3
   proxy.MYSQL_TYPE_FLOAT                       = 4
   proxy.MYSQL_TYPE_DOUBLE                      = 5
   proxy.MYSQL_TYPE_NULL                        = 6
   proxy.MYSQL_TYPE_TIMESTAMP                   = 7
   proxy.MYSQL_TYPE_LONGLONG                    = 8
   proxy.MYSQL_TYPE_INT24                       = 9
   proxy.MYSQL_TYPE_DATE                        = 10
   proxy.MYSQL_TYPE_TIME                        = 11
   proxy.MYSQL_TYPE_DATETIME                    = 12
   proxy.MYSQL_TYPE_YEAR                        = 13
   proxy.MYSQL_TYPE_NEWDATE                     = 14
   proxy.MYSQL_TYPE_ENUM                        = 247
   proxy.MYSQL_TYPE_SET                         = 248
   proxy.MYSQL_TYPE_TINY_BLOB                   = 249
   proxy.MYSQL_TYPE_MEDIUM_BLOB                 = 250
   proxy.MYSQL_TYPE_LONG_BLOB                   = 251
   proxy.MYSQL_TYPE_BLOB                        = 252
   proxy.MYSQL_TYPE_VAR_STRING                  = 253
   proxy.MYSQL_TYPE_STRING                      = 254
   proxy.MYSQL_TYPE_TINY                        = 1
   proxy.MYSQL_TYPE_ENUM                        = 247
   proxy.MYSQL_TYPE_GEOMETRY                    = 255
   proxy.MYSQL_TYPE_BIT                         = 16

/* Entering function read_handshake() */
   Handshake information is sent by the server to the client
   after the initial connection (through connect_server()) has
   been made.  read_handshake() doesn't receive a parameter
   anymore. Instead all the data is available in the connection
   tables.

/* Entering function read_auth() */
   The read_auth() function is triggered when an authentication
   handshake is initiated by the client.  read_auth() doesn't
   receive a parameter anymore. Instead all the data is
   available in the connection tables.

/* Entering function read_auth_result() */
   The return packet from the server during authentication is
   captured by read_auth_result().

/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

| Query               = select @@version_comment limit 1
| Time Stamp          = 2011-11-01 19:09:22
| Proxy Version       = 00802
| Lua Version         = Lua 5.1

/* Entering function read_query_result( inj ) */
| os.date                                      = 2011-11-01 19:09:22
| inj.query                                    = ♥select @@version_comment limit 1
| proxy.connection.server.scramble_buffer      = "^PVA!!\"$0d(8#Cg]zrK^"
| proxy.connection.server.mysqld_version       = 50091
| proxy.connection.server.thread_id            = 8
| proxy.connection.client.default_db           =
| proxy.connection.client.username             = root
| proxy.connection.client.scrambled_password   = "╣\\ª≡½bj¼c¬_╫i♫>5úN╡S"
| proxy.connection.client.dst.name             = 127.0.0.1:4040
| proxy.connection.client.dst.address          = 127.0.0.1
| proxy.connection.client.dst.port             = 4040
| proxy.connection.server.dst.name             = 127.0.0.1:3306
| proxy.connection.server.dst.address          = 127.0.0.1
| proxy.connection.server.dst.port             = 3306
| proxy.connection.client.src.name             = 127.0.0.1:50404
| proxy.connection.client.src.address          = 127.0.0.1
| proxy.connection.client.src.port             = 50404
| proxy.connection.server.src.name             = 127.0.0.1:50405
| proxy.connection.server.src.address          = 127.0.0.1
| proxy.connection.server.src.port             = 50405
| inj.query-time                               = 8.366ms
| inj.response-time                            = 8.381ms
| proxy.connection.backend_ndx                 = 1
| #proxy.global.backends                       = 1
| proxy.global.backends[ndx].dst.name          = 127.0.0.1:3306
| proxy.global.backends[ndx].dst.address       = 127.0.0.1
| proxy.global.backends[ndx].dst.port          = 3306
| proxy.global.backends[ndx].connected_clients = 1
| proxy.global.backends[ndx].state             = 1
| proxy.global.backends[ndx].type              = 1
| proxy.global.backends[ndx].pool.max_idle_connections = 0
| proxy.global.backends[ndx].pool.min_idle_connections = 0
| proxy.global.backends[ndx].pool.users[1]     = 0
| result.len                                   = 1
| result.flags.in_trans                        = false
| result.flags.auto_commit                     = true
| result.flags.no_good_index_used              = false
| result.flags.no_index_used                   = false
| result.flags.cursor_exists                   = nil
| result.flags.last_row_sent                   = nil
| result.flags.db_dropped                      = nil
| result.flags.no_backslash_escapes            = nil
| result.flags.metadata_changed                = nil
| result.warning_count                         = 0
| result.query_status                          = 0
| result.fields[1].type                        = 253
| result.fields[1].name                        = @@version_comment
1       MySQL Enterprise Server - Pro Edition (GPL)
| row_count                                    = 1

/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

| Query               = select * from world.city where countrycode='usa' limit 3
| Time Stamp          = 2011-11-01 19:09:24
| Proxy Version       = 00802
| Lua Version         = Lua 5.1

/* Entering function read_query_result( inj ) */
| os.date                                      = 2011-11-01 19:09:24
| inj.query                                    = ♥select * from world.city where countrycode='usa' limit 3
| proxy.connection.server.scramble_buffer      = "^PVA!!\"$0d(8#Cg]zrK^"
| proxy.connection.server.mysqld_version       = 50091
| proxy.connection.server.thread_id            = 8
| proxy.connection.client.default_db           =
| proxy.connection.client.username             = root
| proxy.connection.client.scrambled_password   = "╣\\ª≡½bj¼c¬_╫i♫>5úN╡S"
| proxy.connection.client.dst.name             = 127.0.0.1:4040
| proxy.connection.client.dst.address          = 127.0.0.1
| proxy.connection.client.dst.port             = 4040
| proxy.connection.server.dst.name             = 127.0.0.1:3306
| proxy.connection.server.dst.address          = 127.0.0.1
| proxy.connection.server.dst.port             = 3306
| proxy.connection.client.src.name             = 127.0.0.1:50404
| proxy.connection.client.src.address          = 127.0.0.1
| proxy.connection.client.src.port             = 50404
| proxy.connection.server.src.name             = 127.0.0.1:50405
| proxy.connection.server.src.address          = 127.0.0.1
| proxy.connection.server.src.port             = 50405
| inj.query-time                               = 8.706ms
| inj.response-time                            = 8.729ms
| proxy.connection.backend_ndx                 = 1
| #proxy.global.backends                       = 1
| proxy.global.backends[ndx].dst.name          = 127.0.0.1:3306
| proxy.global.backends[ndx].dst.address       = 127.0.0.1
| proxy.global.backends[ndx].dst.port          = 3306
| proxy.global.backends[ndx].connected_clients = 1
| proxy.global.backends[ndx].state             = 1
| proxy.global.backends[ndx].type              = 1
| proxy.global.backends[ndx].pool.max_idle_connections = 0
| proxy.global.backends[ndx].pool.min_idle_connections = 0
| proxy.global.backends[ndx].pool.users[1]     = 0
| result.len                                   = 1
| result.flags.in_trans                        = false
| result.flags.auto_commit                     = true
| result.flags.no_good_index_used              = false
| result.flags.no_index_used                   = true
| result.flags.cursor_exists                   = nil
| result.flags.last_row_sent                   = nil
| result.flags.db_dropped                      = nil
| result.flags.no_backslash_escapes            = nil
| result.flags.metadata_changed                = nil
| result.warning_count                         = 0
| result.query_status                          = 0
| result.fields[1].type                        = 3
| result.fields[1].name                        = ID
1       3793
2       New York
3       USA
4       New York
5       8008278
1       3794
2       Los Angeles
3       USA
4       California
5       3694820
1       3795
2       Chicago
3       USA
4       Illinois
5       2896016
| row_count                                    = 3

/* Entering function read_query( packet ) */
| o = [backends] userdata: 019136F8 (type=userdata)
[config] table: 01CAE2C0 (type=table)

/* Entering function disconnect_client() */
Client 8 has disconnected.

Here is the client session for completeness (and perhaps some one may find it useful):

C:\Users\Chris>mysql -uroot -pmysql -P4040
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 5.0.91-enterprise-gpl-nt MySQL Enterprise Server - Pro Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> select * from world.city where countrycode='usa' limit 3;
+------+-------------+-------------+------------+------------+
| ID   | Name        | CountryCode | District   | Population |
+------+-------------+-------------+------------+------------+
| 3793 | New York    | USA         | New York   |    8008278 |
| 3794 | Los Angeles | USA         | California |    3694820 |
| 3795 | Chicago     | USA         | Illinois   |    2896016 |
+------+-------------+-------------+------------+------------+
3 rows in set (0.15 sec)

mysql> exit
Bye

C:\Users\Chris>

And here is the full script, trace.lua:

function connect_server()
	print("/* Entering function connect_server() */")
	print("   When the proxy accepts a connection from a MySQL client, the")
	print("   connect_server() function is called.  There are no arguments")
	print("   to the function, but you can use and if necessary manipulate")
	print("   the information in the proxy.connection table, which is unique")
	print("   to each client session.\n")
	print("   INTERNAL CONSTANTS:\n")
	print("   //Proxy Return State Constants:")
	print("   proxy.PROXY_SEND_QUERY                       = "
				.. proxy.PROXY_SEND_QUERY)
	print("   proxy.PROXY_SEND_RESULT                      = "
				.. proxy.PROXY_SEND_RESULT)
	print("   proxy.PROXY_IGNORE_RESULT                    = "
				.. proxy.PROXY_IGNORE_RESULT .. "\n")
	print("   //Packet State Constants:")
	print("   proxy.MYSQLD_PACKET_OK                       = "
				.. proxy.MYSQLD_PACKET_OK)
	print("   proxy.MYSQLD_PACKET_ERR                      = "
				.. proxy.MYSQLD_PACKET_ERR)
	print("   proxy.MYSQLD_PACKET_RAW                      = "
				.. proxy.MYSQLD_PACKET_RAW .. "\n")
	print("   //Backend State/Type Constants:")
	print("   proxy.BACKEND_STATE_UNKNOWN                  = "
				.. proxy.BACKEND_STATE_UNKNOWN)
	print("   proxy.BACKEND_STATE_UP                       = "
				.. proxy.BACKEND_STATE_UP)
	print("   proxy.BACKEND_STATE_DOWN                     = "
				.. proxy.BACKEND_STATE_DOWN)
	print("   proxy.BACKEND_TYPE_UNKNOWN                   = "
				.. proxy.BACKEND_TYPE_UNKNOWN)
	print("   proxy.BACKEND_TYPE_RW                        = "
				.. proxy.BACKEND_TYPE_RW)
	print("   proxy.BACKEND_TYPE_RO                        = "
				.. proxy.BACKEND_TYPE_RO .. "\n")
	print("   //Server Command Constants:")
	print("   proxy.COM_SLEEP                              = "
				.. proxy.COM_SLEEP)
	print("   proxy.COM_QUIT                               = "
				.. proxy.COM_QUIT)
	print("   proxy.COM_INIT_DB                            = "
				.. proxy.COM_INIT_DB)
	print("   proxy.COM_QUERY                              = "
				.. proxy.COM_QUERY)
	print("   proxy.COM_FIELD_LIST                         = "
				.. proxy.COM_FIELD_LIST)
	print("   proxy.COM_CREATE_DB                          = "
				.. proxy.COM_CREATE_DB)
	print("   proxy.COM_DROP_DB                            = "
				.. proxy.COM_DROP_DB)
	print("   proxy.COM_REFRESH                            = "
				.. proxy.COM_REFRESH)
	print("   proxy.COM_SHUTDOWN                           = "
				.. proxy.COM_SHUTDOWN)
	print("   proxy.COM_STATISTICS                         = "
				.. proxy.COM_STATISTICS)
	print("   proxy.COM_PROCESS_INFO                       = "
				.. proxy.COM_PROCESS_INFO)
	print("   proxy.COM_CONNECT                            = "
				.. proxy.COM_CONNECT)
	print("   proxy.COM_PROCESS_KILL                       = "
				.. proxy.COM_PROCESS_KILL)
	print("   proxy.COM_DEBUG                              = "
				.. proxy.COM_DEBUG)
	print("   proxy.COM_PING                               = "
				.. proxy.COM_PING)
	print("   proxy.COM_TIME                               = "
				.. proxy.COM_TIME)
	print("   proxy.COM_DELAYED_INSERT                     = "
				.. proxy.COM_DELAYED_INSERT)
	print("   proxy.COM_CHANGE_USER                        = "
				.. proxy.COM_CHANGE_USER)
	print("   proxy.COM_BINLOG_DUMP                        = "
				.. proxy.COM_BINLOG_DUMP)
	print("   proxy.COM_TABLE_DUMP                         = "
				.. proxy.COM_TABLE_DUMP)
	print("   proxy.COM_CONNECT_OUT                        = "
				.. proxy.COM_CONNECT_OUT)
	print("   proxy.COM_REGISTER_SLAVE                     = "
				.. proxy.COM_REGISTER_SLAVE)
	print("   proxy.COM_STMT_PREPARE                       = "
				.. proxy.COM_STMT_PREPARE)
	print("   proxy.COM_STMT_EXECUTE                       = "
				.. proxy.COM_STMT_EXECUTE)
	print("   proxy.COM_STMT_SEND_LONG_DATA                = "
				.. proxy.COM_STMT_SEND_LONG_DATA)
	print("   proxy.COM_STMT_CLOSE                         = "
				.. proxy.COM_STMT_CLOSE)
	print("   proxy.COM_STMT_RESET                         = "
				.. proxy.COM_STMT_RESET)
	print("   proxy.COM_SET_OPTION                         = "
				.. proxy.COM_SET_OPTION)
	print("   proxy.COM_STMT_FETCH                         = "
				.. proxy.COM_STMT_FETCH)
	print("   proxy.COM_DAEMON                             = "
				.. proxy.COM_DAEMON .. "\n")
	print("   //MySQL Type Constants:")
	print("   proxy.MYSQL_TYPE_DECIMAL                     = "
				.. proxy.MYSQL_TYPE_DECIMAL)
	print("   proxy.MYSQL_TYPE_NEWDECIMAL                  = "
				.. proxy.MYSQL_TYPE_NEWDECIMAL)
	print("   proxy.MYSQL_TYPE_TINY                        = "
				.. proxy.MYSQL_TYPE_TINY)
	print("   proxy.MYSQL_TYPE_SHORT                       = "
				.. proxy.MYSQL_TYPE_SHORT)
	print("   proxy.MYSQL_TYPE_LONG                        = "
				.. proxy.MYSQL_TYPE_LONG)
	print("   proxy.MYSQL_TYPE_FLOAT                       = "
				.. proxy.MYSQL_TYPE_FLOAT)
	print("   proxy.MYSQL_TYPE_DOUBLE                      = "
				.. proxy.MYSQL_TYPE_DOUBLE)
	print("   proxy.MYSQL_TYPE_NULL                        = "
				.. proxy.MYSQL_TYPE_NULL)
	print("   proxy.MYSQL_TYPE_TIMESTAMP                   = "
				.. proxy.MYSQL_TYPE_TIMESTAMP)
	print("   proxy.MYSQL_TYPE_LONGLONG                    = "
				.. proxy.MYSQL_TYPE_LONGLONG)
	print("   proxy.MYSQL_TYPE_INT24                       = "
				.. proxy.MYSQL_TYPE_INT24)
	print("   proxy.MYSQL_TYPE_DATE                        = "
				.. proxy.MYSQL_TYPE_DATE)
	print("   proxy.MYSQL_TYPE_TIME                        = "
				.. proxy.MYSQL_TYPE_TIME)
	print("   proxy.MYSQL_TYPE_DATETIME                    = "
				.. proxy.MYSQL_TYPE_DATETIME)
	print("   proxy.MYSQL_TYPE_YEAR                        = "
				.. proxy.MYSQL_TYPE_YEAR)
	print("   proxy.MYSQL_TYPE_NEWDATE                     = "
				.. proxy.MYSQL_TYPE_NEWDATE)
	print("   proxy.MYSQL_TYPE_ENUM                        = "
				.. proxy.MYSQL_TYPE_ENUM)
	print("   proxy.MYSQL_TYPE_SET                         = "
				.. proxy.MYSQL_TYPE_SET)
	print("   proxy.MYSQL_TYPE_TINY_BLOB                   = "
				.. proxy.MYSQL_TYPE_TINY_BLOB)
	print("   proxy.MYSQL_TYPE_MEDIUM_BLOB                 = "
				.. proxy.MYSQL_TYPE_MEDIUM_BLOB)
	print("   proxy.MYSQL_TYPE_LONG_BLOB                   = "
				.. proxy.MYSQL_TYPE_LONG_BLOB)
	print("   proxy.MYSQL_TYPE_BLOB                        = "
				.. proxy.MYSQL_TYPE_BLOB)
	print("   proxy.MYSQL_TYPE_VAR_STRING                  = "
				.. proxy.MYSQL_TYPE_VAR_STRING)
	print("   proxy.MYSQL_TYPE_STRING                      = "
				.. proxy.MYSQL_TYPE_STRING)
	print("   proxy.MYSQL_TYPE_TINY                        = "
				.. proxy.MYSQL_TYPE_TINY)
	print("   proxy.MYSQL_TYPE_ENUM                        = "
				.. proxy.MYSQL_TYPE_ENUM)
	print("   proxy.MYSQL_TYPE_GEOMETRY                    = "
				.. proxy.MYSQL_TYPE_GEOMETRY)
	print("   proxy.MYSQL_TYPE_BIT                         = "
				.. proxy.MYSQL_TYPE_BIT .. "\n")
end

function read_handshake()
	print("/* Entering function read_handshake() */")
	print("   Handshake information is sent by the server to the client")
	print("   after the initial connection (through connect_server()) has")
	print("   been made.  read_handshake() doesn't receive a parameter")
	print("   anymore. Instead all the data is available in the connection")
	print("   tables.\n")
end

function read_auth()
	print("/* Entering function read_auth() */")
	print("   The read_auth() function is triggered when an authentication")
	print("   handshake is initiated by the client.  read_auth() doesn't")
	print("   receive a parameter anymore. Instead all the data is")
	print("   available in the connection tables.\n")
end

function read_auth_result()
	print("/* Entering function read_auth_result() */")
	print("   The return packet from the server during authentication is")
	print("   captured by read_auth_result().\n")
end

function read_query( packet )
	print("/* Entering function read_query( packet ) */")
	o = ""
	for k, v in pairs(proxy.global) do
		o = o .. (("[%s] %s (type=%s)\n"):format(k, tostring(v), type(v)))
	end
	print("| o = " .. o)
	if string.byte(packet) == proxy.COM_QUERY then
		proxy.queries:append(1, packet, {resultset_is_needed = true} )
        print("| Query               = "
			.. string.sub(packet, 2))
		print("| Time Stamp          = "
			.. os.date('%Y-%m-%d %H:%M:%S'))
		print("| Proxy Version       = "
			.. string.format("%05x", proxy.PROXY_VERSION))
		print("| Lua Version         = "
			.. _VERSION)
		return proxy.PROXY_SEND_QUERY
	end
end

function read_query_result( inj )
	print("\n/* Entering function read_query_result( inj ) */")
	local res         = assert(inj.resultset)
	local raw_len     = assert(res.raw):len()
	local packet      = assert(inj.query)
	local flags       = res.flags
	local backend_ndx = proxy.connection.backend_ndx
	print("| os.date                                      = "
		.. os.date('%Y-%m-%d %H:%M:%S'))
	print("| inj.query                                    = "
		.. inj.query)
	print("| proxy.connection.server.scramble_buffer      = "
		.. string.format("%q",proxy.connection.server.scramble_buffer))
	print("| proxy.connection.server.mysqld_version       = "
		.. proxy.connection.server.mysqld_version)
	print("| proxy.connection.server.thread_id            = "
		.. proxy.connection.server.thread_id)
	print("| proxy.connection.client.default_db           = "
		.. proxy.connection.client.default_db)
	print("| proxy.connection.client.username             = "
		.. proxy.connection.client.username)
	print("| proxy.connection.client.scrambled_password   = "
		.. string.format("%q", proxy.connection.client.scrambled_password))
	print("| proxy.connection.client.dst.name             = "
		.. proxy.connection.client.dst.name)
	print("| proxy.connection.client.dst.address          = "
		.. proxy.connection.client.dst.address)
	print("| proxy.connection.client.dst.port             = "
		.. proxy.connection.client.dst.port)
	print("| proxy.connection.server.dst.name             = "
		.. proxy.connection.server.dst.name)
	print("| proxy.connection.server.dst.address          = "
		.. proxy.connection.server.dst.address)
	print("| proxy.connection.server.dst.port             = "
		.. proxy.connection.server.dst.port)
	print("| proxy.connection.client.src.name             = "
		.. proxy.connection.client.src.name)
	print("| proxy.connection.client.src.address          = "
		.. proxy.connection.client.src.address)
	print("| proxy.connection.client.src.port             = "
		.. proxy.connection.client.src.port)
	print("| proxy.connection.server.src.name             = "
		.. proxy.connection.server.src.name)
	print("| proxy.connection.server.src.address          = "
		.. proxy.connection.server.src.address)
	print("| proxy.connection.server.src.port             = "
		.. proxy.connection.server.src.port)
	print("| inj.query-time                               = "
		.. (inj.query_time / 1000) .. "ms")
	print("| inj.response-time                            = "
		.. (inj.response_time / 1000) .. "ms")
	print("| proxy.connection.backend_ndx                 = "
		.. proxy.connection.backend_ndx)
	print("| #proxy.global.backends                       = "
		.. #proxy.global.backends)
	print("| proxy.global.backends[ndx].dst.name          = "
		.. proxy.global.backends[backend_ndx].dst.name)
	print("| proxy.global.backends[ndx].dst.address       = "
		.. proxy.global.backends[backend_ndx].dst.address)
	print("| proxy.global.backends[ndx].dst.port          = "
		.. proxy.global.backends[backend_ndx].dst.port)
	print("| proxy.global.backends[ndx].connected_clients = "
		.. proxy.global.backends[backend_ndx].connected_clients)
	print("| proxy.global.backends[ndx].state             = "
		.. proxy.global.backends[backend_ndx].state)
	print("| proxy.global.backends[ndx].type              = "
		.. proxy.global.backends[backend_ndx].type)
	print("| proxy.global.backends[ndx].pool.max_idle_connections = "
		.. proxy.global.backends[backend_ndx].pool.max_idle_connections)
	print("| proxy.global.backends[ndx].pool.min_idle_connections = "
		.. proxy.global.backends[backend_ndx].pool.min_idle_connections)
	print("| proxy.global.backends[ndx].pool.users[1]     = "
		.. proxy.global.backends[backend_ndx].pool.users[1].cur_idle_connections)
	print("| result.len                                   = "
		.. raw_len)
	print("| result.flags.in_trans                        = "
		.. tostring(res.flags.in_trans))
	print("| result.flags.auto_commit                     = "
		.. tostring(res.flags.auto_commit))
	print("| result.flags.no_good_index_used              = "
		.. tostring(res.flags.no_good_index_used))
	print("| result.flags.no_index_used                   = "
		.. tostring(res.flags.no_index_used))
	print("| result.flags.cursor_exists                   = "
		.. tostring(res.flags.cursor_exists))
	print("| result.flags.last_row_sent                   = "
		.. tostring(res.flags.last_row_sent))
	print("| result.flags.db_dropped                      = "
		.. tostring(res.flags.db_dropped))
	print("| result.flags.no_backslash_escapes            = "
		.. tostring(res.flags.no_backslash_escapes))
	print("| result.flags.metadata_changed                = "
		.. tostring(res.flags.metadata_changed))
	print("| result.warning_count                         = "
		.. res.warning_count)
	print("| result.query_status                          = "
		.. res.query_status)
	if res.affected_rows then
		print("| result.affected_rows                         = "
			.. res.affected_rows)
		print("| result.insert_id                             = "
			.. res.insert_id)
	end
	if res.query_status == proxy.MYSQLD_PACKET_ERR then
		print("| result.err.code                              = "
			.. res.raw:byte(2) + (res.raw:byte(3) * 256))
		print("| result.err.sql_state                         = "
			.. string.format("%q", res.raw:sub(5, 9)))
		print("| result.err.msg                               = "
			.. string.format("%q", res.raw:sub(10)))
	end
	if res.query_status == proxy.MYSQLD_PACKET_OK then
		print("| result.fields[1].type                        = "
			.. res.fields[1].type)
		print("| result.fields[1].name                        = "
			.. res.fields[1].name)
		local row_count = 0
		for row in inj.resultset.rows do
			for i,v in ipairs(row) do print(i,v) end
			row_count = row_count + 1
		end
		print("| row_count                                    = "
			.. row_count)
	end
	print("\n")
end

function disconnect_client()
	print("/* Entering function disconnect_client() */")
	if proxy.connection.server.thread_id then
		print("Client " .. proxy.connection.server.thread_id
			.. " has disconnected.")
	end
end

I hope this helps, and happy proxy tracing. ;)


Period Panties by Period Panteez Menstrual Underwear Menstruation PMS Panty