replicate-ignore(d)-table continues to replicate
Hi, We are using the binary distribution of mysql for solaris 9 64bit on 2 machines that are replicating to each other for redundancy: mysql-standard-4.0.14-sun-solaris2.9-sparc-64bit I am successfully able use replicate-ignore-db=blah to stop replication on a database, however when I specify individual tables to ignore the slave thread continues to replicate the tables completely oblivious to the ignore statement. I have so far tried to get the slave to ignore nearly every table in the target database to no effect. Only replicate-do-db replicate-ignore-db work Here is a sample of my.cnf [mysqld] port= 3306 socket = /tmp/mysql.sock log = /var/log/mysql/mysqld.log log-error = /var/log/mysql/mysqld-error.log log-slow-queries = /var/log/mysql/mysqld-slow.log datadir = /var/lib/mysql skip-locking key_buffer = 256M max_allowed_packet = 100M table_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M net_buffer_length = 8K myisam_sort_buffer_size = 64M thread_cache = 8 thread_concurrency = 4 query_cache_size = 16M log-bin server-id = 2 master-host = somehost.somedomain master-user = replicator log-bin log-warnings log-slave-updates replicate-ignore-db=mysql replicate-ignore-table=radius.Accouting replicate-ignore-table=radius.RADAUTHLOG replicate-ignore-table=radius.RADSTATSLOGns1 replicate-ignore-table=radius.RADSTATSLOGns2 replicate-do-table=radius.raduser replicate-do-table=radius.RADONLINE replicate-do-table=radius.RADPOOL Cheers Lee Webb -- Systems Administrator DOT Communications [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql and large size dbs
Hi, We are considering using mySql or postgreSql for an app that handles large volume of data. Somehow, it appears (mostly from the mailing lists) that mySql does not handle large volumes of data very well (crashes, db corruptions, etc). Would ppl who use mySql for large volumes of data share their experience about number of tables, avg/max number of records in a tables, db size, time that it takes to dump/restore db, potential problems. Thanks in advance __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Hi guys, Just wanted to say that I think I remember a recent commit to the source tree to correct a problem or problems with max_user_connections counting. I think one of them was a race condition if 2 threads tried to increase/decrease the counter at once. But it seems like that would rarely happen for you guys unless you're getting tons of connections/second. I'm pretty sure there was another issue that was throwing the count off. I guess you'll have to see if it's fixed in the next release (4.0.17). Sorry I can't remember more details. Matt - Original Message - From: Joe Lewis Sent: Wednesday, November 12, 2003 11:41 AM Subject: Re: max_user_connections problem after upgrading We're experiencing the same issues, but not neccesarily after an upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're getting max'd connections only on specific users, and the show processlist is returning only the show processlist process. the results of netstat show absolutely nothing. What I think is happening is the connections are not properly getting closed. The users are allowed to connect after a flush user_resources is run. Is there a bug in the particular version of MySQL (4.0.12) where the user connections are not getting decremented when a connection is closed? Joe Henrik Skotth wrote: I'm mostly using mytop, and that's the way that I see that there are no (are almost no) connections when the server claims that it is above the connection limit... So I guess that there's something seriously wrong then... Any ideas what? -- Henrik [EMAIL PROTECTED] (Pete Harlan) skrev: What does show processlist say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: Hi, Have you tried netstat -a on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
multiple values in one column
Hi Using MySql 3.23 Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with Regards John Berman [EMAIL PROTECTED]
Innodb: Can't init databases
Hi, I removed idbdata and iblogs and recreated idbdata and iblogs. But I keeps getting Can't init databases. I still have a lot of disk space. Could any one please tell me what's wrong? Here are message from error log InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files because InnoDB: data files are corrupt, or new data files were InnoDB: created when the database was started previous InnoDB: time but the database was not shut down InnoDB: normally after that. 031112 23:44:05 Can't init databases 031112 23:44:05 Aborting Thank you for your help! Hsiu-Hui -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb: Can't init databases
Hi Hsiu-Hui, I havent followed the threat, excuse if this was mentioned before. Did you try to start the server with the --skip-innodb option yet ? -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ On Thursday 13 November 2003 17:38, Hsiu-Hui Tseng wrote: Hi, I removed idbdata and iblogs and recreated idbdata and iblogs. But I keeps getting Can't init databases. I still have a lot of disk space. Could any one please tell me what's wrong? Here are message from error log InnoDB: Database physically writes the file full: wait... InnoDB: Progress in MB: 100 InnoDB: Cannot initialize created log files because InnoDB: data files are corrupt, or new data files were InnoDB: created when the database was started previous InnoDB: time but the database was not shut down InnoDB: normally after that. 031112 23:44:05 Can't init databases 031112 23:44:05 Aborting Thank you for your help! Hsiu-Hui --- -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Synchronization and replication of two MYSQL databases
Dear friends We have our application with data tier as mysql database in our office running on windows platform which we need to synchronize with the same database on a remote system at data centre on Linux. Awaiting your reply at the earliest, Thanks Regards, Prashant A
Urgent problem
Hi I did something stupid: I locked myself out of the MySQL server... My problem: I use MySQLFront to change the allowed hosts and did it for all users. They were 'localhost' and I set them to 'localhost; EcolMod1' where the EcolMod1 is the name of my computer where the MySQL server is installed on. After these changes, I couldn't log on any more. Windows 2000, MySQL 4.012. Any help welcome what I could do to access the data again, Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent problem
Hi I did something very stupid: I locked myself out of the MySQL server (all accounts, incl administrator)... My problem: I use MySQLFront to change the allowed hosts and did it for all users. They were 'localhost' and I set them to 'localhost; EcolMod1' where the EcolMod1 is the name of my computer where the MySQL server is installed on. After these changes, I couldn't log on any more. Windows 2000, MySQL 4.012. Any help welcome what I could do to access the data again, Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Urgent problem
I did something very stupid: I locked myself out of the MySQL server (all accounts, incl administrator)... My problem: I use MySQLFront to change the allowed hosts and did it for all users. They were 'localhost' and I set them to 'localhost; EcolMod1' where the EcolMod1 is the name of my computer where the MySQL server is installed on. After these changes, I couldn't log on any more. Windows 2000, MySQL 4.012. Any help welcome what I could do to access the data again, One mail should be enough in most cases! You may try to edit your host.mid file in \mysql-dir\data\mysql. Make a backup of that file before you start and use a hex-editor for doing so. The first chars in there should look like ÿlocalhost; EcolMod1. Simply overwrite (not erase) what you don't need with spaces (20 hex). You need to restart mysql to apply changes made. Regards, TomH -- PROSOFT EDV-Lösungen GmbH Co. KG Ladehofstraße 28, D-93049 Regensburg Geschäftsführer: Axel-Wilhelm Wegmann AG Regensburg HRA 6608 USt.183/68311 www : http://www.proSoft-Edv.de email: [EMAIL PROTECTED] phone: +49 941 / 78 88 7 - 121 fax : +49 941 / 78 88 7 - 20 cellphone: +49 174 / 41 94 97 0 -- -Original Message- From: Rainer M. Krug [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 10:03 AM To: [EMAIL PROTECTED] Subject: Urgent problem Hi Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=mysql-list@ 7thweb.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Creating random data in a test table based on an existing tab le
Hi, I tried it in 4.0 and got the desired result - but at least the select part should not depend on version, as long as it's recent. The select should return all combinations of first and last names available in the source table ordered by random to be more interesting. You can try that by itself to see what it returns. If the select gives more rows you'll have to find some other way of inserting the data into the test table. The only way it should return only one row is if you have only one row in the source data, if you have two rows it should be four lines returned, and so on. MvH, Luis Lebron wrote: Thanks for the help. I tried your suggestion but only got 1 row inserted in the test table. Luis -Original Message- From: Olof Tjerngren [mailto:[EMAIL PROTECTED] Sent: Tuesday, November 11, 2003 3:01 AM To: Luis Lebron Cc: Mysql (E-mail) Subject: Re: Creating random data in a test table based on an existing table How about somethine like this as a starting point: insert into testuser (firstname,lastname) select u1.firstname,u2.lastname from user u1, user u2 order by rand() limit 1; MvH, Luis Lebron wrote: I have an users table for a php application that I am programming. The current users table has about 1500 records. I would like to create a test table (i.e. users_test) with 10,000 records based on random data from the first table (i.e. random first name combined with a random last name, etc...). Is it possible to do this via a MySQL query? Luis R. Lebron Sigmatech, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - Slave Backup to Master
Hi there I have a question regarding replication. This is the situation: Mysql-Max4.0.16 192.168.0.10 master 192.168.0.11 slave I would like to use the slave as the backup server and use it in case the master goes down for any reason. I'm looking for a safe method to re-update the master when we take it up again. I read that 2-ways replication is dangerous so for peace of mind I would avoid it. I was considering a simple dump of the database on slave, when I have to reconnect the master server. I would: 1. disconnect the slave 2. create a dump file form salve 3. put the dump on the master 4. reconnect the master and slave Questions are.. a. I use innodb tables. Any problems to nofify? b. Are auto_increment values correct/safe after such operations. I guess so but I'm not 100% sure c. Do you have comments and suggestions regarding 2-ways replications Any suggestion / comment is welcome ;) Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Can not make PULL from mysql.bkbits.net
On Nov 12, 2003, at 17:52, Gelu Gogancea wrote: bk pull http://mysql.bkbits.net/mysql-5.0 Try bk clone http://mysql.bkbits.net/mysql-5.0 Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Digest Again, PLEASE SOMEONE HELP?
On Nov 12, 2003, at 20:37, Paul Fine wrote: Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? While I do not use digest for this list, I use it for other MySQL lists. It behaves in the way you describe. I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. Perhaps unsub from the main list, then sub to the digest. I have also not received any response from admin. I have bcc'd the admin. Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - Slave Backup to Master
Hi thanks a lot for your prompt reply. The second email went out on accident - apologies. I managed to fix it - I found localhost; EcolMod1 in user.myd. Thanks again, Rainer On 13 Nov 2003 at 10:41, nm wrote: Hi there I have a question regarding replication. This is the situation: Mysql-Max4.0.16 192.168.0.10 master 192.168.0.11 slave I would like to use the slave as the backup server and use it in case the master goes down for any reason. I'm looking for a safe method to re-update the master when we take it up again. I read that 2-ways replication is dangerous so for peace of mind I would avoid it. I was considering a simple dump of the database on slave, when I have to reconnect the master server. I would: 1. disconnect the slave 2. create a dump file form salve 3. put the dump on the master 4. reconnect the master and slave Questions are.. a. I use innodb tables. Any problems to nofify? b. Are auto_increment values correct/safe after such operations. I guess so but I'm not 100% sure c. Do you have comments and suggestions regarding 2-ways replications Any suggestion / comment is welcome ;) Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Rainer M. Krug, Dipl. Phys. (Germany), MSc Conservation Biology (UCT) Department of Conservation Ecology University of Stellenbosch Matieland 7602 South Africa Tel: +27 - (0)21 882 8862 Fax: +27 - (0)21 808 3304 Cell: +27 - (0)83 9479 042 email: [EMAIL PROTECTED] [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Replication - Slave Backup to Master
On Thursday 13 November 2003 10.41, nm wrote: Hi there I have a question regarding replication. This is the situation: Mysql-Max4.0.16 192.168.0.10 master 192.168.0.11 slave I would like to use the slave as the backup server and use it in case the master goes down for any reason. I'm looking for a safe method to re-update the master when we take it up again. You need a way to resume operation in case your master dies, correct? I was considering a simple dump of the database on slave, when I have to reconnect the master server. You should regularly do backups from your slave. Sounds like you're a slave short, what if your master burns up in flames while you are doing the dump from your slave? I would: 1. disconnect the slave Since the master just went down your slave would already be disconnected. 2. create a dump file form salve Consider which of your hosts have the most correct data after a crash. 3. put the dump on the master Put an exact copy of the dump on all nodes (master(s) and slave(s)) 4. reconnect the master and slave also clear the master bin-logs from the master, and the index files. do the same for the slaves: clear out following files from your new slave(s): master.info all relay-log files (index, *.bin) This way the master will start at bin-log 001 at position 4. And the slave(s) will start reading from the 'first' position from the master. I know, position 4 == first looks weird but that's just the way it is. Questions are.. a. I use innodb tables. Any problems to nofify? I have so far recovered ok with innodb tables, anyone else are welcome to comment. b. Are auto_increment values correct/safe after such operations. I guess so but I'm not 100% sure if you start over with the exact same snapshot on all nodes you should be ok. c. Do you have comments and suggestions regarding 2-ways replications tricky... as Jeremy Zawodny said in a previous thread: it's a race condition with primary keys etc. http://www.mysql.com/doc/en/Replication_FAQ.html These are just some primary hints, corrections are welcome. Mikael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
Hi! Thanks for the info. We ARE getting tons of connections/second, so that might be the problem... We hoped that 4.0.16 would solve it, which it didn't, so now we're hoping for 4.0.17... -- Henrik Matt W skrev: Hi guys, Just wanted to say that I think I remember a recent commit to the source tree to correct a problem or problems with max_user_connections counting. I think one of them was a race condition if 2 threads tried to increase/decrease the counter at once. But it seems like that would rarely happen for you guys unless you're getting tons of connections/second. I'm pretty sure there was another issue that was throwing the count off. I guess you'll have to see if it's fixed in the next release (4.0.17). Sorry I can't remember more details. Matt - Original Message - From: Joe Lewis Sent: Wednesday, November 12, 2003 11:41 AM Subject: Re: max_user_connections problem after upgrading We're experiencing the same issues, but not neccesarily after an upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're getting max'd connections only on specific users, and the show processlist is returning only the show processlist process. the results of netstat show absolutely nothing. What I think is happening is the connections are not properly getting closed. The users are allowed to connect after a flush user_resources is run. Is there a bug in the particular version of MySQL (4.0.12) where the user connections are not getting decremented when a connection is closed? Joe Henrik Skotth wrote: I'm mostly using mytop, and that's the way that I see that there are no (are almost no) connections when the server claims that it is above the connection limit... So I guess that there's something seriously wrong then... Any ideas what? -- Henrik [EMAIL PROTECTED] (Pete Harlan) skrev: What does show processlist say when the connections are maxed out? (You may have to leave a client logged in to reserve a slot so you can submit this query.) If it shows only a few connections, then there's something seriously wrong. If it shows a ton of idle connections, it should tell you which machines they are coming from and which users, and that should help you track down who's holding connections open. --Pete On Wed, Nov 12, 2003 at 01:15:54AM +0100, Henrik Skotth wrote: Hello! I have tested this now, and that isn't the case. Any other ideas? -- Henrik Michael McTernan skrev: Hi, Have you tried netstat -a on the box with the MySQL server? This command (Linux) will show what is connected to where, and will help you double check that there really aren't any open connections to the server. Thanks, Mike -Original Message- From: Henrik Skotth [mailto:[EMAIL PROTECTED] Sent: 10 November 2003 18:54 To: [EMAIL PROTECTED] Subject: Re: max_user_connections problem after upgrading That's new for 4.0? Hadn't noticed it... Anyway, it's set to 0 so there is no limit to exceed. Also, we aren't getting the error messages ALL the time, they start to appear after a day or two and gets more and more frequent untill I restart mysql. Any other ideas? -- Henrik gerald_clark skrev: Are you sure you are net exceeding the setting for maximum connections per hour for that user? Henrik Skotth wrote: Hi! What I meant was that even if there are currently only two user connections being used, and the limit is 300, we still get the already more than max_user_connections error... -- Henrik gerald_clark skrev: Henrik Skotth wrote: Hello all, We recently upgraded to 4.0, it went very well and the performance gains have been great. But now the server has started to act strangely. Every few days, the server starts to refuse connections, saying that there is already more than max_user_connections, but there is really only one or two active connections and our max_user_connections is 300. I have to take down and restart the server to solve the problem, and it keeps happening over and over again every few days... Am I the only one having this problem? Any suggestions? Regards, -- Henrik Skotth, Hogwarts.nu Are there 298 or 299 inactive connections? If so, why are they not being closed? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: Replication - Slave Backup to Master
Hi I set a 2-way replication on 2 servers. I made some test transactions and it is working. I can make the clients go to any of my servers and the replication is done in real time, or so. It looks great. What are the potential problems I could have? Suggestions? Do you have any experience? (using Mysql-Max4.0.16) Thanks!! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Does mySQL support Pascal/C++ like Unions
Hello! Does mySQL support a thing like Pascal/C++ unions for attribute types, i.e. an attribute can be of two types at the same time. Thanks in advance. Florian
Problem with compilation on Solaris 2.6
Hello I'll compile mysql on Solaris 2.6 with gcc 2.95.2 and gnumake and and GNUtar and such error has been present Making all in isam make[2]: Entering directory `/export/home/pekasz/src/mysql-3.23.58/isam' /bin/ksh ../libtool --mode=link gcc -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -lbind -o isamlog isamlog.o libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lpthread -lthread -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread gcc -O3 -DDBUG_OFF -DHAVE_RWLOCK_T -o isamlog isamlog.o -lbind libnisam.a ../mysys/libmysys.a ../dbug/libdbug.a ../strings/libmystrings.a -lpthread -lthread -lcrypt -lgen -lsocket -lnsl -lm -lpthread -lthread ../mysys/libmysys.a(tree.o): In function `tree_delete': tree.o(.text+0x178): multiple definition of `tree_delete' /usr/local/lib/libbind.a(tree.o):/export/home/pekasz/src/src/lib/isc/tree.c:145: first defined here /usr/local/sparc-sun-solaris2.6/bin/ld: Warning: size of symbol `tree_delete' changed from 48 to 444 in tree.o collect2: ld returned 1 exit status make[2]: *** [isamlog] Error 1 make[2]: Leaving directory `/export/home/pekasz/src/mysql-3.23.58/isam' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/export/home/pekasz/src/mysql-3.23.58' make: *** [all] Error 2 I was attempting to compile it with ver. 4.0.16 and this same problem has been occured. Please help, what do Piotr -- Piotr Kasztelowicz [EMAIL PROTECTED] http://www.am.torun.pl/~pekasz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4 Built in SSL?
Thanks but if I am not mistaken, you are talking about SSH tunneling the connection not what I am looking for. I am interested in how to implement SSL. Thanks! -Original Message- From: Ferguson, Michael [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 2:47 PM To: Paul Fine Subject: RE: MySQL 4 Built in SSL? What I have done is load putty on my WIN2K box. Connect to the nix box - make sure authentication is correctly configured Launch mysql and work on it from the command line prompt -Original Message- From: Paul Fine [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:32 PM To: [EMAIL PROTECTED] Subject: MySQL 4 Built in SSL? Would anyone be kind enough to provide an example of using MySQL 4 with it's apparent built in SSL functionality to connect from a Win client to *Nix box? Am I correct in assuming that this new functionality means that I will not have to use stunnel or ssh tunneling? Thanks for any info! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MYSQLD Dump - Locks Database
Can anyone please tell me what if anything happens with database locking during the execution of a mysqldump? Ie. What happens to database transactions occurring while performing a dump? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Import Query From Access?
Hi I am pretty new to mysql. I am porting an access driven website to mysql. All has gone pretty much to plan bar one final page. This page is basically a listing of duplicate users based on the email field. In the access db I had a predefined query called emailduplicate The site asp page called a recordset from this query. For starters, can I create a similar stored query with mysql? If so how do I do it? I use mysql front but can see no way to create a stored query similar to access. What I require at the end is a web page listing member records where the emaila ddress occurs more than once. Also all other data associated with each record as the additional data is very likely to be different even if emails are the same. Should it help the original access query stored as email duplicate was as per below. SELECT Members.Email, Members.ID, Members.Worldpayid, Members.yourname, Members.Password, Members.Email2, Members.fullmember, Members.DateFullmemberpaid, Members.unsubcribed, Members.dateunsubscribed, Members.Paidbycheck, Members.dtmMembershipExpires FROM Members WHERE (((Members.Email) In (SELECT [Email] FROM [Members] As Tmp GROUP BY [Email] HAVING Count(*)1 ))) ORDER BY Members.Email; The asp page used a recorset as follows. Recordset1.Source = SELECT * FROM emailduplicate ORDER BY Email, fullmember Any help or advice on how best to achieve the final end result would be appreciated. best wishes Michael
RE: Digest Again, PLEASE SOMEONE HELP?
Digest mode for this list is 2 digests a day. -Original Message- From: Paul Fine [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:37 PM To: [EMAIL PROTECTED] Subject: Digest Again, PLEASE SOMEONE HELP? Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. I have also not received any response from admin. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql - Problem with Left Join
Your syntax is wrong, or at least not standard, if you are trying to do multiple left joins. Your ordering is not your typical for a query. And you say there instead of where. Your query should be structure like this: SELECT fields FROM table, table,... LEFT JOIN table ON join condition LEFT JOIN table ON join condition LEFT JOIN table ON join condition ... WHERE filter condition I'm surprised your query worked at all. On Wednesday, November 12, 2003, at 05:20 PM, Kim G. Pedersen wrote: the little Query1 works until I add the left join : Query 1 ) Select A.DepartmentName,A.Address,P.Postcode,P.cityname from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query 2) Select A.DepartmentName,A.Address,P.Postcode,P.cityname, CP.firstname left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query1 return : - company , streeet ,2000,copenhagen Query2 return : - Empty I know that the jeft join will give zero result , since the table caddresscontactperson are empty. But I do not Understand Why I not get - company , streeet ,2000,copenhagen , NULL Normally a left join should not effect ur result. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
max_user_connections clarification
Hi all: Need some clarification on this because the books and articles don't make it clear. Let's say I have a program in php or whatever language that contains the username and password for access to mysql and the program executes once anyone visits the site. Can multiple visitors to my site make connections and access the database through that username and password which is in the program code. Is that what max_user_connections is about, connecting using the same username and password? Secondly for the purpose of SAFE public viewing could I have in my settings blanks for username and password,etc. user name: '' password: '' hostname: localhost and then invoke a GRANT SELECT option? Would that be safe? Thanks for your patience. J __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Userpasswords
Hi there . How can i passwords so i can login into myqsl. With current configuration anyone is allowded to login. __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SQL: ORDER BY v LEFT JOINed records
Hello, I have got problem with ordering records selected by LEFT JOIN, there is a SQL select: SELECT so.label, so.price, o.name, o.id id, o.location, p.filename, p.format FROM offers o, offers_soffer so LEFT JOIN offers_photos p ON o.id = p.record_id WHERE so.rec_id = o.id GROUP BY (o.id) ORDER BY o.location DESC, p.id ASC there can be more photos in one offer and I have to get 1 photo which is inserted to offer by the first. I tried to order by p.id, ORDER BY p.itime and this doesn't work :/ Have somebody some idea? Thank you very much for all help. Jiri Nemec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Digest Again, PLEASE SOMEONE HELP?
Thanks. Well I can't figure out what the problem is, I keep getting single e-mails for each post :( even after unsubscribing and resubscribing with digest selected. -Original Message- From: Rob A. Brahier [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 7:44 AM To: Paul Fine; [EMAIL PROTECTED] Subject: RE: Digest Again, PLEASE SOMEONE HELP? Digest mode for this list is 2 digests a day. -Original Message- From: Paul Fine [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 12, 2003 3:37 PM To: [EMAIL PROTECTED] Subject: Digest Again, PLEASE SOMEONE HELP? Unless I am misinformed, subscription to the DIGEST format of this list should cause all messages to come in one daily e-mail? Can someone else who is subscribed this way please at least let me know that this is at least the case for them? I continue to get every single post as an individual e-mail. I have tried canceling and re-subscribing with the DIGEST option selected, to no avail. I have also not received any response from admin. Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple values in one column
On Thursday, Nov 13, 2003, at 02:55 US/Eastern, John Berman wrote: Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with It seems like you'd be better off with a modified database design. If you need multiple values in a column, you might be better off making an intersection table, and indexing that. So instead of having multiple values in column 1 of table A, you have multiple rows in table B, each with one value, referencing a single row in table A. (I hope I've explained that clearly; if I've misunderstood, or someone has a better way of phrasing it, feel free to jump in.) pjm PGP.sig Description: This is a digitally signed message part
Re: Userpasswords
On Nov 13, 2003, at 14:02, Alaios wrote: Hi there . How can i passwords so i can login into myqsl. With current configuration anyone is allowded to login. See the REVOKE command at http://mysql.com/GRANT Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql_client
Hi i have just installed the mysql_client and i am trying to remotely connect to the database of the other pc so i gave the following command mysqlshow -h 192.168.64.12 -u [EMAIL PROTECTED] mysqlshow: Host 'akroneiro' is not allowed to connect to this M What i msut do in order to gain access to the db? Hwo i can create users ? Is there any easy way for that? __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't start server
I am sure that this problem has already been answered but some how I can't figure it out. When I try to start the mysqld I get the error 2002 can't start server: Bind or TCP/IP port: Address already in use or... I check that the damoen is not runnig (ps -ef |grep mysqld ) and shows nothing running. But I just can start it. Someone there can help me with easy step to fix this? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question on slow queries
I just started logging slow queries. Today I found the following lines in the slow query log. # Query_time: 14 Lock_time: 0 Rows_sent: 293 Rows_examined: 293 use storyboards; SELECT /*!40001 SQL_NO_CACHE */ * FROM `rwt_screens`; What exact does the third line mean? thanks, Luis R. Lebron Sigmatech, Inc
Insert select query problem
I am try to copy a couple of fields from one table to another. Here is my query Insert into users_test (firstName, lastName) Select firstName, lastName from users; The users table has 1,263 rows. However, only 1 row is inserted into users_test. If I perform the following query Insert into users_test Select * users; all the rows are inserted. What am I doing wrong in the first query? thanks, Luis R. Lebron Sigmatech, Inc
Limitations on data for default...
I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
i believe you'll need to use a timestamp column, which will set to the current date/time at each insert/update. I don't think you can set the date default to a function. You are also using a reserved word Date for your column name without escaping it, i.e. `Date`, also you have two modify's in your SQL...unless thats an email typo. hth Jeff Mike Morton [EMAIL PROTECTED]To: [EMAIL PROTECTED] m cc: Subject: Limitations on data for default... 11/13/2003 10:15 AM I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
http://www.mysql.com/doc/en/CREATE_TABLE.html A DEFAULT value has to be a constant, it cannot be a function or an expression. ... Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. You have to use NOW() or CURRENT_DATE functions in your insert queries instead. or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html Mike On Thursday 13 November 2003 16.15, Mike Morton wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- Ongame E-Solutions AB Mikael Fridh Junior Systems Administrator Smedsgränd 3, 753 20 Uppsala, Sweden Mobile: +46 708 17 42 00 Office: +46 18 69 55 00 Fax: +46 18 69 44 11 e-mail: [EMAIL PROTECTED] http://www.ongame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
Mike Morton [EMAIL PROTECTED] wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? Default value cannot be a function. In your case TIMESTAMP column may help you: http://www.mysql.com/doc/en/DATETIME.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Limitations on data for default...
Mikael: Thanks - I must have missed that in the create table docs - I did look there first - honest! And you are correct - in that the timestamp is too much precision, and unfortunately due to the thousands of distributed apps that access this DB, I cannot modify all the queries - oh well, guess it is just better to write a cron to modify the bad data :) Thanks for the info! On 11/13/03 10:28 AM, Mikael Fridh [EMAIL PROTECTED] wrote: http://www.mysql.com/doc/en/CREATE_TABLE.html A DEFAULT value has to be a constant, it cannot be a function or an expression. ... Default values must be constants. This means, for example, that you cannot set the default for a date column to be the value of a function such as NOW() or CURRENT_DATE. You have to use NOW() or CURRENT_DATE functions in your insert queries instead. or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html Mike On Thursday 13 November 2003 16.15, Mike Morton wrote: I am trying to change a column specification, a date column, so that the default value is the current date. Is this possible? I just get an error on the change query: alter table Hits modify modify Date date NOT NULL default CURDATE(); ERROR 1064: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'date NOT NULL default CURDATE()' at line 1 Am I just stupid trying to have a function for a default? And if so, is it possible to get the 'current date' as the default value for a field? TIA! :) -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- Cheers Mike Morton * * Tel: 905-465-1263 * Email: [EMAIL PROTECTED] * Indeed, it would not be an exaggeration to describe the history of the computer industry for the past decade as a massive effort to keep up with Apple. - Byte Magazine Given infinite time, 100 monkeys could type out the complete works of Shakespeare. Win 98 source code? Eight monkeys, five minutes. -- NullGrey -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Limitations on data for default...
From: Mikael Fridh [mailto:[EMAIL PROTECTED] or read about the TIMESTAMP type, but I guess that's too much precision since you only wanted the date, not the time. http://www.mysql.com/doc/en/DATETIME.html It's only too much precision when you're selecting the whole field. You can simply select a timestamp as... SELECT DATE_FORMAT(column, '%Y-%m-%d') AS date ...and get a date just fine. -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
CRASH AND ROLLBACK SIMULATION
Do you know how to test a crash and a rollback? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
Hi, Do you know how to test a crash and a rollback? You mean a client app crashing on you? How about disabling/unplugging the network? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
stupid newbie question
At the command prompt, I'm supposed to type: mysql -h host -u user -p Does that mean that I type, for example: mysql -h localhost -u admin -p with localhost=host and admin=user? When I'm asked for the password, I hit return and it lets me in. But if I try to create a database, by using: GRANT ALL ON store.* TO [EMAIL PROTECTED]; I get an ERROR 1044: Access denied for user: '@localhost' to database 'store' Any help is appreciated. --Scott _ Concerned that messages may bounce because your Hotmail account is over limit? Get Hotmail Extra Storage! http://join.msn.com/?PAGE=features/es -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Question on slow queries
A full table scan was performed on the rwt_screens table. The /* ... */ means that if the dbms is 4.0.x then do not cache this query. -Original Message- From: Luis Lebron [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 8:52 AM To: Mysql (E-mail) Subject: Question on slow queries I just started logging slow queries. Today I found the following lines in the slow query log. # Query_time: 14 Lock_time: 0 Rows_sent: 293 Rows_examined: 293 use storyboards; SELECT /*!40001 SQL_NO_CACHE */ * FROM `rwt_screens`; What exact does the third line mean? thanks, Luis R. Lebron Sigmatech, Inc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: stupid newbie question
From: Scott Yamahata [mailto:[EMAIL PROTECTED] At the command prompt, I'm supposed to type: mysql -h host -u user -p Does that mean that I type, for example: mysql -h localhost -u admin -p with localhost=host and admin=user? When I'm asked for the password, I hit return and it lets me in. But if I try to create a database, by using: GRANT ALL ON store.* TO [EMAIL PROTECTED]; I get an ERROR 1044: Access denied for user: '@localhost' to database 'store' Any help is appreciated. Unless you've previously granted GRANT permissions to [EMAIL PROTECTED] (as the MySQL root user), then this will, and should, fail. In addition, though I'm not entirely certain, I think you need to delimit [EMAIL PROTECTED] as two different strings -- GRANT ALL ON store.* TO 'admin'@'localhost' If you need help giving GRANT permissions to [EMAIL PROTECTED], let us know or check out: http://www.mysql.com/doc/en/GRANT.html -- Mike Johnson Web Developer/Systems Asst. Smarter Living, Inc. phone (617) 497-2500 x226 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Urgent problem
restart mysql with the skip-grants option -Original Message- From: Rainer M. Krug [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 3:03 AM To: [EMAIL PROTECTED] Subject: Urgent problem Hi I did something very stupid: I locked myself out of the MySQL server (all accounts, incl administrator)... My problem: I use MySQLFront to change the allowed hosts and did it for all users. They were 'localhost' and I set them to 'localhost; EcolMod1' where the EcolMod1 is the name of my computer where the MySQL server is installed on. After these changes, I couldn't log on any more. Windows 2000, MySQL 4.012. Any help welcome what I could do to access the data again, Rainer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: max_user_connections problem after upgrading
William R. Mussatto wrote: Joe Lewis said: We're experiencing the same issues, but not neccesarily after an upgrade. We're using MySQL 4.0.12 and FreeBSD 4.7 Release. We're getting max'd connections only on specific users, and the show processlist is returning only the show processlist process. the results of netstat show absolutely nothing. What I think is happening is the connections are not properly getting closed. The users are allowed to connect after a flush user_resources is run. Is there a bug in the particular version of MySQL (4.0.12) where the user connections are not getting decremented when a connection is closed? Joe Is the user running show processlist allowed to see all processes (e.g. 'root')? Yes, indeed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: CRASH AND ROLLBACK SIMULATION
Hi there, I am not sure I understood your question correctly. What exactly is it that you want to test ? a) the recovery possibility in case of power down b) the recovery possibility in case of client disconnection (network interruption, timeout etc.) c) Recovery possibilities in general accessing the data files directly and indirectly using command line and/or GUI tools. In case a) mysql would recover the data files itself in most cases next time when the server is started as long as the logfiles, datafiles, config files are all there in the original positions (talking about InnoDB). b) If the network connection times out or client is diconnected than all executed commands since the last commit,begin gets rolled back (will not be applied) c) If the Innodb files are damaged so that the mysql server does not startup than no client tool (command line or GUI) that uses the indirect access method can access any data. I know that there are tools in the mysql package which access and repair (My)ISAM tables directly (server doesnt need to run), but that isnt true for the InnoDB tables I believe. I am not sure if InnoDB Hotbackup can directly access the InnoDB tables. What I know is that there will be a book coming up in February from Paul Dubois (Certification Study Guide) which is already described on Amazon. When it comes out I believe that it will be the best book on the market so far, answering many of those and similar questions. I had the honour to review one of those preprints, all I can say so far is Very impressive, you will see for yourself.. On Friday 14 November 2003 00:58, nm wrote: Do you know how to test a crash and a rollback? -- kind regards Nils Valentin Tokyo/Japan http://www.be-known-online.com/mysql/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Page Numbers
On Wed, Nov 12, 2003 at 08:10:58PM -0600, Mike Blezien wrote: Hello, we've set up many displays using the Previous and Next simple linking set up of search results. But now would like to implement the page number style, IE. Previous [1] [2] [3] Next style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. Paul DuBois provides easy-to-understand code for this exact format in _MySQL and Perl for the Web,_ and his _MySQL Cookbook._ Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Page Numbers
You need to do a count before your select, use limmit to only select a set number of records. That way you can work out how many pages there are and just have a link. You run the same query for each page except that the start record for the limit is the pagenumber * number of records per page. - Original Message - From: Jesse Sheidlower [EMAIL PROTECTED] To: Mike Blezien [EMAIL PROTECTED] Cc: MySQL List [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 4:41 PM Subject: Re: Page Numbers : On Wed, Nov 12, 2003 at 08:10:58PM -0600, Mike Blezien wrote: : Hello, : : we've set up many displays using the Previous and Next simple linking : set up of search results. But now would like to implement the page number : style, IE. Previous [1] [2] [3] Next style format and was hoping : someone may have a sample/example queries to accomplish this. Usually : working with 10 results per page display. : : Paul DuBois provides easy-to-understand code for this exact format : in _MySQL and Perl for the Web,_ and his _MySQL Cookbook._ : : Jesse Sheidlower : : -- : MySQL General Mailing List : For list archives: http://lists.mysql.com/mysql : To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] : : -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Page Numbers
On Wed, Nov 12, 2003 at 08:10:58PM -0600, Mike Blezien wrote: Hello, we've set up many displays using the Previous and Next simple linking set up of search results. But now would like to implement the page number style, IE. Previous [1] [2] [3] Next style format and was hoping someone may have a sample/example queries to accomplish this. Usually working with 10 results per page display. Paul DuBois provides easy-to-understand code for this exact format in _MySQL and Perl for the Web,_ and his _MySQL Cookbook._ also, depending on your language, there are classes out there for exactly that purpose. i know they exist for PERL and PHP... hth Jeff Jesse Sheidlower -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple values in one column
Hm Lost me a bit, if you can all bear with me I will give a little more info I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field Every record has a set identifier (uniqueref) When I search I have a statement like: sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' group by uniqueref As you can see the restuls are grouped by the identifier. The surname coloum is indexed and we have 200,000 records and get a result within a couple of seconds. The results are tabulated on a web page and then one can drill down Go to http://www.jgsgb.org.uk/members/databasex.asp (username and password is: berman) Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Any help would be appreciated. Regards John Berman -Original Message- From: Parker Morse [mailto:[EMAIL PROTECTED] Sent: 13 November 2003 14:14 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: multiple values in one column On Thursday, Nov 13, 2003, at 02:55 US/Eastern, John Berman wrote: Can I have multiple values in one column and then index the column, I have used different delimiters but the index only seems find the whole contents of the column no matter what I separate the data with It seems like you'd be better off with a modified database design. If you need multiple values in a column, you might be better off making an intersection table, and indexing that. So instead of having multiple values in column 1 of table A, you have multiple rows in table B, each with one value, referencing a single row in table A. (I hope I've explained that clearly; if I've misunderstood, or someone has a better way of phrasing it, feel free to jump in.) pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
problem running the following query
Hi , I am using mySQL on windows 98 machine. In my (java)program I want to run the following query query = Select * from kids_books where item_id = 'itemId' ; I get the itemId as a parameter from one of the functions. When i run this query I am not getting any out put. I have checked that the data is there in the table for this query. please let meknow what is wrong with the query. thanks, florentina - Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard
Re: problem running the following query
Hi, I am using mySQL on windows 98 machine. In my (java)program I want to run the following query query = Select * from kids_books where item_id = 'itemId' ; You are comparing values in column item_id with the string 'itemId'. I guess this is not what you want, is it? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server. Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: problem running the following query
what type is item_id? That query is looking for a value in the field item_is that is equal to the a constant string itemId. is there a record with the value in it? - Original Message - From: florentina kore [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 5:18 PM Subject: problem running the following query : Hi , : I am using mySQL on windows 98 machine. : In my (java)program I want to run the following query : : query = Select * from kids_books where item_id = 'itemId' ; : : I get the itemId as a parameter from one of the functions. : When i run this query I am not getting any out put. : I have checked that the data is there in the table for this query. : : please let meknow what is wrong with the query. : : thanks, : florentina : : : : - : Do you Yahoo!? : Protect your identity with Yahoo! Mail AddressGuard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: maintaining size of a db
Got it Harald, thanks. OK, I've got this working now, so I'll do a quick overview of what I've learned... for the archives: I am setting up mysql with msyslog to be a centralized logging server. My servers (Windows Red Hat) will send their logs to this mysql box. To keep the mysql db from growing beyond a certain size, I first estimate the size of my average record and divide by the total byte size I want to allow on disk, to determine about how many records I want as a maximum (of course, one must look at and consider the size of any indexes for your db, also, and leave some extra room for error). For this example, I'll say I've figured out that I can allow a maximum of 250,000 records, and I have: - a db named msyslog - and a table within it named syslogTB syslogTB has an autoincrement field seq. What I do is set up a cron job to run a scan of the database periodically, and yank out all records beyond 250,000. The cron job runs as sql-user with password PASSWORD and calls a plain text file /root/delete_old.sql for it's input. The cron job will thus run this as its command: /path/mysql -u sql-user --password=PASSWORD msyslog /root/delete_old.sql ...and in /root/delete_old.sql, there is only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq DESC limit 25,1; delete from syslogTB where seq @aa; Thanks everyone for your help!! Scott --- Harald Fuchs wrote: Scott H wrote: That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? That's exactly what the two statements above do, except that MySQL has user variables (the @aa shown above) instead of environment variables. = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[syntax error in configure and configure.in]
Description: configure contains the lines compile_readline= no compile_libedit= no which causes a message ./configure: line xxx: no: command not found unless you happen to have a program named no in your path. How-To-Repeat: ./configure Fix: I deleted the white space between the = and no in configure.in at line 2173 and 2174, viz: $ diff -c configure.in.orig configure.in *** configure.in.orig Thu Apr 3 05:15:53 2003 --- configure.inThu Nov 13 11:17:36 2003 *** *** 2170,2177 [ with_libedit=undefined ] ) ! compile_readline= no ! compile_libedit= no if [test $with_libedit = yes] [test $with_readline = yes] then --- 2170,2177 [ with_libedit=undefined ] ) ! compile_readline=no ! compile_libedit=no if [test $with_libedit = yes] [test $with_readline = yes] then Submitter-Id: submitter ID Originator:Ric Anderson - SIRT Organization: Ric Anderson, U of Arizona, 1077 North Highland, Tucson, AZ 85721-0073 Phone: +1 520 626 2758 (v) +1 520 626 8295 FAX [EMAIL PROTECTED] (RA90-ARIN) Personal Email: [EMAIL PROTECTED] UNIX *is* user friendly. It's just selective about who its friends are. MySQL support: none Synopsis: typo in configure script Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-4.1.0-alpha (Source distribution) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: SunOS coyote.Telcom.Arizona.EDU 5.8 Generic_108528-23 sun4u sparc SUNW,Ultra-60 Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gmake /usr/local/bin/gcc GCC: Reading specs from /usr/local/gcc/5.8_current/bin/../lib/gcc-lib/sparcv9-sun-solaris2/3.2.1/specs Configured with: /dfs/src/GNU/gcc-3.2.1/configure --prefix /usr/local/gcc/8_gcc_3.2.1_sparcv9 --enable-languages=c,c++,f77,objc sparcv9-sun-solaris2 Thread model: posix gcc version 3.2.1 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1788708 Jul 29 14:57 /lib/libc.a lrwxrwxrwx 1 root root 11 Sep 21 2002 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157900 Jul 29 14:57 /lib/libc.so.1 -rw-r--r-- 1 root bin 1788708 Jul 29 14:57 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Sep 21 2002 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157900 Jul 29 14:57 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=complex' '--enable-thread-safe-client' '--with-innodb' '--with-berkeley-db' '--with-embedded-server' '--with-openssl' '--with-vio' '--enable-local-infile' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer -g' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Grant permissions problems - help
I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: maintaining size of a db
two last gotchas I thought of... 1- the routine will erase all the old records, but the day's logs will increment between executions, so you may want to give it a day's worth of 'padding' if the 20GB is a hard limit (disk size). No worries if it's flexible 2- until you have filled to your size limit, you may want to run it manually as you won't have your 250,000 records in the table yet, so your initial query will return null (I think), which is very likely to mess up the delete statement following it -Original Message- From: Scott H [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 12:30 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: Re: maintaining size of a db Got it Harald, thanks. OK, I've got this working now, so I'll do a quick overview of what I've learned... for the archives: I am setting up mysql with msyslog to be a centralized logging server. My servers (Windows Red Hat) will send their logs to this mysql box. To keep the mysql db from growing beyond a certain size, I first estimate the size of my average record and divide by the total byte size I want to allow on disk, to determine about how many records I want as a maximum (of course, one must look at and consider the size of any indexes for your db, also, and leave some extra room for error). For this example, I'll say I've figured out that I can allow a maximum of 250,000 records, and I have: - a db named msyslog - and a table within it named syslogTB syslogTB has an autoincrement field seq. What I do is set up a cron job to run a scan of the database periodically, and yank out all records beyond 250,000. The cron job runs as sql-user with password PASSWORD and calls a plain text file /root/delete_old.sql for it's input. The cron job will thus run this as its command: /path/mysql -u sql-user --password=PASSWORD msyslog /root/delete_old.sql ...and in /root/delete_old.sql, there is only this text (2 lines): select (@aa:=seq) as low_seq from syslogTB order by seq DESC limit 25,1; delete from syslogTB where seq @aa; Thanks everyone for your help!! Scott --- Harald Fuchs wrote: Scott H wrote: That's fine. Thus if have seq as an autoincrement field, and I wanted to stay around say 1000 records, deleting the oldest records, I would need to run a cron job that would somehow nest or relate these 2 sql statements: select (@aa:=seq) as low_seq from logtable order by seq limit 1000,1 delete from logtable where seq @aa I've tried putting this into a subquery format but no luck. But I'm a noob, so I keep trying, thinking I might hit on the right syntax. Or, is there some way to pull the value of low_seq into an environment variable and use it in a script file to run the 2nd statement? That's exactly what the two statements above do, except that MySQL has user variables (the @aa shown above) instead of environment variables. = -- To announce that there must be no criticism of the President, or that we are to stand by the President, right or wrong, is not only unpatriotic and servile, but is morally treasonable to the American public. -- Theodore Roosevelt, 1918 . __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant permissions problems - help
I forgot to mentioned that this same code works on the same database on my W2K but I am having problems on my Mac OS X (Darwin). I sqldump the database from my W2K to my Mac Os X. Nestor :-) Nestor A. Florez Nestor Florez 11/13/2003 10:17:37 AM I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT Query assistance please
I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help
execute show grants for nestor@'%' and see what that user has. -Original Message- From: Nestor Florez [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 12:21 PM To: Nestor Florez; [EMAIL PROTECTED] Subject: Re: Grant permissions problems - help I forgot to mentioned that this same code works on the same database on my W2K but I am having problems on my Mac OS X (Darwin). I sqldump the database from my W2K to my Mac Os X. Nestor :-) Nestor A. Florez Nestor Florez 11/13/2003 10:17:37 AM I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant permissions problems - help
Hi Nestor. What does the mysql_error() report as the exact error
message? This could help you identify what kind of access control
issue it is.
Alternatively, you could try running the same query from the command
line (using the mysql client) and see if you get the same error.
Also, I'm not really sure about this feature, but it has helped me
once or twice: trace mode. In PHP, you can do
ini_set('mysql.trace_mode','on');
and there might be a way to do this also with the mysql client
Best, George.
George Webb
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: SELECT Query assistance please
Got it, thanks SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference', EVENT.ID, EVENT.ID_PROJECTCODE FROM PROJECTCODE LEFT JOIN EVENT ON PROJECTCODE.ID = EVENT.ID_PROJECTCODE WHERE EVENT.ID IS NULL ORDER BY PROJECTCODE.Name -Original Message- From: Luc Foisy Sent: Thursday, November 13, 2003 2:01 PM To: MYSQL-List (E-mail) Subject: SELECT Query assistance please I have two tables EVENT and PROJECTCODE EVENT.ID EVENT.ID_PROJECTCODE PROJECTCODE.ID PROJECTCODE.Name EVENT PROJECTCODE ID = 1 ID_PROJECTCODE = 0 ID = 1 ID = 2 ID_PROJECTCODE = 0 ID = 2 ID = 3 ID_PROJECTCODE = 1 ID = 3 ID = 4 ID_PROJECTCODE = 4 ID = 4 SELECT PROJECTCODE.ID AS 'ID', PROJECTCODE.Name AS 'Reference' FROM PROJECTCODE Not quite sure on the join or where claus here, I tried 3 or 4 different ways and can't seem to get what I want. What I want out of the results is PROJECTCODE.ID = 2 and 3, that being all records in PROJECTCODE that do not appear in EVENT Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help
+-+ | Grants for [EMAIL PROTECTED] | +-+ | GRANT USAGE ON *.* TO 'nestor'@'%' IDENTIFIED BY PASSWORD '93c8c803300d29b9' | | GRANT ALL PRIVILEGES ON `test`.* TO 'nestor'@'%' | +-+ IS the same on both my W2K and my Mac OS X Nestor :-) Nestor A. Florez Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:01:37 AM execute show grants for nestor@'%' and see what that user has. -Original Message- I forgot to mentioned that this same code works on the same database on my W2K but I am having problems on my Mac OS X (Darwin). I sqldump the database from my W2K to my Mac Os X. Nestor :-) Nestor A. Florez Nestor Florez 11/13/2003 10:17:37 AM I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant permissions problems - help
I just realized that if I login to mysql as the user I can insert
records.
It must be somehting with my code
I will try the trace_on
It is just amazing that the same code works in my W2K machine
and when I ftp it to the Mac OS X only adding to th eschools fails.
Thanks,
Nestor :-)
Nestor A. Florez
George Webb [EMAIL PROTECTED] 11/13/2003 11:06:29 AM
Hi Nestor. What does the mysql_error() report as the exact error
message? This could help you identify what kind of access control
issue it is.
Alternatively, you could try running the same query from the command
line (using the mysql client) and see if you get the same error.
Also, I'm not really sure about this feature, but it has helped me
once or twice: trace mode. In PHP, you can do
ini_set('mysql.trace_mode','on');
and there might be a way to do this also with the mysql client
Best, George.
George Webb
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant permissions problems - help
OK guys this is beyond me.
I just found out that I can NOT insert into my SCHOOL table
when I login into mysql as user nestor on my Mac OS X.
The table is autoincremented. how do I find out what is the next
number to be incremented?
Nestor :-)
Nestor A. Florez
Nestor Florez [EMAIL PROTECTED] 11/13/2003 11:08:58 AM
I just realized that if I login to mysql as the user I can insert
records.
It must be somehting with my code
I will try the trace_on
It is just amazing that the same code works in my W2K machine
and when I ftp it to the Mac OS X only adding to th eschools fails.
Thanks,
Nestor :-)
Nestor A. Florez
George Webb [EMAIL PROTECTED] 11/13/2003 11:06:29 AM
Hi Nestor. What does the mysql_error() report as the exact error
message? This could help you identify what kind of access control
issue it is.
Alternatively, you could try running the same query from the command
line (using the mysql client) and see if you get the same error.
Also, I'm not really sure about this feature, but it has helped me
once or twice: trace mode. In PHP, you can do
ini_set('mysql.trace_mode','on');
and there might be a way to do this also with the mysql client
Best, George.
George Webb
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
decimal field oddity
Hi all, I have a MyISAM table with a decimal field (12,2) I have loaded a bunch of data into this table, from ascii dumps of data being migrated from other systems. Some of these dumps include a value for the decimal field in the form of 00078000.00 My understanding was that decimal fields behave like numeric fields, but are stored like char field. So i had assumed that the lead zeros would be dropped, in the spirit of a numeric field, but they are kept, in the spirit of a char like field. The lead zero's screw up the formatting for various display API's (homecooked). I'm not arguing that this behaviour is a bug, i am more interested in knowing why this behaviour is the way that it is. thanks much, sean peters [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help
Their are two nestor users. One with a password and one without a password. The one with connect from anywhere is only allowed to modify the test database. Fix your privileges and you should be okay. -Original Message- From: Nestor Florez [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 1:04 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Grant permissions problems - help +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT USAGE ON *.* TO 'nestor'@'%' IDENTIFIED BY PASSWORD '93c8c803300d29b9' | | GRANT ALL PRIVILEGES ON `test`.* TO 'nestor'@'%' | +--- --+ IS the same on both my W2K and my Mac OS X Nestor :-) Nestor A. Florez Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:01:37 AM execute show grants for nestor@'%' and see what that user has. -Original Message- I forgot to mentioned that this same code works on the same database on my W2K but I am having problems on my Mac OS X (Darwin). I sqldump the database from my W2K to my Mac Os X. Nestor :-) Nestor A. Florez Nestor Florez 11/13/2003 10:17:37 AM I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sql - Problem with Left Join
Thanks to You Brent and Bill Easton and Sorry Hrmm , that what happen when not clipping direct into mail program , here is the Original :-) : Select A.DepartmentName,A.Address,P.Postcode,P.cityname,CP.firstname from caddress A,cpostinfo P left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 where A.ID=10 and A.PostInfoID=P.ID Something was wrong in this query above , so I did rewrite the query : Select A.DepartmentName,A.Address,P.Postcode,P.cityname ,CP.firstname from caddress A,cpostinfo P Left join CContactPerson CP on CP.ID=1001 left join caddresscontactperson CACP ,ccontactinfo CCI on CACP.ContactpersonID=1001 and CACP.AddressID=10 and CCI.AddressID=-1 and CCI.AddressContactpersonID=CACP.ID and CCI.contactInfotypeID=1 where A.ID=10 and A.PostInfoID=P.ID but I still do not understand : if I just have one record in caddresscontactperson the query return a the correct answer but I caddresscontactperson are a empty table it give my empty answer - why not --company , streeet ,2000,copenhagen , NULL I know I use addresscontactperson in my left join , but why does that influent other that giving me a NULL for the CP.firstname value Your syntax is wrong, or at least not standard, if you are trying to do multiple left joins. Your ordering is not your typical for a query. And you say there instead of where. Your query should be structure like this: SELECT fields FROM table, table,... LEFT JOIN table ON join condition LEFT JOIN table ON join condition LEFT JOIN table ON join condition ... WHERE filter condition I'm surprised your query worked at all. On Wednesday, November 12, 2003, at 05:20 PM, Kim G. Pedersen wrote: the little Query1 works until I add the left join : Query 1 ) Select A.DepartmentName,A.Address,P.Postcode,P.cityname from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query 2) Select A.DepartmentName,A.Address,P.Postcode,P.cityname, CP.firstname left Join CContactPerson CP , caddresscontactperson CACP ,ccontactinfo CCI on CP.ID =1001 and CACP.ContactpersonID=CP.ID and CACP.AddressID=A.ID and CACP.ID=CCI.AddressContactPersonID and CCI.AddressID=-1 and CCI.ContactInfoTypeID=1 from caddress A,cpostinfo P there A.ID=10 and A.PostInfoID=P.ID Query1 return : - company , streeet ,2000,copenhagen Query2 return : - Empty I know that the jeft join will give zero result , since the table caddresscontactperson are empty. But I do not Understand Why I not get - company , streeet ,2000,copenhagen , NULL Normally a left join should not effect ur result. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 mvh Kim G. Pedersen macaos/elprint Development +45 35373808 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and large size dbs
Anyone? Hope that does not mean that mySql is not used with large dbs ... :) Thanks --- ow [EMAIL PROTECTED] wrote: Hi, We are considering using mySql or postgreSql for an app that handles large volume of data. Somehow, it appears (mostly from the mailing lists) that mySql does not handle large volumes of data very well (crashes, db corruptions, etc). Would ppl who use mySql for large volumes of data share their experience about number of tables, avg/max number of records in a tables, db size, time that it takes to dump/restore db, potential problems. Thanks in advance __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? If you don't get in, try mysql --defaults-file=/path/to/.my.cnf If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help
Victor, I will fixed this, but I do not think this is th eproblem. I have the same settings on both W2K and Mac OS X and it only fails on the Mac Also When I log in as nestor with a password on the Mac I can not insert a record from the ocmmand line. I can do this on my W2K database. Thanks I will try it, Nestor :-) Nestor A. Florez Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:49:51 AM Their are two nestor users. One with a password and one without a password. The one with connect from anywhere is only allowed to modify the test database. Fix your privileges and you should be okay. -Original Message- From: Nestor Florez [mailto:[EMAIL PROTECTED] Sent: Thursday, November 13, 2003 1:04 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: Grant permissions problems - help +--- --+ | Grants for [EMAIL PROTECTED] | +--- --+ | GRANT USAGE ON *.* TO 'nestor'@'%' IDENTIFIED BY PASSWORD '93c8c803300d29b9' | | GRANT ALL PRIVILEGES ON `test`.* TO 'nestor'@'%' | +--- --+ IS the same on both my W2K and my Mac OS X Nestor :-) Nestor A. Florez Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:01:37 AM execute show grants for nestor@'%' and see what that user has. -Original Message- I forgot to mentioned that this same code works on the same database on my W2K but I am having problems on my Mac OS X (Darwin). I sqldump the database from my W2K to my Mac Os X. Nestor :-) Nestor A. Florez Nestor Florez 11/13/2003 10:17:37 AM I have a mysql db where via a web page I access a database I have set the permissions to grant select, insert, update, delete on test.* to [EMAIL PROTECTED] identified by 'nestor'; flush privileges; The database has 3 tables. I can select via PHP code all the tables, but when I try to insert or update 2 of the tables allow me but there is one table that does not. My schools table refuses to insert or update, but my courses_eng and course_esp allows me to select, insert, update, delete I them gave the following command: grant all privileges on test.* to [EMAIL PROTECTED] identified by 'nestor'; grant all privileges on test.* to nestor@'%' identified by 'nestor'; flush privileges; I am still not able to insert and update to the school table, but I can do this on the course_eng and course_esp tables. Any ideas. Thanks, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help
I actually took a look at the mysql.user table and user nestor has a
password in both entries to the table.
One entry is for localhost and the other is for '%';
I try inserting from the command line and I get:
mysql insert into School (school_desc) values ('Nestor');
Query OK, 1 row affected (0.00 sec)
Even when I do it as the main root for mysql the record is not
inserted.
Do you know how to reset the auto_increment
Nestor :-)
Nestor A. Florez
Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:49:51 AM
Their are two nestor users. One with a password and one without a
password.
The one with connect from anywhere is only allowed to modify the test
database. Fix your privileges and you should be okay.
-Original Message-
From: Nestor Florez [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2003 1:04 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Grant permissions problems - help
+---
--+
| Grants for [EMAIL PROTECTED]
|
+---
--+
| GRANT USAGE ON *.* TO 'nestor'@'%' IDENTIFIED BY PASSWORD
'93c8c803300d29b9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'nestor'@'%' |
+---
--+
IS the same on both my W2K and my Mac OS X
Nestor :-)
Nestor A. Florez
Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:01:37 AM
execute show grants for nestor@'%' and see what that user has.
-Original Message-
I forgot to mentioned that this same code works on the same database
on
my W2K
but I am having problems on my Mac OS X (Darwin). I sqldump the
database from my W2K
to my Mac Os X.
Nestor :-)
Nestor A. Florez
Nestor Florez 11/13/2003 10:17:37 AM
I have a mysql db where via a web page I access a database
I have set the permissions to
grant select, insert, update, delete on test.* to [EMAIL PROTECTED]
identified by 'nestor';
flush privileges;
The database has 3 tables. I can select via PHP code all the tables,
but when I try to insert
or update 2 of the tables allow me but there is one table that does
not.
My schools table refuses to insert or update, but my courses_eng and
course_esp
allows me to select, insert, update, delete
I them gave the following command:
grant all privileges on test.* to [EMAIL PROTECTED] identified by
'nestor';
grant all privileges on test.* to nestor@'%' identified by 'nestor';
flush privileges;
I am still not able to insert and update to the school table, but I
can
do this on
the course_eng and course_esp tables.
Any ideas.
Thanks,
Nestor :-)
Nestor A. Florez
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql and large size dbs
You should search the archives. This topic has been covered quite a few times before, mainly in reference to increasing performance. Of course, peoples definition of large differs. If you think large is 10 or 20 millions records, then you will find stuff in the archives discussing databases this size. If you are looking for billions of records, you may not find anything. I would search on million and billion and see what comes up. The databases I work with are fairly small, the largest tables containing only a few 100,000 records. I haven't heard much about crashes or corruption except in specific setups. And usually when only first trying to set things up. Compiler flags have usually solved the problems. On Thursday, November 13, 2003, at 03:24 PM, ow wrote: Anyone? Hope that does not mean that mySql is not used with large dbs ... :) Thanks --- ow [EMAIL PROTECTED] wrote: Hi, We are considering using mySql or postgreSql for an app that handles large volume of data. Somehow, it appears (mostly from the mailing lists) that mySql does not handle large volumes of data very well (crashes, db corruptions, etc). Would ppl who use mySql for large volumes of data share their experience about number of tables, avg/max number of records in a tables, db size, time that it takes to dump/restore db, potential problems. Thanks in advance __ Do you Yahoo!? Protect your identity with Yahoo! Mail AddressGuard http://antispam.yahoo.com/whatsnewfree -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems storing SHA1 Hash
Hello all,
I have a problem storing an SHA1 hash generated as user password for an application...
This is the original SELECT Command:
INSERT INTO Auth$ VALUES ('System',
'%X;Z(B9?(?U?NE?$03?#j???-#N?[. 6?^?', 3)
but the stored values are:
%X;Z(B9?(?U?NE?$03?#j???-#N?[. 6?^?
this differes from the original in some characters and the password check fails...
I've tryed changing the table field in some ways but TEXT, BLOB, VARCHAR settings
produces the same results...
Why?!?!? Anyone can help me?!?!
Thanks a lot for help...
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: multiple values in one column
On Thursday, Nov 13, 2003, at 12:03 US/Eastern, John Berman wrote: I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field [snip] Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Wow. I think I had suspected this might be the problem. I don't think there's any way of solving the two problems of really slowing down the search and not knowing which column matched without fundamentally changing your table structure. It sounds like you've got the whole database in one table, which isn't really making use of the strengths of the DBMS. You'd do much better breaking this data out into multiple related tables. You'd still have to do multiple queries while looking for surnames, but the collected queries on the (much smaller) tables would be still be faster than the single query I've quoted above. Without knowing what columns you have in your current table, I can't suggest how you'd break them down, but a good rule of thumb is that each table should contain only one kind of entity. So you probably shouldn't have marriages and individual persons in one table. (Far better to have a table of people, then a table of marriages which describes the relationships? Then you don't have bridesurname and groomsurname fields, just one surname field. You can also describe multiple marriages for a single person with a minimum of table space.) This isn't the place (nor am I the right person, probably) to get into an extended discussion of database normalization, but if you search that phrase on the web you'll find quite a bit. Here's one place to start: http://databases.about.com/ (look under design). Many MySQL books will discuss normalization as well (it's in chapter 7 of the O'Reilly book I have here.) pjm PGP.sig Description: This is a digitally signed message part
Re: secure automated access (was Re: Backing up all databases)
on 11/13/03 3:57 PM, Michael Stassen at [EMAIL PROTECTED] wrote: The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Permissions are correct. Here's a cat of the file with password changed: # cat /private/var/root/.my.cnf [client] password=rootpassword Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? Yes, that's the error. If you don't get in, try mysql --defaults-file=/path/to/.my.cnf That also doesn't work. If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Doesn't work at all. Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- Randall Perry sysTame Xserve Web Hosting/Co-location Website Development/Promotion Mac Consulting/Sales http://www.systame.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Mysql just stopped working on my server - any way to track do wn the problem?
-Original Message- From: Matt Babineau [mailto:[EMAIL PROTECTED] I am running 4.0.15-standard on RH9. My mysql database just stopped working, is there a way I can log information about why it stops like this? the *.err was unhelpful. Did you just install a glibc upgrade/bugfix? That was the culprit when this happened to me on RH7 a while back. I'd upgraded glibc, but hadn't upgraded mysql, which was linked against the old version and didn't like the new one. Why up2date let me get away with that, I don't know, but it did. Frustrated me for *hours*. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: multiple values in one column
Thanks for the reply, I'm getting the message A shorter question I have seen that you can create an index across multiple columns, is there any way one can tell which column returned the results (this would help me short term). Or indeed have more than one word that's indexed in a single column. Regards John Berman -Original Message- From: Parker Morse [mailto:[EMAIL PROTECTED] Sent: 13 November 2003 21:32 To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: multiple values in one column On Thursday, Nov 13, 2003, at 12:03 US/Eastern, John Berman wrote: I have a single table with 120 fields (its full of genealogical data) All the records apart from marriages have an entry in the surname field [snip] Now marriages don't have an entry in surname but they do in groomsurname and bridesurname, I figured if I copied the groom surname and bridesurname to the main surname index that would do the trick ? I did index groomsurname and bridesurname and then use a statement like sql = SELECT COUNT(*) AS res, uniqueref FROM global WHERE surname = ' globsurname ' or groomsurname = ' globsurname ' or bridesurname = ' globsurname 'group by uniqueref This gave me 2 problems, It really slowed down the search and if a result was found I could not detrmine which field it was found in so drilling down was a problem. Wow. I think I had suspected this might be the problem. I don't think there's any way of solving the two problems of really slowing down the search and not knowing which column matched without fundamentally changing your table structure. It sounds like you've got the whole database in one table, which isn't really making use of the strengths of the DBMS. You'd do much better breaking this data out into multiple related tables. You'd still have to do multiple queries while looking for surnames, but the collected queries on the (much smaller) tables would be still be faster than the single query I've quoted above. Without knowing what columns you have in your current table, I can't suggest how you'd break them down, but a good rule of thumb is that each table should contain only one kind of entity. So you probably shouldn't have marriages and individual persons in one table. (Far better to have a table of people, then a table of marriages which describes the relationships? Then you don't have bridesurname and groomsurname fields, just one surname field. You can also describe multiple marriages for a single person with a minimum of table space.) This isn't the place (nor am I the right person, probably) to get into an extended discussion of database normalization, but if you search that phrase on the web you'll find quite a bit. Here's one place to start: http://databases.about.com/ (look under design). Many MySQL books will discuss normalization as well (it's in chapter 7 of the O'Reilly book I have here.) pjm -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems storing SHA1 Hash
Paride De Gasperis wrote: I have a problem storing an SHA1 hash generated as user password for an application... I do the same thing (although not with a MySQL database) and I anticipated problems in storing the binary hash, so I chose to perform a simple Base64 encoding of the hash result first. So I end up storing 28-character strings in the database, like this: v5szlQNp+vVs1xUZqdYPTe9sgzU= I find this technique works very well and the code to perform the Base64 encode/decode is straightforward and easily found on the web. Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge Tables not working...
Hello, I've posted this before, but did not get a fix or find out what I am not doing coreectly. I've established several merge tables. 3 out of the 5 of my merge tables work fine...but when I try to access (e.g. describe or query) the other two, I get the following error: mysql describe hospchar; ERROR 1016: Can't open file: 'employee.MRG'. (errno: 143) mysql I am running MySQL 4.0.15-max-nt on Windows 2000 Professional. Any ideas? Thanks in advance. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Grant permissions problems - help
Nestor Florez wrote:
I actually took a look at the mysql.user table and user nestor has a
password in both entries to the table.
One entry is for localhost and the other is for '%';
I try inserting from the command line and I get:
mysql insert into School (school_desc) values ('Nestor');
Query OK, 1 row affected (0.00 sec)
OK, mysql says it inserted this row with no errors.
Even when I do it as the main root for mysql the record is not
inserted.
Why do you think the row is not inserted?
Do you know how to reset the auto_increment
Depends on what you mean. What exactly do you want to happen to the
auto_increment column?
Nestor :-)
Nestor A. Florez
Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:49:51 AM
Their are two nestor users. One with a password and one without a
password.
The one with connect from anywhere is only allowed to modify the test
database. Fix your privileges and you should be okay.
-Original Message-
From: Nestor Florez [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2003 1:04 PM
To: [EMAIL PROTECTED]; [EMAIL PROTECTED]
Subject: RE: Grant permissions problems - help
+---
--+
| Grants for [EMAIL PROTECTED]
|
+---
--+
| GRANT USAGE ON *.* TO 'nestor'@'%' IDENTIFIED BY PASSWORD
'93c8c803300d29b9' |
| GRANT ALL PRIVILEGES ON `test`.* TO 'nestor'@'%' |
+---
--+
IS the same on both my W2K and my Mac OS X
Nestor :-)
Nestor A. Florez
Victor Pendleton [EMAIL PROTECTED] 11/13/2003 11:01:37 AM
execute show grants for nestor@'%' and see what that user has.
-Original Message-
I forgot to mentioned that this same code works on the same database
on
my W2K
but I am having problems on my Mac OS X (Darwin). I sqldump the
database from my W2K
to my Mac Os X.
Nestor :-)
Nestor A. Florez
Nestor Florez 11/13/2003 10:17:37 AM
I have a mysql db where via a web page I access a database
I have set the permissions to
grant select, insert, update, delete on test.* to [EMAIL PROTECTED]
identified by 'nestor';
flush privileges;
The database has 3 tables. I can select via PHP code all the tables,
but when I try to insert
or update 2 of the tables allow me but there is one table that does
not.
My schools table refuses to insert or update, but my courses_eng and
course_esp
allows me to select, insert, update, delete
I them gave the following command:
grant all privileges on test.* to [EMAIL PROTECTED] identified by
'nestor';
grant all privileges on test.* to nestor@'%' identified by 'nestor';
flush privileges;
I am still not able to insert and update to the school table, but I
can
do this on
the course_eng and course_esp tables.
Any ideas.
Thanks,
Nestor :-)
Nestor A. Florez
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problems storing SHA1 Hash
In the last episode (Nov 13), Paride De Gasperis said:
Hello all,
I have a problem storing an SHA1 hash generated as user password for an
application...
This is the original SELECT Command:
INSERT INTO Auth$ VALUES ('System', '
% 3/4
X;Z^E(xB9?(?U?N~oSSE?$03^O?#^1j?'U'Y??-#N^A?`U[. 6?thth^?',
3)
but the stored values are:
% 3/4
X;Z^E(xB9?(?U?N~oSSE?$03^O?#^1j?'U'Y??-#N^A?`U[. 6?thth^?
this differes from the original in some characters and the password check fails...
Those two strings are identical; I moved the first one onto its own
line so they line up. Make sure the field is not a VARCHAR field (use
a BLOB) and you should be okay.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Format of stopwords file
In the docs is says that you can define your own stopwords file for fulltext searching. The following is under show variables... ft_stopword_file The file from which to read the list of stopwords for full-text searches. All the words from the file will be used; comments are not honored. By default, built-in list of stopwords is used (as defined in `myisam/ft_static.c'). Setting this parameter to an empty string () will disable stopword filtering. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.10) .. However, it doesn't say what format this file should be in. Should it be a text document with one word per line? Is there some other format? Also, is there a way to list the words mysql is currently using as stopwords? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Backslash and full text searches
We make extensive use of full text searches, but have run into some problems
with backslashes. If a word like somebody's is entered into our database,
we escape the string using mysql_escapes_string in php. So,
mysql_escape_string(somebody's) becomes somebody\'s when it is saved in
the database. The problem is, we don't seem to be able to match against
this in the database.
Let's say we saved somebody's in the data base. The following will match
fine and pull up the results expected...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
... But if somebody\'s is stored in the database, there seems to be no way
to match the \. We've tried all of the following...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\\\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
... Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Grant permissions problems - help - WORKS!
Thanks for the help to ALL I got it to work. I do not know how but I think that they might have been hidden characters in the program doing insert in the actuall insert statements. I retyped the insert code in the program and things started to work. Probably when I ftp the code somethin gelse got added. Thanks again, Nestor :-) Nestor A. Florez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inserting Colons in a Table
Hi, I'm trying to load some data into a MySql table, but some of the srtings that I try to load come with colons in them and then I get an error... is it possible to insert colons and special characters into MySql? How? Thanks! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/MyODBC/Access 97 alpha-sorting doesn't work. PLEASE HELP.
I have Mysql 4.0.14 with MyODBC3.51 and Access97. I have a query SELECT inquiry_data_simple.inquiry_id, inquiry_data_simple.organization, inquiry_data_simple.event_date, inquiry_data_simple.event_id FROM inquiry_data_simple ORDER BY inquiry_data_simple.organization; The organization field does not sort alpha. Any ideas what could that be. It is ONLY a problem on WindowsXP home systems. Other systems are just fine. I have relinked the tables, re-installed the MSOffice and MySQL, done tons of other stuff. I have spent two days on this and I have no idea what else to do. - Visit CARMEL MUSIC ENTERTAINMENT website http://carmelme.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Format of stopwords file
Hi Jason, There is no format per-se of the stopword file. The words are parsed in the same way as when they're being indexed. e.g. A word is a sequence of aplhanumeric characters, _ and ' So one line per word (which is how I do it) will work fine. As would separating with spaces, commas, etc. To see what MySQL is currently using as stopwords, you know about the ft_stopword_file variable, right? That tells you the file, if the built-in list isn't being used. The built-in list is defined in myisam/ft_static.c as it says there in the manual. If you want to see the built-in list of words without downloading the source, I can send it to you. :-) Hope that helps. Matt - Original Message - From: Jason Ramsey Sent: Thursday, November 13, 2003 4:08 PM Subject: Format of stopwords file In the docs is says that you can define your own stopwords file for fulltext searching. The following is under show variables... ft_stopword_file The file from which to read the list of stopwords for full-text searches. All the words from the file will be used; comments are not honored. By default, built-in list of stopwords is used (as defined in `myisam/ft_static.c'). Setting this parameter to an empty string () will disable stopword filtering. Note: FULLTEXT indexes must be rebuilt after changing this variable. (This option is new for MySQL 4.0.10) .. However, it doesn't say what format this file should be in. Should it be a text document with one word per line? Is there some other format? Also, is there a way to list the words mysql is currently using as stopwords? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
G5 64Bit Questions again
I posted this before, Subject: 64bit G5 Panther compiles but received no reply. Is there any answer yet for the ability to compile a working, 64bit Mysql on OSX Panther? Would you use GCC compiler flags-mpowerpc-gpopt and -mpowerpc64 ? -- Adam Goldstein White Wolf Networks http://whitewlf.net -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order by a number not leading in zero
I have the following... tSql = SELECT l.team, l.link, lc.category, l.age, l.date FROM league AS l INNER JOIN league_category AS lc ON l.category = lc.id WHERE lc.id = prepSQL(tConn, id) AND l.status = '1' ORDER BY l.age, l.date; L.age has data in it as varchar() where there are some without a leading zero. Rsults end up like 10U 11U 12U 5U 6U 7U I would like to order those as 5U 6U 7U 10U 11U 12U Thanks for the help. -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backslash and full text searches
Hi Jason,
Of course somebody's isn't going to match somebody\'s. :-) somebody\'s
should match somebody\'s -- because it's really matching the somebody
part. If you're using 4+, you can use IN BOOLEAN MODE to match
somebody\'s exactly:
MATCH (col) AGAINST (' somebody\\\'s ' IN BOOLEAN MODE)
However, I get the feeling that you're not really wanting to match a
word that has a backslash in it. e.g. You're getting extra backslashes.
mysql_escape_string()/addslashes() *DOES NOT* store the backslashes in
the table -- it's not even *possible* since they're only escape
characters in the query. If you're getting any extra backslashes when
retrieving data, too many were added during insertion -- you ran
mysql_escape_string/addslashes too many times. This happens if PHP's
stupid magic_quotes_gpc is on and you don't check for that.
Your text should come out exactly the way it was intended. Never, ever
any need for stripslashes(), etc. if it was inserted correctly. :-)
Hope that helps.
Matt
- Original Message -
From: Jason Ramsey
Sent: Thursday, November 13, 2003 4:10 PM
Subject: Backslash and full text searches
We make extensive use of full text searches, but have run into some
problems
with backslashes. If a word like somebody's is entered into our
database,
we escape the string using mysql_escapes_string in php. So,
mysql_escape_string(somebody's) becomes somebody\'s when it is
saved in
the database. The problem is, we don't seem to be able to match
against
this in the database.
Let's say we saved somebody's in the data base. The following will
match
fine and pull up the results expected...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
... But if somebody\'s is stored in the database, there seems to be
no way
to match the \. We've tried all of the following...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\\\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
... Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge Tables not working...
Hi Tony, shell perror 143 Error code 143: Unknown error 143 = Conflicting table definition between MERGE and mapped table Matt - Original Message - From: [EMAIL PROTECTED] Sent: Thursday, November 13, 2003 4:00 PM Subject: Merge Tables not working... Hello, I've posted this before, but did not get a fix or find out what I am not doing coreectly. I've established several merge tables. 3 out of the 5 of my merge tables work fine...but when I try to access (e.g. describe or query) the other two, I get the following error: mysql describe hospchar; ERROR 1016: Can't open file: 'employee.MRG'. (errno: 143) mysql I am running MySQL 4.0.15-max-nt on Windows 2000 Professional. Any ideas? Thanks in advance. Tony -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Order by a number not leading in zero
Hi Scott, This kind of ordering thing has come up before and there's never a great answer I don't think. You could try this ORDER BY ORDER BY l.age + 0.0, l.age, l.date But I think that might cause sorting problems if age has leading 0s. Maybe not... Matt - Original Message - From: Scott Haneda Sent: Thursday, November 13, 2003 5:34 PM Subject: Order by a number not leading in zero I have the following... tSql = SELECT l.team, l.link, lc.category, l.age, l.date FROM league AS l INNER JOIN league_category AS lc ON l.category = lc.id WHERE lc.id = prepSQL(tConn, id) AND l.status = '1' ORDER BY l.age, l.date; L.age has data in it as varchar() where there are some without a leading zero. Rsults end up like 10U 11U 12U 5U 6U 7U I would like to order those as 5U 6U 7U 10U 11U 12U Thanks for the help. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Dirt Slow Query On Datetime Range...the saga continues
Hi Gabriel,
No, the order of the WHERE clause shouldn't have anything to do with how
the query is executed. The only exception may be if MySQL thinks 2
different ways of doing something are of equal cost. In that case it may
choose one or the other depending on how the query is written.
Matt
- Original Message -
From: Gabriel Ricard
Sent: Wednesday, November 12, 2003 10:00 AM
Subject: Re: Dirt Slow Query On Datetime Range...the saga continues
In order to make sure of a multi-column index, you have to order the
WHERE clauses in the same order as the columns in the index. Since you
query Realm first, then AcctStartTime, then AcctStopTime, MySQL would
use an index on those columns in that order. You can either add a
differently ordered index of the same columns or not, just make sure
your query has the columns in the right order.
- Gabriel
On Tuesday, November 11, 2003, at 05:57 PM, Michael Shuler wrote:
Thanks for the quick help everyone...OK I made a few changes
The Query is now:
SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
(Realm =
'testrealm.com') AND ('2003-10-11 16:00:00' BETWEEN AcctStartTime
AND
AcctStopTime)
Which uses the RealmAndStart index (which as you see in the next
line
has
been improved slightly).
And I modified the last key to:
KEY `RealmAndStart` (`Realm`,`AcctStartTime`,`AcctStopTime`)
I also though about it for a while and had an inspirational idea
that
if I
make a key that looks like this:
KEY `StartStopRealm` (`AcctStartTime`,`AcctStopTime`,`Realm`)
It would go even faster because it will narrow down to the records
within
the time frame (which is about 1000 records) and then down by the
realm name
which would result in ~150 records to count. Oddly enough in the
EXPLAIN it
doesn't even consider it as a possible index to use. What gets even
more
odd is that I swapped the BETWEEN and the Realm in the WHERE clause
and then
it decided to use just the plain Realm index...
SELECT COUNT( * ) AS CallCount FROM ServiceRADIUSAccounting WHERE
('2003-10-11 16:00:00' BETWEEN AcctStartTime AND AcctStopTime) AND
(Realm =
'testrealm.com')
Is this a bug and is there a way to force MySQL to use an index that
you
know is a better choice?
Thanks again,
Michael Shuler
-Original Message-
From: Michael Shuler [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 11, 2003 3:44 PM
To: [EMAIL PROTECTED]
Subject: Dirt Slow Query On Datetime Range
OK, I give up. To anyone out there who can help me, please explain
why
this
query runs slower than dirt. The table has about 1,300,000 records
in
it,
which is not supposed to be a big deal for MySQL to deal with. I
have
tried
it with MyISAM and then changed it to InnoDB which made it even
slower
but
at least the rest of my queries can continue and not be blocked.
This
query
takes 30 seconds on a dual 1GHz 1GB RAM RedHat 9 PC. In my opinion
it
should be 10x faster than that at the very least.
This table is used for RADIUS accounting, all I want to do is find
the
peak
utilization port utilization for the day. The only way I have
figured
out
how to do this is take samples every 5 min and store the highest
one.
Here
is the table:
CREATE TABLE `ServiceRADIUSAccounting` (
`RadAcctId` bigint(21) NOT NULL auto_increment,
`AcctSessionId` varchar(32) NOT NULL default '',
`AcctUniqueId` varchar(32) NOT NULL default '',
`UserName` varchar(64) NOT NULL default '',
`Realm` varchar(64) default '',
`NASIPAddress` varchar(15) NOT NULL default '',
`NASPortId` int(12) default NULL,
`NASPortType` varchar(32) default NULL,
`AcctStartTime` datetime NOT NULL default '-00-00 00:00:00',
`AcctStopTime` datetime NOT NULL default '-00-00 00:00:00',
`AcctSessionTime` int(12) default NULL,
`AcctAuthentic` varchar(32) default NULL,
`ConnectInfo_start` varchar(32) default NULL,
`ConnectInfo_stop` varchar(32) default NULL,
`XmitSpeed` varchar(6) default NULL,
`RecvSpeed` varchar(6) default NULL,
`AcctInputOctets` int(12) default NULL,
`AcctOutputOctets` int(12) default NULL,
`CalledStationId` varchar(11) NOT NULL default '',
`CallingStationId` varchar(11) NOT NULL default '',
`AcctTerminateCause` varchar(32) NOT NULL default '',
`ServiceType` varchar(32) default NULL,
`FramedProtocol` varchar(32) default NULL,
`FramedIPAddress` varchar(15) NOT NULL default '',
`AcctStartDelay` int(12) default NULL,
`AcctStopDelay` int(12) default NULL,
PRIMARY KEY (`RadAcctId`),
KEY `UserName` (`UserName`),
KEY `FramedIPAddress` (`FramedIPAddress`),
KEY `AcctSessionId` (`AcctSessionId`),
KEY `AcctUniqueId` (`AcctUniqueId`),
KEY `AcctStartTime` (`AcctStartTime`),
KEY `AcctStopTime` (`AcctStopTime`),
KEY `NASIPAddress` (`NASIPAddress`),
KEY `Realm` (`Realm`),
KEY
RE: Backslash and full text searches
Thanks for your answer, you can see my comments below.
-Original Message-
From: Matt W [mailto:[EMAIL PROTECTED]
Sent: Thursday, November 13, 2003 3:36 PM
To: Jason Ramsey; [EMAIL PROTECTED]
Subject: Re: Backslash and full text searches
Hi Jason,
Of course somebody's isn't going to match somebody\'s. :-) somebody\'s
should match somebody\'s -- because it's really matching the somebody
part.
^ That makes sense to me, and the behavior I would have expected; except, it
doesn't seem to work that way. Anything I try I can't get somebody\'s to
match a field in the database.
If you're using 4+, you can use IN BOOLEAN MODE to match
somebody\'s exactly:
MATCH (col) AGAINST (' somebody\\\'s ' IN BOOLEAN MODE)
^ Unfortunatley, this doesn't work. I've tried several variations, but
can't seem to match useing MATCH, LIKE or even =.
However, I get the feeling that you're not really wanting to match a
word that has a backslash in it. e.g. You're getting extra backslashes.
mysql_escape_string()/addslashes() *DOES NOT* store the backslashes in
the table -- it's not even *possible* since they're only escape
characters in the query. If you're getting any extra backslashes when
retrieving data, too many were added during insertion -- you ran
mysql_escape_string/addslashes too many times. This happens if PHP's
stupid magic_quotes_gpc is on and you don't check for that.
^hmm. Well, that certainly is different than what I understood, and helpful
information. It looks like magic_quotes_gpc is on. However, now I have a
problem that we have a 100,000 entries in the database like this, and
500,000 lines of code expecting the backslash. I really need to find a
solution so that I can search and somehow account for the \ in my
searches. Any help would be appreciated.
Your text should come out exactly the way it was intended. Never, ever
any need for stripslashes(), etc. if it was inserted correctly. :-)
Hope that helps.
Matt
- Original Message -
From: Jason Ramsey
Sent: Thursday, November 13, 2003 4:10 PM
Subject: Backslash and full text searches
We make extensive use of full text searches, but have run into some
problems
with backslashes. If a word like somebody's is entered into our
database,
we escape the string using mysql_escapes_string in php. So,
mysql_escape_string(somebody's) becomes somebody\'s when it is
saved in
the database. The problem is, we don't seem to be able to match
against
this in the database.
Let's say we saved somebody's in the data base. The following will
match
fine and pull up the results expected...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
... But if somebody\'s is stored in the database, there seems to be
no way
to match the \. We've tried all of the following...
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody's)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody's')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST (somebody\\\'s)
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\'s')
SELECT * FROM Table WHERE MATCH (Field) AGAINST ('somebody\\\'s')
... Any ideas?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: secure automated access (was Re: Backing up all databases)
Mysterious. I assume `mysql -p` prompts you for root's password and works as expected, since without -p it says you're not using a password, rather than saying there's something wrong with the password. When you tried the defaults-file option, did you use the full path? That is, mysql --defaults-file=/private/var/root/.my.cnf My best guess at this point is that mysql is not finding .my.cnf. Perhaps there's a problem with root's $HOME environment variable? For example, I'm using Mac OS X 10.2.8. Root's $HOME says /var/root, but as /var is a symlink to /private/var, the real home directory is /private/var/root. That's fine, as long as the symlink is there. Without the symlink, I'd get the same thing as you from mysql (and a lot of other stuff wouldn't work). If this is not the case, using `cat $HOME/.my.cnf` should give the same result you got using the full path. You could also simply `echo $HOME` to see if it's set correctly. The only other thing I can imagine is that mysql is somehow set to ignore the defaults files. Did you build from source or install one of the packages? Which version mysql? Which OS? Randall Perry wrote: on 11/13/03 3:57 PM, Michael Stassen at [EMAIL PROTECTED] wrote: The client (mysql) reads the .my.cnf file when you start it, so restarting mysqld and relogging in as root are not necessary. Let's see if I have this straight: While logged in as root, you created .my.cnf in root's home directory. This file contains the password for the mysql user root. Yes? Did you make sure the file was readable only by root (`chmod 600 .my.cnf` would do the trick)? Permissions are correct. Here's a cat of the file with password changed: # cat /private/var/root/.my.cnf [client] password=rootpassword Since I'm not sure exactly what happened, I don't know why this didn't work for you. When you say nothing works, do you mean you cannot connect via mysql interactively, or your cron job doesn't work? What error did you get? If you haven't already, try running mysql. Do you get in, or do you get ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: NO), or do you get something else? Yes, that's the error. If you don't get in, try mysql --defaults-file=/path/to/.my.cnf That also doesn't work. If that works, then .my.cnf isn't in the right place. If mysql works interactively, but not via cron, then the problem to be fixed lies with cron. What error do you get from cron? Doesn't work at all. Michael Randall Perry wrote: I created the .my.cnf file in root's home dir, added the directives below setting the correct password. Restarted mysqld, re-logged in as root, but nothing works. It's not getting the password. The simplest solution is to keep the password in the .my.cnf file in your home directory. See http://www.mysql.com/doc/en/Option_files.html in the manual. In the case of root cron jobs then, you need a .my.cnf readable only by root in root's home. It should include [client] password=mysql_root_password As mysql reads the .my.cnf file, this avoids the ps sniffing problem, and also keeps the password out of the script. Because you make the .my.cnf file readable only by root, other users can't see it. (If they can see it, you've got bigger problems than just the mysql password). Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
