A Little Noise

1Oct/140

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
29Apr/140

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?

28Apr/140

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.

Rebuttal:

  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.

26Apr/140

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.

26Apr/140

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.

25Sep/130

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
DETERMINISTIC
RETURN ADDTIME(
d + INTERVAL SUBSTRING_INDEX(t, ':', 1) HOUR,
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
23Jul/132

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 *.*
SUPER or REPLICATION CLIENT 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 2 Comments
17Jul/130

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
25Apr/130

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) 
RETURNS TEXT DETERMINISTIC 
RETURN SUBSTRING_INDEX(SUBSTRING_INDEX(inString, ',', pos), ',', -1);


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

SELECT 

  groupId,

  GROUP_CONCAT(data ORDER BY data) AS dataSet,

  MIN(data) AS min,

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

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

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

  MAX(data) AS max

FROM t 
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
6Feb/130

Default user

It came up twice in two days: if you do not specify the user name when connecting, what gets picked?

The manual says:
"On Unix, most MySQL clients by default try to log in using the current Unix user name as the MySQL user name, but that is for convenience only."
http://dev.mysql.com/doc/refman/5.6/en/user-names.html

"The default user name is ODBC on Windows or your Unix login name on Unix."
http://dev.mysql.com/doc/refman/5.6/en/connecting.html

Let's be a little more specific. The relevant section of code is in libmysql/libmysql.c

On Linux, we check the following in this order:
- if (geteuid() == 0), user is "root"
- getlogin()
- getpwuid(geteuid())
- environment variables $USER, $LOGNAME, $LOGIN
If none of those return non-NULL results, use "UNKNOWN_USER"

On Windows:
- environment variable $USER
If that's not set, use "ODBC".

I wondered why on Windows we check $USER but not $USERNAME. I gather that it's an ODBC thing.

Filed under: MySQL No Comments