MySQL

Setup

Installation

1. Download MySQL Community Server at http://dev.mysql.com/downloads/mysql/, Windows ZIP Archive
2. Extract the ZIP Archive to a path, e.g. C:\Program Files\MySQL (aliased ~)
3. Open my-default.ini in the MySQL directory and edit it: remove # before basedir, datadir, port:

basedir = ~
datadir = ~\data
port = 8888

4. Open command line at ~\bin and run mysqld --console
5. Open a new command line at ~\bin and run mysql -u root -p to login as root user (no password initially)
6. mysql> status to see the current connection status
7. Ctrl + C to shut down the server

MySQL Workbench

Download MySQL Workbench at http://dev.mysql.com/downloads/workbench/, Windows ZIP Archive.

Troubleshooting

Problem: When Ctrl + C shuts down the server, connections can still be established.

Resolution: Run mysqladmin -u root shutdown from ~\bin

Reference: http://dev.mysql.com/doc/refman/5.1/en/windows-start-service.html

Problem: After changing my-default.ini to set the server port to 8888 from 3306, the server restarted with port unchanged.

Resolution: Rename my-default.ini to my.ini.

Data Types

• UNSIGNED integers → only positive, double the max value range.
• SIGNED integers → supports negative numbers.
• Use INT unless you need big values → BIGINT for > 2 billion.

AUTO_INCREMENT

• Gaps happen if rows are deleted or transactions fail — can’t easily “fill holes”.
• Reset: ALTER TABLE table_name AUTO_INCREMENT = 1;

Row & Column Limits

• Max columns: 4096 (practically much less).
• Max row size: ~65,535 bytes logical limit, InnoDB often ≤ 8126 bytes (per row in index page).
• Large VARCHAR with utf8mb4 (4 bytes/char) can quickly hit limits → consider TEXT.

Common Errors

• Lock wait timeout: Increase innodb_lock_wait_timeout or optimize queries.
• Row size too large: Reduce column size, use TEXT, change ROW_FORMAT.
• Incorrect string value: Mismatched character sets, ensure utf8mb4 all the way.

Encoding

• utf8 in MySQL = 3-byte subset (no emojis).
• utf8mb4 = full Unicode (emojis, rare symbols).

InnoDB Notes

• Default page size: 16KB (check with SHOW VARIABLES LIKE 'innodb_page_size';).
• Row format: DYNAMIC or COMPRESSED helps with large rows.