REPLACE query
I have a table with four columns, the first three of which are combined
into a unique key:
create table Test {
cid int(9) NOT NULL default '0',
sid int(9) NOT NULL default '0',
uid int(9) NOT NULL default '0',
rating tinyint(1) NOT NULL default '0',
UNIQUE KEY csu1 (cid,sid,uid),
KEY cid1 (sid),
KEY sid1 (sid),
KEY uid1 (sid),
} TYPE=InnoDB;
I am using a REPLACE query to insert a row if it doesn't exist and
replace an existing row if one does exist:
REPLACE into TEST (cid, sid, uid, rating) values (580, 0, 205, 1)
In the case of this particular row, a row already exists with the
concatenated key of 580-0-205 and I am getting a duplicate key error. I
thought REPLACE was supposed to actually replace the contents of the row
if one exists. Does anyone have any ideas as to why this would be
causing a duplicate key error?
Thanks,
Kevin
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: First letter only of a column
try this: select LEFT(names, 1) from table Tim Johnson wrote: Hello: Is it possible to use mysql to select only the first letter of a string in a column? IOWS select names from table - select first letter of names from table another way of asking my questions would be, Is it possible to truncate columns in selection set to a specific length (in the case: 1) Pointers to relevant documents are welcome. Thanks tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I determine the row number or key when table has no key fields
Andy Ford wrote: I thought LIMIT limited you to N number of CONCURRENT record. ie. limit 10 or limit 20 I believe Ross would like to select select 1000 records and then do a sub select of records 1-20 and then 21-40 on this record set LIMIT also allows you to specify a starting record, i.e. LIMIT 50, 100 so Ross could change the first parameter to accomplish this. Example: First query: LIMIT 0, 20 Second: LIMIT 20, 20 Third: LIMIT 40, 20 etc Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Load Data
Try this (untested) : update table set column=LEFT(column, LENGTH(column)-1) Andrea Broerman wrote: I have successfully loaded data from a comma separated file (CSV) into a table, but the last field in each record appears to have a little square symbol at the end of the text which I assume is either an end of line or new line symbol. Is there a way to get rid of that? How can I run the load data command and not get that symbol stored at the end of the text? Thanks, Andrea __ Do you Yahoo!? Yahoo! Finance Tax Center - File online. File on time. http://taxes.yahoo.com/filing.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: COMPOSITE PRIMARY KEY?
Seena Blace wrote: Hi, I want to create a table with composite Primary key.How to do that? create table ipdet (IPaddress varchar (14) not null , hostid varchar (20) not null primary key (ipaddress,hostid), IP_DESC text , MAC text, interface text); Try this: create table ipdet (IPaddress varchar (14) not null , hostid varchar (20) not null, IP_DESC text, MAC text, interface text, primary key (ipaddress,hostid) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: use mysqldump and mysqlhotcopy together?
Bing Du wrote: Greetings, I've been looking at the backup/recover related discussions in the list archives for a while. Seems to me most people use either mysqldump or mysqlhotcopy. We don't have a good MySQL backup scheme in place yet. Now I have some questions based on my reading. We use mysqldump and then a standard backup tool triggered by cron to backup the file it creates. If you use mysqldump without the -d or -t flags, you will get the sql necessary to recreate the tables as well as the insert statements necessary to populate the tables. For our particular application (which generates large amounts of DB reads and very few inserts/updates/deletes) we found that using mysqldump gave us an accurate snapshot of the data and was less to back up since the indexes in our case can get quite large. We've been using that method for quite some time with good results. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment pseudo sequence?
alter table AUTO_INCREMENT=x Scott Purcell wrote: Hello, I have an application in which I am using auto_increment as a kind of sequence replacement. The only problem I have is trying to get the auto_increment to start at a larger number than 0. Is auto_increment the replacement for sequences? and if so, is there a way to set the digit to a larger number when creating? Thanks, Scott -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JOIN types
Keith Bussey wrote: ... Also, I'm running MySQL 4.0.13-standard, STRAIGHT JOIN doesnt seem to exist for me ;p I think it is actually STRAIGHT_JOIN... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Formatted index
Does MySQL support formatted indexes such as in the statement below? alter table DateInfo add index monthYear (DATE_FORMAT(updateDate, '%m/%Y')) If not, does anyone know of any plans to support this in the future? Thanks, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatted index
Keith C. Ivey wrote: On 1 Dec 2003 at 13:41, Kevin Carlson wrote: Does MySQL support formatted indexes such as in the statement below? alter table DateInfo add index monthYear (DATE_FORMAT(updateDate, '%m/%Y')) What sort of queries would that help you with?... This helps with queries where you want to get all of the records where a date column value falls within a specific month and year. For example: select * from DateInfo where DATE_FORMAT(updateDate, '%m/%Y') = '03/2003' I have an index on the updateDate column but it does not seem to be used since I am altering the value with the DATE_FORMAT function in the where clause. Using 'explain' it becomes obvious that no index is being used to build the result set. I hope that makes things more clear. Any ideas? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Formatted index
Keith C. Ivey wrote: On 1 Dec 2003 at 16:32, Kevin Carlson wrote: select * from DateInfo where DATE_FORMAT(updateDate, '%m/%Y') = '03/2003' I have an index on the updateDate column but it does not seem to be used since I am altering the value with the DATE_FORMAT function in the where clause. How about something like this? I appreciate your help, but my problem is not this exact query as it was used for an example only. My question was whether or not it was possible to use functions in the definition of an index. Other functions would be of interest, too, such as UPPER(). Other commercial databases such as Oracle support this type of thing. All I'm really looking for is an answer to that question. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Security Question
Curley, Thomas wrote: I am trying to find a solution to the following security issue with MySql DB on linux - Someone copies the DB files to another box, starts a mysql instance, loads the DB and presto - views the 'private' data !!! As all the other posters have mentioned, you should have tight file level security set up. However, if you use basic mysql user authentication, even copying the files over shouldn't allow them to view the information in a database since they would need the mysql user/passwd to do anything. Which got me to thinkingis this the case? If I am using MyISAM tables and just port them over to a different box with a different security scheme, would I be allowed to view those MyISAM tables? Also, is this the case for InnoDB as well? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help recovering db
Using a backup I recreated the /var/lib/mysql directory and when I restart mysqld I am getting the following error: InnoDB: Error: log file /var/lib/mysql/ib_logfile0 is of a different size 0 5238827 byes InnoDB: than specified in the .cnf file 0 5242880 Of course, the database never starts because of this. Oddly, if I move the backup to another machine that is running the same version of MySQL (same OS and version), the database starts correctly. I have tried running mysqld with and without a my.cnf file present and have changed some of the values regarding the logfile size but have not been able to successfully start the database. Is anyone aware of any workaround for this? Thanks, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Having MySQL listen on multiple(2) ports at the same time
You can emulate this using IPTables and a FORWARD rule. If you are unfamiliar with IPTables see http://www.iptables.com/ for documentation. Misaochankun wrote: Can this be done? No, I do not mean running multiple MySQL servers. I need to have MySQL listen on two separate ports at the same time. Reason being, the new port is needed for a routing issue, and the default port 3306 needs to stay up to respond to normal traffic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
After successful INSERT, no record found
I am using MySQL version 4.0.11 gamma on Linux 7.1 and have just experienced the following problem: INSERTed a record into a InnoDB table, then used SELECT to retrieve the recordID (auto numbered field) from the same table using a key. The SELECT returned the proper ID. However, when browsing the original table no record with this ID exists. Is it possible that due to some internal error that a successfully INSERTED and subsequently SELECTED error would be deleted from the database? Has anyone seen this type of behavior before? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: After successful INSERT, no record found
gerald_clark wrote: Did you commit the transaction? Yes, the transaction was committed. I was using MyCC at the same time the anomaly occurred, also. Could this have had anything to do with it? Kevin Carlson wrote: I am using MySQL version 4.0.11 gamma on Linux 7.1 and have just experienced the following problem: INSERTed a record into a InnoDB table, then used SELECT to retrieve the recordID (auto numbered field) from the same table using a key. The SELECT returned the proper ID. However, when browsing the original table no record with this ID exists. Is it possible that due to some internal error that a successfully INSERTED and subsequently SELECTED error would be deleted from the database? Has anyone seen this type of behavior before? Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: database restore - please help
You should be able to do this: mysql backup.txt -Original Message- From: Hathaway, Scott L [mailto:[EMAIL PROTECTED]] Sent: Wednesday, May 29, 2002 8:49 AM To: Mysql (E-mail) Subject: database restore - please help I deleted my database today by mistake and I have a backup from yesterday made as follows: mysqldump -A backup.txt How can I restore this database? Thanks for your help in advance. Scott - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Interesting datetime problem
Hi, I have an interesting problem when updating columns of type DATETIME. It seems that exactly one day is subtracted from the DATETIME value that I submit in an update query. Has anyone encountered this? Any ideas? Kevin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Interesting datetime problem
You could be correct about the time zone problem in this sql. I'm somewhat of a MySQL newbie -- do you mean the system environment variable or is there a MySQL environment variable for TZ? Thanks, Kevin -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin Pflugmann Sent: Tuesday, May 28, 2002 6:19 PM To: Kevin Carlson Cc: Mysql Subject: Re: Interesting datetime problem Hi. Maybe your TZ (timezone) environment variable is set to a strange value? If not, could you provide a full example, so that we can try to reproduce it and see whether this is a local behaviour of your machine or a common MySQL behaviour. Bye, Benjamin. On Tue, May 28, 2002 at 01:27:18PM -0400, [EMAIL PROTECTED] wrote: Hi, I have an interesting problem when updating columns of type DATETIME. It seems that exactly one day is subtracted from the DATETIME value that I submit in an update query. Has anyone encountered this? Any ideas? Kevin -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: first mysql table; syntax qustion
How do you reference a column name in another table to avoid repeating the values? -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On Behalf Of Benjamin Pflugmann Sent: Tuesday, May 28, 2002 6:24 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: first mysql table; syntax qustion Hi. Looks okay, except that you probably want to reference via pro_id and d_id in table available_properties (given that I understood your table layout correctly). It is usally considered bad design to repeat the values itself (the varchars) in several tables. Bye, Benjamin. On Tue, May 28, 2002 at 12:17:55PM -0700, [EMAIL PROTECTED] wrote: Hello list, this is my first table for a simple php page and I'm hoping someone could briefly review its structure. The only fields I'll be sharing are property_name and type. Are they set up properly for select join queries? thank you, justin Database=properties Tables are available_properties, property_names, and details create table available_properties ( av_pro_id int not null auto_increment property_name varchar (50) not null type varchar (25) city varchar (25) description varchar (125) number_of_units varchar (25) primary key (av_pro_id) ); create table property_names ( pro_id int not null auto_increment property_name varchar (50) not null address varchar (100) cross_street varchar (50) primary key (pro_id) ); create table details ( d_id int not null auto_increment type varchar (25) price smallint floor varchar (15) sq_ft smallint suite smallint description text available varchar (15) primary key (d_id) ); -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
