MySQL: Bulk import and logging the warnings to a file

Platform: Any UNIX*

If you are working on a small set of data or with fewer tables and if you notice that the DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE operation has generated few warnings then you can just execute the “SHOW WARNINGS” SQL command to display the exact warning messages generated during the last DML operation.
But “SHOW WARINGS” shows nothing if the last statement used a table and generated no messages.
SHOW WARNINGS shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session.
“SHOW WARINGS” can certainly help you in grabbing the warnings but what if you are loading a huge dump file or performing a batch operation and at the middle of load operation MySQL reports that the last DML completed with 500000 warnings? Certainly not sounds good if you are serious about your data.

Query OK, 3000000000 rows affected, 500000 warnings (xxxxx.xxx xxxx)
Records: 3000000000 Duplicates: 0 Warnings: 500000


So how do you grab those warnings?
You can use the --show-warnings parameter with the “mysql” command line utility which causes warnings to be shown after each statement if there are any. This option applies to interactive and batch mode.

/path/to/mysql --show-warnings –uUserName –pPassword DBNAME < /path/to/dump_file.sql >> /path/to/load_warnings.log 2>&1
The 2>&1 will write standard output and standard errors to a file (/path/to/load_warnings.log)

You may not see warnings
(i) If max_error_count is set to 0. In this case, warning_count still indicates how many warnings have occurred, but none of the messages are stored.
(ii) if sql_notes session variable is set to 0 to cause Note-level warnings not to be recorded.


##On EE
ttp://www.experts-exchange.com/Database/MySQL/Q_26835461.html

MySQL: Kill sleeping connections

Platform: MySQL 5.x

Most of the time it would be handy to have a native MySQL script which would allow one to kill the sleeping connections which are in sleep state for more than 180 sec..  On the other hand DBA's can use  "wait_timeout" etc parameters to control this..

[code]

 # -- Make sure you are logged as MySQL 'root' user or any user who have got super privileges 



DELIMITER $$
DROP PROCEDURE IF EXISTS `uKillSleepingSessions`$$


CREATE PROCEDURE `uKillSleepingSessions`()
COMMENT 'This routne is used to kill idle sessions'
READS SQL DATA


BEGIN


DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE uID bigint(4);

DECLARE my_cur CURSOR FOR
SELECT ID
  FROM information_schema.PROCESSLIST PL
WHERE PL.COMMAND='Sleep' AND PL.TIME > 180;


DECLARE CONTINUE HANDLER FOR NOT FOUND
SET no_more_rows = TRUE;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP


FETCH my_cur
INTO uID;


IF no_more_rows THEN
    CLOSE my_cur;
    LEAVE the_loop;
END IF;


SET @tmp_sql= CONCAT("KILL ",uID);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;


DEALLOCATE PREPARE s1;
SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;


END$$
DELIMITER ;



# Usage from mysql prompt/gui client
# Once called , it would cleanup idle connections which are sleeping for more than 180 sec
call uKillSleepingSessions();

[/code]

Cannot create Windows service for MySQL.Error:0

Platform: Windows


People often checks MySQL's download section and when they notice that there is new version of MySQL released they just can't wait for a second & starts downloading it so that they can install it on their windows box for testing purpose. Ok fine, download is completed its time to install it. Couple of successful "NEXT" and In the last step they encounter a error message such as

"Cannot create Windows service for MySQL.Error:0"

We are so intelligent ppl that we don't want to take any help from our colleague..
And what we do is we just delete the directory in which new installation has been done & starts the installation
wizard one-more time in the hope that this time we gonna install it.

Again the result is same "Cannot create Windows service for MySQL.Error:0"

So why are we getting error that too at the last step? one common thought is "there must be something wrong
or some sort of bug in the new release"

How to resolve this error?
The primary reason for this error is that you haven't properly uninstalled the 
previously installed MySQL server & in the installation wizard  last step was
 to create  the service with the name "MySQL" and which has the same name as that 
 of the existing service (default is "MySQL").

Solution for this error is to first stop the service if it is running and then remove 
the service.

C:\>NET STOP MYSQL
The MySQL
service is stopping.
The MySQL service was stopped successfully.

Once the service is stopped, it can be removed with the SC utility:
C:\>SC DELETE MYSQL

[SC]
DeleteService SUCCESS

With the previous service stopped and removed, you can now run the MySQL Configuration Wizard again to configure and start the new MySQL service.

MySQL: Copy tables (Only structure and NO DATA) using stored procedure

Last week some one asked how to Copy the tables (Only structure and NO DATA..also no other DB objects) from one schema to another on EE. This can be easily done from command line but user wanted to do this thru stored procedure.
http://www.experts-exchange.com/Database/MySQL/Q_25024073.html
So I came with a very small MySQL procedure which was doing as needed by the user. I'm not sure whether this is the best way to do this but "There is always room for improvement."
 
