Category Archives: Basics

Changes in MySQL 5.6.20

The MySQL Release Notes should be part of any DBA’s regular reading list. The Changes in MySQL 5.6.20 came out last week and there are some interesting goodies.

  • MySQL now includes DTrace support on Oracle Linux 6 or higher with UEK kernel.
  • A new system variable binlog_impossible_mode controls what happens if the server cannot write to the binary log, for example, due to a file error.
  • The mysqlhotcopy utility is now deprecated and will be removed in a future version of MySQL

5.6.20 has a slew of bug fixes, functionality changes, and notes.

So why should you be reading the changes on a regular basis? There isa goldmine of information in them. For instance, if you use blobs, consider this:

Important Change: Redo log writes for large, externally stored BLOB fields could overwrite the most recent checkpoint. The 5.6.20 patch limits the size of redo log BLOB writes to 10% of the redo log file size. The 5.7.5 patch addresses the bug without imposing a limitation. For MySQL 5.5, the bug remains a known limitation.

As a result of the redo log BLOB write limit introduced for MySQL 5.6, innodb_log_file_size should be set to a value greater than 10 times the largest BLOB data size found in the rows of your tables plus the length of other variable length fields (VARCHAR, VARBINARY, and TEXT type fields). Failing to do so could result in “Row size too large” errors. No action is required if your innodb_log_file_size setting is already sufficiently large or your tables contain no BLOB data. (Bug #16963396, Bug #19030353, Bug #69477)

That is golden information for those of us who used a lot of blobs and great info for configuring servers.

1 Comment

Filed under Administration, Basics, MySQL

MySQL APT Repository

THe MySQL APT Repository provides an easy and convenient way to get the latest MySQL software. My test server was need of a refresh so I put on a fresh install of Ubuntu 14.04 and downloaded mysql-apt-config_0.2.1-1ubuntu14.04_all.deb.

sudo dpkg -i mysql-apt-config_0.2.1-1ubuntu14.04_all.deb
[sudo] password for dstokes:
Selecting previously unselected package mysql-apt-config.

You will get a choice to install MySQL 5.6 or the latest 5.7 DMR.

sudo apt-get update Pulls the latest information from the repository for the various packages.

sudo apt-get install mysql-server Installs the server and will start it running. And then a quick sudo apt-get install mysql-workbench to get me where i needed to be.

There is a detailed information at A Quick Guide to Using the MySQL APT Repository


Filed under Administration, Basics

Triggers — MySQL 5.6 and 5.7

MySQL Triggers are changing in 5.7 in a big way. Triggers have been around since 5.0 and have not changed much up to 5.6 but will gain the ability to have multiple triggers on the same event. Previously you had ONE trigger maximum on a BEFORE UPDATE, for example, and now you can have multiple triggers and set their order.

So what is a trigger? Triggers run either BEFORE or AFTER an UPDATE, DELETE, or INSERT is performed. You also get access to the OLD.col_name and NEW.col_name variables for the previous value and the newer value of the column.

So how do you use a trigger? Let say you are updating the price of an inventory item in a product database with a simple UPDATE statement. But you also want to track when the price change and the old price.

The table for products.
CREATE TABLE products (id INT NOT NULL auto_increment,
price DECIMAL(5,2) NOT NULL,

The table for price changes on the product table.
CREATE TABLE products_log (id INT NOT NULL,
price DECIMAL(5,2) NOT NULL,
change_date timestamp);

Now to define a trigger that will log price changes. We do this when a price is updated. Now the use od OLD.price to avoid confusion between the old price or the new price being saved in the log.
CREATE TRIGGER product_price_logger
INSERT INTO products_log (id, price)

Add in some data.
INSERT INTO products (price) VALUES (1.10),(2.24),(.99),(.01),(.34);

So UPDATE a record.
UPDATE products SET price='1.11' WHERE ID = 1;

So did it work? Yes, and no. Running SELECT * FROM products_log; Provides us with a time stamp of the change and the OLD.price. But I forgot to also record the id!! Challenge: Correct my mistake and compare it to an update I will make in a few days.

Now 5.7 introduces multiple triggers for the same event. Lets add yet another log this time recording who made the change;

The ‘who made the change table’.
CREATE table who_changed (
who_did_it CHAR(30) NOT NULL,
when_did_it TIMESTAMP);

And the second trigger.
CREATE TRIGGER product_price_whom
FOLLOWS product_price_logger
INSERT INTO who_changed (id, who_did_it)
VALUES (, user());

So UPDATE products SET price='19.99' WHERE id=4; is run and we see that both triggers execute. Note that SHOW TRIGGERS from schema; does not provide any information on trigger order. But you can find all that as action_order in PERFORMANCE_SCHEMA.TRIGGERS

Being able to order triggers makes it easy to make logical steps when processing data. Can you get into trouble with this? I am certain someone will manage to make a mess with this. But I think most of us will enjoy being able to use this great new functionality.


Filed under Basics, DBA Tools, MySQL, MySQL Certification, Uncategorized

Foreign Keys and MySQL

Foreign Keys are often a mystery to new DBAs in the MySQL world. Hopefully this blog will clear some of this up.

In this example, we will have a table for employee data and a table for the data on offices. First we need the two tables.
CREATE TABLE employee (
-> e_id INT NOT NULL,
-> name CHAR(20),
-> PRIMARY KEY (e_id)
-> );

CREATE TABLE building (
-> office_nbr INT NOT NULL,
-> description CHAR(20),
-> e_id INT NOT NULL,
-> PRIMARY KEY (office_nbr),
-> FOREIGN KEY (e_id)
-> REFERENCES employee (e_id)

Those who do not use Foreign Keys will not be familiar with the last four lines of the building table. The trick is that there are two e_id columns, one in each table. In the employee table is it simply the employee identification number. However in building table, it is declared to be a foreign key to the employee table using the e_id column. The CASCADE lines are telling MySQL that any UPDATEs or DELETEs on the e_id column in employee table will also be made on the corresponding row(s) in the building table.

Add in some data .
mysql> INSERT INTO employee VALUES (10,'Larry'), (20,'Shemp'), (40,'Moe');
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> INSERT INTO building VALUES (100,'Corner Office',10), (101,'Lobby',40);
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0

mysql> SELECT * FROM employee;
| e_id | name |
| 10 | Larry |
| 20 | Shemp |
| 40 | Moe |
3 rows in set (0.00 sec)

mysql> SELECT * FROM building;
| office_nbr | description | e_id |
| 100 | Corner Office | 10 |
| 101 | Lobby | 40 |
2 rows in set (0.00 sec)

Simple so far, right? So let us join the employee table with the building table. mysql> SELECT * FROM employee JOIN building ON (employee.e_id=building.e_id);
| e_id | name | office_nbr | description | e_id |
| 10 | Larry | 100 | Corner Office | 10 |
| 40 | Moe | 101 | Lobby | 40 |
2 rows in set (0.02 sec)

But we have three employees and only two lines of output? What happened? Well, what happened is that the query wanted the matches from both tables. To get all the rows from the first table and any matches from the second table, use a LEFT JOIN.
mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
| e_id | name | office_nbr | description | e_id |
| 10 | Larry | 100 | Corner Office | 10 |
| 40 | Moe | 101 | Lobby | 40 |
| 20 | Shemp | NULL | NULL | NULL |
3 rows in set (0.00 sec)

Much better.

A big benefit of using foreign keys is that bad values get a lot harder to insert into the database. Try to add an office for a non-existent employ number 77.
mysql> INSERT INTO building VALUES (120,'Cubicle',77);ERROR 1452 (23000): Cannot add or update a child row: a foreign key constraint fails (`test`.`building`, CONSTRAINT `building_ibfk_1` FOREIGN KEY (`e_id`) REFERENCES `employee` (`e_id`) ON DELETE CASCADE ON UPDATE CASCADE)

Now back to all that CASCADE stuff. Remove any of the employees from the employee table and the corresponding building entry will be removed.
mysql> DELETE FROM employee WHERE e_id=40;
Query OK, 1 row affected (0.08 sec)

mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
| e_id | name | office_nbr | description | e_id |
| 10 | Larry | 100 | Corner Office | 10 |
| 20 | Shemp | NULL | NULL | NULL |
2 rows in set (0.00 sec)

Likewise changes are cascaded from the employee table to the building table.
mysql> UPDATE employee SET e_id=21 WHERE e_id=20;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM employee LEFT JOIN building ON (employee.e_id=building.e_id);
| e_id | name | office_nbr | description | e_id |
| 10 | Larry | 100 | Corner Office | 10 |
| 21 | Shemp | NULL | NULL | NULL |
2 rows in set (0.00 sec)

There are many MySQL DBAs who do not use Foreign Keys for various reasons but they can be very handy. I find them useful in one to many relationships where I do not want to have to purge or change the many directly in a query.


Filed under Administration, Basics, MySQL

MySQL Slashes and Backslashes

There was a on Twitter to @MySQL How can I insert “/” into a varchar field please. Since they were polite and used please, I wanted to be helpful.

At first I thought this was the ol’ MySQL string literal problem where you need to have two backslashes to get one backslash in a character field. See String Literals in the MySQL Manual. Back Slash and Fore Slash A lot of people have trouble with the backslash when they first get started with MySQL. From the quote below, you will see that some characters need to be escaped with a backslash. But what about the forward slash?

I remember a previous job were I saved many UNIX file names but I did not remember anything unusual about forward slashed. My mental cache had been flushed of the answer. So I created a test table and experimented.

As can be shown in the image, the ‘\\’ trick works for inputting a single backslash. And ‘//’ will input TWO slashes. So there is no trick for fore slashes.

No too bad for a Monday morning with no caffeine.

Within a string, certain sequences have special meaning unless the NO_BACKSLASH_ESCAPES SQL mode is enabled. Each of these sequences begins with a backslash (“\”), known as the escape character. MySQL recognizes the escape sequences shown in Table 9.1, “Special Character Escape Sequences”. For all other escape sequences, backslash is ignored. That is, the escaped character is interpreted as if it was not escaped. For example, “\x” is just “x”. These sequences are case sensitive. For example, “\b” is interpreted as a backspace, but “\B” is interpreted as “B”. Escape processing is done according to the character set indicated by the character_set_connection system variable. This is true even for strings that are preceded by an introduce that indicates a different character set, as discussed in Section, “Character String Literal Character Set and Collation”.

Table 9.1 Special Character Escape Sequences

Escape Sequence Character Represented by Sequence
An ASCII NUL (0x00) character.
\’ A single quote (“’”) character.
\” A double quote (“””) character.
\b A backspace character.
\n A newline (linefeed) character.
\r A carriage return character.
\t A tab character.
\Z ASCII 26 (Control+Z). See note following the table.
\\ A backslash (“\”) character.
\% A “%” character. See note following the table.
\_ A “_” character. See note following the table.

The ASCII 26 character can be encoded as “\Z” to enable you to work around the problem that ASCII 26 stands for END-OF-FILE on Windows. ASCII 26 within a file causes problems if you try to use mysql db_name < file_name.

The “\%” and “\_” sequences are used to search for literal instances of “%” and “_” in pattern-matching contexts where they would otherwise be interpreted as wildcard characters. See the description of the LIKE operator in Section 12.5.1, “String Comparison Functions”. If you use “\%” or “\_” outside of pattern-matching contexts, they evaluate to the strings “\%” and “\_”, not to “%” and “_”.

There are several ways to include quote characters within a string:

A “'” inside a string quoted with “'” may be written as “''”.

A “"” inside a string quoted with “"” may be written as “""”.

Precede the quote character by an escape character (“\”).

A “'” inside a string quoted with “"” needs no special treatment and need not be doubled or escaped. In the same way, “"” inside a string quoted with “'” needs no special treatment.


Filed under Basics, MySQL

Learning PHP, MySQL, GIT, CSS, HTML, OOP, etc.

“How do I learn PHP, MySQL, GII, CSS, HTML, OOP, and all that quickly, without becoming confused or frustrated but still get to do the cools stuff?” I get this question on a very frequent basis and until last Friday, I had to suggest a number of ways of pulling together all the pieces together. But it still lacked a comprehensive theme and flow. Then last Friday at LonestarPHP, the LonestarPHP organizers teamed with PHPWomen,to provide a foundations track that covered all the above and more. Davey Shafik, Elizabeth Smith, Matt Frost, and Michelle Sanver put together an amazing day of learning with PHPBridge PHPBridgefor a crowd of about 40 novices,

The material is under a Creative Commons License and can be used by other events. The idea was borrowed form RailsBridge and organizes the many moving parts of a PHP application building environment in a very easy to follow but still very informative fashion. If you want to provide this material as a PHPBRidge event, there are some provisos (Free of charge, make tech more welcoming) and you should consider adding this to your local event.

I heartily recommend this to any novices out there or those with some gaps in their knowledge of PHP app development. You will be walked through the basics, set up a development environment, code, connect to a database, use a relational database (MySQL) for storing data, and more. Hats off to PHPWomen and LonestarPHP for this year’s track and I hope to see it again next year.

Leave a comment

Filed under Basics, MySQL, PHP

Newbie password mistake

I received a panic call from a newbie MySQL DBA. Or should I say the ‘Linux Admin’/’MySQl DBA’/’CSS guru’/’PHP Programmer’/’Network Admin’/’Backup Operator’/’CIO’ of a small business. He had reset his password was was now locked out. Luckily, he had only changed his password and still had root access.

What he did:
mysql>use mysql;
SET PASSWORD for 'mrdoesall'@'10.%' to 'bigsecret';

Long time MySQL DBAs should be groaning at this with a wince remembering when it happened to them. For those of you who did not catch the problem, what happened is that he value in the user.password table is set to the string ‘bigsecret’. When our friend tries to login, the password is encrypted and compared to the value in user.Password. The comparison of the encrypted value does not equate to the unencrypted value and the login fails.

What he meant to do:
mysql>use mysql;
SET PASSWORD for 'mrdoesall'@'10.%' = PASSWORD('bigsecret');

So with the help of the root account, all was resolved. The CLI interface can let you step on your own feet which is one of the reasons I recommend MySQL Workbench to novices and non full-time DBAs.

1 Comment

Filed under Basics, MySQL

Presents for DBAs

Dear Fellow DBAs–You are a pain to shop for at holiday time. Once again @stoker has compiled a list that you can print out, circle what you desire, and hand to a relative. If you do not want a Duck Dynasty Chia Pet or a blanket with arms, print this now NOW!

Leave a comment

Filed under Basics

Last Day to register for North American MySQL Virtual Developer Day!

There is one more day until the 2013 Virtual Developer Day for North America so register today! Europe, Middle East, Africa and Some Asian Countries have a week before their event!

Virtual Developer Day: MySQL is a one-stop shop for you to learn all the essential MySQL skills. With a combination of presentations and hands-on lab experience, you’ll learn the latest features in MySQL 5.6, have the opportunity to practice in your own environment and sharpen your skills to:

Develop your new applications cost-effectively using MySQL
Improve performance of your existing MySQL databases
Manage your MySQL environment more efficiently
Don’t miss this exclusive opportunity to learn tips and tricks from the MySQL experts at Oracle!

Rob Young, Product Management Director, Oracle MySQL
Craig Sylvester, Principle Sales Consultant, Oracle MySQL
Ligaya Turmelle, Principle Technical Support Engineer, Oracle MySQL
Lee Stigile, Sales Consulting Senior Manager, Oracle MySQL
Ben Krug, Senior Technical Support Engineer, Oracle MySQL
Calvin Sun, Senior Engineering Manager, InnoDB, Oracle MySQL
Mark Leith, Software Development Senior Manager, Oracle MySQL
Jørgen Løland, Senior Software Engineer, Oracle MySQL
Lynn Ferrante, Principle Sales Consultant, Oracle MySQL
Americas and Canada – March 12th, 2013

9:00 a.m – 1:00 p.m PST / 12:00 p.m. – 4:00 p.m EST / 1:00 p.m – 5:00 p.m BRT

Europe, Middle East, Africa and Some Asian Countries (English) – March 19th, 2013

9:00 – 13:00 UTC/GMT / 10:00 – 14:00 CET / 12:00 – 16:00 AST / 13:00 – 17:00 MSK / 14:30 – 18:30 IST

March 12, 2013 Online – access from your computer directly Learn more and register for the virtual event »
March 19, 2013 Online – access from your computer directly Learn more and register for the virtual event »


Filed under Basics, MySQL

North Texas MySQL Users Group Meeting April 2nd

Come early to have pizza and network and then see a presentation and demo of the new features in MySQL 5.6.

RSVP to make sure we get a large enough room and sufficient pizza.

Pizza at 5:30 p.m.
Presentation begins at 6:00 p.m.

Oracle Corporation
6031 Connection Drive
Suite 900
Las Colinas, TX 75039

Leave a comment

Filed under Basics, community team, MySQL