Open Query blog

Some guidelines to choosing between MySQL’s numeric types, using longitude and latitude as a modelling example.

Filed under: Good practice / Bad practice | by toby Social tagging: accuracy > decimal > double > float > integer > latitude > longitude > mariadb > modelling > mysql > numeric > precision > sql
No Comments »

We got good responses to the “identify this query profile” question. Indeed it indicates an SQL injection attack. Obviously a code problem, but you must also think about “what can we do right now to stop this”. See the responses and my last note on it below the original post.
Got a new one for you!
You [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: master > mysql > open query > replication > slave > trivia
5 Comments »

You do SHOW PROCESSLIST, and you see one of your web apps issue the following query:
 SELECT … WHERE … AND 1=2 UNION SELECT …
What does this tell you, and what do you do next?

Filed under: Good practice / Bad practice | by arjen Social tagging: mysql
6 Comments »

This is a “dogfood” type story (see below for explanation of the term)… Open Query has ideas on resilient architecture which it teaches (training) and recommends (consulting, support) to clients and the general public (blog, conferences, user group talks). Like many other businesses, when we first started we set up our infrastructure quickly and on [...]

Filed under: Good practice / Bad practice | by arjen Social tagging: dogfood > failover > haproxy > lighttpd > Linode > mmm > mysql > open query > redundancy > resilience > zimbra
No Comments »

Do you know if your production MySQL servers will come back up when restarted? A recent support episode illustrates a number of best practices.

Filed under: Good practice / Bad practice | by toby Social tagging: backup > bug > crash > expire_logs_days > mysql > Open Query > repication > segfault > segmentation fault > strack trace
5 Comments »

In today’s gp/bp an open door will be kicked in: take your backups offsite!
I was actually tempted to create a poll to see how many of you do not have proper backups, and how many of you do not take those backups offsite. It is a simple piece of advice and relatively simple to set [...]

Filed under: Good practice / Bad practice | by walter Social tagging: backup > mysql > offsite
3 Comments »

When you write your create table statements, always make sure that you make them non-ambiguous. That way even though other servers might have different configurations, you make sure your table will be created in the same way.
Imagine for instance you are developing an application on a development server, nicely storing all the scripts you need [...]

Filed under: Good practice / Bad practice | by walter Social tagging: CREATE TABLE > InnoDB > mysql > NO_ENGINE_SUBSTITUTION > sql_mode
2 Comments »

I spent some time earlier this week trying to debug a permissions problem in Drupal.
After a lot of head-scratching, it turned out that Drupal assumes that when you run INSERT queries sequentially on a table with an auto_increment integer column, the values that are assigned to this column will also be sequential, ie: 1, 2, [...]

Filed under: Good practice / Bad practice | by cafuego Social tagging: auto_increment > checking > drupal > master > mysql > replication > scaling
5 Comments »

When writing queries, try making a habit out of using short table aliases, no matter how small the query is. Try using an alias that is as short as possible (one letter if possible, two or three when necessary) to avoid clutter in your queries.
Use these aliases in all places where you refer to a [...]

Filed under: Good practice / Bad practice | by walter Social tagging: Good practice / Bad practice > mysql
12 Comments »