Fields seem to be mis-wired (for lack of a better term)
Hello, I'm having a problem when trying to update a record. I'm trying to set a field called 'balance' to 0 but instead of 'balance' becoming 0 the field 'name' becomes 0. None of the other columns are updated incorrectly. Here's the SQL statement I'm using: UPDATE `users` SET `name` = 'First Last' AND `email` = '[EMAIL PROTECTED]' AND `balance` = 0 AND `accrual` = 14400 AND `is_manager` = 1 AND `is_superadmin` = 1 AND `type` = 0 AND `manager_id` = 0 AND `modified` = NOW() WHERE `id` = 5 Here's the table definition: CREATE TABLE `users` ( `id` int(10) unsigned NOT NULL auto_increment, `name` varchar(255) NOT NULL default '', `email` varchar(255) NOT NULL default '', `password` varchar(32) NOT NULL default '', `type` tinyint(3) unsigned NOT NULL default '0', `manager_id` int(10) unsigned NOT NULL default '0', `is_manager` tinyint(1) NOT NULL default '0', `is_superadmin` tinyint(1) NOT NULL default '0', `fulltime_start` date NOT NULL default '-00-00', `accrual` smallint(6) NOT NULL default '0', `balance` mediumint(9) NOT NULL default '0', `created` datetime NOT NULL default '-00-00 00:00:00', `modified` datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (`id`) ) ENGINE=MyISAM DEFAULT CHARSET=latin1 ; I did an experiment just now through phpMyAdmin with the following query and it worked as expected: UPDATE `users` SET `balance` = 0 WHERE `id` = 5 Only thing I can guess is that there's an obvious error that is not obvious to me. :) Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fields seem to be mis-wired (for lack of a better term)
On Tuesday, April 24, 2007 10:12 AM Jim Winstead mailto:[EMAIL PROTECTED] said: AND `type` = 0 AND `manager_id` = 0 AND `modified` = NOW() WHERE `id` = 5 you can't use 'AND' to connect your updates, you need to use commas: UPDATE users SET name = 'First Last', email = '[EMAIL PROTECTED]', ... what you're doing now is setting your name column to a boolean expression. Oops. :) Thanks a lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Workbench
Anyone have any info on MySQL Workbench? In it's latest state it isn't usable at all. It looks like it'll be really great. Thanks, Chris ParkerAardvark Tactical, Inc.IT Manager1002 W Tenth St. Azusa, CA 91702phone: 800.997.3773 x130 fax: 626.334.6860[EMAIL PROTECTED]
Performance of different length/size datatypes
Hello, Originally I had this long explanation of what I'm doing and why I'm asking this question but I thought I'd just cut to the chase and ask... For a db that doesn't get a lot queries is there much of a performance difference between BLOB and VARCHAR(255)? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Delete one record, automatically delete other records...
Hello, I'm not sure if this is possible (or what it's called, and how to search for it) at the db layer or if this has to be done at the application layer... I would like to be able to delete one record in one table and then automatically (without making an extra call to the db) delete other records. If I'm using this term correctly, I think I'm trying to avoid having orphan records. In my specific case I want to delete a product from my database and then automatically delete all associated records like the category and manufacturer relationships. BTW, I'm using MySQL 4.1.20. I'm not really looking for code snippets but rather the name for this idea and/or links so I can rtfm. :) Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Delete one record, automatically delete other records...
Chris mailto:[EMAIL PROTECTED] on Monday, August 07, 2006 6:19 PM said: Foreign keys with an on delete cascade should do it. http://dev.mysql.com/doc/refman/5.1/en/example-foreign-keys.html Thanks everyone! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Transactions and testing an Insert statement
Hello,
Me again. Excuse for sending two questions so closely together.
I'm looking through the MySQL manual (as well as searching Google and
the PHP site's MySQL functions) trying to find out how to test an Insert
statement (or any other statement for that matter).
Although I haven't found a direct answer, my searching usually points me
to transactions in InnoDB. Is this what I will need to use to do what I
want?
I'm preparing to import a bunch of data that is coming from an Excel
file from one the vendors we deal with and I want to find out what
manual data preparation I need to do. I'm using PHP's
mysql_real_escape_string as well as some other custom functions but I
need to find out if this is enough.
As I imagine it in my head:
TEST INSERT INTO `table` VALUES ('value', 'value');
And then get back a success or fail error code.
Using MySQL 4.1.
Thank you for your time,
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How does a multi-row INSERT work?
Hello, I searched the archives, looked through the manual, and searched google for info on how to actually perform a multi-row INSERT but didn't find an answer. Would someone please show me the syntax for this please? I could just do a loop and INSERT the data that way but according to the manual, a multi-row INSERT is faster. Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How does a multi-row INSERT work?
John McCaskey mailto:[EMAIL PROTECTED]
on Thursday, March 31, 2005 12:04 PM said:
This is documented on the INSERT Syntax page of the manual, but it may
be kind of hard to read for a beginner as it just says VALUES({expr |
DEFAULT},...),(...),...
Oooh... In fact I did look through those syntax expressions and din't
notice it. Of course I was looking for a keyword like MULTI or
something.. :\
Thanks everyone!
Chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Optimizing a big query...
Homam S.A. mailto:[EMAIL PROTECTED] on Wednesday, March 09, 2005 5:09 PM said: If your tables are mostly read-only, you could pre-generate page numbers on a periodic basis and select only specific ranges WHERE row_number BETWEEN page_start AND page_finish. How about just getting the entire list on the first page and then on all subsequent pages using LIMIT()? Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple Small Database
John mailto:[EMAIL PROTECTED] on Wednesday, November 10, 2004 10:04 PM said: I want to make a small simple database that searches by state or zip code for jobs. I would enter just a job description, job position and job id #. so 3 fields display. I want to enter in the information by an admin area but not a big deal. How hard would this be to create. for me, that would be really easy (albeit time consuming). for you, it sounds like it would be really hard (and even more time consuming). but seriously, all those parts are individually very easy for many people on this list. but for someone that seemingly doesn't know anything about creating a database or writing the scripts to manage it, it could be quite a daunting task. there are two completely different subjects in your question (well actually i guess it would be a statement since you never used any question marks...). one is database related, the other is script related (script = PHP). i suggest you ask specific questions regarding MySQL databases to this list and you ask specific questions to the PHP-General list (you can sign up by going to www.php.net). hth, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OT: table size WAS RE: optimizing database
Razor Fish mailto:[EMAIL PROTECTED] on Thursday, October 21, 2004 3:19 PM said: i need consulting help for optimizing a database with 1.2 million records to handle 3-4 million hits a day. this is going to be a 'duh' question on my part but i just had to ask anyway. my largest table probably has 700 records in it. what the heck kind of data is being stored where it reaches the millions (or more)? chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: select for text within a field.
Shanta McBain mailto:[EMAIL PROTECTED] on Wednesday, October 20, 2004 9:29 AM said: [% FOREACH link = DBI.query(SELECT * FROM url_tb WHERE products LIKE 'honey' AND (site_name = 'Apis' OR site_name = 'All') ORDER BY link_order )%] are you saying the products field has data like this in it? tacosburritoshoneycrackerscake ... and you want to search for 'honey'? do LIKE '%honey%'. but of course this should really be normalized and split into another table. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Command that I believe should work...
Robert Adkins mailto:[EMAIL PROTECTED] on Friday, October 15, 2004 12:23 PM said: INV_DATE DATETIME DEFAULT NOW() NOT NULL, ); I receive an error message stating that there is an error with 'NOW()' [snip] Is there a very different method of doing this under MySQL 4.0.21? I think MySQL does not support a default value of NOW() like you'd expect it to. Yeah I know, it sucks. I don't know at what point this was added, if it's been added at all. (My MySQL version is a bit old also.) Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Fulltext Search help
leegold mailto:[EMAIL PROTECTED] on Friday, October 15, 2004 2:32 PM said: I do fulltext search on work. And AFAIK the search will not find work. For that matter the seach will not find ingm. How do I implement in MYSQL/PHP a search that will have this action? please share the current query you are trying to use and we can go from there. however, something simple is the following: SELECT * FROM table WHERE field LIKE '%string%'; hth, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: LATEST_DATE
John Mistler mailto:[EMAIL PROTECTED] on Friday, October 15, 2004 3:17 PM said: Is there a function that will return the latest date from a datetime column? SELECT theColumn FROM theTable ORDER BY theColumn ASC (or is it DESC?) LIMIT 1; something like this? (or maybe this is too simple for your needs?) hth, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: help with table structure
Justin Smith mailto:[EMAIL PROTECTED] on Tuesday, October 12, 2004 8:48 AM said: What you have so far looks good, but what I learned from doing my ecomm project was that it is beneficial to make a separate table for anything and everything that you might have more than one of... Addresses, phone numbers, and email addresses are all great candidates for breaking out into another table. interesting you say that because i was going to do this same thing except not as completely as i probably should (which i think is what you are suggesting). what i mean is, my extra table of addresses was going to be merely shipping addresses for the customer and nothing else. but i guess i should change it from being just shipping addresses to include any kind of address relating to the customer? but what about phone numbers? i'm a bit unsure on that one. in my table as it is now, i have three: business, home, and fax. in what case would having a phone (number) table be beneficial? I hope this helps. yes thank you. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
keep field names unique across database?
hello, continuing my quest to build a better database, i'd like to ask a question that i haven't been able to find an answer to. here is an excerpt from an article on evolt (http://www.evolt.org/article/Beginning_Database_Design_Part_I/18/27137/ ): (and where i got the idea as well) You'll probably see some duplicate field names, such as 'Name' in both the 'Companies' and 'People' tables. Let's make them unique across the database. You might choose 'ContactName' and 'CompanyName' but whatever you use, stick to the guidelines above, and be consistent. is this person suggesting that *all* fields within the 'Companies' table be prepended with Company (i.e. CompanyName, CompanyAddress1, CompanyZip, etc.) or is he suggesting that only fields which have the same name in multiple tables have Company added? THIS: +-+ | COMPANIES | +-+ | CompanyName | | CompanyDate | | CompanySize | +-+ +-+ | CONTACTS| +-+ | ContactName | | ContactHeight | | ContactWeight | +-+ OR THIS: +-+ | COMPANIES | +-+ | CompanyName | | Date| | Size| +-+ +-+ | CONTACTS| +-+ | ContactName | | Height | | Weight | +-+ Thank you for your time. Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
help with table structure
hello, i'm just looking for some examples of a customer table that some of you are using for your ecomm sites (or any site that would need a customer table). here is mine so far: (horrible wrapping to follow...) mysql describe customers; ++-+--+-+--- --++ | Field | Type| Null | Key | Default | Extra | ++-+--+-+--- --++ | id | int(10) unsigned| | PRI | NULL | auto_increment | | fname | varchar(20) | | PRI | || | lname | varchar(20) | | PRI | || | address1 | varchar(40) | | | || | address2 | varchar(40) | YES | | || | city | varchar(20) | | | || | state | char(2) | | | || | zip| varchar(10) | | | || | phone | varchar(20) | YES | | || | fax| varchar(20) | YES | | || | email | varchar(64) | | PRI | || | newsletter | tinyint(1) | | | 0 || | password | varchar(32) | | | || | signupdate | datetime| | | -00-00 00:00:00 || | lastvisit | datetime| | | -00-00 00:00:00 || | type | tinyint(3) unsigned | | | 0 || | company| varchar(64) | YES | | || | is_active | tinyint(4) | | | 0 || | activationdate | datetime| | | -00-00 00:00:00 || | activationtype | tinyint(3) unsigned | | | 0 || ++-+--+-+--- --++ i would appreciate not only table descriptions (like mine above)(if you're willing) but comments on what i have so far as well. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How would you make a smarter Search?
Dan Venturini mailto:[EMAIL PROTECTED]
on Friday, October 08, 2004 11:51 AM said:
Now If I do a search for cleaning mouse I get 0 results. If I do
cleaning computer' I get 0 results. But If I do mouse cleaning,
mouse, cleaning your,I get the articles.
[snip]
My question is am I doing something wrong here? Do you have anytips on
making a smart search work? This is the only way I was taught where
you match the user input to something in the database.
well i think the principal is that you need to search for each word
individually, grouping them with AND. i had the same question but never
got around to working on it so i did a little investigation but came up
dry (so far).
i thought an easy way to do it would be to use the IN() function:
SELECT name
FROM products
WHERE name IN ('cleaning', 'computer')
but this doesn't work as it's looking for a name with exactly 'cleaning'
or exactly 'computer'. so i tried adding LIKE before the IN, but that's
just plain invalid. then i tried wrapping each item with % but although
it doesn't throw an error, that doesn't work either.
the only other thing i can think of (not that a better answer is not out
there of course) is to create a statement like the following:
SELECT name
FROM products
WHERE name LIKE '%cleaning%'
AND name LIKE '%computer%'
report back to the list if you find out anything else, or if anyone
would like to chime in and answer this.
chris.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help for a query with MYSQL 3.23.58
Michele mailto:[EMAIL PROTECTED] on Friday, October 08, 2004 3:23 PM said: 2) I'm looking for id_product that can contemporaneously satisfy more than an id_value (NOT ONLY ONE!!) contemporaneously? that's got be one of the best made up wor... wait what? you mean it's a real word? http://dictionary.reference.com/search?q=contemporaneously :\ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help for a query with MYSQL 3.23.58
Michele mailto:[EMAIL PROTECTED] on Friday, October 08, 2004 4:57 PM said: Have I well understood the meaning of your answer ? It was meant as a joke and not a serious response to your question. I apologize for the confusion. Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how can this query be optimized?
hello, i was wondering if anyone could help me to optimize a query i use when gathering search results? this is easily the most complicated query i've written (and likely a walk in the park for most of you) and because of this, i'm afraid it's a bit slow. SELECT COUNT(p.id) FROM products AS p LEFT JOIN products_categories AS pc ON pc.prodid = p.id OR pc.prod_sequential_id = p.sequential_id LEFT JOIN products_masids AS pmas ON pmas.prodid = p.id OR pmas.prod_sequential_id = p.sequential_id LEFT JOIN products_media AS pmed ON (pmed.prodid = p.id OR pmed.prod_sequential_id = p.sequential_id) AND pmed.type = 0 WHERE pc.plft = 17 AND pc.prgt = 174 AND p.is_active = 1 GROUP BY p.id the above query is the first query i initially run to get a count of 'total products found'. the purpose being so that i can determine the number of pages to display. i am using 'modified preorder tree traversal' (http://www.sitepoint.com/article/hierarchical-data-database/2) for classifying my products into categories. also, my mysql version is 3.23.54. i've pretty much learned all i know about databases by messing around with them and using the bits and pieces i read on the internet. in other words, i don't know what important information i may or may not be leaving out of this post. please let me know what else is required for an evaluation of my query to be made, if indeed something is needed. oh... what i *could* say is that the hardware i'm using for this box is not too shabby (not awesome either) and for this exact query it returns 586 records in 2.03 seconds. i have 733 products total in my database. thank you, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how can this query be optimized?
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Thursday, October 07, 2004 12:30 PM said: You set up your situation very well but for one small item. Please allow me to kindly introduce you to the EXPLAIN command http://dev.mysql.com/doc/mysql/en/EXPLAIN.html hey! that looks like it might come in useful. :) i didn't know about this. Post the results from using that on your query and we will be good to go :-) here you are: +---++---+--+-+--+--+--- --+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---++---+--+-+--+--+--- --+ | pmed | system | NULL | NULL |NULL | NULL |0 | const row not found | | p | ALL| NULL | NULL |NULL | NULL | 733 | where used | | pc| ALL| NULL | NULL |NULL | NULL | 753 | where used | | pmas | ALL| NULL | NULL |NULL | NULL | 1410 | | +---++---+--+-+--+--+--- --+ 4 rows in set (0.00 sec) thanks, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how can this query be optimized?
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Thursday, October 07, 2004 1:34 PM said: Can you see where the column possible_keys is NULL for every table? That means that there are NO (none, zilch, nada, zero) indexes that can be used to save your database engine from the trouble of doing a full table scan for EACH AND EVERY MATCH in your query. I don't even see where you defined any PRIMARY KEYS on any of your tables. that's weird because i've definitely defined PRIMARY KEYS. like i know i had a PK on products.id... but admittedly i don't really know too much about indexes or keys (primary, or foreign). (May I suggest you hit web and do a little homework on indexes and primary keys?) you certainly may. :) might you have any specific links for me to look at? Try adding these indexes then let me know how your query performs: i performed your suggested operations and the query runs at the same speed. it nows reports at 2.22. i did read in the link you gave me in the your first post about the ANALYZE table; query so i'll try doing that right now and then see how if it changes. after doing the ANALYZE TABLE table; query on a few tables the time is now down to 2.07. (i think i made a mistake in my original time of 2.03... maybe it was 2.23. i know for sure it was 2 seconds and *something*.) thanks, chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql Newbie: Help requested...password hash should be a 16 digithexadecimal number
Sanjay Arora mailto:[EMAIL PROTECTED] on Wednesday, July 07, 2004 10:04 AM said: I am using Mysql on RH Linux 9. I am getting the following error. [snip] Database changed mysql GRANT ALL PRIVILEGES ON dns.* TO [EMAIL PROTECTED] IDENTIFIED BY PASSWORD dns; ERROR 1133: Password hash should be a 16-digit hexadecimal number copy and paste the error into google and try following the first link or click i'm feeling lucky. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: update query question
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Wednesday, July 07, 2004 11:08 AM said: Have you tried this other way of making an inner join? no i did not because i did know you could do a JOIN on an UPDATE. thanks for your suggestions i will try them out. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update query question
hello, i've had to change some of the tables in my db to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- 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
Kevin Carlson mailto:[EMAIL PROTECTED] on Thursday, October 30, 2003 2:38 PM said: 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? What was the star date of said anomaly? TEEHEE! Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
Matt W mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 5:29 PM said: As to why mysqldump would create a dump file with a syntax error in it, that's because *you* (or the application creator) used a reserved word for a column/index name (bad idea) and mysqldump I always try to be careful about this and I even compared all the column names in my db to the reserved word list on the MySQL site and didn't find any matches. Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
Andy Bakun mailto:[EMAIL PROTECTED] on Wednesday, October 29, 2003 12:27 PM said: It may be best to always use the --quote-names option to mysqldump, which would avoid any problems you might encounter with reserved words being used in column and table names. Already done. :) Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DB not restoring from dump file
Hey everyone, First post to the MySQL list so please be gentle. I recently emptied some tables I shouldn't have in a db of mine and I want to restore the data from a dump file made a few days ago. MySQL version is 3.23. This is the command I used to create the dump: mysqldump --opt -u root --password=password dbname dbname.dump I'm trying to restore that file with: mysql -u root -ppassword dbname dbname.dump I'm getting the following error: ERROR 1064 at line 118: You have an error in your SQL syntax near 'unique (email) ) TYPE=MyISAM' at line 21 Someone suggested to me that it might be because I have a reserved word for a column name but I checked this option out and I do not. Where did I go wrong? Thanks, Chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DB not restoring from dump file
[EMAIL PROTECTED] mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 10:52 AM said: can you send the contents of your dumpfile up to this point. assuming its line 118 of hte dumpfile and 21 of this build table query Line 118 is the beginning of the 'customers' table definition and line 21 is the last line of that definition. Thanks for you help so far. Chris. DUMP: -- MySQL dump 8.22 -- -- Host: localhostDatabase: aardcart - -- Server version 3.23.56 -- -- Table structure for table 'cart' -- DROP TABLE IF EXISTS cart; CREATE TABLE cart ( id int(10) unsigned NOT NULL auto_increment, phpsessid varchar(32) NOT NULL default '', cust_id int(10) unsigned NOT NULL default '0', created datetime NOT NULL default '-00-00 00:00:00', lastaccessed datetime NOT NULL default '-00-00 00:00:00', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart DISABLE KEYS */; -- -- Dumping data for table 'cart' -- LOCK TABLES cart WRITE; INSERT INTO cart VALUES (1,'89ba2322b4b0723bf052ffc2758266a9',6,'2003-10-07 14:50:17','2003-10-10 16:27:49'),(2,'89ba2322b4b0723bf052ffc2758266a9',12,'2003-10-07 16:04:01','2003-10-07 16:05:09'),(10,'cdf88dc1c29adde03b6e3e79942f2a07',0,'2003-10-22 09:48:01','2003-10-22 09:48:01'); /*!4 ALTER TABLE cart ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents' -- DROP TABLE IF EXISTS cart_contents; CREATE TABLE cart_contents ( id int(10) unsigned NOT NULL auto_increment, cart_id int(10) unsigned NOT NULL default '0', prod_id varchar(25) NOT NULL default '', price float(4,2) unsigned NOT NULL default '0.00', qty smallint(5) unsigned NOT NULL default '0', PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents DISABLE KEYS */; -- -- Dumping data for table 'cart_contents' -- LOCK TABLES cart_contents WRITE; INSERT INTO cart_contents VALUES (1,1,'testing013',99.00,4),(10,1,'MONUTB',145.00,1),(9,1,'MONUTB',145.00 ,1),(6,1,'MONUTB',145.00,2),(8,1,'ATITCB',194.00,1),(11,10,'testing019', 104.00,1),(12,10,'testing011',99.00,1); /*!4 ALTER TABLE cart_contents ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'cart_contents_attributes' -- DROP TABLE IF EXISTS cart_contents_attributes; CREATE TABLE cart_contents_attributes ( cart_cont_id int(10) unsigned NOT NULL default '0', attr_id smallint(5) unsigned NOT NULL default '0', option_id smallint(5) unsigned NOT NULL default '0', cart_id int(10) unsigned NOT NULL default '0' ) TYPE=MyISAM; /*!4 ALTER TABLE cart_contents_attributes DISABLE KEYS */; -- -- Dumping data for table 'cart_contents_attributes' -- LOCK TABLES cart_contents_attributes WRITE; INSERT INTO cart_contents_attributes VALUES (9,40,118,1),(8,24,4,1),(8,7,17,1),(8,6,14,1),(6,40,118,1),(8,2,8,1),(10 ,40,118,1),(12,39,113,10); /*!4 ALTER TABLE cart_contents_attributes ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'categories' -- DROP TABLE IF EXISTS categories; CREATE TABLE categories ( id int(10) unsigned NOT NULL auto_increment, name varchar(50) NOT NULL default '', lft smallint(5) unsigned NOT NULL default '0', rgt smallint(5) unsigned NOT NULL default '0', description tinytext NOT NULL, PRIMARY KEY (id) ) TYPE=MyISAM; /*!4 ALTER TABLE categories DISABLE KEYS */; -- -- Dumping data for table 'categories' -- LOCK TABLES categories WRITE; INSERT INTO categories VALUES (31,'Riot Gear',98,99,''),(30,'Entry Tools',96,97,''),(29,'Hydration',94,95,''),(28,'Load Bearing',78,93,''),(27,'Gloves',74,77,''),(26,'Apparel',70,73,''),(21,'A rmor',52,53,''),(20,'WMD',24,51,''),(19,'Less-Lethal',6,23,''),(25,'Flas hlights',68,69,''),(24,'Batons',62,67,''),(23,'Duty Gear',56,61,''),(22,'Holsters',54,55,''),(32,'Gear Bags/Cases',100,101,''),(38,'Root',1,102,''),(39,'Nuclear',49,50,''),(40 ,'Biological',45,48,''),(41,'Chemical',27,44,''),(73,'Force Protection',4,5,''),(74,'Batons',2,3,''); /*!4 ALTER TABLE categories ENABLE KEYS */; UNLOCK TABLES; -- -- Table structure for table 'customers' -- DROP TABLE IF EXISTS customers; CREATE TABLE customers ( id int(10) unsigned NOT NULL auto_increment, fname varchar(20) NOT NULL default '', lname varchar(20) NOT NULL default '', address1 varchar(40) NOT NULL default '', address2 varchar(40) default '', city varchar(20) NOT NULL default '', state char(2) NOT NULL default '', zip varchar(10) NOT NULL default '', phone varchar(20) default '', fax varchar(20) default '', email varchar(32) NOT NULL default '', newsletter tinyint(1) NOT NULL default '0', password varchar(32) NOT NULL default '', signupdate datetime NOT NULL default '-00-00 00:00:00', lastvisit datetime NOT NULL default '-00-00 00:00:00', type tinyint(3) unsigned NOT NULL default '0', security smallint(6) NOT NULL default '0', company varchar(64) default '', PRIMARY KEY (id), UNIQUE KEY unique (email) ) TYPE=MyISAM;
RE: DB not restoring from dump file
Dathan Vance Pattishall mailto:[EMAIL PROTECTED] on Monday, October 27, 2003 11:28 AM said: Try changing the keyname unique to email. UNIQUE KEY email (email) Thanks, this worked. I ended up having to change two more instances of the same error in different tables. Why would the mysqldump command create a dump file with a syntax error in it? Chris. -- Don't like reformatting your Outlook replies? Now there's relief! http://home.in.tum.de/~jain/software/outlook-quotefix/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
