Tag Archives: mysql

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

Ubuntu Server: Quick method to reset MySQL root user Password Tips, Tricks and Tutorials 09 JUL 2015

The chance of remembering your MySQL root user account password when coming back to an Ubuntu server after a long period of time is pretty much zero – after all, this is seldom a username/password combo that you actually use on a daily basis! Luckily, there is a particularly easy way to reset the root user password for an Ubuntu server…

We’ll simply reconfigure the MySQL package!

First, we need to check the version of the currently installed mysql-server.

apt-cache policy mysql-server

This should give us among all the returned information a line that starts with ‘Installed’. My server instance returned this:

Installed: 5.5.43-0ubuntu0.14.04.1

So I have 5.5 installed then. Next, we start the reconfiguration process with:

sudo dpkg-reconfigure mysql-server-*.*

*.* should obviously be replaced with your version, so using my example, I would have ran:

sudo dpkg-reconfigure mysql-server-5.5

This command should then stop the database daemon and a prompt will then appear where you need to enter your new root password (yay!) and then confirm the reconfiguration. On completion, your instance of mysql-server should automatically be started up again.

As simple as that. You should now be able to login as root using your newly set password:

mysql -u root -p

Nifty.

red-reset-button

MySQL: How to add a Super User administrator account Tips, Tricks and Tutorials 08 JUL 2015

Sometimes you don’t want to expose your MySQL root user or password to a third party, but do need to give that party access to a super user or administrator account that has full control over the MySQL server instance.

Turns out, to add such a super user account is pretty easy.

First, access your MySQL console with:

mysql -u root -p

Once in, run the following SQL statement:

GRANT ALL PRIVILEGES ON *.* TO myusername@`%` IDENTIFIED BY 'mypassword';

(‘myusername’ and ‘mypassword’ should of course be substituted for your own values. Also, you don’t have to give the account such wide access by exchanging the any host % symbol with a specific IP/host if necessary)

Easy.

child dressed as a powerful superhero, complete with mask and cape

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 Change the Next Auto Increment Value in phpMyAdmin Tips, Tricks and Tutorials 12 FEB 2015

I needed a quick fix to an urgent problem the other day, and the best solution for that particular challenge turned out to be artificially manipulating tables’ ID primary keys in order to avoid clashes in a shared table when it came to foreign key entries. To do this I needed to set the ID keys of the different MySQL tables into different ranges, and to do that, I would need to manipulate or change the table’s auto_increment value to ensure that the next assigned ID value would be in my preferred range.

As it turns out, later versions of phpMyAdmin exposes this table property very nicely, making it particularly easy to change the auto increment value to whatever you want it to be.

To do this, select the table that you wish to make the change to. Once loaded, navigate to the Operations tab. The resulting screen will have a fieldset entitled Table options. If you look carefully, you’ll spot a field entitled AUTO_INCREMENT. This is the next auto incremented value the table will be using on the next row insert, and so this is the one you want to manipulate.

Change it to a valid entry for the currently applied to field type and hit ‘Go’ to save your change.

phpmyadmin operations change auto increment value

Literally as easy as that.

Related Link: http://www.phpmyadmin.net/home_page/index.php

MySQL: Quickly Load a CSV file into a Database Table Tips, Tricks and Tutorials 17 FEB 2014

20090107_MySQL_Logo2CSV (comma separated value) files are often used as a way to transfer data between two different systems – it’s a simple, verbose, and difficult to break (if you double quote every single string of course) way of saving grouped data into a flat text file.

Pleasingly, MySQL comes with a command line tool that makes it a snap to load a CSV file directly into a database table, namely mysqlimport – a utility described at best as a client that provides a command-line interface to the handy LOAD DATA INFILE SQL statement.

Generally you invoke mysqlimport pretty much like this:

shell> mysqlimport [options] db_name textfile1 [textfile2 ...]

For each text file named on the command line, mysqlimport strips any extension from the file name and uses the result to determine the name of the table into which to import the file’s contents. For example, files named ‘patient.csv’, ‘patient.txt’, and ‘patient’ all would be imported into a table named patient.

In addition to this basic call, you general tweak things via various settable options, for example specifying the user account and password to make use of, how the tool should break up values and lines, etc.

It is important to note that this is a relatively inflexible tool and as such requires good data inputs, and a pretty much near match in terms of available table columns versus the comma separated values.

This utility is a great way to automate data imports via the cron (or scheduled task handler), with my bash files often executing lines that look an awfully lot like this:

