Tag Archives: sql

MySQL: Solve sql_mode=only_full_group_by Error by Turning off Strict SQL Mode Tips, Tricks and Tutorials 26 OCT 2017

When moving an older MySQL-based project to a new server instance, one of the SQL query errors bound to immediately strike is that of triggering the sql_mode=only_full_group error, caused by your SQL select statements referring to columns that are not functionally dependent on the GROUP BY column. (Best explained here in the MySQL documentation)

This new, “Strict SQL Mode” (which is actually a collection of enabled “Server SQL Modes”) is now switched on by default for any MySQL Server 5.7 and up installation, and because in the real world refactoring an entire project just to suit these new settings isn’t really an option, to resolve the issue you need to “disable” this new default on your server instance.

The two newly enabled SQL mode setting that we want to target (because they are the main culprits here) are STRICT_TRANS_TABLES and ONLY_FULL_GROUP_BY.

To do this, you first need to create an additional configuration file for your MySQL server instance:

sudo nano /etc/mysql/conf.d/disable_strict_mode.cnf

Add these two lines to the blank file and then save your changes.

[mysqld]
sql_mode=IGNORE_SPACE,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

As you can see, essentially what we are doing is just declaring a new SQL mode with our two culprit modes omitted.

Finally, restart the MySQL server with:

sudo service mysql restart

Try running your fault trigger SQL statement again, and you should now no longer get this error message staring back at you any more:

Syntax error or access violation: 1055 Expression #1 of SELECT list is not in GROUP BY clause and contains nonaggregated column 'yourdbname.tblname.address' which is not functionally dependent on columns in GROUP BY clause; this is incompatible with sql_mode=only_full_group_by

Related Link: MySQL Group By Handling | Server SQL Modes

How to use MySQL to Import a .sql file Tips, Tricks and Tutorials 05 JUL 2015

Quite often I like backing my databases up as .sql files, which then comes in handy for also moving databases around, or making copies of them. The question is, given your Ubuntu server and the mysql command, how do you import a .sql file?

Well first, you enter the mysql console in the usual manner:

mysql -u USERNAME -p

Once in, switch to the database that you want to run the SQL import against:

mysql> use DATABASE_NAME;

With the database changed, the next step is to actually do the SQL import itself:

mysql> source PATH/TO/FILE.sql;

Easy as that. Of course, you could just have done the whole thing via the terminal command line if you didn’t want to access the MySQL console in the first place:

mysql -u USERNAME -p DATABASE_NAME < PATH/TO/FILE.sql

Nifty.

importing goods on a container ship

MySQL: How to Perform an Order By Sort before a Group By Action CodeUnit 25 JUN 2012

Running an order by sort on a result set before applying the group by call – It’s a pretty common problem and an unfortunate result of the way in which MySQL has chosen to implement the SQL language specification.

At the heart of it all, MySQL performs any group by operation before an order by operation, meaning that you usually won’t get quite what you’re after – for example, if you are trying to get the last received feedback for every client in your database, you’ll quickly find that you fall short if you don’t fall back on this most basic of solutions to the order by before group by dilemma: running a subquery.

A subquery is a SELECT statement within another statement, and although you take a very minor performance hit, it does solve the problem very elegantly.

Essentially what you want to do is run your original statement with the order by applied, and then on this resulting statement (technically a temporary table), you then want to run the group by operation.

And luckily for us, it is as easy to implement as what it is to understand. In practice:

SELECT * FROM (SELECT * FROM `clientfeedback` ORDER BY `timestampcaptured` DESC) GROUP BY `clientname`

Nifty.

MySQL: How to Select the First Word of a Sentence using SQL CodeUnit 21 MAY 2012

Sometime it is useful to be able to extract the first word of a sentence contained in one of the columns of your table. Luckily for us, MySQL makes this a trivial operation thanks to its useful SUBSTRING_INDEX() function.

From the reference manual:

SUBSTRING_INDEX(str,delim,count) – Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

So for our purposes, if we want to select the first word from a sentence or string of words, the natural delimiter we would be looking out for is ‘ ‘ – i.e. an empty space character. Because we’re interested in the first word of the sentence, we want everything returned before the first occurrence of the space, leaving us with sql which looks like this:

SELECT SUBSTRING_INDEX( `myColumn` , ' ', 1 ) as `firstWord` FROM `sentences`

Nifty.

MySQL: How to Order a SQL query’s results by a given ID Sequence CodeUnit 01 AUG 2011

I have a specific sequence of ID numbers and I want the result set retrieved when looking up more information on those records in the exact same sequence that I have it. This sequence is not your usual ascending or descending order, but rather in a very specific order. So how does one do this?

Well the answer lies with the useful FIELD SQL function, normally a string function but one that proves to be quite handy when it comes to numbers to. So let’s lay out an example shall we:

