Archive for category MySQL

MySQL5 Problem while creating new user – SQL Error (1133): Can’t find any matching row in the user table

MySQL 5.5.8

MySQL 5.x

When we use GUI tools like HeidiSQL with MySQL Windows binaries – setup from MySQL.com might be creating issues like these. Because recently faced errors #1364 and #1133 both became visible while I did clean install of MySQL by downloading Windows Installer for MySQL 5.5.8 from MySQL.com and not while using it with MySQL as part of some WAMP package.

It must be because the way some parameters come as defaults while we use MySQL installer or might be the way we configure the MySQL Instance using MySQL instance configuration wizard.

SQL Error (1133): Can’t find any matching row in the user table

Now we will get above error message due to SQL_MODE variable value set to NO_AUTO_CREATE_USER, you can check value for your server’s sql-mode by using following SQL:

SHOW VARIABLES LIKE 'SQL_MODE'

Output can be something like STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION as we can specify multiple values separated by comma. NO_AUTO_CREATE_USER won’t let you create users with empty passwords.

Prevent the GRANT statement from automatically creating new users if it would otherwise do so, unless a nonempty password also is specified. (Added in MySQL 5.0.2)

If you’re using HeidiSQL 6 or earlier you won’t be able to create users even if you specify password due to the way HeidiSQL constructs the SQL to create user. See the screen-shot: Read the rest of this entry »

Tags: , ,

MySQL 5.5.8 – Problem while Creating new User (SQL Error (1364): Field ‘authentication_string’ doesn’t have a default value)

MySQL 5.5.8

MySQL 5.5.8

I have just downloaded and setup MySQL 5.5.8 community server for Windows (32bit Installer) from MySQL official downloads. After downloading while I tried connecting it with HeidiSQL 5.x I got following error “Problem while Creating new User (SQL Error (1364): Field ‘authentication_string’ doesn’t have a default value)“.

It seems like it’s a problem with HeidiSQL-5, probably due to the fact that table schema for mysql.users table might be changed in MySQL 5.5.8. You might see something like following if you’re using HeidiSQL-5.

MySQL5.5 > Create User error while using HeidiSQL 5.0

MySQL5.5 > Create User error while using HeidiSQL 5.0

Solution: There are 2 ways to fix the problem:

A) Download HeidiSQL 6.0 and try creating user again. Read the rest of this entry »

Tags: , ,

Using Views to avoid cross database connection – MySQL

Table Views can be used as an unconventional way to avoid cross/multiple database connections sometimes..! Yes, it can be. I came across such case and found that it can be handy in some simple cases when we just want to avoid connection to another database on the same MySQL server and we can’t afford to create that table in the same database due to some reasons.
Views

Views - Image courtesy Flickr

It’s very simple. You can have your table “contacts” in “my_db” database and you want to access this table (not just read need to write/insert and update too) in another database named “another_db”.
You can have your “contacts” table in “my_db” with following syntax: Read the rest of this entry »

Tags: ,

HeidiSQL 5.1 – good has got better

HeidiSQL

HeidiSQL

HeidiSQL has been excellent SQL Administration and Development tool so far for me. HeidiSQL has active development team which keeps adding new feature and upgrading and correcting bugs to get us the great tool for MySQL development.

I have been using HeidiSQL since more than three and half years now and I am really happy with it. I must say that we work with heavy database consisting of more than 400 tables and database size exceeding few GBs but HeidiSQL has never let us down. I have tried loading my development database with phpMyAdmin and SQL Buddy both but they just failed to load such a large database with few hundreds of tables..! But with HeidiSQL I never had any problem except I got some crashing sometime in newer 5.x version but I am sure all such things will be fixed in coming builds. I am not telling that there are no other good tools are available in the market but HeidiSQL is in premium open-source tool at free of cost. I remember I had written a post on HeidiSQL 4.x more than a year ago and today we are up with HeidiSQL 5.1. Read the rest of this entry »

Tags: , ,

Export/Import CSV files with MySQL – No external tool required

Loading data from and to CSV or other TAB DELIMITED or similar file format is essential in day to day operation. With MySQL it is easy to load data into table using files and exporting data into CSV files is quite easy. No need to use any external tool it can be done right there from the query tool/command prompt, no specific GUI tool required.

Let’s see how?

Exporting data as CSV file directly using simple MySQL Query

Simply add INTO OUTFILE [FILE_PATH] in your query and you are done. Here is a simple example:

SELECT * FROM [TABLE]
INTO OUTFILE 'X:/[FILE]'

Above statement uses default options for file export of result set of a query, but it is simple to override them. By default file will be TAB DELIMITED file. We can always override as showed in example below:

SELECT * FROM  [TABLE] INTO OUTFILE 'X:/[FILE]'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'

OR

SELECT * INTO OUTFILE 'X:/[FILE]'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM  [TABLE]

The beauty is it don’t really matter where you place FROM TABLE clause, any of the style would work from above example. Don’t forget that we can use all our complex join or where conditions which we really use with SELECT. Read the rest of this entry »

Tags:

backup/restore MySQL db using MYSQLDUMP (quick reference)

Whether you are using MySQL with MyISAM or InnoDB or other database engine it is possible to backup/restore database using MYSQLDUMP utility. Though if you have all tables in MyISAM format MYSQLHOTCOPY is faster option but in any case MYSQLDUMP is a handly tool if you have got a mix set of tables.

Commands are very quick simple and easy to execute and can be made automated by a PHP or other script/tool.

How to backup using mysqldump?:

Before you use any of commands you have to move to mysql/bin directory of your MySQL installation and MySQL server should be up and running.

To backup all MySQL database on a server:

mysqldump --user=[USER] --password=[PASSWORD] -A > C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL

To backup specific MySQL database on a server:

mysqldump --user=[USER] --password=[PASSWORD] [DATABASE] > C:/BACKUP_PATH/BACKUP_TIMESTAMP.SQL

To backup certain tables in a MySQL database: Read the rest of this entry »

Tags:

HeidiSQL 4.0 RC1 released

I am not sure how many MySQL developers are using HeidiSQL for development but I am sure those who are using it will be fan of some of it’s features.!

I have been using HeidiSQL tool for my php/mySQL development for a while now. (more than a year and half..!). Before few months I have upgraded to newer version of HeidiSQL which is 4.1 RC1. It has got quite new and exciting features, looks and skins are improved. Read the rest of this entry »

Tags: , ,

Paying the bills.!