show tables
Hi, I want to print the list of tables (one on a line) from a database into an external file. I have tried: mysql -u user -p database -e 'show tables;' file.txt mysql -u root -p information_schema -e 'select table_name from tables where table_schema=database_name;' file.txt But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. The list of tables is big, and it doesn't fit into a screen and that's why I need to print it into a file. Please tell me how can I do this if it possible. Thank you. Octavian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show tables
Octavian Rasnita wrote: Hi, I want to print the list of tables (one on a line) from a database into an external file. I have tried: mysql -u user -p database -e 'show tables;' file.txt mysql -u root -p information_schema -e 'select table_name from tables where table_schema=database_name;' file.txt But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. The list of tables is big, and it doesn't fit into a screen and that's why I need to print it into a file. Please tell me how can I do this if it possible. Thank you. Octavian Hey, Try: mysql -uuser -ppassword -e SHOW TABLES FROM database; file.txt Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 +32 (0)473 47 62 88 [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show tables
On Tue, May 1, 2007 09:57, Stijn Verholen wrote: Octavian Rasnita wrote: mysql -u user -p database -e 'show tables;' file.txt That one works for me, with a litle change. -s (kill's columnnames a.o.). [EMAIL PROTECTED]:~# mysql -s -u rppt test -e 'show tables;' file.txt [EMAIL PROTECTED]:~# cat file.txt abc store t tablea trans Hmmm, there is no such user -u rppt but it works anyway ??? Maybe because it's the test db. But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. What error message do you get ? -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
I'm having problems understanding NULL. I grasp what a NULL value is, Actually, you don't :-) NULL is not a value, it's the lack of value and a state. A column can have 2 states: NULL or NOT NULL, which is part of the reason why SQL allows for the IS NULL and IS NOT NULL clause as opposed to checking for certain values with the equals operator ( MyColumn = 'My Value') but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) If the set is empty, the columns aren't NULL. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: show tables
Ok, thank you all. It was my mistake. I have quoted the SQL command with single quotes under Windows. Octavian - Original Message - From: Stijn Verholen [EMAIL PROTECTED] To: Octavian Rasnita [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, May 01, 2007 10:57 AM Subject: Re: show tables Octavian Rasnita wrote: Hi, I want to print the list of tables (one on a line) from a database into an external file. I have tried: mysql -u user -p database -e 'show tables;' file.txt mysql -u root -p information_schema -e 'select table_name from tables where table_schema=database_name;' file.txt But the result was the help file displayed by MySQL when a command is not correct. The SQL queries I gave are correct, because they work when I give them at the mysql prompt. The list of tables is big, and it doesn't fit into a screen and that's why I need to print it into a file. Please tell me how can I do this if it possible. Thank you. Octavian Hey, Try: mysql -uuser -ppassword -e SHOW TABLES FROM database; file.txt Stijn -- metastable Stijn Verholen Camille Huysmanslaan 114, bus 2 B-2020 ANTWERPEN +32 (0)3 707 08 08 +32 (0)473 47 62 88 [EMAIL PROTECTED] http://www.metastable.be BTW-BE 0873.645.643 bankrek.nr. ING 363-0106543-77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
best filesystem for mysql
hello list, I saw this article for the suitable filesystem for mysql. http://www.bullopensource.org/ext4/sqlbench/ From what I saw,the best filesystem for MyISAM is ext3,the best filesystem for InnoDB is Reiserfs. How about your thought on it?Thanks. 50 AMAZON-Einkaufsgutschein bei Bestellung von Arcor-DSL: Viel oder wenig? Schnell oder langsam? Unbegrenzt surfen + telefonieren ohne Zeit- und Volumenbegrenzung? DAS TOP ANGEBOT JETZT bei Arcor: günstig und schnell mit DSL - das All-Inclusive-Paket für clevere Doppel-Sparer, nur 39,85 inkl. DSL- und ISDN-Grundgebühr! http://www.arcor.de/rd/emf-dsl-2 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Research Subjects drawn randomly from databases
Well, then color me baffled. I don't know why your first query didn't work. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: John Kebbel [mailto:[EMAIL PROTECTED] Sent: Monday, April 30, 2007 2:42 PM To: MySQL Subject: RE: Research Subjects drawn randomly from databases id is an integer ... describe persons; ++---+--+-+--- --+---+ | Field | Type | Null | Key | Default | Extra | ++---+--+-+--- --+---+ | ID | int(11) | YES | | NULL | I got a 0 count ... SELECT COUNT(*) FROM persons WHERE ROUND(id) != id; +--+ | COUNT(*) | +--+ |0 | +--+ 1 row in set (0.06 sec) On Mon, 2007-04-30 at 10:45 -0400, Jerry Schwartz wrote: Is your ID field an integer? If not, you might be running into some rounding corner cases. I don't see why that would happen, off-hand, since integers can be stored exactly as binary floating point numbers, but who knows. To satisfy your curiosity, you could SELECT COUNT(*) FROM table WHERE ROUND(id) != id; If you get a non-zero count, then you know that there is a possibility of CEIL(RND()) not hitting an ID. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: John Kebbel [mailto:[EMAIL PROTECTED] Sent: Sunday, April 29, 2007 11:49 AM To: MySQL Subject: Re: Research Subjects drawn randomly from databases I rewrote my line using your suggestion ... select id,first,middle,last from persons order by rand() limit 10; and it worked perfectly. I'm still curious about why my original version gave such cockeyed results, but I'll focus on the successful solution and leave that unsolved problem for another day. Thank you for your solution Michael. On Sun, 2007-04-29 at 08:48 -0400, Michael Dykman wrote: If might suggest: SELECT * FROM BAR ORDER BY RAND() LIMIT 10 On 4/29/07, John Kebbel [EMAIL PROTECTED] wrote: For possible educational research purposes, I was playing around with a query that would randomly select people from a database. The database I experiment with has a group of fictitious persons with id numbers (primary key) ranging sequentially from 2 to 378. When I ran these queries below, I was expecting to select five random persons from the database. The query partially worked. I was getting random subjects, but everytime I ran the query, I got a different number of subjects, stretching from 0 and up (sometimes as many as 8 or 9). I could see the query generating fewer rows if I duplicated an id or made an off-by-one error, but I don't see how it could generate more than five. Does anyone see my error? (I've used two equivalent forms for the query below; both did the same thing) select id,first,middle,last from persons where id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1) or id = ceil(rand()*377+1); select id,first,middle,last from persons where id in (ceil(rand()*377 +1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1), ceil(rand()*377+1)); +--+-++--+ | id | first | middle | last | +--+-++--+ | 35 | Viridiana | W | McCarthy | | 47 | Crystal | O | Cassady | | 67 | Ricardo | L | Johnson | | 183 | Christopher | E | Denver | | 237 | Christopher | B | Brenner | | 255 | Danielle| W | Nickels | | 299 | Christine | D | Dexter | | 300 | Rachel | J | Baker| | 339 | Jenna | O | Murray | +--+-++--+ 9 rows in set (0.00 sec) -- 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] -- 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]
Re: IS NULL Question
By chance when you set them as null did you use null or 'null' setting it as a string value? __ Lucas Heuman Phone 609.485.5401 Martijn Tonies [EMAIL PROTECTED] 05/01/2007 04:45 AM To MySQL mysql@lists.mysql.com cc Subject Re: IS NULL Question I'm having problems understanding NULL. I grasp what a NULL value is, Actually, you don't :-) NULL is not a value, it's the lack of value and a state. A column can have 2 states: NULL or NOT NULL, which is part of the reason why SQL allows for the IS NULL and IS NOT NULL clause as opposed to checking for certain values with the equals operator ( MyColumn = 'My Value') but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) If the set is empty, the columns aren't NULL. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Need help with query
The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Hi Jesse, Jesse wrote: The following query works just fine: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum However, I would like to add the to have a total of the adults and kids on the bus, so I change my query as follows: SELECT B.BusNum, COALESCE(CampCount.Cnt,0) As Kids, B.CamperCapacity, COALESCE(CounselorCount.Cnt,0) As Adults, B.AdultCapacity, Kids + Adults As GT FROM Buses B LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Campers GROUP BY BusID) AS CampCount ON CampCount.BusID=B.ID LEFT JOIN (SELECT BusID,Count(*) As Cnt FROM Counselors GROUP BY BusID) AS CounselorCount On CounselorCount.BusID=B.ID ORDER BY B.BusNum When I try to execute this, I get the error, Unknown column 'Kids' in 'field list' How do I properly add these together to get a total column? Column aliases can't be referred to later in the select list as far as I know. The only way I know to do this is simply duplicate the expressions: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with query
Jesse wrote: COALESCE(CampCount.Cnt,0) + COALESCE(CounselorCount.Cnt,0) as GT Duuuh. Why didn't I think of that. What is MySQL's issue with referring to the variables (As assignments, whatever you want to call them)? I've had issues like this in situations like this one, when trying to use them in the ORDER BY clause, and other places. They simply don't 'exist' at that point in time. All of the rows are read at one point in time (according to a WHERE clause if applicable), then the expressions within the column lists are done, then grouping, order by etc., and finally HAVING clauses are done (where one can use a column alias, when not available within a WHERE clause). However, you should be able to use them in an ORDER BY - as they do exist at that point - so I'm not sure that issue would have been related to this. Best regards Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB table lock on INSERT
Baron- Thank you for the InnoDB Lock Monitor pointer. I now have a greate deal of informaiton to digest. I will try innotop when I have a chance. :) -Paul Hi Paul, Power, Paul C. wrote: I have an INSERT waiting for a table lock, and i do not understand why. ---TRANSACTION 0 308691, ACTIVE 5 sec, process no 8876, OS thread id 1296547864 inserting mysql tables in use 1, locked 1 LOCK WAIT 1 lock struct(s), heap size 320 MySQL thread id 79126, query id 1113322 bil.oneeighty.com 216.187.166.2 voxcall update INSERT INTO voxinternal.Entity ( Entity_ID, Entity_Name, Entity_Type, Who, Ins_Date ) VALUES ( 'OLEO', 'Ole Matadors', 'Organization', 'PYTH', now() ) --- TRX HAS BEEN WAITING 5 SEC FOR THIS LOCK TO BE GRANTED: TABLE LOCK table `voxinternal/Entity` trx id 0 308691 lock mode IX waiting -- The MySQL version is 5.1.7-beta-max-log Can any one illuminate me? I have read all documentation that i can find that appears pertinent. Does anyone know how I may find out who already has a lock in/on the table? Can one find out what locks are going to be issued when some particular piece of SQL is executed? I don't know any way to find out what locks are going to be acquired, other than if you are deleting or updating specific records, you can generally be sure they will be locked. In this case you have a TABLE LOCK, not a record lock. I'm not sure what level that happens at (MySQL server, or InnoDB storage engine). If it's an InnoDB lock, you can use the InnoDB Lock Monitor as described in the manual (short version: issue CREATE TABLE innodb_lock_monitor(a int) ENGINE=InnoDB). It prints to your mysql.log file. It looks very similar to SHOW INNODB STATUS output, except it prints all locks held, not just the locks waited for. I wrote a tool called innotop that can help with general monitoring (http://sourceforge.net/projects/innotop) and some insight into locking. But there's only so much data available to it :-) I plan to make it capable of reading the lock monitor file I just mentioned, so you don't have to wade through it by hand. But that's future functionality. You can also try SHOW OPEN TABLES and see if that table is locked with a table lock. That might give some insight. My guess is there is a lock from LOCK TABLES on the table, otherwise it would just be waiting to lock the gap between records (so it can insert into the gap). Visibility into locking is my favorite gripe with MySQL -- it is very hard to find answers to these sorts of questions. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Kevin Hunter wrote: At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin MyISAM has no problem representing NULL. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: IS NULL Question
We use NULL all the time with MyISAM tables, and I've never noticed a problem. Well, there was one bizarre bit of business that's already been mentioned: my predecessor actually put the string NULL into a field, and since it looks the same in the MySQL client that gave me a devil of a time. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Kevin Hunter [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 01, 2007 11:37 AM To: John Kebbel Cc: MySQL List Subject: Re: IS NULL Question At 9:06p -0400 on 30 Apr 2007 John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, A NULL value is rather an oxymoron. It'd be more accurate to say that NULL means absence of a value. but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin -- 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]
Re: IS NULL Question
Caveat emptor: I haven't tested this in about a year. Are you perchance using a table type of MyISAM? I seem to recall that MyISAM has a hard time actually representing NULL internally. [ ... Thinks for a minute ... ] I remember something about spaces, like, I think I did INSERT ( val1, val2, NULL ) INTO myTable; and was only able to get the tuples back when I did SELECT * FROM myTable WHERE col3 = ' '; -- a space character If this is the case, you might consider using a different table type, such as InnoDB. HTH, Kevin MyISAM has no problem representing NULL. My mistake. Obviously old data. Kevin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
Jerry Schwartz wrote: We use NULL all the time with MyISAM tables, and I've never noticed a problem. I think there was a bug at one point dealing with NULL and empty strings, but it strikes me that it was a bug in the new client/server protocol that was introduced in (4.1? 5.0?). So, it might be that the columns contain empty strings, yet the client displays them as NULL. Regardless, the earlier suggestion about using length() should show what's really in the columns. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Workbench
Hi, I'm looking for database modeling tool form MySQL. Anybody used the MySQL Workbench? I know the Workbench is in Alpha production, though some hints? How about DB Designer? Thanks for any respond. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump running out out of memory
Have you tried this flag? -q, --quick Don't buffer query, dump directly to stdout. On Sun, 29 Apr 2007, Fredrik Carlsson wrote: Hi, I have a problem with mysqldump, its exiting with the message mysqldump: Error 5: Out of memory (Needed 22042208 bytes) when dumping table `theTable` at row: 2990911 I have searched the archives and tried what people suggested but nothing seems to work, I'm dumping using the following command: /usr/pkg/bin/mysqldump -h localhost -B theDatabase --skip-opt --max_allowed_packet=1024M -q Any tips on how to get the dump running? the dump should be about 15-20GB in size the fully dumped, but I never seems to get there. // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Workbench
I've had a lot of fun using Toad on Oracle. Quest got a freeware for MySQL, that i never got around to try, but have a look at: http://www.quest.com/toad-for-mysql/ On Tue, May 1, 2007 21:36, Afan Pasalic wrote: Hi, I'm looking for database modeling tool form MySQL. Anybody used the MySQL Workbench? I know the Workbench is in Alpha production, though some hints? How about DB Designer? Thanks for any respond. -afan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- Later Mogens Melander +45 40 85 71 38 +66 870 133 224 -- This message has been scanned for viruses and dangerous content by OpenProtect(http://www.openprotect.com), and is believed to be clean. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqldump running out out of memory
Hi, Yes I'm using the -q option with mysqldump, but the result is the same. This is a replicated environment and the master is running FreeBSD and the slave NetBSD and on the master which only has InnoDB tables there is no problems to run a dump but the machine is to loaded so we can not afford to run the dump there. The tables on the slave is mostly Myisam, maybe there is some kind of memory buffer that I'm missing to tune on NetBSD but i can't figure out what it can be, I've already increased the ulimit values for the session running the dump. // Fredrik Atle Veka wrote: Have you tried this flag? -q, --quick Don't buffer query, dump directly to stdout. On Sun, 29 Apr 2007, Fredrik Carlsson wrote: Hi, I have a problem with mysqldump, its exiting with the message mysqldump: Error 5: Out of memory (Needed 22042208 bytes) when dumping table `theTable` at row: 2990911 I have searched the archives and tried what people suggested but nothing seems to work, I'm dumping using the following command: /usr/pkg/bin/mysqldump -h localhost -B theDatabase --skip-opt --max_allowed_packet=1024M -q Any tips on how to get the dump running? the dump should be about 15-20GB in size the fully dumped, but I never seems to get there. // Fredrik Carlsson -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
expire_logs_days
There's a system variable called expire_logs_days that lets you set a number of days to keep binary logs, and automatically delete logs older than that. I've heard rumors that using this feature is problematic. I notice that in the MySQL documentation about binary logging, it tells you to use purge master logs to delete old logs, and does not mention the expire_logs_days variable as another option. Is there a reason for this omission, or is it safe to use? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: IS NULL Question
It looks like it was a string named NULL posing as a null value. I got 0 for ISNULL(suffix), which I assume means false. I tried this command ... update persons set suffix = 'Empty' where suffix = ''; It changed the NULLs to Empty. On Mon, 2007-04-30 at 18:12 -0700, Jeremy Cole wrote: Hi John, Are you sure they are actually NULL and not NULL (i.e. the string NULL)? Try this: SELECT first, last, ISNULL(suffix), LENGTH(suffix) FROM persons LIMIT 5; Regards, Jeremy John Kebbel wrote: I'm having problems understanding NULL. I grasp what a NULL value is, but I can't get NULL to perform correctly. For instance, if I do a Select statement, I see that I have columns with a NULL value. select first, last, suffix from persons LIMIT 5; +---+--++ | first | last | suffix | +---+--++ | Benjamin | Page | NULL | | Jonathan | Watson | NULL | | Jose | Thorson | NULL | | Alejandro | Nickels | NULL | | Griselda | Richards | NULL | +---+--++ 5 rows in set (0.01 sec) Logically, it seems that a Select statement should find these five plus any other NULL values in the suffix column. However, such a select statment returns an empty set. mysql select first, last, suffix from persons where suffix IS NULL; Empty set (0.00 sec) Does anyone see what I'm doing wrong? (Thanks in advance for any help.) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
batch mode
Because I am running on batch mode therefore I'm trying to direct my session output to a file -- meaning stdout. But I'm having a problem. For instance this input: use test_db gave me no output but this input show tables gave me an output. What is missing, what's the trick. Help me Y'all. Thx. This message is intended only for the use of the Addressee and may contain information that is PRIVILEGED and CONFIDENTIAL. If you are not the intended recipient, you are hereby notified that any dissemination of this communication is strictly prohibited. If you have received this communication in error, please erase all copies of the message and its attachments and notify us immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
REGEXP Character Classes
Linux Version: Linux version 2.6.15-28-386 MySQL Version: 5.0.22-Debian_0ubuntu6.06.3-log I have two queries using REGEXP character classes and their respective outputs below. The first is supposed to match an upper case character in a column, but I wind up with 4 rows out of 25 that contain only lower case characters. The second is supposed to match lower case characters but returns 11 rows out of 25 that contain only upper case characters. Am I using these character classes correctly? -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | +--+--+--++++ 25 rows in set (0.00 sec) -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |5 | VBOEUTTM | Human Resources | Purchasing | Clerical | c18528 | |9 | ENDPAXWW | Human Resources | Accounting | Clerical | 73d00f | | 14 | TEVXTOBK | Human Resources | Accounting | Sales Rep. | 6606a0 | | 15 | WREZUFAU | Customer Service | Receiving | Asst. | 14159 | | 17 | LGMMPJEY | Customer Service | Accounting | Asst. | 291512 | | 21 | DMCLWWDX | Customer Service | Receiving | Sales Rep. | 968745 | | 23 | BZZCQWWE | Customer Service | Payroll| Asst. | 11f2b7 | | 24 | EPGWQEXC | Customer Service | Payroll| Clerical | 706894 | | 31 | NYOOQVJI | Human Resources | Accounting | Sales Rep. | e7d0bc | | 33 | BUTSHOUS | Human Resources | Payroll| Asst. | 548082 | | 34 | VOSCTTGZ | Customer Service | Receiving | Sales Rep. | 858435 | +--+--+--++++ 25 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Rename or Delete Users
I've discovered that I can type ... use mysql; show tables; describe user; update user set user='newName' where user='oldName' However, this seems to have no effect on the user name in the privileges table when I searched them. The MySQL site says the alternative way to do this has the same problem: RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events. Is there some kind of shortcut to a privilege swap, or am I better off just deleting users and recreating them with the new name. ___ Sorry to deluge this mailing list with so many requests, but I'm trying to build my knowledge base for a CMDEV and I spend hours painting myself into corners with MySQL every night and weekend day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rename or Delete Users
I trust that you realize that you can never expect related tables to be uodated in the mysql database if you using using SQL to manipulate them directly. moving on.. I can't think of any mainstream systrm that generally allows usernames to be changed.. I mean, sure, there is a trivial hack to do it on not-so-secure unix systems but it's still far from common practice. I recommend that you take the easy road and just delete/create. On 5/1/07, John Kebbel [EMAIL PROTECTED] wrote: I've discovered that I can type ... use mysql; show tables; describe user; update user set user='newName' where user='oldName' However, this seems to have no effect on the user name in the privileges table when I searched them. The MySQL site says the alternative way to do this has the same problem: RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events. Is there some kind of shortcut to a privilege swap, or am I better off just deleting users and recreating them with the new name. ___ Sorry to deluge this mailing list with so many requests, but I'm trying to build my knowledge base for a CMDEV and I spend hours painting myself into corners with MySQL every night and weekend day. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Rename or Delete Users
Thank you for stating something obvious that I was completely overlooking. With normal tables I had created, I would never expect related tables to be magically updated by something I did to one table. Because I was dealing with a table in the mysql database that was created by MySQL, I had assumed it had some special magical status, that behind the scenes routines would synchronize this table with others if I found the right key (command) to do so. They're just normal tables, aren't they? Anyway, it's DELETE and CREATE for me from now on. Thank you. On Tue, 2007-05-01 at 19:51 -0400, Michael Dykman wrote: I trust that you realize that you can never expect related tables to be uodated in the mysql database if you using using SQL to manipulate them directly. moving on.. I can't think of any mainstream systrm that generally allows usernames to be changed.. I mean, sure, there is a trivial hack to do it on not-so-secure unix systems but it's still far from common practice. I recommend that you take the easy road and just delete/create. On 5/1/07, John Kebbel [EMAIL PROTECTED] wrote: I've discovered that I can type ... use mysql; show tables; describe user; update user set user='newName' where user='oldName' However, this seems to have no effect on the user name in the privileges table when I searched them. The MySQL site says the alternative way to do this has the same problem: RENAME USER does not automatically migrate any database objects that the user created, nor does it migrate any privileges that the user had prior to the renaming. This applies to tables, views, stored routines, triggers, and events. Is there some kind of shortcut to a privilege swap, or am I better off just deleting users and recreating them with the new name. ___ Sorry to deluge this mailing list with so many requests, but I'm trying to build my knowledge base for a CMDEV and I spend hours painting myself into corners with MySQL every night and weekend day. -- 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]
Re: REGEXP Character Classes
At 6:20 PM -0400 5/1/07, John Kebbel wrote: Linux Version: Linux version 2.6.15-28-386 MySQL Version: 5.0.22-Debian_0ubuntu6.06.3-log I have two queries using REGEXP character classes and their respective outputs below. The first is supposed to match an upper case character in a column, but I wind up with 4 rows out of 25 that contain only lower case characters. The second is supposed to match lower case characters but returns 11 rows out of 25 that contain only upper case characters. Am I using these character classes correctly? The classes define what characters are members of the class. But the matching is determined by the collation of the REGEXP operands. If you have a case-insensitive collation (which you do, by default), the matching takes place in a case-insensitive fashion. You could apply a COLLATE clause to one of the operands to force the use of a case-sensitive collation. -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:upper:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |8 | euwsrbwm | Customer Service | Accounting | Clerical | 0f1b12 | | 13 | mejccvoz | Customer Service | Receiving | Clerical | 437113 | | 18 | kwkheprh | Customer Service | Purchasing | Clerical | 29652 | | 20 | qpvxvqhz | Customer Service | Accounting | Clerical | bcb244 | +--+--+--++++ 25 rows in set (0.00 sec) -- SELECT id, pswd, division, department, title, classification FROM pswds WHERE pswd REGEXP '[:lower:]' limit 25; -- +--+--+--++++ | id | pswd | division | department | title | classification | +--+--+--++++ |5 | VBOEUTTM | Human Resources | Purchasing | Clerical | c18528 | |9 | ENDPAXWW | Human Resources | Accounting | Clerical | 73d00f | | 14 | TEVXTOBK | Human Resources | Accounting | Sales Rep. | 6606a0 | | 15 | WREZUFAU | Customer Service | Receiving | Asst. | 14159 | | 17 | LGMMPJEY | Customer Service | Accounting | Asst. | 291512 | | 21 | DMCLWWDX | Customer Service | Receiving | Sales Rep. | 968745 | | 23 | BZZCQWWE | Customer Service | Payroll| Asst. | 11f2b7 | | 24 | EPGWQEXC | Customer Service | Payroll| Clerical | 706894 | | 31 | NYOOQVJI | Human Resources | Accounting | Sales Rep. | e7d0bc | | 33 | BUTSHOUS | Human Resources | Payroll| Asst. | 548082 | | 34 | VOSCTTGZ | Customer Service | Receiving | Sales Rep. | 858435 | +--+--+--++++ 25 rows in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
