Sheeri Dot Org

MySQL does not follow the ANSI SQL standard for quoting. MySQL’s default quoting behavior is that either single or double quotes can be used to quote a string (this gets me into trouble when I work with Oracle databases, as double quotes do not indicate a string!).

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT "alive";
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

Bare words are dealt with in context; in this case, a bare word would be parsed as a column name:

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

Backquotes are the way MySQL escapes table names. So, if you want a reserved word, number or operator to be the name of an object (ie, a table named “1″ or a column named “date”) you need to use backquotes to avoid a syntax error….for example:

mysql> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

mysql> CREATE TABLE table (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (column date)' at line 1

mysql> CREATE TABLE `table` (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column date)' at line 1

mysql> CREATE TABLE `table` (`column` date);
Query OK, 0 rows affected (0.11 sec)

mysql> DROP TABLE table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1

mysql> DROP TABLE `table`;
Query OK, 0 rows affected (0.00 sec)

The above illustrates MySQL’s default behavior, which in summary is:
– With no quotes or backquotes, MySQL treats the word ‘alive’ as a field name.
– With single or double quotes, MySQL treats the word ‘alive’ as a string.

Changing SQL Mode
However, this behavior can be changed by setting the sql_mode. There are over 30 different sql modes in MySQL 5.1 that change the behavior of MySQL (not all are related to quoting). The sql_mode value is a comma-separated list showing which sql modes are used.

The default sql_mode is blank, allowing default MySQL behavior.

If sql_mode is changed to ANSI_QUOTES, then ANSI quotes are used….

mysql> SET SESSION SQL_MODE='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)

MySQL still treats barewords and backquotes as columns:

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
mysql> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

MySQL still treats single quotes as strings:

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

However, now MySQL treats double quotes as escape characters, ie, treating double quotes like backquotes:

mysql> SELECT "alive";
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

Putting quote marks in strings

Since I am discussing quoting, I will end with how to put quote marks into strings.

There are 3 ways to embed quotes in a string:

1) mix single and double quotes:

mysql> SELECT 'I say "OK".' , "I say 'OK'." , 'I say ''OK''.';
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

2) Use 2 quotes in a row to escape quotes:

mysql> SELECT 'I say ''OK''.' , "I say ""OK"".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)

3) Use the backslash to escape quotes:

mysql> SELECT 'I say \'OK\'.' , "I say \"OK\".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)

(Interestingly enough, I wrote a blog post on this topic almost exactly 2 years ago, too!)

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT "alive";
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

mysql> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

