Re: Not showing repeating fields.
Thank you a lot Ravi. Ravi Kumar.-2 wrote: select distinct vehicle from table_name On 11/6/07, Jamessketch [EMAIL PROTECTED] wrote: Hello, I have a table that looks like this: vehicle | type| color car mercedes blue boat croater silver plane airbus red car volvogreen car BMW white and when I you the SELECT statement to write down just vehicles is shows car boat plane car car. How can I have it in a way where repeating ones will NOT be shown? So I would have written just car boat plane. Thank you in advance. James -- View this message in context: http://www.nabble.com/Not-showing-repeating-fields.-tf4755269.html#a13598356 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Not-showing-repeating-fields.-tf4755269.html#a13603877 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL University session on November 8
Hi, as some of you may have noticed we've been running educational sessions on MySQL internals for quite a while. That program is called MySQL University; see http://forge.mysql.com/wiki/MySQL_University. This Thursday, Sergei Golubchik will give a MySQL University session on: How to use BitKeeper Please register for this session by filling in your name on the session Wiki page that you can find here: http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions Registering is not required but appreciated. Thanks! Those planning to attend a MySQL University session for the very first time should probably read the instructions for attendees, http://forge.mysql.com/wiki/Instructions_for_Attendees. -- Regards, Stefan Hinz [EMAIL PROTECTED], MySQL AB Documentation Manager Berlin, Germany (UTC +1:00/winter, +2:00/summer) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: privileges headache
hi, [EMAIL PROTECTED] wrote: I rarely need to add users to a running setup but I always seem to run into the same sort of problem: I grant privs to a user but they refuse to show up. This user will be the one a web application goes through to access the DB. As the root user: GRANT SELECT, INSERT, UPDATE ON the_database.* - TO [EMAIL PROTECTED] IDENTIFIED BY 'the_password'; Query OK, 0 rows affected (0.00 sec) FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) That statement does (at least) two things to mysql.user: 1) if there's no row in mysql.user for [EMAIL PROTECTED], it creates it 2) if there's an existing row, it updates its password. IT DOES NOT AFFECT ANY OTHER COLUMN IN mysql.user. But it does insert or update a row in mysql.db. Check there. You are confused on the difference between global privileges, which are stored in mysql.user, and database-level privileges, which are stored in mysql.db and which you are changing with this statement. 0 rows affected on the GRANT suggests that not much was accomplished. That is normal. It will never say anything else. SELECT * FROM user WHERE User = 'the_user'\G *** 1. row *** Host: localhost User: the_user Password: *XXX Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N Drop_priv: N Reload_priv: N Shutdown_priv: N Process_priv: N File_priv: N Grant_priv: N References_priv: N Index_priv: N Alter_priv: N Show_db_priv: N Super_priv: N Create_tmp_table_priv: N Lock_tables_priv: N Execute_priv: N Repl_slave_priv: N Repl_client_priv: N Create_view_priv: N Show_view_priv: N Create_routine_priv: N Alter_routine_priv: N Create_user_priv: N ssl_type: ssl_cipher: x509_issuer: x509_subject: max_questions: 0 max_updates: 0 max_connections: 0 max_user_connections: 0 1 row in set (0.00 sec) I then tried: UPDATE user SET Insert_priv = 'Y', Update_priv = 'Y', Select_priv = 'Y' WHERE User = 'the_user'; FLUSH PRIVILEGES; Which gives me what I expected: Select_priv: Y Insert_priv: Y Update_priv: Y But I still cannot update any tables as this user. Probably because you have a database-level or table-level privilege that overrides the global privileges you are granting. I know that I've resolved this issue before but it's been ages since I've run into this (I generally use Postgres but I've been using MySQL since 3.23, so this is a little embarassing). I *can* connect to the DB as this user, and select from tables. I just can't update them: UPDATE command denied to user 'the_user'@'localhost' for table 'customer' Where the heck am i going wrong? -- Baron Schwartz Xaprb LLC http://www.xaprb.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help creating query statement
--- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Account Problems. Can't register server with new account
Run this SQL Query mysql select Host,User from user; Make sure the output has this record values: +---+--+ | Host | User | +---+--+ | localhost | usuario1 | +---+--+ If your mysql error message were to say this: Access denied for user 'usuario1'@'me-me-me.com' The record values need to look like this: +--+--+ | Host | User | +--+--+ | me-me-me.com | usuario1 | +--+--+ Or Access denied for user 'usuario1'@'192.168.20.5' +--+--+ | Host | User | +--+--+ | 192.168.20.5 | usuario1 | +--+--+ To Update: mysql update user set Host=localhost where User='usuario1' LIMIT 1; -RG AlejandraB wrote: Good evening. I have mysql cluster installed and I'm trying to start the mysql server with a new user account recently created, and I'm having trouble accessing the server with the new account. [EMAIL PROTECTED] bin]# ./mysql -u usuario1 -p Enter password: ERROR 1045 (28000): Access denied for user 'usuario1'@'localhost' (using password: YES) The new user does exists and I know the problem is not password related. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select User from user; +--+ | User | +--+ | mysql| | root | | usuario1 | | | | mysql| | root | +--+ Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Please help to solve a serios problem
Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: utf8 queries
I assume your collation on the database table 'city' is utf8_general_ci And also check the columns of the database table 'city' to ensure they are also utf8_general_ci. The database, its tables, and the tables individual columns can all have a different collation. Last, ensure that you set utf8 for your client connection as well. http://dev.mysql.com/doc/refman/5.0/en/charset-connection.html -RG Ovidiu Farauanu wrote: Hello. I have a problem with queries on a utf8 table. For example when I send select SQL_CALC_FOUND_ROWS * from city where country_code = 'pl' and language_code = 'en' and ( city_name like 'A%' or city_name like 'A% ) order by city_name to MySQL, it returns to me not only cities that begin with capital letter A but also with latin capital letter A with ring above, with diaeresis, with acute and etc. I don't want that. I will query it to do this. Please help. How so query to get only cities that begin with A and only those that begin with A ?? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- 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: Need help creating query statement
You guys are correct, that is exactly what happened. I must thing of this in the future. At this moment I have a lot of other projects to take care, that it is eaiser for me to read the information into an associative array with the columns and the values and sort the array and then print the top 5 values within each array. Thanks, Nestor :-) On 11/6/07, Enrique Sanchez Vela [EMAIL PROTECTED] wrote: --- [EMAIL PROTECTED] wrote: Néstor wrote: I think you'd best begin by normalising your database. Something along these lines: very true indeed, that would save you major headaches when right after finishing the demo, someone would say, can we add a 18 gallon pledge? what about a 25? as of the sort of columns per row, I believe it is not possible nor in the goals of MySQL to make it possible/easy. best regards, enrique. -- What you have been obliged to discover by yourself leaves a path in your mind which you can use again when the need arises.--G. C. Lichtenberg http://themathcircle.org/ __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: privileges headache
Baron Schwartz wrote: hi, [EMAIL PROTECTED] wrote: GRANT SELECT, INSERT, UPDATE ON the_database.* - TO [EMAIL PROTECTED] IDENTIFIED BY 'the_password'; Query OK, 0 rows affected (0.00 sec) FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) That statement does (at least) two things to mysql.user: 1) if there's no row in mysql.user for [EMAIL PROTECTED], it creates it 2) if there's an existing row, it updates its password. IT DOES NOT AFFECT ANY OTHER COLUMN IN mysql.user. But it does insert or update a row in mysql.db. Check there. You are confused on the difference between global privileges, which are stored in mysql.user, and database-level privileges, which are stored in mysql.db and which you are changing with this statement. Right, I'd forgotten about the privs in db. Select_priv: Y Insert_priv: Y Update_priv: Y But I still cannot update any tables as this user. Probably because you have a database-level or table-level privilege that overrides the global privileges you are granting. But this is the thing: why wouldn't the GRANT statement have affected the privs immediately? The changes only showed up after I'd restarted the server (in my 2nd msg) which, judging by the manual, should only happen if i don't issue a FLUSH PRIVILEGES (which I did do). In any case, the problem has been resolved, if not understood. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: privileges headache
[EMAIL PROTECTED] wrote: Baron Schwartz wrote: hi, [EMAIL PROTECTED] wrote: GRANT SELECT, INSERT, UPDATE ON the_database.* - TO [EMAIL PROTECTED] IDENTIFIED BY 'the_password'; Query OK, 0 rows affected (0.00 sec) FLUSH PRIVILEGES; Query OK, 0 rows affected (0.00 sec) That statement does (at least) two things to mysql.user: 1) if there's no row in mysql.user for [EMAIL PROTECTED], it creates it 2) if there's an existing row, it updates its password. IT DOES NOT AFFECT ANY OTHER COLUMN IN mysql.user. But it does insert or update a row in mysql.db. Check there. You are confused on the difference between global privileges, which are stored in mysql.user, and database-level privileges, which are stored in mysql.db and which you are changing with this statement. Right, I'd forgotten about the privs in db. Select_priv: Y Insert_priv: Y Update_priv: Y But I still cannot update any tables as this user. Probably because you have a database-level or table-level privilege that overrides the global privileges you are granting. But this is the thing: why wouldn't the GRANT statement have affected the privs immediately? The changes only showed up after I'd restarted the server (in my 2nd msg) which, judging by the manual, should only happen if i don't issue a FLUSH PRIVILEGES (which I did do). A couple of possibilities: a bug in MySQL, or something you did at the time and have now forgotten... SHOW GRANTS is always a good thing to check when there are weird privilege problems. Do it as the user whom you're trying to grant privileges to, because you can be surprised by the results otherwise. Sounds like it's too late to understand what happened, though. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock Does the file exist? You say this is the sock file created by mysqld: srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Ali Nebi wrote: On Tue, 2007-11-06 at 09:32 -0600, [EMAIL PROTECTED] wrote: i believe you need to have root create the file /var/lib/mysql/mysql.sock and then make it readable and writable by the user that owns mysql. You can make that same file owner by the user that runs mysql. -Original Message- From: Ali Nebi [EMAIL PROTECTED] Sent: Nov 6, 2007 9:24 AM To: mysql@lists.mysql.com Subject: Please help to solve a serios problem Hi, i need of help for a serios problem. We have installed mysql 5 and we are using InnoDB engine. OS is CentOS 5, x86. Our problem is this. We get this message in the log: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (111) The problem is that mysql make recovering the database, we are restarting whole server and it crash again. It continue to crash. We tried to recover manually database, but it continue to crash. What we can do to solve this problem? We need to solve this problem urgently. Thanks in advanced! Regards, Ali Nebi! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] This socket file is created automatically when i run mysql by this way: /etc/init.d/mysql start. The file has these permissions set. I think this is ok, problem is something different i think, but i don't know where. srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Please help to solve a serios problem
- Original Message - From: Russell E Glaue [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, November 06, 2007 8:55 PM Subject: Re: Please help to solve a serios problem The mysql.sock location '/var/lib/mysql/mysql.sock' attempted to be used by your connecting client may be wrong. linux$ ls -la /var/lib/mysql/mysql.sock srwxrwxrwx mysql mysql system_u:object_r:mysqld_var_run_t mysql.sock So your client would need to connect like so: linux$ mysql --socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p What OS are you running MySQL on? -RG Thanks for the reply. Does the file exist? Yes, i see that the file exist. You say this is the sock file created by mysqld: when mysqld daemon is stopped the mysql.sock file not exist, when we run /etc/init.d/mysqd (this is mysql.server), it start mysql_safe and create mysql.sock under /var/lib/mysql/. linux$ mysql -- socket=system_u:object_r:mysqld_var_run_t mysql.sock --user=user -p We don't need to use socket option because in /etc/my.cnf we have set where is mysql.sock file. By this way when we start mysql server, mysql client already know where is mysql socket. I suppose this is correct, if not please correct me. We are using CentOS 5, 64 bits version. Also this: system_u:object_r:mysqld_var_run_t. It is not need to be set in sock=, because these are SELinux permissions, this is different. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Corrupting MySQL on purpose for testing
Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Thanks, Rich(ard) -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupting MySQL on purpose for testing
At 02:18 PM 11/6/2007, Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Sure, power off the computer or throw the breakers when doing updates to simulate a power failure. That's sure to work. :) Mike -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doubt
HI *sql in datawarehousing* i was using the ETL tool INFORMATICA - version 6.2 . and i am creating a TARGET TABLE manually, in oracle database. using the option Targets create. created the table with generation options like create ,not null , drop table . and added all the columns. and saved it. then i did rename the table and when i viewed the data in the renamed table, its gone. only structure is available but no data . what shld i do not to loose the data next time and also how can i getback the lost data in the above case. thanks in advance.
Re: Corrupting MySQL on purpose for testing
On 11/6/07, mos [EMAIL PROTECTED] wrote: At 02:18 PM 11/6/2007, Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Well, there are different types of corruption. Here are a few that come to mind: a)Corruption of a MySQL file, perhaps not repairable (disk hardware problem, OS crash, server power supply failure). b)Corruption in a way that is repairable by a MySQL utility (index file corrupted). c)Application corruption (bad application doesn't handle mutual exclusion correctly, has logical errors, etc.). Hopefully database repair utilities that come with MySQL will map (a) and (b) to be indistinguishable from (c). (But I don't know that--I'm barely a MySQL user.) The most even-handed way to do it would seem to be: a)Write a program that will create a random number of distributed and block corruptions distributed randomly in the MySQL files. (Such a program would take very little time to execute--well under half a second, I'm guessing.) You might want to throw in truncations and blocks added at the end of files, too. b)See how far the MySQL utilities (if there are any) followed by your software will go towards repair. Doesn't seem like an easy thing to test.
Re: Account Problems. Can't register server with new account
Thanks Russell for your help. My user 'usuario1' did have the host='localhost' asigned. It was kind of weird I couldn't enter back then. But today, days after and not even entering the server ... I got in with user=usuario1 ! [EMAIL PROTECTED] bin]# ./mysql -u usuario1 -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 4 to server version: 5.0.27-max Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show databases; ++ | Database | ++ | information_schema | | pruebaCluster | | test | ++ 3 rows in set (0.01 sec) I wonder why (I'm using MySQL Cluster). Russell E Glaue wrote: Run this SQL Query mysql select Host,User from user; Make sure the output has this record values: +---+--+ | Host | User | +---+--+ | localhost | usuario1 | +---+--+ If your mysql error message were to say this: Access denied for user 'usuario1'@'me-me-me.com' The record values need to look like this: +--+--+ | Host | User | +--+--+ | me-me-me.com | usuario1 | +--+--+ Or Access denied for user 'usuario1'@'192.168.20.5' +--+--+ | Host | User | +--+--+ | 192.168.20.5 | usuario1 | +--+--+ To Update: mysql update user set Host=localhost where User='usuario1' LIMIT 1; -RG AlejandraB wrote: Good evening. I have mysql cluster installed and I'm trying to start the mysql server with a new user account recently created, and I'm having trouble accessing the server with the new account. [EMAIL PROTECTED] bin]# ./mysql -u usuario1 -p Enter password: ERROR 1045 (28000): Access denied for user 'usuario1'@'localhost' (using password: YES) The new user does exists and I know the problem is not password related. mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select User from user; +--+ | User | +--+ | mysql| | root | | usuario1 | | | | mysql| | root | +--+ Thanks in advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- View this message in context: http://www.nabble.com/Account-Problems.-Can%27t-register-server-with-new-account-tf4744425.html#a13616486 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Corrupting MySQL on purpose for testing
Hi David, Thanks for your reply. This helped greatly. Yeah, I supposed I could just randomly flip bits in the MySQL files and that would do the trick. The program I'm writing does first attempt to repair the tables with MySQL's utilities if they're MyISAM but InnoDB tables don't support repair and this program is actually meant to be a potential stop-gap solution in the event of hardware errors in a non-failover situation which can do really horrible things like corrupt InnoDB inodes thus rendering tables unusable to keep clients seeing SOMETHING (even if it's stale data) until the hardware can be swapped out. Speaking of which, can anyone think of a reason that InnoDB tables would corrupt aside from hardware problems or a bug in the storage engine? Thanks, Rich(ard) On Nov 6, 2007 6:04 PM, David T. Ashley [EMAIL PROTECTED] wrote: On 11/6/07, mos [EMAIL PROTECTED] wrote: At 02:18 PM 11/6/2007, Richard Edward Horner wrote: Hi, I'm working on a program that will run in the event that tables are crashed or corrupt. Can anyone recommend a good method for crashing my tables or corrupting them so I have some test cases? I'm mostly interested in MyISAM and InnoDB table types. I was thinking I would just start an infinite loop with an UPDATE statement in it and then kill -9 the mysqld process. Any better ideas? Well, there are different types of corruption. Here are a few that come to mind: a)Corruption of a MySQL file, perhaps not repairable (disk hardware problem, OS crash, server power supply failure). b)Corruption in a way that is repairable by a MySQL utility (index file corrupted). c)Application corruption (bad application doesn't handle mutual exclusion correctly, has logical errors, etc.). Hopefully database repair utilities that come with MySQL will map (a) and (b) to be indistinguishable from (c). (But I don't know that--I'm barely a MySQL user.) The most even-handed way to do it would seem to be: a)Write a program that will create a random number of distributed and block corruptions distributed randomly in the MySQL files. (Such a program would take very little time to execute--well under half a second, I'm guessing.) You might want to throw in truncations and blocks added at the end of files, too. b)See how far the MySQL utilities (if there are any) followed by your software will go towards repair. Doesn't seem like an easy thing to test. -- Richard Edward Horner Engineer / Composer / Electric Guitar Virtuoso [EMAIL PROTECTED] http://richhorner.com - updated June 28th -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Virtual Hosting Privileges
On Nov 4, 2007 3:19 PM, David Campbell [EMAIL PROTECTED] wrote: Grant Peel wrote: Hi all, This is my first post to this list so bear with me if I ramble on a bit. I have been reading as much as I can on this subject and am yet to feel confident about an answer so I thought I would throw it at this list and see what comes back. One thing I should mention up front is I am in no way a SQL power user, although, I have enough reference material that I can work my way through most problems. We run a small web hosting and design service and have been offering MySQL as part of the basic setup for quite a while without any problems. One of the things we offer it PHPMyAdmin which has server us well so far. Our clients seem to use it as a browsing tool more that anything, but there are a few that might be called 'Power Users that use it for more SQL intense purposes. (FYI, I am using the PMA user, who only has 'select' privileges to the mysql users table). Recently, I noticed something that caused me to reflect on the privileges that I currently have setup on the server, and was wondering if I could get some feedback and/or some light tutoring on the topic of privileges. It is perhaps prudent to start with some setup info: FreeBSD 6.2 - Not using jails. MySQL 4.1 PHP 4.4 - Soon to be upgraded to 5.x Apache 2.2 PHPMyAdmin 2.11.0 What I am looking for at the end of this discussion is if my current privileges are secure enough, and, give all users the maximum functionality within the security model. All mysql users are alos unix users. When I setup the accounts, I use scripts to setup the mysql user at the same time the UNIX users is setup. That having been said, Users can not log into a shell of the operating system, the unix user is setup to give ftp access, and access to thier back end admin panel. When a new mysql user is setup: 1. the user is assinged a password but is given no privileges in the mysql 'users' table, but is allowed to connect from localhost so thier scripts can run; sampleuser localhost XXX None 2. his/her blank database is setup, and that user is given access to is from localhost, although I have a few users that have requested, and were granted connections (via the 'hosts' table) remote access. sampleuser_database sampleuser localhost Select | Insert | Update | Delete | Create | Drop | Grant | References | Index | Alter I suspect that I want to remove the 'Grant' access from the privileges above. Here is where my confusion escelates If i remove it, can the user still add tables? I suspect that I should consider removing the 'Drop' privilege. If I do that will the user be able to drop tables that he/she created? i.e. ones that reside in thier own database? Hopefully, you all can see my confusion. At the end of the day, I gues the question is: What privileges can I grant each user that will only allow them to completely manange thier own database without allowing them access to others databases? or What priveleges should I grant users at setup time? There is no way I'd have a setup like this with other users databases in the same mysql instance. I'd use openzv or similar to virtualise each account. Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] I think we are looking for something more like GRANT ALL on theuserdatabase.* to [EMAIL PROTECTED] IDENTIFIED BY 'whatever' The 'localhost' is just a guess.. substitute the network name of you web server as seen from the database. This will give each user 100% control of their own db except for having no privilege to GRANT, and no control over anyone else. I have used this plan for years without issue. -- - 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]
load data infile - fails to load my db2 del (ascii) file
hi all, right now i'm trying to migrate from db2 running under linux to mysql v5.1. i manage to export out the db2 structure data into a del (ascii) file. but when i try to load the data from the del file to mysql table, it generate an error. below is the load data infile syntax i use = LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'; and below is the sample layout of del file (in the above case is TABLE01.del) = 1,0,2007-07-31 00:25:12 2,0,2007-07-31 14:09:00 3,0,2007-07-31 00:00:00 4,0,2007-07-31 00:00:00 5,0,2007-07-31 00:00:00 and below is the TABLE01 structure = FieldName, Type, Null, Primary -- MY_TABLEID, int(11), NO, PRIMARY KEY CNTS, int(11), NO, NOT PRIMARY KEY INSERT_DATE, datetime, NO, PRIMARY KEY the error i encounter is = ERROR 1292 (22007): Incorrect datetime value: '2007-07-31 00:25:12. i do some trick by trying to remove the qoute sign from 2007-07-31 00:25:12 so the del file become = 1,0,2007-07-31 00:25:12 2,0,2007-07-31 14:09:00 3,0,2007-07-31 00:00:00 4,0,2007-07-31 00:00:00 5,0,2007-07-31 00:00:00 And i do load data infile again with same command = LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n'; and it was successful. to me it is very strange, since i can do a sql insert using 2007-07-31 00:25:12 for example: insert into TABLE01 values (11, 22, 2007-07-31 00:25:12) successfully without any error!!! in fact, when i use a MySQL Administrator tools, and i export out the data from TABLE01 into CSV, then i found that the structure is exactly the same with del file generated by db2. do i make any mistake?? or do i need to initialize something before i call LOAD DATA INFILE 'C:\\Migration\\del\\TABLE01.del' INTO TABLE TABLE01 FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '' LINES TERMINATED BY '\n' ...? Any help is appreciated. Thank you. Regards, Lanes -- View this message in context: http://www.nabble.com/load-data-infile---fails-to-load-my-db2-del-%28ascii%29-file-tf4762331.html#a13620281 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql connection problems
Hello members, I have two mysqld run on the same host (redhat linux OS with 2.4 kernel). the two mysqld are in different versions, one is 4.0.20,another is 5.0.45. the mysql 4.0.20 uses /etc/my.cnf as its config file,listening on default 3306 port. the mysql 5.0.45 uses /etc/mysql5.cnf as its config file,listening on 3307 port. I start them on command line: /usr/local/mysql/bin/mysqld_safe # for mysql4 /opt/mysql5/bin/mysqld_safe --defaults-file=/etc/mysql5.cnf # for mysql5 All run fine.I didn't see exceptions in mysql's error logs. But, when I try to connect to mysql5, with the command, mysql -uroot -P3307 -h127.0.0.1 Sometime I login it successfully,but most time I can't. The connection seems be blocked. (I don't run any iptables or firewall on this host). This let me really be confused. please help. Thanks! --Ken National Bingo Night. Play along for the chance to win $10,000 every week. Download your gamecard now at Yahoo!7 TV. http://au.blogs.yahoo.com/national-bingo-night/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Trigger problem
Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com
Re: Trigger problem
Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Regarding MySql Service
Dear all, Right Now I am having different kind of problem in mysql server . mysqld service automatically goes off . when we open a website and enter something to search from mysql database it gives the following error - *Warning: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/httpd/html/php/mdp/calendar.php on line 97* *Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2) in /home/httpd/html/php/mdp/calendar.php** on line 97* *Db Error* Kindly tell me where is the problem ..i will be grateful to you ..Thanks in advance .. -- Regards Piyush Joshi 9415414376
pam and crypt using mysql
Hello, sorry if this is not the right mailing list to ask this question, hope someone can point me to a better list in the case. My problem is that I'd like to use mysql as database for storing user accounts for different services, and I started using vsftpd. Now, following the instruction here http://www.howtoforge.com/vsftpd_mysql_debian_etch_p2 I have to configure the pam module using crypt=2 and to store the user password thru the password function. However, this didn't work, and after a lot of trials I found that the only way I can make them working is using crypt=1 and storing password thru the encrypt function. Anyone that can provide me a reason why I'm not able to use the password function? Does it depends on some module I'm missing (with regard to mysql) or to a misconfigured linux server? Thanks, Luca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Trigger problem
No, I didn't set the delimiter. But, it still have an error after I set delimiter in my trigger as your example. By the way, what's delimiter mean ? And what it's for ? Thanks to you Mr. David. David Schneider-Joseph [EMAIL PROTECTED] wrote: Lucky, Did you make sure to set your delimiter before and after the CREATE TRIGGER statement? e.g.: DELIMITER ;; CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END;; DELIMITER ; On Nov 6, 2007, at 11:11 PM, Lucky Wijaya wrote: Hi, my name is Lucky from Indonesia. I build an database application using Delphi 7 MySQL as the RDBMS. Now, I'm having problem in creating trigger in MySQL. Here is the code of the trigger: CREATE TRIGGER DEL_TB_INSTRUKSI AFTER DELETE ON Tb_Instruksi FOR EACH ROW BEGIN DELETE FROM Tb_Stuffing WHERE No_Instruksi = OLD.No_Instruksi; END; It results an error message that the SQL syntax (on delete command) is incorrect. I didn't find yet the incorrect part of my SQL syntax. Could somebody help my problem ? Thank you very much. Note: I'm already using MySQL v. 5.0.41 and using GUI in creating the trigger. I also have tried to create the trigger through mysql command line, but it result the same error message. __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com