Category Archives: MySQL

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)
-> ON UPDATE CASCADE
-> ON DELETE CASCADE);

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)
mysql>

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.

4 Comments

Filed under Administration, Basics, MySQL

Zero to DBA track at SELF

SELF14’s Zero to DBA track is back and starts with MySQL 5.7 – What is new, what is changing, and what we are breaking. The SouthEast Linux Fest SELF 2014 has had amazing sessions and a strong database track in previous years. This year is more great content and you really need to plan on attending June 20th through the 22nd on Charlotte, NC. The social events around this show are fun and relaxed. And, after a short delay, you can see all sessions on SELF’s Youtube channel so you can really see sessions that conflict with your primary choices. This year the venue has changed to a more central location near the airport and their are lots of activities in the area for your family to enjoy in the beautiful surrounding while you get your tech itch scratched.

Leave a comment

Filed under MySQL, MySQL Community Team Event

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 10.1.3.5, “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.

3 Comments

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

DFW Unix User group talk May 1st

Come hear about MySQL at the May 1st DFWUUG Dallas / Fort Worth Unix User group Meeting. Always a great crowd, good pizza, and fun. Seven PM at NEW IBM Innovation Center at 1177 South Beltline Road, in Coppell,

2 Comments

Filed under MySQL

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';
mysql>

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');
mysql>

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

The Reborn MySQL Community

The Percona Live MySQL Conference and Expo ends today but it signals a rebirth of the MySQL Community. 2014 has been the most vibrant, upbeat, and cheerful show in many years. A multitude of new technology, approaches and energies emerged this year.

WebScale is partnership of several of MySQL’s biggest users to pool patches to make a bigger, badder server. Frankly these companies have more resources than MySQL in some areas and will be able to add in features very quickly. And Oracle really wants to add these changes quickly.

Fusion-IO is changing the way we think about writing to disks. The costs of a write is low and the speed is very high. For those of us having cut our teeth on systems where you had to plan for rotational delay and disk arm movements, this is almost spooky. SSDs are going to change many design ideas in the database world and Fusion-IO is working hard with Percona, Oracle, and MariaDB to remove what was a major choke point for performance. I urge you to check out Nisha Talagala keynote, especially if you are not hardware savvy, to get a better understanding of this eveolution.

Oracle’s investment in MySQL was shown by the many new features announced. MySQL Fabric, Workbench 6.1, and the approaching 5.7 are vibrant tools that the community needs.

Peter Z and Robert Hodges both were very frank that users need to upgrade to MySQL 5.6 as it fixes many old lingering problems and provides better performance. By the way, Percona is doing very well and a growing their businesses and Continuent has added Hadoop feeding features to their product.

And MariaDB had their new release ready for this show. Everyone has been very busy.

For the consumer spectrum of the MySQL Community, this is a golden age where competition and renewed innovation are making the latest products much, much better. The addition of WebScale will provide yet again more access to better performance.

Many folks said this year, the Percona Conference felt like a family reunion. But this year, it seems that a new generation is being welcomed into the MySQL Community.

Leave a comment

Filed under MySQL

MySQL and the Entity Framework

I am in Las Vegas this week at the Live 360 Conference for Microsoft Developers. There is a lot of interest in using MySQL with the Entity Framework object-relational mapper and I have been pointing to Using an Entity Framework Entity as a Windows Forms Data Source. So for all you who asked, here is the blog post I promised with relevant link. Entity Framework AND MySQL

1 Comment

Filed under MySQL

MySQL February Webcasts

Monitoring MySQL at Scale
Wednesday, February 19, 2014

50 Tips to Boost MySQL Performance
Wednesday, February 26, 2014

http://www.mysql.com/news-and-events/web-seminars/developing-windows-applications-with-mysql-part-iii-migrating-from-microsoft-sql-server-to-mysql/

Thursday, February 27, 2014

Leave a comment

Filed under MySQL

Looking to Hire a MySQL DBA or Developer?