mysql> CREATE TABLE table (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table (column date)' at line 1

mysql> CREATE TABLE `table` (column date);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'column date)' at line 1

mysql> CREATE TABLE `table` (`column` date);
Query OK, 0 rows affected (0.11 sec)

mysql> DROP TABLE table;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'table' at line 1

mysql> DROP TABLE `table`;
Query OK, 0 rows affected (0.00 sec)

mysql> SET SESSION SQL_MODE='ANSI_QUOTES';
Query OK, 0 rows affected (0.00 sec)

mysql> SHOW SESSION VARIABLES LIKE 'sql_mode';
+---------------+-------------+
| Variable_name | Value       |
+---------------+-------------+
| sql_mode      | ANSI_QUOTES |
+---------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT alive;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'
mysql> SELECT `alive`;
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

mysql> SELECT 'alive';
+-------+
| alive |
+-------+
| alive |
+-------+
1 row in set (0.00 sec)

mysql> SELECT "alive";
ERROR 1054 (42S22): Unknown column 'alive' in 'field list'

mysql> SELECT 'I say "OK".' , "I say 'OK'." , 'I say ''OK''.';
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
| I say "OK". | I say 'OK'. | I say 'OK'. |
+-------------+-------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT 'I say ''OK''.' , "I say ""OK"".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)

mysql> SELECT 'I say \'OK\'.' , "I say \"OK\".";
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
| I say 'OK'. | I say "OK". |
+-------------+-------------+
1 row in set (0.00 sec)

OpenSQLCamp is less than 4 months away, and I have finally gotten around to updating the site. Special thanks go to Bradley Kuzsmaul and the folks at Tokutek for getting the ball rolling and making the reservation at MIT. Using MIT means that we will have *free* reliable wireless guest access and projects.

OpenSQL Camp is a free unconference for people interested in open source databases (MySQL, SQLite, Postgres, Drizzle), including non-relational databases, database alternatives like NoSQL stores, and database tools such as Gearman. We are not focusing on any one project, and hope to see representatives from a variety of open source database projects attend. As usual I am one of the main organizers of Open SQL Camp (in previous years, Baron Schwartz, Selena Deckelmann and Eric Day have been main organizers too; this year Bradley Kuzsmaul is the other main organizer). The target audience are users and developers, but others are encouraged to attend too. There will be both presentations and hackathons, with plenty of opportunities to learn, contribute, and collaborate!

I have updated the main Boston 2010 page at http://opensqlcamp.org/Events/Boston2010/ with travel and logistics information, including links to:

Register — it’s free and easy, and you can always change your mind later!

Maybe you have an idea for a session you would like to see, or a session you would like to give? If so, you can note it on the sessions page. This will give everyone a sense of what type of presentations will be there. I have started by putting 2 sessions I am willing to give and a third at the bottom for one I’d like to see, to give everyone an idea of both types of descriptions.

Probably the most important link right now is the way we keep OpenSQLCamp free for all attendees – sponsor or donate to the conference! Any donation amount is accepted, and all donations are tax-exempt to the fullest extent of the law. Businesses and organizations will be listed as sponsors if they make a donation of $250 or more, and individuals will be listed as sponsors if they make a donation of $100 or more. More information on sponsor benefits, including where to send a graphic to, at the link.

There is a preliminary schedule, up until the conference itself it will only show the agenda of the conference — how many rooms and what time the presentations are supposed to be. During and after the conference we will update this schedule page with the titles, presenters and links to any notes/videos/audio taken.

If you have any questions, please do not hesitate to ask on the mailing list or by posting a comment here.

By now you know that there is a MySQL Track during next week’s ODTUG Kaleidoscope in Washington, DC. Ronald Bradford and I organized the schedule at the last minute (Ronald did a lot of the work!). It was difficult to fill a schedule with 19 sessions that are either 1 hour or 1.5 hours long, and to do it I ended up with three presentations.

At each presentation I will be giving away a copy of The MySQL Administrator’s Bible, so be sure to show up! All MySQL track sessions are in Maryland C, and all times are Eastern.

On Monday, June 28th from 4 pm – 5:30 pm I will be presenting “What do you mean, SQL Syntax Error?”, a presentation about how MySQL’s SQL syntax extends and deviates from the ANSI/ISO SQL:2003 standard. There is an 80-page PDF accompaniment that will be given out for free during this session.

On Tuesday, June 29th from 11 am to 12 noon I will be presenting Importing and Exporting Data with MySQL, about the many tools to load and bulk load data, and how to export data for regular and bulk loads. I will also be going over which storage engines are particularly well-suited for bulk loading, and the caveats to watch out for. This session is useful for those who know MySQL as well as those asking the question, “What’s the equivalent of Oracle’s SQL Loader for MySQL?”

On Wednesday, June 30th from 8:30 am to 9:30 am I will be presenting Navigating MySQL Stored Procedures & Functions, Views and Triggers, which covers all the ways stored procedures, stored functions, views and triggers can be used, including a highlight of Oracle differences.

I hope to see you there!

The MySQL track at Kaleidoscope in Washington, DC during June 28-July 1st is set! Here is the schedule, Lincoln VI is the MySQL track room.

Ronald has done a super job and spent a ton of hours in the past several weeks coordinating this effort. Work has kept me much busier than normal, but I have lent some time to the coordination as well. It is a credit mostly to Ronald that we have been able to plan an entire 19-session conference track, complete with confirming speakers, in less than a month. (You may notice the schedule does not have all 19 sessions full, we are just waiting for some more speakers to confirm details.)

Whether or not you made it to last month’s O’Reilly MySQL User Conference & Expo, and whether you are an expert or casual user, the sessions at Kaleidoscope will teach you new and exciting things. This is a credit to the planning we did (and again, Ronald spent the lion’s share of time on this) — we did not just want to re-do the same content from the April conference, and we wanted something that would be accessible to developers and DBAs who know what they’re doing when it comes to writing SQL queries, but may or may not know how MySQL itself works.

I will definitely be giving away copies of my book, The MySQL Administrator’s Bible, and Ronald will be giving away copies of his new book, Expert PHP and MySQL, so the conference is definitely not-to-be-missed!

It’s not too late to register for Kaleidoscope – be sure to use the discount code MYSQL to save $300 off your registration (assuming you are not a member of ODTUG).

I have been talking with a group of folks who have been making a product that has lots of free functionality, including the ability to centrally manage many MySQL instances. The administration functions include starting and stopping MySQL, seeing status and system variables, seeing and managing the MySQL config file (/etc/my.cnf), seeing and managing accounts, a small dashboard of overall health graphs, and more.

With this free tool you can look at and manage local and remote databases. It supports ssh tunneling, including ssh using password-protected ssh keys. It’s pretty neat, and I have been working with the product manager to add features. I think this took will become the de facto standard for centralized GUI administration of MySQL.

The tool is
MySQL workbench….Surprise! One of the best new features for the administrator is that you can now create an administration connection for an existing workbench connection with a click of a button, instead of having to enter in all that information again.

I use the “developer” version, 5.2.21. Note that the 5.1 version does not have administration capabilities.

A MySQL user group member saw that I use Poderosa as my ssh-on-Windows tool, and asked why I did not use PuTTY. My response was that I like having tabbed windows and hate having to keep opening another PuTTY program every time I want to open another connection. With Poderosa I can open a new connection with Alt-N, and I can even connect directly to Cygwin with an icon.

But Poderosa is not the tool I wanted to mention….Another user group member mentioned PuTTY Connection Manager. It wraps around PuTTY and gets the existing saved connections, makes a nicely tabbed browsing window where you can open sessions by double-clicking the connections, which are now listed on the right-hand side.

See screenshot below:

I have not played with other features such as sending a command to multiple windows, but even just having this is a HUGE win.

In How to tune MySQL’s sort_buffer_size, Baron gives a condescending viewpoint on how to tune the sort_buffer_size variable in MySQL. In a much-nicer-nutshell, his advice is “do not change sort_buffer_size from the default.”

Baron did not explain the logic behind his reasoning, he handwaves that “people utterly ruin their server performance and stability with it,” but does not explain how changing the sort_buffer_size kills performance and stability. Regardless of how respected and knowledgeable the source, NEVER take any advice that tells you what to do or how to do it without understanding WHY.

This article will explain the “why” of Baron’s point, and it will also talk more about understanding why, an integral part against the “Battle against any guess.” Baron’s recommendation to leave sort_buffer_size as the default is just as bad as all the advice given to change the sort_buffer_size, because all that advice (including Baron’s) does not explain the underlying causes.

First, I explain the sort_buffer_size issue. The sort buffer size, as the name implies, is a memory buffer used when ordering is needed (usually for GROUP BY and ORDER BY clauses, when the index used for the filter/join does not follow the GROUP/ORDER BY order). Increasing the sort_buffer_size means allowing more memory to be used for the sorting process.

Increasing the sort_buffer_size usually improves performance because more memory is used in sorting. It can be detrimental to performance because the full size of the sort buffer is allocated for each thread that needs to do a sort, even if that sort does not need a very large sort buffer.

A better optimization would be to change the schema and/or queries so that all that sorting is not necessary. Increasing the sort_buffer_size gives you a false sense of security that your server is performing better. Your server is performing the same tasks, only faster — the best optimization is to make the tasks smaller or eliminate some tasks. If you can have queries without so much sorting, that’s a much better optimization than changing sort_buffer_size.

That being said, increasing the sort_buffer_size is a perfectly acceptable stop-gap solution that can be implemented RIGHT NOW (it’s a dynamic variable), while you examine your queries by doing a query review with a tool such as mk-query-digest. This is indeed what Pythian does — and, by the way, not only do we recommend that course of action, but we explain it to you and help you find and optimize the queries in question.

That all assumes that having lots of sorts that require lots of memory is a bad thing. It may be that you have tuned your queries and schema such that you have eliminated as many sorts as you can, but some may remain. An intensive data mining server is a good example of a situation in which permanently increasing the sort_buffer_size may be the right solution.

Now that we have the specifics of this situation out of the way, let’s look at the Battle Against Any Guess. This is a movement against guessing games. Understanding what you are doing is essential; in the case of sort_buffer_size, you can believe that you know what you are doing by increasing sort_buffer_size. However, the real solution to the problem lies in changing the queries, not changing the memory patterns.

There is a 6-page description of the “Battle against any guess” in the Northern California Oracle User Group’s May Journal, starting on page 13. The examples are specific to Oracle, but the points made are sound even if you do not know Oracle well. For example:

Blindly implementing best practices is nothing different from guesswork; we are applying some past-proven solutions without measuring how they stand against our requirements, and without testing whether they bring us any closer to the targets we have. Industry has become so obsessed with best practices that we commonly see projects in which reviewing an environment for compliance with best practices is the ultimate goal.

One good reason you need to know *why* is also mentioned in the article: The second danger of best practices is that they easily become myths. The technology keeps improving and issues addressed by certain best practices might not be relevant anymore in the next software version.

So, even from respected folks like Baron or myself, do not take advice on face value. Ask why, understand why, and then think if there is another level. It is not always easy; often you think you understand but really you miss that other level – such as with sort_buffer_size.

In The Doom of Multiple Storage Engines, Peter talks about how the storage engine concept of MySQL is usually spoken of in positive terms, but there are many negatives.

I have a hard time trying to figure out the deeper meaning behind Peter’s post, given that Percona writes a storage engine for MySQL, XtraDB. Does this mean that Percona will stop developing XtraDB? Does this mean that the Percona Server will diverge farther and farther away from MySQL so that they’re not compatible any more and migrating from MySQL to Percona Server is very difficult?

Or maybe it’s just that Peter is saying one thing and doing the opposite; which just seems wrong because that would be blatant hypocrisy on Percona’s part.

(This idea was a comment on the blog post but seems to be trapped in the spam filter, so I’m posting it; apologies if the comment comes through eventually….)

My own opinion of the issue: Peter is factually correct with what he says. However, it’s nice to have the framework and be allowed to use more than one storage engine, or use exclusively one storage engine that’s not MyISAM.

Liveblog of the Professional IT Community Conference session Mentoring: It’s for everyone

Ways to learn:
Audio
Visual
Kinetic (doing it)

Everyone learns differently, but most people learn with some combination of all these three.

However, you can also learn by training [that’s the truth, I learned a LOT by writing the book, even things I knew, I ended up needing to research more].

Ways to train:
Explanation
Observation
Demonstration
Questioning (Socratic Method)

What is a mentor?
noun: experienced and trusted adviser. It’s not just someone who teaches, it’s someone who advises.
experienced person in a company, college or schools who trains and counsels new employees or students.

verb: to advise or train (someone, esp. a younger colleague).

A mentorship is a safe place to ask questions.

A mentor is a trainer, but a trainer who also is a professional advisor.

Finding a mentor
Someone…..
– you respect/admire
– works with similar technology
– has a compatible personality
– you have a good rapport with

Being a mentor
– Teach technical skills
– Provide advanced technical/design guidance
– Model and teach professional skills
– Be interested and invested in the [student’s] career

I am moderating and liveblogging the Professional IT Community Conference panel called Tech Women Rule! Creative Solutions for being a (or working with a) female technologist.

One point to keep in mind: The goal is not equality for equality’s sake. The goal is to have a diverse range of experience to make your company/project/whatever the best it could be.

That being said, these issues are not just around women; they are about anyone who is “different”, whether it’s race, ethnicity, gender, sexual orientation, cultural.

So what are some of the solutions?

0) Better align expectations with reality. Are you expecting more from someone who is one gender than another? If a woman makes a mistake is it worse because she has to prove herself? Is it worse because she is representative of her gender? If she does something good is the achievement elevated more because of her gender? Either is bad.

