Position in master.info: read or executed master_log_pos?
Hi, for recovery purposes I need to know what exactly is in the master.info file. Especially the log position. Is it Read_Master_Log_Pos or Exec_Master_Log_Pos? Another question: Does stop slave; only stop reading the log from master or does it also stop executing the log that has been read already, but not yet executed? Regards Dominik -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
duration query: how to ?
Hi, My clients can put their articles on my website. They pay for the real utilisation of the site. If a client has used 2 slots for all year, then he pays for them. Now my articles table has a starddate (when the client has started to sell his product) and an enddate (when the article is sold). I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days How to do so with a query ? fields are client.idclient, article.idclient, article.idarticle, article.startdate, article.enddate. Please help me, I don't know how to retrieve those values and I need them to get payed. Bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
viewing data using MySQL Administrator tool
Hello using MySQL administrator tool, I can view databases under the catalog folder, but I can't view the data filled in the tables, when doing right click table -- Edit table Data , nothing shows up Thanks for your help hicham -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: viewing data using MySQL Administrator tool
When I do this, the query browser pops up with a select * from table query which is executed on my behalf revealing my data. All I have to do is press the edit button (assuming the result set is editable, of course.) Can you be more specific about 'nothing shows up'? Tim -Original Message- From: hicham [mailto:[EMAIL PROTECTED] Sent: Thursday, May 18, 2006 6:30 AM To: mysql@lists.mysql.com Subject: viewing data using MySQL Administrator tool Hello using MySQL administrator tool, I can view databases under the catalog folder, but I can't view the data filled in the tables, when doing right click table -- Edit table Data , nothing shows up Thanks for your help hicham -- 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: duration query: how to ?
Bedford Bob schrieb: Hi, My clients can put their articles on my website. They pay for the real utilisation of the site. If a client has used 2 slots for all year, then he pays for them. Now my articles table has a starddate (when the client has started to sell his product) and an enddate (when the article is sold). I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days How to do so with a query ? fields are client.idclient, article.idclient, article.idarticle, article.startdate, article.enddate. Please help me, I don't know how to retrieve those values and I need them to get payed. Bob Use the DIF functions. What version of MySQL do you have? An other way would be to convert the Dates into UNIX STAMPS that way you could subtract them and you have then the seconds between each date. I don't think i have to tell you how to go on then. Greets Barry -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
Mike Blezien wrote: Hello, this is a continued problem we are having from a earlier posting to the list regarding a query. We need to calculate the SUM of the column 'agent_product_time' which is a TIME datatype column and according to the manual: http://dev.mysql.com/doc/refman/4.1/en/date-and-time-type-overview.html this is the way to SUM the total time, which keeps producing a syntax error and figure out why MySQL version 4.1.12 --- SELECT c.account_id,a.name,a.company, SEC_TO_TIME(SUM(TIME_TO_SEC(c.agent_product_time))) AS mins FROM account a LEFT JOIN calls c ON c.account_id = a.id WHERE c.calldate = DATE_SUB(NOW(),INTERVAL 14 DAY) AND c.agent_id = 2 GROUP BY c.account_id HAVING mins = '500' ORDER BY mins ERROR: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '( SUM( TIME_TO_SEC( c . agent_product_time ) ) ) AS mins FROM account a LEFT JO' at line 1 -- What would be producing the syntax error here.?? Something is strange here. The piece of your query quoted in the syntax error does not match the query you gave us. That makes me think you've given us an edited version of your query. It's hard to catch a syntax error if you don't give us the actual query. The piece of the query quoted in the error has a lot of extraneous spaces. If I had to guess, I'd bet that there is a space between SEC_TO_TIME and the opening parenthesis in your real query. That is, you have SEC_TO_TIME ( SUM... instead of SEC_TO_TIME(SUM... The parser distinguishes functions from columns by the presence of a parenthesis attached to the function name. For example: mysql SELECT VERSION(); +---+ | VERSION() | +---+ | 4.1.15| +---+ 1 row in set (0.00 sec) but mysql SELECT VERSION (); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1 Note that the error message quotes the query starting with the opening parenthesis, as is the case for you. If that isn't it, please copy and paste your actual query into your next message. I'm sure someone will spot the problem. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Baffled by query error syntax
sheeri kritzer wrote: snip MySQL usually gives a syntax error *where* the error happens. In this case, it would indicate a problem with SEC_TO_TIME( but there shouldn't be a problem, both according to the manual AND according to my example. The parser reads the query left-to-right and always quotes the first thing it doesn't understand. As often as not, that's the first thing *after* the actual error. Here's a simple example: SELECT version (); SELECT is proper, of course. Next comes version. It doesn't have a parenthesis attached, so it must be a column name. Since version is a column, it should be followed by a comma, an alias, the word AS, or some operator. In that context, the ( that comes next doesn't make sense, so that is what mysql tells you: mysql SELECT VERSION (); ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '()' at line 1 The actual error, though, is the space right before the quoted part of the query. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duration query: how to ?
Bob I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). In a valid-time app like yours, it is usually more sound to mark current with a far-in-the-future date than with NULL. Arguably, using NULL to denote current misuses NULL. NULLs slow query performance.Queries are more straightforward if you denote current as ending on the largest date possible. For MySQL, that would be -12-31. Duration in years between a startdate and NOW(), averaging leap years, is DATEDIFF( NOW(), startdate)/365.2422. To account for leap years exactly in such a formula requires a UDF, a stored function, or a calendar table. PB - Hi, My clients can put their articles on my website. They pay for the real utilisation of the site. If a client has used 2 slots for all year, then he pays for them. Now my articles table has a starddate (when the client has started to sell his product) and an enddate (when the article is sold). I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days How to do so with a query ? fields are client.idclient, article.idclient, article.idarticle, article.startdate, article.enddate. Please help me, I don't know how to retrieve those values and I need them to get payed. Bob -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 5/16/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duration query: how to ?
Bedford Bob wrote: I'd like to retrieve the total amount of time the articles have been in the site from 2005-01-01 to 2005-01-31 and divide them by 365 (or 366 if leap year). if an article has been online from 2004-10-01 to null (meaning the article hasn't been sold yet) then I may count 365 days Did you mean from 2005-01-01 to 2005-12-31? --J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlcheck
Does mysqlcheck sometimes repair problems that it does not report? I've seen a few instances where running mysqlcheck -r -f fixed application problems even though it reported all tables OK. --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Recursive query
Hi, This is an example of an table that I store categories in. Each category have a id and also a parent. If the parent_id is 0 that category is a super-category. Assume that I would like to get a list of all categories and its parent category, grandparents category etc down to super-category. For example, if I take category id 13 as an example: 13 should be chosen; because that is the one I am working with 9 should be chosen; because it is the parent to 13 6 should be chosen; because it is the parent to 9 1 should be chosen; because it is the parent to 6 And that is it, because 1 has parent 0, and is therefore a super-category. Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? ++---+--+---+ | id | name | priority | parent_id | ++---+--+---+ | 1 | DME | 999 | 0 | | 2 | Training Material | 999 | 0 | | 3 | RND | 999 | 0 | | 4 | LEAP | 999 | 1 | | 5 | TDI | 999 | 1 | | 6 | Technical Support | 999 | 1 | | 7 | Training Module | 999 | 1 | | 8 | Detail by Component | 999 | 6 | | 9 | Step/Process | 999 | 6 | | 10 | Assessment| 999 | 9 | | 11 | Design| 999 | 9 | | 12 | Implement and Monitor | 999 | 9 | | 13 | Evaluation| 999 | 9 | | 14 | Reflection| 999 | 9 | | 15 | Transition| 999 | 9 | | 16 | TDI | 999 | 2 | | 17 | LEAP | 999 | 2 | | 18 | Other | 999 | 2 | | 19 | Tools | 999 | 3 | | 20 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
trigger mysql 5
Hi all! server version: 5.0.18 I attempted to create a trigger with statement and update, it does like this: CREATE TRIGGER update_trans AFTER UPDATE ON main_trans FOR EACH ROW BEGIN IF OLD.trans_status='INITIAL' and OLD.trans_state='INITIAL' THEN update trans set trans_status=NEW.trans_status, trans_state=NEW.trans_state where main_trans_id=NEW.main_trans_id; END IF; END; I 'm getting an error after : update trans set ... I tried to insert BEGIN update trans set .. END Note that I'm creating the trigger directly in phpmyadmin, I already created succesfully a trigger but it was really a simple one... I did try the delimiter $$ or any other delimiter of my choice, but it doesn't accept the DELIMITER declaration before my create trigger Nothing works Any tips!? Is that possible to do this kind of trigger!? Tks for your answers, I appreciate!!
Re: Handler and flush tables with read lock
Well, it's documented here: http://dev.mysql.com/doc/internals/en/flush-tables.html Every time a thread releases a table, it checks if the refresh version of the table (updated at open) is the same as the current refresh_version. If not, it will close it and broadcast a signal on COND_refresh (to await any thread that is waiting for all instances of a table to be closed). -Sheeri On 5/17/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi, If you open a table using, HANDLER tablename OPEN; and then (in another MySQL command windows), execute, flush tables with read lock; The flush tables hangs until you execute a HANDLER tablename CLOSE; command. Is this a bug? Can anyone explain this? Many Regards, Ian Collins. -- 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]
Got a packet bigger than 'max_allowed_packet' bytes
Hi - I'm trying to import some data into a MySQL database, which was dumped from mysqldump. Just looking at the .sql file generated, there's only about 7000 lines, on an 800M dump. I guess this tells me I have some extremely long lines, which would be happy to take up more than 'max_allowed_packet' bytes, which is set to 16M. The specific error I get is: ERROR 1153 (08S01) at line 3132: Got a packet bigger than 'max_allowed_packet' bytes Using: mysql -uroot -p database /tmp/800MSQLFile.sql under MySQL 5.0.21. I don't have much experience importing data in this manner, so maybe I'm missing something here. If anyone wouldn't mind sharing some tips on how to do this, I would grealy appreciate it. Should I increase max_allowed_packet even further? Is there a way to make this value adaptive, as to avoid error? Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Recursive query
After deciding a max level, you can self join the table max level time. Also, you can add two columns, one int column as a hashcode, and one int column as showing current level. And by writing a simple program, you can update the hashcode field. You can think bitwise. Just reserve n bits for each level. Increment the level values by one as you traverse the tree. That way you can algo gain the flexibility to select one branch. (Calculate min and max hashcode values and select from table where hashcode column between the min and max calculated values. HTH, Gokhan -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Thursday, May 18, 2006 8:20 PM To: mysql@lists.mysql.com Subject: Recursive query Hi, This is an example of an table that I store categories in. Each category have a id and also a parent. If the parent_id is 0 that category is a super-category. Assume that I would like to get a list of all categories and its parent category, grandparents category etc down to super-category. For example, if I take category id 13 as an example: 13 should be chosen; because that is the one I am working with 9 should be chosen; because it is the parent to 13 6 should be chosen; because it is the parent to 9 1 should be chosen; because it is the parent to 6 And that is it, because 1 has parent 0, and is therefore a super-category. Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? ++---+--+---+ | id | name | priority | parent_id | ++---+--+---+ | 1 | DME | 999 | 0 | | 2 | Training Material | 999 | 0 | | 3 | RND | 999 | 0 | | 4 | LEAP | 999 | 1 | | 5 | TDI | 999 | 1 | | 6 | Technical Support | 999 | 1 | | 7 | Training Module | 999 | 1 | | 8 | Detail by Component | 999 | 6 | | 9 | Step/Process | 999 | 6 | | 10 | Assessment| 999 | 9 | | 11 | Design| 999 | 9 | | 12 | Implement and Monitor | 999 | 9 | | 13 | Evaluation| 999 | 9 | | 14 | Reflection| 999 | 9 | | 15 | Transition| 999 | 9 | | 16 | TDI | 999 | 2 | | 17 | LEAP | 999 | 2 | | 18 | Other | 999 | 2 | | 19 | Tools | 999 | 3 | | 20 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- 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: Recursive query
Peter, Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? SQL is not recursive, so you need an sproc. There are some examples with discussion at http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html. PB - Peter Lauri wrote: Hi, This is an example of an table that I store categories in. Each category have a id and also a parent. If the parent_id is 0 that category is a super-category. Assume that I would like to get a list of all categories and its parent category, grandparents category etc down to super-category. For example, if I take category id 13 as an example: 13 should be chosen; because that is the one I am working with 9 should be chosen; because it is the parent to 13 6 should be chosen; because it is the parent to 9 1 should be chosen; because it is the parent to 6 And that is it, because 1 has parent 0, and is therefore a super-category. Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? ++---+--+---+ | id | name | priority | parent_id | ++---+--+---+ | 1 | DME | 999 | 0 | | 2 | Training Material | 999 | 0 | | 3 | RND | 999 | 0 | | 4 | LEAP | 999 | 1 | | 5 | TDI | 999 | 1 | | 6 | Technical Support | 999 | 1 | | 7 | Training Module | 999 | 1 | | 8 | Detail by Component | 999 | 6 | | 9 | Step/Process | 999 | 6 | | 10 | Assessment| 999 | 9 | | 11 | Design| 999 | 9 | | 12 | Implement and Monitor | 999 | 9 | | 13 | Evaluation| 999 | 9 | | 14 | Reflection| 999 | 9 | | 15 | Transition| 999 | 9 | | 16 | TDI | 999 | 2 | | 17 | LEAP | 999 | 2 | | 18 | Other | 999 | 2 | | 19 | Tools | 999 | 3 | | 20 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/341 - Release Date: 5/16/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got a packet bigger than 'max_allowed_packet' bytes
On 5/18/06, Dan Trainor [EMAIL PROTECTED] wrote: Hi - I'm trying to import some data into a MySQL database, which was dumped from mysqldump. Just looking at the .sql file generated, there's only about 7000 lines, on an 800M dump. I guess this tells me I have some extremely long lines, which would be happy to take up more than 'max_allowed_packet' bytes, which is set to 16M. The specific error I get is: ERROR 1153 (08S01) at line 3132: Got a packet bigger than 'max_allowed_packet' bytes Using: mysql -uroot -p database /tmp/800MSQLFile.sql under MySQL 5.0.21. I don't have much experience importing data in this manner, so maybe I'm missing something here. If anyone wouldn't mind sharing some tips on how to do this, I would grealy appreciate it. Should I increase max_allowed_packet even further? Is there a way to make this value adaptive, as to avoid error? Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Check this http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got a packet bigger than 'max_allowed_packet' bytes
Kishore Jalleda wrote: On 5/18/06, Dan Trainor [EMAIL PROTECTED] wrote: Hi - I'm trying to import some data into a MySQL database, which was dumped from mysqldump. Just looking at the .sql file generated, there's only about 7000 lines, on an 800M dump. I guess this tells me I have some extremely long lines, which would be happy to take up more than 'max_allowed_packet' bytes, which is set to 16M. The specific error I get is: ERROR 1153 (08S01) at line 3132: Got a packet bigger than 'max_allowed_packet' bytes Using: mysql -uroot -p database /tmp/800MSQLFile.sql under MySQL 5.0.21. I don't have much experience importing data in this manner, so maybe I'm missing something here. If anyone wouldn't mind sharing some tips on how to do this, I would grealy appreciate it. Should I increase max_allowed_packet even further? Is there a way to make this value adaptive, as to avoid error? Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Check this http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Kishore Jalleda Hi - Yes, actually, a few mins after I posted, I found the user contrib notes to be very helpful. For the record, I found the answer there. Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Got a packet bigger than 'max_allowed_packet' bytes
On 5/18/06, Kishore Jalleda [EMAIL PROTECTED] wrote: On 5/18/06, Dan Trainor [EMAIL PROTECTED] wrote: Hi - I'm trying to import some data into a MySQL database, which was dumped from mysqldump. Just looking at the .sql file generated, there's only about 7000 lines, on an 800M dump. I guess this tells me I have some extremely long lines, which would be happy to take up more than 'max_allowed_packet' bytes, which is set to 16M. The specific error I get is: ERROR 1153 (08S01) at line 3132: Got a packet bigger than 'max_allowed_packet' bytes Using: mysql -uroot -p database /tmp/800MSQLFile.sql under MySQL 5.0.21. I don't have much experience importing data in this manner, so maybe I'm missing something here. If anyone wouldn't mind sharing some tips on how to do this, I would grealy appreciate it. Should I increase max_allowed_packet even further? Is there a way to make this value adaptive, as to avoid error? Thanks! -dant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Check this http://dev.mysql.com/doc/refman/5.0/en/packet-too-large.html Kishore Jalleda Just to add a little note, pay special attention to the lines which say --Both the client and the server have their own max_allowed_packet variable, so if you want to handle big packets, you must increase this variable both in the client and in the server. --If you are using the mysql client program, its default max_allowed_packet variable is 16MB. --To set a larger value, start mysql like this: --shell mysql --max_allowed_packet=32M --That sets the packet size to 32MB. So thats the catch here... Kishore Jalleda http://kjalleda.googlepages.com/mysqlprojects -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
insert html into database
Hi everyone... how do I enter html code into my database???
Re: insert html into database
Hi steve, you just have to double quotes your insert! On 5/18/06, Steve [EMAIL PROTECTED] wrote: Hi everyone... how do I enter html code into my database???
RE: Recursive query = Nested Set
I think you might consider refactoring your code to use a hybrid of Nested Sets and Adjacency List. It's fairly trivial to add a 'parent_id' to the nested set, so you really don't loose any of your existing schema structure, but it will be much faster to traverse a tree, and no recursion is required... http://dev.mysql.com/tech-resources/articles/hierarchical-data.html Do a google search for mysql nested set and you will find more. Here are some recommend: http://dev.mysql.com/tech-resources/articles/hierarchical-data.html http://www.sitepoint.com/article/hierarchical-data-database http://www.zend.com/zend/tut/tutorial-ferrara2.php?article=tutorial-ferrara2 id=3453open=1anc=0view=1 http://simon.incutio.com/archive/2003/06/19/storingTrees http://istherelifeafter.com/joecelko.html http://www.codeproject.com/cs/database/Trees_in_SQL_databases.asp http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html You had me at EHLO --E.Webb (10.04.05) -Original Message- From: Gokhan Demir [mailto:[EMAIL PROTECTED] Sent: Thursday, May 18, 2006 12:11 PM To: mysql@lists.mysql.com Subject: RE: Recursive query After deciding a max level, you can self join the table max level time. Also, you can add two columns, one int column as a hashcode, and one int column as showing current level. And by writing a simple program, you can update the hashcode field. You can think bitwise. Just reserve n bits for each level. Increment the level values by one as you traverse the tree. That way you can algo gain the flexibility to select one branch. (Calculate min and max hashcode values and select from table where hashcode column between the min and max calculated values. HTH, Gokhan -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Thursday, May 18, 2006 8:20 PM To: mysql@lists.mysql.com Subject: Recursive query Hi, This is an example of an table that I store categories in. Each category have a id and also a parent. If the parent_id is 0 that category is a super-category. Assume that I would like to get a list of all categories and its parent category, grandparents category etc down to super-category. For example, if I take category id 13 as an example: 13 should be chosen; because that is the one I am working with 9 should be chosen; because it is the parent to 13 6 should be chosen; because it is the parent to 9 1 should be chosen; because it is the parent to 6 And that is it, because 1 has parent 0, and is therefore a super-category. Right now I am doing this with PHP and a recursive function, but is it possible to do this directly with one query? ++---+--+---+ | id | name | priority | parent_id | ++---+--+---+ | 1 | DME | 999 | 0 | | 2 | Training Material | 999 | 0 | | 3 | RND | 999 | 0 | | 4 | LEAP | 999 | 1 | | 5 | TDI | 999 | 1 | | 6 | Technical Support | 999 | 1 | | 7 | Training Module | 999 | 1 | | 8 | Detail by Component | 999 | 6 | | 9 | Step/Process | 999 | 6 | | 10 | Assessment| 999 | 9 | | 11 | Design| 999 | 9 | | 12 | Implement and Monitor | 999 | 9 | | 13 | Evaluation| 999 | 9 | | 14 | Reflection| 999 | 9 | | 15 | Transition| 999 | 9 | | 16 | TDI | 999 | 2 | | 17 | LEAP | 999 | 2 | | 18 | Other | 999 | 2 | | 19 | Tools | 999 | 3 | | 20 | RD Document | 999 | 3 | ++---+--+---+ Best regards, Peter Lauri -- 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: insert html into database
At 15:55 -0500 5/18/06, Steve wrote: Hi everyone... how do I enter html code into my database??? Like any other string. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how to default column value to lower( )
I have column and I want to make sure the db is always making sure the value that gets input into this VARCHAR() column is always lowercase; Is there a way to set the value of a column within a table to automatically be lowercase. I know how to use the LOWER() function when performing queries but is there a way to define LOWER( ) within the definition of the table column itself without having the application specify LOWER( ) to any value passed to this column or if a record had to be manually input and the admin forgot to make sure all the characters were lowercase. Ferindo
Re: can not read from pipe
Hi Baynaa, I am trying to install syslog-ng + phpsyslogng ... It pipes all the log messages to a pipe file mysql.pipe in sql statement format. But now the problem is mysql can not read from the pipe file. When I give the command: $ mysql -u sysloguser -p syslog mysql.pipe Does the mysql command return, or hang? If it returns: You could test mysql reading from a pipe in a more controlled manner: shell mkfifo sql.fifo shell echo INSERT INTO whatever_table ... sql.fifo and from another shell run your original command but reading from the new sql.fifo . If it hangs: Sounds like there's nothing to read from the pipe. What does 'cat mysql.pipe' print? HTH, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
than you for your answer but...
thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. Database Selected: candlecatalog Query: insert into Products (ProdID,ProdName,ProdType,ProdDesc,Price,Pic,Buy) Values (VOALM,Almond Votive,Votive,Our Almond scented Candles are melon colored,1.25,,a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.themelt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/cart.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html;img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/images/bu y_button_f.gif border=0 / /a) Results Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_hhttp://www.ccnow .com/cgi-local/cart.cgi?meltingpot_VOALM_h the code i want to insert is: a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.themelt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/cart.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html;img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/images/bu y_button_f.gif border=0 / /a -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/342 - Release Date: 5/17/2006
Re: than you for your answer but...
Steve wrote: thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. PHP has a function called 'mysql_real_escape_string' this function will add the needed escape characters to allow the insert to work. The APIs for other languages should all have the same function. * * -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: than you for your answer but...
I'm a very beginner and trying to get my way into sql with... One told me to double quote the html. Now, it's said the php has a mysql_real_escape_string so... What the hell is this and how do I make that work??? And where is the radio to call MAYDAY Or SOS??? Steve wrote: thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. PHP has a function called 'mysql_real_escape_string' this function will add the needed escape characters to allow the insert to work. The APIs for other languages should all have the same function. * * -- Chris W KE5GIX Gift Giving Made Easy Get the gifts you want give the gifts they want One stop wish list for any gift, from anywhere, for any occasion! http://thewishzone.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.6.0/342 - Release Date: 5/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. Quentin -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advice on replication
Hello everybody!! I've a problem synchronizing a master mysql to a slave: When my master make a rotate in binlog my slave can follow up it and catch. What should I do ? -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Abra sua conta no Yahoo! Mail: 1GB de espaço, alertas de e-mail no celular e anti-spam realmente eficaz. http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: than you for your answer but...
Steve wrote: thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. Database Selected: candlecatalog Query: insert into Products (ProdID,ProdName,ProdType,ProdDesc,Price,Pic,Buy) Values (VOALM,Almond Votive,Votive,Our Almond scented Candles are melon colored,1.25,,a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.themelt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/cart.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html;img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/images/bu y_button_f.gif border=0 / /a) Results Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_hhttp://www.ccnow .com/cgi-local/cart.cgi?meltingpot_VOALM_h Your problem is not with MySQL but with the programming language you're using. It probably has a facility to do this. PHP, as another poster has noted, has the function mysql_real_escape_string(). But you are not using PHP. What language are you using? Do you have a manual for it? Does it have a section on interfacing with MySQL? On escaping quotes? the code i want to insert is: a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.themelt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/cart.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html;img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/images/bu y_button_f.gif border=0 / /a Since you surround this with double quotes and then include more double quotes inside it, perhaps you are not a native speaker of a language that uses for quotes. In the ASCII character set (and on most keyboards), the beginning quotation mark and ending quotation mark are identical. This makes for difficulties when trying to enclose quotation marks within the larger quote. You can use the single quote ' inside double quotes or you can escape the quotes (by preceding them with an escape character: the backslash character in the case of MySQL). Hope that helps. --J PS Try to keep your messages in the same thread by replying to another message in that thread and not changing the subject. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: than you for your answer but...
There is no reason why you can't use single quotes around the HTML. You don't appear to have any single quotes with the HTML so this should work fine. Everything, including the double quotes, should be then stored in your database. Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +618 8408 4273 _/ _/ _/_/_/ Mobile: 0417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: John Hicks [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 10:03 AM To: Steve Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: than you for your answer but... Steve wrote: thank you for the clue of double quoting the html when inserting into the database but I still get the error. the problem seems to be that I have quotes INSIDE the html code as well (links and pix) so, i still get this error. Database Selected: candlecatalog Query: insert into Products (ProdID,ProdName,ProdType,ProdDesc,Price,Pic,Buy) Values (VOALM,Almond Votive,Votive,Our Almond scented Candles are melon colored,1.25,,a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.the melt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/ca rt.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html; img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/image s/bu y_button_f.gif border=0 / /a) Results Error 1064: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_hhttp://www.c cnow .com/cgi-local/cart.cgi?meltingpot_VOALM_h Your problem is not with MySQL but with the programming language you're using. It probably has a facility to do this. PHP, as another poster has noted, has the function mysql_real_escape_string(). But you are not using PHP. What language are you using? Do you have a manual for it? Does it have a section on interfacing with MySQL? On escaping quotes? the code i want to insert is: a href=HYPERLINK http://www.ccnow.com/cgi-local/cart.cgi?meltingpot_VOALM_http://www.the melt ingpotonline.net/candleshop.htmlimghttp://www.ccnow.com/cgi-local/ca rt.c gi?meltingpot_VOALM_http://www.themeltingpotonline.net/candleshop.html; img src=HYPERLINK http://www.ccnow.com/images/buy_button_f.gifhttp://www.ccnow.com/image s/bu y_button_f.gif border=0 / /a Since you surround this with double quotes and then include more double quotes inside it, perhaps you are not a native speaker of a language that uses for quotes. In the ASCII character set (and on most keyboards), the beginning quotation mark and ending quotation mark are identical. This makes for difficulties when trying to enclose quotation marks within the larger quote. You can use the single quote ' inside double quotes or you can escape the quotes (by preceding them with an escape character: the backslash character in the case of MySQL). Hope that helps. --J PS Try to keep your messages in the same thread by replying to another message in that thread and not changing the subject. -- 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]
Replication advice
I've 3 machines that I use with mysql. One is used for BI consulting and other two for operational consulting. Which one is indicated to be an master server ? Is there possible, with 2 nics in machines, use one for replication and other for receive clients consulting ? Thanks. -- __ Alguns caminham pelo arco, eu caminho pela reta. Alexandre Gonçalves Jacarandá Assessor de Tecnologia de Informação Tel.: 0 ** 21 8131-2313 ___ Navegue com o Yahoo! Acesso Grátis, assista aos jogos do Brasil na Copa e ganhe prêmios de hora em hora! http://br.yahoo.com/artilheirodacopa/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Its not a problem with precision is it - 332.25 looks OK to you and me, but who knows what it looks like to the CPU? Maybe it sees one of the doubles as slightly different and updates? -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:48 a.m. To: mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect?
Thanks Quentin... Further to this, I tried changing the types of the fields from DOUBLE to: DECIMAL(10,2) and also VARCHAR(20) to avoid any issues with precision The query still returns back Query OK, 2 rows affected (0.05 sec) when the fields are DECIMAL or VARCHAR too. Might be time to file a bug report. Best regards, Richard Dale. Norgate Investor Services - Premium quality Stock, Futures and Foreign Exchange Data for markets in Australia, Asia, Canada, Europe, UK USA - www.premiumdata.net -Original Message- From: Quentin Bennett [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 12:41 PM To: Richard Dale; mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Its not a problem with precision is it - 332.25 looks OK to you and me, but who knows what it looks like to the CPU? Maybe it sees one of the doubles as slightly different and updates? -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:48 a.m. To: mysql@lists.mysql.com Subject: RE: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Most likely, MySQL has done a 'DELETE' followed by an 'INSERT' - i.e. two rows. According to: http://dev.mysql.com/doc/refman/4.1/en/insert-on-duplicate.html The rows-affected value is 1 if the row is inserted as a new record and 2 if an existing record is updated. But what happens if the row is neither inserted nor updated? Right now it appears to returns '2'. I think it should return '0'. Cheers, Richard. -Original Message- From: Richard Dale [mailto:[EMAIL PROTECTED] Sent: Friday, 19 May 2006 11:23 a.m. To: mysql@lists.mysql.com Subject: INESRT ... ON DUPLICATE KEY - rows affected incorrect? Mysql 4.1.16 running on Fedora Core 4 (Linu) X86_64 with InnoDB tables I have a table called which has a (combined) primary key of assetid and date plus several data fields (open, high, low, close - all doubles, and voume - a bigint). See the bottom of the email for the table definition. INSERT INTO price (assetid,date,open,high,low,close,volume) VALUES (202690,'2006-05-18','334.25','334.25','334.25','334.25','1') ON DUPLICATE KEY UPDATE open='334.25',high='334.25',low='334.25',close='334.25',volume='1'; The first time around, the insert works fine: Query OK, 1 row affected (0.03 sec); The second time around, when there should be neither an insert nor an update that affects any rows I get: Query OK, 2 rows affected (0.05 sec) To check that the update wasn't in fact updating any rows, I tried: UPDATE price SET open='334.25',high='334.25',low='334.25',close='334.25',volume='1' WHERE assetid=202690 AND date='2006-05-18'; Query OK, 0 rows affected (0.02 sec) Rows matched: 1 Changed: 0 Warnings: 0 So, it appears that the rows affected returned by an INSERT ... ON DUPLICATE KEY is incorrect. Can anyone else confirm that this is the correct behaviour? Thanks, Richard. - Table definition: CREATE TABLE `price` ( `assetid` int(11) NOT NULL default '0', `date` date NOT NULL default '-00-00', `open` double default NULL, `high` double default NULL, `low` double default NULL, `close` double default NULL, `volume` bigint(20) default NULL, PRIMARY KEY (`assetid`,`date`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