DELIMITER $$

DROP PROCEDURE IF EXISTS `CopySchema`$$
CREATE PROCEDURE `CopySchema`(sourceSchema VARCHAR(64),targetSchema VARCHAR(64))


BEGIN

DECLARE no_more_rows BOOLEAN;
DECLARE loop_cntr INT DEFAULT 0;
DECLARE num_rows INT DEFAULT 0;
DECLARE my_table VARCHAR(64);

DECLARE my_cur CURSOR FOR
SELECT TABLE_NAME AS myTable
 FROM information_schema.TABLES
WHERE information_schema.TABLES.TABLE_SCHEMA=sourceSchema AND TABLE_TYPE = 'BASE TABLE';


DECLARE CONTINUE HANDLER FOR NOT FOUND


SET no_more_rows = TRUE;
SET @tmp_sql= CONCAT("CREATE DATABASE IF NOT EXISTS ",targetSchema);
PREPARE s1 FROM @tmp_sql;
EXECUTE s1;
DEALLOCATE PREPARE s1;


OPEN my_cur;
select FOUND_ROWS() into num_rows;

the_loop: LOOP
    FETCH my_cur
      INTO my_table;

     IF no_more_rows THEN
         CLOSE my_cur;
         LEAVE the_loop;
     END IF;


     SET @tmp_sql= CONCAT("CREATE TABLE IF NOT EXISTS ",targetSchema,'.',my_table,' LIKE ',sourceSchema,'.',my_table);
      PREPARE s1 FROM @tmp_sql;
      EXECUTE s1;
     DEALLOCATE PREPARE s1;

     SET loop_cntr = loop_cntr + 1;
END LOOP the_loop;

SELECT CONCAT('Summary: ', loop_cntr, ' tables copied from schema "',sourceSchema,'" to "',targetSchema,'"') AS "Schema copying";


END$$
DELIMITER ;

Usage:

call CopySchema('dba','dba_dummy');

ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).

Today morning when I was trying to execute a peice of SQL code and all the time I was welcomed by the below error.

ERROR 1037 (HY001): Out of memory; restart server and try again (needed 123456789 bytes).

If we get this kind of error message then the first thing we need to check is the available memory on the server and have to ensure that the server has not run out of disk space for the swap file and also need confirm that there is enough free memory for MySQL to grow.

 This indicate that we need to increase or decrease the values of some buffers to manage memory usage more efficiently otherwise MySQL might complain about the same thing by means of above error message.

MySQL - Extract numbers out of a string

Yesterday on EE I saw a very interesting request from a user for "Extracting numbers out of a string".
This could be done in other languages with just 1 liner code but he needed it inside a SELECT query.
 http://www.experts-exchange.com/Programming/Languages/SQL_Syntax/Q_24855357.html

So I came with a very small MySQL function which was doing as needed by the user. I'm not sure whther this is the best way to do this but "There is always room for improvement."
 
 
DELIMITER $$



DROP FUNCTION IF EXISTS `uExtractNumberFromString`$$
CREATE FUNCTION `uExtractNumberFromString`(in_string varchar(50)) RETURNS INT
NO SQL


BEGIN


DECLARE ctrNumber varchar(50);
DECLARE finNumber varchar(50) default ' ';
DECLARE sChar varchar(2);
DECLARE inti INTEGER default 1;


IF length(in_string) > 0 THEN


WHILE(inti <= length(in_string)) DO
    SET sChar= SUBSTRING(in_string,inti,1);
    SET ctrNumber= FIND_IN_SET(sChar,'0,1,2,3,4,5,6,7,8,9');


    IF ctrNumber > 0 THEN


       SET finNumber=CONCAT(finNumber,sChar);
    ELSE


       SET finNumber=CONCAT(finNumber,'');
    END IF;


    SET inti=inti+1;
END WHILE;
RETURN CAST(finNumber AS SIGNED INTEGER) ;
ELSE
  RETURN 0;
END IF;


END$$



select uExtractNumberFromString('12;e1hhsak123s12');
12112312

Client does not support authentication protocol requested by server; consider upgrading MySQL client

Reference: MySQL Manual

If you are trying to evaluate some free open source application available on various websites(which are especially developed using PHP) and when the application try to access newer version of MySQL server you may end up seeing a error message like below


Client does not support authentication protocol requested by server; consider upgrading MySQL client


 Reason for this unwanted welcome message is that In older versions of PHP, the mysql extension does not support the authentication protocol used in MySQL 4.1.1 and higher. This is true regardless of the PHP version being used. If you wish to use the mysql extension with MySQL 4.1 or newer, you may need to follow one of the options discussed above for configuring MySQL to work with old clients. The mysqli extension (stands for "MySQL, Improved"; added in PHP 5) is compatible with the improved password hashing employed in MySQL 4.1 and higher, and no special configuration of MySQL need be done to use this MySQL client library.
 MySQL >=4.1 uses an authentication protocol based on a password hashing algorithm that is not compatible with that used by older (< 4.1) clients.


