MySQL

From 123developers

Contents 1 MySQL - FAQ 1.1 How can I detect that MySQL is already installed? 1.2 Operators Used In MySQL 1.2.1 Comparison Operators 1.2.2 Mathematical Operators 1.3 Maximum number of columns in a table 1.4 Why am I getting an error message like Access denied for user: 'user_name@localhost'? 1.5 I am getting an error message telling me to 'Unblock with mysqladmin flush-hosts' 1.6 What is prepared statement in MySQL? 1.7 Why values with spaces do not come into search result? 1.8 How to take data back up of single table? 1.9 How to find duplicate records from a table in MySQL? 1.10 How to skip repeating values from the query results? 1.11 ERROR 1064 (42000): You have an error in your SQL syntax

MySQL - FAQ

How can I detect that MySQL is already installed?

ANS:By the following ways, you can detect that MySQL is already installed or not.

Firstly, If there is no firewall in between, you may check to see if port 3306 is responding as the MySQL Server uses port 3306 by default. Secondly, it is very possible to have multiple installs of the MySQL on a server without conflict, so done correctly, you won't trash any other MySQL installs. You can simply put all files at another location (your program's folder) and write the my.ini/my.cnf file on the fly, changing the datadir and other options for it to work wherever your program will be located. Thirdly, you can also use sockets or named pipes instead of TCP/IP to ensure you won't block any port or mess with the network, starting your server without networking support. Further, you have to figure out if you use PHP if so you must check to see if it is installed as well, and compiled with MySQL support.

Operators Used In MySQL

Comparison Operators

=	Equal
<=	Less than or Equal to
>	Greater than
>=	Greater than or Equal to
<>	Not Equal to
!=	Not Equal to

Mathematical Operators

()	Paranthesis
Plus	Add
Minus	Substract
*	Multiply
/	Divide
%	Modulo

Maximum number of columns in a table

ANS: As of our understanding, The maximum number of columns for any storage engine depends upon the option "AVG_ROW_LENGTH". If your length of the column name is large, then number of columns will be less and obviously vice-versa also holds true.

The options MAX_ROWS * AVG_ROW_LENGTH will decide the maximum tablesize.

To get the current values, use;

SHOW TABLE STATUS LIKE 'tablename';

Why am I getting an error message like Access denied for user: 'user_name@localhost'?

ANS:You may get this error message, after logging in to your MySQL account using SSH and with the command,

mysql -u user_name -p 

You are getting this error , because the user, user_name, does not have the right to access your MySQL DB. To fix it, you need to create a user, using the following command (Replace firstDB with your database name, user_name with your user name, and password with the user's password).

mysql> GRANT ALL ON *.* to user_name@localhost IDENTIFIED BY 'password'; 

I am getting an error message telling me to 'Unblock with mysqladmin flush-hosts'

ANS:If you are getting an error message like the following:

"Error connecting to MYSQL server. Host 'viator.ucs.indiana.edu' is
blocked because of many connection errors.  Unblock with 'mysqladmin
flush-hosts'"

You should login to your account on the MySQL servers (mysql.server.edu or mysql-test.server.edu) and stop and restart your MySQL instance.

What is prepared statement in MySQL?

ANS:MySQL Server supports prepared statements, which are useful when you want to run several queries that differ only in very small details. For example, you can prepare a statement, and then execute it multiple times, each time with different data values.

Besides offering a convenience, prepared statements also provide enhanced performance because the complete statement is parsed only once by the server. When the parse is complete, the server and client may make use of a new protocol that requires fewer data conversions (and usually makes for less traffic between the server and client) than when sending each statement individually.

In most circumstances, statements are prepared and executed using the programming interface that you normally use for writing applications that use MySQL like PHP (In PHP mysqli extension supports prepared statement functionality. The mysqli extension is recommended for safety from sql injetions and good performance more than mysql classic). However, to aid in testing and debugging, it is possible to define and use prepared statements from within the mysql command-line client.

Here is a short example that illustrates the use of a prepared statement. It prepares a statement that returns us first name of user against the given id, executes it multiple times, and disposes of it:

Why values with spaces do not come into search result?

ANS:To trim spaces from the value of its both sides, MySQL provides TRIM() function. You can use TRIM to fix spaces problem in your select queries. Take an example:

SELECT * FROM tbl_name WHERE TRIM(field_name) = 'searching value'

How to take data back up of single table?

CREATE TABLE backup_MyTable SELECT * FROM MyTable;

How to find duplicate records from a table in MySQL?

SELECT emp_email_address FROM table GROUP BY emp_email_address HAVING COUNT(*)>1;

How to skip repeating values from the query results?

SELECT col_1, col_2, col_3, .... FROM  table_name  GROUP BY col_1;

ERROR 1064 (42000): You have an error in your SQL syntax

ANS:Because you may used a RESERVED word of MySQL.