What MySQL-flavor to choose.
We are about to migrate from MySQL 4.1 to a 5.5 version. We heavily use InnoDB, have an dual quad Nahelem Xeon, 24GB DDR3, 4*SSD in RAID-10 on an Adaptec RAID with 512MB Cache and running under x64 Linux on a modern kernel. We replicate to several other slaves. I only have experience on vanilla MySQL-versions (compile my own). What flavor (MariaDB, MySQL, Percona) should i choose and why? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
On 2011-02-14 15:31, Singer X.J. Wang wrote: What is your load type? Heavy read but enough write not to benefit much from query cache. It is a webshop app (custom). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: How to export data with column names?
-Original Message- From: mos [mailto:mo...@fastmail.fm] Sent: Sunday, February 13, 2011 4:50 PM To: mysql@lists.mysql.com Subject: How to export data with column names? I want to use select * into outfile myfile.txt from table1; and have it export the data as tab delimited but with the column names. I need the column names because the import utility will use that to create the table in another (non-MySQL) database. [JS] mysql -hxxx -uyyy -pzzz -e select * from table1 myfile.txt Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 E-mail: je...@gii.co.jp Web site: www.the-infoshop.com As it stands, I can't get the column names to appear. Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=je...@gii.co.jp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
On 2011-02-14 15:43, Singer X.J. Wang wrote: So I'm assuming OLTP type transaction, then I'm going to recommend MySQL 5.5. Why is that flavor to be chosen over MariaDB with XtraDB or Percona with XtraDB? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
According to the benchmarks on the Personal website, the 5.1 version of Persona has the same about performance as mysql 5.5 (http://www.percona.com/software/benchmarks/) Also the 5.5 version of the Persona(not reported as stable) is even better than mysql 5.5 according the the same site. Also after reading Dimitrik blog (http://dimitrik.free.fr/blog/archives/2010/07/mysql-performance-innodb-io-capacity-flushing.html), its seems to me , than one of the thing that are different between the stock mysql (5.5) and xtradb, is the way that they handle IO capacity and flush. So if you need to control the IO then you should use the stock mysql. My opinion is that both servers(personal 5.1 - mysql 5.5) , seem to be good enough for extreme workloads and they will do your job. Also (if it's an option) you can install both , and test them with a synthetic benchmark like sysbench. akops On 14/2/2011 4:34 μμ, Jay Ess wrote: On 2011-02-14 15:31, Singer X.J. Wang wrote: What is your load type? Heavy read but enough write not to benefit much from query cache. It is a webshop app (custom). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
CR: add support of interactive transactions for webclients
CR: add support of interactive transactions for webclients Hello, I dont know how to place an idea (CR) for mySQL. I try it that way. At the moment I am implementing an easy-to-use multiuser webclient for database usage. (phpMyAdmin in contrast is a very powerful tool for people with technical background knowledge and I like to use it.) In an multiuser environment the usage of transactions to avoid data loss by access conflicts between different users is mandatory. But: Webserver (e.g. apache) doesnt keep open the connection to mySQL after the script (e.g. PHP) has been executed. mySQL thread is terminated and any open transaction is rolled back. Idea (CR): provide new session-variable KEEP_PROCESS and new command CHANGE_PROCESS. --- scenario (simplified): user1 navigates through database SELECT but NOT for update user1 likes to checkout a row to do some changes: ?PHP mySQL authentification START TRANSACTION SELECT FOR UPDATE SET @@KEEP_PROCESS=1 // process shall NOT be terminated after connection has been closed SELECT CONNECTION_ID() data is displayed in webclient FORM for editing ? row (InnoDB) is locked for user1 and cannot be SELECT FOR UPDATE by any other user user1 likes to write the changes back: ?PHP mySQL authentification CHANGE_PROCESS $connectionId // process shall be overtaken if a lot of conditions are satisfied (see bellow) UPDATE . COMMIT SET @@KEEP_PROCESS=NULL // process shall be terminated after connection has been closed ? --- conditions for CHANGE_PROCESS $connectionId : - user (and host) have to match between currently active (new) process and (old, kept) process with ID=$connectionId - currently active (new) process and (old, kept) process have to have successfully finnished user authentification - (original, last) connection of (old, kept) process has to be terminated before active (new) process can overtake if (original, last) connection has not been terminated then CHANGE_PROCESS is queued until (original, last) connection terminates I have been implementing a workaround (around 500 lines of code) that implements the above described behaviour. But this is implemented in C++ as PHP to MySQL bridge acting towards PHP as simulated mySQL server on port 3307 and acting towards mySQL as simulated PHP client on port 3306. This workaround does satisfy my needs but of course it would be much better to have this functionality implemented directly in mySQL if other users need interactive transactions for webclients too. Please give me feedback. Thank you very much! Herbert Don't get soaked. Take a quick peek at the forecast with the Yahoo! Search weather shortcut. http://tools.search.yahoo.com/shortcuts/#loc_weather
Re: What MySQL-flavor to choose.
Hi, Also after reading Dimitrik blog (http://dimitrik.free.fr/blog/archives/2010/07/mysql-performance-innodb-io-capacity-flushing.html), its seems to me , than one of the thing that are different between the stock mysql (5.5) and xtradb, is the way that they handle IO capacity and flush. So if you need to control the IO then you should use the stock mysql. Just to clarify, you can run with the same flushing algorithm under Percona server and the same io capacity options are available. http://www.percona.com/docs/wiki/percona-server:features:innodb_io#innodb_io_capacity The io_capacity feature actually came from the Google and Percona patches http://www.innodb.com/wp/products/innodb_plugin/license/third-party-contributions-in-innodb-plugin-1-0-4/ Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: What MySQL-flavor to choose.
According to http://dimitrik.free.fr/blog/archives/2010/12/mysql-performance-analyzing-perconas-tpcclike-workload-on-mysql-55.html the xtraDB is ignoring the io capacity setting. But this of course it might be just a minor bug in the percona version that dimitriK used in his test..:-) akops On 14/2/2011 7:16 μμ, Ewen Fortune wrote: Hi, Also after reading Dimitrik blog (http://dimitrik.free.fr/blog/archives/2010/07/mysql-performance-innodb-io-capacity-flushing.html), its seems to me , than one of the thing that are different between the stock mysql (5.5) and xtradb, is the way that they handle IO capacity and flush. So if you need to control the IO then you should use the stock mysql. Just to clarify, you can run with the same flushing algorithm under Percona server and the same io capacity options are available. http://www.percona.com/docs/wiki/percona-server:features:innodb_io#innodb_io_capacity The io_capacity feature actually came from the Google and Percona patches http://www.innodb.com/wp/products/innodb_plugin/license/third-party-contributions-in-innodb-plugin-1-0-4/ Cheers, Ewen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Insert only if the entry doesn't exist
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Insert only if the entry doesn't exist
Instead of insert into you can use replace into.
--
João Cândido de Souza Neto
Andre Polykanine an...@oire.org escreveu na mensagem
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Insert only if the entry doesn't exist
Be Careful. REPLACE INTO mechanically does DELETE and INSERT under mysqld's
hood.
If you want to do nothing if row exists already then do:
INSERT IGNORE instead of REPLACE INTO
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Instead of insert into you can use replace into.
--
João Cândido de Souza Neto
Andre Polykanine an...@oire.org escreveu na mensagem
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Insert only if the entry doesn't exist
Hello Rolando,
Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
Original message
From: Rolando Edwards redwa...@logicworks.net
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist
Be Careful. REPLACE INTO mechanically does DELETE and INSERT under
mysqld's hood.
If you want to do nothing if row exists already then do:
INSERT IGNORE instead of REPLACE INTO
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Instead of insert into you can use replace into.
--
João Cândido de Souza Neto
Andre Polykanine an...@oire.org escreveu na mensagem
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Insert only if the entry doesn't exist
If the table has Primary and/or UNIQUE Keys, then you are fine.
You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G
INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: Andre Polykanine [mailto:an...@oire.org]
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: João Cândido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Hello Rolando,
Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
Original message
From: Rolando Edwards redwa...@logicworks.net
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist
Be Careful. REPLACE INTO mechanically does DELETE and INSERT under
mysqld's hood.
If you want to do nothing if row exists already then do:
INSERT IGNORE instead of REPLACE INTO
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Instead of insert into you can use replace into.
--
João Cândido de Souza Neto
Andre Polykanine an...@oire.org escreveu na mensagem
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Foreign Key Error
Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor
Re: Insert only if the entry doesn't exist
Hello Rolando,
So if I do
INSERT IGNORE INTO `Votes` SET `EntryId`='12345', UserId`='789';
it *won't* insert the second row if there's a row with EntryId set to
12345 and UserId set to 789?
Thanks and sorry!)
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
Original message
From: Rolando Edwards redwa...@logicworks.net
To: Andre Polykanine
Date created: , 11:01:40 PM
Subject: Insert only if the entry doesn't exist
If the table has Primary and/or UNIQUE Keys, then you are fine.
You do not need to know what they are. If you want to see them do this:
SHOW CREATE TABLE Votes\G
INSERT IGNORE INTO does not require ON DUPLICATE KEY options.
After all, you said earlier that you want to do nothing if the row exists.
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: Andre Polykanine [mailto:an...@oire.org]
Sent: Monday, February 14, 2011 3:40 PM
To: Rolando Edwards
Cc: João Cândido de Souza Neto; mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Hello Rolando,
Sorry, but if I do INSERT IGNORE INTO, then I must indicate a key
(typically a unique key or a primary key), or is it false? But I don't
know that key and no way to get it without more queries...
--
With best regards from Ukraine,
Andre
Skype: Francophile
My blog: http://oire.org/menelion (mostly in Russian)
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
Original message
From: Rolando Edwards redwa...@logicworks.net
To: João Cândido de Souza Neto
Date created: , 10:33:05 PM
Subject: Insert only if the entry doesn't exist
Be Careful. REPLACE INTO mechanically does DELETE and INSERT under
mysqld's hood.
If you want to do nothing if row exists already then do:
INSERT IGNORE instead of REPLACE INTO
Rolando A. Edwards
MySQL DBA (SCMDBA)
155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards
-Original Message-
From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br]
Sent: Monday, February 14, 2011 1:39 PM
To: mysql@lists.mysql.com
Subject: Re: Insert only if the entry doesn't exist
Instead of insert into you can use replace into.
--
João Cândido de Souza Neto
Andre Polykanine an...@oire.org escreveu na mensagem
news:1621362474.20110214201...@oire.org...
Hi all,
Thanks for your fast answer to my last question!
Here's one more problem I commonly deal with.
There are cases when I need to insert the row only if such a row
doesn't exist, otherwise I need either to update the row or to do
nothing, just skip the query.
The common case is the rating increment/decrement. I have the + and -
links which are AJAX. So I need to prevent fast multiple clicking and
over-increasing or over-decreasing the rating of the blog entry.
Here's what I'm doing, in a simplified form (assume all of the numbers
are PHP variables):
SELECT `Id` FROM `Votes` WHERE `EntryId`='12345' AND `UserId`='789';
then I'm checking if the user has already clicked a link:
if (mysql_num_rows()==0) {
INSERT INTO `Votes` SET `EntryId`='12345', `UserId`='789';
} else {
// do nothing
}
And here is the question (at last!):
How can I MySql'ly check whether a vote does exist and if it does,
then insert the row, else either do nothing or update several columns?
I assume I can't use ON DUPLICATE KEY UPDATE because I have no unique
keys...
Hope you understood my problem).
Thanks!
--
With best regards from Ukraine,
Andre
Skype: Francophile
Twitter: http://twitter.com/m_elensule
Facebook: http://facebook.com/menelion
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=redwa...@logicworks.net
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=an...@oire.org
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: Foreign Key Error
or column types in the table and the referenced table do not match for constraint The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Foreign Key Error
Thank you! V On Mon, Feb 14, 2011 at 9:08 PM, Gavin Towey gto...@ffn.com wrote: or column types in the table and the referenced table do not match for constraint The columns Parent and Child are signed integers and ID is unsigned. Regards, Gavin Towey -Original Message- From: Victor Subervi [mailto:victorsube...@gmail.com] Sent: Monday, February 14, 2011 3:09 PM To: mysql@lists.mysql.com Subject: Foreign Key Error Hi; I have this command: create table if not exists categoriesRelationships (ID integer auto_increment primary key, Store varchar(60), Parent integer not null, foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb; show innodb status prints out this: LATEST FOREIGN KEY ERROR 110214 15:03:43 Error in foreign key constraint of table test/categoriesRelationships: foreign key (Parent) references categories (ID), Child integer not null, foreign key (Child) references categories (ID)) engine=innodb: Cannot find an index in the referenced table where the referenced columns appear as the first columns, or column types in the table and the referenced table do not match for constraint. Note that the internal storage type of ENUM and SET changed in tables created with = InnoDB-4.1.12, and such columns in old tables cannot be referenced by such columns in new tables. See http://dev.mysql.com/doc/refman/5.0/en/innodb-foreign-key-constraints.html for correct foreign key definition. mysql describe categories; +--+-+--+-+-++ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-++ | ID | int(3) unsigned | NO | PRI | NULL| auto_increment | | Store| varchar(60) | YES | | NULL|| | Category | varchar(40) | YES | | NULL|| | Parent | varchar(40) | YES | | NULL|| +--+-+--+-+-++ 4 rows in set (0.00 sec) Please advise. TIA, Victor IMPORTANT: This email message is intended only for the use of the individual to whom, or entity to which, it is addressed and may contain information that is privileged, confidential and exempt from disclosure under applicable law. If you are NOT the intended recipient, you are hereby notified that any use, dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please reply to the sender immediately and permanently delete this email. Thank you.
