theMySQLGuy Podcast

Podcast: Play in new window | Download

 

 

Starting out

Once MySQL is installed you should assign a root password to the server, drop the guest account and remove certain built-in databases you don’t need.

$ mysql -u root

mysql> DELETE FROM mysql.user WHERE User = '';

mysql> UPDATE mysql.user SET Password = PASSWORD('some_pass') WHERE User = 'root';

mysql> DELETE FROM mysql.user WHERE Host <> 'localhost';

mysql> UPDATE mysql.user SET Host = '%';

mysql> create database modes;

mysql> drop database test;

mysql> FLUSH PRIVILEGES;

mysql> quit;

$ mysql -u root -psome_pass

mysql>

$ mysql -u root -p

Enter password:

mysql>

SQL Mode

The SQL Mode you chose is very important because it affects how your MySQL server will behave in certain situations.

 

By Default it runs in “no mode”. Which is kind of a free-for-all mode. This mode is very forgiving…maybe too forgiving for some “Enterprise” applications.

 

$ mysql -u root -psome_pass 

mysql> use modes; 

mysql> CREATE TABLE drivers (id TINYINT UNSIGNED, name CHAR(5));

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO drivers (id, name) VALUES (255,'Doug');

Query OK, 1 row affected (0.02 sec) 

mysql> select * from drivers;

+------+------+

| id   | name |

+------+------+

|  255 | Doug |

+------+------+

1 row in set (0.00 sec) 

mysql> INSERT INTO drivers (id, name) VALUES (259,'Christos');

Query OK, 1 row affected, 2 warnings (0.00 sec) 

mysql> select * from drivers;

+------+-------+

| id   | name  |

+------+-------+

|  255 | Doug  |

|  255 | Chris |

+------+-------+

2 rows in set (0.00 sec) 

mysql> DROP TABLE drivers;

Query OK, 0 rows affected (0.07 sec) 

mysql> quit; 

To avoid the above scenario, you must assign an SQL Mode to your MySQL server. I like the traditional mode. It would raise an error if you tried to run the above commands.

 

Add sql_mode=traditional in your my.cnf/my.ini file, restart the server and that will solve the issue. It would give the following results:

 

$ mysql -u root -psome_pass

mysql> use modes;

mysql> CREATE TABLE drivers (id TINYINT UNSIGNED, name CHAR(5));

Query OK, 0 rows affected (0.03 sec)

mysql> INSERT INTO drivers (id, name) VALUES (255,'Doug');

Query OK, 1 row affected (0.02 sec)

mysql> select * from drivers;

+------+------+

| id   | name |

+------+------+

|  255 | Doug |

+------+------+

1 row in set (0.00 sec)

mysql> INSERT INTO drivers (id, name) VALUES (259,'Christos');

ERROR 1264 (22003): Out of range value adjusted for column 'id' at row 1

mysql> DROP TABLE drivers;

Query OK, 0 rows affected (0.07 sec)

mysql> quit; 

There are more SQL Modes to choose from. Click here to get a complete list of modes and a more in-depth explanation of each one.

 

Bye! Thanks for all the fish!

christos@themysqlguy.com