How to Add to MySQL Configuration on Travis CI When Not Sudo

Question

I am trying to figure out how to change MySQL configuration before a Travis CI test run. We are using the "sudo:false" directive, I think to use containers...I'm not the best devops person.

But even when I set sudo to true, I can't restart MySQL after I try to add lines to "/etc/mysql/my.cnf".

So,

  - cat "some/directory/my.cnf" | sudo tee -a /etc/mysql/my.cnf
  - sudo /etc/init.d/mysql restart

gives me: "start: Job failed to start", but I don't even want to use sudo. For PHP config, I can do something like:

  - echo "apc.shm_size=256M" >> ~/.phpenv/versions/$(phpenv version-name)/etc/php.ini

but I can't see anything in my home folder about MySQL.

I also know about:

  - mysql -e "SET GLOBAL innodb_buffer_pool_size = 512M;"

but the things I want to set give me:

  ERROR 1238 (HY000) at line 1: Variable 'innodb_buffer_pool_size' is a read only variable

So, I'm at a loss on how to accomplish changing MySQL configuration on Travis CI, and every internet search and method I've tried has failed me.

Answer 1

i present a .travis.yml configuration that allows you to run and restart mysqld without using sudo - the logic is along the lines of:

• just out of interest, why can't you use sudo?

• obtain the generic linux .tar.gz from https://dev.mysql.com/downloads/mysql/ in before_script using wget - store the .tar.gz within a directory that will be cached - don't do this if the file already exists

• unpack the .tar.gz to the cached directory - don't do this if the unpack target already exists

• scrub the error log and recreate the .cnf file

• now in script, start mysqld for the first time, wait for a bit, check that it's running, then stop it

• make any change to the config you want

• start mysqld for the second time, wait for a bit, check the config change, then stop it

• the proof .travis.yml (mysql 5.6.40 generic linux 64-bit) is here: https://github.com/knyrb/mysql-travisci/blob/master/.travis.yml - here is a (non-working) rough idea of the contents:

before_script:
  <..snip..>
  - if [ -f 'scripts/mysql_install_db' ]; then ./scripts/mysql_install_db --defaults-file="$TCI_D_MYSQL_ROOT/my.cnf"; fi

script:
  <..snip..>
  - bin/mysqld --defaults-file="$TCI_D_MYSQL_ROOT/my.cnf" --socket=/tmp/.thesock &
  - bin/mysqladmin --defaults-file="$TCI_D_MYSQL_ROOT/my.cnf" --socket=/tmp/.thesock ping
  - export TCI_MYSQL_PID_1="`cat $TCI_D_MYSQL_ROOT/mysql.pid`"
  - kill -SIGTERM $TCI_MYSQL_PID_1
  <..snip..>

after_success:
  <..snip..>

after_failure:
  <..snip..>
  - cat "$TCI_D_MYSQL_ROOT/mysql.err"

---

discussion left for posterity:

as you've identified, there are certain configuration parameters in mysql that can only be set prior to the server starting viz. anything that is 'dynamic: no' in table 14.13 for mysql 5.6 - i restate the obvious fwiw: the php parameters are dynamically inflicted on the php process via php.ini

---

(never tested this - don't know if it works - the constraint was 'no sudo' and this method clearly requires sudo) first off, have you tried using the service binary (https://linux.die.net/man/8/service) instead of a direct call to mysql? viz. https://docs.travis-ci.com/user/database-setup/#MySQL-5.7 :

before_install:
- sudo mysql -e "use mysql; update user set authentication_string=PASSWORD('new_password') where User='root'; update user set plugin='mysql_native_password';FLUSH PRIVILEGES;"
- sudo mysql_upgrade
- sudo service mysql restart

---

what case(s) are you testing by changing these mysql parameters prior to each test's runtime as distinct from configuring them 'permanently' in the test instance db server?

• assuming your mysqld is firing up at container start via services: mysql? could you configure travis to start mysqld specifically in before_install or script and specify the parameters that you want at start time, with mysqld running under the ci framework travis (i think this should allow it to start/stop/restart etc without any sudo use, but i haven't tested it) (see: https://github.com/knyrb/mysql-travisci for a proof of concept)

  • see: https://docs.travis-ci.com/user/database-setup/#MySQL, which hints the possibility (although with jackrabbit),

  • no: must run mysqld as root to switch user though you would run mysqld --user=travis (https://dev.mysql.com/doc/refman/5.6/en/server-options.html#option_mysqld_user), or maybe in the cnf file with [mysqld] user=travis directive (https://dev.mysql.com/doc/mysql-security-excerpt/5.6/en/changing-mysql-user.html)

• (never tested this but it should work as well) another option might be to have a github project for each specific environment test you want to perform, with mysqld configured to run with its limiting parameters already set: your tests would be per container, rather than with interim restarts (possibly insane, depending on how bad you want it!)

• (if your tests are performance related, remember to give the db a/ some warmup queries before you start measuring)

• i ask purely out of interest: when it comes to testing different environments and configuration limits, my humble opinion will always favour a 'more is more' approach!

---

(never tested this either) not sure if you're using a recent enough version of mysql for this option to work, but i see there is an instruction in mysql 8 restart

this might work for you if you can have a mysql stored proc trigger a restart of the database service from a priveleged account calling a stored proc - you would call this stored proc then wait for the server to restart, say with one of the following methods:

• a call to sleep did the job in the proof (it won't account for failure - but the ci build fails if any process returns != 0)

• if your using a local unix/ domain socket, wait on the file /tmp/mysql.sock (or your equivalent) using any of the methods here: https://superuser.com/questions/878640/unix-script-wait-until-a-file-exists while .. sleep, inotifywait from the inotify tools; or from within python using inotify.adapters.InotifyTree().eventgen() (probably overkill!)

• attempt a connection with a generous timeout, mysql -S/tmp/mysql.sock --connect-timeout=# --user=youruser --password

• https://stackoverflow.com/a/21189440/4036945 (wait .. sleep using curl to hit up a webservice that in turn connects to the db: maybe your app has a 'status' page? and maybe it waits a while and retries a db connection before it returns to the http client)

• https://unix.stackexchange.com/a/5279 (wait .. sleep using netcat, netstat and lsof, but these i think will all require sudo unless you start mysql from within the ci framework, in which netstat from the ci framework should provide the info)

Answer 2

innodb_buffer_pool_size cannot be dynamically changed until MySQL 5.7.5. What version are you using?

Before that version, here are some options:

• Modify /etc/my.cnf (or wherever it is located)
• Assuming my.cnf has an 'include' at the end, then temporarily add a file to the directory mentioned. It needs 2 lines [mysqld] and innodb_buffer_pool_size=512M; then restart mysqld.
• Add --innodb_buffer_pool_size=512M to the launching of mysqld.

Other "VARIABLES" may, or may not, be dynamically settable. Research each one separately.