MySQL

• MySQL 4.1 Manual
• MySQL Survival Guide
• MySQL and Spring Connection Pooling
• Getting a rownum
• FOUND_ROWS() to save doing count(*) statement in pagination
• copying db to another machine and mysqldump
• Latin1 to UTF-8
• Use show warnings; to display warnings from previous command

Installing on MacOS 10.5.5

Very easy with MySQL 5.1.30. Default installation, startup task installation and prefs pane install all work great. To change the root password:

mysqladmin -u root password supersecretpassword

Upgrade Lesson

When upgrading my registration database from ISAM to InnoDB, I ran into an error - this INDEX was causing a problem:

INDEX reg_address (reg_address(1024))

To change it, I used the following commands. The address length of 1024 seems a bit excessive anyhow.

drop index reg_address on registration;

alter table registration ENGINE=InnoDB;

alter table registration modify column reg_address VARCHAR(255) null;

create index reg_address on registration (reg_address(255));

---- migration steps ---

alter table wan_game drop column wgm_public_key;
alter table wan_profile drop column wpr_status_code;

According to the MySQL manual index prefix for InnoDB is limited to 767, which may explain the issue. The manual also said it was limited to 1000 for ISAM, which begs the question why the original index worked in the first place.

Useful commands to see the type/stats of each table:

show databases;
show tables;
desc table [table name];
show table status;
show index from wan_game; 
select * from information_schema.statistics; (list all indices)
show create table wan_profile;
show create database poker;

To startup (on Mac)

sudo /Library/StartupItems/MySQLCOM/MySQLCOM start

To shutdown:

mysqladmin -u root -p shutdown

Backup poker database:

mysqldump --tab=~/pokerdb poker -u root --password=supertopsecret

Import from mysql

load data infile 'path/to/wan_game.txt' replace into table wan_game;

Change root password

mysqladmin -u root password [new password]

Performance Tuning

• MySQL Performance Tuning Blog

explain [select ...]
analyze table [table-name]

Doing a query with a generated "id"

Sometimes it is helpful to have a unique id on a select query. This is how one can create it in MySQL (similar to rownum function in Oracle). Note the use of coalesce, which selects the first not-NULL value in the list, for assigning the first value.

SELECT @rownum:=coalesce(@rownum+1,1) AS id, bar, SUM(quantity) AS total
FROM sometable
GROUP BY bar
ORDER BY total;

Unfortunately, in MySQL, you can't use a query with parameters to create a view. Hopefully MySQL will change this in the future.