mysql: unknown variable 'innodb_lock_wait_timeout=500'

Question

I have a query, that takes time to execute, because I'm importing the geonames table

LOAD DATA LOCAL INFILE 'allCountries.txt'
INTO TABLE geoname
CHARACTER SET 'UTF8'
(geonameid, name, asciiname, alternatenames, latitude, longitude, fclass, fcode, country, cc2, admin1, admin2, admin3, admin4, population, elevation, gtopo30, timezone, moddate);

However, if I execute the query, I always get

Lock wait timeout exceeded; try restarting transaction
vagrant@homestead:~/work/homestead/project/bin$

So I tried to increase the lock time out. The documentation states here: http://dev.mysql.com/doc/refman/5.0/en/innodb-parameters.html#sysvar_innodb_lock_wait_timeout that the CLI-parameter is --innodb_lock_wait_timeout=#, but when I set it in my query

mysql -ugeonames -psecret --local-infile=1 --innodb_lock_wait_timeout=500 geonames < geonames_import_data.sql

I'm getting the error

mysql: unknown variable 'innodb_lock_wait_timeout=500'

But my MySQL version mysql Ver 14.14 Distrib 5.6.19, for debian-linux-gnu (x86_64) using EditLine wrapper should support this setting, right?

When I set it globally like SET GLOBAL innodb_lock_wait_timeout = 500; and then read the value with show variables like '%lock_wait%'; I'm getting the correct values

+--------------------------+----------+
| Variable_name            | Value    |
+--------------------------+----------+
| innodb_lock_wait_timeout | 500      |
| lock_wait_timeout        | 31536000 |
+--------------------------+----------+

How do I use it on the command line though?

My Versions:

select @@version // 5.6.19-0ubuntu0.14.04.1
mysql --version // mysql  Ver 14.14 Distrib 5.6.19, for debian-linux-gnu (x86_64) using  EditLine wrapper

Answer 1

You probably have an older version of mysql or at least the cli is old, probably from the v5.0 series. If you check out the documentation you linked yourself, then you can see the following at innodb_lock_wait_timeout description:

Name :innodb_lock_wait_timeout

Variable Scope: Global

Dynamic Variable: No

The dynamic variable no means that the variable cannot be set on the fly, only in the my.ini and the value will only change if the server is restarted. You will get the error message you described if you try to change the variable dynamically.