Version 46 (modified by mfreemon, 7 years ago) (diff)



MySQL installation

The current version of the MySQL server on LSST10 is 5.1.44.

To upgrade the version:

  • Stop the current MySQL server daemon (service mysqld stop)
  • Build and install from the newest source (in /home/dgehrig/mysql-5.1.44)
    configure --with-plugins=innobase
    make install
  • upgrade the databases using mysql_upgrade -u root -p
  • restart the MySQL daemon using service mysqld start

The new binaries should now be at /usr/local/bin.

Add the non-standard mysql library location to the dynamic linker run time bindings.

  • Edit /etc/
    # cat /etc/
  • Reload by executing /sbin/ldconfig.

Install MySQL-python from source as follows:

# cd /root
# tar xvfz MySQL-python-1.2.3c1.tar.gz
# cd MySQL-python-1.2.3c1
# python build
# python install

MySQL data files

MySQL currently locates its data files at /usr/data/mysql1/mysql_data.


The mysql root password is stored in root's home directory, file ~root/.my.cnf. Be sure this file is chmod 600. This allows us to avoid putting the password in various automated scripts (such as the backup script).

# cat /root/.my.cnf


The configuration file is located at /etc/my.cnf.local (so that an rpm install or remove will not disturb it). A symlink for /etc/my.cnf points to this file.

max_allowed_packet = 16M
key_buffer_size = 1GB


# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
# Commented out.  See JIRA issue LSST-254
# old_passwords=1



The startup script, /etc/init.d/mysqld.local, contains a ".local" in order to avoid an rpm install or remove from disturbing it. A symlink for mysqld points to this file. Its contents are the same as the standard rpm-provided file, with the following modifications:

  • /usr/bin is changed to /usr/local/bin for these executables:
    • mysql_install_db
    • mysqld_safe
    • mysqladmin
    • my_print_defaults
  • The mysqld_safe option basedir is set to /usr/local

Starting and stoping MySQL

MySQL will automatically start on server boot for lsst10:

# grep mysqld /etc/rc.local
service mysqld start

To stop MySQL server:

service mysqld stop

To start MySQL server:

service mysqld start

Number of Connections

If you encounter errors in numerous Slices of the form:

File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/", 
     line 440, in tryProcess stageObject.process()
File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/", 
     line 60, in process self._output()
File "/u/ncsa/rplante/stack/Linux64/pex_harness/3.3.4/python/lsst/pex/harness/", 
     line 148, in _output logLoc)
File "/u/ncsa/rplante/stack/Linux64/daf_persistence/3.3.7/python/lsst/daf/persistence/", line 1349, in getPersistStorage 
return _persistenceLib.Persistence_getPersistStorage(*args)
LsstCppException: 0: lsst::pex::exceptions::RuntimeErrorException  
thrown at src/ in void lsst::daf::persistence::DbStorageImpl::
error(const std::string&, bool) 0: Message: Unable to connect to MySQL database:  - * 
Too many connections

Increase the number of connections.

To check the current setting:

show variables like 'max_connections';
| Variable_name   | Value |
| max_connections | 100   |

To increase it, set:

max_connections = 500

to /etc/my.cnf under the [mysqld] section.

Then restart mysql.

If you want to avoid restarting the server, execute

SET GLOBAL max_connections = 500

inside mysql client (as superuser)

Open Files Limit

Increase the number of open files allowed for mysql so that the mysqldump backups will run successfully.

In the /etc/my.cnf.local file, add the following (1024 is the default):


This requires mysqld restart.

Ensure enough space for tmpdir

If there is not enough space in /tmp for mysql, then an alternate tmp directory can be set for mysql to use by specifying tmpdir in the my.cnf configuration file.

tmpdir=<the alternate tmp directory>

under [mysqld]

This requires mysqld restart.

Building the UDFs

source $LSST_HOME/loadLSST.csh
setup mysqlclient
cd /lsst/home/becla/qservWorker/udf
g++ -fPIC -shared -o -I$MYSQLCLIENT_DIR/include MySqlSpatialUdf.c
mysql -u adm_becla -p mysql < createMySqlUdfs_.sql

UTC on database backups

Due to a bug in the CSV engine, the timestamps on the general_log are incorrect. The option --skip-tz-utc is being used on the mysqldump command in the database backup script to circumvent this problem. The risk is that a backup/restore across a daylight saving time boundary will be screwed up. Hopefully, a future version of mysql will fix this problem (the current version as of this writing is 5.1.44).

Ignore the above (but retained for historical reference). We are migrating the two tables affected to another storage engine.