Re: MySQL Performance Analysis tools
Bernd Jagla wrote: sar will give you some basic information about what happens on the system... (see e.g.: http://linux.die.net/man/1/sar)... Munin (http://munin.projects.linpro.no/) will generate graphs and stats over time for system usage (cpu, mem load, disk usage etc) and includes good support for mysql graphing throughput, queries, threads and slow queries. Example here: http://munin.ping.uio.no/ping.uio.no/dahl.ping.uio.no.html mark | MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- |From: thomas Armstrong [mailto:[EMAIL PROTECTED] |Sent: Monday, November 19, 2007 6:42 AM |To: mysql@lists.mysql.com |Subject: MySQL Performance Analysis tools | |Hi. | |Using MySQL on Linux, I'd like to analyze the performance and know how |resources (memory, threads) are used during a period of time. | |Do you know any tool to carry it out? Thank you very much. | |-- |MySQL General Mailing List |For list archives: http://lists.mysql.com/mysql |To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL - master/slave replation question
bruce wrote: Hi... If I have a master/slave setup, I can do a mysqlshow slave status\G and get information on the overall status of the slave. Is there a way to break out this information without having to parse the output? In other words, are there other cmds that might provide the different pieces of information in a way that won't require me to parse it? Basically, I'm trying to figure out the best approach to being able to automatically look at a mySQL/Slave and determine i it's connected to the master, and if it's relatively up to date, relative to the master. If you want to check if the slaves are in sync I'd use the table checksum that is part of Baron's mysql toolkit: http://mysqltoolkit.sourceforge.net/ mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Use select within delete
Dario Hernan wrote: Hi all I need to delete some fields from a table but in the where clause I need to put a select command. For instance, delete from table1 where id=(select id from table2 where dateoneweek) Is it possible in mysql 4.0.24?? Thanks in advance Dario Not until 4.1. What you can do instead is run the select into a temp table and then run the delete as a join with that temp table. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Transactions and locking
Baron Schwartz wrote: Yves Goergen wrote: (For the record... I missed the mailing list recipient - again!!) On 13.11.2007 00:30 CE(S)T, Perrin Harkins wrote: On Nov 12, 2007 5:58 PM, Yves Goergen [EMAIL PROTECTED] wrote: First I find a new id value, then I do several INSERTs that need to be atomic, and especially roll back completely if a later one fails. If you use a table lock on the first table where you get the ID, you know that ID is safe to use. Using a table lock when you get the ID and then trusting transactions to roll back all the inserts in the event of a later failure should work fine. From what I've read about MySQL's table locks and InnoDB, you cannot use LOCK TABLES with transactions. Either of them deactivates the other one. Beginning a transaction unlockes all tables, locking tables ends a transaction. It's more complicated than that. You can use them together, you just have to do it like this: set autocommit = 0; begin; lock tables; -- you are now in a transaction automatically begun by LOCK TABLES . commit; -- your tables are now unlocked. In fact, you *must* use a transaction for LOCK TABLES to be safe, at least in MySQL 5. Even if you're using non-transactional tables. Otherwise, you can get nasty behavior. See http://bugs.mysql.com/bug.php?id=31479 The manual isn't very clear on the interaction between LOCK TABLES and transactions, it's true. But this is what I've found. As your using InnoDB, which has row level locking a SELECT ... FOR UPDATE should work. http://dev.mysql.com/doc/refman/4.1/en/innodb-locking-reads.html e.g. BEGIN TRANSACTION new_id := (SELECT MAX(id) FROM table FOR UPDATE) + 1 -- some more work here INSERT INTO table (id, ...) VALUES (new_id, ...) COMMIT mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You.
RE: DB Schema Comparison Utility ?
On Mon, 2007-10-01 at 12:56 -0700, Daevid Vincent wrote: This has been asked for many many times on this list, not sure why mySQL AB doesn't just release a command line tool like a 'mysql diff' and also a 'mysql lint'. The lint one should be totally trivial for them to do, as they already have a SQL parser! I can't tell you how many times our daily build was broken by a missing semi-colon or some other SQL syntax error. We run all commits through php -l and ruby's checker, but mysql is the only one we have to sweat over. While I'm glad that pretty GUI tools like Upscene's exist, that doesn't do us any good on a linux build system where it does an svn checkout, runs automated BVT tests, compiles code, uploads to a daily build directory, etc. We need command line tools that run on linux. :( :) Theres a perl tool called SLQ Fairy. Its main use is translating DDL to other dialects, html, images, templates etc but it also has a very handy sqlt-diff that does want you want from the command line. http://search.cpan.org/~jrobinson/SQL-Translator-0.08001/ http://sqlfairy.sourceforge.net/ # cpan SQL::Translater hth, mark MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK P Please consider the environment. Do you really need to print this email? -Original Message- From: John Comerford [mailto:[EMAIL PROTECTED] Sent: Thursday, September 27, 2007 9:49 PM To: mysql@lists.mysql.com Subject: DB Schema Comparison Utility ? Hi Folks, I am new to MySQL. We have a development environment where we have three systems 1) Developement Database on Machine A 2) Test Databasae on Machine B 3) Live Database on Machine C So we make changes to the Developement Database, then move them to test then to live. My question is, is there a way of automatically migrating the changes from Dev to Test, Test to Live ? I have worked with a DB which had an admin function wherein you could connect two databases and it would run a comparison between the two databases and produce a file of schema differences which you could import to make the two DB schema's the same. Is there something like this for MySQL ? TIA, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting numerically within a varchar
As I've often thought it would be very useful if MySQL regexp support included being able to pull out the substring matched (and do regexp substitutions). Although that could just be my perl sensibilities ;-) mark On Tue, 2006-10-03 at 17:27 -0500, mos wrote: James, That wasn't too easy to figure out. But this will work: select * from Table1 order by substring_index(Music_Title,' ',2),-- Extracts first 2 words 0+Substring_Index(Substring_index(Music_Title,'-',1),' ',-2), -- Extracts the number substring_index(Music_Title,' ',-1) -- Extracts the last word Mike At 02:42 PM 10/3/2006, you wrote: If I have the following strings in a varchar column: Piano Sonata 1 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 2 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel Is it possible to sort the records into the following order? Piano Sonata 1 - Brendel Piano Sonata 2 - Brendel Piano Sonata 10 - Brendel Piano Sonata 11 - Brendel Piano Sonata 12 - Brendel Piano Sonata 13 - Brendel Piano Sonata 14 - Brendel Piano Sonata 15 - Brendel Piano Sonata 16 - Brendel Piano Sonata 17 - Brendel Piano Sonata 18 - Brendel Piano Sonata 19 - Brendel Piano Sonata 20 - Brendel Piano Sonata 21 (Waldstein) - Brendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: comparing two databases
On Thu, 2006-09-28 at 15:06 -0500, Steve Buehler wrote: Is there a program out there that I can use to compare two databases? Just the structure, not the content. SqlFairy (http://sqlfairy.sourceforge.net/) has a sqlt-diff tool that will output the differences as a set of sql alter statements. Easiest way to install on nix box is cpan: $ sudo cpan SQL::Translator). mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple table inserts
On Tue, 2006-08-15 at 07:56 -0700, bruce wrote: hi... the mysql docs don't seem to allow this.. but i'll ask. is there a way to accomplish: insert into cat,dog (cat.x,dog.y) values (1,2) allowing me to essentially insert items into multiple tables simultaneously... If your using version 5 you can create a view accross the tables and insert into that. mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication Binary Logs - How Long to Keep?
On Sat, 2006-08-12 at 08:38 -0400, Warren Crigger wrote: Note that you should not just delete the bin logs. Instead use PURGE MASTER LOGS. See http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html hth, mark Sorry, accidently hit Ctrl/Enter :( Anyway, I can't purge with that command: mysql PURGE MASTER LOGS TO 'mysql-bin.023'; ERROR: A purgeable log is in use, will not purge Any ideas? I'm tempted to just delete but would prefer to do this the right way, and for some reason it thinks they are in use :/. I'm showing: mysql show master status; +--+---+--+--+ | File | Position | Binlog_do_db | Binlog_ignore_db | +--+---+--+--+ | repl.024 | 110962544 | | | +--+---+--+--+ 1 row in set (0.00 sec) That File column looks wrong, the name should match your setting for the name of the binary log e.g. 'mysql-bin.023'. 'repl' looks like the name of a relay log, which is what slaves use to update them selves. Can you send the output of SHOW MASTER STATUS; and SHOW SLAVE STATUS; for both boxes? Note if you use \G for the slave on the mysql command line the output is much easy to read. e.g. mysql SHOW SLAVE STATUS\G Also you might find running SHOW PROCESSLIST; on the servers usefull, if the bin log is in use you should be able to see the replication processes using it. cheers, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running mysqld on certain interface
On Mon, 2006-08-14 at 00:27 -0700, The Nice Spider wrote: How do I set mysqld hears only on eth0? i am using FC linux. Add a setting for bind_address under the mysqld section in the my.cnf file, set to the IP of eth0 (then restart the mysql server). hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem with decimal part
On Fri, 2006-08-11 at 10:30 +0100, Jorge Martins wrote: Hi, I have a client that want's to store in a table the exact number that he get's from a file, i've used a DOUBLE data type (MySQL 3.2x), but I have the following problem: If the number is for example 9.0 mysql truncates and only stores 9 I tried to use the (M,D) for example as (6,5) but the problem is that mysql stores the number as 9.0 and I don't want that, I want the number to be the exact number I read from the file. Technically 9, 9.0 and 9.0 are exacatly the same _number_. Is there any way to fix that? I thought of using a VARCHAR data type. What do you think? Would be the way to go as what your after is the origional string value. Why do you need exactly the same string? MySQL is pretty good at auto casting so you can still treat the field as a number in most cases and see http://dev.mysql.com/doc/refman/4.1/en/cast-functions.html for functions to use in the other cases. mark Thanks -- Com os melhores cumprimentos Jorge Martins - Wemake, Tecnologias de Informação, Lda. Tel. 223744827 MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Replication Binary Logs - How Long to Keep?
On Thu, 2006-08-10 at 14:47 +1000, Logan, David (SST - Adelaide) wrote: Hi Warren, I would generally keep the binary log until you have done a full backup. Once that is done, all the transactions that are contained in those logs are now committed and saved in your database backup. snip You also need to keep any binary logs until all slaves to that server have finished with them. You can find that out by running SHOW SLAVE STATUS; # on the slave(s) look for Master_Log_File SHOW MASTER STATUS; # on the master and comparing the binary log filenames. You can delete any numbered less than the lowest numbered log listed in the slave status output. I found that when I set this up by default mysql didn't create a new bin log until the old one was over a gig, which meant it couldn't be cleared up for weeks (not a busy database). So I set the max_binlog_size option in my.cnf to a 100 meg so I could clean up more often. Note that you should not just delete the bin logs. Instead use PURGE MASTER LOGS. See http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html hth, mark MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK -Original Message- From: Warren Crigger [mailto:[EMAIL PROTECTED] Sent: Thursday, 10 August 2006 9:52 AM To: mysql@lists.mysql.com Cc: [EMAIL PROTECTED] Subject: MySQL Replication Binary Logs - How Long to Keep? I've just recently set up MySQL replication amongst two servers so I'm not too familiar with it. I was cleaning up my /var filesystem and found the binary data below being stored in /var/lib/mysql, taking up 1.5gb. I did a little reading on mysql.org docs. My interpretation was that you can have it replicate every so often, then you can purge these after that happens..however, my replication is instantaneous. I can insert a record on the master and then go select it on the slave immediately. Is there any reason to keep this data? It's replicated to the 2nd server, in addition to dumps of the more important databases nightly, and dumps of the entire server weekly, which go to different physical drives and then ultimately off-site. I like redudancy obviously, however, this seems pretty useless to me. If it is of use, is there a way I can put this on a different filesystem, keeping the databases themselves within /var/lib/mysql? Thanks, Warren [EMAIL PROTECTED] mysql]# pwd /var/lib/mysql [EMAIL PROTECTED] mysql]# ls -lart |grep repl -rw-rw1 mysqlmysql 3088434 Jun 11 04:02 repl.001 -rw-rw1 mysqlmysql 107 Jun 11 04:02 repl.002 -rw-rw1 mysqlmysql188387006 Jun 18 04:02 repl.003 -rw-rw1 mysqlmysql 107 Jun 18 04:02 repl.004 -rw-rw1 mysqlmysql 107 Jun 25 04:02 repl.006 -rw-rw1 mysqlmysql156749380 Jun 25 04:02 repl.005 -rw-rw1 mysqlmysql153489679 Jul 2 04:02 repl.007 -rw-rw1 mysqlmysql 107 Jul 2 04:02 repl.008 -rw-rw1 mysqlmysql 107 Jul 9 04:02 repl.010 -rw-rw1 mysqlmysql140922795 Jul 9 04:02 repl.009 -rw-rw1 mysqlmysql58638790 Jul 11 17:30 repl.011 -rw-rw1 mysqlmysql 38410 Jul 11 17:46 repl.012 -rw-rw1 mysqlmysql 5927431 Jul 12 09:00 repl.013 -rw-rw1 mysqlmysql80007235 Jul 16 04:02 repl.014 -rw-rw1 mysqlmysql 107 Jul 16 04:02 repl.015 -rw-rw1 mysqlmysql 107 Jul 23 04:02 repl.017 -rw-rw1 mysqlmysql155468996 Jul 23 04:02 repl.016 -rw-rw1 mysqlmysql 107 Jul 30 04:02 repl.019 -rw-rw1 mysqlmysql155806419 Jul 30 04:02 repl.018 -rw-rw1 mysqlmysql 107 Aug 6 04:02 repl.021 -rw-rw1 mysqlmysql159420166 Aug 6 04:02 repl.020 -rw-rw1 mysqlmysql 6366383 Aug 6 13:33 repl.022 -rw-rw1 mysqlmysql 1138297 Aug 6 17:36 repl.023 -rw-rw1 mysqlmysql 264 Aug 6 17:40 repl.index -rw-rw1 mysqlmysql43014905 Aug 9 00:03 repl.024 Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Adding Foreign Key Fails
On Wed, 2006-07-26 at 08:58 -0400, Jesse wrote: I am trying to add a foreign key to one of my tables. When I execute the following SQL Code: ALTER TABLE `bpa`.`confinvitems` ADD CONSTRAINT `FK_confinvitems_1` FOREIGN KEY `FK_confinvitems_1` (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE; I get the error: MySQL Error Number 1452 Cannot add or update a child row: a foreign key constraint fails (`bpa/#sql-162c_1b`, CONSTRAINT `FK_confinvitems_1` FOREIGN KEY (`InvDetID`) REFERENCES `confinvdet` (`ID`) ON DELETE CASCADE) I have checked, and all the indexes seem to be in place, By that I hope you mean there is an index on confinvitems.InvDetID _and_ confinvdet.ID the data types are exactly the same. There are no duplicate ID's in the ConfInvDet table. Any idea what this error means, and how to fix it? Could be a record in confinvitems that has an InvDetID that doesn't exist in ConfInvDet. Check with something like: SELECT InvDetID FROM confinvitems WHERE InvDetID NOT IN (SELECT ID FROM ConfInvDet); Also if you do a SHOW INNODB STATUS after your failed query you can get more details on the last error. The InnoDB fkey errors reported back tends to be a bit vague, covering all sorts of failures. Looking at the text and sql examples it could be a table name case problem i.e. you refer to `ConfInvDet` in text but `confinvdet` in SQL. Are you on windows (case insensative table names) or a *nix machine (case sensative)? hth, mark -- MARK ADDISON WEB DEVELOPER 200 GRAY'S INN ROAD LONDON WC1X 8XZ UNITED KINGDOM T +44 (0)20 7430 4678 F E [EMAIL PROTECTED] WWW.ITN.CO.UK Please Note: Any views or opinions are solely those of the author and do not necessarily represent those of Independent Television News Limited unless specifically stated. This email and any files attached are confidential and intended solely for the use of the individual or entity to which they are addressed. If you have received this email in error, please notify [EMAIL PROTECTED] Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read messages sent to and from our systems. Thank You. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recommendations on new hardware
On Wed, 2005-09-07 at 08:30 -0600, Cory Robin wrote: Brent Baisley wrote: If you do go the new hardware route, I wouldn't go with SCSI is you only have $2K to spend. S-ATA2 based drives would give you similar performance to SCSI, but at a big cost savings. SCSI's big performance advantage was in command queueing which SATA2 drives now have. snip I agree with the S-ATA2 recommendation. The SCSI choice is not purely about performance. In my experience SCSI drives are much more reliable than IDE or SATA. You'll save money to start with but may spend it later in replacement drives and downtime. Its a tricky choice with only 2k to spend... mark -- This email (and any attachments) is intended solely for the individual(s) to whom addressed. It may contain confidential and/or legally privileged information. Any statement or opinions therein are not necessarily those of ITN unless specifically stated. Any unauthorised use, disclosure or copying is prohibited. If you have received this email in error, please notify the sender and delete it from your system. Security and reliability of the e-mail and attachments are not guaranteed. You must take full responsibility for virus checking. Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read e-mails sent to and from our server(s). Independent Television News Limited, Registered No. 548648 England, VAT Reg. No: GB 756 2995 81, 200 Gray's Inn Road, London WC1X 8XZ, Telephone: 020 7833 3000. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Compare two tables
On Fri, 2005-08-26 at 09:14 -0600, Alfredo Cole wrote: El Viernes, 26 de Agosto de 2005 08:16, Martijn Tonies escribió: You could check the table DDL. Or use a third party tool, like Database Workbench, that can do this for you and even generator a change script. Check www.upscene.com With regards, Martijn Tonies Hmmm. No Linux version. Thank you anyway. lol! You could give SqlFairy a go, it comes with a sqlt-diff command that should do want you want. See:http://sqlfairy.sourceforge.net/ Or, as its perl, you can just do 'install SQL::Translator' in a CPAN shell. mark -- This email (and any attachments) is intended solely for the individual(s) to whom addressed. It may contain confidential and/or legally privileged information. Any statement or opinions therein are not necessarily those of ITN unless specifically stated. Any unauthorised use, disclosure or copying is prohibited. If you have received this email in error, please notify the sender and delete it from your system. Security and reliability of the e-mail and attachments are not guaranteed. You must take full responsibility for virus checking. Please note that to ensure regulatory compliance and for the protection of our clients and business, we may monitor and read e-mails sent to and from our server(s). Independent Television News Limited, Registered No. 548648 England, VAT Reg. No: GB 756 2995 81, 200 Gray's Inn Road, London WC1X 8XZ, Telephone: 020 7833 3000. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