Given the ID sequence of 3, 1, 5, 2, 7 the normal way to retrieve say the name field would be:

SELECT `name` FROM `table` WHERE `id` IN (3,1,5,2,7);

Of course, this would return a result set in no particular order, simply in the order in which the database was able to retrieve the results.

Now for our example, we want to retrieve the list of names in the exact order of that given ID sequence, changing our SQL statement to this:

SELECT `name` FROM `table` WHERE `id` IN (3,1,5,2,7) ORDER BY FIELD(`id`,3,1,5,2,7);

The resulting resultset will have the names ordered in the exact sequence you wanted them – which is obviously very nifty!

MySQL: How to Remove all Newline Characters from a Column CodeUnit 29 JUN 2011

Annoyingly, carriage returns and line-feeds (aka newline characters) often get sucked up into our database through imports, inserts and the like, which is annoying because these generally invisible characters play havoc with simple WHERE COLUMN = STRING statements because what you thought were matching strings are actually not matching at all! However, a once off statement to remove all these annoying chr(10) and chr(13) characters is actually pretty simple to implement.

Using the handy REPLACE MySQL function and taking recognition that MySQL denotes character returns as r and line-feeds as n, we can put together a simply SQL statement that looks like this:

UPDATE `myTable` SET `myColumn` = REPLACE(REPLACE(`myColumn`, 'r', ''), 'n', '');

This will remove all character returns and line-feeds by replacing them with a blank string.

Nifty.

SQL: Toggle 1 and 0 Value in an Update SQL Statement CodeUnit 14 JAN 2011

I tend to use a lot of tinyint columns as controllers for my database-held objects, usually sticking to the convention of 1 means on and 0 means off. In other words, the perfect target for a SQL toggle statement!

In order to toggle update a value in SQL, we’ll need to make use of the common SQL control flow function IF. The IF function takes three parameters, the first being the test expression, the second being the term to return if the test expression passes and the third being the term returned if the test expression fails.

So putting this into our 1 or 0 toggle SQL statement, we get:

UPDATE `table` SET `column` = IF(`column` = 1, 0, 1) WHERE `id` = x

It’s pretty intuitive to see what is going on above, now that we understand the form of the fabulous IF SQL function!

Nifty.

Related Link: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if

SQL: Finding Duplicate Rows based on Multiple Columns CodeUnit 25 JUN 2010

Locating duplicate rows based on multiple column values with SQL turns out to be a fairly simple exercise.

If we take the usual method for locating duplicate rows based on a single column’s value, we see that locating a duplicate is nothing more than grouping on that column where the instance of that column value appears more than once.

In other words:

SELECT email, COUNT(email)
FROM users
GROUP BY email
HAVING ( COUNT(email) > 1 )

Now to find duplicate rows based on the values held by multiple columns, we simply expand on what we have done before and provide more parameters to work against, resulting in a SQL statement that looks like:

SELECT firstname, COUNT(firstname), surname, COUNT(surname), email, COUNT(email) 
FROM users
GROUP BY firstname, surname, email
HAVING (COUNT(firstname) > 1) AND (COUNT(surname) > 1) AND (COUNT(email) > 1)

And that is seriously how simple finding duplicate rows based on a compound key (multiple columns restraint) really is! :)

MySQL: Prevent Two PHP Scripts from Overwriting One Another’s Table Updates CodeUnit 23 JUN 2010

From day one, the problem with database-driven applications is of course that the potential for two separate scripts to overwrite data being used by one another when run simultaneously is pretty huge.

One of the main ways of dealing with this is to use “locks”, basically allowing one script to lock a particular set of tables, meaning that other scripts can’t make use of those tables until the locks are released.

Using locks with MySQL and PHP is pretty simple if we are keeping things straightforward, and the implementation goes something like this:

mysql_query("LOCK TABLES `mytable` WRITE");
// ...do all your insert queries on `mytable` here...
mysql_query("UNLOCK TABLES");

And it’s as simple as that.

There are two major types of locks available to you, namely WRITE and READ, where WRITE indicates that you wish to read and write to the table and that no one else should be allowed to either alter or read the data while you hold the lock. (Whilst a table is in a WRITE locked state, other requests block until the lock is released).

A READ lock indicates that you wish to read a table but not write to it, and thus by implication means that multiple scripts can all hold a READ lock at once – in fact a script can still read the READ locked table without first requiring a lock at all!

Of course, this is simply scratching the complexity which can be delved into when it comes to table locking, but for a straightforward simple implementation to ensure your precious data isn’t being overwritten when you are busy with a table, the above works perfectly fine!

In other words, nice! :)

Related Link: http://dev.mysql.com/doc/refman/5.0/en/lock-tables.html