mysqlimport -u Myusername -pMypassword  --fields-enclosed-by='"' --fields-terminated-by=',' --lines-terminated-by='\r\n' --local Mydatabase datatoimport.csv

In other words, mysqlimport is a great way of grabbing CSV data and dumping it into your database fast!

Related Link: mysqlimport

How to quickly install the LAMP Stack on an Ubuntu Server Tips, Tricks and Tutorials 24 AUG 2013

ubuntu-10-logoIf you are running an Ubuntu server then chances are pretty good that you are using it as a web server. That in turn means you probably want to run it as a LAMP stack, i.e. Linux, Apache, MySQL, and PHP, or at least that’s what I need 90% of the time.

Of course the fact that you are running Ubuntu means that the Linux part of the stack is already taken care of meaning that the next thing to sort out is the installation of the Apache webserver, MySQL database server, and of course the web scripting workhorse that is PHP.

Now while you can install these things one by one, but if defaults are fine for you and time is of the essence, then thanks to the nifty tasksel Ubuntu command installing all three at once is a snap.

Basically tasksel groups software packages by tasks and offers an easy way to install all packages needed for that particular task. In other words, basically it does the same as a conventional meta-package.

If it isn’t installed by default, run:

sudo apt-get install tasksel

Once installed, you can run the application like so:

sudo tasksel

From there it is a matter of scrolling through the menu to locate the LAMP server option, press space to select it (an asterisk will appear next to the menu option) and then a final click on OK to begin the installation process (you can tab to the OK button if you don’t have a mouse available). Answer the MySQL root password prompt accordingly and once done, you should have a nice and shiny new installation of Apache, MySQL and PHP at your fingertips, verified by the standard “It Works!” message returned when hitting http://localhost

Note: I’m quite fond of following this up by installing the web-based database management tool PHPMyAdmin, which can be done via a call to:

sudo apt-get install phpmyadmin

Again, you’ll be asked for the root MySQL password to complete this installation, so it is probably worth having jotted this down somewhere! :)

How to Exclude Tables from a mysqldump SQL Export Software & Sites 15 MAY 2013

20090910-MySQL-logoWhen backing up or copying across a MySQL database, the command line mysqldump executable is invaluable in automating the creation of SQL dump files which can be used to create or populate database instances.

Now there are a myriad of options available to this little command, but the one I want to highlight today is the ability to ignore specific tables when doing a mysqldump of an entire database. This is particularly useful if you have an overly large table which doesn’t play nicely with an import process, say for example if you were storing base-encoded images in your database. –ignore-table is the switch, and in practice it looks like this:

mysqldump -u username -p database --ignore-table=database.table1 --ignore-table=database.table2 > database.sql

As you can see from the line above, it is possible to specify multiple tables to exclude by simply passing it in multiple times. Also worthwhile to note is the fact that you do need to prefix the table which is to be omitted with the database name. Failing to do this will result in an Illegal Usage error from mysqldump.

Nifty.

PHP Tip for when connecting to Multiple MySQL Databases in a Project Programming 25 APR 2013

php elephant iconIt is not very common but sometimes you’ll find yourself working on a project that features multiple databases holding all the information we need.

Now of course we know that when making an initial database connection we get a identifier on a successful connect, which we can then pass along to a mysql_query call as a parameter in order to force that query to happen on that particular database connection:

$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
mysql_query($mysqlquery, $conn);

And this will work 99% of the time. However, there is one instance when it won’t work, and is guaranteed to trip you up if you’ve never encountered this before. If you are using the same hostname, username and password across your databases, in other words the arguments passed to your mysql_connect call remains the same, mysql_connect will in fact not open a new connection on your secondary connect to another database, and instead just pass you the identifier it came up with on your first connect. Obviously this will break your code because the database you’re interested in using doesn’t exist on this first connection!

Luckily to get around this limitation is pretty simple. The mysql_connect function has an often neglected boolean parameter known as $new_link which if set to true, will force a new connection to be created regardless of whether or not the parameters for this connect request matches those of your previous connect request

//Doesn't work - $conn2 is never actually made, meaning your SQL statement will fail.
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
$conn2 = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase2', $conn2);
mysql_query($mysqlquery, $conn2);

//Does work - $conn2 is made, meaning your SQL statement will succeed.
$conn = mysql_connect('localhost', 'mysql_user', 'mysql_password');
mysql_select_db('mydatabase', $conn);
$conn2 = mysql_connect('localhost', 'mysql_user', 'mysql_password',true);
mysql_select_db('mydatabase2', $conn2);
mysql_query($mysqlquery, $conn2);

And now you know. Not that this scenario should happen very often mind you…