MySQL/notes

Notes

Repairing

mysqlcheck -A --auto-repair -p

SQL error messages

1271 - Illegal mix of collations for operation 'concat'
- http://www.issociate.de/board/post/274187/CONCAT_with_IF.html
- When the problem expression was a CONCAT(...) function, doing CAST(CONCAT(...) AS BINARY) fixed it.

munged MySQL installation

If you can't reinstall or remove MySQL because the "/etc/init.d/mysql start" command fails during (un)installation, check for rogue "mysql" and "mysql-safe" processes which aren't getting stopped by "/etc/init.d/mysql stop".

debian-start

The /etc/mysql/debian-start file appears to contain a minor error, at least on the MySQL install I just reinstalled. It sets the variable MYCHECK, and then tries to invoke the command to which it is set by saying just "mycheck;", resulting in the error message "/etc/mysql/debian-start: line 17: mycheck: command not found" although the mysql daemon is apparently restarted successfully. To fix the error, change "mycheck;" to "$mycheck;". (This also has the benefit of actually running whatever command MYCHECK is set to, which is typically some kind of integrity check to help keep your databases from becoming corrupted.)

external connections

If mysql refuses to respond to connection attempts from anywhere but the local machine, you need to edit /etc/mysql/my.conf and comment out the following line:

bind-address  = 127.0.0.1

and then restart mysqld.

miscellaneous

To restart the mysql daemon (mysqld) when you don't have a user with SHUTDOWN privileges but you do have system root:

sudo /etc/init.d/mysql restart

Command-line Client Example

editing a blank table: To work around the Query Browser bug (fixed in the latest version) which won't let you edit a blank table, create the first entry using MySQL's command line interface:

woozle@camilla:~ $ mysql -hyourhost -uyourusername -pyourpassword
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12531702 to server version: 4.1.13-standard

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql> use yourdatabasename
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> INSERT INTO tablename () VALUES();
Query OK, 1 row affected (0.05 sec)

mysql>

...where yourhost is the name of your MySQL server, and can be anything that can resolve to an IP address, e.g. localhost, yourmachinename, yourserverdomain.com, or an actual IP address (I think).

SQL Examples

creating a table: If the Query Browser crashes when you try to create or edit a table, use the command line interface to create the table:

create table table_name (field_name field_type, field_name field_type

REDIRECT Template:arg/opt);

Example: <sql>create table example (key varchar(255));</sql>

More examples: importing data into ZenCart
Documentation: CREATE TABLE Syntax

making a field autonumbered:

ALTER TABLE tablename MODIFY fieldname fieldtype AUTO_INCREMENT;

Example: <sql>ALTER TABLE Tracks_Queued MODIFY id INTEGER AUTO_INCREMENT;</sql>

changing the name of a field (column):

ALTER TABLE tablename CHANGE oldname newname new_fieldtype;

This also changes the field's definition, if new_fieldtype is not the same as the field's prior definition.

Example: <sql>ALTER TABLE cat_items CHANGE isInStock qtyInStock INT COMMENT 'number of pieces currently in stock (calculated from stock table)';</sql>

Documentation: ALTER TABLE Syntax