There are several ways through which you can resolve this type of issues


- Tell the server to use the older password hashing algorithm
   (i) Start mysqld with the --old-passwords option(also, you can place old-passwords in my.cnf/my.ini under mysqld group).
   (ii) Assign an old-format password to each account that has had its password updated to the longer 4.1 format. You can identify these accounts with the following query: 
 mysql> SELECT Host, User, Password FROM mysql.user
                            -> WHERE LENGTH(Password) > 16;
For each account record displayed by the query, use the Host and User values and assign a password using the OLD_PASSWORD() function and either SET PASSWORD or UPDATE



For further options please take a look at the manual page
http://dev.mysql.com/doc/refman/5.1/en/old-client.html

Scheduled tasks in MySQL

Couple of days ago, during the training session one of the trainee(experienced in other DBMS) was really curious to know on how to setup & use Scheduled tasks in MySQL. I was also equally interested to spend some time on "Event Scheduler" as I was also getting a chance to show that how MySQL is equipped with the rich features but unfortunately the discussion ended before taking off. Most of the ppl felt its just a matter of one line adding to the crontab  & job is done. So most of users were not aware of the fact that MySQL has added a feature which could do what the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler can.

MySQL Events are tasks that run according to a schedule due to this can also be referred as scheduled events. When you create an event, you are creating a named database object containing one or more SQL statements to be executed at one or more regular intervals, beginning and ending at a specific date and time. Conceptually, this is similar to the idea of the Unix crontab  (also known as a “cron job”) or the Windows Task Scheduler.

(From Manual)

The global event_scheduler system variable determines whether the Event Scheduler is enabled and running on the server.

Version Introduced           5.1.6
Command Line Format     --event-scheduler[=value]
Config File Format           event-scheduler
Option Sets Variable        Yes, event_scheduler
Variable Name                 event_scheduler
Variable Scope                 Global
Dynamic Variable             Yes
Permitted Values   
            Type                    enumeration
            Default                OFF
            Valid Values         ON, OFF, DISABLED

From the mysql prompt you can enable/disable  using any one of the following statements

mysql>SET GLOBAL event_scheduler = ON;
mysql>SET @@global.event_scheduler = ON;
mysql>SET GLOBAL event_scheduler = 1;
mysql>SET @@global.event_scheduler = 1;

Also, In the server configuration file (my.cnf, or my.ini on Windows systems), include the line where it will be read by the server (for example, in a [mysqld] section): 

event_scheduler=ON|OFF|DISABLED


So now we know that what needs to be done at the server level in order to use the feature. Okay the platform is ready for event_scheduler and can demonstrate you with a tiny example.
 
 Just ensuring that event_scheduler is enabled or not

show global variables like 'event_scheduler';
event_scheduler    ON

 The result of the SQL command ensures me that "event_scheduler" is enabled now and I can use the feature(but this settings should be made permanent by adding it to config file ).

Syntax for CREATE EVENT

CREATE EVENT [ IF NOT EXISTS ] event_name
ON SCHEDULE schedule
[ ON COMPLETION [ NOT ] PRESERVE ]
[ ENABLED | DISABLED ]
[ COMMENT 'comment' ]
DO sql_statement;

In which - the "event_name" must be a valid identifier of up to 64 characters (e.g me_ushastry, me_trainer etc ushastry1978 etc), with delimiters allowed (`Something To Do`), possibly qualified (database1.event1). Events are database objects, so they are stored within a database and event names must be unique within that database. When checking for uniqueness, MySQL uses case-insensitive comparisons.

The "schedule" can be a timestamp in the future, a recurring interval, or a combination of recurring intervals and timestamps.

The possibilities are:

• AT timestamp [+ interval integer_value time_keyword ]
• EVERY interval
• EVERY interval STARTS timestamp
• EVERY interval ENDS timestamp
• EVERY interval STARTS timestamp ENDS timestamp

"AT timestamp" means "Do this once at the specified time". The timestamp must contain both date and time (that is, it must be a DATETIME or TIMESTAMP value) and must be in the future -- you cannot make an event which is supposed to have already occurred. To specify an exact time, you can also add an interval to the timestamp (using + INTERVAL, a positive integer and one of YEAR, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND); this makes sense only when you're using the CURRENT_TIMESTAMP function. Here are two examples:

Scenario is like we have a table named `tbl_users` and we have to delete the records from the table every  5 minutes and we want to automate this process so that no one has to sit and run the piece of SQL code manually.



 # Checking "event_scheduler"

