Steps to take to reset slave and delete old connect info alt. break master-master seup.

On the slave server(s): 
alt.
on the Master when you want to break a master-master replication setup:

1.      Run 'stop slave' to stop replication.
2.      Run 'reset slave' to tell the slave server to forget it's position in the binary log retrieved from the master server.
3.      Add 'skip-slave-start' to my.cnf to prevent replication from starting when you restart MySQL.

Done.

Queries to use and diagnose blocking and locking threads 

... only works in MySQL version 5.5.x

This is the Query that is blocking :

--------------
SELECT r.trx_id waiting_trx_id,  r.trx_mysql_thread_id waiting_thread,
       r.trx_query waiting_query,
       b.trx_id blocking_trx_id, b.trx_mysql_thread_id blocking_thread,
       b.trx_query blocking_query
FROM       information_schema.innodb_lock_waits w
INNER JOIN information_schema.innodb_trx b  ON  b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.innodb_trx r  ON  r.trx_id = w.requesting_trx_id
--------------

Empty set (0.00 sec)

=======================================================================

Tell you how long it has waited and from which host and port

... only works in MySQL version 5.5.x

--------------
select r.trx_id as waiting_trx_id, r.trx_mysql_thread_id as waiting_thread,
 timestampdiff(second, r.trx_wait_started, current_timestamp) as wait_time,
 r.trx_query as waiting_query,
 l.lock_table as waiting_table_lock,
 b.trx_id as blocking_trx_id, b.trx_mysql_thread_id as blocking_thread,
 substring(p.host, 1, instr(p.host, ':') - 1) as blocking_host,
 substring(p.host,    instr(p.host, ':') + 1) as blocking_port,
 if(p.command = "Sleep" , p.time, 0) as idle_in_trx,
 b.trx_query as blocking_query
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS r ON b.trx_id = w.requesting_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_LOCKS AS l ON w.requested_lock_id = l.lock_id
LEFT  JOIN INFORMATION_SCHEMA.PROCESSLIST  AS p ON p.id = b.trx_mysql_thread_id
ORDER BY wait_time DESC
--------------

Empty set (0.00 sec)

========================================================================

... show how many queries are blocked on which threads...

--------------
SELECT CONCAT('thread ' , b.trx_mysql_thread_id , ' from ' , p.host) as who_blocks,
       IF(p.command = "Sleep", p.time, 0 ) as idle_in_trx,
       MAX(TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW() )) as max_wait_time,
       COUNT(*) as num_waiters
FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS AS w
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS b ON b.trx_id = w.blocking_trx_id
INNER JOIN INFORMATION_SCHEMA.INNODB_TRX  AS r ON b.trx_id = w.requesting_trx_id
LEFT  JOIN INFORMATION_SCHEMA.PROCESSLIST AS p ON p.id = b.trx_mysql_thread_id
GROUP BY who_blocks ORDER BY num_waiters DESC
--------------

Empty set (0.00 sec)

MySQL String Functions :

