RE: Replication
WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
Hi, On Mon, Dec 8, 2008 at 9:20 AM, Marcel Grandemange [EMAIL PROTECTED] wrote: WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Here you are filtering your replication, are you happy the filter is correctly applied and that you understand this configuration option. Peter from Percona wrote a blog post about this last year. http://www.mysqlperformanceblog.com/2007/11/07/filtered-mysql-replication/ Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. This Seconds_Behind_Master: 0 combined with Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the slave is both up to date and running without problems caused by connectivity issues. How are you testing the differences between tables? to really know what is different you need to perform something like a table checksum, maatkit has a tool for that. http://www.maatkit.org/doc/mk-table-checksum.html If there really are differences between the two versions of table data I would suggest you are either filtering replication incorrectly (remember what filters are for master and which are for slaves) or you are using non-deterministic functions which when executed on the slave give a different result, something like this. http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-triggers-in-mysql-is-bad-for-replication/ Cheers, Ewen What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication
did u check if any of the file system holding bin-logs/data files are having enough free space. If the slave runs out off disk space, then you need to rebuild the slave from scratch. regards anandkl On 12/8/08, ewen fortune [EMAIL PROTECTED] wrote: Hi, On Mon, Dec 8, 2008 at 9:20 AM, Marcel Grandemange [EMAIL PROTECTED] wrote: WHat errors are you getting when you try and start the slave? That's the exact thing mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.11.252 Master_User: cjcrepl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.05 Read_Master_Log_Pos: 98 Relay_Log_File: gw2-relay-bin.99 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.05 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: cjcd0,cjcd0 Here you are filtering your replication, are you happy the filter is correctly applied and that you understand this configuration option. Peter from Percona wrote a blog post about this last year. http://www.mysqlperformanceblog.com/2007/11/07/filtered-mysql-replication/ Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) According to the slave all is running and 100%, although the data is visibly outdated. And updates to tables or even new tables do not replicate since connectivity loss.. This Seconds_Behind_Master: 0 combined with Slave_IO_Running: Yes and Slave_SQL_Running: Yes indicate that the slave is both up to date and running without problems caused by connectivity issues. How are you testing the differences between tables? to really know what is different you need to perform something like a table checksum, maatkit has a tool for that. http://www.maatkit.org/doc/mk-table-checksum.html If there really are differences between the two versions of table data I would suggest you are either filtering replication incorrectly (remember what filters are for master and which are for slaves) or you are using non-deterministic functions which when executed on the slave give a different result, something like this. http://www.mysqlperformanceblog.com/2008/09/29/why-audit-logging-with-triggers-in-mysql-is-bad-for-replication/ Cheers, Ewen What does the error log say? Good Day. Im wondering if someone can assist me. Ive been using replication for a while now and it tends to fail very easily. One of my sites lost connectivity for a while and when it came back obviously replication broke again. How can I get it to populate all data from master again? Load data from master; is being depreciated and doesn't really work anyhow. I do not wish to create dump and do this manually every time it fails. Advise? Also is replication really this unreliable? It breaks at the slightest hiccup... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ This email has been scanned by Netintelligence http://www.netintelligence.com/email -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple Cores.
How we can configure Mysql in such a way so that i start using all the cores of CPU. I Have a QuadCore server but somehow mysql use only single core whose usage percentage goes upto 99% while other three cores remains idle. Any Idea or Multiple Core/CPU is wastage. -- Kunal Jain, burrp! http://burrp.com
Uptimize: join table on if()
Hi list I have this statement that really need optimizing. The result is about 5500 rows, and it runs for about 3-5 minutes. If i do the same in a PHP script (outer loop, inner loop) it run in 20 sec. The idea is that data in tdata might be shared between 2 or more records in main. The main.parent field is a pointer to main.code, so if main.parent is positive, i need to retrieve data linked to parent. Did i miss something? select m.code, m.parent, t.data from main m left join tdata t on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and t.country='dk' ) where m.active = 'on' and m.tdataon = 'on' order by m.code; CREATE TABLE `main` ( `code` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `active` varchar(2) NOT NULL, `tdataon` varchar(2) NOT NULL default '', PRIMARY KEY (`code`), KEY `parent` (`parent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `data` ( `code` int(10) unsigned NOT NULL default '0', `country` varchar(2) NOT NULL default '', `data` varchar(130) NOT NULL default '', PRIMARY KEY (`code`,`country`), KEY `code` (`code`), KEY `country` (`country`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Later Mogens Melander -- This message has been scanned for viruses and dangerous content by MailScanner, and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 6.0.8 Alpha has been released!
Dear MySQL users, MySQL 6.0.8-alpha, a new version of the MySQL database system has been released. The main page for MySQL 6.0 release is at http://www.mysql.com/mysql60/ MySQL 6.0 includes two new storage engines: the transactional Falcon engine, and the crash-safe Maria engine. If you are new to the Falcon storage engine and need more information, please read the Falcon Evaluation Guide at http://www.mysql.com/why-mysql/white-papers/falcon-getting-started.php and the Falcon White Paper at http://www.mysql.com/why-mysql/white-papers/storage-engines-falcon.php The Maria storage engine is a crash safe version of MyISAM. Maria supports all of the main functionality of the MyISAM engine, but includes recovery support (in the event of a system crash), full logging (including CREATE, DROP, RENAME and TRUNCATE operations), all MyISAM row forma ts and a new Maria specific row format. Maria is documented at http://dev.mysql.com/doc/refman/6.0/en/se-maria.html MySQL 6.0.8-alpha is available in source and binary form for a number of platforms from our download pages at http://dev.mysql.com/downloads/mysql/6.0.html and mirror sites. Note that not all mirror sites may be up to date at this point in time, so if you can't find this version on some mirror, please try again later or choose another download site. We welcome and appreciate your feedback, bug reports, bug fixes, and patches at http://forge.mysql.com/wiki/Contributing The following section lists important, incompatible and security changes since the previous version of MySQL 6.0. The full changelog, including many more fixes can be viewed online at http://dev.mysql.com/doc/refman/6.0/en/news-6-0-8.html Sincerely, Kent Boortz The MySQL build team at Sun Microsystems == Functionality added or changed: * Incompatible Change: The tables for MySQL Backup logging have been renamed, and the logging capabilities now are more flexible, similar to the capabilities provided for the general query log and slow query log. + The names of the MySQL Backup log tables in the mysql database have been changed from 'online_backup' and 'online_backup_progress' to 'backup_history' and 'backup_progress'. + Logging now can be enabled or disabled, it is possible to log to tables or to files, and the names of the log files can be changed. For details, see Section 6.3.3.1, MySQL Backup Log Control. + A new statement, FLUSH BACKUP LOGS, closes and reopens the backup log files. A new option for mysql_refresh(), REFRESH_BACKUP_LOG, performs the same operation. * Important Change: The '--skip-thread-priority' option is now deprecated in MySQL 5.1 and is removed in MySQL 6.0 such that the server won't change the thread priorities by default. Giving threads different priorities might yield marginal improvements in some platforms (where it actually works), but it might instead cause significant degradation depending on the thread count and number of processors. Meddling with the thread priorities is a not a safe bet as it is very dependent on the behavior of the CPU scheduler and system where MySQL is being run. (Bug#35164: http://bugs.mysql.com/35164, Bug#37536: http://bugs.mysql.com/37536) * Important Change: The '--log' option now is deprecated and will be removed (along with the log system variable) in the future. Instead, use the '--general_log' option to enable the general query log and the '--general_log_file=file_name' option to set the general query log filename. The values of these options are available in the general_log and 'general_log_file' system variables, which can be changed at runtime. Similar changes were made for the '--log-slow-queries' option and 'log_slow_queries' system variable. You should use the '--slow_query_log' and '--slow_query_log_file=file_name' options instead (and the 'slow_query_log' and 'slow_query_log_file' system variables). * The BUILD/compile-solaris-* scripts now compile MySQL with the mtmalloc library rather than malloc. (Bug#38727: http://bugs.mysql.com/38727) * BACKUP DATABASE now performs an implicit commit, like RESTORE. (Bug#38261: http://bugs.mysql.com/38261) * The deprecated '--default-table-type' server option has been removed. (Bug#34818: http://bugs.mysql.com/34818) * On WIndows, use of POSIX I/O interfaces in mysys was replaced with Win32 API calls (CreateFile(), WriteFile(), and so forth) and the default maximum number of open files has been increased to 16384. The maximum can be increased further by using the '--max-open-files=N' option at server startup. (Bug#24509: http://bugs.mysql.com/24509) * Most statements that previously caused an implicit commit before
Re: Multiple Cores.
Kunal Jain schrieb: How we can configure Mysql in such a way so that i start using all the cores of CPU. I Have a QuadCore server but somehow mysql use only single core whose usage percentage goes upto 99% while other three cores remains idle. Any Idea or Multiple Core/CPU is wastage. IMHO, normaly the (linux) kernel handels all distribution of processes, and that you have 99% load means there is no need to use an other CPU. Switching between CPUs has a measureable overheat and it is possible that the kernel assumes that you will not benefit from using that other CPU. re, wh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple Cores.
MySQL processes each request in a separate thread... hence, it is *always* multi-core capable. Check the thread_concurrency setting in my.cnf. Also, although not exactly related, check the size of thread_cache. Are you by chance running 1 long-running query at a time? A single query runs in a single thread, hence can never use more than one core. So if your workload is dominated by 1 long-running query after another, it may seem like it's using only one core. Periodically check the output of mysqladmin status... if the thread counter is 1, you're using multiple cores... assuming, of course, that your OS actually *knows* about them (ie: you're running an SMP-aware OS kernel). Jake On Mon, Dec 8, 2008 at 4:52 AM, Kunal Jain [EMAIL PROTECTED] wrote: How we can configure Mysql in such a way so that i start using all the cores of CPU. I Have a QuadCore server but somehow mysql use only single core whose usage percentage goes upto 99% while other three cores remains idle. Any Idea or Multiple Core/CPU is wastage. -- Kunal Jain, burrp! http://burrp.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Uptimize: join table on if()
The idea is that data in tdata might be shared between 2 or more records in main. Is this any faster? select m.code, m.parent, t.data from main m left join tdata t on t.code = m.parent and t.country='dk' where m.parent=0 and m.active = 'on' and m.tdataon = 'on' union select m.code, m.parent, t.data from main m left join tdata t on t.code = m.code and t.country='dk' where m.parent=0 and m.active = 'on' and m.tdataon = 'on' order by code; Or this? select m.code, m.parent, t.data from main m left join tdata t on t.code = IF(m.parent0,m.parent,m.code) and t.country='dk' where m.active = 'on' and m.tdataon = 'on' order by m.code; PB - Mogens Melander wrote: Hi list I have this statement that really need optimizing. The result is about 5500 rows, and it runs for about 3-5 minutes. If i do the same in a PHP script (outer loop, inner loop) it run in 20 sec. The idea is that data in tdata might be shared between 2 or more records in main. The main.parent field is a pointer to main.code, so if main.parent is positive, i need to retrieve data linked to parent. Did i miss something? select m.code, m.parent, t.data from main m left join tdata t on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and t.country='dk' ) where m.active = 'on' and m.tdataon = 'on' order by m.code; CREATE TABLE `main` ( `code` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `active` varchar(2) NOT NULL, `tdataon` varchar(2) NOT NULL default '', PRIMARY KEY (`code`), KEY `parent` (`parent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `data` ( `code` int(10) unsigned NOT NULL default '0', `country` varchar(2) NOT NULL default '', `data` varchar(130) NOT NULL default '', PRIMARY KEY (`code`,`country`), KEY `code` (`code`), KEY `country` (`country`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; Internal Virus Database is out of date. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.11/1820 - Release Date: 11/29/2008 6:52 PM
RE: Randomize by Score and DESC
-Original Message- From: Brent Baisley [mailto:[EMAIL PROTECTED] Sent: Saturday, December 06, 2008 11:35 AM To: sangprabv Cc: mos; mysql@lists.mysql.com Subject: Re: Randomize by Score and DESC That's because RAND() is a decimal (0.37689672). Try score*RAND(). [JS] That in itself shouldn't matter. A random number is a random number, regardless of the scale. ORDER BY RAND() is equivalent to ORDER BY myweight*RAND(). As for ordering by score*RAND(), if score comes up 12 and RAND() comes up .1, you will have a product equal to 1.2; if score comes up as 10 and RAND() comes up .2, then you'll have a product equal to 2.0. That is not going to give you what you want. I'm pretty sure what you need to do is come up with a random sample first, and then order the result. SELECT score FROM (SELECT score FROM table WHERE RAND() .1) ORDER BY score DESC; That should give you a random sample of approximately 10% of your original table, in descending order by score. Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com Brent Baisley On Nov 30, 2008, at 2:03 AM, sangprabv wrote: Hi, Thans for the reply, I have tried it but I don't see the RAND() to be work. This list is always the same. TIA Willy -Original Message- From: mos [EMAIL PROTECTED] To: sangprabv [EMAIL PROTECTED], mysql@lists.mysql.com Subject: Re: Randomize by Score and DESC Date: Sat, 29 Nov 2008 13:05:09 -0600 Mailer: QUALCOMM Windows Eudora Version 6.0.0.22 At 09:15 AM 11/29/2008, sangprabv wrote: Hi, I have a query like this SELECT * FROM table ORDER BY score DESC, RAND() The aim is to randomize the result by score with descending order. But it doesn't work. What missed here? TIA. Willy Willy, That is because you are ordering by Score then Rand. If Score is an integer, try SELECT * FROM table ORDER BY score+RAND() DESC This will randomize the higher scores first, followed by the lower scores etc.. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Vexing permissions issue with partitioned CREATE TABLE
Thanks to everyone who offered their suggestions. Those of you who suggested I look into SElinux were correct - my admin adjusted the policy and rebooted the server, and my problem is solved. I've never run across that issue before, but I'll know to look for it in the future. The list the community were a big help in pointing me in the right direction. Cheers, - Brad On Sat, Dec 6, 2008 at 6:48 AM, Per Jessen [EMAIL PROTECTED] wrote: Brad Heintz wrote: Thanks for responding. The CREATE TABLE docs for 5.1 say that DATA DIRECTORY and INDEX DIRECTORY take absolute paths (not relative), and will in fact reject paths containing the MySQL data dir. Because I'm out of other ideas, I did try creating the directories under the MySQL data dir and it doesn't change the error, so it has nothing to do with MySQL secretly expecting relative paths. I have created the directories by hand, and as I said in my original email, I've tried chown'ing them to the MySQL user. No change in outcome. If you're running SElinux or AppArmor, check the audit logs, e.g. /var/log/audit/audit.log /Per Jessen, Zürich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brad Heintz [EMAIL PROTECTED]
Re: Uptimize: join table on if()
On Mon, Dec 8, 2008 at 7:14 AM, Mogens Melander [EMAIL PROTECTED] wrote: Hi list I have this statement that really need optimizing. The result is about 5500 rows, and it runs for about 3-5 minutes. If i do the same in a PHP script (outer loop, inner loop) it run in 20 sec. The idea is that data in tdata might be shared between 2 or more records in main. The main.parent field is a pointer to main.code, so if main.parent is positive, i need to retrieve data linked to parent. Did i miss something? select m.code, m.parent, t.data from main m left join tdata t on ( if( m.parent 0, t.code = m.parent, t.code = m.code ) and t.country='dk' ) where m.active = 'on' and m.tdataon = 'on' order by m.code; CREATE TABLE `main` ( `code` int(10) unsigned NOT NULL default '0', `parent` int(10) unsigned NOT NULL default '0', `active` varchar(2) NOT NULL, `tdataon` varchar(2) NOT NULL default '', PRIMARY KEY (`code`), KEY `parent` (`parent`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; CREATE TABLE `data` ( `code` int(10) unsigned NOT NULL default '0', `country` varchar(2) NOT NULL default '', `data` varchar(130) NOT NULL default '', PRIMARY KEY (`code`,`country`), KEY `code` (`code`), KEY `country` (`country`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- What you are doing wrong is putting a condition on the JOIN. This prevent MySQL from optimizing the query because it has to check every record to determine the join. Put the condition in the field list you pull. Alias the table you are joining on so you can join it twice, one for each condition. select m.code, m.parent, if( m.parent 0, t.data, t1.data ) AS data from main m left join tdata t on t.code = m.parent and t.country='dk' left join tdata t1 on t1.code=m.code and t1.country='dk' where m.active = 'on' and m.tdataon = 'on' order by m.code; That may not be completely correct. What you are doing is getting 2 copies of the data field and conditional adding the one you need to the retrieved record. Brent Baisley -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
different results from '=' vs. 'LIKE'
Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison-functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:03 PM, Daevid Vincent wrote: Do you seriously have a column named 0020,0032 ?!!? And don't even get me started on the actual name of these images (column data). Jepp, and there are a hell of a lot of more weird number like that. That's an attribute tag from DICOM images. The names might alter, the tag value won't. Wow. That makes my head hurt. I think mySQL is just punishing you for both of those offenses. *hee hee* ;-p But if I were to venture a guess, and RTFM... http://dev.mysql.com/doc/refman/5.1/en/string-comparison- functions.html I'd say it might be related to casting. If a string function is given a binary string as an argument, the resulting string is also a binary string. A number converted to a string is treated as a binary string. This affects only comparisons. So perhaps in the first case you're dealing with strings, but in the second case you're dealing with numbers? Or possibly you're not escaping your \ enough? Yeah, I actually figured that out after writing the first mail. Then I was talking to myselfe for quite a while like: freakin' 4 backslashes just to get one out of in the end, tsss. To search for “\”, specify it as “”; this is because the backslashes are stripped once by the parser and again when the pattern match is made, leaving a single backslash to be matched against. I don't know, but that should point you in the right direction and perhaps convince you to rename your column and use a more sane data naming convention... good luck! Thx for the feedback and cooperation! :) Cheers, Michael D.Vin http://daevid.com On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: different results from '=' vs. 'LIKE'
On Dec 9, 2008, at 2:27 PM, Daevid Vincent wrote: Also, I realize you're trying to 'encode' some sort of X\Y\Z coordinates in that column, so perhaps a different delimiter such as the pipe | character or , would be more appropriate than a \ which has special meanings? That's actually how the values are in original. At this stage, I don't want to mess around with it further. But exporting it later to another coordinate-table, if that'll be required, is being thought of. Perhaps the field might also get dropped. (Ups, now the wholly god of db schema design will put rage upon me for bad designing in the first place ... not to mention the NU** values ... :-$) Thx for the hint! :) Cheers, Michael Or possibly just split them out into separate X, Y, Z columns rather than cramming them together like that. This would allow you to do various trig and math functions on them easier (assuming you are storing coordinates for a reason). There are basic SQL 'update' statements you could write to fix your existing data and/or convert it to the new delimiter. This may save you headaches going forward. d. On Tue, 2008-12-09 at 12:42 +1300, SolidEther wrote: Hi, I'm trying to compare strings on a varchar field. The code: 'select * from Image where `0020,0032`=-131.178600\ \107.113725\\200.064000;' returns the correct result set. However, the code: 'select * from Image where `0020,0032` LIKE %-131.178600\\107.113725\\200.064%;' returns an empty set, and so does 'select * from Image where `0020,0032` LIKE -131\.178600\\107\. 113725\\200\.064000;' I can't really figure out why, can anyone explain? Thx, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Call for Articles for Winter Issue of MySQL Magazine
Its that time again. Time for fame and fortune! Sorry, as usual there is no fortune, but the good news is that our readership continues to grow so author fame is imminent. We are planning the winter issue of MySQL Magazine (http://www.mysqlzine.net). With the new GA release of MySQL server 5.1 last month there is certainly plenty to talk about! Draft articles will need to be in by the 10th of January so you have a month to get things ready. I need to hear about possible articles by the 15th of this month. I can be reached at bmurphy AT paragon-cs.com with your ideas. I want to mention that in the past we have used blog postings (with author permission and full credit of course). If you have blogged something recently that you feel is worthy of publishing in the magazine send me the link and I will take a look. Never heard of MySQL Magazine? It is a digital magazine designed for both database administrators and developers of the MySQL database server. All six back issues are available for free download from the website. Thanks, Keith Murphy - editor MySQL Magazine http://www.mysqlzine.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