create database mysqlscheduler;
use mysqlscheduler;

CREATE TABLE IF NOT EXISTS `tbl_users`
(                     
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT, 
    `name` varchar(100) DEFAULT NULL,              
    `email` varchar(100) DEFAULT NULL,              
    `promo_emails` enum('YES','NO') DEFAULT 'NO',
    PRIMARY KEY (`id`)                             
) ENGINE=MyISAM;




# Add couple of records for testing


INSERT INTO mysqlscheduler.tbl_users VALUES('','Umesh','ushastry@gmail.com','NO'),('','Umesh','ushastry_duplicate@gmail.com','YES'),('','Umesh','ushastry_dummy@gmail.com','NO');

SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
2    Umesh    ushastry_duplicate@gmail.com    YES
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO



 Now time to create EVENT which would take care of deleting the records


CREATE EVENT drop_dummy_user
ON SCHEDULE EVERY 15 MINUTE
STARTS TIMESTAMP '2009-01-01 00:00:00'
ENDS TIMESTAMP '2009-12-31 23:59:00'
DO DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES';

C:\ushastry\MySQL_Com\mysql-5.1.34-win32\bin>mysql -uroot -p

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 26
Server version: 5.1.34-community MySQL Community Server (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: NULL
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)

mysql>

Above command output can be read as

• the db column shows the name of the database that contains the event
• the name column holds the name of the event; recall that it must be unique within the database
• the definer column shows whose privileges will be checked when the event is executed; if the user shown no longer has the proper privileges to execute the event's SQL statement, an error will be returned
• the execute_at column shows when the event should be executed; this is always NULL unless the ON SCHEDULE clause uses the AT timestamp option
• the interval_value column shows the numeric value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the interval_field columns shows the datetime value of how often the event will be executed; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the created column shows the timestamp when the event was created
• the modified column shows the timestamp when the event was last modified; this will be the same as the created column if ALTER EVENT has never changed the event
• the last_executed column shows the timestamp of the last time the event was executed; in this example, it is NULL because the newly-created event hasn't been executed yet
• the starts column shows the timestamp when the event will start executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the ends column shows the timestamp when the event will stop executing; this value will be NULL if the ON SCHEDULE clause uses the AT timestamp option
• the status column shows whether the event is currently enabled or disabled; in this example, it shows ENABLED, the default value
• the on_completion column shows whether the event will be preserved or dropped when it is finished; in this example, it shows that the event will be preserved, as specified in the CREATE EVENT statement
• the comment column shows the comment included in the CREATE EVENT statement

After 15 min or so you can check and confirm that the records has been deleted 


SELECT * FROM mysqlscheduler.tbl_users;

1    Umesh    ushastry@gmail.com    NO
3    Umesh    ushastry_dummy@gmail.com    NO
\N    \N    \N    NO


Also, can be verified from the event meta data that when it was executed last time



mysql> SELECT * FROM mysql.event\G
*************************** 1. row ***************************
                  db: mysqlscheduler
                name: drop_dummy_user
                body: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
             definer: root@localhost
          execute_at: NULL
      interval_value: 15
      interval_field: MINUTE
             created: 2009-09-02 14:51:45
            modified: 2009-09-02 14:51:45
       last_executed: 2009-09-02 14:00:00
              starts: 2009-01-01 00:00:00
                ends: 2009-12-31 23:59:00
              status: ENABLED
       on_completion: DROP
            sql_mode:
             comment:
          originator: 1
           time_zone: SYSTEM
character_set_client: utf8
collation_connection: utf8_general_ci
        db_collation: latin1_swedish_ci
           body_utf8: DELETE FROM mysqlscheduler.tbl_users WHERE promo_emails='YES'
1 row in set (0.00 sec)
 


More details can be found here - http://dev.mysql.com/doc/refman/5.1/en/events.html

MySQL - Restoring a single table from nightly backup (mysqldump generated file)

Very often we get the requests from customer to restore a "tiny" table from the nightly backup into the development/staging environment. This request need to be done on urgent basis as its just a matter of a one table and customer don't want to restore entire database of 10GB so the restoration might take less than 5 min - this is how most of the customer/managers thinks and they are abs right as why should it take more time for a single table?. This can be addressed by playing with the privileges and with the help of parameters available to 'mysql' command line utility

Steps to restore single table from dump file

# Create a user & GRANT him all rights on the table which need to be restored
# Start importing as show below

mysql -uRestUser -pSecret --force --one-database DB_NAME < /path/to/dumpfile.sql

ResetUser - "user" created for restoring single table

--one-database - Ignore statements except those for the default database named on the command line