Name	Description
ASCII()	Return numeric value of left-most character
BIN()	Return a string representation of the argument
BIT_LENGTH()	Return length of argument in bits
CHAR_LENGTH()	Return number of characters in argument
CHAR()	Return the character for each integer passed
CHARACTER_LENGTH()	A synonym for CHAR_LENGTH()
CONCAT_WS()	Return concatenate with separator
CONCAT()	Return concatenated string
CONV()	Convert numbers between different number bases
ELT()	Return string at index number
EXPORT_SET()	Return a string such that for every bit set in the value bits, you get an on string and for every unset bit, you get an off string
FIELD()	Return the index (position) of the first argument in the subsequent arguments
FIND_IN_SET()	Return the index position of the first argument within the second argument
FORMAT()	Return a number formatted to specified number of decimal places
HEX()	Return a string representation of a hex value
INSERT()	Insert a substring at the specified position up to the specified number of characters
INSTR()	Return the index of the first occurrence of substring
LCASE()	Synonym for LOWER()
LEFT()	Return the leftmost number of characters as specified
LENGTH()	Return the length of a string in bytes
LOAD_FILE()	Load the named file
LOCATE()	Return the position of the first occurrence of substring
LOWER()	Return the argument in lowercase
LPAD()	Return the string argument, left-padded with the specified string
LTRIM()	Remove leading spaces
MAKE_SET()	Return a set of comma-separated strings that have the corresponding bit in bits set
MID()	Return a substring starting from the specified position
OCT()	Return a string representation of the octal argument
OCTET_LENGTH()	A synonym for LENGTH()
ORD()	If the leftmost character of the argument is a multi-byte character, returns the code for that character
POSITION()	A synonym for LOCATE()
QUOTE()	Escape the argument for use in an SQL statement
REGEXP	Pattern matching using regular expressions
REPEAT()	Repeat a string the specified number of times
REPLACE()	Replace occurrences of a specified string
REVERSE()	Reverse the characters in a string
RIGHT()	Return the specified rightmost number of characters
RPAD()	Append string the specified number of times
RTRIM()	Remove trailing spaces
SOUNDEX()	Return a soundex string
SOUNDS LIKE	Compare sounds
SPACE()	Return a string of the specified number of spaces
STRCMP()	Compare two strings
SUBSTRING_INDEX()	Return a substring from a string before the specified number of occurrences of the delimiter
SUBSTRING(), SUBSTR()	Return the substring as specified
TRIM()	Remove leading and trailing spaces
UCASE()	Synonym for UPPER()
UNHEX()	Convert each pair of hexadecimal digits to a character
UPPER()	Convert to uppercase

ASCII(str)

Returns the numeric value of the leftmost character of the string str. Returns 0 if str is the empty string. Returns NULL if str is NULL. ASCII() works for characters with numeric values from 0 to 255.

mysql> SELECT ASCII('2'); +---------------------------------------------------------+ | ASCII('2') | +---------------------------------------------------------+ | 50 | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT ASCII('dx'); +---------------------------------------------------------+ | ASCII('dx') | +---------------------------------------------------------+ | 100 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

BIN(N)

Returns a string representation of the binary value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,2). Returns NULL if N is NULL.

mysql> SELECT BIN(12); +---------------------------------------------------------+ | BIN(12) | +---------------------------------------------------------+ | 1100 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

BIT_LENGTH(str)

Returns the length of the string str in bits.

mysql> SELECT BIT_LENGTH('text'); +---------------------------------------------------------+ | BIT_LENGTH('text') | +---------------------------------------------------------+ | 32 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

CHAR(N,... [USING charset_name])

CHAR() interprets each argument N as an integer and returns a string consisting of the characters given by the code values of those integers. NULL values are skipped.

mysql> SELECT CHAR(77,121,83,81,'76'); +---------------------------------------------------------+ | CHAR(77,121,83,81,'76') | +---------------------------------------------------------+ | MySQL | +---------------------------------------------------------+ 1 row in set (0.00 sec)

CHAR_LENGTH(str)

Returns the length of the string str, measured in characters. A multi-byte character counts as a single character. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

mysql> SELECT CHAR_LENGTH("text"); +---------------------------------------------------------+ | CHAR_LENGTH("text") | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

CHARACTER_LENGTH(str)

CHARACTER_LENGTH() is a synonym for CHAR_LENGTH().

CONCAT(str1,str2,...)

Returns the string that results from concatenating the arguments. May have one or more arguments. If all arguments are non-binary strings, the result is a non-binary string. If the arguments include any binary strings, the result is a binary string. A numeric argument is converted to its equivalent binary string form; if you want to avoid that, you can use an explicit type cast, as in this example:

mysql> SELECT CONCAT('My', 'S', 'QL'); +---------------------------------------------------------+ | CONCAT('My', 'S', 'QL') | +---------------------------------------------------------+ | MySQL | +---------------------------------------------------------+ 1 row in set (0.00 sec)

