Application level security
Hi All, I'm writing a application that needs row level security in it so that only certain users can view/change etc. certain records. I know that MySQL currently does not have row level security, and when I've asked this list before I've been given some helpfull suggestions (although none of them would actually work for me). So I've decided that the only way to go is to put the security logic into the application I am building. This works fine for my application, but means that if anyone connects directly to the server using the MySQL client etc, then they'll be able to see/update everything - making it a pretty weak security system. I've had two thoughts about this, but am willing to hear of any other thoughts people might have. One that I could do right now is to get my program to add something to the password of every user (users are created using the program and passwords can be changed using it too). This way if they try to connect to the server directly they won't have the extra bit on their passwords and it won't let them connect. The program could take care of adding this extra bit each time anything password related was needed so it shouldn't be a problem in this respect, however if anyone discovered the extra bit then it would be imposible to change the extra bit without giving everyone new passwords (which would be a real pain). A second way would be if there was like an application password in the security area that could be set and would be needed for connection - sort of like the ssl extra stuff that's been added recently. The downside of this is it isn't currently there and I'd need to convince someone at MySQL that it was worth adding and then wait for it to be added, the upside is that it would be easier to change if the application password was discovered. I've looked a little at the ssl/encryption stuff to see if I could use that but I don't think it can really help me achieve what I'm trying to achieve but if someone thinks it can I'd love to hear how. If anyone has any other ideas, can see problems that I haven't seen in the above ideas then I'd appreciate knowing. Are there any others having this problem (or is it just me!)? Thanks for your thoughts, cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: UDF, Can anyone please help?
So if below is actually what your data looks like, what about: Select Field1, Field2, MAX(IDField) From MyTable Group by Field1 That might get the correct answer for you, but it does however have an extra field in the result (which may or may not be a problem). cheers, noel filter word:mysql On Tuesday, July 09, 2002 7:03 AM, Miles Roper [SMTP:[EMAIL PROTECTED]] wrote: > Hi Frank, > > Good idea, but I actually need the last value, ie > > IDField Field1 Field2 > 1A1 > 2A4 > 3A2 > 4A3 > 5B7 > 6B10 > 7B3 > 8B8 > 9C1 > 10 C2 > 11 C4 > 12 C1 > > The answer would be > > select field1, last(field2) from table1 group by field1 > > Feild1 Field2 > A3 > B8 > C1 > > Select Field1, Max( Field2 ) From MyTable Group by Field1 > > Would produce a wrong answer > > Feild1 Field2 > A4 > B10 > C4 > > I'm keeping an eye on the mailing list via the net. :o) I get to many > emails as it is :o) > > Hope this makes sense :o) Thanks for you help. > > Cheers > > Miles > - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: can't set a password
Oh forgot to ask in the last post, how are you setting the password in the first place? Are you using a grant statement or inserting values into the table. I reckon the former is better (and safer) but if you're doing the later the you need to "flush privliges" after any changes so that mysql rereads the grant tables. If you can give us the exact line(s) you're putting in then that could help us see the problem (change/mask the password of course). cheers, noel On Tuesday, May 07, 2002 1:44 PM, Noel Clarkson [SMTP:[EMAIL PROTECTED]] wrote: > See that line that says accept any user (%) from any host (%) with password > null. There's your answer. It's a default entry that should be removed (I > think there's stuff in the securing mysql section of the manual about > this). > > cheers > > noel > > On Tuesday, May 07, 2002 1:28 PM, Brandon Metcalf > [SMTP:[EMAIL PROTECTED]] wrote: > > Okay, I'm obviously missing something here. Even though the > > submit_write user has a password set according to the SELECT output > > below, I can't use that password or any password to login as this user. > > Only a null password is accepted. The first attempt I enter the correct > > password; no go. The second attempt I don't enter a password; I'm in. > > > > What gives? > > > > > > mysql> select host,user,password from user mysql; > > +---+--+--+ > > | host | user | password | > > +---+--+--+ > > | localhost | root | 0d7d2b5f41747747 | > > | mccrae| root | 0d7d2b5f41747747 | > > | localhost | | | > > | mccrae| | | > > | % | %| | > > | % | submit_write | 65f6943414e7dad9 | > > | localhost | %| | > > +---+--+--+ > > 7 rows in set (0.01 sec) > > > > mysql> quit > > Bye > > root@mccrae mysql # bin/mysql --user=submit_write --password > > Enter password: > > ERROR 1045: Access denied for user: 'submit_write@localhost' (Using > > password: YES) > > root@mccrae mysql # bin/mysql --user=submit_write --password > > Enter password: > > Welcome to the MySQL monitor. Commands end with ; or \g. > > Your MySQL connection id is 76 to server version: 3.23.48 > > > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > > > mysql> > > > > > > > > Brandon > > -- > > Beware the fury of a patient man. > >--John Dryden > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: can't set a password
See that line that says accept any user (%) from any host (%) with password null. There's your answer. It's a default entry that should be removed (I think there's stuff in the securing mysql section of the manual about this). cheers noel On Tuesday, May 07, 2002 1:28 PM, Brandon Metcalf [SMTP:[EMAIL PROTECTED]] wrote: > Okay, I'm obviously missing something here. Even though the > submit_write user has a password set according to the SELECT output > below, I can't use that password or any password to login as this user. > Only a null password is accepted. The first attempt I enter the correct > password; no go. The second attempt I don't enter a password; I'm in. > > What gives? > > > mysql> select host,user,password from user mysql; > +---+--+--+ > | host | user | password | > +---+--+--+ > | localhost | root | 0d7d2b5f41747747 | > | mccrae| root | 0d7d2b5f41747747 | > | localhost | | | > | mccrae| | | > | % | %| | > | % | submit_write | 65f6943414e7dad9 | > | localhost | %| | > +---+--+--+ > 7 rows in set (0.01 sec) > > mysql> quit > Bye > root@mccrae mysql # bin/mysql --user=submit_write --password > Enter password: > ERROR 1045: Access denied for user: 'submit_write@localhost' (Using > password: YES) > root@mccrae mysql # bin/mysql --user=submit_write --password > Enter password: > Welcome to the MySQL monitor. Commands end with ; or \g. > Your MySQL connection id is 76 to server version: 3.23.48 > > Type 'help;' or '\h' for help. Type '\c' to clear the buffer. > > mysql> > > > > Brandon > -- > Beware the fury of a patient man. >--John Dryden > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Can AUTO_INCREMENT return from INSERT function into a table ?
Hi Son, On Monday, April 08, 2002 2:37 PM, Son Nguyen [SMTP:[EMAIL PROTECTED]] wrote: > > --- BD <[EMAIL PROTECTED]> wrote: > > Son, > > > > At 09:48 PM 4/7/2002, you wrote: > > >mysql> desc threads; > > > >+-+--+--+-+-++ > > >| Field | Type | Null | Key | Default | Extra > > | > > > >+-+--+--+-+-++ > > >| thread_ID | int(11) | | PRI | NULL| auto_increment > > | > > >| subject | varchar(255) | YES | | NULL| > > | > > >| author | varchar(30) | YES | | NULL| > > | > > >| last_by | varchar(30) | YES | | NULL| > > | > > >| views | int(11) | | | 0 | > > | > > >| date_posted | datetime | YES | | NULL| > > | > > > >+-+--+--+-+-++ > > >6 rows in set (0.00 sec) > > > > > > $SQL_insert_thread = "INSERT into threads "; > > > $SQL_insert_thread .= "(subject, author, last_by, views, "; > > > $SQL_insert_thread .= "date_posted) " values "; > > > $SQL_insert_thread .= "(\'some_subject\', \'test_username\', > > > $SQL_insert_thread .= "\'test_username\', 1, now())"; > > > > > > In the table threads above, I do have a field: "thread_ID" with > > >auto_increment value. My question is can I obtain the value > > thread_ID > > >while I do the insert statement just by 1 mySQL statement? If yes, > > >please can somebody give me a sample code? > > > > execute the MySQL query: select last_insert_id(); > > 1st: select last_insert_id() doesn't work > > 2nd: It's not the answer I am looking for ... Because of the race > condition if two users access the that script at the same time... I > will have when I leave the INSERT statement !!! > > Here is what I mean: think about this situation: > > USER 1 ===> INSERT > STOP <== due to process scheduling > > USER 2 ===> INSERT > SELECT last_insert_id(); > > USER 1 ===> SELECT last_insert_id(); > > It's the race condition, I am talking about. > True, this would seem to be the case, but MySQL returns the last_inert_id relative to the user and connection so as long as the user inserts and then selects in the same connection, it doesn't matter what other connections are doing because they are ignored. (I think if you did this in two different connections it would something meaningless anyway). http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html #Reference - search for LAST_INSERT_ID - about halfway down the page. cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: -p password
Hi there, the other comments may have got you where you want but as no one had mentioned your final option thought I'd mention (from /h at the mysql command line) source (\.)Execute a SQL script file. Takes a file name as an argument. so you can run the script from the mysql prompt. cheers, noel On Friday, February 22, 2002 8:41 AM, James Austin [SMTP:[EMAIL PROTECTED]] wrote: > Hi all, > > A novice question: > > How can I do away with having to use the -u user and -p password > arguments every time I execute the mysql command? When I create a table > with a script the only way I can get it to work is with the command: > > $> mysql samp_db -u root -p < create_member.sql > $> Enter password: > > What can I do to not have to enter a user and password each time? Can I > use the script at the mysql prompt? This way I would only have to enter > the user and password once. > > Thanks very much, > Jim > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: row level privilages - thoughts on how to do
Thanks Arjen, seems like a bit of a fiddle but not a bad solution considering some of the other options. Unfortunately I also need the row level locking of innodb so it's not going to work in my case. This lead me to ponder, if this sort of row level protection was added would it be at the mySQL level or the table handler level - ie do I need it in innodb table types or lower than that for it to work? I know this is sort of hypothetical at the moment but if it did get added then at what level it gets added would be of interest. Thanks again for the thoughts, cheers, noel On Wednesday, February 20, 2002 8:47 AM, Arjen Lentz [SMTP:[EMAIL PROTECTED]] wrote: > Hi Noel, > > On Tue, 2002-02-19 at 13:40, [EMAIL PROTECTED] wrote: > > I'm wondering about row level privilages - being able to restrict what > > people see and can update etc on a row level as well as a column/table > > level. > > As others mentioned, having VIEWs wouldn't necessarily be of any help, > since VIEWs are quite restrictive about INSERT/UPDATE type operations on > the view. > > Anyway, after a little pondering, I think I've come up with a fairly > nice solution for this problem. It is MySQL specific though: > Use MyISAM MERGE tables (http://www.mysql.com/doc/M/E/MERGE.html). > > You can define a MERGE table, merging multiple *identical* MyISAM type > tables. It is a bit like a view, in a way. You can do a query on the > merge table, which will internally gather data from all the tables. > > How does this help you? You set up your permissions so that admin tools > have full access to the merge table and all its parts. For individual > users, you only grant access to their own table, to the extent that you > want. > > Some shuffling between tables might be required when a row is for > instance 'transfered' to another user, but I would figure that that is > fairly easy to handle. > > Anyway, this way the plain end-user application can be bound by the user > permissions, there's nothing specific the application needs to do, and > no way for the user to circumvent that system. > > This may not be suitable for every situation, but it might be useful > here > > > Regards, > Arjen. > > -- > Get MySQL Training Worldwide, http://www.mysql.com/training/ >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Arjen G. Lentz <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Technical Writer, Trainer > /_/ /_/\_, /___/\___\_\___/ Brisbane, QLD Australia ><___/ www.mysql.com > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
row level privilages - thoughts on how to do
Hi all, I'm wondering about row level privilages - being able to restrict what people see and can update etc on a row level as well as a column/table level. I know that I can implement it in my program (which is what I'm currently doing) but unless it's in the backend db then every program that is written to work with the data must also include it and it doesn't protect against people using a client to connect directly and make changes or view data they shouldn't see. The only other way I've thought of is to have all of the programs go through middleware sort of thing that implements this and then accesses mysql using it's own login so that no one has direct access to mysql, but it is messy and means that much of the built in security in mysql would need to be duplicated by the middleware, and it would probably be slow etc etc. So, anyone got any ideas about how you'd best go about this? Will triggers/ store proceedures help (or does it need to be in the engine of the database to really work)? Any thoughts appreciated, cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: LIKE work around??
You don't want to use LIKE in mysql - or have you not read about LIKE in the fine mysql manual?? If you use LIKE you will also need to use a % sign signify which bit is the variable bit. This is because mysql allows you to say if the variable bit is at the start or the end or both. LIKE 'banana%' would give you what you want, but it sounds like you should read the manual a bit more. cheers, noel On Friday, February 08, 2002 6:56 AM, Rutledge, Aaron [SMTP:[EMAIL PROTECTED]] wrote: > I am trying to query the database for records that have the same > sequence of starting characters--such as banana1 and banana2 and have a > result set returned that contains everything that starts with banana. > Any way to do this? I think I would use LIKE in other types of > databases, but I was wondering if there was a work around for MySQL. > Thanks, Aaron > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: BOOLEAN Variable
Hi there, My understanding is that there is no BOOL type in MySQL but BOOL is allowed in create table statements (or alter table etc) for compatibility with other databases (but MySQL uses a tinyint for a bool because it doesn't have bool). You can use a tinyint and say that 0 is false 1 is true, or you could define an enum or something. There has been much discussion on this list about which is the best approach - basically depends on what you see as important, you might want to do a search on old list stuff to get some ideas. hope that helps. cheers, noel On Tuesday, October 30, 2001 1:52 PM, Shomaail [SMTP:[EMAIL PROTECTED]] wrote: > I am using MySQL 3.23.22-beta > pls tell is there any way to define boolean variable in this db > what is the BOOL for ? > declaring a var BOOL make it TINYINT(1)! but not boolean > > thanks > Shomaai > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Beginner help with selecting
Ok so SELECT * FROM Events, Djs, EventDjs WHERE Events.eventID = EventDjs.eventID AND Djs.DJID = EventDjs.DJID; should give you a list with the event and the dj etc, if you only want one event then add something like AND Events.Events.eventID = 'x' or whatever. cheers, noel On Tuesday, July 17, 2001 8:06 AM, Technodium.net [SMTP:[EMAIL PROTECTED]] wrote: > Well, let me give the complete details > > Let's say I have one table called events > with fields eventID and eventName > > Then one table called djs > with fields djID and djName > > And finally a table linking them all called eventdjs > with fields called eventID and djID > this table links one event with all the appropriate djs. > > I use the same linking tables for outlets and other infos.. those are many > to many relations. > > Now I want to select all djs from one event, this means taking info from > events and djs table, but using the 3rd table for the link.. that's what I'm > not able to do. > > There is a way to do it with a script that takes the list of djID from the > link table and then makes a new select.. but I'm sure there is a way to do > it at once.. > > I looked in the MySQL book from paul dubois and somewhere it states how to > change subselects into selects for mysql, but I can't acheive my goal. > > Any help appreciated > > Etienne > > > - Original Message - > > From: "Simon Green" <[EMAIL PROTECTED]> > > To: "'Technodium.net'" <[EMAIL PROTECTED]>; > <[EMAIL PROTECTED]> > > Sent: Monday, July 16, 2001 8:56 AM > > Subject: RE: Beginner help with selecting > > > > > > > Ok > > > So you give the event an ID and the DJ and Id > > > > > > Select dj.djid > > > from dj, event > > > where event.eventid = 'x'; > > > > > > I think this should work? > > > Long weekend so if this is not right it should be close! > > > > > > Simon > > > > > > -Original Message- > > > From: Technodium.net [mailto:[EMAIL PROTECTED]] > > > Sent: 16 July 2001 13:41 > > > To: [EMAIL PROTECTED] > > > Subject: Beginner help with selecting > > > > > > > > > Hello there, I would need some help with a select statement for my > > > databases. > > > > > > I've done all the design of the database and I think it's all good, but > I > > > can't make a select as I want to do it. > > > There is the problem: > > > I have one table for the list of events and one for the list of djs for > > this > > > event. > > > I have also one linking table with the list of events keys with their > > > according djs > > > > > > I would like to do a select so that for one event it gives me all the > > djs.. > > > But I'm not able to do it. > > > > > > And after that I'd like to dothe same to be able to get the djs, the > > > outlets, etc in less queries than getting the djs ID from the linking > > table > > > and then doing a new query in the dj table to get the info > > > > > > If you'd like more info please don't hesitate.. > > > > > > This bug is stopping my progress in the DB design and I can't get help > > > anywhere for this > > > > > > Etienne > > > > > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Client Command Line interface "auto" compellation 3.23.39
> > > >interestingly, if I type sh I get 'show ' but if I type show ta I > >don't get 'show tables' which is what I would expect. > > Does "show tables" actually produce a list of tables, or no tables? > On some systems, there is a bug in glibc that prevents it from working > properly, in which case auto-completion won't work. (I have one such > system, and I see the same behavior you do, even if I type "rehash" > to force re-hashing of the names.) > Thanks Paul rehash fixed my problems with it not showing up (I've never had to use rehash before). I was actually getting a list of tables with show tables, now the only thing that doesn't work is the two worded commands where one word is common to multiple commands (eg show tables, show database etc completes the show but then thinks that it is the start of another separate command for the next word - and tables or database aren't commands), but I guess that's a reasonable thing because there might be times when that behaviour is needed. cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Client Command Line interface "auto" compellation 3.23.39
Yep, some things work others don't. if I type sh then I get 'show ' sel give 'select ' but I thought I was able to type select * from tbl and that this used to give me all a list of all the table names that started with tbl (or if there was only one it would just auto complete it of course). Now I just get the mysql commands and none of the nice tablename and fieldname completion that I'm sure used to be there (or was I just wishfully dreaming??). Here's the full list of what I get if I just press tab without entering anything: ? ego notee show fields from alter table exit pager show keys from clear explain print show tables connect goquit source create database help rehashstatus create table insertreplace tee deleteload data fromselectunlock tables drop lock tables set optionupdate edit nopager show databasesuse interestingly, if I type sh I get 'show ' but if I type show ta I don't get 'show tables' which is what I would expect. If there is anything else that I can provide to help sort this out I'd be only too happy to. cheers, noel On Friday, July 13, 2001 9:59 PM, Sinisa Milivojevic [SMTP:[EMAIL PROTECTED]] wrote: > Noel Clarkson writes: > > Hi all, > > > > When using the 3.23.39 client to connect to the server, pressing tab to > > complete table names and field names etc doesn't seem to work. I seem to > > get the mysql commands, but I thought in past clients I was able to get > > table name and field name completion as well. > > > > Have others noticed this or is it just me who's lucky?!? > > > > I'm using the rpm off the mysql site, maxsql is also installed (from rpm) > > and it's on a red hat 6 box. > > > > cheers, > > > > noel > > > > It works for me ... > > Try first to type in some command, then try ... > > -- > Regards, >__ ___ ___ __ > / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic <[EMAIL PROTECTED]> > / /|_/ / // /\ \/ /_/ / /__ MySQL AB, FullTime Developer > /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ><___/ www.mysql.com > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to reduce data by averaging ???
Didn't see a response to this so here's some ideas. You can do an INSERT INTO tablename SELECT ... statement or if you are creating a new table to put these values into CREATE TABLE tablename (SELECT ...). Check the manual for the exact syntax and for which version of mysql you'll need for this to work (especially if you have an older one, I know it has worked for me for a while). hope that helps, cheers, noel On Friday, July 13, 2001 5:45 AM, Nemholt, Jesper Frank [SMTP:[EMAIL PROTECTED]] wrote: > Hi! > > I have various tables more or less similar to this : > > mysql> desc disk; > +--+---+--+-+--- --+- > --+ > | Field| Type | Null | Key | Default | > Extra | > +--+---+--+-+--- --+- > --+ > | timecode | datetime | | | -00-00 00:00:00 | > | > | system_id| smallint(5) unsigned | | MUL | 0 | > | > | devicename | char(8) | | | | > | > | device_id| char(8) | | | | > | > | read_count | mediumint(8) unsigned | YES | | NULL | > | > | read_kb | mediumint(8) unsigned | YES | | NULL | > | > | write_count | mediumint(8) unsigned | YES | | NULL | > | > | write_kb | mediumint(8) unsigned | YES | | NULL | > | > | servicetime | float(4,2)| YES | | NULL | > | > | waittime | float(4,2)| YES | | NULL | > | > | active_queue | float(4,2)| YES | | NULL | > | > | wait_queue | float(4,2)| YES | | NULL | > | > | busy | float(4,2)| YES | | NULL | > | > +--+---+--+-+--- --+- > --+ > 13 rows in set (0.00 sec) > > > Lots of data is inserted into these tables and allways (well, more or less) > later selected with a ordering by timecode. > > I would like to reduce the size of old data by averaging the values by hour > instead of the current where I usually have 12 samples per hour. > The goal is to end up with high resolution data for the last couple of > monts, while older and more unimportant data is averaged by hour. > > If I do a select like this : > > select > substring_index(timecode,':',1),system_id,devicename,device_id,avg(read_ coun > t),avg(read_kb),avg(write_count),avg(write_kb),avg(servicetime),avg(wait time > ),avg(active_queue),avg(wait_queue),avg(busy) from disk where system_id='1' > group by substring_index(timecode,':',1) order by > substring_index(timecode,':',1) > > > ...I will get all the data averaged by hour, but how do I get it back into a > table ??? > In Oracle my guess is that I can do this with a combination of a select and > insert, but I don't think this is supported in MySQL. As far as I know this > is more a less a subselect thing, and MySQL doesn't support subselects. > > Do I need to make some external scripting (Perl with DBI) or can everything > be done inside MySQL ??? > -- > Un saludo / Venlig hilsen / Regards > > Jesper Frank Nemholt > Unix System Manager > Compaq Computer Corporation > > Phone : +34 699 419 171 > E-Mail: [EMAIL PROTECTED] > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Client Command Line interface "auto" compellation 3.23.39
Hi all, When using the 3.23.39 client to connect to the server, pressing tab to complete table names and field names etc doesn't seem to work. I seem to get the mysql commands, but I thought in past clients I was able to get table name and field name completion as well. Have others noticed this or is it just me who's lucky?!? I'm using the rpm off the mysql site, maxsql is also installed (from rpm) and it's on a red hat 6 box. cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql problem
Wouldn't that depend on if the MySQL db is on the same server as the web servers and the scripts that connect to them. It could also depend on if the scripts use the -h option when making a connection and if this is the ip address (as in number) or is a resolvable name. If it is all local, if the grants use an ip number instead of localhost to give the needed permissions then you might want to check that also. I agree if you are using root to connect from web scripts etc you really need to change this. cheers, noel On Tuesday, July 10, 2001 11:34 AM, Rolf Hopkins [SMTP:[EMAIL PROTECTED]] wrote: > Changing your IP address shouldn't affect your MySQL access in that way. I > would say you have done something else to cause those errors. You can look > at skip grants in the manual. From what you have written, your web pages > are accessing the DB as root. That spells D A N G E R. You should create > another user with less access. > > - Original Message - > From: "Kevin Robertson" <[EMAIL PROTECTED]> > To: <[EMAIL PROTECTED]> > Sent: Tuesday, July 10, 2001 1:43 > Subject: mysql problem > > > > I changed IP numbers on the Cobalt RAQ i have mysql on. I no longer have > > access to the databases. I think i have lost access as root. Is there a > > way to restore the root user password and/or change it to something else > > manually? I have many websites with many 'access denied' errors. > > > > > > Any help would be appreciated. > > > > Thanks, > > Kevin Robertson > > > > > > - > > Before posting, please check: > >http://www.mysql.com/manual.php (the manual) > >http://lists.mysql.com/ (the list archive) > > > > To request this thread, e-mail <[EMAIL PROTECTED]> > > To unsubscribe, e-mail > <[EMAIL PROTECTED]> > > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: tables locked
How are you updating in delphi and what is the error? if you are running a query, setting the edit state and request live (either at design or run time) and then updating the data and it complains that someone else has changed the record then check the TQuery settings, there is one that determines whether delphi uses the key field(s) or the entire row to match when updating. Obviously if you match using the entire row (which was the default in my setup) you'll never be able to update the row because it will always have changed and so woun't match (I'm still confussed as to why you'd even have such an option, but one day I might understand). If it is telling you that there is a lock on the table and that is why you can't update then I haven't had that happen but I'd suggest trying to update from another client interface and see if it complains about the table being locked (if you can try one that runs through odbc that might help determine where the problem orrigonates). good luck, cheers, noel On Monday, July 09, 2001 6:59 PM, Patrick Premartin [SMTP:[EMAIL PROTECTED]] wrote: > >> I use Delphi 5 and MyODBC to access a MySQL database. > >> > >> In my program, I've sometimes a tables locked and I don't understand why > >! I am the only user on this database and I change records correctly (as I > >do it for other programs on other databases). > >> > >> Does somebody have this problem ? > >> Can you explain me how to unlock tables and records with MySQL ? > >> Is it a bug in Borland Delphi ou BDE ? Is it a bug in MyODBC ? Is it a > >bug in MySQL ? Is it a bug in my program (I don't see where I could have > >make an error... but it's a possibility) ? > > > > > >You did not included enough information to solve your problem. But I can > >thing we can solve it easily if you give us output of "mysqladmin > >processlist" while the problem exists. I think we can read out the > >problem from there. > > Hi > > Only mine process appears in the list. I think the bug in inside the Borland Database Engine and its parameters. I'll try something and let you know. > > cu9 > -- > Patrick Premartin > contacts perso: [EMAIL PROTECTED], http://www.pprem.net/ > > > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Enhanced MySQL
I read the comments on NuSphere in the portals section: "As long as GEMINI is not released under open source, as required by the GPL license of the MySQL server, we at MySQL AB can't recommend anyone to use this distribution." and I'm left thinking they are in violation of the GPL. If this is the case, I'm sort of suprised that a bigger noise hasn't been made about this. Anyone know any different? Monty, I did try to add this as a comment to the portals area (as suggested) but the link below told me I didn't have the rights to do so. Just thought I'd let you know. cheers, noel > > About NuSphere's distribution, I can only say that I haven't tested it > myself but I have read some good comments about it. On the other hand > as we at MySQL AB have not been consulted when this distribution was > set together and as we haven't yet got any opportunities to test > Gemini we at MySQL AB can't recommend anyone to use this distribution > at this point. > > If anyone has any further comments about the NuSphere distributions, > please add them to the MySQL distributions portal at: > > http://www.mysql.com/portal/distributions/php/item_edit.php?item_id=1 > > Please note that all additions and comments to the portals are > moderated by us at MySQL AB to ensure that no one uses the portals > as medium to flame others (without a good cause). > > Regards, > Monty > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innodb and timestamps
Hi there,
I'm using MaxSQL 3.23.39 and have set up an innodb table but when I run an
update statement the timestamp field does not update automatically. Is
anyone else experiencing this, or is it an expected behaviour of innodb
tables? I couldn't find anything in the manual saying that this was
different for innodb tables. Below is an example of what I did and the
results.
running on Red Hat linux 6.1.
cheers,
noel
mysql> create table tbl_test(name varchar(10), lastedit timestamp)
type=innodb;
Query OK, 0 rows affected (0.04 sec)
mysql> describe tbl_test;
+--+---+--+-+-+---+
| Field| Type | Null | Key | Default | Extra |
+--+---+--+-+-+---+
| name | varchar(10) | YES | | NULL| |
| lastedit | timestamp(14) | YES | | NULL| |
+--+---+--+-+-+---+
2 rows in set (0.01 sec)
mysql> insert into tbl_test(name) values('bob');
Query OK, 1 row affected (0.00 sec)
mysql> insert into tbl_test(name) values('ted');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tbl_test;
+--++
| name | lastedit |
+--++
| bob | 20010613171610 |
| ted | 20010613171618 |
+--++
2 rows in set (0.00 sec)
mysql> update tbl_test set name='fred' where name='ted';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> commit;
Query OK, 0 rows affected (0.03 sec)
mysql> select * from tbl_test;
+--++
| name | lastedit |
+--++
| bob | 20010613171610 |
| fred | 20010613171618 |
+--++
2 rows in set (0.00 sec)
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Auto Increment
I don't know about MySQL 3.22.32 but in 3.23.27 if you create a table with
a primary key of two fields, one of which is an auto increment field then
you will get the behaviour you are looking for (ie the auto inc will start
at 1 for each different value in the other field). I don't have 3.22.x
installed and am not sure if this has been a change in later versions, but
you could give it a go and see what happens (there is an example of what I
did below).
cheers,
noel
mysql> create table testme(a varchar(3) not null, b integer auto_increment
not n
ull, PRIMARY KEY(a,b));
mysql> insert into testme values("hee", null);
mysql> select * from testme
mysql> insert into testme values("hee", null);
mysql> select * from testme;
+-+---+
| a | b |
+-+---+
| hee | 1 |
| hee | 2 |
+-+---+
2 rows in set (0.00 sec)
mysql> insert into testme values("ha", null);
mysql> select * from testme;
+-+---+
| a | b |
+-+---+
| ha | 1 |
| hee | 1 |
| hee | 2 |
+-+---+
3 rows in set (0.01 sec)
On Tuesday, June 12, 2001 11:33 AM, Chris Bolt [SMTP:[EMAIL PROTECTED]]
wrote:
Ver 8.0, Distrib 3.22.32
> > I have a customer file, keyed by an auto-increment customer
> > number. Customers can have orders. The order file is keyed by an
> > auto-increment order sequence number, which works fine, but is not
> > convenient. Rather, I would like the order sequence number to start at
1
> > for each customer, and have two keys on the order file, customer id,
> > followed by order sequence. I would like the database to enforce
> > uniqueness on this key pair.
> >
> > I don't think the built in auto-increment field can support this kind
of
> > mechanism, so - What is a safe, efficient way to find the
> > customers highest
> > order number, increment it by one, and write the new order
> > record? I need
> > to be absolutely sure that if ten people enter a new order all
> > for the same
> > customer at (nearly) the same time, they all get a unique order number.
>
> Create a single unique index on both columns (CREATE UNIQUE INDEX
> ordernumber ON table (customerid, orderid);) and use LOCK TABLES when
> creating the order id.
>
>
> -
> Before posting, please check:
>http://www.mysql.com/manual.php (the manual)
>http://lists.mysql.com/ (the list archive)
>
> To request this thread, e-mail <[EMAIL PROTECTED]>
> To unsubscribe, e-mail
<[EMAIL PROTECTED]>
> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail <[EMAIL PROTECTED]>
To unsubscribe, e-mail <[EMAIL PROTECTED]>
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: So, just how long should it take the "make install" to run?
I've done it on a 486 with 64Mb memory and it took about an hour or an hour and a half or so (it was a long time and I went off and did other things while it was happening so my timing is not precise (oh that's make time for the main part, make install much quicker) - and it was a few versions ago - 3.23.25 about. cheers, noel On Wednesday, February 21, 2001 8:41 AM, Matthew P. Marino [SMTP:[EMAIL PROTECTED]] wrote: > Don't laugh, > > Intel PI 166MHZ, 32MB RAM,10GB HD > FreeBSD4 > MySQL3.23.33 from .tar.gz distribution > > - > Before posting, please check: >http://www.mysql.com/manual.php (the manual) >http://lists.mysql.com/ (the list archive) > > To request this thread, e-mail <[EMAIL PROTECTED]> > To unsubscribe, e-mail <[EMAIL PROTECTED]> > Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
running in a chroot environment
Hi all, I'm wanting to set up mysql to run in a chroot jail. I saw the command line option --chroot and tried this which seemed to work (well mysql ran anyway). I added this to the startup script (running redhat 7.0) in /etc/rc.d/init.d/mysql so that it would use this option (and because I need to server to start automatically after a reboot) and thought I'd try it out. I created a new database, but it appeared outside the chroot jail in /var/lib/mysql which is what the datadir is set to but I thought that if running in a chroot jail then it should go to the one inside the chroot not one outside. I then set the chroot up by copying all of the bits from the rpm distribution into the chroot environment, altered the startup script and tried to run it, but it complains about not having touch, sed etc (which I haven't put in the chroot - the less the better). Now, it might not be a problem to copy all of the relevant bits into the chroot environment, but this little experience has got me wondering what is actually happening when I use the --chroot command line option to start the mysql server? At what level are things in the chroot (and what bits are not), or is it more likely that my setup is not correct and that when using the --chroot command switch it isn't actually running properly? I'm running Red Hat Linux v 7.0, MySQL 3.23.31 installed from rpms, any help would be much appreciated. cheers, noel - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail <[EMAIL PROTECTED]> To unsubscribe, e-mail <[EMAIL PROTECTED]> Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
