Re: Deleting Records in Big tables
- Original Message - From: mos mo...@fastmail.fm If you could use MyISAM tables then you could use Merge Tables and Ick, merge tables :-) If your version is recent enough (Isn't 4.whatever long out of support anyway?) you're much better off using partitioning - it's engine-agnostic and has a lot more features. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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
- Original Message - From: Javier Yévenez jyeve...@gmail.com 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: No, he's using a subquery, those fields cannot be referenced. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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
- Original Message - From: Shawn Green (MySQL) shawn.l.gr...@oracle.com 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. Shawn, The select count(distinct)s show that db1.table1.field1 has a lot less unique values than db2.table1.field1, so I'm kind of struggling to understand how 3516 unique values can be mapped to a subset of 1063 unique values :-) SELECT db2.field1, db1.field1 FROM db2.table1 LEFT JOIN db1.table1 ON db2.field1 = db1.field1 WHERE db1.field1 IS NULL; That's the interesting bit, indeed - if that gives back a more sensible number (logic dictates at least 2053 of the 3516 can not be the same as the 1063) it'll prove that there is indeed a bug somewhere. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- 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
Hah. I suspect the machine was running when it crashed - that is, the mysql was shut down uncleanly? You can safely ignore the mysql.plugin bit for the moment - that's for when it's booted properly. It looks like the newer InnoDB has trouble reading your ibdata file. Possible causes: * Are all the files in the right place as referenced by your my.cnf ? * Does the user the daemon run as have proper permissions to access the files? If in doubt, set the files to read/write for everyone. * Possible physical file corruption - did your old server die from a bad disk? It's also possible that the newer InnoDB recovery code is unable to properly handle the older file format. I strongly suggest to figure out what the old version was and set that up to attempt recovery, and only upgrade based off cleanly closed datafiles, with innodb_fast_shutdown set to 0 - see http://dev.mysql.com/doc/refman/5.1/en/innodb-parameters.html#sysvar_innodb_fast_shutdown for info on that. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
LOAD DATA INFILE Problem
Hello, after switching from MySQL 5.0.67 to 5.1.59 we get the following problem: We want to import data from a textfile example of the textfile: t...@test.com$yes$432145$xyz The command is: LOAD DATA LOCAL INFILE 'textfile.txt' INTO TABLE TESTTABLE FIELDS TERMINATED BY '$'; Selecting the data from the TESTTABLE by select * from TESTTABLE; the data in the first field looks like |test.com, if I do a select * from TESTTABLE\G I can see t...@test.com in the first field. Are there any changes between MySQL 5.0 and 5.1 ? Best Regards Spiker -- Empfehlen Sie GMX DSL Ihren Freunden und Bekannten und wir belohnen Sie mit bis zu 50,- Euro! https://freundschaftswerbung.gmx.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: Reusing ibdata1 space
- Original Message - From: Nick Khamis sym...@gmail.com I should mention that we have deleted the ib_* files in the past. I hope that was an accident, because if you thought that was a good idea I'm sending someone over with the spiked cluebat. Luckily for you, the solution to that particular problem (in the cases where it's indeed caused by the delete of the ib_data files) is more deletes, and you're good at those :-p The tables show up in the listing because in the mysqldata/database directory there are .frm (table descriptor) files created for tables of all engines, even though those files are really artifacts from the MyISAM legacy. Thus, the server scans it, lists it, notices it's an InnoDB table and then fails to find it in the InnoDB data dictionary because you deleted the one it was in. Simply delete the .frm file for such tables, and they'll no longer show up. As for file_per_table, it's generally a good idea to set that to 1, yes. Be aware that you may need to tune other MySQL and/or OS level settings, too, for example max_open_files. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Relication Issues
Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. Thanks! Steve. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Relication Issues
replication is buggy sometimes but without any outputs desribing your problem nobody can really help you - memory tables as example are making much more troubles as myisam Am 10.11.2011 14:26, schrieb Steven Staples: Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. signature.asc Description: OpenPGP digital signature
RE: Relication Issues
My Apologies, The tables are all MyISAM, most of the inserts/updates/deletes are done through stored procedures on the master. There are about 2 stored procedure calls per second, consisting of a whole bunch of queries, updates, and inserts within them. The latest issue(s) we're having, are: Could not execute Write_rows event on table xxx.x; Duplicate entry '20-1016792' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's mas Could not execute Write_rows event on table xxx.x; Duplicate entry '44870420' for key 'PRIMARY', Er Update_rows event on table xxx.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY (Where xxx is the databse, and x is the table) The replication broke yesterday morning for some reason, and the primary key index was at around 900,000. When we found out it was down, and ended up getting to work on it, we tried to restart it and the next ID it was inserting for was 1,016,792. So we're missing about 100,000 enteries, and they are not in the bin log (as far as I can see). Steve. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: November 10, 2011 8:42 AM To: mysql@lists.mysql.com Subject: Re: Relication Issues replication is buggy sometimes but without any outputs desribing your problem nobody can really help you - memory tables as example are making much more troubles as myisam Am 10.11.2011 14:26, schrieb Steven Staples: Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Relication Issues
Hi, It could be a hardware issue, as if you do not use RAID and you have bad sectors or the likes. But the most common cause could be a server crash. The INSERT or transaction was partially completed when server crash. When the slave is then restarted, it will try to execute the transaction/statement again *from the beginning*, and will fail since some of the rows were already added to the table. The situation occurs also if you are using MyISAM tables and a statement outside a transaction is interrupted. Thanks, On Thu, Nov 10, 2011 at 6:08 AM, Steven Staples sstap...@mnsi.net wrote: My Apologies, The tables are all MyISAM, most of the inserts/updates/deletes are done through stored procedures on the master. There are about 2 stored procedure calls per second, consisting of a whole bunch of queries, updates, and inserts within them. The latest issue(s) we're having, are: Could not execute Write_rows event on table xxx.x; Duplicate entry '20-1016792' for key 'PRIMARY', Error_code: 1062; handler error HA_ERR_FOUND_DUPP_KEY; the event's mas Could not execute Write_rows event on table xxx.x; Duplicate entry '44870420' for key 'PRIMARY', Er Update_rows event on table xxx.x; Can't find record in 'x', Error_code: 1032; handler error HA_ERR_KEY (Where xxx is the databse, and x is the table) The replication broke yesterday morning for some reason, and the primary key index was at around 900,000. When we found out it was down, and ended up getting to work on it, we tried to restart it and the next ID it was inserting for was 1,016,792. So we're missing about 100,000 enteries, and they are not in the bin log (as far as I can see). Steve. -Original Message- From: Reindl Harald [mailto:h.rei...@thelounge.net] Sent: November 10, 2011 8:42 AM To: mysql@lists.mysql.com Subject: Re: Relication Issues replication is buggy sometimes but without any outputs desribing your problem nobody can really help you - memory tables as example are making much more troubles as myisam Am 10.11.2011 14:26, schrieb Steven Staples: Good morning list, I am having issues with my replication setup. it seems that every few weeks/months something happens, and I have to restart it, skip a row, or delete, rsync and restart replication from scratch. The databases that are being replicated, are rather large... there are about 12 new tables every year, each consisting of about 2-4g in size each (today's total database size is 83g). Again, things work fine for a while, and then there are issues. The backup/slave is on the same network/datacenter, so network speed isn't an issue. I just don't understand why inconsistencies keep arising. Is there a better way to do live backups, or have a hot space in the event of a catastrophe? Is there 3rd party software that would better achieve data integrity or something? Any help here would be appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Reusing ibdata1 space
I wonder, if there could be any method to regain InnoDB space other than dump the whole database and reimport. Thanks, On Thu, Nov 10, 2011 at 12:44 AM, Johan De Meersman vegiv...@tuxera.bewrote: - Original Message - From: Nick Khamis sym...@gmail.com I should mention that we have deleted the ib_* files in the past. I hope that was an accident, because if you thought that was a good idea I'm sending someone over with the spiked cluebat. Luckily for you, the solution to that particular problem (in the cases where it's indeed caused by the delete of the ib_data files) is more deletes, and you're good at those :-p The tables show up in the listing because in the mysqldata/database directory there are .frm (table descriptor) files created for tables of all engines, even though those files are really artifacts from the MyISAM legacy. Thus, the server scans it, lists it, notices it's an InnoDB table and then fails to find it in the InnoDB data dictionary because you deleted the one it was in. Simply delete the .frm file for such tables, and they'll no longer show up. As for file_per_table, it's generally a good idea to set that to 1, yes. Be aware that you may need to tune other MySQL and/or OS level settings, too, for example max_open_files. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=aim.prab...@gmail.com -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: Reusing ibdata1 space
- Original Message - From: Prabhat Kumar aim.prab...@gmail.com I wonder, if there could be any method to regain InnoDB space other than dump the whole database and reimport. Very simple answer to that: no. -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
InnoDB free - What does it really mean?
In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
Re: InnoDB free - What does it really mean?
Hi, The comment is just telling you how much free space is in your InnoDB datafile(s). When that approaches 0, InnoDB will add the data file. Image that there's a box, say it Innodb tablespace, this box is consist of your data, and innodb free is the same as the (capacity of your box - usage) To increase the size simply add innodb data file. MySQL documentation: The data files (tables) that you define in an InnoDB form the tablespace of InnoDB. The tablespace consists of database pages with a default size of 16KB. The pages are grouped into extents of 64 consecutive pages (i.e. 1024Kb). InnoDB allocates space starting from the first data file (table). InnoDB can add a large segment up to 4 extents at a time to ensure good sequentiality of data (4096Kb). Thanks, On Thu, Nov 10, 2011 at 10:03 AM, Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov wrote: In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov -- Best Regards, Prabhat Kumar MySQL DBA My Blog: http://adminlinux.blogspot.com My LinkedIn: http://www.linkedin.com/in/profileprabhat
Re: InnoDB free - What does it really mean?
it refers to free innodb tablespace. From: Rozeboom, Kay [DAS] kay.rozeb...@iowa.gov To: mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, November 10, 2011 10:03 AM Subject: InnoDB free - What does it really mean? In the show table status output, there is comment field labeled InnoDB free. Can someone explain what kind of free space is counted in this figure? Is it space that is not currently part of any segment? Does it include empty pages within segments? Does it include unused space within pages? Kay Rozeboom Information Technology Enterprise Iowa Department of Administrative Services Telephone: 515.281.6139 Fax: 515.281.6137 Email: kay.rozeb...@iowa.gov
