Category Archives: 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

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,


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

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

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

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

MySQL 5.7 & Workbench 6.1 Query Plans

MySQl 5.7 and Workbench 6.1 Query Plan

MySQL 5.7 and Workbench 6.1 work together to provide an even prettier version of a query plan than the impressive stuff from the 5.6/6.0 combo

Here is a sneak peek at MySQL Workbench 6.1’s VISUAL EXPLAIN.

Recently I was demonstrating the difference between using EXPLAIN and VISUAL EXPLAIN to a full room at the fantastic SkiPHP Conference in Salt Lake City. MySQL 5.6 and Workbench 6.0 combine to make an easy to read graphic that aids in understanding the Query Plan Generated by the Optimizer. All in the audience agrees that the ASCII-ish output of EXPLAIN paled in comparison to VISUAL EXPLAIN. Now MySQL 5.7 and Workbench 6.1 work together to provide an even better VISUAL EXPLAIN.

I really meant to test 5.7/6.0 on the plane on the way to SLC but did not get around to it. If I had known, I would have covered the new VISUAL EXPLAIN at SkiPHP. So those who attended, please accept my apology!

For those who do not know, VISUAL EXPLAIN takes a query, such as
SELECT Country.Name, City.Name, Language
FROM CountryLanguage
JOIN Country on (CountryLanguage.CountryCode = Country.Code)
JOIN City on (City.CountryCode = Country.Code)
WHERE IsOfficial = 'T' AND City.Population > 1000000;

and generates a graphic as seen at the start of this blog post. There are lots of great docs on using EXPLAIN, and some on VISUAL explain. Now I am eagerly awaiting the Workbench 6.1 docs to learn more.


Filed under MySQL

Watch Todd Farmer at San Francisco MySQL User Group

Todd Farmer’s MySQL 5.7 talk can been seen at For those few of you who did not stay up to watch Todd live, you get TWO slide decks in just over ninety minutes — it is almost the next best as being at the San Francisco Users Group in person.

Leave a comment

Filed under MySQL, User Group