MySQL Notes

Note that MySQL is currently installed a service. It can be viewed via Settings - Control Panel - Administrative Tools - Services. This can be used to start and stop the database service.

See MySQL 5.0 Documentation for in-depth information on running and using MySQL.

Starting MySQL

The MySQL server can be started manually from the command line. This can be done on any version of Windows.

To start the mysqld server from the command line, you should start a console window (a ``DOS'' window) and enter this command:

shell> C:\mysql\bin\mysqld

On non-NT versions of Windows, this will start mysqld in the background. That is, after the server starts, you should see another command prompt. If you start the server this way on Windows NT, 2000, or XP, the server will run in the foreground and no command prompt will appear until the server exits. Because of this, you should open another console window to run client programs while the server is running.

Stopping MySQL

You can stop the MySQL server by executing this command:

shell> C:\mysql\bin\mysqladmin -u root shutdown

This invokes the MySQL administrative utility mysqladmin to connect to the server and tell it to shut down. The command connects as root, which is the default administrative account in the MySQL grant system. Please note that users in the MySQL grant system are wholly independent from any login users under Windows.

If mysqld doesn't start, check the error log to see whether the server wrote any messages there to indicate the cause of the problem. The error log is located in the `C:\mysql\data' directory. It is the file with a suffix of `.err'. You can also try to start the server as mysqld --console; in this case, you may get some useful information on the screen that may help solve the problem.

The last option is to start mysqld with --standalone --debug. In this case mysqld will write a log file `C:\mysqld.trace' that should contain the reason why mysqld doesn't start. See section D.1.2 Creating Trace Files.

Use mysqld --help to display all the options that mysqld understands!

Developing Applications

Removed 4.1. Installed 5.0.24 as a service. Starts automatically. Only user (so far) is root with a password of "sakai".

The ODBC connector (Connector/J) is available here

The JAR for this connect is also present on the Sakai Maven repository in mysql/mysql-connector-java-5.0.3.

Sakai Maven dependency:

<dependency>
	<groupId>mysql</groupId>
	<artifactId>mysql-connector-java</artifactId>
	<version>5.0.3</version>
</dependency>

I had to create a user called admin with the following command:

• create user 'admin' identified by 'sakai'; (password is sakai)

Then granted them full access rights using this command:

• grant all on sakai_sis.* to admin;

When developing providers in the Sakai test environment, the JDBC connector in SakaiSis needs to changed to reference the IP address of my laptop (instead of MarkLaptop).

MySQL Tools

The interactive SQL shell is called mysql and can be run from a command line:

Unfortnately, this doesn't seem to be the correct password, even though it seems to work on a JDBC connector.

Useful Expressions

To create a new database:

• create database DB_NAME;

To show all databases:

• show databases;

Restoring Dumps

When you use MySQL for your persistent storage, there's a bit of a
trick to restoring your mysqldump'd data back into a live database:

If you try to restore your mysql data straight from the mysqldump
output you'll get errors like this:

1. mysqldump realSakaiDB > mysqldump-file.sql
2. echo "create database mirrordatabase default character set utf8" | mysql
3. mysql mirrordatabase < mysqldump-file.sql
   ERROR 1005 (HY000): Can't create table
   './mirrordatabase/ASN_AP_ITEM_ACCESS_T.frm' (errno: 150)

The problem is that the table definition includes foreign keys that
are referring to other tables that don't exist yet. You could conjure
up a script to go through the mysqldump file and rearrange them in
dependency order, which would be a fun exercise for third-year CS
students, or...

SOLUTION:

Prefix your data-load SQL with "SET FOREIGN_KEY_CHECKS = 0" (and turn
it back on after you're all loaded up):

1. mysql mirrordatabase
   mysql> SET FOREIGN_KEY_CHECKS = 0;
   Query OK, 0 rows affected (0.00 sec)

mysql> \. mysqldump-file.sql
snip

mysql> SET FOREIGN_KEY_CHECKS = 1;
Query OK, 0 rows affected (0.00 sec)

will@serensoft.com

More Notes

Include the mysql database connector JAR in the binary distribution.

Include in the exercise notes how to add the connector and JARs.

Need to enable network access to SIS database by editing the mysql database, user table to use"%" instead of "localhost" for host column. This wild card allows any connecting host to have access to the database.

TCP/IP connections can be made to the MySQL database on port 3307 (the default was 3306). MySQL binary commands should be generally available from console on laptop. Database was NOT installed as a windows service.