Re: Issue With Subqueries
Hi Mike, On 11/8/2011 20:46, Mike Seda wrote: All, Can anyone out there explain the result of the third statement provided below: mysql select count(distinct field1) from db1.table1; ++ | count(distinct field1) | ++ | 1063 | ++ 1 row in set (0.01 sec) mysql select count(distinct field1) from db2.table1; ++ | count(distinct field1) | ++ | 3516 | ++ 1 row in set (0.03 sec) mysql select count(distinct field1) from db2.table1 where field1 not in (select field1 from db1.table1); ++ | count(distinct field1) | ++ | 0 | ++ 1 row in set (0.08 sec) A colleague of mine is stating that the result should be much greater than 0. Please let me know what you think. Thanks In Advance, Mike Simple math (set theory) suggests that all of the values of field1 on db2.table1 contain only copies or duplicates of the field1 values in the rows in db1.table1. Try this: SELECT db2.field1, db1.field1 FROM db2.table1 LEFT JOIN db1.table1 ON db2.field1 = db1.field1 WHERE db1.field1 IS NULL; How many rows do you get back from that? -- Shawn Green MySQL Principal Technical Support Engineer Oracle USA, Inc. - Hardware and Software, Engineered to Work Together. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Issue With Subqueries
Hi, mysql select count(distinct field1) from db2.table1 where field1 not in (select field1 from db1.table1); ++ | count(distinct field1) | ++ If the field db1.table1.field1 has the same name that the field db2.table1.field1, maybe you have to use an alias for each table: try: mysql select count(distinct A.field1) from db2.table1 A where field1 not in (select B.field1 from db1.table1 B); -- Javier
Re: Deleting Records in Big tables
If you could use MyISAM tables then you could use Merge Tables and create a table for each day (or whatever period you are collecting data for). Then when it is time to get rid of the old data, drop the oldest table (T2001 or T10 for 10 days ago) and create a new empty table for the new day, and redefine the Merge table definition. This can be done in under 1 second. You have the ability to access the Merge Table directly or each individual table that makes up the Merge Table. (The Merge table is a logical representation of MyISAM tables and requires no data copying). Mike At 01:22 AM 11/4/2011, Adarsh Sharma wrote: Dear all, Today I need to delete some records in 70 GB tables. I have 4 tables in mysql database. my delete command is :- delete from metadata where id2474; but it takes hours to complete. One of my table structure is as :- CREATE TABLE `metadata` ( `meta_id` bigint(20) NOT NULL AUTO_INCREMENT, `id` bigint(20) DEFAULT NULL, `url` varchar(800) DEFAULT NULL, `meta_field` varchar(200) DEFAULT NULL, `meta_value` varchar(2000) DEFAULT NULL, `dt_stamp` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`meta_id`) ) ENGINE=InnoDB AUTO_INCREMENT=388780373 ; Please let me know any quickest way to do this. I tried to create indexes in these tables on id, but this too takes time. Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Issue With Subqueries
Rong, On the server, we are using the following RPMs (from MySQL): - MySQL-server-5.5.11-1.rhel5.x86_64 - MySQL-client-5.5.11-1.rhel5.x86_64 On the clients, we are using the following RPM (from Red Hat): - mysql-5.0.45-7.el5.x86_64 RPM Mike On 11/09/2011 10:49 AM, Rong Chen wrote: Javier, I tried it, and still get the result of 0. It is a bug in the mysql. Mike, could you post the version of mysql? Thank you. Rong */Rong Chen, Ph.D./* /Bioinformatics Scientist, Butte Lab/ /Division of Systems Medicine, MC5415/ /Dept. of Pediatrics/ /MSOB X155/ /1265 Welch Road/ /Stanford University/ /Stanford, CA 94305/ /Tel: 858-837-2265 (cell)/ /Fax: 650-724-2259/ /Twitter: @RongChenBioinfo http://twitter.com/#%21/RongChenBioinfo/ /http://www.stanford.edu/~rchen1 http://www.stanford.edu/%7Erchen1/ *From:*Javier Yévenez [mailto:jyeve...@gmail.com] *Sent:* Wednesday, November 09, 2011 7:19 AM *To:* Shawn Green (MySQL) *Cc:* Mike Seda; mysql@lists.mysql.com; Rong Chen *Subject:* Re: Issue With Subqueries Hi, mysql select count(distinct field1) from db2.table1 where field1 not in (select field1 from db1.table1); ++ | count(distinct field1) | ++ If the field db1.table1.field1 has the same name that the field db2.table1.field1, maybe you have to use an alias for each table: try: mysql select count(distinct A.field1) from db2.table1 A where field1 not in (select B.field1 from db1.table1 B); -- Javier
Re: Reusing ibdata1 space
Hello Reindi, I don't mean to revisit an old post however, we are also using innodb and experiencing a rough start. Should we set innodb_file_per_table to 1? Also we expereince this scenario a lot: mysql show tables; +---+ | Tables_in_symax | +---+ | acc | ERROR 1146 (42S02): Table 'symax.acc' doesn't exist I should mention that we have deleted the ib_* files in the past. Is it ok if I post our config for a quick review? Thanks in Advance, Nick. On Tue, Nov 1, 2011 at 10:09 AM, Reindl Harald h.rei...@thelounge.net wrote: Am 01.11.2011 15:02, schrieb Rozeboom, Kay [DAS]: We are running MySQL 5.0.77, and using INNODB in production for the first time. The production database has a lot of inserts and deletes, and the shared ibdata1 file is continually growing. I understand that to return the unused space to the operating system, we must delete and recreate ibdata1 and its associated .frm files. I am wondering if we could do the following instead: 1) Let ibdata1 grow for a while. 2) Rebuild the tables periodically using this syntax: ALTER TABLE t1 ENGINE = InnoDB; this will not help as long you are not using innodb_file_per_table and if you would using it ibdata1 would not grow in my opinion innodb_file_per_table=0 is a dumb default and requires that people with too few expierience with mysql/innodb would much more carefully read documentations as they usually do -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Restore data from MySQL data files
Hi, Recently a home server has crashed and would not boot anymore into Windows. We decided to replace it altogether and I managed to repair the file system more or less so that I could copy some directories from the broken disk. One of them is the MySQL data directory of that XAMPP installation. I have now set up a new computer and tried to use the old data files but the MySQL service doesn't start. The old machine was an unknown MySQL version on Windows XP, the new is running MySQL 5.5.16 on Windows 7 (32 bit). The error log is attached to this mail. It all sounds like real bugs in MySQL, at least it's standing in the file. I already ran mysql_upgrade.exe but it didn't change anything. What can I do now to restore that data? -- Yves Goergen LonelyPixel nospam.l...@unclassified.de Visit my web laboratory at http://beta.unclassified.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Restore data from MySQL data files
Am 09.11.2011 21:46, schrieb Yves Goergen: The old machine was an unknown MySQL version on Windows XP pfff - unknown version? how comes? The error log is attached to this mail. It all sounds like real bugs in MySQL, at least it's standing in the file there is no attachment so please CLEAR the log, try again restart and post the log here inline signature.asc Description: OpenPGP digital signature
Re: Restore data from MySQL data files
On 09.11.2011 21:58 CE(S)T, Reindl Harald wrote: Am 09.11.2011 21:46, schrieb Yves Goergen: The old machine was an unknown MySQL version on Windows XP pfff - unknown version? how comes? The old MySQL installation doesn't run anymore because the OS is gone. I could only backup the files. The error log is attached to this mail. It all sounds like real bugs in MySQL, at least it's standing in the file there is no attachment so please CLEAR the log, try again restart and post the log here inline Done, here it is: 09 22:49:59 [Note] Plugin 'FEDERATED' is disabled. c:\xampp\mysql\bin\mysqld.exe: Table 'mysql.plugin' doesn't exist 09 22:49:59 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. (That's what I did.) 09 22:49:59 InnoDB: The InnoDB memory heap is disabled 09 22:49:59 InnoDB: Mutexes and rw_locks use Windows interlocked functions 09 22:49:59 InnoDB: Compressed tables use zlib 1.2.3 09 22:49:59 InnoDB: Initializing buffer pool, size = 16.0M 09 22:49:59 InnoDB: Completed initialization of buffer pool 09 22:49:59 InnoDB: highest supported file format is Barracuda. InnoDB: Resetting space id's in the doublewrite buffer 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB: Error: trying to access tablespace 1 page no. 2, InnoDB: but the tablespace does not exist or is just being dropped. 09 22:49:59 InnoDB:
Re: Reusing ibdata1 space
Hello Reindl, I just noticed that I misspelled your name. Sorry about that! Cheers, Nick. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Benetl, a free ETL tool for MySQL, is out in version 3.9.
Dear all, Benetl, a free ETL tool for MySQL, is out in version 3.9. This version is correcting two bugs in GUI and brings code improvement. Benetl is freely dowloadable at: http://www.benetl.net You can learn more about ETL tools at: http://en.wikipedia.org/wiki/Extract,_transform,_load Thanks for your interest. Regards, -- Benoît Carpentier http://www.benetl.net Founder of Benetl and Java project manager -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
