MySQL Unknown InnoDB Options on Command Line

Question

I'm trying to set some innodb settings when starting mysql on the command line:

mysql -p*password* --skip-innodb_doublewrite

mysql -p*password* --innodb_flush_log_at_trx_commit=2

Which appear to be possible from the MySQL docs:

Command-Line Format --innodb-doublewrite ... This variable can be turned off with --skip-innodb_doublewrite

Command-Line Format --innodb_flush_log_at_trx_commit[=#]

However, when executing I get:

mysql: unknown option '--skip-innodb_doublewrite'

mysql: unknown variable 'innodb_flush_log_at_trx_commit=0'

What's going on? Have I completely missinterpreted the docs?

I'm using MySQL 5.5.45

@Barmar Well, that would explain it! Put that in an answer and I'll accept. — Arth
– Arth, Commented Feb 1, 2017 at 17:13

Barmar · Accepted Answer · 2017-02-01 17:30:32Z

1

These are server options, not client options, so they need to be specified on the mysqld command line. However, as the documentation says:

Many system variables can be changed at runtime (see Section 6.1.6.2, “Dynamic System Variables”).

If you go to that page, the first line says:

Many server system variables are dynamic and can be set at runtime using SET GLOBAL or SET SESSION.

There's a table that lists all the dynamic variables. innodb_doublewrite is not one of them, so it can only be specified at server startup (either with a command-line argument or in the configuration file). But innodb_flush_log_at_trx_commit is listed as a global variable, so you can do:

mysql -u root -p"password" -e "set global innodb_flush_log_at_trx_commit = 0"

answered Feb 1, 2017 at 17:30

Barmar

789k57 gold badges555 silver badges669 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bogdan · Accepted Answer · 2019-06-23 02:02:03Z

0

If anyone is receiving the message unknown variable 'innodb_flush_log_at_trx_commit=0' trying to set this configuration in a my.ini or mysql.cnf file, the line innodb_flush_log_at_trx_commit=0 must be written under [mysqld] section.

answered Jun 23, 2019 at 2:02

Bogdan

1163 bronze badges

Collectives™ on Stack Overflow

MySQL Unknown InnoDB Options on Command Line

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related