--force - Parameter would force the import to continue even if an SQL error occurs( mainly due to the privileges as the new user don't have the privilege on other tables)

Step-by-Step guide for Installing MySQL on RHEL5

Installation of MySQL Server on Linux (RedHat Enterprise Linux 5)

The recommended way to install MySQL on RPM-based Linux distributions is by using the RPM packages. The RPMs that MySQL/Sun provide to the community should work on all versions of Linux that support RPM packages and use glibc 2.3. You can download it from http://mirrors.sunsite.dk/mysql/downloads/mysql/5.1.html

Checking if MySQL is already installed & would be uninstalling it to install latest MySQL version

[root@localhost mysql]# rpm -qa | grep -i '^mysql-'
MySQL-python-1.2.1-1
mysql-5.0.22-2.1.0.1
mysql-server-5.0.22-2.1.0.1
mysql-connector-odbc-3.51.12-2.2

Un-installing older version of MySQL

[root@localhost mysql]# rpm --nodeps -ev MySQL-python-1.2.1-1
[root@localhost mysql]# rpm --nodeps -ev mysql-5.0.22-2.1.0.1
[root@localhost mysql]# rpm --nodeps -ev mysql-server-5.0.22-2.1.0.1
warning: /var/log/mysqld.log saved as /var/log/mysqld.log.rpmsave
[root@localhost mysql]# rpm --nodeps -ev mysql-connector-odbc-3.51.12-2.2
[root@localhost mysql]#


First, using a web browser or wget utility, you should download the server RPM from the location: http://www.mysql.com/Downloads/MySQL-*.rpm
Once you download the packages, install it as shown below.


[root@localhost mysql]# ls -la
total 39572
drwxr-xr-x 2 root root 4096 Jul 26 17:26 .
drwxr-xr-x 3 root root 4096 Jul 26 16:40 ..
-rw-r--r-- 1 root root 6664128 Jun 11 14:07 MySQL-client-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 10616677 Jun 11 14:17 MySQL-devel-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 19613508 Jun 11 14:26 MySQL-server-community-5.0.83-0.rhel5.i386.rpm
-rw-r--r-- 1 root root 3532055 Jun 11 14:31 MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm

Here is brief details of packages that we are going to install

MySQL-server-community-5.0.83-0.rhel5.i386.rpm: The MySQL server. You need this unless you only want to connect to a MySQL server running on another machine.
MySQL-client-community-5.0.83-0.rhel5.i386.rpm: The standard MySQL client programs. You probably always want to install this package.
MySQL-devel-community-5.0.83-0.rhel5.i386.rpm: The libraries and include files that are needed if you want to compile other MySQL clients, such as the Perl modules.
MySQL-shared-compat-5.0.83-0.rhel5.i386.rpm: This package includes the shared libraries for MySQL 3.23, 4.0, and so on, up to the current release. It contains single-threaded and thread-safe libraries. Install this package instead of MySQL-shared if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.


To perform a standard minimal installation, you can only install the server and client RPMs and for complete installation you have to install all the packages.



[root@localhost mysql]# rpm -ivh MySQL-*.rpm
Preparing... ########################################### [100%]
1:MySQL-shared-compat ########################################### [ 25%]
2:MySQL-client-community ########################################### [ 50%]
3:MySQL-devel-community ########################################### [ 75%]
4:MySQL-server-community ########################################### [100%]
PLEASE REMEMBER TO SET A PASSWORD FOR THE MySQL root USER !
To do so, start the server, then issue the following commands:
/usr/bin/mysqladmin -u root password 'new-password'
/usr/bin/mysqladmin -u root -h localhost.localdomain password 'new-password'

Alternatively you can run:
/usr/bin/mysql_secure_installation

which will also give you the option of removing the test
databases and anonymous user created by default. This is
strongly recommended for production servers.

See the manual for more instructions.

Please report any problems with the /usr/bin/mysqlbug script!

The latest information about MySQL is available on the web at
http://www.mysql.com
Support MySQL by buying support/licenses at http://shop.mysql.com
Starting MySQL..[ OK ]
Giving mysqld 2 seconds to start
[root@localhost mysql]#


The server RPM places data under the /var/lib/mysql directory. The RPM also creates a login account for a user named mysql (if one does not exist) to use for running the MySQL server, and creates the appropriate entries in /etc/init.d/ to start the server automatically at boot time.

If you want to install the MySQL RPM on older Linux distributions that do not support initialization scripts in /etc/init.d (directly or via a symlink), you should create a symbolic link that points to the location where your initialization scripts actually are installed. For example, if that location is /etc/rc.d/init.d, use these commands before installing the RPM to create /etc/init.d as a symbolic link that points there:

shell> cd /etc
shell> ln -s rc.d/init.d .

However, all current major Linux distributions should support the new directory layout that uses /etc/init.d, because it is required for LSB (Linux Standard Base) compliance.

If the RPM files that you install include MySQL-server, the mysqld server should be up and running after installation. You should be able to start using MySQL.

Securing the Server

[root@localhost mysql]# /usr/bin/mysql_secure_installation




NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
SERVERS IN PRODUCTION USE! PLEASE READ EACH STEP CAREFULLY!


In order to log into MySQL to secure it, we'll need the current
password for the root user. If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.

Set root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them. This is intended only for testing, and to make the installation
go a bit smoother. You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'. This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MySQL comes with a database named 'test' that anyone can
access. This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...



All done! If you've completed all of the above steps, your MySQL
installation should now be secure.

Thanks for using MySQL!


[root@localhost mysql]#

Test mysql connectivity

Type the following command to connect to MySQL server:


[root@localhost mysql]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 10
Server version: 5.0.83-community MySQL Community Edition (GPL)

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql>

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

Platform: Linux/Unix

If you come across below errors when trying to connect MySQL then there might be couple of reasons behind it.

-bash-3.2$ mysql --user=root --password
Enter password:

ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)

