Category Archives: MySQL

ERROR 1290 (HY000): The MySQL server is running with the –secure-file-priv option so it cannot execute this statement

Posted on by
Reply

Problem

You want to export a MySQL database table as a csv with something like:

mysql> select * from table_name into outfile '/tmp/table_name.csv';

but you get the error that MySQL cannot execute this command.

Solution

Find out the location that MySQL can use to export files by running the following:

mysql> show variables like 'secure_file_priv';
+------------------+-----------------------+
| Variable_name    | Value                 |
+------------------+-----------------------+
| secure_file_priv | /var/lib/mysql-files/ |
+------------------+-----------------------+
1 row in set (0.00 sec)

and then use the path to change the outfile path (ie /var/lib/mysql-files/table_name.csv).

Taken from the answer here

Create a MySQL table with dashes

Posted on by
Reply

Problem

You want to create a MySQL table that contains spaces, but when you try you get the following error message:

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 '-afterdash' at line 1

Solution

Use the backticks to create the table:

mysql> create database `table-afterdash`;
Query OK, 1 row affected (0.00 sec)

Using tables with dashes in MySQL queries

Posted on by
Reply

Problem

You have some databases in MySQL that contain dashes in their names, and when you try to use them (ie grant access to them), you get the following error:

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 '-db.* to 'username'@'%'' at line 1

Solution

To be able to use them, enclose the database name in backticks (`) like:

GRANT ALL on `databasename-db`.* to 'username'@'%'

rails `require’: cannot load such file — mysql2/mysql2 (LoadError)

Posted on by
Reply

Problem

You want to use a different ruby version from the one you have initially installed and build your application with, but when you change it in your rvm installation you get the following error:

..gems/mysql2-0.3.13/lib/mysql2.rb:8:in `require': cannot load such 
file -- mysql2/mysql2 (LoadError)

Solution

To make it work again you will have to uninstall the mysql2 gem and install it again in the new ruby version with the option –platform=ruby.

rvm use ruby-2.0.0-p247
cd my_project
rails s
..... gems/mysql2-0.3.13/lib/mysql2.rb:8:in `require': cannot load 
such file -- mysql2/mysql2 (LoadError)
gem uninstall mysql2
gem install mysql2 --platform=ruby
rails s
=> Booting WEBrick
....

Exporting query results from MySQL

Posted on by
Reply

Problem

You would like to export some query results from MySQL into a format that can be imported in LibreOffice, OpenOffice or Excel.

Solution

Try first with the default export options, as by doing a google search, there are a lot of different options for specifying field delimeters, lines terminated, etc.
So by not specifying any additional options the export is a tab delimited, linefeed-terminated file which should work in most of the programs.
So in order to do that you would need a folder in the MySQL server that should have write access. Usually /tmp should do. Then you can build your query and at the end add the INTO OUTFILE ‘/path’, like:

SELECT one,max(two) max_two,min(three) min_three 
FROM table 
WHERE one=1 
GROUP BY one 
INTO OUTFILE '/tmp/my_query.csv';

You can use any file ending but usually by specifying .csv the program that you will try to open will understand it.

Enabling MySQL server to be remotely accessible in Amazon EC2

Posted on by
Reply

Problem

You want to make your instance’s mysql server in Amazon available remotely, and you have set the firewall setting in your security group to allow access to port 3306, but you get the following error:

ERROR 2003 (HY000): Can't connect to MySQL server

Solution

You would need to edit your /etc/mysql/my.cnf file to allow access to the mysql server by changing the line:

bind_address 127.0.0.1

to 

bind_address AMAZON_PRIVATE_IP

Make sure that the IP used is the private ip and not the Elastic IP.
You should be able to find that in your instance control panel.

Adding timezone information in MySQL

Posted on by
Reply

Problem
You would like to convert times in MySQL with the CONVERT_TZ function and to be able to use timezone names (ie CE).

Problem

  1. Find out if your MySQL includes timezone information as it is not included by default. Log in and run : select count(*) from mysql.time_zone_name;
  2. If the result is 0 then the timezone information is not included
  3. Log out from your MySQL server on the command prompt and upgrade your server with: mysql_upgrade -p
  4. On the command prompt again load up the table with the data (linux server): mysql_tzinfo_to_sql /usr/share/zoneinfo/ | mysql -u root -p mysql/li>
  5. Login to your MySQL server again and run the first query: select count(*) from mysql.time_zone_name;