Re: Comma's in data?
Good Morning Steve The insertion of comma in your Data works if your columns are VARCHAR and you have place Data Value in 'tick marks' e.g. mysql alter table test CHANGE test test VARCHAR(100); Query OK, 1 row affected (0.03 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql commit; Query OK, 0 rows affected (0.00 sec) mysql desc test.test - ; +---+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-+---+ | test | varchar(100) | YES | | NULL| | +---+--+--+-+-+---+ 1 row in set (0.00 sec) mysql insert into test.test value('Oracle Corporation 10 Van der Graaf, Burling ton MA'); Query OK, 1 row affected (0.00 sec) mysql select * from test.test; ++ | test | ++ | Oracle Corporation 10 Van der Graaf, Burlington MA | ++ 1 rows in set (0.00 sec) Viel Gluck/Buena Suerte Martin-- - Original Message - From: Steve Edberg [EMAIL PROTECTED] To: J. Todd Slack [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Sunday, March 09, 2008 1:00 AM Subject: Re: Comma's in data? At 11:52 PM -0800 3/7/08, J. Todd Slack wrote: Hi All, I have a client that wants to insert data into a VarChar field that contains commas. These are property addresses. Example: 2966 Moorpark Ave, San Jose, CA, 95128 1 Infinite Loop, Cupertino, CA, 95 How can I allow this? Thanks! -Jason I've never had to escape commas; only special characters, eg: '_%. MySQL seems to ignore escaping if the following character is not special, though: [EMAIL PROTECTED] create table test (t1 varchar(255)); Query OK, 0 rows affected (0.03 sec) [EMAIL PROTECTED] insert into test values ('qwert'), ('qwe,rt'),('qwe\,rt'); Query OK, 3 rows affected (0.00 sec) Records: 3 Duplicates: 0 Warnings: 0 [EMAIL PROTECTED] select * from test; ++ | t1 | ++ | qwert | | qwe,rt | | qwe,rt | ++ 3 rows in set (0.00 sec) Are you getting an error when you insert a row? If so, what is the error? Perhaps you are having a character set issue. This is what mine looks like: [EMAIL PROTECTED] show variables like 'character\_set\_%'; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | +--++ 6 rows in set (0.00 sec) steve -- +--- my people are the people of the dessert, ---+ | Steve Edberghttp://pgfsun.ucdavis.edu/ | | UC Davis Genome Center[EMAIL PROTECTED] | | Bioinformatics programming/database/sysadmin (530)754-9127 | + said t e lawrence, picking up his fork + -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Server Instance Setup Error
Hello I just tried to install the mysql server and I am getting the following error messgae for the Apply Security Setting: The security settings could not be applied. Error number 1045 Access denied for use '[EMAIL PROTECTED]' (using password:YES) What does this mean and how do I fix it? Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Instance Setup Error
Greetings Andrew, Make sure you have granted access to your user: (run command line) mysql GRANT ALL ON databaseName.* TO 'your_mysql_name'@'your_client_host'; Sincerely, Craig Huffstetler P.S. - What version of MySQL, what operating system and are you using the command line (I was assuming you are familiar with the command line)...the command above is to be run via the command line. On Sun, Mar 9, 2008 at 2:23 PM, AndrewMcHorney [EMAIL PROTECTED] wrote: Hello I just tried to install the mysql server and I am getting the following error messgae for the Apply Security Setting: The security settings could not be applied. Error number 1045 Access denied for use '[EMAIL PROTECTED]' (using password:YES) What does this mean and how do I fix it? Thanks, Andrew -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: Server Instance Setup Error
Greetings again Andrew, That error message is usually when you try to login to MySQL by whatever means (the Windows install Wizard may be attempting this in the final steps upon starting up? But it should not be starting up as root...). Can you complete the installation using the wizard (I am assuming this is where the error is occurring), then start-up MySQL? This error generally occurs when a user attempts to login. We may need some more details to troubleshoot this. Can you please re-run the install. Sincerely, Craig Huffstetler On Sun, Mar 9, 2008 at 6:12 PM, AndrewMcHorney [EMAIL PROTECTED] wrote: Hello I am running on Windows XP and MYSQL 5.05.1A. I have not yet started up mysql. Andrew At 13:52 2008-03-09, you wrote: mysql GRANT ALL ON databaseName.* TO 'your_mysql_name'@'your_client_host';
Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote: I'm trying to dump all the columns of a table but one in a restructuring of my schema. I found this post: http://www.jsw4.net/info/listserv_archives/mysql/05-wk49/msg00131.html which seems to indicate that this isn't possible, but I was wondering if anyone had any suggestions. My current solution is to do a simple search and replace, but that won't work for everything. I could always do regex search/replace, as well, but I'm hoping for something native to mysqldump, since the files are going to be rather big. Thanks, Waynn I would probably use INTO OUTFILE. Any particular reason it does not work? CREATE TABLE `t` ( `c1` varchar(10) NOT NULL, `c2` varchar(10) NOT NULL, `c3` varchar(10) NOT NULL ); INSERT INTO `t` (`c1`, `c2`, `c3`) VALUES ('1', '2', '3'), ('4', '5', '6'), ('7', '8', '9'), ('10', '11', '12'); mysql select c1,c2 from t INTO OUTFILE 'outfile'; Query OK, 4 rows affected (0.00 sec) outfile contents: 1 2 4 5 7 8 10 11 -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote: Subject: Re: mysqldump on specific columns only? I'm trying to dump all the columns of a table but one in a restructuring of my schema. By dump, I assume you mean output, not delete. I suspect I'm too inexperienced in MySQL or I don't understand the question, because I don't see why you haven't mentioned ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original database but rather a copy database: mysqldump, load it into another database, ALTER TABLE DROP COLUMN. It's true that you wrote since the files are going to be rather big. The second notion has the disadvantage that the data will be copied three times (mysqldump, load, ALTER TABLE). On the other hand, you have the full reliability of mysqldump, with its care in copying CREATE TABLEs and data correctly. Am I perhaps misunderstanding? On Sun, 9 Mar 2008, Rob Wultsch [EMAIL PROTECTED] wrote: I would probably use [SELECT] INTO OUTFILE. Any particular reason it does not work? It's not well-suited for restructuring of my schema. mysqldump emits SQL that can be used immediately to create the table with exactly the same data types and column names and keys and other properties (like NOT NULL), and to insert data into columns with quoting and escaping as needed, and nice little flourishes like DROP TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES. mysql select c1,c2 from t INTO OUTFILE 'outfile'; Query OK, 4 rows affected (0.00 sec) outfile contents: 1 2 4 5 7 8 10 11 That provides only the data to insert. Further, it would require considerable massaging to get it into the form of INSERT statements, though for all I know MySQL provides other mechanisms to load that sort of data. But those are tab-separated columns: what if one of the columns were a text field with a literal tab? -- Tim McDaniel, [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
Thanks for the responses, Inline: On Sun, Mar 9, 2008 at 7:49 PM, Tim McDaniel [EMAIL PROTECTED] wrote: On Sat, Mar 8, 2008 at 11:55 PM, Waynn Lue [EMAIL PROTECTED] wrote: Subject: Re: mysqldump on specific columns only? I'm trying to dump all the columns of a table but one in a restructuring of my schema. By dump, I assume you mean output, not delete. Indeed, I meant output. I suspect I'm too inexperienced in MySQL or I don't understand the question, because I don't see why you haven't mentioned ALTER TABLE DROP COLUMN. Or, if you don't want to touch the original database but rather a copy database: mysqldump, load it into another database, ALTER TABLE DROP COLUMN. It's true that you wrote since the files are going to be rather big. The second notion has the disadvantage that the data will be copied three times (mysqldump, load, ALTER TABLE). On the other hand, you have the full reliability of mysqldump, with its care in copying CREATE TABLEs and data correctly. Am I perhaps misunderstanding? ALTER TABLE unfortunately takes longer than dumping and recreating the tables. I started a previous thread on this, and the consensus was recreating everything was faster, and I'm taking advantage of this downtime to restructure our schema, and add a few other columns that will be needed. The initial impetus was expanding an INT to a BIGINT, but in the presence of a foreign key. So that would require dropping the FK, expanding the INT to BIGINT in two tables, then recreating the FK. I would also want to add three more columns while I'm at it. And in this case, the cost of all those operations is significantly higher than just dumping all the tables, truncating/dropping the tables, recreating the tables, then importing. On Sun, 9 Mar 2008, Rob Wultsch [EMAIL PROTECTED] wrote: I would probably use [SELECT] INTO OUTFILE. Any particular reason it does not work? It's not well-suited for restructuring of my schema. mysqldump emits SQL that can be used immediately to create the table with exactly the same data types and column names and keys and other properties (like NOT NULL), and to insert data into columns with quoting and escaping as needed, and nice little flourishes like DROP TABLE IF EXISTS, LOCK TABLES, UNLOCK TABLES. I'm actually not going to be using the exact same schema anyway, so SELECT INTO does work, but ... let me continue this thought below. mysql select c1,c2 from t INTO OUTFILE 'outfile'; Query OK, 4 rows affected (0.00 sec) outfile contents: 1 2 4 5 7 8 1011 That provides only the data to insert. Further, it would require considerable massaging to get it into the form of INSERT statements, though for all I know MySQL provides other mechanisms to load that sort of data. But those are tab-separated columns: what if one of the columns were a text field with a literal tab? MySQL does provide LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was hoping to do it through mysqldump instead of SELECT INTO because I assumed the performance was faster. If that's not the case, though, I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I guess most any character is fine as a delimiter, since my fields should all just be IDs... Thanks, Waynn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump on specific columns only?
MySQL does provide LOAD DATA INFILE (http://dev.mysql.com/doc/refman/5.0/en/load-data.html), but I was hoping to do it through mysqldump instead of SELECT INTO because I assumed the performance was faster. If that's not the case, though, I'll go ahead and use SELECT INTO OUTFILE and LOAD DATA INFILE. I guess most any character is fine as a delimiter, since my fields should all just be IDs... Thanks, Waynn OK. Why not just create the new table and SELECT... INSERT . I would think that would be the fastest solution of all. -- Rob Wultsch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]