“Why can’t I find an MySQL DBAs or Developers?” This morning I got a message from a very perplexed Human Resources person on why their ads on Linkedin were not getting any results. Several such emails, calls, or messages make it to me each week and I would like take this opportunity to cover this subject. MySQL DBAs and Developers are out there but there are reasons why they are not interested in your job posting.

1. Provide details — “Exciting new position in rapidly growing start up in an expensive city and we want you to know how to program in every programming language, be a recent university graduate (hopefully PhD or higher), with ten years of experience but please be under twenty three years of age. Must prefer stock options and left over pizza crusts over a regular salary. And be flexible.” No, most ads are not quite that bad but several are very, very close.

Spell out what you want — A DBA or Developer is judging you on your ad. Companies that write poor ads tend to have poor mission statements, memos, and other general communication faults. Put in the general requirements such as number of servers, seven-twenty four three sixty five operations, general industry, and what good/service you provide. Then go heavy on what your candidate should have for skills. If you need a Developer to write in Java, Perl, C++, and have a smattering of Javascript as a need state those up front and put your want list later.

2. Be realistic — “Great opportunity in McMurdo Sound Antartica, but no relocation.” If you are in a small market, you need to chase down local and nearby talent pools. Check with local user groups — MySQL, PHP, Linux, etc. Sometimes someone with experience with another database is looking for greener pastures. Check with local schools, consultants, and ASK YOUR STAFF.

3. Grow your own — Does someone in your organization have some skills over lap or, more importantly, an interest in the subject? Training someone already hired may seem expensive because your training budget is cut to the bone but it can be cost effective if you look at the time and costs of drug screens, background checks, on-boarding process paperwork, etc.

Looking only for the pot of gold means you will miss a lot of rainbows.

4. Developers like to develop. Many prefer to maintain or re-factor older code. If you are looking for a bright shining programmer and stick them with legacy code of dubious worth, you will take the shine right of them. Other Developers love to create from scratch. Realistically present how much new coding versus support code is required or you will lose the Developer and have to start again from square one.

DBAs like to admin databases. If your schemas have been de-normalized, broken apart, reassembled, and then de-normalized again but you expect a DBA to wave a magic wand to provide high performance during their first hour on the jobs — you need an attitude adjustment. DBAs can work miracles but having to be Sisyphus ten hours a day will not attract the talent you want. If things have ‘gone to Hell in a hand basket’ you need to mention this in the pre-interview or interview.

5. Do not stick with ‘the DBA, he must’ — Many ads are written using male pronouns exclusively. I am a big fan of the epicine ‘he’ but many ads are written in a way that instantly turns off female job seekers. There are many talented women who want a chance but are not going to single handily battle a Neanderthal group when there is more demand than supply. This is not political correctness — it is keeping you from cutting off a high percentage of qualified candidates.

6. Yes, mention pay scale. MySQL DBAs and Developers are in demand and spending a day interviewing only to find that the jobs pays very low is frustrating. MySQL is not a toy database and going cheap is going to cost you sooner than later.

7. Answer your responses. If someone answers your ad, send an acknowledgement even if it is a simple ‘We got your resume’ reply. Hearing nothing back while their resume makes the circuit of HR, Senior Staff, Project Staff, Junior Management, and hiring managers for a few months will discourage anyone. The candidate will more than likely take another job while you wait for the feedback from the manager who is on an extended tour of the other hemisphere.

8. Target your ad — “The DBA must be able to program in C, C++, C#, RPG, COBOL, Javascript, Ecmascript, PHP, ALGOL, assembler (68000 and 6502), and R” is not going to catch the eye of any DBA. IF you want a Developer who can also admin your MySQL instances, please state that in your ad.” If you want someone to mainly be a DBA but be able to help find fault in code, you need to acknowledge that.

9. If you ad draws ZERO responses, it may be your ad not the group where you posted.

I generally refer folks to the Jobs board on forums.mysql.com and their local user groups. Linkedin is good but some of the various MySQL-centric groups seems to have almost as many HR staffers as MySQL professionals.


Leave a comment

Filed under MySQL