How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. I'm doing this in PHP and mySQL (of course), so if it can't be done with a single query, I can split it up. Here's the challenge, given a text field search box, someone enters: Sony 20 TV How do I search for that, not knowing which fields are which? For example, they could have also entered: 20 Sony TV This is the one I have now, but (as you probably noticed), it will return many rows, I expect that most of the time 1 row will be returned, but I'm getting a grip more than I want (or the customer would want), and also rows that have nothing to do with the search terms. SELECT products.*, companies.name AS company_name, categories.name AS category_name FROM products LEFT JOIN companies ON company_id = companies.id LEFT JOIN categories ON category_id = categories.id WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%') OR (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') OR (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') OR (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') OR (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%') ) ORDER BY categories.name DESC, products.name ASC, companies.name ASC; (and that just gets uglier the more words in the search) ++--+--+--+---+ | id | name | model| company_name | category_name | ++--+--+--+---+ | 1 | 20 TV | STV20-KVR-HD | Sony | Tube | --- | 2 | 36 TV | STV36-KVR-HD | Sony | Tube | | 4 | Ultra-Plasma 62 | UP62F900 | Sony | Plasma| | 5 | Value Plasma 38 | VPR542_38| Sony | Plasma| | 6 | Power-MP3 5gb| 09834wuw34 | Sony | MP3 Players | | 3 | Super-LCD 42| SLCD42hd002 | Sony | LCD | | 7 | Super-Player 1gb | SP1gb| Sony | Flash | | 8 | Porta CD | pcd500 | Sony | CD Players| .. ++--+--+--+---+ Obviously the person wanted id = 1 in this case. Unrelated, is there any speed improvement using JOIN instead of LEFT JOIN ? Think millions of products. Thanks for help and suggestions... Daevid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
On Fri, May 4, 2007 10:21, Daevid Vincent wrote:
I'm having trouble figuring out the logic/query I want.
I know that all those ORs are not right.
I'm doing this in PHP and mySQL (of course),
so if it can't be done with a single query, I can split it up.
Here's the challenge, given a text field search box, someone enters:
Sony 20 TV
How do I search for that, not knowing which fields are which?
For example, they could have also entered:
20 Sony TV
How about:
select soundex('Sony 20 TV' ),soundex('20 Sony TV');
'S531', 'S531'
--
Later
Mogens Melander
+45 40 85 71 38
+66 870 133 224
--
This message has been scanned for viruses and
dangerous content by OpenProtect(http://www.openprotect.com), 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]
Object-Oriented database
Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Re: Object-Oriented database
You can store any object as blob, but that's not a good idea (unless object is atomic in terms of your application). If you want to refer to your data in object-oriented manner, you can use one of the ORM pattern implementations on the client sise. As for a separate data type, no, MySQL doesn't have it. - Olexandr Melnyk, http://omelnyk.net/ 2007/5/4, sam rumaizan [EMAIL PROTECTED]: Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center.
Re: expire_logs_days
Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
Hi Daevid, Daevid Vincent wrote: I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. I'm doing this in PHP and mySQL (of course), so if it can't be done with a single query, I can split it up. Here's the challenge, given a text field search box, someone enters: Sony 20 TV How do I search for that, not knowing which fields are which? For example, they could have also entered: 20 Sony TV I think you're describing full-text indexing. MySQL supports it but only on MyISAM tables. If you don't want to use MyISAM, full-text search engines like Lucene or Sphinx may be worth looking at. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: secure port 3306
Thank You so much. I will have to try this later today. I have never done a port forward in iptables before. I knew that I could, but just never had a need or tried so it slipped my mind about this. Again, Thank You. Steve On linux, one could do a port forward: EXTIF=eth0 # Or whatever the interface that faces internet is called. iptables -A FORWARD -i $EXTIF -p tcp -s client-ip --dport 3306 -j ACCEPT iptables -A PREROUTING -t nat -p tcp -s client-ip \ -d linux-fw-ip --dport 3306 -j DNAT --to internal-ip:3306 On Wed, May 2, 2007 17:03, Steven Buehler wrote: I have a client that needs to be able to remotely connect to port 3306 securely. I have tried to suggest an SSH Tunnel, but they do not want their clients to have SSH access. Another problem is that even if we do tunnel, it needs to go thru one server that is connected to the Internet and into the MySQL server which is NOT accessible from the Internet. Any suggestions? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), 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]
Re: expire_logs_days
Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 Cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Object-Oriented database
If you serialize the object, you can store it in the data base as a blob. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: sam rumaizan [mailto:[EMAIL PROTECTED] Sent: Friday, May 04, 2007 6:27 AM To: mysql@lists.mysql.com Subject: Object-Oriented database Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fwd: expire_logs_days
-- Forwarded message -- From: Jake Peavy [EMAIL PROTECTED] Date: May 4, 2007 7:41 AM Subject: Re: expire_logs_days To: Baron Schwartz [EMAIL PROTECTED] On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. -jp
Re: expire_logs_days
Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
Baron Schwartz wrote: I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 OK even with a slave connected to a master with expire_logs_days, I still see the desired affect. I've made a note on the bug - let's continue discussion on there? Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. Baron Thanks for the reply. I went and checked a few things and in my data dir, there is a mysql.log file that is actively logging any mysql activity. In data dir/mysql there is a general_log.CSM, general_log.CSV, general_log.frm, and the same goes for slow_log. However, the .CSV files are empty. Based on this, any additional thoughts for me? Again, thanks for your help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Hi, CA Lists wrote: Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. Baron Thanks for the reply. I went and checked a few things and in my data dir, there is a mysql.log file that is actively logging any mysql activity. In data dir/mysql there is a general_log.CSM, general_log.CSV, general_log.frm, and the same goes for slow_log. However, the .CSV files are empty. Based on this, any additional thoughts for me? What is the output of SHOW CREATE TABLE? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Baron Schwartz wrote: Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. It looks like there was a 5.1 installation here at some point - 5.0.37 should not include the general_log or slow_log tables - these are 5.1 new features. I suspect that you likely have general_log and slow_log frm files within the mysql database directory, without any corresponding data files. If this is the case then just 'rm' the general_log.frm and slow_log.frm files from the mysql database directory, and all these errors should go away. Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't find file: 'general_log'
Hi, CA Lists wrote: Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. Baron Thanks for the reply. I went and checked a few things and in my data dir, there is a mysql.log file that is actively logging any mysql activity. In data dir/mysql there is a general_log.CSM, general_log.CSV, general_log.frm, and the same goes for slow_log. However, the .CSV files are empty. Based on this, any additional thoughts for me? What is the output of SHOW CREATE TABLE? Baron Sorry, forgot to include that. It's: ERROR 1017 (HY000): Can't find file: 'general_log' (errno: 2) -- Joseph Koenig Creative Anvil, Inc. Ph: 314.773.2611 FX: 314.773.2942 http://www.creativeanvil.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multilanguage web application with mysql database?
I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, enter the Chinese name in gb2312, and then switch the session language to big5, enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks
RE: multilanguage web application with mysql database?
Are all of your fields using the UTF8 character set? I think that's necessary. We use UTF8 and have stored Chinese characters successfully using UTF8 with a PHP web application. (At least, they look right to me - I don't know Chinese at all.) Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: YL [mailto:[EMAIL PROTECTED] Sent: Friday, May 04, 2007 10:19 AM To: mysql@lists.mysql.com Subject: multilanguage web application with mysql database? I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, enter the Chinese name in gb2312, and then switch the session language to big5, enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BEFORE trigger abd REPLACE
We have come across an interesting anomoly in MySQL behaviour in the course of our investigations. It seems that when A table is equiped with both BEFORE INSERT and BEFORE DELETE triggers and a REPLACE statement is used against that table, the INSERT trigger is fired first and the DELETE trigger is fired afterwards.. this surprised us as the REPLACE is supposed to fire a DELETE and then an INSERT. This behavious was not found when using AFTER triggers which act is the expected manner. This was discovered by creating triggers which inserted a 'journal' row into a temporary table (using an AUTO_INCREMENT) and then by observing the resulting records. This was observed in 5.0.34 Enterprise; we have not gone through other versions to see if this bug is to be found elsewhere. Happily, we are not using REPLACE (and have no intention or desire to do it) in our application so this is not causing our project any real inconvenience, but it could come as a nasty shock to some. -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Object-Oriented database
This really isn't what he's talking about - rather than storing data as rows and tables, you store as objects and methods. MySQL does not support this; you can get this sort of functionality using something like Hibernate (an Object-Relational-Mapping tool), which is free but has a learning curve. David -Original Message- From: Jerry Schwartz [mailto:[EMAIL PROTECTED] Sent: May 4, 2007 6:36 AM To: 'sam rumaizan'; mysql@lists.mysql.com Subject: RE: Object-Oriented database If you serialize the object, you can store it in the data base as a blob. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: sam rumaizan [mailto:[EMAIL PROTECTED] Sent: Friday, May 04, 2007 6:27 AM To: mysql@lists.mysql.com Subject: Object-Oriented database Is there such thing call Object-Oriented database for mysql? Basically can I store an item as an object in myql? - Looking for earth-friendly autos? Browse Top Cars by Green Rating at Yahoo! Autos' Green Center. -- 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: Can't find file: 'general_log'
Baron Schwartz wrote: Hi Joseph, Joseph Koenig wrote: Hi, I'm running MySQL 5.0.27-standard-log on RHEL 4. After install, I moved the data directory of MySQL. I updated everything that SELinux complained about, etc., and have a functional install of MySQL. It's actually been up and running with no issues for months. However, every time I run a mysqldump, I get: mysqldump: Got error: 1017: Can't find file: 'general_log' (errno: 2) when using LOCK TABLES mysqldump: Couldn't execute 'show create table `general_log`': Can't find file: 'general_log' (errno: 2) (1017) mysqldump: Couldn't execute 'show create table `slow_log`': Can't find file: 'slow_log' (errno: 2) (1017) My dump proceeds and just spits these errors out to me. From what I can tell, no harm is done, as the dump is full and is perfectly usable for restoring databases from. However, it's driving me nuts. Is there any way to create the necessary tables now so that MySQL does actually start logging everything they way it should be able to and also will make these errors go away? Thanks in advance, It's a hard to tell from this description what is the matter. Can you connect via mysql and run SHOW CREATE TABLE slow_log without errors? If so, what storage engine do they use? It sounds to me like they might be using the CSV storage engine and the file isn't there. Error 2 is 'OS error code 2: No such file or directory' according to perror. You probably don't want to mysqldump a big CSV file of your general log, at least not if you're using this for backups (but maybe you do, I don't know). In short, you might want to DROP the tables instead of creating them. It looks like there was a 5.1 installation here at some point - 5.0.37 should not include the general_log or slow_log tables - these are 5.1 new features. I suspect that you likely have general_log and slow_log frm files within the mysql database directory, without any corresponding data files. If this is the case then just 'rm' the general_log.frm and slow_log.frm files from the mysql database directory, and all these errors should go away. Cheers, Mark Brilliant! You are absolutely right - I had installed 5.1 at one point, realized it was still alpha and went back to 5.0.27. I got rid of the .frm files and all is well. Thanks! -- Joseph Koenig Creative Anvil, Inc. Ph: 314.773.2611 FX: 314.773.2942 http://www.creativeanvil.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: expire_logs_days
On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. Yes, this quote refers to file locking/concurrent access to the bin files. What I was getting at is if the slave has fallen behind and hasn't yet parsed some particular bin files, you wouldn't want to remove them from the master until the slave I/O thread was able to parse them. Otherwise your slave would lose those database changes and thus be out of sync. When purging master logs in a replicated setup one must first examine the result of SHOW SLAVE STATUS and only PURGE MASTER LOGS up to the log indicated by Master_Log_File. -- -jp Chuck Norris frequently donates blood to the Red Cross. Just never his own.
Re: expire_logs_days
Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Hi, Jake Peavy wrote: On 5/4/07, Baron Schwartz [EMAIL PROTECTED] wrote: Mark Leith wrote: Baron Schwartz wrote: I will test again on my servers now that I have upgraded to 5.0.38. One question for people for whom expire_logs_days DOES work: do you have any slaves connected to the server? I did not within my test. I could easily add that if need be however.. Let me know if your testing does show that it's not working for you. I think we've found the bug. I just did a bunch of tests and I'm 99% sure not only does expire_logs_days not work if there are slaves attached, neither does PURGE MASTER LOGS. When I read my email this morning, Nagios alerted me the master server was over the expected disk usage, and I looked at the disk and saw our nightly PURGE MASTER LOGS job hasn't been working. http://bugs.mysql.com/28238 It seems to me that some communication is neccessary in the case of replication -- you wouldn't want to purge MASTER logs if the slave hadn't parsed them yet. Perhaps this is why the feature is disabled in this case. Not according to http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html: This statement is safe to run while slaves are replicating. You do not need to stop them. If you have an active slave that currently is reading one of the logs you are trying to delete, this statement does nothing and fails with an error. Yes, this quote refers to file locking/concurrent access to the bin files. What I was getting at is if the slave has fallen behind and hasn't yet parsed some particular bin files, you wouldn't want to remove them from the master until the slave I/O thread was able to parse them. Otherwise your slave would lose those database changes and thus be out of sync. When purging master logs in a replicated setup one must first examine the result of SHOW SLAVE STATUS and only PURGE MASTER LOGS up to the log indicated by Master_Log_File. Understood. But that is a reason for DBA caution, not a reason for disabling the feature as you wrote above. If the feature were disabled when there are any slaves connected, the manual should say so. It looks like other people have found the feature to work when there are slaves, so I'm sure it's just some configuration or other problem with my (and many other people's) setup. -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multilanguage web application with mysql database?
I have many tables like the table Person:below, in mysql database. person_id, first_name,last_name, mi, gb_first_name, gb_last_name, b5_first_name, b5_last_name, gender, dob where different columns storing strings in different encodings. All the strings/fields etc. need to be in the *same* encoding - UTF-8 Only UTF-8 can handle all languages. At anytime, a web user can switch the language and the application will get the values in the right columns to generate web pages. The purpose of Multi-language tables is to make multilanguage dynamic content management easier for web applications. For example, to add a person record, the user enter the English name, then switch the session language to gb2312, gb2312 is an encoding - not a language. enter the Chinese name in gb2312, and then switch the session language to big5, big5 is an encoding - not a language enter the name in big5. And then commit the data into the database. The whole thing sounds complicated but can be treated as a pattern and let a framework to take care of those and the code can be as clean as a single language app. I actually have the framework that works well for me with mysql database. I didn't do anything about language encoding in mysql database, it just worked for me. At least with english, gb2312 and big5 altogether in a table like table Person above. I noticed that (english, gb2312, big5, Jp) cannot work together (where jp is any kind of japaness language encoding). My approach seems fine with most western languages So after all such experimental work, I still don't know how to make a real multi language web app such that the languages are switchable within the same session. Any suggestions? Any web application known to be able to solve the problem? Thanks Everything needs to be in UTF-8. - the database - application logic - and all web interfaces. Forget about big5, gb2312, shift_jis etc! Also, web forms submit content in the encoding of the web page. So make sure your http content-type header is set to UTF-8 for all web pages. I'm not sure what technology you're using - php/java etc? By default, most web servers/applications serve content as ISO-8859-1encoding - which only works for western European languages. - unless you specifically tell it otherwise. In a JSP for example, you need to put this directive at the top of a page: %@ page contentType=text/html;charset=UTF-8 pageEncoding=UTF-8 % This article is useful - http://java.sun.com/developer/technicalArticles/Intl/HTTPCharset/index.html even if you're not using Java technology. ~mm
indexing order column
hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Order By and Ignore Punctuation
Try something like this. If there are multiple punctuation values you
want to ignore you can nest multiple REPLACE functions.
mysql create table names (name varchar(20));
Query OK, 0 rows affected (0.01 sec)
mysql insert into names values
('Osbourn'),(O'shea),(O'Malley),('Olathe'),('Ottawa');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql SELECT name
FROM names
ORDER BY REPLACE(name,','');
+--+
| name |
+--+
| Olathe |
| O'Malley |
| Osbourn |
| O'shea |
| Ottawa |
+--+
5 rows in set (0.00 sec)
-Original Message-
From: Andreas Iwanowski [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 7:48 PM
To: Bill Guion
Cc: mysql@lists.mysql.com
Subject: RE: Order By and Ignore Punctuation
I would suggest you order by something that includes a fulltext index on
the specific column.
Maybe check out the documentation on the MATCH()AGAINST() systax as well
as fulltext searches in general.
For example:
SELECT Col1, Col2, Score AS MATCH(TextCol) AGAINST () WHERE ... ORDER
BY Score;
Hope to help,
-Andy
-Original Message-
From: Bill Guion [mailto:[EMAIL PROTECTED]
Sent: Thursday, May 03, 2007 8:33 PM
To: mysql@lists.mysql.com
Subject: Order By and Ignore Punctuation
I would like to perform a query of a personnel database with an ORDER BY
clause that ignores punctuation. For example, O'shea would sort after
Osbourne, not to the beginning of the Os.
Is this doable in the query?
-= Bill =-
--
You can tell a lot about a man by the way he handles these three
things: a rainy day, lost luggage, and tangled Christmas tree lights.
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron -- Baron Schwartz http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Baron Schwartz wrote: Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron I have table products (product_id is PK). I have table categories (cat_id is PK). since the product can be in more than one category, I have prod_cat table: create prod_cat( cat_id int(8) unsigned not null, prod_id int(8) unsigned not null, order_id int(4) unsigned null, PRIMARY KEY (cat_id, prod_id) ) engine=Innodb; would it be enough info? thanks. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Hi, Afan Pasalic wrote: Baron Schwartz wrote: Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron I have table products (product_id is PK). I have table categories (cat_id is PK). since the product can be in more than one category, I have prod_cat table: create prod_cat( cat_id int(8) unsigned not null, prod_id int(8) unsigned not null, order_id int(4) unsigned null, PRIMARY KEY (cat_id, prod_id) ) engine=Innodb; Okay, so your order_id is really sort order, not id of the customer's request to buy something. (As an aside, perhaps sort_order would confuse you less in the future when you don't remember the column's purpose anymore). It probably makes sense to index the column if you want to use it for sorting. You could also just order by the primary key. But I understand there are times when you want to have a different ordering. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: indexing order column
Baron Schwartz wrote: Hi, Afan Pasalic wrote: Baron Schwartz wrote: Hi Afan, Afan Pasalic wrote: hi, if I have column order_id(int(4)) null do I have to index it too. I'm going to use it ONLY for sorting records. It depends a lot on how much data is in the table, etc etc. An index will make sorting more efficient in the general case when you have a decent amount of data. But it's hard to be specific with so little information. Baron I have table products (product_id is PK). I have table categories (cat_id is PK). since the product can be in more than one category, I have prod_cat table: create prod_cat( cat_id int(8) unsigned not null, prod_id int(8) unsigned not null, order_id int(4) unsigned null, PRIMARY KEY (cat_id, prod_id) ) engine=Innodb; Okay, so your order_id is really sort order, not id of the customer's request to buy something. (As an aside, perhaps sort_order would confuse you less in the future when you don't remember the column's purpose anymore). It probably makes sense to index the column if you want to use it for sorting. You could also just order by the primary key. But I understand there are times when you want to have a different ordering. Baron yes, you're right. sort_order does make more sense :) order by PK, in my case is, let's say impossible because I'm sorting products in ONE category. e.g.: cat_id|prod_id|order_id 1 | 23 | 1 1 | 25 | 2 1 | 36 | 3 1 | 13 | 4 2 | 13 | 1 2 | 45 | 2 2 | 47 | 3 2 | 51 | 4 3 | 32 | 1 3 | 33 | 2 3 | 34 | 3 3 | 35 | 4 -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
-Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Daevid Vincent wrote: I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. I'm doing this in PHP and mySQL (of course), so if it can't be done with a single query, I can split it up. Here's the challenge, given a text field search box, someone enters: Sony 20 TV How do I search for that, not knowing which fields are which? For example, they could have also entered: 20 Sony TV I think you're describing full-text indexing. MySQL supports it but only on MyISAM tables. If you don't want to use MyISAM, full-text search engines like Lucene or Sphinx may be worth looking at. I don't think I am. While full-text indexing might help since the indexes would be faster. I think this is a logic issue. The full-text index would be useful on a TEXT or BLOB or some long varchar field, but it doesn't solve that I'm trying to pull from two different tables, Product and Company and mapping the free-form string to fields that could be one of several. I think my first attempt is close, but it's something to do with all the AND and OR combinations that's not right. My version gives many results because it matches (SONY OR TV OR 20). I need it to match (SONY AND TV AND 20) But this isn't it either (returns 0 results) because some fields, like the categories.name, products.upc and products.model don't match so the entire condition fails. SELECT products.*, companies.name AS company_name, categories.name AS category_name FROM products LEFT JOIN companies ON company_id = companies.id LEFT JOIN categories ON category_id = categories.id WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%') AND (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') AND (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') AND (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') AND (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%') ) ORDER BY categories.name DESC, products.name ASC, companies.name ASC; Also, the 'SONY' is really the companies.name, while the '20' _AND_ 'TV' together form '20 TV' to make the products.name. +--+--+--+--+ | name | model| upc | company_name | +--+--+--+--+ | 20 TV | STV20-KVR-HD | 097855008633 | Sony | | 36 TV | STV36-KVR-HD | 087452047023 | Sony | +--+--+--+--+ One way might be to do three separate queries, one for each word. Then store them in an array and compare the overlaps, removing any that aren't shared. Then a final query where product.id IN(array) That seems extremely inefficient and hackish though. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I find products when a user types freeform strings like 'Sony 20 TV' or '20 Sony TV'?
Hi Daevid, Daevid Vincent wrote: -Original Message- From: Baron Schwartz [mailto:[EMAIL PROTECTED] Daevid Vincent wrote: I'm having trouble figuring out the logic/query I want. I know that all those ORs are not right. I'm doing this in PHP and mySQL (of course), so if it can't be done with a single query, I can split it up. Here's the challenge, given a text field search box, someone enters: Sony 20 TV How do I search for that, not knowing which fields are which? For example, they could have also entered: 20 Sony TV I think you're describing full-text indexing. MySQL supports it but only on MyISAM tables. If you don't want to use MyISAM, full-text search engines like Lucene or Sphinx may be worth looking at. I don't think I am. While full-text indexing might help since the indexes would be faster. I think this is a logic issue. The full-text index would be useful on a TEXT or BLOB or some long varchar field, but it doesn't solve that I'm trying to pull from two different tables, Product and Company and mapping the free-form string to fields that could be one of several. I think my first attempt is close, but it's something to do with all the AND and OR combinations that's not right. My version gives many results because it matches (SONY OR TV OR 20). I need it to match (SONY AND TV AND 20) But this isn't it either (returns 0 results) because some fields, like the categories.name, products.upc and products.model don't match so the entire condition fails. SELECT products.*, companies.name AS company_name, categories.name AS category_name FROM products LEFT JOIN companies ON company_id = companies.id LEFT JOIN categories ON category_id = categories.id WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' OR products.model LIKE '20%' OR products.model LIKE 'tv%') AND (products.upc LIKE 'sony' OR products.upc LIKE '20' OR products.upc LIKE 'tv') AND (products.name LIKE '%sony%' OR products.name LIKE '20%' OR products.name LIKE '%tv%') AND (companies.name LIKE 'sony%' OR companies.name LIKE '20%' OR companies.name LIKE 'tv%') AND (categories.name LIKE '%sony%' OR categories.name LIKE '20%' OR categories.name LIKE '%tv%') ) ORDER BY categories.name DESC, products.name ASC, companies.name ASC; Also, the 'SONY' is really the companies.name, while the '20' _AND_ 'TV' together form '20 TV' to make the products.name. +--+--+--+--+ | name | model| upc | company_name | +--+--+--+--+ | 20 TV | STV20-KVR-HD | 097855008633 | Sony | | 36 TV | STV36-KVR-HD | 087452047023 | Sony | +--+--+--+--+ One way might be to do three separate queries, one for each word. Then store them in an array and compare the overlaps, removing any that aren't shared. Then a final query where product.id IN(array) That seems extremely inefficient and hackish though. I misunderstood what you meant at first. So, you want rows where all words appear in the row, rather than where all words appear in any one column. How about this: every time you get a match on a term in any column, count it as 1. Then add all these and compare to the number of words in your search input, which you can determine either in SQL with a little text wrangling, or probably more easily in whatever the client code is with a split() or similar. SELECT products.*, companies.name AS company_name, categories.name AS category_name FROM products LEFT JOIN companies ON company_id = companies.id LEFT JOIN categories ON category_id = categories.id WHERE products.enabled = 1 AND( (products.model LIKE 'sony%' + products.model LIKE '20%' products.model LIKE 'tv%') + (products.upc LIKE 'sony' + products.upc LIKE '20' + products.upc LIKE 'tv') + (products.name LIKE '%sony%' + products.name LIKE '20%' + products.name LIKE '%tv%') + (companies.name LIKE 'sony%' + companies.name LIKE '20%' + companies.name LIKE 'tv%') + (categories.name LIKE '%sony%' + categories.name LIKE '20%' + categories.name LIKE '%tv%') ) = [$number_of_words_in_input] ORDER BY categories.name DESC, products.name ASC, companies.name ASC; This will be ugly and inefficient though. It might be better to build and maintain a separate table with the concatenation of all the fields, and fulltext index that. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
slave replication fails, cannot create user
I'm transitioning from one mysql server to another, in production use, by setting the new server up as a slave, so that when it's time to switch, we'll have very little downtime. Then we can turn off replication and the new slave will become the new production server. I set up a replication slave, tested it, and everything was fine. Then I stopped it, reset slave, and used it for something else. Now I want to make it a slave again, but it stops partway through catching up on the binary logs, with this error: Operation CREATE USER failed for 'replication'@'[host]' (where [host] is the slave's hostname) Here is the procedure I followed to make it a slave: 1. drop database our_main_db; create database our_main_db; 2. Brought over the most recent mysqldump from the master, which was created from a command like this: mysqldump --flush-logs --master-data=2 -uuser -ppasswd our_main_db ... and fed the dump's contents into the slave-to-be server. 3. Using the log file name and position from the master data in that dump file, issed a change master statement: CHANGE MASTER TO MASTER_HOST='masterserver.domain.name', MASTER_USER='replication', MASTER_PASSWORD='replicationuserpassword', MASTER_LOG_FILE='binlog.11', MASTER_LOG_POS=98; START SLAVE; ... everything was running fine for an hour or so, and the slave caught up through several days worth of logs from the master, but then it stopped with this error: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'replication'@'[host]'' on query. Default database: 'mysql'. Query: 'create user 'replication'@'[host]' identified by 'replicationuserpassword'' (again, [host] is the slave's own hostname). I checked the mysql.user table and found that the [EMAIL PROTECTED] user *did* exist. So I removed it from the table, then tried to restart replication ... and got the same error. So then I went to the binary log on the master and, using mysqlbinlog, found the exact create user statement, and tried to run it by hand on the slave, which looked like this: create user 'replication'@'[host]' identified by 'replicationuserpasswd'; I tried running that by hand on the slave server (from the mysql root user) and got the error again: mysql create user 'replication'@'[host]' identified by 'replicationuserpasswd'; ERROR 1396 (HY000): Operation CREATE USER failed for 'replication'@'[host]' ... I solved the problem by adding slave_skip_errors=1396 to my.cnf and restarting the slave server. It was able to pick up replication and is now caught up with the master and seems to be fine. However, 1. I don't understand what caused the problem 2. I fear that after I un-slave it (we're planning to switch masters) I still won't be able to create users on this new server. -- Cos (Ofer Inbar) -- [EMAIL PROTECTED] So you're one of Jehovah's Witnesses. I'm Cthulhu's defence lawyer. prepare for cross-questioning -- Tanuki, on alt.sysadmin.recovery -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Hi, Ofer Inbar wrote: I'm transitioning from one mysql server to another, in production use, by setting the new server up as a slave, so that when it's time to switch, we'll have very little downtime. Then we can turn off replication and the new slave will become the new production server. I set up a replication slave, tested it, and everything was fine. Then I stopped it, reset slave, and used it for something else. Now I want to make it a slave again, but it stops partway through catching up on the binary logs, with this error: Operation CREATE USER failed for 'replication'@'[host]' (where [host] is the slave's hostname) Here is the procedure I followed to make it a slave: 1. drop database our_main_db; create database our_main_db; 2. Brought over the most recent mysqldump from the master, which was created from a command like this: mysqldump --flush-logs --master-data=2 -uuser -ppasswd our_main_db ... and fed the dump's contents into the slave-to-be server. 3. Using the log file name and position from the master data in that dump file, issed a change master statement: CHANGE MASTER TO MASTER_HOST='masterserver.domain.name', MASTER_USER='replication', MASTER_PASSWORD='replicationuserpassword', MASTER_LOG_FILE='binlog.11', MASTER_LOG_POS=98; START SLAVE; ... everything was running fine for an hour or so, and the slave caught up through several days worth of logs from the master, but then it stopped with this error: Last_Errno: 1396 Last_Error: Error 'Operation CREATE USER failed for 'replication'@'[host]'' on query. Default database: 'mysql'. Query: 'create user 'replication'@'[host]' identified by 'replicationuserpassword'' (again, [host] is the slave's own hostname). I checked the mysql.user table and found that the [EMAIL PROTECTED] user *did* exist. So I removed it from the table, then tried to restart replication ... and got the same error. So then I went to the binary log on the master and, using mysqlbinlog, found the exact create user statement, and tried to run it by hand on the slave, which looked like this: create user 'replication'@'[host]' identified by 'replicationuserpasswd'; I tried running that by hand on the slave server (from the mysql root user) and got the error again: mysql create user 'replication'@'[host]' identified by 'replicationuserpasswd'; ERROR 1396 (HY000): Operation CREATE USER failed for 'replication'@'[host]' ... I solved the problem by adding slave_skip_errors=1396 to my.cnf and restarting the slave server. It was able to pick up replication and is now caught up with the master and seems to be fine. However, 1. I don't understand what caused the problem 2. I fear that after I un-slave it (we're planning to switch masters) I still won't be able to create users on this new server. What version of MySQL are you running on each machine? The statement might have failed because the user already existed, or because the slave's version was pre-5.0.3... just a guess. Otherwise, I guess I'm an old-timer, because I always use GRANT to create users, so I don't know a lot about any subtleties or problems there might be with CREATE USER :-) Have you checked the server's error log to see if there's more information there? Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Baron Schwartz [EMAIL PROTECTED] wrote: What version of MySQL are you running on each machine? Sorry, I should've included this information. Both of them are running 5.0.24, installed from exactly the same .rpm file. I wanted to avoid any issues related to different MySQL versions during this transition. The statement might have failed because the user already existed, You can see that was my first guess too. That's why I tried removing the user from mysql.user and starting the slave threads again, as I described. Unless it was trying to create the same user twice, that should've fixed it (if this was the cause), but it didn't seem to. -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave replication fails, cannot create user
Ofer Inbar wrote: Baron Schwartz [EMAIL PROTECTED] wrote: What version of MySQL are you running on each machine? Sorry, I should've included this information. Both of them are running 5.0.24, installed from exactly the same .rpm file. I wanted to avoid any issues related to different MySQL versions during this transition. The statement might have failed because the user already existed, You can see that was my first guess too. That's why I tried removing the user from mysql.user and starting the slave threads again, as I described. Unless it was trying to create the same user twice, that should've fixed it (if this was the cause), but it didn't seem to. Have you tried creating another user, which you know does not exist and never has? Removing the user from mysql.user table with DELETE is not a really clean way to do it, if that's what you did. You are better off using DROP USER. Even after you removed the user, unless you ran FLUSH PRIVILEGES, it was still cached in memory. The built-in commands like DROP USER, GRANT, REVOKE etc will flush the in-memory caches. The user and privilege data is cached in memory because it is accessed constantly and must be very fast. I don't know if this would have caused the problem, but I'm curious to see if you can create a different user. Regards Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