CONCAT_WS(separator,str1,str2,...)

CONCAT_WS() stands for Concatenate With Separator and is a special form of CONCAT(). The first argument is the separator for the rest of the arguments. The separator is added between the strings to be concatenated. The separator can be a string, as can the rest of the arguments. If the separator is NULL, the result is NULL.

mysql> SELECT CONCAT_WS(',','First name','Last Name' ); +---------------------------------------------------------+ | CONCAT_WS(',','First name','Last Name' ) | +---------------------------------------------------------+ | First name, Last Name | +---------------------------------------------------------+ 1 row in set (0.00 sec)

CONV(N,from_base,to_base)

Converts numbers between different number bases. Returns a string representation of the number N, converted from base from_base to base to_base. Returns NULL if any argument is NULL. The argument N is interpreted as an integer, but may be specified as an integer or a string. The minimum base is 2 and the maximum base is 36. If to_base is a negative number, N is regarded as a signed number. Otherwise, N is treated as unsigned. CONV() works with 64-bit precision.

mysql> SELECT CONV('a',16,2); +---------------------------------------------------------+ | CONV('a',16,2) | +---------------------------------------------------------+ | 1010 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

ELT(N,str1,str2,str3,...)

Returns str1 if N = 1, str2 if N = 2, and so on. Returns NULL if N is less than 1 or greater than the number of arguments. ELT() is the complement of FIELD().

mysql> SELECT ELT(1, 'ej', 'Heja', 'hej', 'foo'); +---------------------------------------------------------+ | ELT(1, 'ej', 'Heja', 'hej', 'foo') | +---------------------------------------------------------+ | ej | +---------------------------------------------------------+ 1 row in set (0.00 sec)

EXPORT_SET(bits,on,off[,separator[,number_of_bits]])

Returns a string such that for every bit set in the value bits, you get an on string and for every bit not set in the value, you get an off string. Bits in bits are examined from right to left (from low-order to high-order bits). Strings are added to the result from left to right, separated by the separator string (the default being the comma character .,.). The number of bits examined is given by number_of_bits (defaults to 64).

mysql> SELECT EXPORT_SET(5,'Y','N',',',4); +---------------------------------------------------------+ | EXPORT_SET(5,'Y','N',',',4) | +---------------------------------------------------------+ | Y,N,Y,N | +---------------------------------------------------------+ 1 row in set (0.00 sec)

FIELD(str,str1,str2,str3,...)

Returns the index (position starting with 1) of str in the str1, str2, str3, ... list. Returns 0 if str is not found.

mysql> SELECT FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo'); +---------------------------------------------------------+ | FIELD('ej', 'Hej', 'ej', 'Heja', 'hej', 'foo') | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

FIND_IN_SET(str,strlist)

Returns a value in the range of 1 to N if the string str is in the string list strlist consisting of N substrings.

mysql> SELECT FIND_IN_SET('b','a,b,c,d'); +---------------------------------------------------------+ | SELECT FIND_IN_SET('b','a,b,c,d') | +---------------------------------------------------------+ | 2 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

FORMAT(X,D)

Formats the number X to a format like '#,###,###.##', rounded to D decimal places, and returns the result as a string. If D is 0, the result has no decimal point or fractional part.

mysql> SELECT FORMAT(12332.123456, 4); +---------------------------------------------------------+ | FORMAT(12332.123456, 4) | +---------------------------------------------------------+ | 12,332.1235 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

HEX(N_or_S)

If N_or_S is a number, returns a string representation of the hexadecimal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,16).
If N_or_S is a string, returns a hexadecimal string representation of N_or_S where each character in N_or_S is converted to two hexadecimal digits.

mysql> SELECT HEX(255); +---------------------------------------------------------+ | HEX(255) | +---------------------------------------------------------+ | FF | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT 0x616263; +---------------------------------------------------------+ | 0x616263 | +---------------------------------------------------------+ | abc | +---------------------------------------------------------+ 1 row in set (0.00 sec)