1) Respect people for who they are. Everyone deserves respect; if someone is not at your technical level, they still deserve respect.

If someone says something that is completely wrong from a technical perspective, do not assume that they have no idea what they are talking about. It could be that they are the exact case in which that technical scenario is appropriate for them. If they are correct, your attitude will be refreshing and you might learn something. If they are indeed wrong, ask them about a scenario in which their thinking falls apart, or otherwise guide them through learning why what they are saying is wrong.

2) Be nice. Don’t condescend.

3) Be helpful. “RTFM, n00b!” is not helpful, and certainly does not follow rule #2.

4) Don’t do #1-3 for women only. Don’t treat women nicely because they’re women, and be a jerk to men because they’re men. Being helpful is good for anyone, not just women.

5) Cooperate, do not compete. Whether you are co-workers, working together on a software project, or just in a conversation, the game of “one-upping” another is a lot less useful than working together.

6) When hiring or when in an interview, concentrate on skills, not knowledge. “Skills” refers to attributes such as their ability to listen, how judgmental they are about a legacy system, whether they are open to new ideas, whether they disdain anything that is not cutting edge, and even technical skills such as thinking patterns, research patterns, algorithms, etc.

If someone says “I don’t know” in an interview, ask them “how would you go about figuring it out?” If someone says “I think it’s x and y” ask “how would you confirm/test that?” If a backup failed, do they start the backup over or do they try to figure out why it failed?

Are they thorough? Do they follow through? It is a lot easier to teach knowledge than it is to teach something like “debugging skills”.

7) Specifically encourage people to speak up. Train yourself to NOTICE when folks are not speaking up, and ask them if they have any suggestions or ideas.

8) If you are running an IT conference, specifically ask qualified women you know to speak, not about “women in IT”. If you hear of an IT conference, tell specific women you know that you think they would be a great speaker. Get women to speak at local user groups to get practice in a less intimidating space.

Resources:
Read HOWTO Encourage Women in Linux

You Just Don’t Understand
The Male Mind at Work
How to succeed in business without a penis: a guide for working women (this is a humor book)

Join and/or send messages to Systers, the world’s largest e-mail list of women in computing and technology fields.