If you need to manipulate the data, you cannot use SELECT *. If you are seeing [partnumber] coming from the table, then GIGO. Note: MySQL does not use brackets. If the column is named `partnumber then either of these will work, but brackets won't: partnumber `partnumber` Some other db engines use brackets. If the table contains 1234567, but you want to see p-1234567: SELECT CONCAT('p-', partnumber) AS partnumber, ... If the table contains p-1234567 and you want to display 1234567, here is a way assuming the first 2 characters need to be stripped: SELECT MID(partnumber, 3) AS partnumber, ... If neither of those answer your question, please rephrase it.

Well, I can at least save you some time wondering about this. The mysqlcheck utility only does one thing at a time. sqlbot@dev:~$ mysqlcheck --check --optimize --all-databases --verbose Error: mysqlcheck doesn't support multiple contradicting commands. So, if you want to do both, you'll need 2 runs. While --optimize doesn't appear to first go through the identical motions to "check" the tables, it does rebuild them, which would imply that it has to be able to read them successfully. I've never seen CHECK TABLE find a new problem with an InnoDB table, but if it does, InnoDB will shut down the MySQL Server, according to the docs... and I would think it likely that "shut down" means "force crash" to keep the corruption contained. On the other hand, if you have tables that are corrupt, OPTIMIZE TABLE (in InnoDB) would likely "help you find" that corruption in much the same way. Arguably, checking the tables before optimizing seems redundant, though I would welcome correction on this point; however, it also seems potentially wise in the face of such a large amount of data that you're about to rebuild, to invest the time running through the less obtrusive "check" process before launching into the "optimize" process. Under the hood, all mysqlcheck really does is connect to the server as a client and execute the corresponding SQL statements.

If you have shell access to the server on which mysql is running, you can use tcpdump + pt-query-digest. First, do a tcpdump with the appropriate switches necessary for pt-query-digest: tcpdump -i [your interface] port [3306,etc] -s 65535 -x -nn -q -tttt > tcpdump.out Then, after you are done with your packet capture, do this: pt-query-digest tcpdump.out --type=tcpdump --group-by=fingerprint --print --no-report That will give you the raw digest of all queries and the hosts from which they originated. More information can be found at: http://www.percona.com/doc/percona-toolkit/2.1/pt-query-digest.html

The pt-mysql-summary tool from Percona toolkit gives you a very nice overview of your running server. It will tell you stuff like what the major config params are set to, what technologies ara available in your server, take a 10 second status sample, analyze your schema etc. Also worth to run pt-duplicate-key-checker at least once, to detect redundant keys. I'm authoring the common_schema tool for MySQL, which also offers some diagnostics: the redundant_keys view is parallel to pt-duplicate-key-checker; the data_size_per_schema view will tell you, well, the datasize per schema; the auto_increment_columns view will tell you about "free space" in your AUTO_INCREMENT columns; and more. Watching the processes as they go, using innotop or the old-timer mytop is also worthwhile: get to know the processes that run most, the longest etc.

There is no immediate control data to tell you that, but there are a few mechanisms you can setup. MECHANISM #1 If you have binary logging enabled, simply do a grep -i "analyze table" against all the binary logs using the output from mysqlbinlog. MECHANISM #2 If you have the general log enabled, simply do a grep -i "analyze table" against the general log file and locate the timestamp just about the command. MECHANISM #3 You should schedule a cronjob that runs ANALYZE TABLE against all tables that have high-write, high-update, high-delete volume. That way, there is no guess work. MECHANISM #4 Try setting innodb_stats_on_metadata to have a measure of predictability as to when an InnoDB table needs ANALYZE TABLE. (See my Mar 26, 2012 post : When are InnoDB table index statistics updated? ) CAVEAT In the past, I have often stated that running ANALYZE TABLE table against InnoDB is useless. Jun 21, 2011 : From where does the MySQL Query Optimizer read index statistics? Aug 04, 2011 : Optimizing InnoDB default settings Oct 16, 2011 : Suddenly have to rebuild indexes to prevent site from going down Hopefully, MECHANISM #4 is probably what you need.

You have to take into consideration the Full-Text Search Functions Full-text searching is performed using MATCH() ... AGAINST syntax. MATCH() takes a comma-separated list that names the columns to be searched. AGAINST takes a string to search for, and an optional modifier that indicates what type of search to perform. The search string must be a string value that is constant during query evaluation. This rules out, for example, a table column because that can differ for each row. The initial setup and configuration is very simple

There is no substitute of tuning MySQL. Left unconfigured, there are cases when even MySQL 4.1 outperforms MySQL 5.x on the same level playing field. Here are my past posts on the subject Oct 05, 2011 : Query runs a long time in some newer MySQL versions Nov 24, 2011 : Why mysql 5.5 slower than 5.1 (linux,using mysqlslap) Feb 08, 2012 : MySQL 5.5 perfomance (StackOverflow) Feb 22, 2012 : MySQL 5.1 vs MySQL 5.5 (5.1 twice as fast) (ServerFault) Jul 16, 2012 : decreased performance of stored procedure when migrated from mysql server 5.0 to 5.5 Bottom Line You should always use the most stable release of MySQL because there are just certain bug and features that are fixed that do not carry over to the next major release. Note that MySQL 5.0 is up to 5.0.96, but the bugs that MySQL 5.5 Addressed still reside in 5.0.96 MySQL 5.1 is up to 5.1.67, but the bugs that MySQL 5.5 Addressed still reside in 5.1.67 There are many exciting things that MySQL 5.5 features tuning for the following: Multicore Processing May 26, 2011 : About single threaded versus multithreaded databases performance Sep 12, 2011 : Possible to make MySQL use more than one core? Sep 20, 2011 : Multi cores and MySQL Performance Semisynchronous Replication Aug 05, 2011 : Is MySQL Replication Affected by a High-Latency Interconnect? Oct 29, 2012 : Installing and enabling both master and slave semisynchronous plugins on same server Buffer Pool Instances : Feb 12, 2011 : How do you tune MySQL for a heavy InnoDB workload?

MySQL 5.6 introduces Global Transaction Identifiers to replication, and complements these with new failover and switchover utilities which can automate fault recovery You can learn more about replication in MySQL 5.6 here: http://dev.mysql.com/tech-resources/articles/mysql-5.6-replication.html You can also watch a tutorial demonstrating how these utilities work to provide self-healing clusters: http://dev.mysql.com/tech-resources/articles/mysql-replication-utilities.html

The max_allowed_packet variable limits the size of a single result set. In the [mysqld] section, any normal connection can only get 1M worth of data in a single query. In mysqldump you typically produce "extended INSERT" queries, where you list multiple rows within the same INSERT command. It's better, then, to have this variable set high. The above is a brief explanation. Now, as for your question "What is the point?" -- there is no clear answer. The my-XXXXX.cnf set of files are long since outdated, and have their issues, including recommendations which are commonly not agreed upon (like the amount of memory you should allocate to the innodb_buffer_pool_size). So take it as a general starting point. In particular, these settings relate to systems with very low memory available (like 128M RAM), so the numbers don't really apply to today's hardware. One could argue that you won't necessarily import the dump into same server, so possible to another server with higher setting for max_allowed_packet. As a side note, I usually set max_allowed_packet to very high values, like 32M or 64M. As opposed to other variables like sort_buffer_size, with max_allowed_packet you only pay for what you need. So a connection will not allocate the full 64M if it doesn't have to. A 1M limit means you could have some row with some TEXT or BLOB, possibly larger than 1M, and in such case, you would actually be unable to read that row.

Your query has to use the table and not the index alone because you have two columns (is_live and sub_id) in the WHERE clause that are not part of this (created) index. The sub_id column is also included in the GROUP BY and the SELECT list so how would it be possible for the table not to be used? I would try an index that "covers" the query, like the (is_live, created, subid) and writing the query like this: SELECT subid FROM leads WHERE is_live = 1 AND created >= '2012-12-13 00:00:00' AND created or the index (is_live, subid, created) and the query written like: SELECT subid FROM ( SELECT subid FROM leads WHERE is_live = 1 GROUP BY is_live, subid ) AS d WHERE subid IS NOT NULL AND EXISTS ( SELECT * FROM leads l WHERE l.is_live = 1 AND l.subid = d.subid AND l.created >= '2012-12-13 00:00:00' AND l.created

I did as described here and it solved my problem. So basically I added the following to [mysqld] section in my.cnf files: Master-1: server-id = 1 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 1 Master-2: server-id = 2 replicate-same-server-id = 0 auto-increment-increment = 2 auto-increment-offset = 2

If the keys repeat frequently, create a second table CREATE TABLE Key_tbl (key_id INT, key VARCHAR(<>)) and reference the first table with this, while inserting just look up the key and insert the key_id in SearchKeyword

Why not add two indexes: a) A two column index for (state, city) - handles queries for state only, then state and city b) An index on city - queries on cities only To tune for performance further you may be able to add lookup tables for state and city then use numeric keys to speed up query performance.

The no-beep option causes the mysql command line client not to beep when errors occur. If that's an option you want, it should be in the [mysql] section of your my.cnf. This error suggests that it's in the [client] section of your my.cnf, which causes mysql_upgrade to throw the error since the option isn't recognized. Check your my.cnf and either remove the line or place it in the [mysql] section instead of [client] and you should be good. The [client] option group is read by all client programs (but not by mysqld). This enables you to specify options that apply to all clients. For example, [client] is the perfect group to use to specify the password that you use to connect to the server. (But make sure that the option file is readable and writable only by yourself, so that other people cannot find out your password.) Be sure not to put an option in the [client] group unless it is recognized by all client programs that you use. Programs that do not understand the option quit after displaying an error message if you try to run them. -- http://dev.mysql.com/doc/refman/5.5/en/option-files.html

Here is the query you need: set @groupnum = 0; set @groupon = 0; set @inc = 1; select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from (select @inc := IF(@groupon=0,1,0) inc, @groupnum := @groupnum + @inc grpn, @s_tag := IF(diary='start',1,0) stag, @e_tag := IF(diary='end', 1,0) etag, @groupon := IF(diary='start',1,@groupon) g1, @groupon := IF(diary='end', 0,@groupon) g2, (@ordernum:=@e_tag*1000000+@s_tag) ord, id,diary from diary ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn; First, here is your sample data mysql> use all_db Database changed mysql> drop table if exists diary; Query OK, 0 rows affected (0.03 sec) mysql> create table diary -> ( -> id int not null auto_increment, -> diary varchar(25), -> primary key (id) -> ); Query OK, 0 rows affected (0.11 sec) mysql> insert into diary (diary) values ('breakfast'),('walk'),('start'), -> ('office works'),('office projects'),('end'),('taxi'),('start'), -> ('preparing for meeting'),('doing the meeting'),('end'),('night'); Query OK, 12 rows affected (0.06 sec) Records: 12 Duplicates: 0 Warnings: 0 mysql> select * from diary; +----+-----------------------+ | id | diary | +----+-----------------------+ | 1 | breakfast | | 2 | walk | | 3 | start | | 4 | office works | | 5 | office projects | | 6 | end | | 7 | taxi | | 8 | start | | 9 | preparing for meeting | | 10 | doing the meeting | | 11 | end | | 12 | night | +----+-----------------------+ 12 rows in set (0.00 sec) mysql> Using iterative variable manipulation set @groupnum = 0; set @groupon = 0; set @inc = 1; select * from (select @inc := IF(@groupon=0,1,0) inc, @groupnum := @groupnum + @inc grpn, @s_tag := IF(diary='start',1,0) stag, @e_tag := IF(diary='end', 1,0) etag, @groupon := IF(diary='start',1,@groupon) g1, @groupon := IF(diary='end', 0,@groupon) g2, (@ordernum:=@e_tag*1000000+@s_tag) ord, id,diary from diary ) A; Here is what it generates: mysql> set @groupnum = 0; Query OK, 0 rows affected (0.00 sec) mysql> set @groupon = 0; Query OK, 0 rows affected (0.00 sec) mysql> set @inc = 1; Query OK, 0 rows affected (0.00 sec) mysql> select * from -> (select -> @inc := IF(@groupon=0,1,0) inc, -> @groupnum := @groupnum + @inc grpn, -> @s_tag := IF(diary='start',1,0) stag, -> @e_tag := IF(diary='end', 1,0) etag, -> @groupon := IF(diary='start',1,@groupon) g1, -> @groupon := IF(diary='end', 0,@groupon) g2, -> (@ordernum:=@e_tag*1000000+@s_tag) ord, -> id,diary from diary -> ) A; +-----+------+------+------+------+------+---------+----+-----------------------+ | inc | grpn | stag | etag | g1 | g2 | ord | id | diary | +-----+------+------+------+------+------+---------+----+-----------------------+ | 1 | 1 | 0 | 0 | 0 | 0 | 0 | 1 | breakfast | | 1 | 2 | 0 | 0 | 0 | 0 | 0 | 2 | walk | | 1 | 3 | 1 | 0 | 1 | 1 | 1 | 3 | start | | 0 | 3 | 0 | 0 | 1 | 1 | 0 | 4 | office works | | 0 | 3 | 0 | 0 | 1 | 1 | 0 | 5 | office projects | | 0 | 3 | 0 | 1 | 1 | 0 | 1000000 | 6 | end | | 1 | 4 | 0 | 0 | 0 | 0 | 0 | 7 | taxi | | 1 | 5 | 1 | 0 | 1 | 1 | 1 | 8 | start | | 0 | 5 | 0 | 0 | 1 | 1 | 0 | 9 | preparing for meeting | | 0 | 5 | 0 | 0 | 1 | 1 | 0 | 10 | doing the meeting | | 0 | 5 | 0 | 1 | 1 | 0 | 1000000 | 11 | end | | 1 | 6 | 0 | 0 | 0 | 0 | 0 | 12 | night | +-----+------+------+------+------+------+---------+----+-----------------------+ 12 rows in set (0.00 sec) mysql> With a few refinements ignoring ord=1 and ord=1000000 GROUP BY on grpn Here is the final query first mentioned set @groupnum = 0; set @groupon = 0; set @inc = 1; select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from (select @inc := IF(@groupon=0,1,0) inc, @groupnum := @groupnum + @inc grpn, @s_tag := IF(diary='start',1,0) stag, @e_tag := IF(diary='end', 1,0) etag, @groupon := IF(diary='start',1,@groupon) g1, @groupon := IF(diary='end', 0,@groupon) g2, (@ordernum:=@e_tag*1000000+@s_tag) ord, id,diary from diary ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn; Here it is executed: mysql> set @groupnum = 0; Query OK, 0 rows affected (0.00 sec) mysql> set @groupon = 0; Query OK, 0 rows affected (0.00 sec) mysql> set @inc = 1; Query OK, 0 rows affected (0.00 sec) mysql> select grpn,GROUP_CONCAT(diary ORDER BY id) itinerary_step from -> (select -> @inc := IF(@groupon=0,1,0) inc, -> @groupnum := @groupnum + @inc grpn, -> @s_tag := IF(diary='start',1,0) stag, -> @e_tag := IF(diary='end', 1,0) etag, -> @groupon := IF(diary='start',1,@groupon) g1, -> @groupon := IF(diary='end', 0,@groupon) g2, -> (@ordernum:=@e_tag*1000000+@s_tag) ord, -> id,diary from diary -> ) A WHERE ord NOT IN (1,1000000) GROUP BY grpn; +------+-----------------------------------------+ | grpn | itinerary_step | +------+-----------------------------------------+ | 1 | breakfast | | 2 | walk | | 3 | office works,office projects | | 4 | taxi | | 5 | preparing for meeting,doing the meeting | | 6 | night | +------+-----------------------------------------+ 6 rows in set (0.00 sec) mysql> Give it a Try !!! CAVEAT I tried out @a1ex07's query against my sample data mysql> select d.id1, -> CASE -> WHEN COUNT(*) >1 THEN 'concatenated' -> ELSE NULL -> END AS `type`, -> GROUP_CONCAT( -> case -> when diary = 'start' or diary = 'end' then null -> else diary -> end -> order by id -> ) as diary -> FROM -> ( -> SELECT x.id, x.diary, -> CASE WHEN start_id IS NULL OR end_id IS NULL OR start_id > end_id THEN id -> ELSE start_id -> END as id1, -> end_id -> FROM -> ( -> select a.*, -> (select max(id) from diary b where b.diary ='start' and b.id (select min(id) from diary b where b.diary ='end' and b.id >=a.id) as end_id -> -> from diary a -> )x -> )d -> group by id1,end_id; +------+--------------+-----------------------------------------+ | id1 | type | diary | +------+--------------+-----------------------------------------+ | 1 | NULL | breakfast | | 2 | NULL | walk | | 3 | concatenated | office works,office projects | | 3 | NULL | taxi | | 8 | concatenated | preparing for meeting,doing the meeting | | 12 | NULL | night | +------+--------------+-----------------------------------------+ 6 rows in set (0.00 sec) mysql> By George, it works !!! He get's +1 for getting his answer in first.

You will have to look at this from different perspectives Data / Storage Engine The InnoDB Storage Engine is constructed to do as little writing as possible. As an ACID-complaint storage engine, InnoDB has mechanisms in place for concurrent manipulation of data. The structures used by the InnoDB Infrastrcuture are the following: Buffer Pool : Changes to Data and Index Pages that are Cached in RAM Double-Write Buffer : Backup buffer to prevent data corruption Insert Buffer : Changes to secondary non-unique indexes Redo Logs : Playback instructions in the event of Crash Recovery Undo Logs : Reverse instructions in the event of Crash Recovery/Rollback See Pictorial Representation Master In all likelihood, there are concurrent operations from multiple DB Connections performing INSERTs UPDATEs DELETEs SELECTs on the Master. These changes and lookups are cached and managed with the InnoDB Infrastructure. Flushing all recorded changes is serially managed but has no immediate effect on data concurrency. The only discernible bottleneck would be the time an SQL transaction decides to write the SQL Commands into its Binary Logs. The OS is responsible for flushing changes to binary logs to disk. Slave Here is where things can get interesting. While the Master handles everything thrown at it, it has to write the SQL Commands into its Binary Logs. The Slave has the responsibility to process what the Master gives it. Here is the MySQL Replication Paradigm IO Thread on Slave Connects to Master The Master Does the Following on a Continuous Basis: Completes SQL Command Records SQL Command in its Binary Logs The Slave Does the Following on a Continuous Basis: IO Thread from Slave Reads incoming SQL Commands from the Master's Binary Logs IO Thread Records the SQL into its Relay Logs SQL Thread from Slave Reads Next SQL Command to Be Executed SQL Thread Executes the Latest SQL Command From the Relay Logs If Binary Logging is enabled on the Slave, Slave Records SQL Command in its Binary Logs If you carefully follow the flow of control from this Paradigm description, you can easily see that all the SQL sent from the Master is serialized. In other words, the SQL commands from the Relay Logs are executed one at a time. This can manifest itself on the Slave as follows: When you run SHOW SLAVE STATUS\G, you see Seconds_Behind_Master increasing When you run SHOW PROCESSLIST;, the SQL thread (whose user is 'system user') is executing an SQL statement that is taking a long time on its own. This can easily happen when a Master is handling hundreds of SQL commands in parallel. It is when the SQL statement are lined up single-file and recorded as a FIFO queue on the Master, that the Slave must then process each command first-come, first serve only without any benefits from parallelism. Conclusions Drawn From the roles of Master and Slave given an all-InnoDB database, a Master to could handle multiple SQL transactions and be done with all commands. By design a Slave has no choice but to process commands in a serialized fashion. The background processing performed by a Master would easily get finished a stay quiet for a long time while a rather busy Slave would have process SQL one-by-one and have resources handle processed changes in a similar manner. Troubleshooting / Resolution Once you can see this funneling of SQL from Master to Slave, your only recourse would be to consider some of the following options to apply to the Slave and Master Query Optimization Create the needed indexes to support Query Optimization Make adjustments in the networking setup between Master and Slave Install a 2nd NIC on Master and Slave Assign IP address to the 2nd NIC (NIC_IP) Run CHANGE MASTER TO command to assign (NIC_UP) to MASTER_HOST Upgrade to MySQL 5.5 (if not using MySQL 5.5) Configure MySQL 5.5 configure InnoDB for multiple core engagement configure SemiSynch Replication Upgrade RAM Change to Individual Tablespaces

I can see how READ_COMMITTED can cause this situation. READ_COMMITTED allows for three things: Visibility of committed changes by other transactions using READ_COMMITTED isolation level. Non-Repeatable Reads : Transaction performing same retrieval with the possibility of getting a different result each time. Phantoms : Transactions may have rows appear where it was not visible beforehand. This creates an internal paradigm for the transaction itself because the transaction must maintain contact with: InnoDB Buffer Pool (while commit is still unflushed) Table's Primary Key Possibly the Double Write Buffer Undo Tablespace Pictorial Representation If two distinct READ_COMMITTED transactions are accessing the same tables/rows that are being updated in the same way, be ready to expect not a table lock, but an exclusive lock within the gen_clust_index (aka Clustered Index). Given the queries from your simplified case: Transaction 1 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET AUTOCOMMIT=0; BEGIN; DELETE c FROM child c INNER JOIN parent p ON p.id = c.parent_id WHERE p.id = 1; Transaction 2 SET TRANSACTION ISOLATION LEVEL READ COMMITTED; SET AUTOCOMMIT=0; BEGIN; DELETE c FROM child c INNER JOIN parent p ON p.id = c.parent_id WHERE p.id = 2; You are locking the same location in the gen_clust_index. One may say, "but each transaction has a different primary key.". Unfortunately, this is not the case in the eyes of InnoDB. It just so happens that id 1 and id 2 reside on the same page. Look back at information_schema.innodb_locks you supplied in the Question | lock_id | lock_trx_id | lock_mode | lock_type | lock_table | lock_index | lock_space | lock_page | lock_rec | lock_data | | '1A2973A4:0:3172298:2' | '1A2973A4' | 'X' | 'RECORD' | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' | | '1A296F67:0:3172298:2' | '1A296F67' | 'X' | 'RECORD' | '`deadlock_test`.`proc_warnings`' | '`PRIMARY`' | '0' | '3172298' | '2' | '53' | With the exception of lock_id, lock_trx_id, the rest of the lock description is identical. Since the transactions are on the same level playing field (same transaction isolation), this should indeed happen. Believe me, I have addressed this kind of situation before. Here are my past posts on this: Nov 05, 2012 : How to analyse innodb status on deadlock in insert Query? Aug 08, 2011 : Are InnoDB Deadlocks exclusive to INSERT/UPDATE/DELETE? Jun 14, 2011 : Reasons for occasionally slow queries? Jun 08, 2011 : Will these two queries result in a deadlock if executed in sequence? Jun 06, 2011 : Trouble deciphering a deadlock in an innodb status log

Based on your old_passwords setting, I would ask you to look at three things What password functions return What passwords are stored in mysql.user What authentication style is the client launching PASSWORD FUNCTIONS Please run the following: show variables like 'old_passwords'; select password('rolando'),old_password('rolando'); Depending on where old_passwords is enabled or not, you should get either the following: MySQL 5.0 with old_passwords off mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('rolando'),old_password('rolando'); +-------------------------------------------+-------------------------+ | password('rolando') | old_password('rolando') | +-------------------------------------------+-------------------------+ | *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409 | +-------------------------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> MySQL 5.1 with old_passwords on mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('rolando'),old_password('rolando'); +---------------------+-------------------------+ | password('rolando') | old_password('rolando') | +---------------------+-------------------------+ | 05e97e95241a4409 | 05e97e95241a4409 | +---------------------+-------------------------+ 1 row in set (0.00 sec) mysql> MySQL 5.5 with old_passwords off mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | OFF | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('rolando'),old_password('rolando'); +-------------------------------------------+-------------------------+ | password('rolando') | old_password('rolando') | +-------------------------------------------+-------------------------+ | *C1868CE781FCB521A03168DE044BF3F64E9E485A | 05e97e95241a4409 | +-------------------------------------------+-------------------------+ 1 row in set (0.00 sec) mysql> MySQL 5.5 with old_passwords on mysql> show variables like 'old_passwords'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | old_passwords | ON | +---------------+-------+ 1 row in set (0.00 sec) mysql> select password('rolando'),old_password('rolando'); +---------------------+-------------------------+ | password('rolando') | old_password('rolando') | +---------------------+-------------------------+ | 05e97e95241a4409 | 05e97e95241a4409 | +---------------------+-------------------------+ 1 row in set (0.00 sec) mysql> PASSWORDS STORED Please run this query: SELECT COUNT(1) password_length_count,password_length FROM (SELECT LENGTH(password) password_length FROM mysql.user) A GROUP BY password_length; If you get any password_length of 16, you have to enable old_passwords for the old style authentication to work. PASSWORDS FROM CLIENT In some cases, using mysql libraries that came from old yum installs of MySQL, PHP, Perl or Apache may have pre-MySQL4.1 user authentication code. Please upgrade such libraries.

Bascially yes, there's a way to do that. Here's a select statement that gets you each sentence with it's score SELECT PARSED_SENTENCES.sentence, SUM(COALESCE(WordScores.score, 0)) score FROM ( SELECT sentence, SUBSTRING( sentence FROM CASE WHEN INDEX_TABLE.POS = 1 THEN 1 ELSE INDEX_TABLE.POS + 1 END FOR CASE LOCATE(' ', sentence, INDEX_TABLE.POS + 1) WHEN 0 THEN CHARACTER_LENGTH(sentence) + 1 ELSE LOCATE(' ', sentence, INDEX_TABLE.POS + 1) END - CASE WHEN INDEX_TABLE.POS = 1 THEN 1 ELSE INDEX_TABLE.POS + 1 END ) AS word FROM SentenceScores INNER JOIN ( SELECT @rownum:=@rownum+1 POS FROM ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) a, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) b, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) c, (SELECT @rownum:=0) r ) INDEX_TABLE ON INDEX_TABLE.POS You should be able to convert that to an update statement so that you could calculate the scores and apply them to the table at the same time. Per the comment discussion below, if you wanted to replace the numbers-table subquery with a static numbers table you could replace this part of the query: INNER JOIN ( SELECT @rownum:=@rownum+1 POS FROM ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) a, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) b, ( SELECT 0 UNION ALL SELECT 1 UNION ALL SELECT 2 UNION ALL SELECT 3 UNION ALL SELECT 4 UNION ALL SELECT 5 UNION ALL SELECT 6 UNION ALL SELECT 7 UNION ALL SELECT 8 UNION ALL SELECT 9 ) c, (SELECT @rownum:=0) r ) INDEX_TABLE with just: INNER JOIN INDEX_TABLE where your numbers table and number column are assumed to have the same names as in the subquery, i.e. INDEX_TABLE and POS

This question came back on my radar after a recent comment was posted. This issue at the time turned out to be this reporting table that was meant to be maintained by triggers was on a slave only. The insert on duplicate key statements were getting flagged as not safe for statement based replication and getting pushed through in RBR (the stream was in mixed mode). RBR events do not fire triggers.