I wanted to let you know all about a blog and podcast I have been reading/listening to lately. Its called OurSQL and the author is Sheeri Kritzer.I have been in email contact with Sheeri and she seems like a really great person. Why wouldn’t she be? She’s a MySQL DBA after all! The self proclaimed “She-BA”.
Check out her blog at sheeri.net and her Podcast OurSQL on iTunes.
One of the best features of MySQL is the fact that you can have a log of all queries that take longer than n seconds.
To activate this log start MySQL with the –log-slow-queries[=file_name] option, or add the previous option to your my.cnf or my.ini and restart the server:
[mysqld] ... log-slow-queries[=file_name]
You can specify a file name for the slow-query-log or it will be called host_name-slow.log. By default the log is in the data directory, unless you specify an absolute path with the file name.
By default a slow query is one that takes 10 seconds. You can change that by specifying long_query_time in either during startup or in the my.cnf or my.ini file.
[mysqld] ... log-slow-queries long_query_time=8
Viewing the Log
The log is in text format and can easily be viewed by any text editor and looks like this:
# Time: 070116 5:16:35 # User@Host: nvusr[nvusr] @ app01 [10.30.5.226] # Query_time: 21 Lock_time: 0 Rows_sent: 20 Rows_examined: 4078677 SELECT ectransactions.*, interchanges.interchange_datetime as transaction_datetime, interchanges.partner_name, interchanges.direction, functional_groups.functional_group_control_numb, fo_name, functional_organization_qualid, partner_name, partner_qualid, interchanges.interchange_control_number, operators.name as operator_name, trading_participants.name as client_name FROM ectransactions left join functional_groups ON ectransactions.functional_group_id=functional_groups.id left join interchanges ON ectransactions.interchange_id=interchanges.id left join trading_participants ON ectransactions.trading_participant_id=trading_participants.id left join operators ON ectransactions.operator_id=operators.id ORDER BY client_name asc, transaction_datetime desc LIMIT 0, 20;
However, its a lot more readable with the mysqldumpslow command, whose output looks like this:
Count: 1 Time=24.00s (24s) Lock=0.00s (0s) Rows=20.0 (20), nvusr[nvusr]@tqapp02 SELECT ectransactions.*, interchanges.interchange_datetime as transaction_datetime, interchanges.partner_name, interchanges.direction, functional_groups.functional_group_control_numb, fo_name, functional_organization_qualid, partner_name, partner_qualid, interchanges.interchange_control_number, operators.name as operator_name, trading_participants.name as client_name FROM ectransactions left join functional_groups ON ectransactions.functional_group_id=functional_groups.id left join interchanges ON ectransactions.interchange_id=interchanges.id left join trading_participants ON ectransactions.trading_participant_id=trading_participants.id left join operators ON ectransactions.operator_id=operators.id ORDER BY transaction_datetime asc, description desc LIMIT N, N Count: 2 Time=22.00s (44s) Lock=0.00s (0s) Rows=20.0 (40), nvusr[nvusr]@2hosts SELECT ectransactions.*, interchanges.interchange_datetime as transaction_datetime, interchanges.partner_name, interchanges.direction, functional_groups.functional_group_control_numb, fo_name, functional_organization_qualid, partner_name, partner_qualid, interchanges.interchange_control_number, operators.name as operator_name, trading_participants.name as client_name FROM ectransactions left join functional_groups ON ectransactions.functional_group_id=functional_groups.id left join interchanges ON ectransactions.interchange_id=interchanges.id left join trading_participants ON ectransactions.trading_participant_id=trading_participants.id left join operators ON ectransactions.operator_id=operators.id ORDER BY client_name asc, transaction_datetime desc LIMIT N, N
I Want More!
You can also have MySQL add all queries that don’t use indexes into the slow-query-log. Add –log-queries-not-using-indexes during startup or in the my.cnf or my.ini file.
Bye! Thanks for all the fish!
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>
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
Hi everyone! Just introducing myself, hanging out and outlining how we are going to do things.
Bye! Thanks for all the fish!
christos@themysqlguy.com 
A while back I invited you all, from my sister site, to join me on a journey to explore my new favorite RDBMS, MySQL. Well here we are and thank you for joining me. In this the first installment, I wanted to set down the ground rules and let you know how we are going to be doing things.
Every installment will have 2 parts. An audio podcast, covering that installment’s material, and a blog entry on the website to support the podcast, show notes, links, code and commands mentioned in the podcast. The blog will not be limited to simply supporting the podcasts. I will also be blogging my ideas and thoughts about a multitude of subjects related directly and indirectly to MySQL.
I will try as much as possible to stay true to MySQL and not rant about other subjects on this site. You can always visit my main site at christoskalantzis.com for those.
Bye! Thanks for all the fish!
Note: The closing line is taken from the Hitchhiker’s Guide to the Galaxy by Douglas Adams. In the book, the Dolphins leave before the Earth is destroyed and thank the humans for all the fish they fed on while visiting us here. MySQL uses a Dolphin (Sakila) to represent itself, so I find it fitting to end every podcast and blog entry with that line.
