Re: cannot alter table - solved
Michael Dykman wrote: > On Thu, May 21, 2009 at 11:06 PM, PJ wrote: > >> I have a seemingly impossible situation. I cannot insert values into the >> tables and I cannot alter or delete the primary key (which should not >> exist) or delete the foreign keys nor remove the constraint. G search >> doesn't help. >> >> CREATE TABLE `book_categories` ( >> `bookID` smallint(6) unsigned NOT NULL, >> `categories_id` int(2) unsigned NOT NULL, >> PRIMARY KEY (`bookID`,`categories_id`), >> KEY `fk_book_categories_books` (`bookID`), >> KEY `fk_book_categories_categories` (`categories_id`), >> CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES >> `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE >> ) ENGINE=InnoDB DEFAULT CHARSET=latin1 >> >> Anybody out there still up? I'm rather desperate to fix this this evening... >> Thanks in advance. >> > > We will need a little more information. The table looks sound but is > clearly designed to link 2 other tables. If you are failing to > insert or update, it seems likely that it is because the data is > absent in the foreign tables. Can you confirm? Because without that > forgeign data, these rows are pretty meaningless. > > What is it you are trying to do? > > I was trying to insert some records to fill up empty id numbers and in the process noticed that there is a primary key in the tables but unnecessary if I am not mistaken. Also the book_categories.categories_id should be referencing categories.id -- I think I had somehow wet up the table erroneously. The problem was that one of the books was not entered as it should have and I was assuming it had been entered (2 others were at the same time - using phpMyAdmin instead of my insert page). It now works with minimal bugs on the back-end, but the panic is over. I'll try to fix the primary key issue next. Thanks for the quick response. -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
>I cannot insert values into the tables What is the error message? Has the smallint key run out of values? >and I cannot alter or delete the primary key (which should not exist) Eh? Without a PK, it ain't a table. >or delete the foreign keys nor remove the constraint. G search doesn't help. If the pk referenced by the fk is full, I think you need to drop the fk, then drop the pk in the table referenced by the fk, then recreate that pk as an int, then recreate the fk. PB - PJ wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.36/2126 - Release Date: 05/21/09 06:22:00
Re: cannot alter table - rather urgent
On Thu, May 21, 2009 at 11:06 PM, PJ wrote: > I have a seemingly impossible situation. I cannot insert values into the > tables and I cannot alter or delete the primary key (which should not > exist) or delete the foreign keys nor remove the constraint. G search > doesn't help. > > CREATE TABLE `book_categories` ( > `bookID` smallint(6) unsigned NOT NULL, > `categories_id` int(2) unsigned NOT NULL, > PRIMARY KEY (`bookID`,`categories_id`), > KEY `fk_book_categories_books` (`bookID`), > KEY `fk_book_categories_categories` (`categories_id`), > CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES > `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE > ) ENGINE=InnoDB DEFAULT CHARSET=latin1 > > Anybody out there still up? I'm rather desperate to fix this this evening... > Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? -- - michael dykman - mdyk...@gmail.com - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
cannot alter table - rather urgent
I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. -- Hervé Kempf: "Pour sauver la planète, sortez du capitalisme." - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Date Time
mysql> select sysdate() from DUAL; +-+ | sysdate() | +-+ | 2009-05-21 17:37:13 | +-+ i would get the proprt format is i could CONVERT_TZ to work can you get CONVERT_TZ to work ? Martin Gainty __ Verzicht und Vertraulichkeitanmerkung/Note de déni et de confidentialité Diese Nachricht ist vertraulich. Sollten Sie nicht der vorgesehene Empfaenger sein, so bitten wir hoeflich um eine Mitteilung. Jede unbefugte Weiterleitung oder Fertigung einer Kopie ist unzulaessig. Diese Nachricht dient lediglich dem Austausch von Informationen und entfaltet keine rechtliche Bindungswirkung. Aufgrund der leichten Manipulierbarkeit von E-Mails koennen wir keine Haftung fuer den Inhalt uebernehmen. Ce message est confidentiel et peut être privilégié. Si vous n'êtes pas le destinataire prévu, nous te demandons avec bonté que pour satisfaire informez l'expéditeur. N'importe quelle diffusion non autorisée ou la copie de ceci est interdite. Ce message sert à l'information seulement et n'aura pas n'importe quel effet légalement obligatoire. Étant donné que les email peuvent facilement être sujets à la manipulation, nous ne pouvons accepter aucune responsabilité pour le contenu fourni. > Date: Thu, 21 May 2009 15:19:16 -0600 > From: john.l.me...@gmail.com > To: mysql@lists.mysql.com > Subject: Date Time > > Is "Thu May 21 03:15:28 + 2009" a valid date/time string? > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=mgai...@hotmail.com > _ Hotmail® has ever-growing storage! Don’t worry about storage limits. http://windowslive.com/Tutorial/Hotmail/Storage?ocid=TXT_TAGLM_WL_HM_Tutorial_Storage1_052009
Date Time
Is "Thu May 21 03:15:28 + 2009" a valid date/time string? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Upgrade Mysql
Thank You for all the help. I was upgrading from 4.0.x to 5.0.x The new mysql recognized the old databases without problems. Bye :)
General Question..
Hello all, Hope you are doing well, I have a general question creating foreign keys , I'm creating FK from catalog tables to relation tables with them, when I create a FK in innodb engine on MYSQL a new field with the same name of the FK is created, in this case if I create for example 3 FK the table will have 3 fields more, my Question is, is it right? How should be filled these fields? Are they autofilled when I start to fill the table? Should I ignore them? Best Regards. Saludos Arturo Hinojo P NO imprimas este correo a menos que sea necesario. Please consider the environment before printing this page.
RE: mySQL slave IO Running and SQL Running
I'm not running with replication, but I AM often running the CLI against the production and test environments at the same time. I found it invaluable to add the line prompt=\h > to my.ini (I'm running on Windows). That adds the host name to the prompt, and has saved my butt more than once. >-Original Message- >From: Daevid Vincent [mailto:dae...@daevid.com] >Sent: Wednesday, May 20, 2009 4:49 PM >To: mysql@lists.mysql.com >Cc: 'Gavin Towey'; 'Claudio Nanni' >Subject: RE: mySQL slave IO Running and SQL Running > >Well, in 90% of our cases it is. Most often caused by some dumb-ass >(usually >me) doing an INSERT or UPDATE on the slave on accident since I'm often >logged into it doing SELECTs but I sometimes need to 'debug' or 'test' >something and forget which box I'm on. So I happily do my altering of >the >slave's data and check my pages (which now are reading from slave) and >all >looks great, only to realize that saving via the web page isn't working. >I >then spend some time pulling my hair out and debugging the page only to >realize that the page is writing to master (as it should) but >replication >has shit the bed from my aforementioned dumb-assed-ness and then I have >to >run said incantation below to get the binlog to skip and sync up again. > >But I understand what you're trying to say and concur. Blindly skipping >binlog SQL commands is not any way to solve a problem. Eyeballs have to >view >the Last_Error and act appropriately. > >The 'read-only' seems to be a great preventative step that we're going >to >take and hopefully that will stave off a good portion of my >stupid-user-mistakes. > >> -Original Message- >> From: Gavin Towey [mailto:gto...@ffn.com] >> Sent: Wednesday, May 20, 2009 1:20 PM >> To: Claudio Nanni; Daevid Vincent >> Cc: mysql@lists.mysql.com >> Subject: RE: mySQL slave IO Running and SQL Running >> >> Please note that this is *NOT* a way to "get them synched again" >> >> In fact if you have to skip a replication statement on the >> slave then it is usually a sign your slave has different data >> than you master already. Skipping statements/errors may keep >> replication running, but you're just masking problems. >> >> >> >> -Original Message- >> From: Claudio Nanni [mailto:claudio.na...@gmail.com] >> Sent: Wednesday, May 20, 2009 12:49 PM >> To: Daevid Vincent >> Cc: mysql@lists.mysql.com >> Subject: Re: mySQL slave IO Running and SQL Running >> >> Yeah Daevid! I know very well the issue! >> >> first set the slave to READ ONLY >> >> [mysqld] >> read-only >> >> then there is a configuration option to tell the server to >> skip some type of >> errors automatically >> >> slave-skip-errors= >> >> http://dev.mysql.com/doc/refman/5.1/en/replication-options-sla >> ve.html#option_mysqld_slave-skip-errors >> >> >> But, But, BUT! >> >> What I did is to remove the constraint on the table of the >> slave so that you >> can control better the thing. >> Because if you systematically skip the 'foreign key forcing' >> error, you will >> skip them with any table, >> if you remove just that constraint on that table you have the >> situation more >> under control. >> >> I think one of these two are enough, the cron is very not recomended! >> >> Ciao >> >> Claudio >> >> >> >> 2009/5/20 Daevid Vincent >> >> > We have a master / slave setup and as you know, one bad >> query can ruin your >> > whole day. Or if you accidentally write to the slave when >> you meant to >> > write >> > to the master, or any number of other things that break the >> fragility of a >> > replication setup. >> > >> > The magic incantation to get them synched again seems to be >> to login to the >> > slave and do this (over and over again until the >> Slave_IO_Running and >> > Slave_SQL_Running both say "Yes"): >> > >> > mysql> stop slave; SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; >> start slave; show >> > slave status\G >> > >> > Is there a way to automate this a little bit. Maybe some >> bash script that >> > uses "mysql -e" and parses for those two strings? >> > Is this dangerous to do? >> > Is there a setting to have the slave do this already? >> > >> > In every case I've ever seen, it's always some SQL that got >> out of whack >> > like this: >> > >> > Last_Error: Error 'Duplicate key name 'id_operator'' on >> query. Default >> > database: 'core'. Query: 'ALTER TABLE >> `user_has_notification` ADD INDEX >> > `id_operator` (`id_operator`)' >> > >> >> The information contained in this transmission may contain >> privileged and confidential information. It is intended only >> for the use of the person(s) named above. If you are not the >> intended recipient, you are hereby notified that any review, >> dissemination, distribution or duplication of this >> communication is strictly prohibited. If you are not the >> intended recipient, please contact the sender by reply email >> and destroy all copies of the original message. >> > > >-- >MySQL General Mailing List >For list archives: http://lists.mysql
6.0.11 source
Anyone knows where I can download the 6.0.11 gziped source from?
Re: MySQL: large_page_size
I tried it on win32, it worked but the server failed under heavy load. I'd suggest running mysql on a 64 bit Linux machine instead. Jw On Thursday, May 21, 2009, Krishna Chandra Prajapati wrote: > Hi, > > How much performance improvement we can get using large_page_size in mysql > server. Is there anybody using on productions. > > Thanks, > Prajapati > > Krishna Chandra Prajapati > Email-id: prajapat...@gmail.com > -- - Johnny Withers 601.209.4985 joh...@pixelated.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
MySQL: large_page_size
Hi, How much performance improvement we can get using large_page_size in mysql server. Is there anybody using on productions. Thanks, Prajapati Krishna Chandra Prajapati Email-id: prajapat...@gmail.com