INSERT(str,pos,len,newstr)

Returns the string str, with the substring beginning at position pos and len characters long replaced by the string newstr. Returns the original string if pos is not within the length of the string. Replaces the rest of the string from position pos if len is not within the length of the rest of the string. Returns NULL if any argument is NULL.

mysql> SELECT INSERT('Quadratic', 3, 4, 'What'); +---------------------------------------------------------+ | INSERT('Quadratic', 3, 4, 'What') | +---------------------------------------------------------+ | QuWhattic | +---------------------------------------------------------+ 1 row in set (0.00 sec)

INSTR(str,substr)

Returns the position of the first occurrence of substring substr in string str. This is the same as the two-argument form of LOCATE(), except that the order of the arguments is reversed.

mysql> SELECT INSTR('foobarbar', 'bar'); +---------------------------------------------------------+ | INSTR('foobarbar', 'bar') | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LCASE(str)

LCASE() is a synonym for LOWER().
LEFT(str,len)
Returns the leftmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT LEFT('foobarbar', 5); +---------------------------------------------------------+ | LEFT('foobarbar', 5) | +---------------------------------------------------------+ | fooba | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LENGTH(str)

Returns the length of the string str, measured in bytes. A multi-byte character counts as multiple bytes. This means that for a string containing five two-byte characters, LENGTH() returns 10, whereas CHAR_LENGTH() returns 5.

mysql> SELECT LENGTH('text'); +---------------------------------------------------------+ | LENGTH('text') | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LOAD_FILE(file_name)

Reads the file and returns the file contents as a string. To use this function, the file must be located on the server host, you must specify the full pathname to the file, and you must have the FILE privilege. The file must be readable by all and its size less than max_allowed_packet bytes.
If the file does not exist or cannot be read because one of the preceding conditions is not satisfied, the function returns NULL.
As of MySQL 5.0.19, the character_set_filesystem system variable controls interpretation of filenames that are given as literal strings.

mysql> UPDATE table_test -> SET blob_col=LOAD_FILE('/tmp/picture') -> WHERE id=1; ...........................................................

LOCATE(substr,str), LOCATE(substr,str,pos)

The first syntax returns the position of the first occurrence of substring substr in string str. The second syntax returns the position of the first occurrence of substring substr in string str, starting at position pos. Returns 0 if substr is not in str.

mysql> SELECT LOCATE('bar', 'foobarbar'); +---------------------------------------------------------+ | LOCATE('bar', 'foobarbar') | +---------------------------------------------------------+ | 4 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LOWER(str)

Returns the string str with all characters changed to lowercase according to the current character set mapping.

mysql> SELECT LOWER('QUADRATICALLY'); +---------------------------------------------------------+ | LOWER('QUADRATICALLY') | +---------------------------------------------------------+ | quadratically | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LPAD(str,len,padstr)

Returns the string str, left-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT LPAD('hi',4,'??'); +---------------------------------------------------------+ | LPAD('hi',4,'??') | +---------------------------------------------------------+ | ??hi | +---------------------------------------------------------+ 1 row in set (0.00 sec)

LTRIM(str)

Returns the string str with leading space characters removed.

mysql> SELECT LTRIM(' barbar'); +---------------------------------------------------------+ | LTRIM(' barbar') | +---------------------------------------------------------+ | barbar | +---------------------------------------------------------+ 1 row in set (0.00 sec)

MAKE_SET(bits,str1,str2,...)

Returns a set value (a string containing substrings separated by .,. characters) consisting of the strings that have the corresponding bit in bits set. str1 corresponds to bit 0, str2 to bit 1, and so on. NULL values in str1, str2, ... are not appended to the result.

mysql> SELECT MAKE_SET(1,'a','b','c'); +---------------------------------------------------------+ | MAKE_SET(1,'a','b','c') | +---------------------------------------------------------+ | a | +---------------------------------------------------------+ 1 row in set (0.00 sec)

