BLOGS@DiGiTSS

DiGiTSS Team's Programming experience with PHP, MySQL, Ajax, Javascript, jQuery, C# and Microsoft technologies

Archive for category Database

Alternate to FIND_IN_SET for non-MySQL databases

Mar 6

Posted by Dharmavirsinh Jhala in Database | No Comments

Missing MySQL

Missing MySQL

If you are MySQL user initially at the beginning of your career and later on as the career progresses got in touch with other RDBMS then you might miss MySQL's FIND_IN_SET function badly. :)

What MySQL's FIND_IN_SET does is it returns position of the matched element in the SET. That SET of items must be comma-separated.

For example take following code into consideration:

SELECT FIND_IN_SET('b','a,b,c,d');

It will return "2" as the result. So generally what we have been doing is we use it not to find whether particular element is at what position in the comma-separated list but just to find simply that whether item exists in the list at all or not?

For that we use SQL something like following:

SELECT COUNT(0) FROM products WHERE FIND_IN_SET('7', category_id);

Above query will give you count of records which are having category_id 7 along with or without other categories. In table records might have values comma-separated like '4,5,7' or '7' or '2,7,8' or anything. So above expression in WHERE will return value greater than "zero" (0) and that row will be returned in result or calculated in count. Here category_id in products table is of type varchar.

Now when we are not using MySQL many times we can get similar result if we are not worried about the position of the item in the set but just want to make sure that it exists or not. It can be achieved with following SQL statement. Read the rest of this entry »

Tags: ,

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

Jan 16

Posted by Dharmavirsinh Jhala in MySQL | 2 Comments

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)

Jan 16

Posted by Dharmavirsinh Jhala in MySQL | 4 Comments

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

Jul 6

Posted by Dharmavirsinh Jhala in MySQL | No Comments

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

Jul 5

Posted by Dharmavirsinh Jhala in MySQL | 4 Comments

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

Nov 14

Posted by Dharmavirsinh Jhala in MySQL | 5 Comments

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)

Oct 25

Posted by Dharmavirsinh Jhala in MySQL | 1 Comment

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:

Paying the bills.!