ANN: Database Workbench 2.8.6 released
Ladies, gentlemen, Upscene Productions is proud to announce the next version of the popular database development tool: Database Workbench 2.8.6 has been released today! Download a trial at: http://www.upscene.com What's new?: http://www.upscene.com/products/dbw/whatsnew.htm Full list of features and fixes: http://www.upscene.com/news/20060522.htm We've still got a spring sale with 25% discount on all our products! Database Workbench supports: - Borland InterBase ( 4.x - 7.x ) - Firebird ( 1.x, 2.0 ) - MS SQL Server/MSDE ( v6.5, 7, 2000, 2005, MSDE 1 2, SQL Express ) - MySQL 4, 4.1, 5.0 - Oracle Database ( 8i, 9i, 10g ) - NexusDB ( 2.05 ) If you experience any problems with this new version, don't hestitate and either go to the website and send a support email or email directly to [EMAIL PROTECTED] New/improved - NexusDB 2 support - Microsoft SQL 2005 support - MySQL 5 support - Two-way Visual Query Builder - Increased Oracle support - New SQL Insight - Create INSERT script from ODBC datasource - TIFF support in BLOB Editor Enhancements and bugfixes related to Import/Export, DataPump, Code Editors, SQL Insight, NexusDB module performance and much more. Thank you for your support, Martijn Tonies Database Workbench - the database developer tool for professionals Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Let's make a difference
I'm doing a little something for the earth, and I thought I'd invite you to join me: http://friends.earthscreen.com/?r=UQF0KDIIICdXCWoICigCi=gmail[EMAIL PROTECTED]p=2z=1tc=23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Let's make a difference
I'm doing a little something for the earth, and I thought I'd invite you to join me: http://friends.earthscreen.com/?r=EQFEKDIIICdXCWoICigCi=gmail[EMAIL PROTECTED]p=2z=1tc=23 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Hello how to connect MySQL with Visual Foxpro
Hi all, I have got a database in mysql and i need to use the gis and that was in VFOXPRO.I need to display my data which is mysql on the map which database is in VFoXPRO.Is there any way to have communication betweeen these two DBs. Thanks and regards, venu.
Re: Hello how to connect MySQL with Visual Foxpro
Hi Venu, I would like to know the OS your using (for mysql server), BTW if your using window. Try installing the myodbc and configure it so that vfoxpro can communicate with it. check this site: http://fox.wikis.com/wc.dll?Wiki~VFPCommandSQLStringConnect~VFP At 06:28 PM 5/22/2006, VenuGopal Papasani wrote: Hi all, I have got a database in mysql and i need to use the gis and that was in VFOXPRO.I need to display my data which is mysql on the map which database is in VFoXPRO.Is there any way to have communication betweeen these two DBs. Thanks and regards, venu. Ehrwin C. Mina Chikka Phils. Inc. A CMMI LEVEL 5, AND A ISO 9001:2000; ISO 15000:2002; ISO 17799:2002 CERTIFIED COMPANY This message and any attachment are confidential and may be privileged or otherwise protected from disclosure. If you are not the intended recipient, you must not copy this message or attachment or disclose the contents to any other person. If you have received this transmission in error, please notify the sender immediately and delete the message and any attachment from your system. Chikka does not accept liability for any omissions or errors in this message which may arise as a result of E-Mail-transmission or for damages resulting from any unauthorized changes of the content of this message and any attachment thereto. Chikka does not guarantee that this message is free of viruses and does not accept liability for any damages caused by any virus transmitted therewith. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I can't connect to mysql server with PHP
Hi everybody! I've install a mysql server on my computer. The operating system is Redhat fedora core 4. The version of the mysql server is 4.1.3 Beta. I wanna connect to the server using the following statement: $db_connection=mysql_pconnect(localhost,root,); The server returned the error 2002: 2002 Can't connect to local MySQL server through socket '/var/mysql/lib/mysql.sock(13)' I was suggested that the server may not running, or the sockect is wrong. But when I run the following statement, there was no error occured: localhost#/usr/local/mysql/bin/mysql -uroot -S/var/mysql/lib/mysql.sock Can anyone help me? Fang
Re: I can't connect to mysql server with PHP
fool.ben wrote: Hi everybody! I've install a mysql server on my computer. The operating system is Redhat fedora core 4. The version of the mysql server is 4.1.3 Beta. I wanna connect to the server using the following statement: $db_connection=mysql_pconnect(localhost,root,); The server returned the error 2002: 2002 Can't connect to local MySQL server through socket '/var/mysql/lib/mysql.sock(13)' The user running the php connection does not have permissions to open /var/mysql/lib/mysql.sock. I was suggested that the server may not running, or the sockect is wrong. But when I run the following statement, there was no error occured: localhost#/usr/local/mysql/bin/mysql -uroot -S/var/mysql/lib/mysql.sock Here you are root, so you have permissions. Can anyone help me? Fix your permissions or move your socket to a directory that is world searchable. Fang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find ids that are NOT in other two tables.
Yesmin Patwary wrote: Hi, Greatly appreciated your kind help. Would it work, if I rewrite your query as below to remove all records from company_db that don't have any relation to other two tables? DELETE FROM company_db c LEFT JOIN person p ON c.company_id = p.company_id LEFT JOIN customer cu ON c.company_id = cu.company_id WHERE p.company_id IS NULL AND cu.company_id IS NULL; I am not sure if this join query will remove records from other tables. The DELETE statement can specify which tables you want to delete from. Specify the table you want to delete from before the FROM keyword. And, yes, the query works properly, as the example below indicates :) [EMAIL PROTECTED]:~$ mysql -uroot -p test Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 5.0.18-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql CREATE TABLE t1 (id INT NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.21 sec) mysql CREATE TABLE t2 (id INT NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.01 sec) mysql CREATE TABLE t3 (id INT NOT NULL PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t1 VALUES (1),(2),(3),(4),(5); Query OK, 5 rows affected (0.05 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql INSERT INTO t2 VALUES (2),(4),(6),(8),(10); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql INSERT INTO t3 VALUES (3),(5),(7),(9),(11); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql DELETE t1 FROM t1 - LEFT JOIN t2 ON t1.id = t2.id - LEFT JOIN t3 ON t1.id = t3.id - WHERE t2.id IS NULL - AND t3.id IS NULL; Query OK, 1 row affected (0.01 sec) mysql SELECT * FROM t1; ++ | id | ++ | 2 | | 3 | | 4 | | 5 | ++ 4 rows in set (0.00 sec) mysql SELECT * FROM t2; ++ | id | ++ | 2 | | 4 | | 6 | | 8 | | 10 | ++ 5 rows in set (0.00 sec) mysql SELECT * FROM t3; ++ | id | ++ | 3 | | 5 | | 7 | | 9 | | 11 | ++ 5 rows in set (0.00 sec) Hope this answers your questions! Cheers, -jay Jay Pipes [EMAIL PROTECTED] wrote: Yesmin Patwary wrote: Dear All, Need to find ids that are NOT in other two tables. With the help of PHP scripting and three queries I am able to do the task. Can this be done with one mysql query? Below are current quires: SELECT DISTINCT company_id FROM company_db SELECT company_id FROM person WHERE company_id=\$company_id\ SELECT company_id FROM customer WHERE company_id=\$company_id\ Use outer joins: SELECT DISTINCT c.company_id FROM company_db c LEFT JOIN person p ON c.company_id = p.company_id LEFT JOIN customer cu ON c.company_id = cu.company_id WHERE p.company_id IS NULL AMD cu.company_id IS NULL; -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: limit
Hi Eko! It shouldn't be an issue with table size. More likely, it is due to duplicate records in the import file. Can you post the SHOW CREATE TABLE for your table in full (including primary key information)? Also, any output from your import run would be useful. Thanks! -- Jay Pipes Community Relations Manager, North America, MySQL Inc. Roaming North America, based in Columbus, Ohio email: [EMAIL PROTECTED]mob: +1 614 406 1267 Are You MySQL Certified? http://www.mysql.com/certification Got Cluster? http://www.mysql.com/cluster Eko Budiharto wrote: hi, I have a database from dbf file that has almost 100 thousands records, when I import them into mysql, it is only imported 13359 records. my table structure is like this: `faktur` varchar(10) NOT NULL, `tgl` date default NULL, `tgl_terima` date default NULL, `kontan` varchar(1) default NULL, `cust` varchar(10) default NULL, `slm` varchar(3) default NULL, `term` bigint(3) default NULL, `muka` decimal(8,2) default NULL, `disc1` decimal(4,2) default NULL, `ndisc1` decimal(10,2) default NULL, `ppn` decimal(4,2) default NULL, `mtr` decimal(8,2) default NULL, `netto` decimal(12,2) default NULL, `ket` varchar(30) default NULL, `total` decimal(12,2) default NULL, `f_pajak` varchar(10) default NULL, `tgl_pajak` date default NULL, `ncust` varchar(30) default NULL, `wil` varchar(3) default NULL, `so` varchar(10) default NULL, `satuan` varchar(1) default NULL, `post` varchar(1) default NULL, `vlt` varchar(3) default NULL, `tukar` decimal(4,2) default NULL, `sj1` varchar(10) default NULL, `sj2` varchar(10) default NULL, `sj3` varchar(10) default NULL, `sj4` varchar(10) default NULL, `sj5` varchar(10) default NULL, `disc2` decimal(4,2) default NULL, `ndisc2` decimal(10,2) default NULL, `disc3` decimal(4,2) default NULL, `ndisc3` decimal(10,2) default NULL, I am wondering how I can enlarge the capacity of the table. I am looking forward to a favorable reply from you. Thank you. - Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: aha! replication breaking due to duplicate queries
As an addendum to this -- this was solved. The master and slave were out of sync. -Sheeri On 5/19/06, Jeremy Cole [EMAIL PROTECTED] wrote: Hi Sheeri, So I've seen replication break a few times due to duplicate queries. A few times it was around a server crashing, but I thought perhaps it was because of the crash. (ie, master sends a query, crashes, and then tries to send the query again when it comes back up). But in the past 16 hours, it's happened twice. Both times, no crash. No network problems that we know of. No other query problems. Therefore, we've deduced it's in the code -- it's trying to insert a field with a duplicate primary key. I've sent that off to the developers, who will hit things with sticks. However, why does MySQL transfer over DML queries that fail? If they have an error, shouldn't they not replicate? This seems like a very large bug to me. It shouldn't, except in *very* limited circumstances (where you've already shot yourself in the foot a few times, generally). I have seen a case quite a few times where the slave hiccups and apparently runs the same query twice. Is it possible that this is what you're seeing? Is your system replicating a mix of large (images, maybe) and small (single-row) updates? Regards, Jeremy -- Jeremy Cole MySQL Geek, Yahoo! Inc. Desk: 408 349 5104 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb files
Well, you're going to need to state how big a record is, what OS platform you're using, what MySQL version you're using, and exactly what error message you get when you're trying to insert that 5th record. Your my.cnf would help, too. -Sheeri On 5/22/06, Eko Budiharto [EMAIL PROTECTED] wrote: hi, I still confuse how store very large database with innodb engine in mysql. I already activate one file per table (that will creates its own file per table .idb file), but I only can store 4 records only. Do you mind if someone can tell me how to store very large database with innodb engine in mysql? - Yahoo! Messenger with Voice. PC-to-Phone calls for ridiculously low rates. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find ids that are NOT in other two tables.
Uh, I don't have anything to add to this thread except that this was the *exact* problem I was trying to solve and this really helped me a lot. Thanks guys. -BL On 5/22/06, Jay Pipes [EMAIL PROTECTED] wrote: Yesmin Patwary wrote: Hi, Greatly appreciated your kind help. Would it work, if I rewrite your query as below to remove all records from company_db that don't have any relation to other two tables? DELETE FROM company_db c ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Crypt Functions
http://dev.mysql.com/doc/refman/4.1/en/encryption-functions.html On 5/21/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Hi Guys, Can somebody enlighten me on AES_CRYPT AES_DECRYPT functions on MySQL. I noticed that you can mention DES key file in mysqld statup options. how does the AES works? is there any one who used these functions (production systems)? Thanx in Advance, Kosala This message was sent using IMP, the Internet Messaging Program. -- 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]
Need guidance with perfomance improvement...
Hi, list! I have a little table (about 0.5 milloin records) which is kind of queue. My perl script takes records from it one by one and does somes actions. Here's my table structure: mysql desc queue; +--+-+--+-+---++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+---++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | status | tinyint(4) | | MUL | 0 || | processor_id | int(10) unsigned| YES | MUL | NULL || ... some other data ... +--+-+--+-+---++ This table, of course, has some indecies: mysql show indexes from queue; +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | queue| 0 | PRIMARY |1 | id | A | 522756 | NULL | NULL | | BTREE | | | queue| 1 | status |1 | status | A | 3 | NULL | NULL | | BTREE | | | queue| 1 | processor_id |1 | processor_id | A | 522756 | NULL | NULL | YES | BTREE | | +--++--+--+--+---+-+--++--++-+ 3 rows in set (0.00 sec) Perl scripts runs a query like 'UPDATE queue SET status=1, processor_id=XXX WHERE status=0 LIMIT 1'. Whis query shoud (and uses as we can see in following EXPLAIN result) `status` index and work rather fast. But.. It becomes slower and slower. I don't understand why. mysql explain select * from certificates where status=0; ++-+--+--+---++-+---+---+-+ | id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra | ++-+--+--+---++-+---+---+-+ | 1 | SIMPLE | queue| ref | status| status | 1 | const | 52740 | Using where | ++-+--+--+---++-+---+---+-+ 1 row in set (0.00 sec) Despite row numbers to scan (in EXPLAIN results) I think query should work fast because of 'LIMIT 1'. And it works. A query like 'SELECT * FROM queue WHERE status=0 LIMIT 1' works very fast. But in case of update I see that it's not so fast. mysql show processlist; +--+--+---+--+-+--+---+---+ | Id | User | Host | db | Command | Time | State | Info | +--+--+---+--+-+--+---+---+ | 3257 | root | localhost | certificates | Sleep | 247 | | NULL | | 3261 | root | localhost | certificates | Query |0 | Locked | SELECT id, domain FROM queue WHERE status='1' AND processor_id='10580' LIMIT 1 | | 3262 | root | localhost | certificates | Query |1 | Locked | UPDATE queue SET status='1', processor_id='10584' WHERE status='0' LIMIT 1| | 3263 | root | localhost | certificates | Query |1 | Locked | SELECT id, domain FROM queue WHERE status='1' AND processor_id='10588' LIMIT 1 | | 3264 | root | localhost | certificates | Query |1 | Locked | SELECT id, domain FROM queue WHERE status='1' AND processor_id='10590' LIMIT 1 | | 3265 | root | localhost | certificates | Query |1 | Locked | UPDATE queue SET status='1', processor_id='10592' WHERE status='0' LIMIT 1| | 3266 | root | localhost | certificates | Query |1 | Locked | UPDATE queue SET status='1', processor_id='10604' WHERE status='0' LIMIT 1| | 3267 | root | localhost | certificates | Query |1 | Locked | UPDATE queue SET status='1', processor_id='10614' WHERE status='0' LIMIT 1| | 3268 | root | localhost |
Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya
Re: Sparse 1.0b - framework for MySQL programs
Hi, According to the CC license, Creative Commons licenses are not intended to apply to software. They should not be used for software. So I'm not entirely sure why you're recommending it. ^^; It is released under the GPL. --Daniel Keith Roberts [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi Daniel. Distribute it under the CC license? http://creativecommons.org/about/licenses/index_html Keith In theory, theory and practice are the same; in practice they are not. On Fri, 19 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Sparse 1.0b - framework for MySQL programs I'd like to announce the first release of Sparse, a new way to create MySQL programs without actually programming anything! Sparse takes care of handling the SQL data, navigation, displaying errors, input validation, and caching. Using a few extra HTML tags allows surprising power, yet remains easy to use and very customizable. It can save a lot of time and effort during development! It's especially well-suited for creating admin back-ends of sites that use MySQL. This means that you can actually create entire MySQL-backed programs without using a single line of PHP code. However, more complex forms and queries can be easily integrated with PHP code as well. The Sparse homepage is here: http://sparse-php.sourceforge.net/ Besides looking for beta testers, I'd also like to know the best way to spread the word about it, so if anyone has any comments or help they can offer, please do so! Thanks! --Daniel Orner -- 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]
dump stored procedure
Hi, I would like to make a dump of my database. however, i have some stored procedure and they are not exported to the *.txt file i've created via mysqldump. So, how can i dump stored procedure ? thanks a lot, Alain
Re: dump stored procedure
In the last episode (May 22), Alain Roger said: I would like to make a dump of my database. however, i have some stored procedure and they are not exported to the *.txt file i've created via mysqldump. So, how can i dump stored procedure ? Add the -R flag to mysqldump: -R, --routines Dump stored routines (functions and procedures). -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql4.0
Does mysqlcheck sometimes repair problems that it does not report? I've seen a few instances where running mysqlcheck -r -f fixed application problems even though it reported all tables OK. --Eric -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
Only with MySQL 5.0 is multi-master replication possible. Guiseppe Maxia has a wonderful article about it at: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html -Sheeri On 5/22/06, Winanjaya - CBN [EMAIL PROTECTED] wrote: Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
On 5/22/06, Winanjaya - CBN [EMAIL PROTECTED] wrote: Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya On 5/22/06, sheeri kritzer [EMAIL PROTECTED] wrote: Only with MySQL 5.0 is multi-master replication possible. Guiseppe Maxia has a wonderful article about it at: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html -Sheeri The solution to his setup is not Multi-Master replication (even in Multi-Master replication any single instance of MySQL can have atmost one master), but a Slave with Multiple Masters, which offcourse is not possible unless he plans to run five different mysqld instances (diff ports and diff data dirs...) on the same box, Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sparse 1.0b - framework for MySQL programs
Thankyou for pointing that out Daniel. Regards Keith On Mon, 22 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Re: Sparse 1.0b - framework for MySQL programs Hi, According to the CC license, Creative Commons licenses are not intended to apply to software. They should not be used for software. So I'm not entirely sure why you're recommending it. ^^; It is released under the GPL. --Daniel Keith Roberts [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] Hi Daniel. Distribute it under the CC license? http://creativecommons.org/about/licenses/index_html Keith In theory, theory and practice are the same; in practice they are not. On Fri, 19 May 2006, Daniel Orner wrote: To: mysql@lists.mysql.com From: Daniel Orner [EMAIL PROTECTED] Subject: Sparse 1.0b - framework for MySQL programs I'd like to announce the first release of Sparse, a new way to create MySQL programs without actually programming anything! Sparse takes care of handling the SQL data, navigation, displaying errors, input validation, and caching. Using a few extra HTML tags allows surprising power, yet remains easy to use and very customizable. It can save a lot of time and effort during development! It's especially well-suited for creating admin back-ends of sites that use MySQL. This means that you can actually create entire MySQL-backed programs without using a single line of PHP code. However, more complex forms and queries can be easily integrated with PHP code as well. The Sparse homepage is here: http://sparse-php.sourceforge.net/ Besides looking for beta testers, I'd also like to know the best way to spread the word about it, so if anyone has any comments or help they can offer, please do so! Thanks! --Daniel Orner -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LEFT JOIN Multiple Tables
I have four tables that I join to get one record set about a customer. They are: UserInfo, ExtraAddr, DslInfo, and DslExtra. Specifically the main tables are joined with: SELECT * FROM UserInfo u, DslInfo d WHERE u.UserID = d.UserID; I've been adding more data to other tables and LEFT JOINING to get the data (since it's optional). SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x (It works on 4.x). I need to LEFT JOIN *two* tables, but I can't seem to get it. I just LEFT JOIN the DslExtra table in the above example it works just fine, it's only when I try and do the second that I get an error. Unknown column 'u.UserID' in 'on clause' Can I not do this type of multi table left join with 5.x? Or do I need to recraft the query? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN Multiple Tables
Scott SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x Right, as the 5.x docs say, 5.x wants ISO-compatible explicit joins, ie SELECT * FROM UserInfo u INNER JOIN DslInfo d USING (UserID) LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID; PB - Scott Baker wrote: I have four tables that I join to get one record set about a customer. They are: UserInfo, ExtraAddr, DslInfo, and DslExtra. Specifically the main tables are joined with: SELECT * FROM UserInfo u, DslInfo d WHERE u.UserID = d.UserID; I've been adding more data to other tables and LEFT JOINING to get the data (since it's optional). SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x (It works on 4.x). I need to LEFT JOIN *two* tables, but I can't seem to get it. I just LEFT JOIN the DslExtra table in the above example it works just fine, it's only when I try and do the second that I get an error. Unknown column 'u.UserID' in 'on clause' Can I not do this type of multi table left join with 5.x? Or do I need to recraft the query? -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 5/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN Multiple Tables
Perfect. This is exactly what I needed. Is there some place I get some more documentation on the specifics of the ISO-compatible queries? Might save me some hair-pulling-out in the future. Scott Peter Brawley wrote: Scott SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x Right, as the 5.x docs say, 5.x wants ISO-compatible explicit joins, ie SELECT * FROM UserInfo u INNER JOIN DslInfo d USING (UserID) LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID; PB - Scott Baker wrote: I have four tables that I join to get one record set about a customer. They are: UserInfo, ExtraAddr, DslInfo, and DslExtra. Specifically the main tables are joined with: SELECT * FROM UserInfo u, DslInfo d WHERE u.UserID = d.UserID; I've been adding more data to other tables and LEFT JOINING to get the data (since it's optional). SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x (It works on 4.x). I need to LEFT JOIN *two* tables, but I can't seem to get it. I just LEFT JOIN the DslExtra table in the above example it works just fine, it's only when I try and do the second that I get an error. Unknown column 'u.UserID' in 'on clause' Can I not do this type of multi table left join with 5.x? Or do I need to recraft the query? -- Scott Baker - RHCE Canby Telcom System Administrator 503.266.8253 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN Multiple Tables
Scott, Perfect. This is exactly what I needed. Is there some place I get some more documentation on the specifics of the ISO-compatible queries? Might save me some hair-pulling-out in the future. See 'Changes in 5.0.12' on the Joins manual page (http://dev.mysql.com/doc/refman/5.1/en/join.html). PB - Scott Peter Brawley wrote: Scott SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x Right, as the 5.x docs say, 5.x wants ISO-compatible explicit joins, ie SELECT * FROM UserInfo u INNER JOIN DslInfo d USING (UserID) LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID; PB - Scott Baker wrote: I have four tables that I join to get one record set about a customer. They are: UserInfo, ExtraAddr, DslInfo, and DslExtra. Specifically the main tables are joined with: SELECT * FROM UserInfo u, DslInfo d WHERE u.UserID = d.UserID; I've been adding more data to other tables and LEFT JOINING to get the data (since it's optional). SELECT * FROM UserInfo u, DslInfo d LEFT JOIN DslExtra e ON d.DslID = e.DslID LEFT JOIN ExtraAddr a ON a.UserID = u.UserID WHERE u.UserID = d.UserID; However it appears this syntax is not valid in MySQL 5.x (It works on 4.x). I need to LEFT JOIN *two* tables, but I can't seem to get it. I just LEFT JOIN the DslExtra table in the above example it works just fine, it's only when I try and do the second that I get an error. Unknown column 'u.UserID' in 'on clause' Can I not do this type of multi table left join with 5.x? Or do I need to recraft the query? No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.392 / Virus Database: 268.7.0/345 - Release Date: 5/22/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance Available
Hy Guys, I have a simple structure of tables, howewer has 50 million of registers and 2,5 GB of data. The table is MyIsam and has 4 integer fields and 1 Text field, one primary key with two integer fields and one btree index with one integer field. There is a select in this table using an index(with one integer field), whith a set value for this field ( select * from table where field in (value1,value2,value3,value4,etc) ). This select has delay 4s average. Is this a good time for the select ? How can I run this select in less time? I had make this optimization: 1-Compress the index 2-sort the index with myisamchk PS.: This table is read-only, hasn't an insert, update or delete. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need guidance with perfomance improvement...
Hi, Eugene! I suspect that you have run into locking issues with your table, which I am guessing is a MyISAM table. MyISAM provides only table-level locking - that is, only one write operation can occur at any time on the table. Other operations are blocked until their turn comes. It appears you have about 10 threads or processes running against this table, which is enough to introduce performance problems. Some suggestions: 1 - while your current select query is reasonably fast, it likely could be faster. It's using an indexed column (status), but it's not a very good index (no offense). There are only a few possible values, so the index isn't terribly helpful in locating matching records. If possible, you could re-write your perl scripts to do selects and updates against the ID column (primary key), which should be much faster. This may be enough to alleviate your locking problem; not sure. 2 - Eugene Kosov wrote: Hi, list! I have a little table (about 0.5 milloin records) which is kind of queue. My perl script takes records from it one by one and does somes actions. Here's my table structure: mysql desc queue; +--+-+--+-+---++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+---++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | status | tinyint(4) | | MUL | 0 || | processor_id | int(10) unsigned| YES | MUL | NULL || ... some other data ... +--+-+--+-+---++ This table, of course, has some indecies: mysql show indexes from queue; +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | queue| 0 | PRIMARY |1 | id | A | 522756 | NULL | NULL | | BTREE | | | queue| 1 | status |1 | status | A | 3 | NULL | NULL | | BTREE | | | queue| 1 | processor_id |1 | processor_id | A | 522756 | NULL | NULL | YES | BTREE | | +--++--+--+--+---+-+--++--++-+ 3 rows in set (0.00 sec) Perl scripts runs a query like 'UPDATE queue SET status=1, processor_id=XXX WHERE status=0 LIMIT 1'. Whis query shoud (and uses as we can see in following EXPLAIN result) `status` index and work rather fast. But.. It becomes slower and slower. I don't understand why. mysql explain select * from certificates where status=0; ++-+--+--+---++-+---+---+-+ | id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra | ++-+--+--+---++-+---+---+-+ | 1 | SIMPLE | queue| ref | status| status | 1 | const | 52740 | Using where | ++-+--+--+---++-+---+---+-+ 1 row in set (0.00 sec) Despite row numbers to scan (in EXPLAIN results) I think query should work fast because of 'LIMIT 1'. And it works. A query like 'SELECT * FROM queue WHERE status=0 LIMIT 1' works very fast. But in case of update I see that it's not so fast. mysql show processlist; +--+--+---+--+-+--+---+---+ | Id | User | Host | db | Command | Time | State | Info| +--+--+---+--+-+--+---+---+ | 3257 | root | localhost | certificates | Sleep | 247 | | NULL| | 3261 | root | localhost | certificates | Query |0 | Locked | SELECT id, domain FROM queue WHERE status='1' AND processor_id='10580' LIMIT 1 | | 3262 | root | localhost | certificates | Query |1 |
Re: Need guidance with perfomance improvement...
Hi, Eugene! Sorry, my last post was sent too soon. I suspect that you have run into locking issues with your table, which I am guessing is a MyISAM table. MyISAM uses table-level locking - that is, only one write operation can occur at any time on the table. Other operations are blocked until their turn comes. http://dev.mysql.com/doc/refman/5.0/en/table-locking.html It appears you have about 10 threads or processes running against this table, which is enough to introduce performance problems in your case. Some suggestions: 1 - while your current select query is reasonably fast, it likely could be faster. It may be that it's taking 0.9 seconds and feels fast, but perhaps it could be 0.09 seconds instead - and that's not something a human can really tell the difference on. It's using an indexed column (status), but it's not a very good index (no offense). There are only a few possible values in 500,000 records, so the index isn't terribly helpful in locating matching records. If possible, you could re-write your perl scripts to do selects and updates using the ID column (primary key), which should be faster than the status column alone. This may be enough to alleviate your locking problem; not sure. 2 - Switch to an InnoDB table, which should allow concurrent SELECT and UPDATE commands. This is a fairly big change if you're not used to InnoDB tables, so read up on this first. 3 - Depending on how you use this table, running OPTIMIZE TABLE periodically may help performance. Updates and deletes on a MyISAM table can cause a fragmented table and poor performance. In my last job I had a table with many thousands of INSERT and subsequent UPDATE operations every day, and it would regularly become fragmented to the point where performance was 1/4 of what it should have been. I started running an OPTIMIZE TABLE command every few hours, took a couple of seconds each time, and performance stayed good. Hope this helps, Eugene. Dan Eugene Kosov wrote: Hi, list! I have a little table (about 0.5 milloin records) which is kind of queue. My perl script takes records from it one by one and does somes actions. Here's my table structure: mysql desc queue; +--+-+--+-+---++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+---++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | status | tinyint(4) | | MUL | 0 || | processor_id | int(10) unsigned| YES | MUL | NULL || ... some other data ... +--+-+--+-+---++ This table, of course, has some indecies: mysql show indexes from queue; +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | queue| 0 | PRIMARY |1 | id | A | 522756 | NULL | NULL | | BTREE | | | queue| 1 | status |1 | status | A | 3 | NULL | NULL | | BTREE | | | queue| 1 | processor_id |1 | processor_id | A | 522756 | NULL | NULL | YES | BTREE | | +--++--+--+--+---+-+--++--++-+ 3 rows in set (0.00 sec) Perl scripts runs a query like 'UPDATE queue SET status=1, processor_id=XXX WHERE status=0 LIMIT 1'. Whis query shoud (and uses as we can see in following EXPLAIN result) `status` index and work rather fast. But.. It becomes slower and slower. I don't understand why. mysql explain select * from certificates where status=0; ++-+--+--+---++-+---+---+-+ | id | select_type | table| type | possible_keys | key| key_len | ref | rows | Extra | ++-+--+--+---++-+---+---+-+ | 1 | SIMPLE | queue| ref | status| status | 1 | const | 52740 | Using where | ++-+--+--+---++-+---+---+-+ 1 row in set (0.00 sec) Despite row numbers to
