A Little Noise


Undelete rows with the binary log

Can you use the binary logs to undo a DELETE? Maybe, if you're using ROW format logging. The difference between a delete and an insert event could be just one byte - the one that maps it as a DELETE_ROWS_EVENT or a WRITE_ROWS_EVENT. Let's try it.

I've already populated this table with a few rows:

CREATE TABLE `undo_test` (
`id` int(10) unsigned NOT NULL AUTO_INCREMENT,
`v` varchar(20) DEFAULT NULL,
`b` blob,

Save a hash so we can see if the undo really works later:

mysql -e "SELECT * FROM test.undo_test" | md5sum > before.md5

Delete an unwanted row:

DELETE FROM undo_test;
Query OK, 1693 rows affected (0.14 sec)

Oops! Forgot the WHERE clause! And of course I don't have any recent backups. Is the data really gone?

mysql> select count(*) from undo_test;
| count(*) |
| 0 |

But maybe there's hope. Find the delete event in the binary log.

| Log_name   | Pos    | Event_type  | Server_id | End_log_pos | Info
| bin.000001 |      4 | Format_desc |         1 |         120 | Server ver: 5.6.20-log, Binlog ver: 4
| bin.000001 |    516 | Query       |         1 |         588 | BEGIN
| bin.000001 |    588 | Table_map   |         1 |         647 | table_id: 70 (test.undo_test)
| bin.000001 |    647 | Delete_rows |         1 |        8859 | table_id: 70
| bin.000001 |   8859 | Delete_rows |         1 |       16932 | table_id: 70
| bin.000001 |  16932 | Delete_rows |         1 |       25040 | table_id: 70
| bin.000001 |  25040 | Delete_rows |         1 |       33244 | table_id: 70
| bin.000001 |  33244 | Delete_rows |         1 |       41408 | table_id: 70
| bin.000001 |  41408 | Delete_rows |         1 |       49549 | table_id: 70
| bin.000001 |  49549 | Delete_rows |         1 |       57764 | table_id: 70
| bin.000001 |  57764 | Delete_rows |         1 |       65956 | table_id: 70
| bin.000001 |  65956 | Delete_rows |         1 |       74096 | table_id: 70
| bin.000001 |  74096 | Delete_rows |         1 |       82237 | table_id: 70
| bin.000001 |  82237 | Delete_rows |         1 |       90445 | table_id: 70
| bin.000001 |  90445 | Delete_rows |         1 |       98586 | table_id: 70
| bin.000001 |  98586 | Delete_rows |         1 |      106728 | table_id: 70
| bin.000001 | 106728 | Delete_rows |         1 |      114886 | table_id: 70
| bin.000001 | 114886 | Delete_rows |         1 |      123085 | table_id: 70
| bin.000001 | 123085 | Delete_rows |         1 |      131290 | table_id: 70
| bin.000001 | 131290 | Delete_rows |         1 |      139476 | table_id: 70
| bin.000001 | 139476 | Delete_rows |         1 |      146945 | table_id: 70 flags: STMT_END_F
| bin.000001 | 146945 | Xid         |         1 |      146976 | COMMIT /* xid=36 */

The delete starts at position 516 and ends at 146976. We can pass those positions to mysqlbinlog to get just this delete event, along with the necessary header so mysql knows how to interpret the event.

From sql/log_event.h we see that a delete is 0x20 and an insert is 0x1E. This gawk program will parse the mysqlbinlog output and make the necessary changes.


	base64cmd = "xxd -r -p | base64"
	inHexSection = 0
	handledHexSection = 0
	inBinlogSection = 0 

/# Position  Timestamp/ && !handledHexSection { 
	inHexSection = 1 

!handledHexSection && inHexSection && ($7 == "20") { 
	hexString = $3 $4 $5 $6 $7 $8 $9 $10 $11
	print hexString |& base64cmd
	close(base64cmd, "to")
	base64cmd |& getline oldBase64String

	$7 = "1E" 
	hexString = $3 $4 $5 $6 $7 $8 $9 $10 $11
	print hexString |& base64cmd
	close(base64cmd, "to")
	base64cmd |& getline newBase64String

	inHexSection = 0
	handledHexSection = 1

/BINLOG '/,/'\/\*!\*\/;/ { 
	inBinlogSection = 1 
	handledHexSection = 0

/'\/\*!\*\/;/ { inBinlogSection = 0 }

inBinlogSection && substr($0, 1, 12) == oldBase64String { $0 = newBase64String substr($0, 13) }

{ print $0 }

Pass the event through that code, and then back into mysql:

mysqlbinlog --hexdump --start-position=516 --stop-position=146976 bin.000001 | gawk -f undelete.awk | mysql

Compare it to the original hash to see if it's really back:

mysql -e "SELECT * FROM test.undo_test" | md5sum -c before.md5
-: OK

Looks like it worked.

Filed under: MySQL 12 Comments

Ambiguous column names (not that kind)

Time for another quiz to impress your friends, if you have that kind of friends.

The manual page in question

mysql --skip-enable-disable-column-names=0 -e "select 1"
mysql --skip-disable-enable-column-names=0 -e "select 1"
mysql --loose-disable-enable-skip-column-names=0 -e "select 1"

What's the result? An error about unknown options? A warning? Column headers enabled? Disabled?

Answer ▼

Filed under: MySQL Gotchas No Comments

Mental Activity

the verbal quibbling in which great minds, for want of better occupation, frequently expended all their energies.

Pike, Luke Owen, M.A. "Woman and Political Power." Popular Science May 1872. Google Books Web. 29 Apr. 2014.

Was he talking about metaphysical arguments from the middle ages, or YouTube comments?


Science and Immortality

A summary of the article "Science and Immortality", by Rev. T. W. Fowle, Popular Science May 1872.

Modern science affects belief in two ways:

  1. Believers in science require proof from observable facts alone.
  2. Believers in science are biased against any supernatural explanation.

Four common arguments for belief in immortality:

  1. It is an "original intuition", meaning we were created with the belief.
  2. It is a universal belief.
  3. It follows necessarily from the existence of God.
  4. It is essential as a motive for morality.


  1. It could have arisen after creation, and must have if Evolution is true as many men of science believe.
  2. It is not universally believed. Moses made no indication of believing in an afterlife, Buddha believed that eternity was non-existence, Julius Caesar believed in no-afterlife enough to consider death too good for his enemies.
  3. To a man of science demanding proof, the idea of God is just an attempt to explain our past which we don't understand, and immortality is likewise an attempt to explain our future.
  4. Buddhists are moral, and immortality is not their motive. Pharaoh believed in immortality, and yet the resulting behavior was immoral. So it is neither necessary nor sufficient for morality.

It would be sufficient proof for life after death if a man were resurrected. Jesus was resurrected, and would be sufficient proof if the account were believed. Those who testified to the Resurrection may have been mistaken, but there is no reason to assume them to be liars. They were multiple men, who were able to see something amazing and miraculous and describe it in a sober and measured manner. We take historical testimony as factual all the time and rely upon it to be true. The only reason anyone doubts the testimony of the apostles is that it requires a supernatural element. When the miraculous is involved, then a certain turn of mind will simply assume that any testimony is always mistaken.

There is the Religious and, for lack of a better word, the "Rational," at war within each of us, and we tend to one side or the other. Do we dwell on the present natural world or on hopes for the future? The rise of scientific thinking will cause doubts about the supernatural, but it will also help clear away erroneous dogmas. The bias to one side or the other will be created and sustained by moral means. Each side, religious and scientific, has moral value to offer.

There is no intellectual shortcut to the Christian faith; we cannot logically or scientifically prove the way to Christ (and here are my favorite quotes from this article: You can’t win by shouting). The conclusion is to live as Christ taught, and so win by faith what we cannot by force.


You can’t win by shouting

...denunciations of moral turpitude only harden the hearts of men who ask for the bread of evidence and receive stones in the shape of insults.

...all attempts on the part of religion to confute the "skeptic" by purely intellectual methods are worse than useless. There is no intellectual short cut to the Christian faith.... It is not because men love the truth, but because they hate their enemies, that in things religious they desire to have what they call an overwhelming preponderance of argument on their side of the question, the possession of which enables them to treat their opponents as knaves or fools or both.

Fowle, Rev. T. W. "Science and Immortality." Popular Science May 1872. Google Books Web. 26 Apr. 2014.


Fools think the government can fix everything.

Minds in which the conceptions of social actions are thus rudimentary, are also minds ready to harbor wild hopes of benefits to be achieved by administrative agencies. In each such mind there seems to be the unexpressed postulate that every evil in a society admits of cure; and that the cure lies within the reach of law.

[A superstitious conciousness] may even wonder how any being can reverence a thing shaped with his own hands; and yet it readily entertains subtler forms of the same feelings.... there is a tacit supposition that a government moulded by themselves has some efficiency beyond that naturally possessed by a certain group of citizens subsidized by the rest of the citizens.

...the instinct of self-preservation in each institution soon becomes dominant over every thing else; and maintains it when it performs some quite other function that that intended, or no function at all.

Spencer, Herbert. "The Study of Sociology." Popular Science May 1872. Google Books Web. 26 Apr. 2014.


Pre-5.0.30 ADDTIME()

For when you want to add times greater than 839 hours.

CREATE FUNCTION ADDTIME_OLD(d datetime, t varchar(12))
RETURNS datetime
CONCAT(IF(LEFT(t, 1) = '-', '-', ''), '00:', SUBSTRING_INDEX(t, ':', -2))

(Almost the same. I'm not going to figure out how to do the "you can use any delimiter you want" that MySQL supports.)

Filed under: MySQL No Comments

mysqldump privileges required

"mysqldump requires at least the SELECT privilege for dumped tables, SHOW VIEW for dumped views, TRIGGER for dumped triggers, and LOCK TABLES if the --single-transaction option is not used. Certain options might require other privileges as noted in the option descriptions."
- http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html

Format Default Privileges Required
--add-drop-database Off  
--add-drop-table On in --opt  
--add-drop-trigger Off  
--add-locks On in --opt  
--all-databases Off SELECT, SHOW DATABASES ON *.*
--allow-keywords Off  
--apply-slave-statements Off  
--bind-address=ip_address Off  
--comments On  
--compact Off  
--compatible=name[,name,...] Off  
--complete-insert Off  
--create-options On in --opt  
--databases Off  
--debug[=debug_options] Off  
--debug-check Off  
--debug-info Off  
--default-auth=plugin Off  
--default-character-set=charset_name utf8/latin1  
--delayed-insert Off  
--delete-master-logs Off SUPER ON *.*
--disable-keys On in --opt  
--dump-date On in --comments  
--dump-slave[=value] Off SUPER or REPLICATION CLIENT ON *.*
--events Off EVENT
--extended-insert On in --opt  
--fields-enclosed-by=string '' in --tab  
--fields-escaped-by '\\' in --tab  
--fields-optionally-enclosed-by=string Off  
--fields-terminated-by=string '\t' in --tab  
--flush-logs Off RELOAD ON *.*
--flush-privileges Off  
--help Off  
--hex-blob Off  
--host localhost  
--ignore-table=db_name.tbl_name Off  
--include-master-host-port Off  
--insert-ignore Off  
--lines-terminated-by=string '\n' in --tab  
--lock-all-tables Off LOCK TABLES ON *.*
--lock-tables On in --opt LOCK TABLES
--log-error=file_name Off  
--login-path=name Off controlled at OS level
--master-data Off RELOAD ON *.*
--max_allowed_packet=value 24MB  
--net_buffer_length=value 1022KB  
--no-autocommit Off  
--no-create-db Off  
--no-create-info Off  
--no-data Off  
--no-set-names Off  
--no-tablespaces Off  
--opt On  
--order-by-primary Off  
--password[=password] Off  
--pipe Off  
--plugin-dir=path Off  
--port=port_num 3306  
--quick On in --opt  
--quote-names On  
--replace Off  
--result-file=file Off  
--routines Off SELECT ON mysql.proc
--set-charset On  
--set-gtid-purged=value Auto  
--single-transaction Off  
--skip-add-drop-table Off in --opt  
--skip-add-locks Off in --opt  
--skip-comments Off  
--skip-compact On  
--skip-disable-keys Off in --opt  
--skip-extended-insert Off in --opt  
--skip-opt Off  
--skip-quick Off in --opt  
--skip-quote-names Off  
--skip-set-charset Off  
--skip-triggers Off  
--skip-tz-utc Off  
--ssl-ca=file_name Off  
--ssl-capath=dir_name Off  
--ssl-cert=file_name Off  
--ssl-cipher=cipher_list Off  
--ssl-crl=file_name Off  
--ssl-crlpath=dir_name Off  
--ssl-key=file_name Off  
--ssl-verify-server-cert Off  
--tab=path Off  
--tables Off  
--triggers On TRIGGER since 5.1.23, SUPER ON *.* earlier
--tz-utc On  
--user=user_name system user on Linux, 'ODBC' on Windows  
--verbose Off  
--version Off  
--where='where_condition' Off  
--xml Off  
Filed under: MySQL FAQ 3 Comments

Where’s my line?

mysql -e "select * from test.t where d < '2013-07-17 17:00:00'"
| d                   |
| 2013-07-17 15:34:19 |

mysqldump -t --compact test t --where="d < '2013-07-17 17:00:00'"
(no output)

Where's my line?

Hint ▼

Filed under: MySQL Gotchas No Comments

Five Number Summary

From Freenode: how do you generate a five number summary in MySQL? There is no "median" aggregate function built in. You could do some clever things involving self joins or temporary tables, or build an aggregate UDF - see the comments section in the manual for those approaches.

Here's another way using a single query. Be sure to set group_concat_max_len high enough for your data, and since it relies on string manipulation, it's probably not a good choice if your data is millions of rows.

First, a helper function to get the Nth element of a comma-delimited string, just to make the query shorter:

CREATE FUNCTION LIST_ELEM(inString text, pos int) 

Now, fetching the min, max, median, and first and third quartiles (computing method 2) for each group:



  GROUP_CONCAT(data ORDER BY data) AS dataSet,

  MIN(data) AS min,

    + LIST_ELEM(GROUP_CONCAT(data ORDER BY data), FLOOR(COUNT(*)/4 + 1))
  ) / 2 AS q1,

    + LIST_ELEM(GROUP_CONCAT(data ORDER BY data), FLOOR(COUNT(*)/2 + 1))
  ) / 2 AS median,

  ) / 2 AS q3,

  MAX(data) AS max

GROUP BY groupId;
| groupId | dataSet             | min  | q1   | median | q3   | max  |
|       1 | 0,0,1,2,13,27,61,63 |    0 |  0.5 |    7.5 |   44 |   63 |
|       2 | 0,0,1,2,25          |    0 |    0 |      1 |    2 |   25 |
Filed under: MySQL No Comments