Re: 4.1 character set documentation
Paul DuBois advised us that Alexander Barkov and Peter Gulutzan have written up some documentation on the new character set support in MySQL 4.1, which has now been added to the online manual. You can read it here: http://www.mysql.com/doc/en/Charset.html Question about this: In UCS-2 (binary Unicode representation) every character is represented by a two-byte Unicode code ... Should I read this to imply that the current level of support is BMP only? Note that this documentation actually is ahead of the current release (4.1.0) because it is current for 4.1.1 and some things have changed since 4.1.0. -- Joel Rees [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to import MySQL tables from Linux?
http://www.mysql.com/doc/en/mysql.html In the MySQL client, end your command line statement with \. From the shell, use this syntax: shell mysql database script.sql output.tab Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: CM Miller [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 20:31 Subject: How to import MySQL tables from Linux? Instead of creating tables manually in MySQL, I would like to import them. I have done this under Oracle, with the following @a:\sqlfiles\foo.sql How do I import sql files into mysql using Linux? I have a directory under /root/sql thanks -Chris = GTFG GAIM ID: cmmiller1973 __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.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: Second Auto-increment
While I'm not sure I understand your intention, it seems to me like you need another table to hold your data. As Paul stated, AUTO_INCREMENT doesn't work this way. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Q Zantos [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 22:43 Subject: Second Auto-increment I would like to auto-increment within an auto-incremented value. For example, if I have a rental agreement(auto-incremented) and that rental agreement becomes month-to-month. I was thinking that I would want to track the receipts as such: Contract # | Type | Second Auto # | Duration |payment total 1 | 1 | 1 | 6 months| 6,000 1 | 2 | 1 | 1 month | 1,000 1 | 2 | 2 | 1 month | 1,000 As you can see, the contract stays the same (1), but the Contract Type changes to 2(month-to-month). When this happens, the first transaction is reset to auto-increment from that point forward. The third record illustrates the second auto-incremented #, within the Type #2. Although I have seen many auto-incremented examples, I have yet to see one that auto-increments within another auto-incremented value. Any suggestions or examples would be appreciated. Thanks. - Do you Yahoo!? The New Yahoo! Search - Faster. Easier. Bingo. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about replication
On Thu, Jun 12, 2003 at 09:51:06AM +0800, MaFai wrote: Hello, mysql, 2 Mysql 4.0.12 windows version with INNODB replciate sucessfully,both in myisam and innodb table. According to mysql manual(PDF),there is only one master database in the same time,but with many slave server. Right. Master for query,and slave for update or insert operation. Right. Therefore,while programming with JAVA tech,I must write 2 connection pool? one for query,other for update? If you're using pooling, yes. That's not very proper.Coze the programe switch the connection frequently.It really slow down the web application performance. Can you explain how? I know of many people and companies doing this. They've never found the swtiching connections to be an expensive operation. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 8 days, processed 287,018,968 queries (370/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can't insert data from Apache/PHP
having register_globals 'on' is only a security risk if the code is sloppy. We're in agreement here, Jay. I see turning register_globals off as a band-aid fix for poor coding rather than a rule of thumb. That 'off' was made the default only recently emphasizes this, in my opinion. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Jay Blanchard [EMAIL PROTECTED] To: CM Miller [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 08:04 Subject: RE: Can't insert data from Apache/PHP [snip] Sorry, but I am a bit behind on MySQL digest, but isn't turning Register Globals 'on' for php a security risk? [/snip] We recently had this discussion on the PHP-General list and the upshot is that having register_globals 'on' is only a security risk if the code is sloppy. PHP allows this as the variables are not strongly typed and most developer fail to do any checking of variables to make sure that they contain what they want them to contain. Turning register_globals 'off' does not take care of that. It just adds another layer of abstraction to certain vaariables (such as GET, POST, etc) which is still vulnerable if the developer does not do a good job of taking care with his or her variables. HTH! Jay -- 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]
BDB or InnoDB
Hi MySQL Fans, I am sure this question was asked before, but I did not find sufficient info, so please allow me to ask this question again. When comparing BDB and InnoDB which one would be (generally speaking) a better choice for a certain purpose ? I understood that BDB and InnoDB have basically similar features. Except BDB uses-page-level locking and InnoDB uses row-level-locking. There are some things which I am not so sure about. Does BDB support foreign keys ? Any response much appreciated. I searched through the archive back to June 2002 and in the info manual , but unfortunately I did not find what I was looking for. Did I miss something ? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query
Hi A query... is the bulk update call from JDBC supported by MySQL? Regards Deepak Saini **Disclaimer Information contained in this E-MAIL being proprietary to Wipro Limited is 'privileged' and 'confidential' and intended for use only by the individual or entity to which it is addressed. You are notified that any use, copying or dissemination of the information contained in the E-MAIL in any manner whatsoever is strictly prohibited. ***
Re: BDB or InnoDB
According to Sams Publishing (April 2002): The Berkeley DB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini table types. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 02:19 Subject: BDB or InnoDB Hi MySQL Fans, I am sure this question was asked before, but I did not find sufficient info, so please allow me to ask this question again. When comparing BDB and InnoDB which one would be (generally speaking) a better choice for a certain purpose ? I understood that BDB and InnoDB have basically similar features. Except BDB uses-page-level locking and InnoDB uses row-level-locking. There are some things which I am not so sure about. Does BDB support foreign keys ? Any response much appreciated. I searched through the archive back to June 2002 and in the info manual , but unfortunately I did not find what I was looking for. Did I miss something ? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- 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: BDB or InnoDB
Thanks Edward, Thank you that confirms what I found. I also found that BDB is not available on Mac and Linux (alpha) architecture. Best regards Nils Valentin Tokyo/Japan 2003 6 12 16:02Becoming Digital : According to Sams Publishing (April 2002): The Berkeley DB table type is a usable, transaction-safe table type, but it is not the most optimized table type in the mix. BDB tables support the basic elements of transactions as well as the AUTOCOMMIT variable, but are not as popular or as developed as the InnoDB or Gemini table types. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Nils Valentin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 02:19 Subject: BDB or InnoDB Hi MySQL Fans, I am sure this question was asked before, but I did not find sufficient info, so please allow me to ask this question again. When comparing BDB and InnoDB which one would be (generally speaking) a better choice for a certain purpose ? I understood that BDB and InnoDB have basically similar features. Except BDB uses-page-level locking and InnoDB uses row-level-locking. There are some things which I am not so sure about. Does BDB support foreign keys ? Any response much appreciated. I searched through the archive back to June 2002 and in the info manual , but unfortunately I did not find what I was looking for. Did I miss something ? -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
memory setup
Hello, anybody knows if I can setup the memory usage under MySQL 4.1.0? I need to set the min and max otherwise my pc gets really slow because I load a huge script on MyCC. thanks George Christoforakis
Re: my.cnf
* Paul DuBois You can relocate the data directory at server startup time with a --datadir option. But when looking for my.cnf files, the server will continue to look in the hardwired directory, if it exists. That's what that sentence means. (The server still looks in /etc/my.cnf; that doesn't change even if you use --datadir.) However, can't you start the mysqld daemon with '--basedir=/mysuperdrive' and mount the separate disk drive on /mysuperdrive. Then all /var, /etc, /tmp files will be resolved relative to /mysuperdrive? (Haven't tried though.) -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: memory setup
Please check the manual. http://www.mysql.com/doc/en/Memory_use.html Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: George Christoforakis [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 04:07 Subject: memory setup Hello, anybody knows if I can setup the memory usage under MySQL 4.1.0? I need to set the min and max otherwise my pc gets really slow because I load a huge script on MyCC. thanks George Christoforakis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forming foreign keys
Nils Valentin [EMAIL PROTECTED] wrote: Thank you for the reply. I understand now that it must be the first part of the primary index in both related tables. But what I still dont understand is the following: How do I create several foreign keys in a single table (f.e a link table) which relates f.e to 10 other tables ? For example like that: mysql CREATE TABLE p1(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql CREATE TABLE p2(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE child( - id1 INT, - id2 INT, - INDEX(id1), - INDEX(id2), - FOREIGN KEY (id1) REFERENCES p1(id), - FOREIGN KEY (id2) REFERENCES p2(id))TYPE=InnoDB; Query OK, 0 rows affected (0.01 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: left join
Fabio Bernardo [EMAIL PROTECTED] wrote: Which mysql?s version is able to make sub selects statament??? Since 4.1 And left join? could you there give me some left join examples??? thanks a lot Here is you can find syntax and examples of using JOINs: http://www.mysql.com/doc/en/JOIN.html -- 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: Problem with MAX()
Chris Boget [EMAIL PROTECTED] wrote: We are running v4.0.12. Consider the following: mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; ++ | assets | ++ | NULL | ++ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd' AND - assets IS NOT NULL; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) mysql SELECT MAX(assets) as assets - FROM do_deductibles - WHERE currency = 'usd'; +--+ | assets | +--+ | 1000 | +--+ 1 row in set (0.00 sec) What's going on with the MAX() function? Why did it return NULL in the first query above. There were no adding/deleting data between the above queries and yet MySQL didn't pull the MAX from the table until I added the AND assets IS NOT NULL to the query. And yet after I ran that query, attempting to run the first query yields the proper results. Is this a bug with MySQL? I wasn't able to repeat it on my test table. Could you provide a test case? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
link err in CYGWIN32
hi all, I install mysql4.0.13 client library in CYGWIN32 , i write a simple data.c to test mysql , but link error $ gcc data.c -lmysqlclient /usr/lib/gcc-lib/i686-pc-cygwin/3.2/../../../libmysqlclient.a(my_compress.o)(.te xt+0x8b):my_compress.c: undefined reference to `_compress' /usr/lib/gcc-lib/i686-pc-cygwin/3.2/../../../libmysqlclient.a(my_compress.o)(.te xt+0x16d):my_compress.c: undefined reference to `_compress' /usr/lib/gcc-lib/i686-pc-cygwin/3.2/../../../libmysqlclient.a(my_compress.o)(.te xt+0x210):my_compress.c: undefined reference to `_uncompress' collect2: ld returned 1 exit status gcc version 3.2 20020927 which lib is must add? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
tables
Hi, I have this: ++---++ ¦ Jahr ¦ Name ¦ Budget ¦ ++---++ 2003 Hans 2000 2003 Fritz 5000 2004 Hans 1500 2005 Pia 3500 How is it possible to make this in sql? +-+ ¦ 2003 Hans2000 ¦ ¦ 2003 Fritz 5000 ¦ +-+ ¦ Total 7000 ¦ +-+ +-+ ¦ 2004 Hans1500 ¦ +-+ ¦ Total 1500 ¦ +-+ +-+ ¦ 2005Pia 3500 ¦ +-+ ¦ Total 3500 ¦ +-+ Thanks a lot, Glenn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: forming foreign keys
Hi Victoria, yes... we come closer now. I can already spot my 2nd mistake which I made. ;-) I was believing that the index setup in table child for the foreign key must setup as primary key (talking about id1 and id2). That would explain the error messages to me. That leaves only a few questions now. I will try it now and feedback shortly. Best regards Nils Valentin Tokyo/Japan 2003 6 11 22:41Victoria Reznichenko : Nils Valentin [EMAIL PROTECTED] wrote: Thank you for the reply. I understand now that it must be the first part of the primary index in both related tables. But what I still dont understand is the following: How do I create several foreign keys in a single table (f.e a link table) which relates f.e to 10 other tables ? For example like that: mysql CREATE TABLE p1(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.03 sec) mysql CREATE TABLE p2(id INT PRIMARY KEY)TYPE=INNODB; Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE child( - id1 INT, - id2 INT, - INDEX(id1), - INDEX(id2), - FOREIGN KEY (id1) REFERENCES p1(id), - FOREIGN KEY (id2) REFERENCES p2(id))TYPE=InnoDB; Query OK, 0 rows affected (0.01 sec) -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete with Left Join???
Hi, I'm using: mysql Ver 11.18 Distrib 3.23.53a, for pc-linux-gnu (i686) I did the following query succesfully SELECT * FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL It returned all the row that was not in the right table. Know I want them deleted, but using: DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL gives me an error: mysql DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL; ERROR 1064: You have an error in your SQL syntax near 'LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL' at line 1 what is going on here and can someone help me?? thx Cecil Mnr Cecil J.C. Brand Internet Office RekenaarDienste University of the Free State/ Universiteit van die Vrystaat PO Box 339 Bloemfontein 9300 South Africa Tel :401 2645 Email :[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sub select
Mauro Andre Flores dos Santos [EMAIL PROTECTED] wrote: I've just downloaded the version 4.1. I tryed to run my first sub-select : SELECT curso.* FROM curso where curso.cdCurso IN (SELECT EstabMantemCurso.cdCurso FROM EstabMantemCurso) The following ERROR returned: [local] ERROR 1235: This version of MySQL doesn't yet support 'LIMIT IN/ALL/ANY/SOME subquery' The version is: 4.1.0-alpha-max-debug for Win95/Win98 Doesn't version 4.1 support yet sub-selects ? It supports subselects, but without LIMIT clause in the nested query. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Delete with Left Join???
Cecil Brand [EMAIL PROTECTED] wrote: I'm using: mysql Ver 11.18 Distrib 3.23.53a, for pc-linux-gnu (i686) I did the following query succesfully SELECT * FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL It returned all the row that was not in the right table. Know I want them deleted, but using: DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL gives me an error: mysql DELETE FROM staff_user LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL; ERROR 1064: You have an error in your SQL syntax near 'LEFT OUTER JOIN staff ON SCode1 = staff_SCode WHERE SCode1 IS NULL' at line 1 what is going on here and can someone help me?? 3.23.53 doesn't support multi-table deletes. It's supported since 4.0: http://www.mysql.com/doc/en/DELETE.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Newbie Question
Peter Burden [EMAIL PROTECTED] wrote: I'm sure there's an answer somewhere but the 900+ page manual is a bit overwhelming. I have MySQL 4.1 running on one machine and I'd like to access using the client software I alerady have on another machine which was built against 3.23.33. Access is via command line client (mysql), PHP and C-API - all obviously built against the same client library. I gather from the manual (brief note at top of page 222) that 4.1 has a different way of handling passwords which is why a mysql connection failed with error 1249 about authentication protocols. Is there any way round this? I really don't want to have two separate sets of client stuff around and I'm not ready to switch a production system to 4.1 You can run mysqld with --old-passwords option or put old-passwords to the my.cnf: [mysqld] old-passwords -- 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: same query different result on slightly different db
Giovanna Pavarani [EMAIL PROTECTED] wrote: i'm sorry if this kind of question was already posted, i'm a newbie and i hope you will find anyway the time to answer me. I've installed mysql version 4.0.12-nt on my Windows XP Professional notebook and mysql version 4.0.12-standard on a Solaris 8 machine. Some antefacts: I need a very very small database to keep track of a set of discussion topics, a set of users and the subscriptions of the users to the discussion topics. This is my database (a poor designed one, i know): [skip] what i'd need is to present the user a list of the topics that he's not yet subscribed. I read the MySQL manual and I found that NOT IN and the nested SELECTs are not supported in this version, so I ended up to use temporary tables. These are the queries that i use: CREATE TEMPORARY TABLE user03 (TopicName varchar(40)); INSERT INTO user03 SELECT TopicName FROM subscriptions WHERE SipUri='[EMAIL PROTECTED]'; SELECT topics.TopicName FROM topics,user03 WHERE topics.TopicName != user03.TopicName; It's not correct query, because it gives you cartesian product of not equal TopicName. As to query with NOT IN you can rewrite it with LEFT JOIN as described at: http://www.mysql.com/doc/en/ANSI_diff_Sub-selects.html They are working fine on XP, the result is the list of topics at which user03 isn't subscribed yet. But on Solaris, with the same database, the result is a list of all the topics, repeated a certain number of times. I'm stucked on this, is it possible that the problem is the 4.0.12-standard version? Should I use the 4.0.12-max version? Or am i missing something? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Problem in UNION clause
Asish Samanta [EMAIL PROTECTED] wrote: When I run the sql query : select a.group_id as Select, a.group_name as Group Name, date_format(a.group_created_date,%M %e, %Y %H:%i) as Created Date, date_format(a.last_modification_date,%M %e, %Y %H:%i) as Last Modified, a.learning_style as Style , count(c.group_id) as No. of Members from student_group a left join student_group_association c on a.group_id = c.group_id where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id union select a.group_id as Select, a.group_name as Group Name, date_format(a.group_created_date,%M %e, %Y %H:%i) as Created Date, date_format(a.last_modification_date,%M %e, %Y %H:%i) as Last Modified, a.learning_style as Style , count(c.group_id) as No. of Members from student_group a left join student_group_association c on a.group_id = c.group_id where a.group_id 'FIN' group by c.group_id, a.group_name, a.group_id; the buggy result will come: ++-+++--++ | Select | Group Name | Created Date | Last Modified | Style | No. of Members | ++-+++--++ | FIN| Finance | June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced | 1 | | DEV| Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert | 0 | ++-+++--++ 2 rows in set (0.00 sec). Actually in the count column will be 0 but in first row it shows 1. If I ommit the Union clause, it shows the result properly: select a.group_id as Select, a.group_name as Group Name, date_format(a.group_created_date,%M %e, %Y %H:%i) as Created Date, date_format(a.last_modification_date,%M %e, %Y %H:%i) as Last Modified, a.learning_style as Style , count(c.group_id) as No. of Members from student_group a left join student_group_association c on a.group_id = c.group_id where a.group_id = 'FIN' group by c.group_id, a.group_name, a.group_id; +++++--++ | Select | Group Name | Created Date | Last Modified | Style| No. of Members | +++++--++ | FIN| Finance| June 6, 2003 19:32 | June 6, 2003 19:32 | Advanced | 0 | +++++--++ 1 row in set (0.00 sec) and ++-+++++ | Select | Group Name | Created Date | Last Modified | Style | No. of Members | ++-+++++ | DEV| Development | June 6, 2003 19:38 | June 6, 2003 19:38 | Expert | 0 | ++-+++++ 1 row in set (0.00 sec) I am usin MySQL 4.0.12 on Windows NT 4.0 Could you provide table structures and some data for testing? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: Need help with an update
On Mac OS X (also FreeBSD Unix), a new installation installs the new version in another folder and changes the /usr/local/mysql symbolic link to point to the new installation (in the same folder) but the old installation is intact there aswell. So navigate to /usr/local and see what you have there. When I upgraded to 4.0.12 I just moved the data folder from the old installation to the new installation. -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 1:53 AM To: [EMAIL PROTECTED] Subject: Re: Need help with an update The OS upgrade probably just over-wrote the old MySQL install. Hopefully it left the files intact and you can import them as described in the manual. FWIW, MySQL is at v4.0.13 and some major improvements came with v4. You might want to consider reading the upgrade section of the manual, too. ;) Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: System Administrator a.k.a. The Root of the Problem [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, 11 June, 2003 19:15 Subject: Need help with an update Here is the scenario. I was running 3.23.39 that came with BSD/OS 4.3 , All the databases were running active. I upgraded to BSD/OS 5.0 which has Mysql 3.23.49 and suddenly NO databases are seen. Everything is where is is supposed to be, but the mysqld is not seeing the DBs that were running with 3.23.39. What do I need to do to correct this?? -- 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]
Native MS Access 97 Searches from an Access Form
I'm running Access 97 as a front end to a MySQL ODBC linked table. Everything is working fine with the exception of long searches from within Access. In the past we would be able to go to any one of the fields from within the Form, and then do a Ctrl + F to find a record by whatever value we chose. Results would take about a second when they were native Access table searches. Since converting them it takes about 40 seconds or more.. I've indexed the fields, and optimized the tables, still long searches. All ODBC settings in the Control panel appear to be correct, ONLY Don't Optimize column width and Return Matching Rows are checked. Any Ideas? (Table is an 8 meg table with about 5 thousand records in it)
Instant Messaging
Sehr geehrte MySQL Mitarbeiter, Da ich aktuell an einer Diplomarbeit im Bereich Corporate Instant Messaging arbeite und dazu eine Umfrage gestartet habe, bitte ich Professionals die Instant Messaging einsetzen, implementieren oder administrieren, an meiner Umfrage teilzunehmen. Sie dauert keine 5 Minuten. Ich bedanke mich im Vorraus! Link ist unten angegeben, Gruß! Dennis Tabi http://home.arcor.de/tabde/ home.arcor.de/tabde/
Visual Foxpro
Are you still using VisualFoxPro as the programmig language in your project? If so, you can use SQL functions from VisualFoxPro to connect to mySQL using ODBC Install mySQL (if you have not allready do it ) Then install MyODBC-3.51.06.exe (downloadable from mySQL page) Create the ODBC bridge to mySQL with the ODBC Administrator If your project is OO then you can understand the next code I wrote (Whith this classes I connecto to Oracle, SQL Server and, of course mySQL ) COdbc ^ ^ | | COdbcSynchBatchMan COdbcSynchBatchNotrans ^ ^ ^ | | | CORACLECSQLSERVER CMYSQL Instantiate a CMYSQL object and you are ready * // define class COdbc as custom protected m_sClsNam protected m_sSourceName CO protected m_sUserID ops$jjr protected m_sPasswordjjr protected m_iConnectionIDnumero devuelto por sqlconnect protected m_sCursorResults cursor en que se devuelven los datos protected m_sCursorColumns cursor en que estan los nombres de las columnas SQLCOLUMNS protected m_sCursorTablescursor en que estan los nombres de la tablas SQLTABLES protected m_bInTransaction bandera para indicar si se esta en transaccion o no protected m_sErrorString cadena con mensaje de error protected m_sLogFile ruta y nombre de archivo log protected m_bLoggingOut ¿Logging? protected m_oLogFile CTextFile protected m_sSqlCmd protected m_sScriptsPath protected m_iIdiom idioma a usar para los nombres de meses ** * ts1: SourceName * ts2: UserId * ts3: Password procedure Init parameter ts1, ts2, ts3 this.m_sClsNam = 'Codbc' this.m_sSourceName = ts1 this.m_sUserID = ts2 this.m_sPassword = ts3 this.m_iConnectionID = -1 this.m_sCursorResults = '' this.m_sCursorColumns = '' this.m_sCursorTables = '' this.m_sErrorString = '' this.m_sLogFile = TEMPDIR + 'codbc.log' this.m_bLoggingOut = .F. this.m_sSqlCmd = '' this.m_sScriptsPath = 'd:\fpw25\importa\oracle\scripts\' this.m_iIdiom = THIs.GENGLISH() endproc * procedure destroy if this.m_bLoggingOut this.SetLogOff endif if this.m_iConnectionID # -1 this.Disconnect endif endproc function GStillExecuting return 0 ** function GFinished return 1 function GNoMoreData return 2 ** function GSystemTables return 'SYSTEM TABLE' function GTables return 'TABLE' *** function GViews return 'VIEW' function GDBComplete return 1 ** function GDBPrompt return 2 function GDBNoPrompt return 3 *** function GTransAuto return 1 * function GTransManual return 2 ** function GAsynchronous return .T. * function GSynchronous return .F. *** function GBatchMode return .T. * function GNoBatchMode return .F. function Connect local m.bRet ? 'COdbc::Connect()' m.bRet = .T. this.m_iConnectionID = SQLCONNECT( this.m_sSourceName, this.m_sUserID, this.m_sPassword ) ? this.m_iConnectionID if this.m_iConnectionID 0 this.GetError( this.m_iConnectionID ) this.m_iConnectionID = -1 m.bRet = .F. endif return m.bRet ***
Re: about replication
I've written my own MySQL specific connection pool for load balancing connections across a single master multi-slave setup. You get a handle to a Connection object by calling 'checkout' or 'checkoutReadonly' where 'checkout' returns a read/write connection to the master and 'checkoutReadonly' read-only returns a read-only connections to one of the slaves. Even though I'm using the code in a production environment, I've labeled it alpha code because their are tons of features that I want to add but haven't gotten around to as yet. You are welcome to inquire off-list about it if you are so inclined. Dane Foster - Original Message - From: MaFai [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, June 12, 2003 00:00 Subject: about replication Hello, mysql, 2 Mysql 4.0.12 windows version with INNODB replciate sucessfully,both in myisam and innodb table. According to mysql manual(PDF),there is only one master database in the same time,but with many slave server. Master for query,and slave for update or insert operation. Therefore,while programming with JAVA tech,I must write 2 connection pool? one for query,other for update? That's not very proper.Coze the programe switch the connection frequently.It really slow down the web application performance. How do you slove this problem?Any idea appreciate. Best regards. MaFai [EMAIL PROTECTED] 2003-06-12 -- MySQL Java Mailing List For list archives: http://lists.mysql.com/java 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]
Weird problem with differences MySQL 3.23.38 und 4.0.12
Hi everyone... I actually have two servers: 1. Server: MySQL 4.0.12 2. Server: MySQL 3.23.38 This query: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB' AND o_val = '1') ) GROUP BY o_obj HAVING (_69B96431 = '01') shows on server 1 (4.0.12): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) but on server 2 (3.23.38): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | NULL | NULL | | D27518B1 | 01| S | NULL | NULL | +--+---++---++ Please note the differences in fourth and fifth column while having absolutely identical tables and data. The following query works on 1. Server (4.0.12), but not on 2. Server (3.23.38): SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB') ) GROUP BY o_obj HAVING (_69B96431 = '01'); 1. Server says: +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) 2. Server says: Empty set (0.00 sec) Explain shows the folowing in column 'Extra': 1. Server: Using where; Using temporary; Using filesort 2. Server: where used; Using temporary All other columns are identical: table TBL_32BF90B0 type ALL possible_keys o_key,o_val key NULL key_len NULL ref NULL rows 312 The following query shows the same result on both servers: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431 FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') ) GROUP BY o_obj HAVING (_69B96431 = '01') Both servers say: +--+---++ | o_obj| _69B96431 | T_69B96431 | +--+---++ | 1672BE70 | 01| S | | D27518B1 | 01| S | +--+---++ 2 rows in set (0.01 sec) Now, why that? TIA, Axel.
RE: mySQL GUIs
I wrote my own admin tool. It runs in a browser under a Tango Application Server. It doesn't have all the features of some of the gui interfaces, but it has some features none of them have. I have the flexibility to add any new features I need, and I can access my client's databases from 2000 miles away. I'll give it away free to anyone who has a Tango or Witango Server. David Shelley President DMS Technical Services -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Anybody have any recommendations for one that runs on Mac OS X? Thanks! Justin On 6/12/03 9:21 AM, Adam Nelson [EMAIL PROTECTED] wrote: I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: my.cnf
At 10:22 +0200 6/12/03, Jon Haugsand wrote: * Paul DuBois You can relocate the data directory at server startup time with a --datadir option. But when looking for my.cnf files, the server will continue to look in the hardwired directory, if it exists. That's what that sentence means. (The server still looks in /etc/my.cnf; that doesn't change even if you use --datadir.) However, can't you start the mysqld daemon with '--basedir=/mysuperdrive' and mount the separate disk drive on /mysuperdrive. Then all /var, /etc, /tmp files will be resolved relative to /mysuperdrive? Yes, but that does not affect where the server looks for option files. In other words, what you say is true, but it is unrelated to what I said above. :-) (Haven't tried though.) -- Jon Haugsand, [EMAIL PROTECTED] http://www.norges-bank.no -- 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]
Re: tables
Hi Edward, thanks a lot for your hints. I will try it as soon as possible. Best regards, Glenn Becoming Digital [EMAIL PROTECTED] schrieb im Newsbeitrag news:[EMAIL PROTECTED] SQL isn't a spreadsheet program. It's a database programming language. As such, it's not going to output tables in the manner you want. From what I see, it looks like you want to get individual results and a total for each year. As best I can figure (without sub-selects), that will take two queries, one to get the per-year info, another to get the total of that info. SELECT * FROM table WHERE year=; SELECT sum(budget) FROM table; You then need some sort of scripting language to put this into the format you desire. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Glenn [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 06:25 Subject: tables Hi, I have this: ++---++ ¦ Jahr ¦ Name ¦ Budget ¦ ++---++ 2003 Hans 2000 2003 Fritz 5000 2004 Hans 1500 2005 Pia 3500 How is it possible to make this in sql? +-+ ¦ 2003 Hans2000 ¦ ¦ 2003 Fritz 5000 ¦ +-+ ¦ Total 7000 ¦ +-+ +-+ ¦ 2004 Hans1500 ¦ +-+ ¦ Total 1500 ¦ +-+ +-+ ¦ 2005Pia 3500 ¦ +-+ ¦ Total 3500 ¦ +-+ Thanks a lot, Glenn -- 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: mySQL GUIs
Hi Jim Mysql List members, sad to hear that there are still people around not using a propper OS ;-) - like a Linux, BSD or Unix based OS. Anyway as you asked about the best GUI for accesing MySQL - and as you asked the question you know there is only one best GUI ;-). Anything mentioned below is my personal opinion and I am ot related to tany of the parties mentioned. Any welcome to fire back on me ;-) EMS's MySQL Mangaer is to heavy loaded and the interface is anything else then intuitive - so kick it into the bucket ;-) There are many such tools around to be honest, but my absolute favourite one has to be DbVisualizer (www.minq.se) with a loong distance second . That has very good reasons which I am willing to explain. a) First the Menu is intuitive AND context sensitive. b) its fast c) it works d) its java e) its professionally done f) contains a chart software (yfiles from www.yworks.com) g) support replies under 10 minutes !! REGULARLY h) its not a windows copy GUI ;-) i) two license model j) any major OS supported About a) I want to explain a bit more. When you choose data and go to the export menu it will offer you to export as html or csv. If you choose to export the diagramme it will offer as choice jpeg or gif. So the menu changes (in the background) and you dont have to think about it. It also takes the millions off not needed options out of the menu simpler. Of course there is more but I believe that I gave enough good reasons ;-) I have no clue why MySQL is partnering with EMS ;-), but I strongly believe that DbVisualizer is the best around. If anybody believes that another tool is better than I would be VERY interested to hear about it. Best regards Nils Valentin Tokyo/Japan 2003 6 12 23:21Adam Nelson : I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
delete record
Hi, I've done these operations: - I deleted one record - I didn't execute optimize table Do you know if it's possible to undelete the record? Many thanks Arcangelo
Looking for a parseInt() / str_to_int() function...
Is there any built-in funtion at all, that allows us to parse number values from strings? This would be a huge aid in getting numeric sorting of results; something like: select scene, take from movie_table order by parseint(scene[,10]), scene, -- sc:8 | tk: sc:a9 | tk: sc:9 | tk: sc:9a | tk: sc:10 | tk: sc:a10 | tk: sc:11 | tk: Historically, i've had to create a separate numeric column for each and every string column that needed to first be numeric sorted, and then copy the programtically parsed int/long/etc string value to each one, strictly to give the desired sort. thnx! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
sum on counts
Can you sum on counts? What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects. select object_id from object_hist where type_id=5879; +---+ | object_id | +---+ | 2121 | | 3234 | | 2121 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4889 | | 3091 | | 3092 | | 3092 | | 3103 | | 3103 | | 1390 | | 1874 | | 3234 | | 2121 | | 4889 | +---+ select object_id, count(*) from object_hist where type_id=5879 group by object_id; +---+--+ | object_id | count(*) | +---+--+ | 1390 |1 | | 1874 |1 | | 2121 |3 | | 3091 |1 | | 3092 |2 | | 3103 |2 | | 3234 |2 | | 4876 |7 | | 4889 |2 | +---+--+ What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects ...something like this... (in 1 row) ++++---+---+ | VIEW_1 | VIEW_2 | VIEW_3 | VIEW_MORE | TOTAL | ++++---+---+ | 3 | 4 | 1 | 1 | 9 | +--+---+ I read this article http://www.devshed.com/Server_Side/MySQL/MySQLWiz/ and got all excited and tried to write something like this select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Thanks Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mySQL GUIs
Nils, and other well meaning members who mail me directly: (B (BI subscribe to the digest for a reason, please do not copy me directly on replies. (BYes, I'm guilty of doing this myself... Live and learn. (B (B (B-Original Message- (BFrom: Nils Valentin [mailto:[EMAIL PROTECTED] (BSent: Thursday, June 12, 2003 9:16 AM (BTo: Adam Nelson; Knepley, Jim; 'Rodolphe Toots'; [EMAIL PROTECTED] (BSubject: Re: mySQL GUIs (B (B (BHi Jim Mysql List members, (B (Bsad to hear that there are still people around not using a propper OS ;-) - (Blike a Linux, BSD or Unix based OS. (B (BAnyway as you asked about the best GUI for accesing MySQL - and as you asked (Bthe question you know there is only one best GUI ;-). Anything mentioned (Bbelow is my personal opinion and I am ot related to tany of the parties (Bmentioned. Any welcome to fire back on me ;-) (B (BEMS's MySQL Mangaer is to heavy loaded and the interface is anything else then (Bintuitive - so kick it into the bucket ;-) (B (BThere are many such tools around to be honest, but my absolute favourite one (Bhas to be DbVisualizer (www.minq.se) with a loong distance second . (B (BThat has very good reasons which I am willing to explain. (B (Ba) First the Menu is intuitive AND context sensitive. (Bb) its fast (Bc) it works (Bd) its java (Be) its professionally done (Bf) contains a chart software (yfiles from www.yworks.com) (Bg) support replies under 10 minutes !! REGULARLY (Bh) its not a windows copy GUI ;-) (Bi) two license model (Bj) any major OS supported (B (B (BAbout a) I want to explain a bit more. When you choose data and go to the (Bexport menu it will offer you to export as html or csv. If you choose to (Bexport the diagramme it will offer as choice jpeg or gif. So the menu changes (B(in the background) and you dont have to think about it. It also takes the (Bmillions off not needed options out of the menu simpler. (B (BOf course there is more but I believe that I gave enough good reasons ;-) (B (BI have no clue why MySQL is partnering with EMS ;-), but I strongly believe (Bthat DbVisualizer is the best around. (B (BIf anybody believes that another tool is better than I would be VERY (Binterested to hear about it. (B (BBest regards (B (BNils Valentin (BTokyo/Japan (B (B (B (B (B2003$BG/(J 6$B7n(J 12$BF|(J $BLZMKF|(J 23:21$B!"(JAdam Nelson $B$5$s$O=q$-$^$7$?(J: (B I like MySQL Manager - it costs a bit of money, but I find it (B indispensible. (B (B www.ems-hitech.com (B (B It runs on Windows AND Linux. I'll be switching to the linux version (B in about two weeks, so I'll tell you how well it works. (B (B -Original Message- (B From: Knepley, Jim [mailto:[EMAIL PROTECTED] (B Sent: Wednesday, June 11, 2003 3:00 PM (B To: Rodolphe Toots; [EMAIL PROTECTED] (B Subject: RE: mySQL GUIs (B (B (B I'm a big fan of Scibit's "Mascon" (B (B (B (B -Original Message- (B From: Rodolphe Toots [mailto:[EMAIL PROTECTED] (B Sent: Wednesday, June 11, 2003 5:17 AM (B To: [EMAIL PROTECTED] (B Subject: mySQL GUIs (B (B (B hi! (B (B i am looking for a good mySQL gui for windows (B i have used mySQL front, which was an excellent free program, but i (B did not handle relations and diagrams. also the program is no longer (B being developed (B (B i have now found the prog mySQL tools (http://www.mysqltools.com/) (B and mySQL explorer that works almost as enterprise manager for MS (B SQL server. it even creates database diagrams with relations as in (B enterprise manager! only backdraw is that this program is not free, (B but it is the best i have ever seen so far (B (B is there anyone out there that knows of a program that is (B freeware/shareware and is good (like mySQL tools)? (B (B (B (B /rewdboy (B (B-- (B--- (BValentin Nils (BInternet Technology (B (B E-Mail: [EMAIL PROTECTED] (B URL: http://www.knowd.co.jp (B Personal URL: http://www.knowd.co.jp/staff/nils (B (B (B-- (BMySQL General Mailing List (BFor list archives: http://lists.mysql.com/mysql (BTo unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: delete record
That depends, do you have a backup tape? ;) Unfortunatly a DELETE is a one-way trip. In theory the data is still there is the spot on the hard-drive has not been overwritten, but there are no tools (that I know of) to retrieve that row. Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Arcangelo [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 9:20 AM To: [EMAIL PROTECTED] Subject: delete record Hi, I've done these operations: - I deleted one record - I didn't execute optimize table Do you know if it's possible to undelete the record? Many thanks Arcangelo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
I like PremiumSoft's MySQL Studio (now called Navicat, I believe). Very clean, easy to use interface. You can get a trial here: http://www.mysqlstudio.com/ --Scott Brown At 01:16 PM 6/11/2003 +0200, Rodolphe Toots wrote: hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- 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: delete record
hehe... been there... if there isnt a way if you have logging turned on, you might be able to find the insert and following updates statements in the log to rebuild the row. good luck chris -Original Message- From: Arcangelo [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 10:20 AM To: [EMAIL PROTECTED] Subject: delete record Hi, I've done these operations: - I deleted one record - I didn't execute optimize table Do you know if it's possible to undelete the record? Many thanks Arcangelo -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Try SQLyog at http://www.webyog.com/sqlyog Its FREE and very powerful Karam --- Nils Valentin [EMAIL PROTECTED] wrote: Hi Jim Mysql List members, sad to hear that there are still people around not using a propper OS ;-) - like a Linux, BSD or Unix based OS. Anyway as you asked about the best GUI for accesing MySQL - and as you asked the question you know there is only one best GUI ;-). Anything mentioned below is my personal opinion and I am ot related to tany of the parties mentioned. Any welcome to fire back on me ;-) EMS's MySQL Mangaer is to heavy loaded and the interface is anything else then intuitive - so kick it into the bucket ;-) There are many such tools around to be honest, but my absolute favourite one has to be DbVisualizer (www.minq.se) with a loong distance second . That has very good reasons which I am willing to explain. a) First the Menu is intuitive AND context sensitive. b) its fast c) it works d) its java e) its professionally done f) contains a chart software (yfiles from www.yworks.com) g) support replies under 10 minutes !! REGULARLY h) its not a windows copy GUI ;-) i) two license model j) any major OS supported About a) I want to explain a bit more. When you choose data and go to the export menu it will offer you to export as html or csv. If you choose to export the diagramme it will offer as choice jpeg or gif. So the menu changes (in the background) and you dont have to think about it. It also takes the millions off not needed options out of the menu simpler. Of course there is more but I believe that I gave enough good reasons ;-) I have no clue why MySQL is partnering with EMS ;-), but I strongly believe that DbVisualizer is the best around. If anybody believes that another tool is better than I would be VERY interested to hear about it. Best regards Nils Valentin Tokyo/Japan 2003年 6月 12日 木曜日 23:21、Adam Nelson さんは書きました: I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Hi Jim, Mysql members, I understand what you are saying, but all I did is was a standard all reply. So there is nothing I can do from my side ;-), thats either the way you have setup your e-mail client (reply to adress) and /or how the mailing list works. You can unsubscribe from the mailing list and follow up here if this is what you would like to do ;-) http://www.listsearch.com/mysql.lasso Best regards Nils Valentin Tokyo/Japan 2003 6 13 00:23Knepley, Jim : Nils, and other well meaning members who mail me directly: I subscribe to the digest for a reason, please do not copy me directly on replies. Yes, I'm guilty of doing this myself... Live and learn. -Original Message- From: Nils Valentin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 9:16 AM To: Adam Nelson; Knepley, Jim; 'Rodolphe Toots'; [EMAIL PROTECTED] Subject: Re: mySQL GUIs Hi Jim Mysql List members, sad to hear that there are still people around not using a propper OS ;-) - like a Linux, BSD or Unix based OS. Anyway as you asked about the best GUI for accesing MySQL - and as you asked the question you know there is only one best GUI ;-). Anything mentioned below is my personal opinion and I am ot related to tany of the parties mentioned. Any welcome to fire back on me ;-) EMS's MySQL Mangaer is to heavy loaded and the interface is anything else then intuitive - so kick it into the bucket ;-) There are many such tools around to be honest, but my absolute favourite one has to be DbVisualizer (www.minq.se) with a loong distance second . That has very good reasons which I am willing to explain. a) First the Menu is intuitive AND context sensitive. b) its fast c) it works d) its java e) its professionally done f) contains a chart software (yfiles from www.yworks.com) g) support replies under 10 minutes !! REGULARLY h) its not a windows copy GUI ;-) i) two license model j) any major OS supported About a) I want to explain a bit more. When you choose data and go to the export menu it will offer you to export as html or csv. If you choose to export the diagramme it will offer as choice jpeg or gif. So the menu changes (in the background) and you dont have to think about it. It also takes the millions off not needed options out of the menu simpler. Of course there is more but I believe that I gave enough good reasons ;-) I have no clue why MySQL is partnering with EMS ;-), but I strongly believe that DbVisualizer is the best around. If anybody believes that another tool is better than I would be VERY interested to hear about it. Best regards Nils Valentin Tokyo/Japan 2003 6 12 23:21Adam Nelson : I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
soft real-time database
Greetings, I'm new to databases, but I am considering one for soft real-time data recording of scientific data. Then any client/display program can grab data. I'll be recording 300-500 variables/columns per second for about 10 hours at a stretch. All variables are floats. a) Is there a way to insert/write floats to the DB without converting to ASCII first? Seems expensive. b) I'm still wading through the documentation, but what would be the prefered method for polling to see if a new row has been written? I assume there is no notification process available from the server TIA for any pointers. -- --Chris I don't approve of political jokes. I've seen too many of them get elected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
Just wanted to throw in my vote for phpMyAdmin. It requires that PHP and a webserver be installed... but EXTREMELY easy to use and actively updated. Currently supports MySQL 3. and 4.0.xxx, supposedly 4.1 support is on the way. www.phpmyadmin.net Later. Quoting Karam Chand [EMAIL PROTECTED]: Try SQLyog at http://www.webyog.com/sqlyog Its FREE and very powerful Karam --- Nils Valentin [EMAIL PROTECTED] wrote: Hi Jim Mysql List members, sad to hear that there are still people around not using a propper OS ;-) - like a Linux, BSD or Unix based OS. Anyway as you asked about the best GUI for accesing MySQL - and as you asked the question you know there is only one best GUI ;-). Anything mentioned below is my personal opinion and I am ot related to tany of the parties mentioned. Any welcome to fire back on me ;-) EMS's MySQL Mangaer is to heavy loaded and the interface is anything else then intuitive - so kick it into the bucket ;-) There are many such tools around to be honest, but my absolute favourite one has to be DbVisualizer (www.minq.se) with a loong distance second . That has very good reasons which I am willing to explain. a) First the Menu is intuitive AND context sensitive. b) its fast c) it works d) its java e) its professionally done f) contains a chart software (yfiles from www.yworks.com) g) support replies under 10 minutes !! REGULARLY h) its not a windows copy GUI ;-) i) two license model j) any major OS supported About a) I want to explain a bit more. When you choose data and go to the export menu it will offer you to export as html or csv. If you choose to export the diagramme it will offer as choice jpeg or gif. So the menu changes (in the background) and you dont have to think about it. It also takes the millions off not needed options out of the menu simpler. Of course there is more but I believe that I gave enough good reasons ;-) I have no clue why MySQL is partnering with EMS ;-), but I strongly believe that DbVisualizer is the best around. If anybody believes that another tool is better than I would be VERY interested to hear about it. Best regards Nils Valentin Tokyo/Japan 2003年 6月 12日 木曜日 23:21、Adam Nelson さんは書きました: I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- --- Valentin Nils Internet Technology E-Mail: [EMAIL PROTECTED] URL: http://www.knowd.co.jp Personal URL: http://www.knowd.co.jp/staff/nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Calendar - Free online calendar with sync to Outlook(TM). http://calendar.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Tim Meader ACS Government Solutions, Inc. (301) 286-8013 [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Open-Source/Freeware Tool To Generate ER Diagrams From SQL Sc ripts ? - ImportER (ER Tool: Dezign)
Apparently, the reverse engineering add-on utility, ImportER, for the ER Tool, Dezign, see Free evaluation versions: www.datanamic.com/download/index.html can import MySQL tables directly. QED -Original Message- From: Eldrid Rensburg [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 5:11 PM To: [EMAIL PROTECTED] Subject: Open-Source/Freeware Tool To Generate Entity Relationship Diagram s From Text File Containing SQL Scripts ? Importance: High I have exported tables from MySQL 3.23 to a text file script containing all its 'create table', indexing, etc, statements. Is there an open-source / freeware tool to generate Entity Relationship Diagrams from this script file ? If not what other tools exist ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from. This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from. This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from.
Re: newbie question
Well, for the image type at least, the blob type would be appropriate. -justin v7rg8 wrote: Hi all, my table is like this: professor (name, gender, bodyImage) Could anyone guide me how to deal with this image type data? Another question is how to implement weak entity in mysql. Thanks, Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 4.1 character set documentation
At 15:05 +0900 6/12/03, Joel Rees wrote: Paul DuBois advised us that Alexander Barkov and Peter Gulutzan have written up some documentation on the new character set support in MySQL 4.1, which has now been added to the online manual. You can read it here: http://www.mysql.com/doc/en/Charset.html Question about this: In UCS-2 (binary Unicode representation) every character is represented by a two-byte Unicode code ... Should I read this to imply that the current level of support is BMP only? That's correct. (I assume you mean first 65536 code points.) -- 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]
Re: mySQL GUIs
On Thursday, June 12, 2003, at 10:44 AM, Justin wrote: Anybody have any recommendations for one that runs on Mac OS X? I use phpMyAdmin to manage several MySQL databases, both on my LAN and over the internet. Way cool and powerful! ___/ / __/ / / Ed Leafe http://leafe.com/ http://opentech.leafe.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOINs on link tables
Hi, I have a database which keeps track of STUDENTS doing CLASSES. Each CLASS has a list of ASSESSMENTs and the database stores a GRADE for each each STUDENT in the CLASS. The table structures are as follows: class: CID(*), Title, CourseCode, Year student:SID(*), FirstName, SecondName, StudentNo, Year, CID(FK) assessment: AID(*), AssName, Weight, DueDate, CID(FK) grade: SID(*)(FK), AID(*)(FK), Grade, Shown Is it possible to create an SQL query to do the following: I want to list every STUDENT alongside every ASSESSMENT listed as being in the CLASS that STUDENT is in, along with the GRADE the student received in that ASSESSMENT. The query I was using was as follows: SELECT FirstName, SecondName, AssName, Grade FROM student, assessment, grade WHERE grade.SID = student.SID AND grade.AID = assessment.AID AND student.CID = 1 (say). This lists all the GRADEs for all the STUDENTs in CLASS 1. My problem is that I want to do this with a LEFT JOIN so that even if the STUDENT did not take a particular ASSESSMENT (and so there is no entry with that combination of SID and AID in the GRADE table), the entry will still be returned, just with a NULL value. I was trying this with various combinations of LEFT JOINs on the three tables, but none of them seemed to work. Thank you. Michael Cooney [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
left join
Hi there... I have this situation: table_A table_B id name id name 1 A 1 A 2 B 2 B 3 C If i run this statement : SELECT table_A.* FROM table_A outer join table_B ON table_A.id=table_B.id WHERE table_B.id is null and I got: id name 3C But now i wanna use i join which i dont know, to results: idname 1 A 2 B I mean the rows which are exactly the same is there a fast type of join to make it?
Re: Weird problem with differences MySQL 3.23.38 und 4.0.12
Some of the values passed to the CASE expressions are NULL. There was a bug in handling NULL in CASE that was fixed in MySQL 4.0.8: http://www.mysql.com/doc/en/News-4.0.8.html Note the last item on the page. I believe this explains the differences that you are seeing. At 16:28 +0200 6/12/03, Axel Tietje wrote: Hi everyone... I actually have two servers: 1. Server: MySQL 4.0.12 2. Server: MySQL 3.23.38 This query: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB' AND o_val = '1') ) GROUP BY o_obj HAVING (_69B96431 = '01') shows on server 1 (4.0.12): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) but on server 2 (3.23.38): +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | NULL | NULL | | D27518B1 | 01| S | NULL | NULL | +--+---++---++ Please note the differences in fourth and fifth column while having absolutely identical tables and data. The following query works on 1. Server (4.0.12), but not on 2. Server (3.23.38): SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_val END) AS _AA0887CB, MAX(CASE WHEN o_key = 'AA0887CB' THEN o_typ END) AS T_AA0887CB FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') OR (o_key = 'AA0887CB') ) GROUP BY o_obj HAVING (_69B96431 = '01'); 1. Server says: +--+---++---++ | o_obj| _69B96431 | T_69B96431 | _AA0887CB | T_AA0887CB | +--+---++---++ | 1672BE70 | 01| S | 1 | B | | D27518B1 | 01| S | 1 | B | +--+---++---++ 2 rows in set (0.01 sec) 2. Server says: Empty set (0.00 sec) Explain shows the folowing in column 'Extra': 1. Server: Using where; Using temporary; Using filesort 2. Server: where used; Using temporary All other columns are identical: table TBL_32BF90B0 type ALL possible_keys o_key,o_val key NULL key_len NULL ref NULL rows 312 The following query shows the same result on both servers: SELECT o_obj, MAX(CASE WHEN o_key = '69B96431' THEN o_val END) AS _69B96431, MAX(CASE WHEN o_key = '69B96431' THEN o_typ END) AS T_69B96431 FROM TBL_32BF90B0 WHERE ( (o_key = '69B96431' AND o_val = '01') ) GROUP BY o_obj HAVING (_69B96431 = '01') Both servers say: +--+---++ | o_obj| _69B96431 | T_69B96431 | +--+---++ | 1672BE70 | 01| S | | D27518B1 | 01| S | +--+---++ 2 rows in set (0.01 sec) Now, why that? TIA, Axel. -- 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]
in in where
My first day with MySQLworking with a Access to MySQL project... when I try to execute the query: SELECT tblSiteConfig.fldSiteID FROM tblSiteConfig WHERE tblSiteConfig.fldServerName in (SELECT fldServerName from tblServerConfig) I get: [My_MYSQL] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT fldServerName from tblServerConfig)' at line 3 I'm not able to find out the error. Please help. Please suggest a good source for learning MySQL SQL. Thanks, raj Unless expressly stated to the contrary, the views expressed in this email are not necessarily the views of National Grid Transco plc or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees make no representation and accept no liability for its accuracy or completeness. This e-mail, and any attachments are strictly confidential and intended for the addressee(s) only. The content may also contain legal, professional or other privileged information. If you are not the intended recipient, please notify the sender immediately and then delete the e-mail and any attachments. You should not disclose, copy or take any action in reliance on this transmission. You may report the matter by calling us on + 44(0) 1455 230999 Please ensure you have adequate virus protection before you open or detach any documents from this transmission. The Group Companies do not accept any liability for viruses. An e-mail reply to this address may be subject to monitoring for operational reasons or lawful business practices. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: in in where
Subselects such as this are only available in MySQL 4.1 and higher. You can upgrade, or rewrite this query as follows: SELECT tblSiteConfig.fldSiteID FROM tblSiteConfig, tblServerConfig WHERE tblSiteConfig.fldServerName = tblServerConfig.fldServerName Regards, Mike Hillyer www.vbmysql.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:23 AM To: [EMAIL PROTECTED] Subject: in in where My first day with MySQLworking with a Access to MySQL project... when I try to execute the query: SELECT tblSiteConfig.fldSiteID FROM tblSiteConfig WHERE tblSiteConfig.fldServerName in (SELECT fldServerName from tblServerConfig) I get: [My_MYSQL] ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT fldServerName from tblServerConfig)' at line 3 I'm not able to find out the error. Please help. Please suggest a good source for learning MySQL SQL. Thanks, raj Unless expressly stated to the contrary, the views expressed in this email are not necessarily the views of National Grid Transco plc or any of its subsidiaries or affiliates (Group Companies), and the Group Companies, their directors, officers and employees make no representation and accept no liability for its accuracy or completeness. This e-mail, and any attachments are strictly confidential and intended for the addressee(s) only. The content may also contain legal, professional or other privileged information. If you are not the intended recipient, please notify the sender immediately and then delete the e-mail and any attachments. You should not disclose, copy or take any action in reliance on this transmission. You may report the matter by calling us on + 44(0) 1455 230999 Please ensure you have adequate virus protection before you open or detach any documents from this transmission. The Group Companies do not accept any liability for viruses. An e-mail reply to this address may be subject to monitoring for operational reasons or lawful business practices. -- 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]
query question
Is there a way to check if a field has x number of characters in it? I have a table with a column (phone) which, for the moment is varchar(25). I need to find those entries which have less than 10 characters. Can this be done? Thanks susan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Open-Source/Freeware Tool To Generate Entity Relationship Diagram s From Text File Containing SQL Scripts ?
WebObjects (30 day demo or $699) has a tool called Enterprise Object Modeler which reads in a database schema directly from the database and generates all the database diagrams, etc. and can generate Enterprise Objects in java to interact with the database. Might be worth playing with the demo to get the job done if it is an urgent requirement. Runs on Windows 2000 or Mac OS X. -Original Message- From: Eldrid Rensburg [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:11 AM To: [EMAIL PROTECTED] Subject: Open-Source/Freeware Tool To Generate Entity Relationship Diagram s From Text File Containing SQL Scripts ? Importance: High I have exported tables from MySQL 3.23 to a text file script containing all its 'create table', indexing, etc, statements. Is there an open-source / freeware tool to generate Entity Relationship Diagrams from this script file ? If not what other tools exist ? This message and any attachments are confidential and intended solely for the addressee. Any unauthorized use, alteration or dissemination is prohibited. Lawyers Access Web (Pty) Ltd. accepts no liability whatsoever for any loss, whether it be direct, indirect or consequential, arising from information made available and actions resulting there from. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
SELECT * FROM mytable WHERE LENGTH(phone) 10; http://www.mysql.com/doc/en/String_functions.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Susan Ator [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 11:23 AM To: [EMAIL PROTECTED] Subject: query question Is there a way to check if a field has x number of characters in it? I have a table with a column (phone) which, for the moment is varchar(25). I need to find those entries which have less than 10 characters. Can this be done? Thanks susan -- 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: query question
[snip]
Is there a way to check if a field has x number of characters in it?
I have a table with a column (phone) which, for the moment is
varchar(25). I need to find those entries which have less than 10
characters.
[/tuck]
http://www.mysql.com/doc/en/String_functions.html
mysql SELECT LENGTH('text');
- 4
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reply-All (RE: mySQL GUIs)
I understand what you are saying, but all I did is was a standard all reply. So there is nothing I can do from my side ;-), thats either the way you have setup your e-mail client (reply to adress) and /or how the mailing list works. I've noticed that some messages from the list have a reply-to of just [EMAIL PROTECTED] and others have both the MySQL list and the address of the sender. When I hit reply-all on this message three addresses are in the To field. Nils, Jim, and the list. Delete the redundant ones... -- Ray -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: query question
Thank y'all very very much. Each day I learn something new ;)
susan
-Original Message-
From: Jay Blanchard [mailto:[EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 1:35 PM
To: Susan Ator; [EMAIL PROTECTED]
Subject: RE: query question
[snip]
Is there a way to check if a field has x number of characters in it?
I have a table with a column (phone) which, for the moment is
varchar(25). I need to find those entries which have less than 10
characters.
[/tuck]
http://www.mysql.com/doc/en/String_functions.html
mysql SELECT LENGTH('text');
- 4
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mySQL GUIs
* Rodolphe Toots ([EMAIL PROTECTED]) wrote: hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed Try MySQL's own MySQL Control Center (mysqlcc): http://www.mysql.com/downloads/mysqlcc.html You need Qt, but Qt is available for many platforms, *nix, Win32, and Mac OS X too I believe. MySQLcc is a pretty nice product, reminds me a bit of pgAdmin II on Win32 (for the postgresql db). The other requirement is you need MySQL 4 if you want to compile it yourself, but binaries are available from the website that still work with 3.23.x -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
Re: mysql and clustering
On Wed, Jun 11, 2003 at 12:21:17PM +0200, Roberto Barbieri wrote: They're completely different clustering solution. What, exactly, are you trying to acheive? LVS is primarily for load balancing and possibly fail-over. OpenMosix is a different beast entirely. Jeremy Thanks for the reply Jeremy. What i need to build are a mysql master server with two nodes both active at the same time. Perhaps MySQL's replication in a dual-master setup? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 9 days, processed 310,123,483 queries (378/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication problems
On Wed, Jun 11, 2003 at 05:22:50PM +0530, Mithun Bhattacharya wrote: I am frequently getting errors as given below on my slave server. - 030605 3:27:19 Slave: did not get the expected error running query from master - expected: 'Got error %d from table handler' (1030), got 'no error' (0) 030605 3:27:19 Slave: error running query 'delete from page_cache_2003_06_04 where FILEKEY = '0'' 030605 3:27:19 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'ebldb05p2-bin.630' position 354859069 030605 3:27:19 Slave thread exiting, replication stopped in log 'ebldb05p2-bin.630' at position 354859069 - 030610 3:17:02 Slave: did not get the expected error running query from master - expected: 'Got error %d from table handler' (1030), got 'no error' (0) 030610 3:17:02 Slave: error running query 'delete from page_cache_2003_06_09 where FILEKEY = '0'' 030610 3:17:02 Error running query, slave aborted. Fix the problem, and re-start the slave thread with mysqladmin start-slave. We stopped at log 'ebldb05p2-bin.654' position 189278930 030610 3:17:02 Slave thread exiting, replication stopped in log 'ebldb05p2-bin.654' at position 189278930 - I can successfully run the SQL on the slave manually and it obviously has run successfully on the master. Why is that obvious? It's telling you that the slave expected to get an error but didn't. Those expectations come from the master's binlog. I'd repair the table on the master, since it's likely in some sort of trouble. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.13: up 9 days, processed 310,309,685 queries (378/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Open-Source/Freeware Tool To Generate Entity Relationship Diagram s From Text File Containing SQL Scripts ?
* Eldrid Rensburg ([EMAIL PROTECTED]) wrote: I have exported tables from MySQL 3.23 to a text file script containing all its 'create table', indexing, etc, statements. Is there an open-source / freeware tool to generate Entity Relationship Diagrams from this script file ? If not what other tools exist ? The problem with this approach is that SQL Tables do NOT translate directly to ER. In some cases yes, but the ER is not necessarily the DB Schema and visa versa. Total relationships are not there in the schema but are in the ER for example. The same with aggregates and so forth. As to doing ER, you can use Dia http://www.lysator.liu.se/~alla/dia/ which will also do UML. Now, it shouldn't be hard to go from your SQL script file to a UML type diagram (ala Absess and Oracle) but I'm not aware of utils that do that either. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
Re: Open-Source/Freeware Tool To Generate ER Diagrams From SQL Sc ripts ? - ImportER (ER Tool: Dezign)
* Eldrid Rensburg ([EMAIL PROTECTED]) wrote: Apparently, the reverse engineering add-on utility, ImportER, for the ER Tool, Dezign, see Free evaluation versions: www.datanamic.com/download/index.html can import MySQL tables directly. Having looked at that, it only runs on Windows. It also looks more like class diagrams (from the screenshots) than true ER, but I haven't looked at it in detail. Just my $0.02 worth. -- .''`. Carl B. Constantine : :' : [EMAIL PROTECTED] `. `'GnuPG: 135F FC30 7A02 B0EB 61DB 34E3 3AF1 DC6C 9F7A 3FF8 `- Debian GNU/Linux -- The power of freedom pgp0.pgp Description: PGP signature
sql Query problem?
Hi, I have a sql query problem... user points table, which I am sorting(order by) points. Now if I want to have some 5 records above and below a certain member, how could I write the sql query for the same? say I want 5 records above and below NICKNAME_14! thanks and regards, point nickname -- 999 NICKNAME_6 980 NICKNAME_23 970 NICKNAME_8 960 NICKNAME_9 940 NICKNAME_11 940 NICKNAME_41 932 NICKNAME_26 930 NICKNAME_12 930 NICKNAME_42 922 NICKNAME_38 920 NICKNAME_13 900 NICKNAME_2 900 NICKNAME_25 900 NICKNAME_5 === 900 NICKNAME_14 900 NICKNAME_3 880 NICKNAME_30 860 NICKNAME_57 860 NICKNAME_39 860 NICKNAME_21 859 NICKNAME_36 850 NICKNAME_20 840 NICKNAME_35 840 NICKNAME_19 836 NICKNAME_45 830 NICKNAME_33 830 NICKNAME_18 820 NICKNAME_17 810 NICKNAME_32 800 NICKNAME_24 800 NICKNAME_15 764 NICKNAME_54 700 NICKNAME_27 625 NICKNAME_59 600 NICKNAME_28 563 NICKNAME_55 500 NICKNAME_29 270 NICKNAME_49 270 NICKNAME_48 262 NICKNAME_56 260 NICKNAME_44 260 NICKNAME_51 260 NICKNAME_47 256 NICKNAME_50 250 NICKNAME_58 246 NICKNAME_43 226 NICKNAME_60 140 NICKNAME_53 Nishant [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE - longtext
Hi I have a database whose table has a column, which has a datatype longtext. I then try a query mysql select MY_DATE from my_table where MLC_DATE between '09/22/2003' AND '10/21/2 003'; I thought it would fail since the datatype isnt DATE nor TIMESTAMP. But from the result, it seems to work. 1) Is this query reliable ? 2) What possible scenarios where comparing the string dates would go wrong ? 3) Is there a way to convert all the strings in a database table eg 09/22/2003 in each row in the database to 2003-09-22 in each row in the database Any help is fully appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Which version do I install?
I am used to using P3 and P4 machines with the x86 download. I just got myself a Dual Xeon 2.60 Ghz machine. Does this still use the x86 download, or is there a better binary to use (ie IA64)? Any other tips for someone new to the Xeon chip family with linux would be nice too. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DATE - longtext
At 14:44 -0500 6/12/03, Wong Zach-CHZ013 wrote: Hi I have a database whose table has a column, which has a datatype longtext. I then try a query mysql select MY_DATE from my_table where MLC_DATE between '09/22/2003' AND '10/21/2 003'; I thought it would fail since the datatype isnt DATE nor TIMESTAMP. But from the result, it seems to work. 1) Is this query reliable ? No. You're doing a string comparison with the sort parts (year, month, day) not listed in that order in the comparison values. 2) What possible scenarios where comparing the string dates would go wrong ? 3) Is there a way to convert all the strings in a database table eg 09/22/2003 in each row in the database to 2003-09-22 in each row in the database You can use LEFT(), MID(), RIGHT() to bust up the strings into pieces, and CONCAT() to put them back together. -- 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]
again with SELECT
coming back to my earlier question but much more simplified portion of it :) I the following table : theTable --- ID | int (key) colorID | int itemID | int --- how do I select all itemIDs that have colorID = 1 and colorID = 2 and colorID = 3 ... and colorID = N ? (note: using JOIN theTable... for every colorID in question ends up with too many JOINS in single select) any suggestions please? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which version do I install?
I am used to using P3 and P4 machines with the x86 download. I just got myself a Dual Xeon 2.60 Ghz machine. Does this still use the x86 download, or is there a better binary to use (ie IA64)? Any other tips for someone new to the Xeon chip family with linux would be nice too. -- This is a x86 (32 bit) chip. It may or may not have 'hyperthreading' where it can act as two processors. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FULLTEXT Search and Charsets
Hi. I have a table with a fulltext search index on a field. I am inserting text using UTF-8 characters (with the JDBC driver). That includes words in English, French, Russian and Greek. The FULLTEXT search only seems to find words in the latin charset. Does anyone have any guidance on how I can get it working with other charsets? Regards, Peter MySQL SQL -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DATE
Hi 1 - I have a column whose datatype is longtext. Its content is 08/06/2003; I created a new column whose datatype is DATE. Its content is null now. How do write a SQL statement that inputs each row from 08/06/2003 in the old column to 2003-08-06 in a new column ? Eg: old column name - my_date new column name - my_new_date I tried select CONCAT(SUBSTRING(MY_DATE FROM 7), '-',SUBSTRING_INDEX(MY_DATE,'/',1), '-', MID(MY_DATE,4,2)) from dap_cell; But how do I put this value into its corresponding row in the new column ? Any help is appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: FULLTEXT Search and Charsets
At 14:13 -0700 6/12/03, Peter Bryant wrote: Hi. I have a table with a fulltext search index on a field. I am inserting text using UTF-8 characters (with the JDBC driver). That includes words in English, French, Russian and Greek. The FULLTEXT search only seems to find words in the latin charset. Does anyone have any guidance on how I can get it working with other charsets? FULLTEXT currently works only with single-byte character sets. Regards, Peter MySQL SQL -- 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]
Re: Bug with MySQL (was Re: Problem with MAX())
At 16:09 -0500 6/12/03, Chris Boget wrote: CREATE TABLE do_deductibles ( currency varchar(10) NOT NULL default '', assets int(20) NOT NULL default '0', deductible int(10) NOT NULL default '0', do_deductibles_id int(5) NOT NULL auto_increment, PRIMARY KEY (do_deductibles_id), UNIQUE KEY do_deductibles_idx (currency,assets) ) TYPE=ISAM PACK_KEYS=1; This is definitely a bug with MySQL. Where do I report these things? http://bugs.mysql.com/ I tracked the problem down to this: UNIQUE KEY do_deductibles_idx (currency,assets) If I remove this line altogether from the create table and run the queries provided in my previous post, everything works as expected. If I added the above line but removed the key word UNIQUE, everything works as expected. But if I leave the above line in the create query in whole, the first MAX query that is run return NULL as does every query there after until I qualify the MAX column to be IS NOT NULL then the query and all subsequent queries thereafter work as expected. Chris -- 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]
Re: DATE
At 16:23 -0500 6/12/03, Wong Zach-CHZ013 wrote: Hi 1 - I have a column whose datatype is longtext. Its content is 08/06/2003; I created a new column whose datatype is DATE. Its content is null now. How do write a SQL statement that inputs each row from 08/06/2003 in the old column to 2003-08-06 in a new column ? Eg: old column name - my_date new column name - my_new_date I tried select CONCAT(SUBSTRING(MY_DATE FROM 7), '-',SUBSTRING_INDEX(MY_DATE,'/',1), '-', MID(MY_DATE,4,2)) from dap_cell; But how do I put this value into its corresponding row in the new column ? UPDATE dap_cel SET my_new_date = CONCAT(that-long-expression-above); Any help is appreciated. -- 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]
DATE
Hi How do I rename a new column ? How do I drop a column ? From MySQL documentation, it refers to rename/drop table. I dont want to do that. Thanks Paul for your aid. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DATE
Use the ALTER TABLE syntax, see http://www.mysql.com/doc/en/ALTER_TABLE.html Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Wong Zach-CHZ013 [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:43 PM To: 'Paul DuBois'; [EMAIL PROTECTED] Subject: DATE Hi How do I rename a new column ? How do I drop a column ? From MySQL documentation, it refers to rename/drop table. I dont want to do that. Thanks Paul for your aid. -- 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: DATE
At 16:43 -0500 6/12/03, Wong Zach-CHZ013 wrote: Hi How do I rename a new column ? How do I drop a column ? From MySQL documentation, it refers to rename/drop table. I dont want to do that. Use the ALTER TABLE statement: http://www.mysql.com/doc/en/ALTER_TABLE.html Thanks Paul for your aid. -- 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]
Re: again with SELECT
danchik wrote:
coming back to my earlier question but much more simplified portion of it :)
I the following table :
theTable
---
ID | int (key)
colorID | int
itemID | int
---
how do I select all itemIDs that have colorID = 1 and colorID = 2 and
colorID = 3 ... and colorID = N ?
I didn't read the previous version of your question, but is the set of
'n' color ID's known at query time? If so,
SELECT ID FROM theTable WHERE colorID in {1, 2, 3, 4, ..., n}
Or if you want say a range where colorID is between 5 and 10,
SELECT ID FROM theTable WHERE colorID = 5 AND colorID = 10
-Fred
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How can I get the number of entries retrieved by a SELECT - FROM
Dell 4500, mem 1g, disk 45g, mhz 2g, Mysql-3.23.54a-linux-i686, PHP-4.30, httpd_2.0.44 1. How do I trap or collect or save the digit 3 generated the mysql SELECT COUNT(*) statement below? 2. The PHP SELECT FROM below (before snip) listed the expected data. Is there a way to get the digit 3 into a PHP variable? SELECT h_id, name FROM tbtm # displays WHERE h_id=1# three OR h_id=2 # lines OR h_id=3; # of data SELECT COUNT(*) FROM tbtm # displays WHERE h_id='1'# the OR h_id='2'# digit OR h_id='3'; # three +--+ | COUNT(*) | +--+ |3 | +--+ 1 row in set (0.00 sec) The next PHP is the same as the SELECT FROM above. ?php $result = mysql_query( SELECT h_id, name, FROM tbtm WHERE h_id='1' OR h_id='2' OR h_id='3', $tmtb_db); # printf(ID: %sbr\n, mysql_result($result,1,h_id)); printf(name: %sbr\n,mysql_result($result,1,name)); snip snip # ? Thanks [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
Hi, how do I select all itemIDs that have colorID = 1 and colorID = 2 and colorID = 3 ... and colorID = N ? Try this SELECT itemID FROM theTable WHERE colorID IN (1,2,3,...,N); G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
thats effectively an OR statement, isnt it?
how would i select the itemsIDs that have all the requested colors?
for example if data was:
id:colorID:itemID
-
11 1
21 2
31 3
42 1
52 4
and i want the ones that are of color 1 AND 2 i should get back
itemID
1
(since its the only one with both specified colors)
- Original Message -
From: Fred Whipple [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Thursday, June 12, 2003 2:47 PM
Subject: Re: again with SELECT
danchik wrote:
coming back to my earlier question but much more simplified portion of
it :)
I the following table :
theTable
---
ID | int (key)
colorID | int
itemID | int
---
how do I select all itemIDs that have colorID = 1 and colorID = 2 and
colorID = 3 ... and colorID = N ?
I didn't read the previous version of your question, but is the set of
'n' color ID's known at query time? If so,
SELECT ID FROM theTable WHERE colorID in {1, 2, 3, 4, ..., n}
Or if you want say a range where colorID is between 5 and 10,
SELECT ID FROM theTable WHERE colorID = 5 AND colorID = 10
-Fred
--
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: How can I get the number of entries retrieved by a SELECT - FROM
Change you SQL statement as follows. SELECT Count(*) as Cnt FROM Then use Cnt as your variable like you would h_id Roger. -Original Message- From: ed anderson [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 5:58 PM To: [EMAIL PROTECTED] Subject: How can I get the number of entries retrieved by a SELECT - FROM Dell 4500, mem 1g, disk 45g, mhz 2g, Mysql-3.23.54a-linux-i686, PHP-4.30, httpd_2.0.44 1. How do I trap or collect or save the digit 3 generated the mysql SELECT COUNT(*) statement below? 2. The PHP SELECT FROM below (before snip) listed the expected data. Is there a way to get the digit 3 into a PHP variable? SELECT h_id, name FROM tbtm # displays WHERE h_id=1# three OR h_id=2 # lines OR h_id=3; # of data SELECT COUNT(*) FROM tbtm # displays WHERE h_id='1'# the OR h_id='2'# digit OR h_id='3'; # three +--+ | COUNT(*) | +--+ |3 | +--+ 1 row in set (0.00 sec) The next PHP is the same as the SELECT FROM above. ?php $result = mysql_query( SELECT h_id, name, FROM tbtm WHERE h_id='1' OR h_id='2' OR h_id='3', $tmtb_db); # printf(ID: %sbr\n, mysql_result($result,1,h_id)); printf(name: %sbr\n,mysql_result($result,1,name)); snip snip # ? Thanks [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: again with SELECT
Ah, I see what you mean, I misunderstood before. In this case, I would tend to reevaluate my table structure since you're inherently giving a key (colorID) more than one possible value (1 or 2) at the same time. There could be something you could do with DISTINCT but I can't think exactly how you'd do this with a SQL query, short of using 'n' joins. Of course if it's possible, it'd be best to modify the table such that you separate colorID into two columns, and then use an AND. -Fred danchik wrote: thats effectively an OR statement, isnt it? how would i select the itemsIDs that have all the requested colors? for example if data was: id:colorID:itemID - 11 1 21 2 31 3 42 1 52 4 and i want the ones that are of color 1 AND 2 i should get back itemID 1 (since its the only one with both specified colors) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: sum on counts
select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Syntax, my dear boy. It's just a hunch, but shouldn't you declare 'count(*) AS C' before you start trying to do calculations on it? Try this: select count(*) as C, SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3 from object_hist where type_id=5879 group by object_id Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Christopher Knight [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 11:22 Subject: sum on counts Can you sum on counts? What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects. select object_id from object_hist where type_id=5879; +---+ | object_id | +---+ | 2121 | | 3234 | | 2121 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4889 | | 3091 | | 3092 | | 3092 | | 3103 | | 3103 | | 1390 | | 1874 | | 3234 | | 2121 | | 4889 | +---+ select object_id, count(*) from object_hist where type_id=5879 group by object_id; +---+--+ | object_id | count(*) | +---+--+ | 1390 |1 | | 1874 |1 | | 2121 |3 | | 3091 |1 | | 3092 |2 | | 3103 |2 | | 3234 |2 | | 4876 |7 | | 4889 |2 | +---+--+ What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects ...something like this... (in 1 row) ++++---+---+ | VIEW_1 | VIEW_2 | VIEW_3 | VIEW_MORE | TOTAL | ++++---+---+ | 3 | 4 | 1 | 1 | 9 | +--+---+ I read this article http://www.devshed.com/Server_Side/MySQL/MySQLWiz/ and got all excited and tried to write something like this select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Thanks Chris -- 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: Thousands of users? Newbie question
I think that I would code it into my application. Essentially, I would set up Agents on your end so that your customer service folks can set up companies and administrative users for those companies. Then let the companies handle their own users. Each company would have a database password and a username/password combo to access their admin section of the website. Curtis On Wed, 11 Jun 2003, 2Hosts.com wrote: Hi Guys, I intend my online database to be used by 100 or so companies each with up to 100 employees. Each of these employees may add data as required. I don't want to assign one password for an entire company, in case one disgruntled ex-employee decides to post erroneous data. So presumably I need to issue each employee a username and password, then remove this when the employee leaves the company (or grant an admin for that company permission to do it). My questions are : How is the username and password issue best handled when so many potential users are involved? Should a username and password be added to the web form used to add data? or Should I just use .htaccess to grant usernames and passwords so no unauthorized user can access the submission form in the first place? Which is less resource hungry? -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
thats effectively an OR statement, isnt it? how would i select the itemsIDs that have all the requested colors? for example if data was: id:colorID:itemID - 11 1 21 2 31 3 42 1 52 4 and i want the ones that are of color 1 AND 2 i should get back itemID 1 (since its the only one with both specified colors) Normally (this is what most ppl would think), for a given item you have one single unique color, but as i see this is not in your case. You should reorganize your tables. But if for some reason you cannot, here is a suggestion (just off my head). Assuming that colorIDs do not take very large values. To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. I dont think deleting will be such a problem also, say you want to delete all items which have colorID = 2. Find the corresponding prime, lets say it is P then you simply need to check if mod(colorPRODUCT, P) = 0. Of course no indexes can be used directly. Well, not such a bright suggestion !! G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: soft real-time database
If I interpret correctly, you'd like to to read sensor data (not in ASCII format) as FLOATs and insert it into a database on the fly without an intermediary. While this may be possible, perhaps there's a better solution. I would suggest reading the data to a file (which you must be able to do any way) for the duration of your test. When the test is complete, parse the data and do a batch insert into the database. MySQL will report the number of rows recorded, which can act as your polling system. If you are able to provide more details about your application, perhaps we can be of more help. Most significant is likely the data format coming from your sensors. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Chris Webster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 11:46 Subject: soft real-time database Greetings, I'm new to databases, but I am considering one for soft real-time data recording of scientific data. Then any client/display program can grab data. I'll be recording 300-500 variables/columns per second for about 10 hours at a stretch. All variables are floats. a) Is there a way to insert/write floats to the DB without converting to ASCII first? Seems expensive. b) I'm still wading through the documentation, but what would be the prefered method for polling to see if a new row has been written? I assume there is no notification process available from the server TIA for any pointers. -- --Chris I don't approve of political jokes. I've seen too many of them get elected. -- 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: mySQL GUIs
PHPMyAdmin Support for MySQL 4.1 is starting to appear. Runs on a webserver and just gets it done. (Though I still use mysqlcc, too.) I find that mysqlcc runs better if you compile it yourself. You also get more themes. ;-) Curtis On Thu, 12 Jun 2003, Justin wrote: Anybody have any recommendations for one that runs on Mac OS X? Thanks! Justin On 6/12/03 9:21 AM, Adam Nelson [EMAIL PROTECTED] wrote: I like MySQL Manager - it costs a bit of money, but I find it indispensible. www.ems-hitech.com It runs on Windows AND Linux. I'll be switching to the linux version in about two weeks, so I'll tell you how well it works. -Original Message- From: Knepley, Jim [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 3:00 PM To: Rodolphe Toots; [EMAIL PROTECTED] Subject: RE: mySQL GUIs I'm a big fan of Scibit's Mascon -Original Message- From: Rodolphe Toots [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 11, 2003 5:17 AM To: [EMAIL PROTECTED] Subject: mySQL GUIs hi! i am looking for a good mySQL gui for windows i have used mySQL front, which was an excellent free program, but i did not handle relations and diagrams. also the program is no longer being developed i have now found the prog mySQL tools (http://www.mysqltools.com/) and mySQL explorer that works almost as enterprise manager for MS SQL server. it even creates database diagrams with relations as in enterprise manager! only backdraw is that this program is not free, but it is the best i have ever seen so far is there anyone out there that knows of a program that is freeware/shareware and is good (like mySQL tools)? /rewdboy -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
On 13 Jun 2003 at 4:02, gamin wrote: To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. A more normal way, which wouldn't require such large numbers or the complication of factoring, would be to assign powers of 2 to the colors and add them rather than multiplying them. Or you could use the SET column type in MySQL, which handles the powers of 2 behind the scenes: http://www.mysql.com/doc/en/SET.html -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Which version do I install?
x86 IA64 is Itanium. Curtis On Thu, 12 Jun 2003, Jason wrote: I am used to using P3 and P4 machines with the x86 download. I just got myself a Dual Xeon 2.60 Ghz machine. Does this still use the x86 download, or is there a better binary to use (ie IA64)? Any other tips for someone new to the Xeon chip family with linux would be nice too. -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: soft real-time database
I'd write a perl script to pool the device and send the data to the database. Curtis On Thu, 12 Jun 2003, Becoming Digital wrote: If I interpret correctly, you'd like to to read sensor data (not in ASCII format) as FLOATs and insert it into a database on the fly without an intermediary. While this may be possible, perhaps there's a better solution. I would suggest reading the data to a file (which you must be able to do any way) for the duration of your test. When the test is complete, parse the data and do a batch insert into the database. MySQL will report the number of rows recorded, which can act as your polling system. If you are able to provide more details about your application, perhaps we can be of more help. Most significant is likely the data format coming from your sensors. Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Chris Webster [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 11:46 Subject: soft real-time database Greetings, I'm new to databases, but I am considering one for soft real-time data recording of scientific data. Then any client/display program can grab data. I'll be recording 300-500 variables/columns per second for about 10 hours at a stretch. All variables are floats. a) Is there a way to insert/write floats to the DB without converting to ASCII first? Seems expensive. b) I'm still wading through the documentation, but what would be the prefered method for polling to see if a new row has been written? I assume there is no notification process available from the server TIA for any pointers. -- --Chris I don't approve of political jokes. I've seen too many of them get elected. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: again with SELECT
I think you can get those rows that have multiple colors, if you know the list of colors to check for... Maybe this can help... SELECT itemID from theTable Where colorID in (1,2,3,4,...N) Group by itemID Having count(*) = N; This will only return the itemID if it appears in all of the colors listed, *unless* an item can be listed in a color More than once. In that case, it'll return false rows. Kevin -Original Message- From: gamin [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:33 PM To: danchik; Fred Whipple; [EMAIL PROTECTED] Subject: Re: again with SELECT thats effectively an OR statement, isnt it? how would i select the itemsIDs that have all the requested colors? for example if data was: id:colorID:itemID - 11 1 21 2 31 3 42 1 52 4 and i want the ones that are of color 1 AND 2 i should get back itemID 1 (since its the only one with both specified colors) Normally (this is what most ppl would think), for a given item you have one single unique color, but as i see this is not in your case. You should reorganize your tables. But if for some reason you cannot, here is a suggestion (just off my head). Assuming that colorIDs do not take very large values. To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. I dont think deleting will be such a problem also, say you want to delete all items which have colorID = 2. Find the corresponding prime, lets say it is P then you simply need to check if mod(colorPRODUCT, P) = 0. Of course no indexes can be used directly. Well, not such a bright suggestion !! G -- 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: soft real-time database
Curtis Maurand wrote: I'd write a perl script to pool the device and send the data to the database. Sorry for not being clearer. I can get the data into the database fine. Assuming new values or rows are added once per second, how would a client program go about polling the database to see when a new value was ready? Check # of rows for a column and when it's incremented grab the latest value? --Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: again with SELECT
SET is limited to 64 bit values (i.e. only 64 possible colors). Solution given by Kevin Fries [EMAIL PROTECTED] (with unique color/item having count query) seems very efficient. Thank you all for taking time to ponder my question. - Original Message - From: Mike Hillyer [EMAIL PROTECTED] To: Kevin Fries [EMAIL PROTECTED]; gamin [EMAIL PROTECTED]; danchik [EMAIL PROTECTED]; Fred Whipple [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, June 12, 2003 3:59 PM Subject: RE: again with SELECT Rather than using primes, you could use the SET datatype http://www.mysql.com/doc/en/SET.html This allows for a color to be zero or more values at the same time. SELECT * FROM tbl_name WHERE color = '1,2,3,n' Regards, Mike Hillyer www.vbmysql.com -Original Message- From: Kevin Fries [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 4:51 PM To: 'gamin'; 'danchik'; 'Fred Whipple'; [EMAIL PROTECTED] Subject: RE: again with SELECT Normally (this is what most ppl would think), for a given item you have one single unique color, but as i see this is not in your case. You should reorganize your tables. But if for some reason you cannot, here is a suggestion (just off my head). Assuming that colorIDs do not take very large values. To each colorID you assign a unique prime number (maintain this in a table). For each itemID now you have a unique colorPRODUCT (product of the prime numbers coressponding the various colorIDs for this itemID). To add a colorID to an itemID simply muliply the new prime (coressponding to the colorID being added) to the old colorPRODUCT. In the same way to remove a colorID devide colorPRODUCT by the corressponding prime. And to find the itemID which has a particular set of colors just check for the colorPRODUCT. I dont think deleting will be such a problem also, say you want to delete all items which have colorID = 2. Find the corresponding prime, lets say it is P then you simply need to check if mod(colorPRODUCT, P) = 0. Of course no indexes can be used directly. Well, not such a bright suggestion !! G -- 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: sum on counts
I tried that way also Unfortunately I get ERROR 1054: Unknown column 'C' in 'field list'. Btw... In case it matters ver4.0 -Original Message- From: Becoming Digital [mailto:[EMAIL PROTECTED] Sent: Thursday, June 12, 2003 5:31 PM To: Christopher Knight; MySQL List Subject: Re: sum on counts select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Syntax, my dear boy. It's just a hunch, but shouldn't you declare 'count(*) AS C' before you start trying to do calculations on it? Try this: select count(*) as C, SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3 from object_hist where type_id=5879 group by object_id Edward Dudlik Becoming Digital www.becomingdigital.com - Original Message - From: Christopher Knight [EMAIL PROTECTED] To: MySQL List [EMAIL PROTECTED] Sent: Thursday, 12 June, 2003 11:22 Subject: sum on counts Can you sum on counts? What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects. select object_id from object_hist where type_id=5879; +---+ | object_id | +---+ | 2121 | | 3234 | | 2121 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4876 | | 4889 | | 3091 | | 3092 | | 3092 | | 3103 | | 3103 | | 1390 | | 1874 | | 3234 | | 2121 | | 4889 | +---+ select object_id, count(*) from object_hist where type_id=5879 group by object_id; +---+--+ | object_id | count(*) | +---+--+ | 1390 |1 | | 1874 |1 | | 2121 |3 | | 3091 |1 | | 3092 |2 | | 3103 |2 | | 3234 |2 | | 4876 |7 | | 4889 |2 | +---+--+ What I want is the # of objects with a freq of 1, freq of 2, freq of 3... and then total # of objects ...something like this... (in 1 row) ++++---+---+ | VIEW_1 | VIEW_2 | VIEW_3 | VIEW_MORE | TOTAL | ++++---+---+ | 3 | 4 | 1 | 1 | 9 | +--+---+ I read this article http://www.devshed.com/Server_Side/MySQL/MySQLWiz/ and got all excited and tried to write something like this select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Thanks Chris -- 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: How can I get the number of entries retrieved by a SELECT - FROM
At 17:57 -0400 6/12/03, ed anderson wrote: Dell 4500, mem 1g, disk 45g, mhz 2g, Mysql-3.23.54a-linux-i686, PHP-4.30, httpd_2.0.44 1. How do I trap or collect or save the digit 3 generated the mysql SELECT COUNT(*) statement below? 2. The PHP SELECT FROM below (before snip) listed the expected data. Is there a way to get the digit 3 into a PHP variable? SELECT h_id, name FROM tbtm # displays WHERE h_id=1# three OR h_id=2 # lines OR h_id=3; # of data SELECT COUNT(*) FROM tbtm # displays WHERE h_id='1'# the OR h_id='2'# digit OR h_id='3'; # three +--+ | COUNT(*) | +--+ |3 | +--+ 1 row in set (0.00 sec) The next PHP is the same as the SELECT FROM above. ?php $result = mysql_query( SELECT h_id, name, FROM tbtm WHERE h_id='1' OR h_id='2' OR h_id='3', $tmtb_db); Well, *after* you check $result to make sure that the query didn't fail (which you don't do above, but should), use the mysql_num_rows() function to determine how many rows the query returns. Something like this: $result = mysql_query ($query) or die (Query failed\n); $row_count = mysql_num_rows ($result); # printf(ID: %sbr\n, mysql_result($result,1,h_id)); printf(name: %sbr\n,mysql_result($result,1,name)); snip snip # ? Thanks [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- 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]
Re: sum on counts
At 18:31 -0400 6/12/03, Becoming Digital wrote: select SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3, count(*) as C from object_hist where type_id=5879 group by object_id and it complains about unknown column C. Anyone have any insite on how to do this? Syntax, my dear boy. It's just a hunch, but shouldn't you declare 'count(*) AS C' before you start trying to do calculations on it? Try this: Won't work. You can't refer to a column alias elsewhere in the column output list. You might run one query to get the counts into a temporary table, then another to join the temp table to the original one. select count(*) as C, SUM(IF(C=1,1,0)) AS VIEW_1, SUM(IF(C=2,1,0)) AS VIEW_2, SUM(IF(C=3,1,0)) AS VIEW_3 from object_hist where type_id=5879 group by object_id Edward Dudlik Becoming Digital www.becomingdigital.com -- 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]
