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>