NULL or NOT NULL in relational design
Hi, I am often running into this type of design, but would it be a good design. Normally you design tables related to each other and having e.g. parameter tables like a country-table holding country names with their country-id's; and you will find these country-ids back in other tables, which have the foreign key country_id related to the primary key country_id. (voila). Would it still be a good design if you would except NULL values in a related field which hold the foreign key; because it could be possible that nobody would enter or select a country. The trouble would be i guess, consistency; because you now have a value (NULL) which is not present in the country table. The country_id is an example, but you can guess there would be more examples you can think of. If I am not clear enough please let me know; and I can be more specific. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: doc for beginner admin
How about a start in the dev.mysql.com and browse for documentation. Start with the differences of all kinds of installations ;-) and using the [.]my.cnf file[s]. Cheers, Danny Bayrouni wrote: Hello, I would like to understand the mysql administration, is there any good doc for beginners. Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Lock issue on insert
Hi john, When did you start getting this error message? There aren't any trouble with MyIsam tables? Danny Brittingham, John wrote: They are InnoDB and max_write_lock_count=4294967295. The same thing happens when I create a copy of the table. -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Saturday, December 31, 2005 2:59 AM To: Brittingham, John; mysql@lists.mysql.com Subject: Re: Table Lock issue on insert Hi John, What kind of engine are you using on your table? MyIsam or InnoDB or are you using merged tables? If you query your system variables what is your max_write_lock_count? If you create a copy of the table: mysqlcreate table cp1 like USERS_PER_HOUR; and you try the insert again; is the error gone? Danny Brittingham, John wrote: I am having trouble with table lock. The query is as follows: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; I keep getting this error: #1206 - The total number of locks exceeds the lock table size How do I fix this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Lock issue on insert
Hi John, I have looked around a bit and you might be interested in this part: InnoDB: Do not intentionally crash mysqld if the buffer pool is exhausted by the lock table; return error 1206 instead ... check this link: http://dev.mysql.com/doc/refman/4.1/en/news-4-1-8.html it is quite interesting and i am trying to get this error on my mysql databases by changing the bufferpool and inserting a lot of data; Best regards, Danny Brittingham, John wrote: They are InnoDB and max_write_lock_count=4294967295. The same thing happens when I create a copy of the table. -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Saturday, December 31, 2005 2:59 AM To: Brittingham, John; mysql@lists.mysql.com Subject: Re: Table Lock issue on insert Hi John, What kind of engine are you using on your table? MyIsam or InnoDB or are you using merged tables? If you query your system variables what is your max_write_lock_count? If you create a copy of the table: mysqlcreate table cp1 like USERS_PER_HOUR; and you try the insert again; is the error gone? Danny Brittingham, John wrote: I am having trouble with table lock. The query is as follows: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; I keep getting this error: #1206 - The total number of locks exceeds the lock table size How do I fix this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table Lock issue on insert
Hi John, What kind of engine are you using on your table? MyIsam or InnoDB or are you using merged tables? If you query your system variables what is your max_write_lock_count? If you create a copy of the table: mysqlcreate table cp1 like USERS_PER_HOUR; and you try the insert again; is the error gone? Danny Brittingham, John wrote: I am having trouble with table lock. The query is as follows: INSERT INTO USERS_PER_HOUR SELECT count( DISTINCT ( CUID ) ),`TV_LOG_DATE`,`TV_LOG_TIME`, INTERFACE_ID FROM `TV_LOG_ALL` group by 2,3; I keep getting this error: #1206 - The total number of locks exceeds the lock table size How do I fix this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to insert CURDATE() as default
Marc, In MySql (I am using 4.1.9 and 4.1.15; so i am not sure about 5) it is not possible to use functions as default values; you could create: create table tester (f_date date default curdate()); But this doesn;t work. You have to struggle through your knoda how to present the current date. Be sure you have your field datatype set to DATE. Create your form and set the datasource to the table having the date-field. Create your textbox and assign the field to it. Put the %NOWDATE% in the 'default value' field of your textbox. The currentdate will be shown after you run the form. Hope this little info helps you :-) Danny Marc wrote: I forgot to mention - I'm running MySQL 4.0.14 on Linux. And I'm not very knowledgeable on databases. I just use MySQL with Knoda to get the job done. == I've got a MySQL table that I'd like to have the current date, CURDATE(), as the default in a column. I'm using knoda to worj with this table. How do I use knoda to get this done? I can enter CURDATE() in the default using the GridColumns button, but all that does is insert the phrase CURDATE(). I've got the column with Date for the ColummnType. Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: backslash and Update
Hi, What you perhaps could use is the REGEXP usage in your where clause. Try this: update name set first_name=replace(first_name, '\\', '') where first_name regexp ''; The fun thing is that when you put '\\' instead of the '' after the regexp function it doesn't work. But this sure does. Before you use the update, create a selection first with the regexp function; if that works, your update will do fine. Hope this little info helps you :-) Danny Jerry Swanson wrote: I have 290 records in the database with backslashes. I want to remove the backslashes. Why the query below doesn't remove backslashes? update name set first_name = REPLACE(first_name,'','') where first_name like '%%'; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: STOPPING server from pid file
Hi, Did you check your error log file? Is there allready a pid file in the directory? You have to delete this one first. Hope this helps :-) Danny ali asghar torabi parizy wrote: hi to all i have mysql5.1 installed on fedoracore3 it was worked sucsessfully since yesterday. but when i run mysql_safe script today, it prompt following error: STOPPING server from pid file /usr/local/mysql/var/localhost.localdomain.pid please help me. i tierd.i think that i have to leave fc3 and work with better platform that be compatible with mysql. what is your suggestions? - Yahoo! Photos Ring in the New Year with Photo Calendars. Add photos, events, holidays, whatever. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key with constant?
Hi jesse, You can only set the reference in your constraint: ... ADD FOREIGN KEY (id) references table(id) ... In this case you will get an inconsistent database. Dont reference the two tables Campers and Counselers with the ActivitySelections table, but use two tables in which you put the references: - CampersActivitySelections - CounselorsActivitySelections These tables will have the keys from Campers and Counselors and the ActivitySelections table. The matter is perhaps that you can have the same Activity for both Campers and Counselers, than you would have a problem in the old situation. As a db schema: Campers - CampersActivitySelections - ActivitySelections Counselors - CounselorsActivitySelections - ActivitySelections In your selection you can use the many-to-many tables to create your selection-output. You won't need a Type field in this case and your ActivitySelections holds the data pure for this perpose. Hope this small info will help you on your way :-) Best regards, Danny Jesse wrote: I need to be able to add a foreign key that will allow a constant is possible. I have a table named Campers that has a field named ID. I also have another table called Counselors that has a field named ID as well. There is a detailed table called ActivitySelections that I use for both Campers and Counselors. In ActivitySelections, the field PersonID holds the ID value from either Camper or Counselor, and I've got another field named Type. Type='C' where we're dealing with a Camper, and it holds 'O' where we're dealing with a Counselor. I tried the following, but got an error: ALTER TABLE activityselections DROP FOREIGN KEY FK_Campers, ADD CONSTRAINT FK_Campers FOREIGN KEY FK_Campers (PersonID, Type) REFERENCES campers (ID, 'C') ON DELETE CASCADE ON UPDATE CASCADE; Is what I'm trying to do possible, or do I need to go back to the drawing board, or do this manually? Thanks, Jesse -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySqld wll not start on Whitebox linux
Hi Charles, Did you create the MySql system tables by running the script mysql_install_db which is located in the scripts folder? This script will create the system tables for your mysql database. After creation you can run the mysqld_safe as root Login using mysql as user root , leave the password blanc. mysql -u root -p -h localhost or point to the socket: mysql -u root -p -S /tmp/mysql.sock Be sure you have created a special user like mysql and a group mysql. Change your file permissions on your mysql installation directory; best is to change it to: root:mysql (user:group) and change the permissions on the data directory to: mysql:mysql. Hopes this small description will help you on your way to run a complete mysql database :-) Danny Charles Gambrell wrote: I am running Whitebox linux 4 and have MySql 4 installed. When I run the mysqld_safe file, mysql starts then immediately stops. It does give me the message that it is starting with databases from /var/lib/mysql In the mysqld.log file I see - [ERROR] /user/libexec/mysqld: Can't fine file: './mysql/host.frm (errno: 13) [ERROR] Fatal error: Can't open privilege tables: Can't find file: './mysql/host.frm' errno: 13) The file host.frm is in the directory /var/lib/mysql/mysql/ I am logged in as root when I run mysqld_safe. I have tried it as with the same result. This is my first attempt to run mysql on a linux server and I am hopeful for a few instructions from a more experienced user. Thank you for any help. Charles -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL hangs each relative poor time
Well Roberto, it could be anything... analyzing your my.cnf file and variables would be looking through spagetti (i like spagetti ... but not in this flavour). the time your database hangs, are the intervals random minutes/seconds/hours? or is there a constant interval like precise on 30 minutes the mysql database hangs. perhaps a trial and error method should do the trick? it could be your disk quota? perhaps a process which kills the database? perhaps first run the database using defaults. keep the my.cnf file as small as you can. build a new mysql-version; and run this one, see if it reacts the same as your database. can you tell me the mysql\s values? i am just giving you some hints, to analyse the problem using trial and error. i am sorry i can't give you the full solluton; because too many factors can cause the error. Danny Roberto Rodriguez Garrido wrote: MySQL hangs every 30 minutes, it start to make a big process and when I make a show variables I get that values: +-+-+ | Variable_name | Value | +-+-+ | back_log| 50 | | basedir | /usr/ | | binlog_cache_size | 32768 | | bulk_insert_buffer_size | 8388608 | | character_set_client| latin1 | | character_set_connection| latin1 | | character_set_database | latin1 | | character_set_results | latin1 | | character_set_server| latin1 | | character_set_system| utf8| | character_sets_dir | /usr/share/mysql/charsets/ | | collation_connection| latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server| latin1_swedish_ci | | concurrent_insert | ON | | connect_timeout | 10 | | datadir | /var/lib/mysql/ | | date_format | %Y-%m-%d| | datetime_format | %Y-%m-%d %H:%i:%s | | default_week_format | 0 | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000| | expire_logs_days| 0 | | flush | OFF | | flush_time | 0 | | ft_boolean_syntax | + -()~*:| | | ft_max_word_len | 84 | | ft_min_word_len | 4 | | ft_query_expansion_limit| 20 | | ft_stopword_file| (built-in) | | group_concat_max_len| 1024| | have_archive| YES | | have_bdb| NO | | have_compress | YES | | have_crypt | YES | | have_csv| YES | | have_example_engine | NO | | have_geometry | YES | | have_innodb | YES | | have_isam | YES | | have_ndbcluster | DISABLED| | have_openssl| NO | | have_query_cache| YES | | have_raid | YES | | have_rtree_keys | YES | | have_symlink| YES | | init_connect| | | init_file | | | init_slave | | | innodb_additional_mem_pool_size | 1048576 | | innodb_autoextend_increment | 8 | | innodb_buffer_pool_awe_mem_mb | 0 | | innodb_buffer_pool_size | 8388608 | | innodb_data_file_path | ibdata1:10M:autoextend | | innodb_data_home_dir| | | innodb_fast_shutdown
connect with myODBC to mysql through proxy
Hi all, I have a windows computer in my network which needs an odbc connection through proxy. the only thing is that i don't know how to do this on a windows machine. i have installed the myodbc driver and it works for connecting on a mysql server in my local network. but i have an isp address, the database name. connecting without proxy, that is directly, works fine; but i need to make a connection behind a proxy. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Recover from plain text log
Hi, I was wondering if you have solved your problem. I am quite interested excually. Best regards, Danny Todd Ellison wrote: OK, I am ready to get flamed. I have done so much stuff wrong on this MySQL server that it finally came back to bite me. I've learned my lesson and hopefully someone here will find it in their heart to help me. I have a database which is updated quite often, running from a web server. There is no binary log. There is, however, a text query log going back to before my problem. The server had an I/O error and apparently corrupted the files for my database. I have a backup of the database from some time back, but need to recover the data since that time. I know there is a way to parse binary logs and recover from them, but is there a way to do the same for text logs? I think I can write a bash script to reformat the file into a series of queries, but I was hoping there was an easier way. Thanks so much, anyone willing to help. Todd Ellison -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
Well I haven't realy found a select method in which you can isolate a field. Like a complementary method, in which you select like one field, but shows the fields except the field which you have used in your select-statement. So you excually want to dynamically insert the records, not knowing how many fields you excually have; excluding the auto-numbering field. Wouldn't it be better to use PHP or another API in which you retrieve the fields and create an SQL statement using these variables and having the knowledge of creating the sql-statement? You could also try to automate an export and use the load datafile to import the information; but then again you have to rewrite the datafile. Best regards, Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: insert into... select... duplicate key
I am not familiour with Cold Fusion but: cant you use 'show columns from table' ?? and use the result object? This normally works in e.g. C or PHP danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: I am using Cold Fusion and as I stated in my original message, if I were using MySQL 5, then I could use information_schema to retrieve the column names in the table and do it with variables in Cold Fusion. I do that on all my pages on the MySQL 5 servers with which I work. However, the server I am working with currently is MySQL 4 and I am unaware of any way to retrieve the column names from a table in MySQL 4. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Well I haven't realy found a select method in which you can isolate a field. Like a complementary method, in which you select like one field, but shows the fields except the field which you have used in your select-statement. So you excually want to dynamically insert the records, not knowing how many fields you excually have; excluding the auto-numbering field. Wouldn't it be better to use PHP or another API in which you retrieve the fields and create an SQL statement using these variables and having the knowledge of creating the sql-statement? You could also try to automate an export and use the load datafile to import the information; but then again you have to rewrite the datafile. Best regards, Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: That is the effect that I am looking for, but exactly the method that I am trying to avoid. If I type the column names into my INSERT... SELECT and someone later adds a column to the table, I would have to go back into my program and update the statement. I am looking for a way to do it dynamically in order to avoid maintenance of the statement in my program later. Rob Danny Stolle [mailto:[EMAIL PROTECTED] wrote: Hi, I am hoping you meen this: You have to use the fields in your into -statement and select statement, not including the field having the auto-numbering so if e.g. field1 has autonumbering - insert into table1 (field2, field3) select (field2, field3) from table1; autonumbering will automatticaly be applied :-) Danny Schimmel LCpl Robert B (GCE 2nd Intel Bn Web Master) wrote: Here is the problem that I am having. I am trying to make a copy of a full record in a table that has a primary key with auto-increment. The real problem is that I want the statement to use SELECT * so that if columns ever get added to the table the statement will still work for the full record. I know that I can use the information_schema to do this in MySQL 5, but the server I am currently work with is MySQL 4. Basically, I am looking for a way to select all of the columns in a record except one, so that the auto-incrementing primary key will automatically insert itself. Of course, if anyone has any other suggestions for a work around, that would be good, too. Rob Schimmel 2d Intel bn USMC -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_hex_string() c-api
Hello all, can somebody explain to me the c-api mysql_hex_string(). when i debug my example in DDD i get the full sql-dml (insert) as a hex-value; which this function returns. putting a 0x in front of the hex and use it in a mysql_real_query() returns an error. or am i doing it wrong! should i use the values which are used in the insert-string and convert them into hex using this function? the example provided in the documentation is not realy helpfull though. hopefully i am clear, otherwise please let me know. Thank you. Danny Stolle Netherlands. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql_hex_string() c-api
I thought it was something like that ... I just found it strange allready. I'll give it a try, thanx. Danny Paul DuBois wrote: At 19:43 +0200 8/16/05, Danny Stolle wrote: Hello all, can somebody explain to me the c-api mysql_hex_string(). when i debug my example in DDD i get the full sql-dml (insert) as a hex-value; which this function returns. putting a 0x in front of the hex and use it in a mysql_real_query() returns an error. or am i doing it wrong! should i use the values which are used in the insert-string and convert them into hex using this function? the example provided in the documentation is not realy helpfull though. hopefully i am clear, otherwise please let me know. It take an argument string such as abc and returns it as a string representing the hex digits for the characters in the string. Input: abc Output: 616263 You should take that result and insert it into a SQL statement where a data value is allowable, preceded by 0x. It's not intended that you pass mysql_hex_string() a complete SQL statement. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Good Oracle Forum
Daniel Cummings wrote: This forum has been very helpful. There is always somebody that is willing to respond to queries. Does anybody know of a good Oracle forum? TIA Dan Dan, You could try http://www.lazydba.com/ Best regards, Danny Stolle Netherlands. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create an Innodb database ?
Darryl Hoar wrote: I am running MySql 3.23.53. I am developing a multi-user application in delphi using the Zeoslib controls. Since it is going to be a multi-user application, should I use Innodb ? If so, are there any good tutorial/references for the Innodb ? thanks, Darryl Hi Darryl, I am wondering my self about the question when to use innodb instead of myisam; because this engine is set to default. You need to check first if your MySql supports the InnoDB engine by invoking: SHOW ENGINES; I have read in the O'reilly book that you can overwrite the default myISAM engine by setting the mysqld-option: --default-storage-engine=type similair to --default-table-type=type in your .my.cnf file. So now also my question: When to use innodb instead of myisam? What performace advantages does this engine have? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql forgets user passwords
Michael Stassen wrote: Danny Stolle wrote: Chris Fonnesbeck wrote: I have mysql 4.1.12 installed on OSX 10.4, and have run into the curious problem that mysql forgets my user password (but not my root password) when I restart the server. When I attempt to log in, I get: ERROR 1045 (28000): Access denied for user 'chris'@'localhost' (using password: YES) Yet, when I go in as root and re-grant permissions with the user password, access is restored. When I reboot, I get the error again. What could possibly be causing this? Thanks, Chris Fonnesbeck Sorry again ... forgot the mailing group ... Did you 'flush privileges'? Danny Stolle Netherlands Danny, First, FLUSH PRIVILEGES is not needed with GRANT. Second, if he were editing the user table instead of using GRANT and failing to FLUSH PRIVILEGES, he would get the opposite behavior -- the login would not work before the restart, but would work after. Chris, One possibility is a startup script which is altering the user table. Another possibility is some error in granting permissions or restarting the server, or logging in. It is difficult to say without knowing more. Please show us * the GRANT command you use to create 'chris'@'localhost' (but don't show us the real password) * the output of SHOW GRANTS FOR 'chris'@'localhost' when it is working (before a restart). * the method you use to restart the server * the output of SHOW GRANTS FOR 'chris'@'localhost' when it isn't working (after the restart). Michael Aah I get the picture, thanx Michael. Danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General-list comment
Is there a problem with the mysql-general list? I haven't had any messages? Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doubt on MySQL for Linux...
Ashok Kumar wrote: Hi friends, I'm now started to using the Redhat-Linux. Now i want to start MySQL on Linux. I'm not having much idea about Linux. I'm new to this. For Linux which MySQL installation i've to choose from the site. there are lot of binary distributions, i don't which one is compatible for my OS. Linux and H/W specification of my system is as follows. 1. Redhat Linux 9 2. Intel PIII And i also want to know abt how to install and configure MySQL for using that in 'C'. Pls guide me in this. Thanks and Regards, Ashok Kumar.P.S. Yahoo! Sports Rekindle the Rivalries. Sign up for Fantasy Football http://football.fantasysports.yahoo.com Ashok, I suggest that you carefully read the documentation on http://dev.mysql.com/doc/mysql/en/index.html Here you can find the installation program and tutorials to help you get through MySql If you are new to Linux, then perhaps reading some stuff about linux first. Good luck and enjoy :^) Best Regards, Danny Stolle Netherlands EmoeSoft (http://www.emoesoft.nl) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlshow question
Berman, Mikhail wrote: Hi Danny, Thank you for helping me here. Yes, I am aware of need to have the space between --host=xxx and --user=xxx, and command I am executing actually has a space. It looks like you believe that my problems related to security of remote logon. That is something, I am going to investigate more closely. My mysql command hangs, too. But, I have not done complete research on all its variations and has not done complete investigation on remote security. Best, Mikhail Berman -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Wednesday, June 22, 2005 4:47 PM To: Berman, Mikhail; mysql@lists.mysql.com Subject: Re: mysqlshow question Berman, Mikhail wrote: Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman Hi Berman, sorry if i might ask some simple questions ... but can you logon remotely at all(?), using the client tools like: - mysql -u username -ppassword -h host -D database - mysqladmin -u username -ppassword -h host flush-tables are you granted for a remote logon? does your mysql-client tools hang as well? the statement show a no-space between '--host=xxx--user=xxx' it should be '--host=xxx --user=xxx' or are you aware of that? Best Regards, Danny Stolle Netherlands Hi Berman, i am not sure if it IS a security matter. What version are you running? mysql \s mysql mysql Ver 14.7 Distrib 4.1.9, for pc-linux-gnu (i686) mysql ... it could be that you are running a firewall, so the port is blocked and the client is still waiting for an answer (network problems hint)? if it excually was a security matter, you would get error messages, like 'Access denied ... ' or what ever. 'Sorry for the mishap for the space between the statement.' Try to log: use a my.cnf and put inside some logging under the [mysqld] section, like: log-error = location/error.log or locate where you have put your log-files, by: mysqlshow variables like log% Danny Stolle Netherlands ps. If you have solved the problem, please let us know so we can learn from it. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: strange database grant to mysql database
Harald Falkenberg wrote: Hallo, after creating a user via the grant command, I found that this new user can see by the 'show database' command the mysql database although the grant does not apply to it. This new user can run the 'use mysql' command, but has no access to the tables. The db table only show access grants to non mysql databases. What has went wrong here and how can I solve the problem? mysql version 4.0.16. regards Harald Hi Harald, that is quite easy. Using the grant command sets the privileges in the user table (which is one of the security tables). read the info at: http://dev.mysql.com/doc/mysql/en/privilege-system.html to understand the privilege system in MySql. you have to revoke all privileges in the user table and insert a record into the db table where you can define the database you want the user to be granted on. Best Regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlshow question
Berman, Mikhail wrote: Hi everyone, My /usr/local/mysql/bin/mysqlshow --host=xxx--user=xxx --password= command hangs. I can ping host from local machine I can locally execute mysqlshow command on the host. I can locally execute mysqlshow command on remote server My environment is UNIX with MySQL 4.1.xx installed on both local server and host. Any help is greatly appreciated, Mikhail Berman Hi Berman, sorry if i might ask some simple questions ... but can you logon remotely at all(?), using the client tools like: - mysql -u username -ppassword -h host -D database - mysqladmin -u username -ppassword -h host flush-tables are you granted for a remote logon? does your mysql-client tools hang as well? the statement show a no-space between '--host=xxx--user=xxx' it should be '--host=xxx --user=xxx' or are you aware of that? Best Regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: help joining tables in a query
James M. Gonzalez wrote: -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: 15 June 2005 21:09 To: James M. Gonzalez Subject: Re: help joining tables in a query James M. Gonzalez wrote: Greetings, I'm facing a difficult query at the moment. I have tried many different queries but still not get the desired result. My case: Tables: shipped ( id, shipdate, sn); undelivered (id, undate, sn); return (id, redate, sn); I need the following output: DATE SHIPMENTS UNDELIVERED RETURNS Fri 1 may 2005 87 11 4 Sat 2 may 2005 82 17 5 ........ ... So far, Im have succesfulyl manage to get the result using just one table: DATE SHIPMENTS Fri 1 may 2005 87 Sat 2 may 2005 82 ..... With the query: SELECT DATE_FORMAT(shipdate,'%a %e %b %Y'), COUNT(shipdate) FROM shipped WHERE (YEAR(shipdate) = 2004) AND (MONTH(shipdate) = 05) GROUP BY shipdate ORDER BY shipdate DESC However, joining the 3 tables and getting the right results is being a nightmare. I have already tried different left and right joins, with no success. Please any help, hints, or light on the query will be greately appreciated. James. James, please check your table design. i can't find any relational keys, like a primarry key and a foreign key (or would that be the 'sn' as in 'serial number'?). if the tables can't relate to each other it is not possible to get a reliable resultset. Danny Stolle Netherlands -- Thanks a lot for your reply!! Actually yes, sn means serial numbers. No, there are no foreign keys, so you are right, it is not possible to join tables without keys. (The primary keys in the three are the id; however I think Im not writing it with the standard notation) On the other hand, the 3 tables got a date field. So I should be able to link them using this field, shouldn't I? I mean, the 4th of June 2005, there are an X amount of rows on each table that has this date in their date field don't they? shipped ( id, shipdate, sn); 1 2005-06-03 400250 2 2005-06-04 400251 3 2005-06-04 400252 4 2005-06-04 400253 5 2005-06-04 400254 6 2005-06-05 400255 undelivered (id, undate, sn); 1 2005-06-03 400220 2 2005-06-03 400218 3 2005-06-04 400223 4 2005-06-04 400229 5 2005-06-04 400211 6 2005-06-05 400235 return (id, redate, sn); 1 2005-06-03 400160 2 2005-06-03 400168 3 2005-06-03 400153 4 2005-06-04 400219 5 2005-06-04 400221 6 2005-06-05 400230 So, I should be able to get this result: Date | shipped | undelivered | return 2005-06-04 | 4 | 3 |2 Because of the common date field. Do you think this is possible? I really need to make this sql work, I have spend a lot of time on the internet and I cant make it work, any further help would be greately appreciated! Thanks a lot. James. Dublin, Ireland. hi james, well i realy would consider your design, but give this one a try: just use an inner join for this one. text: on a certain date you want the amount of shipped, undelivered and returns. i'll take the date from shipped as the leading date: select s.date, count(s.id), count(u.id), count(r.id) from shipped s, undelivered u, return r where s.date=u.date or s.date=r.date group by s.date; i would use a different column than of date (for example sdate), because date is a datatype. the table 'return' i would rename to returned (return is a reserved word as well). returned (id, sdate) undelivered (id, sdate) shipped (id, sdate) your query would look like: select s.sdate, count(s.id),count(r.id),count(u.id) from shipped s, returned r, undelivered u where s.sdate=r.sdate and s.sdate=u.sdate group by s.sdate; Best Regards, Danny Stolle Netherlands EmoeSoft -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie mysql.sock question
Paul Mitchell wrote:
On Tue, 14 Jun 2005, Danny Stolle wrote:
if i start my mysqld it creates a mysql.sock because of an option in
my.cnf: socket=/tmp/mysql.sock
when i `mysqladmin -u root shutdown` the database, the mysql.sock is
removed.
does your system errors when starting mysqld?
Hello Danny,
Thanks for the info. With it, I've gotten considerably further down
the road before my next blow out. I've relocated mysql to
/usr/local/mysql, and then made /usr/local/mysql/tmp my data directory,
as I was getting errors that my local account mysql didn't have
permission to create ./ibdata1.
Once that was accomplisghed, the following appeared to accomplish the db
creation:
./mysqld_safe --user=mysql --datadir=/usr/local/mysql/tmp
Now I have the following in my /usr/local/mysql/tmp directory:
ls -l
total 41014
-rw-rw 1 mysqlOther 2870 Jun 16 13:29 hazy.err
-rw-rw 1 mysqlnobody 5242880 Jun 16 13:29 ib_logfile0
-rw-rw 1 mysqlnobody 5242880 Jun 16 13:22 ib_logfile1
-rw-rw 1 mysqlnobody 10485760 Jun 16 13:27 ibdata1
significantly, the hazy.err file contains:
050616 13:29:26 mysqld started
050616 13:29:26 InnoDB: Database was not shut down normally!
InnoDB: Starting crash recovery.
InnoDB: Reading tablespace information from the .ibd files...
InnoDB: Restoring possible half-written data pages from the doublewrite
InnoDB: buffer...
050616 13:29:26 InnoDB: Starting log scan based on checkpoint at
InnoDB: log sequence number 0 43634.
InnoDB: Doing recovery: scanned up to log sequence number 0 43634
050616 13:29:26 InnoDB: Flushing modified pages from the buffer pool...
050616 13:29:26 InnoDB: Started; log sequence number 0 43634
050616 13:29:26 [ERROR] Fatal error: Can't open privilege tables: Table
'mysql.host' doesn't exist
050616 13:29:26 mysqld ended
So far, I haven't found anything on the net which corrects this
situation, though the error shows up with pretty good regularity.
I made sure that I had a datadir definition in /etc/my.cnf:
datadir=/usr/local/mysql/tmp
but that doesn't seem to have corrected the problem.
Paul
==
Paul Mitchell
email: [EMAIL PROTECTED]
phone: (919) 962-9778
office: I have an office, room 14, Phillips Hall
==
hello Paul,
i have had the same trouble for a long time, because i moved the data
directory and mysql wasn't able to find the system tables also i ran the
mysql_install_db in the wrong way.
i have created an install script which only works on suse linux, because
of its user and group creation. but i did the following thing for
installation:
- first creating a user and group named as mysql
- than installing mysql by binaries - i copied the dirs/files into
/etc/mysql/prodocut/{version}/
- creating the soft-link in /usr/local
- than running the script mysql_install_db - not in the directory
script, but an upper level so i run: ./scripts/mysql_install_db
after that i assigned the user permissions: user root and group mysql on
the install directory. user mysql group mysql on the datadir.
i have used this my.cnf as start up - please change if you'd like.
[client]
host=localhost
port=3306
socket=/tmp/mysql.sock
user=could be root
password=your password
[mysql]
line-numbers
table
tee=/tmp/mysql-all.log
prompt=([EMAIL PROTECTED]) [\\d]\\_
[mysqld]
big-tables
bind-address=mysql-server ip-address
core-file
datadir=your data directory
flush
lower_case_table_names=1
log-bin={dir}/mysql/bin.log
log-bin-index={dir}/bin-index.log
log-isam={dir}/mysql/isam.log
log-update={dir}/mysql/update.log
log-slow-queries={dir}/slow-queries.log
log-long-format
low-priority-updates
log-error={dir}/error.log
skip-locking
port=3306
socket=/tmp/mysql.sock
temp-pool
tmpdir=/tmp
user=would be mysql
lower_case_table_names=1
if changing your datadir, be sure to copy the entire data directory
which was created by mysql after running the script and assign the
proper permissions. (create it first than copy it to the desired location.)
running the script 'mysql_install_db' needed to be done directly in the
main directory of mysql and not in the script directory it self. (run
./scripts/mysql_install_db)
Best Regards,
Danny Stolle
Netherlands
EmoeSoft
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and specifying DEFAULT
Les Schaffer wrote: Gordon Bruce wrote: I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows upgrading to 5.0.6 solved the problem. do i need to let MySQL developers know about this or do they monitor the list or once a release is gone, i can assume THIS problem was fixed? thanks to all for the fast help. i am new to the mysql list and i can see it works. les schaffer Les, i don't know if you have managed to solve your problem. it took a while but i asked my brother to try it on his mysql which is on a windows platform. the result was that he also got the nulls after ID_streettype_spec_hosp. he is working with version 5.0 mysql server. this has got to be a bug, or there must be something else going on. Danny Stolle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management: conclusion
Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands Thanx everybody for sharing some cool information on User Management. I have read some cool ideas and arguments on the options which i suggested. I guess there is no best or a most prefered method on user management. The method that is being used is the most suitable in the environment the database is used and the database administrator's prefered working method offcourse. But i must conclude that by reading the messages carefully the most prefered method was option 3: creating multiple user IDs for each task that the user would perform on the database. Creating these roles and assigning the user(s) to this specific role (=RBAC). The advantage is that you can create custom roles for specific tasks and communicate these roles to the users who will use them. You would also get a small amount of connections which can leed to high performance tuning from your MySql database. Still it would take a lot of time consuming thinking when you design and configure your database in dealing with User Management. Hope you all don't mind if i come up with more topic discussions like this one. I must say it is a real mind breaker. Hope you enjoyed this discussion; please feel free to continue. I shall share my opinion on your comments. Best Regards, Danny Stolle EmoeSoft, Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
discuss: user management
hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Hi Kevin, yes it is a complex matter, i agree completely. but how would you plan this as a dba or the person involved on administrating MySql. For instance: You would choose option 2 as the preferable one. But what would you do if somebody would change its role or that the person would get other privileges? he will get a new or already created role userID, but would still be able to logon using the previous user id. why wouldn't you choose for the 3th option or 1st option? what disadvantages do you think would option 1 and 3 have? Best regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, Although my experience with MySQL user management is limited to just maintaining a handful of users, I find it rather overly-complex because of the need to maintain a table of users and 'from where' they can have access, and to what databases they can have access to. For example, I just installed MySQL Administrator on my laptop and then I had to add rows allowing me to access MySQL from my laptop. The ODBC connection setup should suffice. For every instance of MySQL, you have to have an entry in the user table for every user from every access point. Then multiply that by the number of databases in each instance and you can see that administration of the users can get out of hand. If I had to choose between the 3 methods listed below, I would choose #2 if there was a large number of roles and users. I would definitely stay away from option #3 no matter what. HTH. Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 10:08 AM To: mysql@lists.mysql.com Subject: [SPAM] - discuss: user management - Bayesian Filter detected spam hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Hi Kevin, i started this discussion to find out, how most database administrators or users involved in managing MySql, would deal with a topic as User Management. So the question(s) is(are) more hypothetical, e.g. What if (...) 'you would have a development site and an accounting site' how would you plan your user management? I like the way you state your opinion on User Management and the examples you give. It is not so that i would stick on these options, if there are other ideas, please let us discuss them. but if you have given some examples, i would like to give an example on the 3th option: it is not so that you have to create a user with these prefixes (_dev, _arch); why not having departmentnames as userID's or perhaps fantasynames as userID's (which could be uses as role names). your question on the role-part: 'why would somebody create roles?' is an interesting question. i have no direct answer to this question. the only thing i would come up with is: when you have a lot of tables and you have to change a privilege on several tables. you have the choice for changing that for 40 users each or 5 roles each. Best Regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, I would stay away from option 3 for exactly the example you provided. You have 1 user with 2 roles. What if you had 30 users with 2 roles? I would choose option 2 because I would only have to maintain 2 users in MySQL, not 60 as you would in option 3. For option 1, you would have 30 users, but then you would to give them the 'most permissible' privileges of the 2 roles. What I don't know is why you need to have roles in the first place. Do you have a large number of users and a large number of roles? Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 11:12 AM To: [EMAIL PROTECTED]; mysql@lists.mysql.com Subject: Re: discuss: user management Hi Kevin, yes it is a complex matter, i agree completely. but how would you plan this as a dba or the person involved on administrating MySql. For instance: You would choose option 2 as the preferable one. But what would you do if somebody would change its role or that the person would get other privileges? he will get a new or already created role userID, but would still be able to logon using the previous user id. why wouldn't you choose for the 3th option or 1st option? what disadvantages do you think would option 1 and 3 have? Best regards, Danny Stolle EmoeSoft, Netherlands Kevin Struckhoff wrote: Danny, Although my experience with MySQL user management is limited to just maintaining a handful of users, I find it rather overly-complex because of the need to maintain a table of users and 'from where' they can have access, and to what databases they can have access to. For example, I just installed MySQL Administrator on my laptop and then I had to add rows allowing me to access MySQL from my laptop. The ODBC connection setup should suffice. For every instance of MySQL, you have to have an entry in the user table for every user from every access point. Then multiply that by the number of databases in each instance and you can see that administration of the users can get out of hand. If I had to choose between the 3 methods listed below, I would choose #2 if there was a large number of roles and users. I would definitely stay away from option #3 no matter what. HTH. Kevin Struckhoff Customer Analytics Mgr. NewRoads West Office 818.253.3819 Fax 818.834.8843 [EMAIL PROTECTED] -Original Message- From: Danny Stolle [mailto:[EMAIL PROTECTED] Sent: Tuesday, June 14, 2005 10:08 AM To: mysql@lists.mysql.com Subject: [SPAM] - discuss: user management - Bayesian Filter detected spam hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list
Re: discuss: user management
George, from a MySql point of view, how would you deal with security on a site? would you than create individual users? Best Regards, Danny Stolle EmoeSoft, Netherlands [EMAIL PROTECTED] wrote: Danny, My 2 cents (American dollars): From a security standpoint (if you are a financial institution) you'd would never get away with #2 because there is no audit trail with multiple users using a generic user id. The MySQL implementation of security is not implemented with auditing and standard role based assignments in mind. I am hopeful that they will correct this issue down the road. Regards, George Danny Stolle [EMAIL PROTECTED] wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] __ Switch to Netscape Internet Service. As low as $9.95 a month -- Sign up today at http://isp.netscape.com/register Netscape. Just the Net You Need. New! Netscape Toolbar for Internet Explorer Search from anywhere on the Web and block those annoying pop-ups. Download now at http://channels.netscape.com/ns/search/install.jsp -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: discuss: user management
Peter, that is an interesting formulation which you gave. can you please comment that with an example? would you create roles for users who are assigned the same privileges and individual users who have multiple roles? Best Regards, Danny Stolle Netherlands Peter Brawley wrote: Danny /1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). / #2 has a name (role-based user access, RBAC) and is widely used, but its formulation above needs a correction: create roles, and users who can be assigned different and possibly multiple roles. PB - Danny Stolle wrote: hi, i would like to discuss 'user management' in mysql. Working with Oracle you can assign users to roles giving them privileges provided by that role. MySql doesn't have Roles. I have read (Managing and Using MySql, O'Reilly) 3 options on managing users having multiple roles in a MySql environment: 1. Giving the user a Single user ID and assign the privileges to that user ID 2. Create role-bases users and have different people share the same user ID for a given role. 3. Create multiple user IDs for each role played by each user (dannys_arch as an architect, dannys_dev as a developer). Which of these 3 options is the most preferable one or are there more options which you can use. What are the advantages and disadvantages on working with one of these 3 options? how do you handle hostnames when working with random ip-addresses on your site. Or just plain simple (or stupid) what are your experiences on user management in a MySql environment. Best regards, Danny Stolle Netherlands No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.323 / Virus Database: 267.7.2 - Release Date: 6/14/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbie mysql.sock question
Paul Mitchell wrote: Hello All, I'm trying to get mediawiki working on a solaris 9 domain (this is on a Sun 15k, FWIW). I had mysql installed and in a fit of stupidity, managed to remove /tmp/mysql.sock. After scouring the Usenet via google, and searching through the archives at lists.mysql.com, I'm still in a quandry as to how to recreate this pointer. First off, there is no /var/lib directory, so it wasn't pointing there (as was pointed to in one google hit). Actually, there's no mysql.sock anywhere on this system (as far as I can tell). So I've re-configured, re-compiled and re-installed mysql-4.1.12 but it still does not create the pointer to mysql.sock. I'm enough of a mysql newbie (though a solaris oldbee) to be uncertain how to communicate with this service (I thought that if the socket wasn't there, it would use tcp/ip). Goes to show what I know! Any pointers on how to recreate /tmp.mysql.sock, and what it should be pointing to will be a great help. Thanks in advance for any help, Paul Mitchell == Paul Mitchell email: [EMAIL PROTECTED] phone: (919) 962-9778 office: I have an office, room 14, Phillips Hall == Paul, the installation of a mysql on a solaris must not be different than an installation on a linux distrib (which i did). so if you would create a my.cnf file in your /etc directory with several [mysqld] options. did you create a symbolic link mysql in your /usr/local pointing to your mysql-install-dir? if i start my mysqld it creates a mysql.sock because of an option in my.cnf: socket=/tmp/mysql.sock when i `mysqladmin -u root shutdown` the database, the mysql.sock is removed. does your system errors when starting mysqld? Best Regards, Danny Stolle EmoeSoft, Netherlands -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and specifying DEFAULT
Les Schaffer wrote: Gordon Bruce wrote: I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows i was running 5.0.4 on Windows: mysql \s -- mysql Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32) switching to 5.0.6 now. anyone wanna make a bet ;-) les schaffer euh .. would that be on a windows platform ;^) ?? i am running the 14.7 on a linux distro and i have no trouble since ... danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CREATE TABLE and specifying DEFAULT
Les Schaffer wrote: Gordon Bruce wrote: I just ran the creates on 4.0.20 on LINUX and 5.0.6 on Windows i was running 5.0.4 on Windows: mysql \s -- mysql Ver 14.9 Distrib 5.0.4-beta, for Win32 (ia32) switching to 5.0.6 now. anyone wanna make a bet ;-) les schaffer euh .. would that be on a windows platform ;^) ?? i am running the 14.7 on a linux distro and i have no trouble since ... danny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