MID(str,pos,len)

MID(str,pos,len) is a synonym for SUBSTRING(str,pos,len).

OCT(N)

Returns a string representation of the octal value of N, where N is a longlong (BIGINT) number. This is equivalent to CONV(N,10,8). Returns NULL if N is NULL.

mysql> SELECT OCT(12); +---------------------------------------------------------+ | OCT(12) | +---------------------------------------------------------+ | 14 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

OCTET_LENGTH(str)

OCTET_LENGTH() is a synonym for LENGTH().

ORD(str)

If the leftmost character of the string str is a multi-byte character, returns the code for that character, calculated from the numeric values of its constituent bytes using this formula:

(1st byte code) + (2nd byte code � 256) + (3rd byte code � 2562) ...

If the leftmost character is not a multi-byte character, ORD() returns the same value as the ASCII() function.

mysql> SELECT ORD('2'); +---------------------------------------------------------+ | ORD('2') | +---------------------------------------------------------+ | 50 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

POSITION(substr IN str)

POSITION(substr IN str) is a synonym for LOCATE(substr,str).

QUOTE(str)

Quotes a string to produce a result that can be used as a properly escaped data value in an SQL statement. The string is returned enclosed by single quotes and with each instance of single quote (.'.), backslash (.\.), ASCII NUL, and Control-Z preceded by a backslash. If the argument is NULL, the return value is the word .NULL. without enclosing single quotes.

mysql> SELECT QUOTE('Don\'t!'); +---------------------------------------------------------+ | QUOTE('Don\'t!') | +---------------------------------------------------------+ | 'Don\'t!' | +---------------------------------------------------------+ 1 row in set (0.00 sec)

NOTE: Please check if your installation has any bug with this function then don't use this function.

expr REGEXP pattern

This function performs a pattern match of expr against pattern. Returns 1 if expr matches pat; otherwise it returns 0. If either expr or pat is NULL, the result is NULL. REGEXP is not case sensitive, except when used with binary strings.

mysql> SELECT 'ABCDEF' REGEXP 'A%C%%'; +---------------------------------------------------------+ | 'ABCDEF' REGEXP 'A%C%%' | +---------------------------------------------------------+ | 0 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

Another example is:

mysql> SELECT 'ABCDE' REGEXP '.*'; +---------------------------------------------------------+ | 'ABCDE' REGEXP '.*' | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

Let's see one more example:

mysql> SELECT 'new*\n*line' REGEXP 'new\\*.\\*line'; +---------------------------------------------------------+ | 'new*\n*line' REGEXP 'new\\*.\\*line' | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

REPEAT(str,count)

Returns a string consisting of the string str repeated count times. If count is less than 1, returns an empty string. Returns NULL if str or count are NULL.

mysql> SELECT REPEAT('MySQL', 3); +---------------------------------------------------------+ | REPEAT('MySQL', 3) | +---------------------------------------------------------+ | MySQLMySQLMySQL | +---------------------------------------------------------+ 1 row in set (0.00 sec)

REPLACE(str,from_str,to_str)

Returns the string str with all occurrences of the string from_str replaced by the string to_str. REPLACE() performs a case-sensitive match when searching for from_str.

mysql> SELECT REPLACE('www.mysql.com', 'w', 'Ww'); +---------------------------------------------------------+ | REPLACE('www.mysql.com', 'w', 'Ww') | +---------------------------------------------------------+ | WwWwWw.mysql.com | +---------------------------------------------------------+ 1 row in set (0.00 sec)

REVERSE(str)

Returns the string str with the order of the characters reversed.

mysql> SELECT REVERSE('abcd'); +---------------------------------------------------------+ | REVERSE('abcd') | +---------------------------------------------------------+ | dcba | +---------------------------------------------------------+ 1 row in set (0.00 sec)

RIGHT(str,len)

Returns the rightmost len characters from the string str, or NULL if any argument is NULL.

mysql> SELECT RIGHT('foobarbar', 4); +---------------------------------------------------------+ | RIGHT('foobarbar', 4) | +---------------------------------------------------------+ | rbar | +---------------------------------------------------------+ 1 row in set (0.00 sec)

RPAD(str,len,padstr)

Returns the string str, right-padded with the string padstr to a length of len characters. If str is longer than len, the return value is shortened to len characters.

mysql> SELECT RPAD('hi',5,'?'); +---------------------------------------------------------+ | RPAD('hi',5,'?') | +---------------------------------------------------------+ | hi??? | +---------------------------------------------------------+ 1 row in set (0.00 sec)

RTRIM(str)

Returns the string str with trailing space characters removed.

mysql> SELECT RTRIM('barbar '); +---------------------------------------------------------+ | RTRIM('barbar ') | +---------------------------------------------------------+ | barbar | +---------------------------------------------------------+ 1 row in set (0.00 sec)

SOUNDEX(str)

Returns a soundex string from str. Two strings that sound almost the same should have identical soundex strings. A standard soundex string is four characters long, but the SOUNDEX() function returns an arbitrarily long string. You can use SUBSTRING() on the result to get a standard soundex string. All non-alphabetic characters in str are ignored. All international alphabetic characters outside the A-Z range are treated as vowels.

mysql> SELECT SOUNDEX('Hello'); +---------------------------------------------------------+ | SOUNDEX('Hello') | +---------------------------------------------------------+ | H400 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

expr1 SOUNDS LIKE expr2

This is the same as SOUNDEX(expr1) = SOUNDEX(expr2).

SPACE(N)

Returns a string consisting of N space characters.

mysql> SELECT SPACE(6); +---------------------------------------------------------+ | SELECT SPACE(6) | +---------------------------------------------------------+ | ' ' | +---------------------------------------------------------+ 1 row in set (0.00 sec)

STRCMP(str1, str2)

Compares two strings and returns 0 if both strings are equal, it returns -1 if the first argument is smaller than the second according to the current sort order otherwise it returns 1.

mysql> SELECT STRCMP('MOHD', 'MOHD'); +---------------------------------------------------------+ | STRCMP('MOHD', 'MOHD') | +---------------------------------------------------------+ | 0 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

Another example is:

mysql> SELECT STRCMP('AMOHD', 'MOHD'); +---------------------------------------------------------+ | STRCMP('AMOHD', 'MOHD') | +---------------------------------------------------------+ | -1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

Let's see one more example:

mysql> SELECT STRCMP('MOHD', 'AMOHD'); +---------------------------------------------------------+ | STRCMP('MOHD', 'AMOHD') | +---------------------------------------------------------+ | 1 | +---------------------------------------------------------+ 1 row in set (0.00 sec)

SUBSTRING(str,pos)

SUBSTRING(str FROM pos)

SUBSTRING(str,pos,len)

SUBSTRING(str FROM pos FOR len)

The forms without a len argument return a substring from string str starting at position pos. The forms with a len argument return a substring len characters long from string str, starting at position pos. The forms that use FROM are standard SQL syntax. It is also possible to use a negative value for pos. In this case, the beginning of the substring is pos characters from the end of the string, rather than the beginning. A negative value may be used for pos in any of the forms of this function.

mysql> SELECT SUBSTRING('Quadratically',5); +---------------------------------------------------------+ | SSUBSTRING('Quadratically',5) | +---------------------------------------------------------+ | ratically | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING('foobarbar' FROM 4); +---------------------------------------------------------+ | SUBSTRING('foobarbar' FROM 4) | +---------------------------------------------------------+ | barbar | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT SUBSTRING('Quadratically',5,6); +---------------------------------------------------------+ | SUBSTRING('Quadratically',5,6) | +---------------------------------------------------------+ | ratica | +---------------------------------------------------------+ 1 row in set (0.00 sec)

SUBSTRING_INDEX(str,delim,count)

Returns the substring from string str before count occurrences of the delimiter delim. If count is positive, everything to the left of the final delimiter (counting from the left) is returned. If count is negative, everything to the right of the final delimiter (counting from the right) is returned. SUBSTRING_INDEX() performs a case-sensitive match when searching for delim.

mysql> SELECT SUBSTRING_INDEX('www.mysql.com', '.', 2); +---------------------------------------------------------+ | SUBSTRING_INDEX('www.mysql.com', '.', 2) | +---------------------------------------------------------+ | www.mysql | +---------------------------------------------------------+ 1 row in set (0.00 sec)

TRIM([{BOTH | LEADING | TRAILING} [remstr] FROM] str)

TRIM([remstr FROM] str)

Returns the string str with all remstr prefixes or suffixes removed. If none of the specifiers BOTH, LEADING, or TRAILING is given, BOTH is assumed. remstr is optional and, if not specified, spaces are removed.

mysql> SELECT TRIM(' bar '); +---------------------------------------------------------+ | TRIM(' bar ') | +---------------------------------------------------------+ | bar | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TRIM(LEADING 'x' FROM 'xxxbarxxx'); +---------------------------------------------------------+ | TRIM(LEADING 'x' FROM 'xxxbarxxx') | +---------------------------------------------------------+ | barxxx | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TRIM(BOTH 'x' FROM 'xxxbarxxx'); +---------------------------------------------------------+ | TRIM(BOTH 'x' FROM 'xxxbarxxx') | +---------------------------------------------------------+ | bar | +---------------------------------------------------------+ 1 row in set (0.00 sec) mysql> SELECT TRIM(TRAILING 'xyz' FROM 'barxxyz'); +---------------------------------------------------------+ | TRIM(TRAILING 'xyz' FROM 'barxxyz') | +---------------------------------------------------------+ | barx | +---------------------------------------------------------+ 1 row in set (0.00 sec)

UCASE(str)

UCASE() is a synonym for UPPER().

UNHEX(str)

Performs the inverse operation of HEX(str). That is, it interprets each pair of hexadecimal digits in the argument as a number and converts it to the character represented by the number. The resulting characters are returned as a binary string.

mysql> SELECT UNHEX('4D7953514C'); +---------------------------------------------------------+ | UNHEX('4D7953514C') | +---------------------------------------------------------+ | MySQL | +---------------------------------------------------------+ 1 row in set (0.00 sec)

The characters in the argument string must be legal hexadecimal digits: '0' .. '9', 'A' .. 'F', 'a' .. 'f'. If UNHEX() encounters any non-hexadecimal digits in the argument, it returns NULL.

UPPER(str)

Returns the string str with all characters changed to uppercase according to the current character set mapping.

mysql> SELECT UPPER('Allah-hus-samad');
+---------------------------------------------------------+
| UPPER('Allah-hus-samad')                                |
+---------------------------------------------------------+
| ALLAH-HUS-SAMAD                                         |
+---------------------------------------------------------+
1 row in set (0.00 sec)

MySQL master-master replication and autoincrement indexes setup /etc/my.cnf

MySQL master-master replication and autoincrement indexes

If you are using master-slave replication, than most likely you will design your application the way to write to master and read from slave or several slaves. But when you are using master-master replication you are going to read and write to any of master servers. So, in this case the problem with autoincremental indexes will raise. When both servers will have to add a record (different one each server simultaneously) to the same table. Each one will assign them the same index and will try to replicate to the salve, this will create a collision. Simple trick will allow to avoid such collisions on MySQL server.
On the Master 1/Slave 2 add to /etc/my.cnf:

auto_increment_increment= 2

auto_increment_offset   = 1

On the Master 2/Slave 1 add to /etc/my.cnf:

auto_increment_increment= 2

auto_increment_offset   = 2

MySQL Replication Error 1236

MySQL error 1236: Client requested master to start replication from impossible position

Symptoms:
[1236] Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position'

110902 16:47:08 [ERROR] Error reading packet from server: Client requested master to start replication from impossible position ( server_errno=1236)
110902 16:47:08 [ERROR] Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Client requested master to start replication from impossible position', Error_code: 1236
110902 16:47:08 [Note] Slave I/O thread exiting, read up to log 'mysql-bin.000033', position 4621679

on the master
root@dimko:/var/lib/mysql# ls -la mysql-bin.000033
-rw-rw---- 1 mysql mysql 4620018 2011-09-01 13:45 mysql-bin.000033

4620018 is less than 4621679, therefore it's an invalid position.


Causes:
Master server has crashed and the binlog cache has not been flushed to disk. Slave has recieved a new position, did not recieve data, and data gets lost in a crash (however it might have been written to table, but not in binlog).

Solution:
Use this CHANGE MASTER statement on the slave.
CHANGE MASTER TO MASTER_LOG_FILE=[NEXT FILE], MASTER_LOG_POS=4;
SLAVE START;

in my case that would be

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000034', MASTER_LOG_POS=4;
SLAVE START;

I don't know why the master log position needs to be 4 for the new file.

What happens:
When the master server restarts it logs binary changes to a new binlog file, so that we minimize data loss by skipping to the next file (everything from the previous file was written already).

Prevention:
Add this line to my.cnf:

sync_binlog = 1

 With this setting the master server flushes cache in the binlog after every write, so that in case of a crash you can lose one statement at most.

MySQL Replication error 1594: Relay log read failure

MySQL slaves and the “corrupted” relay log/binlog problem

When trying to pull up to date a new slave, maybe using a binlog server (a MySQL server with no true tablespace, just acting as master for slaves, feeding them with replication data), you may encounter an error like this in your mysql.err file, after noticing the replication has just broken

091028 11:32:49 [ERROR] Slave SQL: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave. Error_code: 1594
091028 11:32:49 [Warning] Slave: Field 'owner' doesn't have a default value Error_code: 1364
091028 11:32:49 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with "SLAVE START". We stopped at log 'mysqld-bin.000780' position 786513508


Well, everything on the web points to a “OMG, my file is corrupt!! My disk is freaking out!”. Probably, instead, it’s just simpler: you have forgotten a FLUSH LOGS; before backing up your binary logs from the original master. So, when you are recreating the binlog server from the backup, the final slave arrives to the last statement wich obviously is not complete/correct and booom, it stops.

The solution is to copy the correct and complete binlog file (in my case mysqld-bin.000780) again in the binlog server, then issue a

STOP SLAVE;
CHANGE MASTER TO master_log_file="mysqld-bin.000780", master_log_pos=786513508;
SLAVE START;


and… problem solved!
(remember to adjust file name and position to suite your needs)
Lesson learned (once again): always test your backups and do not hesitate to flush with MySQL :)

MySQL Data export/import probelm with SQL SECURITY DEFINER

MySQL Data export/import probelm with SQL SECURITY DEFINER

Importing a MySQL database dump from a client into my system I got this error:

$ mysql -u root -p  example_db < example_db_dump.sql 
Enter password: 
ERROR 1449 (HY000) at line 5172: The user specified as a definer ('smith'@'localhost') does not exist

Running 'grep' for that user turned up a bunch of lines like this:

/*!50013 DEFINER=`smith`@`localhost` SQL SECURITY DEFINER */

These are created on the 'donor' MySQL system when creating one or more views of the data. That user does not exist on my system and so MySQL complains.

I don't care about those views so the easiest way to deal with this issue is to remove these '50013' lines. You can do that with 'sed':

$ sed '/\*\!50013 DEFINER/d' example_db_dump.sql > example_db_dump_clean.sql

You need to drop the new database as the import process partially worked, re-create it and then you can reimport:

$ mysqladmin -u root -p drop example_db
$ mysqladmin -u root -p create example_db
$ mysql -u root -p example_db < example_db_dump_clean.sql