My (SQL) WorkLog: October 2009

Friday, October 30, 2009

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.

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."

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


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);

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

    SET inti=inti+1;


select uExtractNumberFromString('12;e1hhsak123s12');

Tuesday, October 06, 2009

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