Re: Can records be moved between tables without knowing columns
On Tue, 7 Oct 2003 16:48:55 -0400, Jim wrote Hi, I need to be able to 'delete' records from table(s) but still maintain their information for an audit trail. I'd rather not have a field for a deleted flag and have to condition all the queries on the table on the state of this flag. Is it possible to move a record from one table to another easily? Even better, can I do it opaquely (without knowledge of the columns)? Something like: move from LiveUsers to DeletedUsers where ID=?; where all the fields in the first table get moved to the corresponding field of the 2nd. (If this is possible, what happens with autoincrement, timestamp fields?) In the second table (DeletedUsers you don't define the ID autoincrement. Timestamp you can freeze, and you can try. From ducumentation I cut for You: Let MySQL set the column when the row is created. This will initialise it to the current date and time. When you perform subsequent updates to other columns in the row, set the TIMESTAMP column explicitly to its current value. http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#DATET IME Thanks for the help; I hope it isn't a trivial question. I'm new at this; I didn't find anything on moving records in the list archives. Jim Cant -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED] Laszlo Illyes Teaching-assistant Sapientia University (Csikszereda) Miercurea-Ciuc Tel:+40266317310 Fax:+40266317310/+40266371121 Mobil:+40740055706 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: error messages in different languages
I use 4.0.14 and 4.1.0-Alpha Thanks Emery - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 16:45 Subject: Re: error messages in different languages Director General: NEFACOMP [EMAIL PROTECTED] wrote: Yup, there is a comp-err.exe in the bin directory. I am sorry but I asked this after not finding it. Will you please give me an URL on the MySQL website where I can download it from ? What version of MySQL do you use? I don't exactly remember in which version comp-err.exe was added into windows distribution, seems it was 3.23.50. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: printing reports
Some are free, others are commercial. But, note that most of these are mainly for administration purposes. For example phpMyAdmin is a free PHP based client. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, ... You said that you are able to run queries, ... How do you run those queries? I suspect you use SHELLmysql -u UserName -h HostNameOrIP -p Is that right? If it is, then I will tell you that mysql is a client developped by the MySQL team to ease your administration of the database. Unfortunately, they have not implemented a reporting tool in that client. Are you familiar with MS Access? If yes, you can use Access to work with MySQL data. If you have Access installed, just let me know and I will send you Step by Step Instructions. Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 22:38 Subject: Re: printing reports Hi, Emery What do you mean client software? I just downloaded the MySQL server and I can insert data to the database and select the data from database and do some queries. In Oracle server, there're some commands which can be used to edit and print reports. What about MySQL? Also, as you said, I need a client software, what client software are you using? Are they free? Please give me some advice. cheers, feng - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:49 AM Subject: Re: printing reports To get data from/into MySQL, you will always need a client software. So, the reporting thing should be implemented in your client software. Hope to be right!!! Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:22 Subject: Re: printing reports In MySQL, how can I create and print reports without the help of neither a scripting language(e.g. php) nor a thrid party software tool? bad grammar :-( but you guys understand what i'm saying, right? :-) -- 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]
AW: printing reports
Well, it seems to me, that he is looking for some replacement of oracle reports ... Which is ... As far as i know ... Something like MS Access-Report-Wizard where you can say: Take this table, this field, link it to this field in the second table, show me all records which have date = TODAY and print it as a list in this form ... It is something like that and is the opposite to oracle FORMS which helps up building input forms for clients ... I think thats all. So, if I would be you I would take access for that ... Small, simple, fast in building reports, and easy to handle (- only sad thing about it: it is not working for real programers ... But quite good for dummy users ;-)) Michael -Ursprüngliche Nachricht- Von: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 8. Oktober 2003 09:52 An: Wang Feng; [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: printing reports Some are free, others are commercial. But, note that most of these are mainly for administration purposes. For example phpMyAdmin is a free PHP based client. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, ... You said that you are able to run queries, ... How do you run those queries? I suspect you use SHELLmysql -u UserName -h HostNameOrIP -p Is that right? If it is, then I will tell you that mysql is a client developped by the MySQL team to ease your administration of the database. Unfortunately, they have not implemented a reporting tool in that client. Are you familiar with MS Access? If yes, you can use Access to work with MySQL data. If you have Access installed, just let me know and I will send you Step by Step Instructions. Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 22:38 Subject: Re: printing reports Hi, Emery What do you mean client software? I just downloaded the MySQL server and I can insert data to the database and select the data from database and do some queries. In Oracle server, there're some commands which can be used to edit and print reports. What about MySQL? Also, as you said, I need a client software, what client software are you using? Are they free? Please give me some advice. cheers, feng - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:49 AM Subject: Re: printing reports To get data from/into MySQL, you will always need a client software. So, the reporting thing should be implemented in your client software. Hope to be right!!! Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:22 Subject: Re: printing reports In MySQL, how can I create and print reports without the help of neither a scripting language(e.g. php) nor a thrid party software tool? bad grammar :-( but you guys understand what i'm saying, right? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Eingehende Mail ist zertifiziert virenfrei. Überprüft durch AVG Antivirus System (http://www.grisoft.com/de). Version: 6.0.522 / Virendatenbank: 320 - Erstellungsdatum: 29.09.2003 --- Ausgehende Mail ist zertifiziert virenfrei. Überprüft durch AVG Antivirus System (http://www.grisoft.com/de). Version: 6.0.522 / Virendatenbank: 320 - Erstellungsdatum: 29.09.2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: No more room in index file
So Ken Menzel says: Hi Jacco, Perhaps the manual needs to be enhanced, but it is only a guide, perhaps the language should be softened or 'error 136' modified that it could be either problem. Either way I hope this fixes you problem, my guess would be you have very large rows of data, which is why you did not hit the error 135 first. I hope this takes care of it for you, we have created very large tables (150 million rows) and had good success, as have others. If this does not fix it we may have dig a little deeper. Let us know. Just thought I'd let you know, increasing the number of rows to 1 (that's 10^12) did the trick. We now have 4-byte index pointers with a maximum index-file size of 4398046510079 bytes. Thank you very much for your help. Groeten, - Jacco (still going to submit a manual bug, though) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
Unfortunately, they have not implemented a reporting tool in that client. that's the problem, as I was trying to find some sql commands to edit the report, such as report titles. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, i know them, but as I mentioned earlier, I was trying to find some sql commnds to achieve that. and i don't wanna print the report with the help of a programming language since i think if we can solve problems using SQL itself then it costs less. Are you familiar with MS Access? If yes, you can use Access to work with MySQL data. If you have Access installed, just let me know and I will send you Step by Step Instructions. yes, please send me the Step by Step Instructions. i do want to give it a try with Access although it's not free. cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
thanks for your nice advice. *dummy users* --- let's see. - Original Message - From: Michael Haunzwickl [EMAIL PROTECTED] To: 'Director General: NEFACOMP' [EMAIL PROTECTED]; 'Wang Feng' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 6:03 PM Subject: AW: printing reports Well, it seems to me, that he is looking for some replacement of oracle reports ... Which is ... As far as i know ... Something like MS Access-Report-Wizard where you can say: Take this table, this field, link it to this field in the second table, show me all records which have date = TODAY and print it as a list in this form ... It is something like that and is the opposite to oracle FORMS which helps up building input forms for clients ... I think thats all. So, if I would be you I would take access for that ... Small, simple, fast in building reports, and easy to handle (- only sad thing about it: it is not working for real programers ... But quite good for dummy users ;-)) Michael -Ursprüngliche Nachricht- Von: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 8. Oktober 2003 09:52 An: Wang Feng; [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: printing reports Some are free, others are commercial. But, note that most of these are mainly for administration purposes. For example phpMyAdmin is a free PHP based client. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, ... You said that you are able to run queries, ... How do you run those queries? I suspect you use SHELLmysql -u UserName -h HostNameOrIP -p Is that right? If it is, then I will tell you that mysql is a client developped by the MySQL team to ease your administration of the database. Unfortunately, they have not implemented a reporting tool in that client. Are you familiar with MS Access? If yes, you can use Access to work with MySQL data. If you have Access installed, just let me know and I will send you Step by Step Instructions. Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 22:38 Subject: Re: printing reports Hi, Emery What do you mean client software? I just downloaded the MySQL server and I can insert data to the database and select the data from database and do some queries. In Oracle server, there're some commands which can be used to edit and print reports. What about MySQL? Also, as you said, I need a client software, what client software are you using? Are they free? Please give me some advice. cheers, feng - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:49 AM Subject: Re: printing reports To get data from/into MySQL, you will always need a client software. So, the reporting thing should be implemented in your client software. Hope to be right!!! Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:22 Subject: Re: printing reports In MySQL, how can I create and print reports without the help of neither a scripting language(e.g. php) nor a thrid party software tool? bad grammar :-( but you guys understand what i'm saying, right? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL ODBC Mailing List For list archives: http://lists.mysql.com/myodbc To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --- Eingehende Mail ist zertifiziert virenfrei. Überprüft durch AVG Antivirus System (http://www.grisoft.com/de). Version: 6.0.522 / Virendatenbank: 320 - Erstellungsdatum: 29.09.2003 --- Ausgehende Mail ist zertifiziert virenfrei. Überprüft durch AVG Antivirus System (http://www.grisoft.com/de). Version: 6.0.522 / Virendatenbank: 320 - Erstellungsdatum: 29.09.2003 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help With a DATETIME Query PLEASE!
Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exponentiation operator
What is the correct syntax for using a Exponentiation operator in MySQL? BTW is this even posible? bye, Bob http://www.beheervisie.nl/disclaimer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exponentiation operator
On Wed, Oct 08, 2003 at 11:16:03AM +0200, Bob Brands wrote: What is the correct syntax for using a Exponentiation operator in MySQL? It's not an operator but a function. Look at EXP and POW in the docs: http://www.mysql.com/doc/en/Mathematical_functions.html Regards, Fred. -- Fred van Engen XB Networks B.V. email: [EMAIL PROTECTED]Televisieweg 2 tel: +31 36 5462400 1322 AC Almere fax: +31 36 5462424 The Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
Michael, Couldn't help myself to read your words again, and I have to say you r so stupid and annoying, at least today (i'm in a bad temper today). Although I don't use Access often, I don't say Access is for dummy users. In my opinion, every programming language and any tool has its own depth. Although there are some limits for Access, eg. speed and limits of user numbers, it has its own market, and if you can play it perfectly well, you'll be better off. You think MySQL is not for Dummy users? Well, in Oracle forums, they say it is, in this forum, people don't say so. But is MySQL really for dummy users? I say no, again, if you can play it well, you'll be better off. Access could be lightweight to MySQL, MySQL is definitely light-weight to Oracle so far. You suggest me to use Access, right? No thanks. I hope you can insist on Oracle, since Oracle is definitely not for dummy users, and then you can laugh at everybody in this mailing list - dummy users. Look, man, even you know I'm a dummy user, you don't have to say so on the mailing list :-) Now people around the world know *Wang Feng* is a dummy user, some of my friends on the list will laugh at me later. (Luckily I'm not a lecturer, otherwise just can't imagine how to face the students.) So, I hate you, hate you! and you're so annoying!! Don't wanna talk to you anymore. feng - Original Message - From: Michael Haunzwickl [EMAIL PROTECTED] To: 'Director General: NEFACOMP' [EMAIL PROTECTED]; 'Wang Feng' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 6:03 PM Subject: AW: printing reports Well, it seems to me, that he is looking for some replacement of oracle reports ... Which is ... As far as i know ... Something like MS Access-Report-Wizard where you can say: Take this table, this field, link it to this field in the second table, show me all records which have date = TODAY and print it as a list in this form ... It is something like that and is the opposite to oracle FORMS which helps up building input forms for clients ... I think thats all. So, if I would be you I would take access for that ... Small, simple, fast in building reports, and easy to handle (- only sad thing about it: it is not working for real programers ... But quite good for dummy users ;-)) Michael -Ursprüngliche Nachricht- Von: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 8. Oktober 2003 09:52 An: Wang Feng; [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: printing reports Some are free, others are commercial. But, note that most of these are mainly for administration purposes. For example phpMyAdmin is a free PHP based client. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, ... You said that you are able to run queries, ... How do you run those queries? I suspect you use SHELLmysql -u UserName -h HostNameOrIP -p Is that right? If it is, then I will tell you that mysql is a client developped by the MySQL team to ease your administration of the database. Unfortunately, they have not implemented a reporting tool in that client. Are you familiar with MS Access? If yes, you can use Access to work with MySQL data. If you have Access installed, just let me know and I will send you Step by Step Instructions. Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Director General: NEFACOMP [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 22:38 Subject: Re: printing reports Hi, Emery What do you mean client software? I just downloaded the MySQL server and I can insert data to the database and select the data from database and do some queries. In Oracle server, there're some commands which can be used to edit and print reports. What about MySQL? Also, as you said, I need a client software, what client software are you using? Are they free? Please give me some advice. cheers, feng - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:49 AM Subject: Re: printing reports To get data from/into MySQL, you will always need a client software. So, the reporting thing should be implemented in your client software. Hope to be right!!! Thanks Emery - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 12:22 Subject: Re: printing reports In MySQL, how can I create and print reports without the help of neither a scripting language(e.g. php) nor a thrid party software tool? bad grammar :-( but you guys understand what i'm saying, right? :-) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql
AW: printing reports
So listen guy, this is a big miss understanding: What i wanted to say, and what i did say was: 1.) If you want to have an easy way tool for creating your reports - use MS Access 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Michael -Ursprüngliche Nachricht- Von: Wang Feng [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 8. Oktober 2003 11:52 An: Michael Haunzwickl; 'Director General: NEFACOMP'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: printing reports Michael, Couldn't help myself to read your words again, and I have to say you r so stupid and annoying, at least today (i'm in a bad temper today). Although I don't use Access often, I don't say Access is for dummy users. In my opinion, every programming language and any tool has its own depth. Although there are some limits for Access, eg. speed and limits of user numbers, it has its own market, and if you can play it perfectly well, you'll be better off. You think MySQL is not for Dummy users? Well, in Oracle forums, they say it is, in this forum, people don't say so. But is MySQL really for dummy users? I say no, again, if you can play it well, you'll be better off. Access could be lightweight to MySQL, MySQL is definitely light-weight to Oracle so far. You suggest me to use Access, right? No thanks. I hope you can insist on Oracle, since Oracle is definitely not for dummy users, and then you can laugh at everybody in this mailing list - dummy users. Look, man, even you know I'm a dummy user, you don't have to say so on the mailing list :-) Now people around the world know *Wang Feng* is a dummy user, some of my friends on the list will laugh at me later. (Luckily I'm not a lecturer, otherwise just can't imagine how to face the students.) So, I hate you, hate you! and you're so annoying!! Don't wanna talk to you anymore. feng - Original Message - From: Michael Haunzwickl [EMAIL PROTECTED] To: 'Director General: NEFACOMP' [EMAIL PROTECTED]; 'Wang Feng' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 6:03 PM Subject: AW: printing reports Well, it seems to me, that he is looking for some replacement of oracle reports ... Which is ... As far as i know ... Something like MS Access-Report-Wizard where you can say: Take this table, this field, link it to this field in the second table, show me all records which have date = TODAY and print it as a list in this form ... It is something like that and is the opposite to oracle FORMS which helps up building input forms for clients ... I think thats all. So, if I would be you I would take access for that ... Small, simple, fast in building reports, and easy to handle (- only sad thing about it: it is not working for real programers ... But quite good for dummy users ;-)) Michael -Ursprüngliche Nachricht- Von: Director General: NEFACOMP [mailto:[EMAIL PROTECTED] Gesendet: Mittwoch, 8. Oktober 2003 09:52 An: Wang Feng; [EMAIL PROTECTED]; [EMAIL PROTECTED] Betreff: Re: printing reports Some are free, others are commercial. But, note that most of these are mainly for administration purposes. For example phpMyAdmin is a free PHP based client. Do you know some programming language? Like Visual Basic, C, C++, Java, PHP, ... You said that you are able to run queries, ... How do you run those queries? I suspect you use SHELLmysql -u UserName -h HostNameOrIP -p Is that right? If it is, then I will tell you that mysql is a client developped by the MySQL team to ease your administration of the database. Unfortunately, they have not implemented a reporting tool in that client. Are
RE: What would be an efficient way to accomplish this ( Statistic s/Trends in a table?) Thanks!
Paul How about select FIRST_NAME, count(*) from a table group by FIRSTNAME having count(FIRST_NAME) 1 Mike -- Live Life in Broadband www.telewest.co.uk The information transmitted is intended only for the person or entity to which it is addressed and may contain confidential and/or privileged material. Statements and opinions expressed in this e-mail may not represent those of the company. Any review, retransmission, dissemination or other use of, or taking of any action in reliance upon, this information by persons or entities other than the intended recipient is prohibited. If you received this in error, please contact the sender immediately and delete the material from any computer. == -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
So listen guy, this is a big miss understanding: NO 1.) If you want to have an easy way tool for creating your reports - use MS Access Hey, man, inserting a record is also ealier in Access. = You suggest me to do everything in Access. You'r bad bad man!!! -- Tools are for dummy users, Clever users create tools. -- 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. Unbelievable, haven't seen any smart guy doing so. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT normal? See, you're laughing at me, I can hear that. 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. If you understand my email, then Access is the tool you suggest? liar. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. Of course they do understand the word since you're telling them that. Actually, you can use the 'dummy user' word, but you should send the mail to me secretly :-) , now people know who I am are laughing at me. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. See, now people know that you prefer Oracle to MySQL. Because you're poor and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) Although I have $ to spend on Oracle, I still insist on MySQL. :-) So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. I was just joking, no worries. :-) My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Now seriously, thanks for the advice, but I won't adopt this since I really want to use something completely free. Access is good for printing, but it costs $. Michael i'll have to go to the gym. email you later. BTW, I don't mind people call me dummy user Hope you didn't read my words seriously, they're just jokes. :-) see ya. cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
So listen guy, this is a big miss understanding: NO 1.) If you want to have an easy way tool for creating your reports - use MS Access Hey, man, inserting a record is also ealier in Access. = You suggest me to do everything in Access. You'r bad bad man!!! -- Tools are for dummy users, Clever users create tools. -- 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. Unbelievable, haven't seen any smart guy doing so. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT normal? See, you're laughing at me, I can hear that. 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. If you understand my email, then Access is the tool you suggest? liar. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. Of course they do understand the word since you're telling them that. Actually, you can use the 'dummy user' word, but you should send the mail to me secretly :-) , now people know who I am are laughing at me. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. See, now people know that you prefer Oracle to MySQL. Because you're poor and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) Although I have $ to spend on Oracle, I still insist on MySQL. :-) So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. I was just joking, no worries. :-) My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Now seriously, thanks for the advice, but I won't adopt this since I really want to use something completely free. Access is good for printing, but it costs $. Michael i'll have to go to the gym. email you later. BTW, I don't mind people call me dummy user Hope you didn't read my words seriously, they're just jokes. :-) see ya. cheers, feng -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
Access is not a Database, its a kiddies toy. You can just as easlily make your reports using crystal reports or any thing else like that if you really want to, or PHP/ASP Now, please, for suggesting there is any use what so ever in the world for access, please take your self out back and shoot your self before the problem gets any worse - Original Message - From: Wang Feng [EMAIL PROTECTED] To: Michael Haunzwickl [EMAIL PROTECTED]; 'Director General: NEFACOMP' [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 11:32 AM Subject: Re: printing reports : So listen guy, this is a big miss understanding: : : NO : : : 1.) If you want to have an easy way tool for creating your reports - : use MS Access : : Hey, man, inserting a record is also ealier in Access. = You suggest me to : do everything in Access. You'r bad bad man!!! : : -- Tools are for dummy users, Clever users create tools. -- : : : 2.) All this things do not have anything to do with the base of your : datas (either you use Oracle, MySQL, Access or whatever) - so that : means: if you are using oracle, you can still use ACCESS to create some : reports. : : Unbelievable, haven't seen any smart guy doing so. : : : 3.) a dummy users is not a fool user but is a user how is like a : user is ... normal, not trained, unexperienced user ... That doesnt : mean that he has just shit in his head! : : yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT : normal? See, you're laughing at me, I can hear that. : : : : 4.) I didnt talk of you as a dummy user, because as I understand your : email, you were asking for tool to create reports easily and fast. : : If you understand my email, then Access is the tool you suggest? : : liar. : : : 5.) I think the only guy in this list, who falls into this : missunderstanding is you, because nobody contacted me to tell me, not to : use words like dummy user on the list. Maybe thats because they did : understand what was mentioned about it. : : Of course they do understand the word since you're telling them that. : : Actually, you can use the 'dummy user' word, but you should send the mail to : me secretly :-) , now people know who I am are laughing at me. : : : 6.) This shouldnt start a discussion of what things are good or bad in : IT science. So if you like Access, choose access, if you like MySQL : choose that one, and if you have a lot of money, than choose oracle, : which is definitly (sorry list) the best and completest database product : on the market. : : See, now people know that you prefer Oracle to MySQL. Because you're poor : and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) : Although I have $ to spend on Oracle, I still insist on MySQL. :-) : : : : So guy, before I come up and start giving you words, which i maybe see : later as a little bit to unfriendly ... I will stop this email now. : : I was just joking, no worries. :-) : : : : My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU : WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. : : Now seriously, thanks for the advice, but I won't adopt this since I really : want to use something completely free. Access is good for printing, but it : costs $. : : : Michael : : : i'll have to go to the gym. email you later. BTW, I don't mind people call : me dummy user Hope you didn't read my words seriously, they're just jokes. : :-) : : see ya. : : : cheers, : : feng : : : : : : -- : 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]
Limit queries
I have 2 tables: currencies and quotes currencies == id currency - quotes == id date id_currency value --- Index (date, id_currency - UNIQUE) In order to find the most recent value for a currency I use the following logic: 1. SELECT max(date), id_currency FROM quotes GROUP BY id_currency 2. for each set of values in result: SELECT C.currency, Q.value FROM quotes Q, currencies C WHERE C.id = Q.id_currency AND Q.date = $result[max_date] AND Q.id_currency = $result[id_currency] The procedure is quite slow, because for the 2nd step I have nnn queries where nnn = number of rows in the 1st step. Could you suggest a better and faster approach ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help With a DATETIME Query PLEASE!
On Wed, 2003-10-08 at 09:52, shaun thornburgh wrote: Hi, I have a table called Bookings which has two important columns; Booking_Start_Date and Booking_End_Date. These columns are both of type DATETIME. The following query calculates how many hours are available between the hours of 09.00 and 17.30 so a user can see at a glance how many hours they have unbooked on a particular day (i.e. 8.5 hours less the time of any bookings on that day). However, when a booking spans more than one day the query doesn't work, for example if a user has a booking that starts on day one at 09.00 and ends at 14.30 on the next day, the query returns 3.5 hours for both days. Any help here would be greatly appreciated. SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_End_Date, '%i')) - ((DATE_FORMAT(B.Booking_Start_Date, '%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND B.Booking_Status '1' AND NOT ( '2003-10-07' DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) Thanks for your help _ Find a cheaper internet access deal - choose one to suit you. http://www.msn.co.uk/internetaccess It would be a good idea to format your SQL so it can be read more easily, I am sure people would be more inclined to help you if you did this. I am sure PHP has date time functions that help with this. Have a look at the online manual or download it. Can't quite remember but I think there is an hour between function, go to php.net and look at the date/time function bit. If not try looking on the net for someone who has already written such a function, there probably is someone who has. Ben -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit queries
Add LIMIT x,y (x is first row and y the number of rows 1.e. 1,1 for first row) at the end of the SQL and order the SQL. Not sure if this helps, just initial thoughts. On Wed, 2003-10-08 at 13:04, Ciprian Trofin wrote: I have 2 tables: currencies and quotes currencies == id currency - quotes == id date id_currency value --- Index (date, id_currency - UNIQUE) In order to find the most recent value for a currency I use the following logic: 1. SELECT max(date), id_currency FROM quotes GROUP BY id_currency 2. for each set of values in result: SELECT C.currency, Q.value FROM quotes Q, currencies C WHERE C.id = Q.id_currency AND Q.date = $result[max_date] AND Q.id_currency = $result[id_currency] The procedure is quite slow, because for the 2nd step I have nnn queries where nnn = number of rows in the 1st step. Could you suggest a better and faster approach ? -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: printing reports
Please take this flame war off list. -Original Message- From: Wang Feng [mailto:[EMAIL PROTECTED] Sent: 08 October 2003 11:31 To: Michael Haunzwickl; 'Director General: NEFACOMP'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: printing reports Importance: Low So listen guy, this is a big miss understanding: NO 1.) If you want to have an easy way tool for creating your reports - use MS Access Hey, man, inserting a record is also ealier in Access. = You suggest me to do everything in Access. You'r bad bad man!!! -- Tools are for dummy users, Clever users create tools. -- 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. Unbelievable, haven't seen any smart guy doing so. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT normal? See, you're laughing at me, I can hear that. 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. If you understand my email, then Access is the tool you suggest? liar. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. Of course they do understand the word since you're telling them that. Actually, you can use the 'dummy user' word, but you should send the mail to me secretly :-) , now people know who I am are laughing at me. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. See, now people know that you prefer Oracle to MySQL. Because you're poor and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) Although I have $ to spend on Oracle, I still insist on MySQL. :-) So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. I was just joking, no worries. :-) My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Now seriously, thanks for the advice, but I won't adopt this since I really want to use something completely free. Access is good for printing, but it costs $. Michael i'll have to go to the gym. email you later. BTW, I don't mind people call me dummy user Hope you didn't read my words seriously, they're just jokes. :-) see ya. cheers, feng -- 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: printing reports
Sorry about that, those were jokes to entertain you experts. I know you guys work hard everyday, so just made some funs. Now Stop! :-) cheers, feng - Original Message - From: Andy Eastham [EMAIL PROTECTED] To: Mysql List [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 9:29 PM Subject: RE: printing reports Please take this flame war off list. -Original Message- From: Wang Feng [mailto:[EMAIL PROTECTED] Sent: 08 October 2003 11:31 To: Michael Haunzwickl; 'Director General: NEFACOMP'; [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: printing reports Importance: Low So listen guy, this is a big miss understanding: NO 1.) If you want to have an easy way tool for creating your reports - use MS Access Hey, man, inserting a record is also ealier in Access. = You suggest me to do everything in Access. You'r bad bad man!!! -- Tools are for dummy users, Clever users create tools. -- 2.) All this things do not have anything to do with the base of your datas (either you use Oracle, MySQL, Access or whatever) - so that means: if you are using oracle, you can still use ACCESS to create some reports. Unbelievable, haven't seen any smart guy doing so. 3.) a dummy users is not a fool user but is a user how is like a user is ... normal, not trained, unexperienced user ... That doesnt mean that he has just shit in his head! yeah? a 'dummy user' is 'normal' = not dummy users on this list are NOT normal? See, you're laughing at me, I can hear that. 4.) I didnt talk of you as a dummy user, because as I understand your email, you were asking for tool to create reports easily and fast. If you understand my email, then Access is the tool you suggest? liar. 5.) I think the only guy in this list, who falls into this missunderstanding is you, because nobody contacted me to tell me, not to use words like dummy user on the list. Maybe thats because they did understand what was mentioned about it. Of course they do understand the word since you're telling them that. Actually, you can use the 'dummy user' word, but you should send the mail to me secretly :-) , now people know who I am are laughing at me. 6.) This shouldnt start a discussion of what things are good or bad in IT science. So if you like Access, choose access, if you like MySQL choose that one, and if you have a lot of money, than choose oracle, which is definitly (sorry list) the best and completest database product on the market. See, now people know that you prefer Oracle to MySQL. Because you're poor and that's why you HAVE TO pick MySQL. I'm different, I like MySQL. :-) Although I have $ to spend on Oracle, I still insist on MySQL. :-) So guy, before I come up and start giving you words, which i maybe see later as a little bit to unfriendly ... I will stop this email now. I was just joking, no worries. :-) My tip for you: TAKE MS ACCESS TO CREATE YOUR REPORTS ... Or ... IF YOU WANT TO HAVE A REAL GOOD REPORTING TOOL ... TAKE CRYSTAL REPORT. Now seriously, thanks for the advice, but I won't adopt this since I really want to use something completely free. Access is good for printing, but it costs $. Michael i'll have to go to the gym. email you later. BTW, I don't mind people call me dummy user Hope you didn't read my words seriously, they're just jokes. :-) see ya. cheers, feng -- 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[2]: printing reports
1.) If you want to have an easy way tool for creating your reports - use MS Access Yep if you know Access WF -- Tools are for dummy users, Clever users create tools. -- Clever users select the best way they can do things for themselves What is best depends on user knowledge and objectives ( best could mean cheapest or fastest or least learning curve or steepest learning curve etc etc ) For me for example having Borland C++ Builder and Rave Reports and being skilled in working with these tools the best would probably mean to convert c++ code in question from MS Visual Studio to Borland C++ Builder and use Rave as reporting tool (BTW Rave has PDFRenderer and HTMLRenderer to convert Rave reports to PDF and HTML) For those who used to work with Crystal Reports the best would probably mean working with Crystal Reports. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re[2]: printing reports
One of the best things is to listen from diffrent opinions from different people who have different backgrounds. :-) cheers, feng - Original Message - From: DeBug [EMAIL PROTECTED] To: Wang Feng [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 9:42 PM Subject: Re[2]: printing reports 1.) If you want to have an easy way tool for creating your reports - use MS Access Yep if you know Access WF -- Tools are for dummy users, Clever users create tools. -- Clever users select the best way they can do things for themselves What is best depends on user knowledge and objectives ( best could mean cheapest or fastest or least learning curve or steepest learning curve etc etc ) For me for example having Borland C++ Builder and Rave Reports and being skilled in working with these tools the best would probably mean to convert c++ code in question from MS Visual Studio to Borland C++ Builder and use Rave as reporting tool (BTW Rave has PDFRenderer and HTMLRenderer to convert Rave reports to PDF and HTML) For those who used to work with Crystal Reports the best would probably mean working with Crystal Reports. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: printing reports
I'd hardly call it a war, more of a skirmish, if that. perhaps at best it should be considered a Terrorist action, don't think it even gets up to an Act of Terror which is good, because I don't want the American Jack Boot of Opression on my back, thank you very much. - Original Message - From: Andy Eastham [EMAIL PROTECTED] To: Mysql List [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 12:29 PM Subject: RE: printing reports : Please take this flame war off list. : -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.0.9 STABLE Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.0.9, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. It is now available in source and binary form from the Connector/J download pages at http://www.mysql.com/downloads/api-jdbc-stable.html and mirror sites. Note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site. The new CHANGELOG for 3.0.9 should be posted on the website later today as well (the one for 3.0.8 is still listed there), but 3.0.9 is available for downloading from the URL above. - From the changelog: 10-07-03 - Version 3.0.9-stable - Faster date handling code in ResultSet and PreparedStatement (no longer uses Date methods that synchronize on static calendars). - Fixed test for end of buffer in Buffer.readString(). - Fixed ResultSet.previous() behavior to move current position to before result set when on first row of result set (bugs.mysql.com BUG#496) - Fixed Statement and PreparedStatement issuing bogus queries when setMaxRows() had been used and a LIMIT clause was present in the query. - Fixed BUG#661 - refreshRow didn't work when primary key values contained values that needed to be escaped (they ended up being doubly-escaped). - Support InnoDB contraint names when extracting foreign key info in DatabaseMetaData BUG#517 and BUG#664 (impl. ideas from Parwinder Sekhon) - Backported 4.1 protocol changes from 3.1 branch (server-side SQL states, new field info, larger client capability flags, connect-with-database, etc). - Fix UpdatableResultSet to return values for getXXX() when on insert row (BUG#675). - The insertRow in an UpdatableResultSet is now loaded with the default column values when moveToInsertRow() is called (BUG#688) - DatabaseMetaData.getColumns() wasn't returning NULL for default values that are specified as NULL. - Change default statement type/concurrency to TYPE_FORWARD_ONLY and CONCUR_READ_ONLY (spec compliance). - Don't try and reset isolation level on reconnect if MySQL doesn't support them. - Don't wrap SQLExceptions in RowDataDynamic. - Don't change timestamp TZ twice if useTimezone==true (BUG#774) - Fixed regression in large split-packet handling (BUG#848). - Better diagnostic error messages in exceptions for 'streaming' result sets. - Issue exception on ResultSet.getXXX() on empty result set (wasn't caught in some cases). - Don't hide messages from exceptions thrown in I/O layers. - Don't fire connection closed events when closing pooled connections, or on PooledConnection.getConnection() with already open connections (BUG#884). - Clip +/- INF (to smallest and largest representative values for the type in MySQL) and NaN (to 0) for setDouble/setFloat(), and issue a warning on the statement when the server does not support +/- INF or NaN. - Fix for BUG#879, double-escaping of '\' when charset is SJIS or GBK and '\' appears in non-escaped input. - When emptying input stream of unused rows for 'streaming' result sets, have the current thread yield() every 100 rows in order to not monopolize CPU time. - Fixed BUG#1099, DatabaseMetaData.getColumns() getting confused about the keyword 'set' in character columns. - Fixed deadlock issue with Statement.setMaxRows(). - Fixed CLOB.truncate(), BUG#1130 - Optimized CLOB.setChracterStream(), BUG#1131 - Made databaseName, portNumber and serverName optional parameters for MysqlDataSourceFactory (BUG#1246) - Fix for BUG#1247 -- ResultSet.get/setString mashing char 127 - Backported auth. changes for 4.1.1 and newer from 3.1 branch. - Added com.mysql.jdbc.util.BaseBugReport to help creation of testcases for bug reports. - Added property to 'clobber' streaming results, by setting the 'clobberStreamingResults' property to 'true' (the default is 'false'). This will cause a 'streaming' ResultSet to be automatically closed, and any oustanding data still streaming from the server to be discarded if another query is executed before all the data has been read from the server. Have fun with it! -Mark -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQE/hAb8tvXNTca6JD8RAoKSAJ9TOpOhxzWEN6W1S2zLZeVgskdTswCgnJLO Cvu/tpY75QUIQGNwq2tJql0= =okMW -END PGP
Sudden error when replicationg - please help
I have a slave that has been running for a long time without any problems, but now its not replicating any more. So I tried to do a LOAD DATA MASTER; which always gives me this error now: Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 031008 15:07:59 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 031008 15:08:14 Slave I/O thread exiting, read up to log 'mysql-bin.006', position 357019 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/otafrontenduser' with keycache 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/profile' with keycache 031008 15:08:16 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 I have tried to do a REPAIR TABLE otafrontenduser,profile EXTENDED; but no success (its says ok, but the replication does still not work). Why and what are these errors, and what can I do? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden error when replicationg - please help
you are not using Debian testing by any chance? If so last time I did an apt-get upgrade it broke mysql and I fixed it by doing a reinstall. On Wed, 2003-10-08 at 14:13, Søren Neigaard wrote: I have a slave that has been running for a long time without any problems, but now its not replicating any more. So I tried to do a LOAD DATA MASTER; which always gives me this error now: Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 031008 15:07:59 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 031008 15:08:14 Slave I/O thread exiting, read up to log 'mysql-bin.006', position 357019 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/otafrontenduser' with keycache 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/profile' with keycache 031008 15:08:16 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 I have tried to do a REPAIR TABLE otafrontenduser,profile EXTENDED; but no success (its says ok, but the replication does still not work). Why and what are these errors, and what can I do? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sudden error when replicationg - please help
Pada Wed, 8 Oct 2003 15:13:00 +0200 Søren Neigaard [EMAIL PROTECTED] menulis: what was the show slave status output ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sudden error when replicationg - please help
No its Redhat, and i have made absolutly no updates :( -Original Message- From: Ben Edwards [mailto:[EMAIL PROTECTED] Sent: 8. oktober 2003 15:23 To: Søren Neigaard Cc: '[EMAIL PROTECTED]' Subject: Re: Sudden error when replicationg - please help you are not using Debian testing by any chance? If so last time I did an apt-get upgrade it broke mysql and I fixed it by doing a reinstall. On Wed, 2003-10-08 at 14:13, Søren Neigaard wrote: I have a slave that has been running for a long time without any problems, but now its not replicating any more. So I tried to do a LOAD DATA MASTER; which always gives me this error now: Version: '4.0.15-standard-log' socket: '/var/lib/mysql/mysql.sock' port: 3306 031008 15:07:59 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 031008 15:08:14 Slave I/O thread exiting, read up to log 'mysql-bin.006', position 357019 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/otafrontenduser' with keycache 031008 15:08:14 Couldn't fix table with quick recovery: Found wrong number of deleted records 031008 15:08:14 Run recovery again without -q 031008 15:08:14 Note: Retrying repair of: './otamanager/profile' with keycache 031008 15:08:16 Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306', replication started in log 'mysql-bin.006' at position 357019 I have tried to do a REPAIR TABLE otafrontenduser,profile EXTENDED; but no success (its says ok, but the replication does still not work). Why and what are these errors, and what can I do? Med venlig hilsen/Best regards Søren Neigaard System Architect Mobilethink A/S Arosgaarden Åboulevarden 23, 4.sal DK - 8000 Århus C Telefon: +45 86207800 Direct: +45 86207810 Fax: +45 86207801 Email: [EMAIL PROTECTED] Web: www.mobilethink.dk -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- 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: Sudden error when replicationg - please help
I attached the output from show slave status. /Søren -Original Message- From: Dicky 'Mas Saint' [mailto:[EMAIL PROTECTED] Sent: 8. oktober 2003 15:28 To: [EMAIL PROTECTED] Subject: Re: Sudden error when replicationg - please help Pada Wed, 8 Oct 2003 15:13:00 +0200 Søren Neigaard [EMAIL PROTECTED] menulis: what was the show slave status output ? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ | Master_Host | Master_User | Master_Port | Connect_retry | Master_Log_File | Read_Master_Log_Pos | Relay_Log_File | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_do_db | Replicate_ignore_db | Last_errno | Last_error | Skip_counter | Exec_master_log_pos | Relay_log_space | +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ | 172.16.1.103 | dba | 3306| 60| mysql-bin.037 | 11894440| waspstat1-relay-bin.001 | 4 | mysql-bin.037 | Yes | Yes | | | 0 || 0| 11894440| 4 | +--+-+-+---+-+-+-+---+---+--+---+-+-+++--+-+-+ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limit queries
* Ciprian Trofin I have 2 tables: currencies and quotes currencies == id currency - quotes == id date id_currency value --- Index (date, id_currency - UNIQUE) In order to find the most recent value for a currency I use the following logic: 1. SELECT max(date), id_currency FROM quotes GROUP BY id_currency 2. for each set of values in result: SELECT C.currency, Q.value FROM quotes Q, currencies C WHERE C.id = Q.id_currency AND Q.date = $result[max_date] AND Q.id_currency = $result[id_currency] The procedure is quite slow, because for the 2nd step I have nnn queries where nnn = number of rows in the 1st step. Could you suggest a better and faster approach ? Maybe you can use the max-concat trick in this case? SELECT C.currency, SUBSTRING(MAX(CONCAT(Q.date,'-',Q.value)),12) as value FROM quotes Q, currencies C WHERE C.id = Q.id_currency GROUP BY C.currency URL: http://www.mysql.com/doc/en/example-Maximum-column-group-row.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Easy (?) conditional SELECT
I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: What is 4.0.15-max-debug
Arthur Maloney [EMAIL PROTECTED] wrote: I have recently reformatted my hard disk and done a clean install of 4.0.15 on Win 2k Pro SP4. I used C:\ C:\mysql\bin\ mysqld --install This created a mySQL service and it works ok. I happened look at a log in the data folder and noticed 031007 9:37:53 InnoDB: Started MySql: ready for connections. Version: '4.0.15-max-debug' socket: '' port: 3306 Since I asked for the standard server mysqld What is max-debug and where did it come from? Look at the manual: http://www.mysql.com/doc/en/Windows_prepare_environment.html You can find here: mysqld Compiled with full debugging and automatic memory allocation checking, symbolic links, InnoDB, and BDB tables. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
) In MySQL we don't have subselect. this is not valid: SELECT article, dealer, price FROM shop s1 WHERE price=(SELECT MAX(s2.price) FROM shop s2 WHERE s1.article = s2.article); instead of this, in MySQL we can write: CREATE TEMPORARY TABLE tmp ( article INT(4) UNSIGNED ZEROFILL DEFAULT '' NOT NULL, price DOUBLE(16,2) DEFAULT '0.00' NOT NULL); LOCK TABLES shop read; INSERT INTO tmp SELECT article, MAX(price) FROM shop GROUP BY article; SELECT shop.article, dealer, shop.price FROM shop, tmp WHERE shop.article=tmp.article AND shop.price=tmp.price; UNLOCK TABLES; DROP TABLE tmp; Can it be done with a single query? Yes, but only by using a quite inefficient trick that I call the MAX-CONCAT trick: SELECT article, SUBSTRING( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 7) AS dealer, 0.00+LEFT( MAX( CONCAT(LPAD(price,6,'0'),dealer) ), 6) AS price FROM shop GROUP BY article; - Original Message - From: Mark Wilson [EMAIL PROTECTED] To: Mysql Mailing List [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 9:35 AM Subject: Easy (?) conditional SELECT I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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: Easy (?) conditional SELECT
BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL doesn't like the single quotes around the table name, so I took them out. Now THIS query works: SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1' -- (returns '2') But this one still doesn't: SELECT * from artifacts WHERE (product_id = '1' AND plan_submission_number = (SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1') ) Although that seems as if it should be equivalent to the following hard-coded version: SELECT * FROM `artifacts` WHERE (product_id = '1' AND plan_submission_number = '2') -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 Quoting Mark Wilson [EMAIL PROTECTED]: I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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: error messages in different languages
Director General: NEFACOMP [EMAIL PROTECTED] wrote: I use 4.0.14 and 4.1.0-Alpha Hmm .. I have comp-err.exe in 4.0.14, but it's absent in 4.1.0. Thanks Emery - Original Message - From: Egor Egorov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 16:45 Subject: Re: error messages in different languages Director General: NEFACOMP [EMAIL PROTECTED] wrote: Yup, there is a comp-err.exe in the bin directory. I am sorry but I asked this after not finding it. Will you please give me an URL on the MySQL website where I can download it from ? What version of MySQL do you use? I don't exactly remember in which version comp-err.exe was added into windows distribution, seems it was 3.23.50. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
Er, I _used_ a basic SQL tutorial, which specifically said that should work. The problem seems to be a limitation of MySQL, not general SQL operation. That being said, are there any clever one-query options (using JOINs, etc?) or is this basically a 2-step process in MySQL? -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 Quoting Ben Edwards [EMAIL PROTECTED]: On Wed, 2003-10-08 at 14:35, Mark Wilson wrote: I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 Looks like you need to find a basic SQL tutorial. You can only have one SELECT, FROM and WHERE. Also you cant really do anything useful without joins. Ben -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
On Wed, 2003-10-08 at 15:06, Mark Wilson wrote: Er, I _used_ a basic SQL tutorial, which specifically said that this should work. The problem seems to be a limitation of MySQL, not general SQL operation. That being said, are there any clever one-query options (using JOINs, etc?) or is this basically a 2-step process in MySQL? Actually I just reread the SQL. MySQL 3 douse not support sub-queries. I think 4 might and I think subqueries are in the ANSI 95 SQL standard (or was it 92). It douse have to be done in a two step operation for this type of max function. The other option, if you can order the query, is to use the LIMIT clause (mysql specific). LIMIT x,y, x=first row and y=number of rows. So if you ordered your query with the row you wanted first add LIMIT 1,1 at the beginning. Ben -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FW: MySQL not null vs MSAccess required
Michael Stassen wrote: Cal Evans wrote: or '' (empty string). You can change this behavior by building your own mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option. Thanks, I'm off to recompile my development server. you should be able to define a field as NOT NULL without a default or at the very least, define the default as NULL. This does not make sense to me. Allowing NULL as the default for a column declared NOT NULL would defeat the purpose of declaring it NOT NULL in the first place. It makes perfect sense. As a matter of fact, declaring a column as NOT NULL and then providing a default value of anything other than NULL means that you are defeating the constraint and it will almost never be triggered. If I set a field as NOT NULL then that means I want the user/application to put some thought into what is going into it. I do not want a default value. An example would be credit card number. I set that to NOT NULL in my shopping cart. I do a rudimentary check at the FE and I do a more exhaustive check at the middle-tier to make sure that I have a number and it looks valid. However, the order is invalid if I don't have it so at the database level I make that column NOT NULL. If something goes wrong along the way and the first two checks miss it, this one will catch it. The way my framework works is that the Insert into the database is built based on the values passed in. If ccNumber does not exist then it is not inserted. If the field is NOT NULL and there is a default value of anything OTHER than null then the database will accept an invalid order. IMHO, FWIW, etc. =C= * Cal Evans * http://www.eicc.com * We take care of your IT, * So you can take care of your business. * * I think inside the sphere. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
Problem 1 : Your query contains a subquery : SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) subqueries are (AFAIK) not supported in the latest production version of MySQL (4.0.15) but are coming soon...might already be in a beta ver Two possible solutions - 1) If there are a fixed number of artifacts per product (n): SELECT * FROM artifacts WHERE product_id = 1 ORDER BY plan_submission_number DESC LIMIT 1, n This retrieves the n most recent records for the specific product_id But obviously this only works if n is fixed... 2)Otherwise the only solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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: Exponentiation operator
I think exp(number, number) will do the job. Check the manual for the correct syntax. Thanks Emery - Original Message - From: Bob Brands [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 11:16 Subject: Exponentiation operator What is the correct syntax for using a Exponentiation operator in MySQL? BTW is this even posible? bye, Bob http://www.beheervisie.nl/disclaimer. -- 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]
threading support
Hi list, I have the following problem and here is the message that displays the console when I try to connect Warning: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent I'm using linux, Red Hat 7.3 Linux version 2.4.22, and 3.23.55-Max-log. Is This information ok?, or I'm missing something. Greetings Mikel _ Charla con tus amigos en línea mediante MSN Messenger: http://messenger.microsoft.com/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how to export data from multiple tables
any idea, how to export data to a file on the remote machine? thanx for the help anyway Nitin - Original Message - From: Andy Eastham [EMAIL PROTECTED] To: Mysql List [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 9:49 PM Subject: RE: how to export data from multiple tables Nitin, Create a temporary table, then select each table into it in turn, then output that to the file? Andy -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: 07 October 2003 11:40 To: [EMAIL PROTECTED] Subject: how to export data from multiple tables Hi all, I've got a small problem (hope it's very small). Could any of you suggest me how to select my data into a simple text file from multiple tables (selected with union)! I've tried: select field-list into outfile filename from table1 union select field-list into outfile filename from table2 union select field-list into outfile filename from table3 It says check your version manualblah blah Any idea, how to do it? Thanx in advance Nitin -- 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: Easy (?) conditional SELECT
Subqueries don't show up until v4.1 which I have running on one of my servers. Runs great and I have it into production. --ja On Wed, 8 Oct 2003, Rory McKinley wrote: Problem 1 : Your query contains a subquery : SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) subqueries are (AFAIK) not supported in the latest production version of MySQL (4.0.15) but are coming soon...might already be in a beta ver Two possible solutions - 1) If there are a fixed number of artifacts per product (n): SELECT * FROM artifacts WHERE product_id = 1 ORDER BY plan_submission_number DESC LIMIT 1, n This retrieves the n most recent records for the specific product_id But obviously this only works if n is fixed... 2)Otherwise the only solution I can think of is to have two queries - One gets the max submission number and the other gets the artifacts (using the result of the first query) Not much help I guess..but my 2c. Rory McKinley Nebula Solutions +27 82 857 2391 [EMAIL PROTECTED] There are 10 kinds of people in this world, those who understand binary and those who don't (Unknown) I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- 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]
smaller time units?
it seems that time gives the hour, minutes, seconds. is there a way to get tenths and hundreths? how about without hours? basically, i'm wanting to store lap times for racing. Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to export data from multiple tables
What machines are you working with at each end? Internal network? -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 10:19 AM To: Andy Eastham; Mysql List Subject: Re: how to export data from multiple tables any idea, how to export data to a file on the remote machine? thanx for the help anyway Nitin - Original Message - From: Andy Eastham [EMAIL PROTECTED] To: Mysql List [EMAIL PROTECTED] Sent: Tuesday, October 07, 2003 9:49 PM Subject: RE: how to export data from multiple tables Nitin, Create a temporary table, then select each table into it in turn, then output that to the file? Andy -Original Message- From: Nitin [mailto:[EMAIL PROTECTED] Sent: 07 October 2003 11:40 To: [EMAIL PROTECTED] Subject: how to export data from multiple tables Hi all, I've got a small problem (hope it's very small). Could any of you suggest me how to select my data into a simple text file from multiple tables (selected with union)! I've tried: select field-list into outfile filename from table1 union select field-list into outfile filename from table2 union select field-list into outfile filename from table3 It says check your version manualblah blah Any idea, how to do it? Thanx in advance Nitin -- 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: smaller time units?
* [EMAIL PROTECTED] it seems that time gives the hour, minutes, seconds. is there a way to get tenths and hundreths? how about without hours? Not as a data type, but see the DATE_FORMAT() function: URL: http://www.mysql.com/doc/en/Date_and_time_functions.html#IDX1333 basically, i'm wanting to store lap times for racing. I would convert everything to tenths or hundreths and store it as integers. You could also use a floating point type, like FLOAT or DOUBLE, but be aware of the problems dealing with approximate numbers: URL: http://www.mysql.com/doc/en/Problems_with_float.html Note that these problems also apply to the so-called exact types DECIMAL and NUMERIC. -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MacOSX 4.0.15 mysqld_safe restarts after STOP
Hi! I'm struggling to understand how to fix the mysqld_safe script for an OSX machine. If I run mysql.server stop then the mysqld process is killed and mysqld_safe promptly starts it again. I know mysqld_safe is supposed to start mysqld if it stops but I also know there are times I need to work on files and so on and when you need to stop it you need to stop it... I'm afraid the scripting in mysqld_safe is beyond my ability to figure out. mysql.safe I can work with easily enough, and I was planning to kill the mysqld_safe process when I got a confirmation that mysqld had stopped (when the pid file disappeared), however I don't have a way (that I know) to tell the PID of the sh that mysqld_safe is running under to add the kill to the script, and I don't want to blindly start killing shells in the hope I get the one running mysqld_safe. On our Solaris systems this isn't a problem and the the mysql.server stop script stops mysqld and mysqld_safe then decides to quit... I don't know how to fix it for OSX... I'm happy to add something to the relevant part of mysqld.server to kill mysqld_safe's shell if I knew the right shell to kill. Any ideas here? Best Regards, Bruce -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MacOSX 4.0.15 mysqld_safe restarts after STOP
At 10:45 -0700 10/8/03, Bruce Dembecki wrote: Hi! I'm struggling to understand how to fix the mysqld_safe script for an OSX machine. If I run mysql.server stop then the mysqld process is killed and mysqld_safe promptly starts it again. I know mysqld_safe is supposed to start mysqld if it stops but I also know there are times I need to work on files and so on and when you need to stop it you need to stop it... Try using mysqladmin -u root -p shutdown instead. I'm afraid the scripting in mysqld_safe is beyond my ability to figure out. mysql.safe I can work with easily enough, and I was planning to kill the mysqld_safe process when I got a confirmation that mysqld had stopped (when the pid file disappeared), however I don't have a way (that I know) to tell the PID of the sh that mysqld_safe is running under to add the kill to the script, and I don't want to blindly start killing shells in the hope I get the one running mysqld_safe. On our Solaris systems this isn't a problem and the the mysql.server stop script stops mysqld and mysqld_safe then decides to quit... I don't know how to fix it for OSX... I'm happy to add something to the relevant part of mysqld.server to kill mysqld_safe's shell if I knew the right shell to kill. Any ideas here? Best Regards, Bruce -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup methodology?
I am learning the DBA ropes on a MySQL 4.0.14 database. I am working on a backup strategy and I have a few questions. But first, a bit about the database: we are collecting messages from a customer, storing them in a table, then pushing the data to a master database. The messages are inserts into the table. We get roughly 9million inserts a week. Nothing else is done to the database. What I want to do is this: 1. Do a full snapshot of the tables and data once a week (I know how to do this with mysqldump). 2. Do a daily incremental backup. I can do this two ways: A. Do a mysqldump of all the data for that day B. Use the update-log (or binary-log) and back that up. Which is the best way? I am looking for small size and ease of restoration. I am leaning towards the update-log, since this database has so many record additions. Any ideas? Ben Ricker Wellinx, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with deleting records
We have a script that deletes records from a database that are over then a certain date. We had no problem running the delete on 3.x, but on 4.0.14, we are having a problem. Here is the command that does the delete: $EXECPATH/mysql --host=hostname --user=blah --password=blah --execute=delete from MESSAGES where to_days(now()) - to_days(timestamp) 2 and sent = 'Y' test After running this, the index is hopelessly corrupted and we need to run myisamchk -ro on the index file to fix the problem. I also noticed that the index is corrupt even before the delete is happening. This database receives about 9 million records a week and is constantly updated 24/7. Here is the output of myisamchk -ai *.MYI: bash-2.05$ bin/myisamchk -ai ./data/test/*.MYI Checking MyISAM file: ./data/test/MESSAGES.MYI Data records: 108469 Deleted blocks: 0 myisamchk: warning: 1 clients is using or hasn't closed the table properly - check file-size - check key delete-chain - check record delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 97% Packed:0% Max levels: 3 - check data record references index: 2 Key: 2: Keyblocks used: 62% Packed: 69% Max levels: 3 - check data record references index: 3 myisamchk: error: Found key at page 7577600 that points to record outside datafile - check record links myisamchk: error: Keypointers and record positions doesn't match Record blocks: 108469Delete blocks: 0 Record data: 92107260Deleted data: 0 Lost space: 81555Linkdata: 406685 MyISAM-table './data/test/MESSAGES.MYI' is corrupted Fix it using switch -r or -o User time 4.47, System time 1.51 Maximum resident set size 0, Integral resident set size 0 Non-physical pagefaults 0, Physical pagefaults 88, Swaps 0 Blocks in 2524 out 4, Messages in 0 out 0, Signals 0 Voluntary context switches 387, Involuntary context switches 773 Could this corruption cause the delete to totally fubar the indexes? If so, is there a good way to manage the indexes on the database that has so many changes? Should I run the -r or -o on the file before I delete? Thanks, Ben Ricker Wellinx, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
foreign key constraints
Here is the script that creates the database.
create table object_attrib (obj_record_id INTEGER(7) AUTO_INCREMENT NOT
NULL,
PRIMARY KEY (obj_record_id),
INDEX (rel_obj_int_id),
FOREIGN KEY (rel_obj_int_id)
REFERENCES obj_id_internals (obj_int_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_os_id),
FOREIGN KEY (rel_os_id)
REFERENCES os_product (os_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_db_id),
FOREIGN KEY (rel_db_id)
REFERENCES db_product (db_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
rel_obj_int_id INTEGER(7) NOT NULL, rel_os_id INTEGER(7) NOT NULL,
rel_db_id INTEGER(7) NOT NULL, obj_path VARCHAR(255) NOT NULL,
obj_name VARCHAR(255) NOT NULL, obj_4n6_purpose BLOB NULL,
obj_info_source BLOB NULL, obj_comments BLOB NULL,
obj_md5_value VARCHAR(43) NOT NULL, obj_stat_dyn VARCHAR(8) NULL,
obj_byte_size VARCHAR(16) NOT NULL, obj_category VARCHAR(255) NULL,
obj_rec_last_update DATE NOT NULL, obj_rec_orgin DATE NOT NULL) TYPE =
INNODB;
Here are the lines of perl code that I am using.
open(INPUT,$filename) || die Check to see if $filename is a valid file.\n;
$sth = $dbh-prepare(q{INSERT INTO
object_attrib(obj_path,obj_name,obj_byte_size,obj_md5_value) VALUES
(?,?,?,?)}) || die $dbh-errstr;
while (INPUT)
{
chomp;
($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) = split /,/;
$sth-execute($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) || die
$dbh-errstr;
}
close INPUT;
$dbh-disconnect;
Can someone tell me why I get the following error:
Cannot add or update a child row: a foreign key constraint fails.
Thanks,
Brian Croniser
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: foreign key constraints
At 18:22 + 10/8/03, Croniser Brian Contr AFRL/IFGB wrote:
Here is the script that creates the database.
create table object_attrib (obj_record_id INTEGER(7) AUTO_INCREMENT NOT
NULL,
PRIMARY KEY (obj_record_id),
INDEX (rel_obj_int_id),
FOREIGN KEY (rel_obj_int_id)
REFERENCES obj_id_internals (obj_int_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_os_id),
FOREIGN KEY (rel_os_id)
REFERENCES os_product (os_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
INDEX (rel_db_id),
FOREIGN KEY (rel_db_id)
REFERENCES db_product (db_id)
ON DELETE RESTRICT ON UPDATE CASCADE,
rel_obj_int_id INTEGER(7) NOT NULL, rel_os_id INTEGER(7) NOT NULL,
rel_db_id INTEGER(7) NOT NULL, obj_path VARCHAR(255) NOT NULL,
obj_name VARCHAR(255) NOT NULL, obj_4n6_purpose BLOB NULL,
obj_info_source BLOB NULL, obj_comments BLOB NULL,
obj_md5_value VARCHAR(43) NOT NULL, obj_stat_dyn VARCHAR(8) NULL,
obj_byte_size VARCHAR(16) NOT NULL, obj_category VARCHAR(255) NULL,
obj_rec_last_update DATE NOT NULL, obj_rec_orgin DATE NOT NULL) TYPE =
INNODB;
Here are the lines of perl code that I am using.
open(INPUT,$filename) || die Check to see if $filename is a valid file.\n;
$sth = $dbh-prepare(q{INSERT INTO
object_attrib(obj_path,obj_name,obj_byte_size,obj_md5_value) VALUES
(?,?,?,?)}) || die $dbh-errstr;
while (INPUT)
{
chomp;
($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) = split /,/;
$sth-execute($obj_path,$obj_name,$obj_byte_size,$obj_md5_value) || die
$dbh-errstr;
}
close INPUT;
$dbh-disconnect;
Can someone tell me why I get the following error:
Cannot add or update a child row: a foreign key constraint fails.
Thanks,
Brian Croniser
You're not assigning an explicit value to any of your foreign
keys. Apparently the default value (0) is not present in one or
the other of the referenced keys in the parent table?
--
Paul DuBois, Senior Technical Writer
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
Are you MySQL certified? http://www.mysql.com/certification/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unmet Dependency Hell
I installed 4.1.0 alpha from the RPMS on Redhat 9. I had to use --force (or is it --nodeps, I can never remember) because MySQL wants libcrypto and libssl 0.9.6 and Redhat 9 has 0.9.7. First question (coming from that other world) why aren't these dependencies 0.9.6, or greater, instead of being locked into a specific version. My second question is what can I do about it? MySQL runs fine despite the version difference BUT I can't apt-get upgrade because of the broken dependency. That means I can't upgrade anything else on my little box. What do I have to do to resolve this really irritating issue? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backup, move, restore..?
Hi all. I am needing to do a backup of two smalldatabases, both live on the same server and under the same Mysql binary installation... I would like to back them up, dump them into a file(s), make my move, install the same database, and then restore/load this dumped information back into the new database. I am using mysql-max 4.0.13 on HP-UX 11.00, 64 bit... I am also using some InnoDB tables... Is it as simple as doing mysqldumps and taring/zipping up those files? If so, how do I load these files back into the new database? Thanks Please Note The information in this E-mail message is legally privileged and confidential information intended only for the use of the individual(s) named above. If you, the reader of this message, are not the intended recipient, you are hereby notified that you should not further disseminate, distribute, or forward this E-mail message. If you have received this E-mail in error, please notify the sender. Thank you * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I have been using ODBC v3.51 to access MySQL DB's hosted by a RH v7.2 Linux VM for select purposes f
I have been using ODBC v3.51 to access MySQL DB's hosted by a RH v7.2 Linux VM for select purposes from within an application script. They are normally imported in the early morning. I now have a need to Insert into a table from an application script. The insert works when submitted through phpMyAdmin. However, when the Insert statement is submitted via the application script, I get: End Action [ failure 0x1602] in the application log. Any suggestions on how to proceed and deal with this issue would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help With a DATETIME Query PLEASE!
I'm under the impression that your over thinking the problem. LOOK-UP the functions DATE_SUB / INTERVAL / TIME_TO_SEC / TO_DAYS and the arithmetic should be easy. - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: shaun thornburgh [mailto:[EMAIL PROTECTED] --Sent: Wednesday, October 08, 2003 1:52 AM --To: [EMAIL PROTECTED] --Subject: Help With a DATETIME Query PLEASE! -- --Hi, -- --I have a table called Bookings which has two important columns; --Booking_Start_Date and Booking_End_Date. These columns are both of type --DATETIME. The following query calculates how many hours are available --between the hours of 09.00 and 17.30 so a user can see at a glance how --many --hours they have unbooked on a particular day (i.e. 8.5 hours less the --time --of any bookings on that day). However, when a booking spans more than one --day the query doesn't work, for example if a user has a booking that --starts --on day one at 09.00 and ends at 14.30 on the next day, the query returns --3.5 --hours for both days. Any help here would be greatly appreciated. -- --SELECT 8.5 - (SUM(((DATE_FORMAT(B.Booking_End_Date, '%k') * 60 ) + --DATE_FORMAT(B.Booking_End_Date, '%i')) - --((DATE_FORMAT(B.Booking_Start_Date, --'%k') * 60 ) + DATE_FORMAT(B.Booking_Start_Date, '%i'))) / 60) AS --Available_Hours FROM WMS_Bookings B WHERE B.User_ID = '16' AND --B.Booking_Status '1' AND NOT ( '2003-10-07' --DATE_FORMAT(Booking_Start_Date, %Y-%m-%d) OR '2003-10-07' --DATE_FORMAT(Booking_End_Date, %Y-%m-%d) ) -- --Thanks for your help -- --_ --Find a cheaper internet access deal - choose one to suit you. --http://www.msn.co.uk/internetaccess -- -- --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]
deleting semi-duplicate records
Let's say I have the following table (t1): ++--++ | id | c1 | c2 | ++--++ | 1 | NULL | 1 | | 2 | a| 1 | | 3 | NULL | 2 | | 4 | b| 2 | | 5 | NULL | 3 | | 6 | c| 3 | | 7 | NULL | 4 | ++--++ I would like to delete all rows that c1 = NULL *and* have a duplicate c2. So, in this case I want to eliminate rows 1,3, and 5. So my question is two-fold: 1) What's a good command way to do this? 2) I tried an UPDATE/DELETE combo: UPDATE t1 LEFT JOIN t1 as t2 ON (t1.c2 = t2.c2 AND t2.c1 IS NOT NULL) SET t1.c2 = NULL; DELETE FROM t1 WHERE c1=NULL and c2 =NULL; This would only replace and delete one record at a time (so I would need to run it 3 times). Why is it only updating one row? Why not all matches or no matches? Thanks, Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Errors starting Mysql
I have installed Mysql 2.23.56 in Linux (Cobalt), but when it is started shows the next messages in the ../var/*.err file: mysqld started /home/mysql/libexec/mysqld: File './dns1-bin.1' not found (Errcode: 13) 031008 15:18:16 Could not use dns1-bin for logging (error 13) 031008 15:18:16 /home/mysql/libexec/mysqld: Can't create/write to file '/home/mysql/var/dns1.sinfin.net.mx.pid' (Errcode: 13) 031008 15:18:16 /home/mysql/libexec/mysqld: Can't find file: './mysql/host.frm' (errno: 13) 031008 15:18:16 /home/mysql/libexec/mysqld: Error on delete of '/home/mysql/var/dns1.sinfin.net.mx.pid' (Errcode: 13) 031008 15:18:16 mysqld ended I changed group write permissions to ../var directory, but the other files about error 13 I don't know how to fix them
Innodb won't recognize index when optimizing query
The query optimizer will not recognize an index on an innodb table. tranfer_logs is an innodb table, auth_user is not. As demonstrated below, trans_team is not even recognized as a possible key when EXPLAIN SELECT is used. (my apologies for the extra wide message). I am not sure if this is an innodb issue or just an ignorant user(me) issue. mysql show keys from transfer_logs; +---+++--+-+---+-+--++ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | +---+++--+-+---+-+--++ | transfer_logs | 0 | PRIMARY|1 | trans_id| A | 3573681 | NULL | NULL | | transfer_logs | 1 | trans_time |1 | trans_time | A | 3573681 | NULL | NULL | | transfer_logs | 1 | user_id|1 | user_id | A | 11872 | NULL | NULL | | transfer_logs | 1 | event_id |1 | event_id| A | 108293 | NULL | NULL | | transfer_logs | 1 | trans_team |1 | trans_team | A | 1786840 | NULL | NULL | | transfer_logs | 1 | trans_type |1 | trans_type | A | 21 | NULL | NULL | | transfer_logs | 1 | trans_cat |1 | trans_cat | A | 21 | NULL | NULL | +---+++--+-+---+-+--++ mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees - FROM transfer_logs, auth_user - WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---+--+--+--+-+---+--++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+--+--+--+-+---+--++ | auth_user | ref | PRIMARY,user_ref | user_ref | 20 | const | 13 | where used | | transfer_logs | ref | trans_time,user_id,trans_cat | user_id | 32 | auth_user.user_id | 301 | where used | +---+--+--+--+-+---+--++ As witnessed above, possible_keys doesn't even list trans_team as a possible index. I want to use the index on trans_team but when I try to force this via USE INDEX mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees -FROM transfer_logs USE INDEX(trans_team), auth_user -WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---++--+-+-+---+-++ | table | type | possible_keys| key | key_len | ref | rows| Extra | +---++--+-+-+---+-++ | transfer_logs | ALL| trans_time,user_id,trans_cat | NULL|NULL | NULL | 2036463 | where used | | auth_user | eq_ref | PRIMARY,user_ref | PRIMARY | 32 | transfer_logs.user_id | 1 | where used | +---++--+-+-+---+-++ 2 rows in set (0.00 sec) Any ideas on wny innodb won't recognize the index on trans_team? This query shouldn't take 1 minute+ but it does unless I can force the optimizer to use the proper index. ANALYZE TABLE has no effect on innodb tables, correct? As you can see above even trying to use USE INDEX doesn't get the optimizer to behave. This is on MysQL 3.23.58-Max. Thanks, sql query
RE: Easy (?) conditional SELECT
Try the following: SELECT a.* FROM artifacts a left join artifacts higher on a.product_id = higher.product_id and higher.plan_submission_number a.plan_submission_number WHERE a.product_id = '1' AND higher.product_id is null; For anyone following this, I'm using some sample code, which should always be included when you want someone to try to solve your SQL problems. Please correct me if this is inappropriate data. drop table if exists artifacts; create table artifacts ( product_id int, plan_submission_number int ); insert into artifacts values ( 1, 1 ); -- is highest for product_id 1. insert into artifacts values ( 2, 1 ); insert into artifacts values ( 2, 2 ); -- is highest for product_id 2. insert into artifacts values ( 3, 1 ); insert into artifacts values ( 3, 2 ); insert into artifacts values ( 3, 3 ); -- is highest for product_id 3. Luckily, LEFT JOIN solves many problems for you. By self-joining the table, and looking for adjoining records to have a higher plan number, you'll have table instance 'higher' always greater than those in 'a'. And with the left join, you'll show 'a' even when there's no 'higher' record meeting that qualification. Now add on the filter 'higher.product_id is null' and you'll only show those records from 'a' where there is no matching higher record. As long as product_id is indexed and very selective (relatively few plans per product_id), this will be an efficient query. Hope that helps, Kevin -Original Message- From: Mark Wilson [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 08, 2003 6:35 AM To: Mysql Mailing List Subject: Easy (?) conditional SELECT I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: MySQL not null vs MSAccess required
Now that I listen to you Michael, you must probably know how MySQL refers to NULL values on a logical sentence, e.g. If(something=NULL...) How do I get TRUE on a logical sentence if I want to refer to it as If it is NULL do...? Thanks on advance, Miguel Ernesto -Mensaje original- De: Michael Stassen [mailto:[EMAIL PROTECTED] Enviado el: Martes, 07 de Octubre de 2003 17:46 Para: [EMAIL PROTECTED] Asunto: Re: FW: MySQL not null vs MSAccess required Cal Evans wrote: I humbly submit an apology. You are correct. This is a bug (No it is NOT a feature) While you may not like it, this definitely is a feature (or an intentional design decision, at least), not a bug. See the docs at http://www.mysql.com/doc/en/constraint_NOT_NULL.html. The first line is, To be able to support easy handling of non-transactional tables, all fields in MySQL have default values. So, if you don't set a default for a column, mysql chooses one for you. With a few exceptions, NULLable columns default to NULL, NOT NULL columns default to 0 (zero) or '' (empty string). You can change this behavior by building your own mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option. you should be able to define a field as NOT NULL without a default or at the very least, define the default as NULL. This does not make sense to me. Allowing NULL as the default for a column declared NOT NULL would defeat the purpose of declaring it NOT NULL in the first place. Michael -- 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]
collation in a multi-language text field.
I have imported 1000s of records into a table and even spent some time cleaning up characters which I messed up as I imported them (not the fault of the DB layer, but the result of bringing things into a text editor), but now I have the following problem. I have a whole series of author and book/article names for bibliographic entries. I am using whatever character set and collation was given by default when I installed MySQL and imported the data. I now have trouble ordering those with diacriticals. With no explicit collation nor character sets specified many (most? all?) diacritically marked characters follow the English characters. My problem is that what I am sorting is a long list of bibliographic entries which are in various European languages including French, Spanish and German. I have read some of the collation sequence documentation and the use of characters sets and I'm not sure how to proceed. Would I just define an appropriate character set for the author and title columns? OR Would I specify an explicit character set in the select. These are important fields and secondary key fields for this data. Does that make a difference to the appropriate approach I should take? Which is more approach is more efficient for a table which has many times more reads than writes. Help! -Paul -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Easy (?) conditional SELECT
You cant have subqueries (select from where) in the where clause. On Wed, 2003-10-08 at 15:00, Mark Wilson wrote: BTW, I tested the SELECT(MAX) part of this separately, and discovered that MySQL doesn't like the single quotes around the table name, so I took them out. Now THIS query works: SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1' -- (returns '2') But this one still doesn't: SELECT * from artifacts WHERE (product_id = '1' AND plan_submission_number = (SELECT MAX( plan_submission_number ) FROM artifacts WHERE product_id = '1') ) Although that seems as if it should be equivalent to the following hard-coded version: SELECT * FROM `artifacts` WHERE (product_id = '1' AND plan_submission_number = '2') -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 Quoting Mark Wilson [EMAIL PROTECTED]: I have an app for which people can submit plans. Each plan relates to a particular product. A new plan can be submitted for the same product, so each plan has its own submission number. (1,2,3...) Each plan is composed of artifacts. The (artifacts) table looks like this: artifact_id INT product_id INT plan_submission_number INT (etc) Task: get all the items for the most recent (i.e., highest) submission plan for a particular product. Since I'm relatively new to MySQL, and haven't mastered much beyond the most basic SELECTs, much less JOINs, I'm not sure how to do this. I think the following should work (for product_id = 1), but it returns a syntax error. SELECT * FROM `artifacts` WHERE ( product_id = '1' AND plan_submission_number = ( SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE product_id = '1' ) ) --- Error message: 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 'SELECT MAX( plan_submission_number ) FROM 'artifacts' WHERE p --- What am I missing? Thanks - Mark -- Mark Wilson, Computer Programming Unlimited (cpuworks.com) Web : http://cpuworks.com Tel: 410-549-6006 Email: [EMAIL PROTECTED] Fax: 410-549-4408 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- * Ben Edwards Tel +44 (0)1179 553 551 ICQ 42000477 * * Homepage - nothing of interest here http://gurtlush.org.uk * * Webhosting for the masses http://www.serverone.co.uk * * Critical Site Builderhttp://www.criticaldistribution.com * * online collaborative web authoring content management system * * Get alt news/views films online http://www.cultureshop.org * * i-Contact Progressive Video http://www.videonetwork.org * * Fun corporate graphics http://www.subvertise.org * * Bristol Indymedia http://bristol.indymedia.org * * Bristol's radical news http://www.bristle.org.uk * -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb won't recognize index when optimizing query
Hi Heath, MySQL cannot use the trans_team query because you're using !=, for which an index is never used (currently anyway). Do you think that trans_team is the best index that will find the least rows and produce the fastest result? If so, you can try using the following, which can be optimized, instead of != ... AND (transfer_logs.trans_team 'team oscar' OR transfer_logs.trans_team 'team oscar') I think that's the same as !=. :-) But MySQL will only use the index if it will find few enough rows ( ~30%) -- in other words, if more than ~2/3 of the trans_team values ARE 'team oscar'. Hope that helps. Matt - Original Message - From: heath boutwell Sent: Wednesday, October 08, 2003 3:15 PM Subject: Innodb won't recognize index when optimizing query The query optimizer will not recognize an index on an innodb table. tranfer_logs is an innodb table, auth_user is not. As demonstrated below, trans_team is not even recognized as a possible key when EXPLAIN SELECT is used. (my apologies for the extra wide message). I am not sure if this is an innodb issue or just an ignorant user(me) issue. mysql show keys from transfer_logs; +---+++--+-+ ---+-+--++ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | +---+++--+-+ ---+-+--++ | transfer_logs | 0 | PRIMARY|1 | trans_id | A | 3573681 | NULL | NULL | | transfer_logs | 1 | trans_time |1 | trans_time | A | 3573681 | NULL | NULL | | transfer_logs | 1 | user_id|1 | user_id | A | 11872 | NULL | NULL | | transfer_logs | 1 | event_id |1 | event_id | A | 108293 | NULL | NULL | | transfer_logs | 1 | trans_team |1 | trans_team | A | 1786840 | NULL | NULL | | transfer_logs | 1 | trans_type |1 | trans_type | A | 21 | NULL | NULL | | transfer_logs | 1 | trans_cat |1 | trans_cat | A | 21 | NULL | NULL | +---+++--+-+ ---+-+--++ mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees - FROM transfer_logs, auth_user - WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---+--+--+--+-- ---+---+--++ | table | type | possible_keys| key | key_len | ref | rows | Extra | +---+--+--+--+-- ---+---+--++ | auth_user | ref | PRIMARY,user_ref | user_ref | 20 | const | 13 | where used | | transfer_logs | ref | trans_time,user_id,trans_cat | user_id | 32 | auth_user.user_id | 301 | where used | +---+--+--+--+-- ---+---+--++ As witnessed above, possible_keys doesn't even list trans_team as a possible index. I want to use the index on trans_team but when I try to force this via USE INDEX mysql EXPLAIN SELECT - SUM(transfer_logs.trans_net)/100 AS all_fees -FROM transfer_logs USE INDEX(trans_team), auth_user -WHERE - auth_user.user_ref = '37' - transfer_logs.user_id = auth_user.user_id - transfer_logs.trans_time 20031008153915 - transfer_logs.trans_cat = 'deposit' - transfer_logs.trans_app = 't' - transfer_logs.trans_team != 'team oscar'; +---++--+-+- +---+-++ | table | type | possible_keys| key | key_len | ref | rows| Extra | +---++--+-+- +---+-++ | transfer_logs | ALL| trans_time,user_id,trans_cat | NULL| NULL | NULL | 2036463 | where used | | auth_user | eq_ref | PRIMARY,user_ref | PRIMARY | 32 | transfer_logs.user_id | 1 | where used | +---++--+-+- +---+-++ 2 rows in set (0.00 sec) Any ideas on wny innodb won't recognize the index on trans_team? This query shouldn't take 1 minute+ but it does unless I
select from two tables then create a new table
Hi all: There is a question: Two tables: table_out: ( fields ) product_name,out_count , out_date table_in: ( fields ) product_name,in_count , in_date I want to create a table that show the remain of product. Can you help me?? thanks in advance! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trouble acquiring proper AVG with TIME
Hello, I've searched the manuals and any websites I can find but am still at a loss on how to calculate what I thought was going to be an easy 'Average' of Call Times. Situation. Have a table which is updated daily for users metrics and have configured one column as TIME which should have the default format (if I understand correctly) of HH:MM:SS. However when I try to calculate an average for an individual who has the following two times listed: 00:09:15 00:04:26 it calculates the average as being 670.5 The query I'm running is SELECT AVG(avg_tt) FROM table WHERE agent_ln = name It is changing the times to 915 + 426 = 1341/2 = 670.5 Does anyone know what I'm doing wrong? Much appreciated!! Chuck _ Tired of spam? Get advanced junk mail protection with MSN 8. http://join.msn.com/?page=features/junkmail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
moving a column to new table
I have a table let's call it (old) containing items of interest index field (id) another field (data) I want the data colum to move to a normalized table (normal) that uses id as part of a multifield primary key. So I created a data column in the normalized table. How can I now ELEGANTLY update the normal table so the values for data are the same as the old table. What I want to do is this: UPDATE normal SET data = old.data WHERE normal.id = old.id except that this doesn't work. THANKS! Orni -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Innodb won't recognize index when optimizing query
Matt, Thank you for the help and you are correct the index can't be utilized when using !=. I will try your hack/work around. Thanks again. sql,query Hi Heath, MySQL cannot use the trans_team query because you're using !=, for which an index is never used (currently anyway). Do you think that trans_team is the best index that will find the least rows and produce the fastest result? If so, you can try using the following, which can be optimized, instead of != ... AND (transfer_logs.trans_team 'team oscar' OR transfer_logs.trans_team 'team oscar') I think that's the same as !=. :-) But MySQL will only use the index if it will find few enough rows ( ~30%) -- in other words, if more than ~2/3 of the trans_team values ARE 'team oscar'. Hope that helps. Matt __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MacOSX 4.0.15 mysqld_safe restarts after STOP
Bruce Dembecki wrote: Hi! I'm struggling to understand how to fix the mysqld_safe script for an OSX machine. If I run mysql.server stop then the mysqld process is killed and mysqld_safe promptly starts it again. I know mysqld_safe is supposed to start mysqld if it stops but I also know there are times I need to work on files and so on and when you need to stop it you need to stop it... at least on Linux you can kill mysqld_safe do a ps -aux | grep mysql then start killing those processe with kill -9 usually after killing 2-3 processes all others vanish, too in general this shouldn't be necessary. a shutdown with mysqladmin should put all of mysql to sleep cleanly. usually I run in this issue when I graft the mysql.com binary on a SuSE distribution without having set the necessary symlinks so all apps and libs and pid and socket find each other. (Why cant those *you know what* use proper directory tree like mysql.com wants it to build the distri ?) without this precaution the server comes up and mysqladmin cant talk to the server and thus cant stop it savely. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: moving a column to new table
My first thought would be to create an empty table call it new_table set it up like you want. then INSERT INTO new_table SELECT n.*,a.data FROM normal n LEFT JOIN another a ON o.id=a.id ORDER BY (n.id) and then verify it got there, and drop the other tables and rename the new_table to normal table. Kelley Colleen Dick wrote: I have a table let's call it (old) containing items of interest index field (id) another field (data) I want the data colum to move to a normalized table (normal) that uses id as part of a multifield primary key. So I created a data column in the normalized table. How can I now ELEGANTLY update the normal table so the values for data are the same as the old table. What I want to do is this: UPDATE normal SET data = old.data WHERE normal.id = old.id except that this doesn't work. THANKS! Orni -- 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: FW: MySQL not null vs MSAccess required
To test for NULL, you use the IS NULL comparison operator. SELECT * FROM table_name WHERE col_name IS NULL; See http://www.mysql.com/doc/en/Comparison_Operators.html in the manual for more. Michael Miguel Ernesto wrote: Now that I listen to you Michael, you must probably know how MySQL refers to NULL values on a logical sentence, e.g. If(something=NULL...) How do I get TRUE on a logical sentence if I want to refer to it as If it is NULL do...? Thanks on advance, Miguel Ernesto -Mensaje original- De: Michael Stassen [mailto:[EMAIL PROTECTED] Enviado el: Martes, 07 de Octubre de 2003 17:46 Para: [EMAIL PROTECTED] Asunto: Re: FW: MySQL not null vs MSAccess required Cal Evans wrote: I humbly submit an apology. You are correct. This is a bug (No it is NOT a feature) While you may not like it, this definitely is a feature (or an intentional design decision, at least), not a bug. See the docs at http://www.mysql.com/doc/en/constraint_NOT_NULL.html. The first line is, To be able to support easy handling of non-transactional tables, all fields in MySQL have default values. So, if you don't set a default for a column, mysql chooses one for you. With a few exceptions, NULLable columns default to NULL, NOT NULL columns default to 0 (zero) or '' (empty string). You can change this behavior by building your own mysql from source with the -DDONT_USE_DEFAULT_FIELDS compile option. you should be able to define a field as NOT NULL without a default or at the very least, define the default as NULL. This does not make sense to me. Allowing NULL as the default for a column declared NOT NULL would defeat the purpose of declaring it NOT NULL in the first place. Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
duplicating databases
I am running PHP/MySQL for a program that I am writing. We will have 100's or 1000's of databases that will be duplicates in structure. The problem is when I make a change to the database, I have to go to every database manually and make the change. All of the databases start with a_ and are on the same server. I would like to be able to have one master database and then run a script when I make a change to it that will get the structure of the master database and check all of the other databases to make sure that their structures match. If not, it will make the change to the other databases. Does anybody know if there is all ready a program out there that would do this? Can anybody point me in the right direction? Or if it is only a few lines of script that somebody all ready has to do this, can you share it? Thank You Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: duplicating databases
Check out sqlyog. (www.sqlyog.com) It has a structure sync tool. After making the first change, you could generate a script to make the change to the next database and then take that script and parameterize it so that a PHP script could use it to update all of your database. HOWEVER, A better way to do this may be to re-think your entire schema. Instead of keeping the data in separate but identical databases, put it all in one database but add a key (owner, database, etc) to each table. Then in your selects, simply add to your where clause AND ownerID=x. This way you only have 1 database but your data is kept separate. (Until MySQL comes out with views, then it gets a lot easier.) HTH, =C= * Cal Evans * http://www.eicc.com * We take care of your IT, * So you can take care of your business. * * I think inside the sphere. Steve Buehler wrote: I am running PHP/MySQL for a program that I am writing. We will have 100's or 1000's of databases that will be duplicates in structure. The problem is when I make a change to the database, I have to go to every database manually and make the change. All of the databases start with a_ and are on the same server. I would like to be able to have one master database and then run a script when I make a change to it that will get the structure of the master database and check all of the other databases to make sure that their structures match. If not, it will make the change to the other databases. Does anybody know if there is all ready a program out there that would do this? Can anybody point me in the right direction? Or if it is only a few lines of script that somebody all ready has to do this, can you share it? Thank You Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