One of the reason could be that MySQL server isn't running. So how do you check whether or not MySQL is running? try running below shell command and see what it reports


-bash-3.2$ ps -e|grep mysqld
30878 ? 00:00:00 mysqld_safe
30939 ? 00:58:06 mysqld
-bash-3.2$


If you see at least 2 process are running/listed from the above command then it is confirmed that MySQL is running.
What if you don't see any process(as shown below)? then the MySQL services is not at all running then try to start it and watch for errors(you can check error log for same) and resolve them before making an attempt to start.
-bash-3.2$ ps -e|grep mysqld
-bash-3.2$

Starting MySQL(easiest way :-))

/etc/init.d/mysql start

If there are no errors, re-enter the process status(ps) command once again & if the service is now running, then try connecting to MySQL and If still you are receiving the error then another reason(wasn't first one) could be if the location of the Unix socket has been changed or is different from the default location.

It might be that the location of the socket file has been changed so first one has to check the MyQL config file (/etc/my.cnf) and locate the the value for the socket parameter(you may see two socket details - for client and mysqld groups).

-bash-3.2$ more /etc/my.cnf |grep socket
socket=/var/lib/mysql/mysql.sock
-bash-3.2$

It could be that the value for the --socket option is set at the command-line when MySQL is started is different than what the client mysql is expecting in that case you have to either change the socket file path in the config file or you will have to supply the socket parameter with its path for the the client

-bash-3.2$ mysql --user=root --password --socket=/var/lib/mysql/mysql.sock
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 421830
Server version: 5.0.45 Source distribution

Type 'help;' or '\h' for help. Type '\c' to clear the buffer.

mysql>

Resetting the Root Password on Unix/Linix Systems

Log on to your system as the Unix mysql user that the mysqld server runs as.

Locate the .pid file that contains the server's process ID. The exact location and name of this file depend on your distribution, host name, and configuration. Common locations are /var/lib/mysql/, /var/run/mysqld/, and /usr/local/mysql/data/. Generally, the file name has an extension of .pid and begins with either mysqld or your system's host name.

You can stop the MySQL server by sending a normal kill (not kill -9) to the mysqld process, using the path name of the .pid file in the following command:

shell> kill `cat /mysql-data-directory/host_name.pid`

Note the use of backticks rather than forward quotes with the cat command; these cause the output of cat to be substituted into the kill command.

Create a text file and place the following statements in it. Replace the password with the password that you want to use.

UPDATE mysql.user SET Password=PASSWORD('MyNewPass') WHERE User='root';
FLUSH PRIVILEGES;

The UPDATE and FLUSH statements each must be written on a single line. The UPDATE statement resets the password for all existing root accounts, and the FLUSH statement tells the server to reload the grant tables into memory.

Save the file. For this example, the file will be named /home/me/mysql-init. The file contains the password, so it should not be saved where it can be read by other users.

Start the MySQL server with the special --init-file option:

shell> mysqld_safe --init-file=/home/me/mysql-init &

The server executes the contents of the file named by the --init-file option at startup, changing each root account password.

After the server has started successfully, delete /home/me/mysql-init.

Reference: MySQL Manual

MySQL - Which engine to use?

it is good to have an idea of the advantages and disadvantages of the various storage engines. The following table provides an overview of some storage engines provided with MySQL





Reference: MySQL Manual

Choosing Which Version of MySQL to Install

The first decision to make is whether someone want to use a production (stable) release or a development release. In the MySQL development process, multiple release series co-exist, each at a different stage of maturity:

At the time of writing this:

• 
  MySQL 5.4 and 6.0 are the current development release series.
  
  
• 
  MySQL 5.1 is the current General Availability (Production) release series. New releases are issued for bugfixes only; no new features are being added that could affect stability.
  
  
• 
  MySQL 5.0 is the previous stable (production-quality) release series.
  
  
• 
  MySQL 4.1, 4.0, and 3.23 are old stable (production-quality) release series. MySQL 4.1 is now at the end of the product lifecycle. Active development and support for these versions has ended.
  
  

The MySQL naming scheme uses release names that consist of three numbers and a suffix; for example, mysql-5.0.12-beta. The numbers within the release name are interpreted as follows:

• 
  The first number (5) is the major version and describes the file format. All MySQL 5 releases have the same file format.
  
  
• 
  The second number (0) is the release level. Taken together, the major version and release level constitute the release series number.
  
  
• 
  The third number (12) is the version number within the release series. This is incremented for each new release. Usually you want the latest version for the series you have chosen.
  
  

Release names also include a suffix to indicates the stability level of the release. Releases within a series progress through a set of suffixes to indicate how the stability level improves. The possible suffixes are:

• 
  alpha indicates that the release is for preview purposes only. Known bugs should be documented in the News section . Most alpha releases implement new commands and extensions. Active development that may involve major code changes can occur in an alpha release. However, we do conduct testing before issuing a release.
  
  
• 
  beta indicates that the release is appropriate for use with new development. Within beta releases, the features and compatibility should remain consistent. However, beta releases may contain numerous and major unaddressed bugs.
  All APIs, externally visible structures, and columns for SQL statements will not change during future beta, release candidate, or production releases.
  
  
• 
  rc indicates a Release Candidate. Release candidates are believed to be stable, having passed all of MySQL's internal testing, and with all known fatal runtime bugs fixed. However, the release has not been in widespread use long enough to know for sure that all bugs have been identified. Only minor fixes are added. (A release candidate is what formerly was known as a gamma release.)
  
  
• 
  If there is no suffix, it indicates that the release is a General Availability (GA) or Production release. GA releases are stable, having successfully passed through all earlier release stages and are believed to be reliable, free of serious bugs, and suitable for use in production systems. Only critical bugfixes are applied to the release.
  
  

MySQL uses a naming scheme that is slightly different from most other products. In general, it is usually safe to use any version that has been out for a couple of weeks without being replaced by a new version within the same release series.

MySQL - Commonly used SQL commands

Most commonly used MySQL specific SQL Commands - Now I don't have to google for them every time I forget the syntax.


GRANT

The GRANT statement enables system administrators to create MySQL user accounts and to grant rights to accounts. To use GRANT, you must have the GRANT OPTION privilege, and you must have the privileges that you are granting. The REVOKE statement is related and enables administrators to remove account privileges. To determine what privileges an account has, use SHOW GRANTS.

GRANT
priv_type [(column_list)]
[, priv_type [(column_list)]] ...
ON [object_type] priv_level
TO user [IDENTIFIED BY [PASSWORD] 'password']
[, user [IDENTIFIED BY [PASSWORD] 'password']] ...
[REQUIRE {NONE | ssl_option [[AND] ssl_option] ...}]
[WITH with_option [with_option] ...]

object_type:
TABLE
| FUNCTION
| PROCEDURE

priv_level:
*
| *.*
| db_name.*
| db_name.tbl_name
| tbl_name
| db_name.routine_name

with_option:
GRANT OPTION
| MAX_QUERIES_PER_HOUR count
| MAX_UPDATES_PER_HOUR count
| MAX_CONNECTIONS_PER_HOUR count
| MAX_USER_CONNECTIONS count

ssl_option:
SSL
| X509
| CIPHER 'cipher'
| ISSUER 'issuer'
| SUBJECT 'subject'

REVOKE

SHOW VARIABLES

SHOW [GLOBAL | SESSION] VARIABLES

 [LIKE 'pattern' | WHERE expr]

SHOW VARIABLES shows the values of MySQL system variables. This information also can be obtained using mysqladmin command. The LIKE clause, if present, indicates which variable names to match. The WHERE clause can be given to select rows using more general conditions.

With the GLOBAL modifier, SHOW VARIABLES displays the values that are used for new connections to MySQL. With SESSION, it displays the values that are in effect for the current connection. If no modifier is present, the default is SESSION. LOCAL is a synonym for SESSION.

SHOW STATUS
SHOW STATUS provides server status information. The LIKE clause, if present, indicates which variable names to match

SHOW [GLOBAL | SESSION] STATUS
    [LIKE 'pattern' | WHERE expr]

With a LIKE clause, the statement displays only rows for those variables with names that match the pattern:

mysql> SHOW STATUS LIKE 'Key%';
+--------------------+----------+
| Variable_name      | Value    |
+--------------------+----------+
| Key_blocks_used    | 14955    |
| Key_read_requests  | 96854827 |
| Key_reads          | 162040   |
| Key_write_requests | 7589728  |
| Key_writes         | 3813196  |
+--------------------+----------+

SHOW SLAVE STATUS

This statement provides status information on essential parameters of the slave  threads.
If you issue this statement using  the `mysql` client, you can use a \G  statement terminator
rather than a semicolon to obtain a more readable vertical  layout:

mysql> SHOW SLAVE STATUS\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: localhost
Master_User: root
Master_Port: 3306
Connect_Retry: 3


SHOW BINARY LOGS / SHOW MASTER LOGS

Lists the binary log files on the server

mysql> SHOW BINARY LOGS;
+---------------+-----------+
| Log_name      | File_size |
+---------------+-----------+
| binlog.000015 |    724935 |
| binlog.000016 |    733481 |
+---------------+-----------+


SHOW [STORAGE] ENGINES

SHOW ENGINES displays status information about the server's storage engines.
This is particularly useful for checking whether a storage engine is
supported, or to see what the default engine is. 
SHOW TABLE TYPES is a deprecated synonym.


mysql> SHOW ENGINES\G
*************************** 1. row ***************************
Engine: MyISAM
Support: DEFAULT
Comment: Default engine as of MySQL 3.23 with great performance
*************************** 2. row ***************************
Engine: MEMORY
Support: YES
Comment: Hash based, stored in memory, useful for temporary tables
*************************** 3. row ***************************
Engine: HEAP
Support: YES
Comment: Alias for MEMORY

--

*************************** 4. row ***************************
Engine: InnoDB
Support: YES

SHOW ERRORS

SHOW ERRORS [LIMIT [offset,] row_count]
SHOW COUNT(*) ERRORS

This statement is similar to SHOW WARNINGS, except that instead of displaying errors, warnings, and notes, it displays only errors.

SHOW WARNINGS

SHOW WARNINGS [LIMIT [offset,] row_count]

SHOW COUNT(*) WARNINGS

show warnings shows the error, warning, and note messages that resulted from the last statement that generated messages in the current session. It shows nothing if the last statement used a table and generated no messages. (That is, a statement that uses a table but generates no messages clears the message list.) Statements that do not use tables and do not generate messages have no effect on the message list.

Warnings are generated for DML statements such as INSERT, UPDATE, and LOAD DATA INFILE as well as DDL statements such as CREATE TABLE and ALTER TABLE.

SHOW many forms that provide information about databases, tables, columns, or status information about the server. This section describes those following:

SHOW CHARACTER SET [like_or_where]
SHOW COLLATION [like_or_where]
SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [like_or_where]
SHOW CREATE DATABASE db_name
SHOW CREATE FUNCTION func_name
SHOW CREATE PROCEDURE proc_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [like_or_where]
SHOW ENGINE engine_name {LOGS | STATUS }
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW FUNCTION CODE func_name
SHOW FUNCTION STATUS [like_or_where]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW PROCEDURE CODE proc_name
SHOW PROCEDURE STATUS [like_or_where]
SHOW [BDB] LOGS
SHOW MUTEX STATUS
SHOW OPEN TABLES [FROM db_name] [like_or_where]
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW PROFILE [types] [FOR QUERY n] [OFFSET n] [LIMIT n]
SHOW PROFILES
SHOW [GLOBAL | SESSION] STATUS [like_or_where]
SHOW TABLE STATUS [FROM db_name] [like_or_where]
SHOW TABLES [FROM db_name] [like_or_where]
SHOW TRIGGERS [FROM db_name] [like_or_where]
SHOW [GLOBAL | SESSION] VARIABLES [like_or_where]
SHOW WARNINGS [LIMIT [offset,] row_count]

like_or_where:
LIKE 'pattern'
| WHERE expr


ALTER TABLE

ALTER [IGNORE] TABLE tbl_name

    alter_specification [, alter_specification] ...

alter_specification:
table_options
| ADD [COLUMN] col_name column_definition
[FIRST | AFTER col_name ]
| ADD [COLUMN] (col_name column_definition,...)
| ADD {INDEX|KEY} [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]] PRIMARY KEY
[index_type] (index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
UNIQUE [INDEX|KEY] [index_name]
[index_type] (index_col_name,...) [index_type]
| ADD [FULLTEXT|SPATIAL] [INDEX|KEY] [index_name]
(index_col_name,...) [index_type]
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
reference_definition
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name new_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] col_name column_definition
[FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP {INDEX|KEY} index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name [, col_name] ...
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET [=] charset_name [COLLATE [=] collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE

index_col_name:
col_name [(length)] [ASC | DESC]

index_type:
USING {BTREE | HASH | RTREE}

table_options:
table_option [[,] table_option] ...


To be continued...