Beginner - PHP Triad, PHP Amin and MySQL
Dear All, I have downloaded and installed PHP Triad (Apache, MySQL and PHP). I have two problems : 1. I can't see any obvious way of shutting down Apache. I can only seem to do it by closing the DOS session. When I restart Apache I get the '[warn] pid file /apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run ?' warning. How can I shut this down cleanly ? 2. When starting phpmyadmin I get the following warning : 'Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) :\apache\htdocs\phpmyadmin\lib.inc.php on line 255 Error' Thankyou so much in advance if anyone can help. Regards, Anthony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Some amazing query numbers from MySQL 4.0
Some free configuration advice: * You should increase your ``table_cache'' variable, ``Opens'' is much higher than ``Open_tables'' indicating that MySQL is having to close other tables in order to open some for new clients. I've thought about that and even tried a couple different configurations however I seemed to get a worse performance. However that was in early 3.23 stagesperhaps it is time to relook at some of the configuration variablesthanks for the tip =) Anyhow, in the above config, the high count of Threads may lead to an unstable machine particularly on Linux with large per thread caches (there is an equation in the MySQL manual somewhere for this). I'll bet he is using PHP and Apache and has persistent connection on PHP and Keep-Alive on Apache. Actually the machine has been quite stable for me. And although I am using PHP mainly to do all the scripting I need with MySQL I am not using apache at all =) The server is basically a high end data miner for about 20k other servers constantly recording data. The only time I ever experience problems was using a 2.2.x kernel where the MySQL tables kept corrupting on mewhen I switched to 2.4 that problem was resolved. Also when I tried using Slackware 8.0 I took a major performance hitdoing only about 30 QPS the server was having significant problems, when I moved back to 7.1 the problems poofed (quick little FYI for ye ol' slackware users) A quick (partial) fix is to use MM inside PHP so persistent connections act as expected (i.e. shared between processes). Even easier (sloppier) fix is to not use Keep-Alive, use persistent connections, but have the timeout inside mysql be a couple of seconds. That gets a better match between active http processes and mysql threads with the least bit of effort. Since typically the processes always retain their same connection this doesn't apply to me. But thank you very much for the tip. It will be definately something I look at when developing more interactive applications online =) Sincerely, Craig Vincent - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem to log to the database with password.
Hi, I have problem to manage the users of my MySQL server. I connect to the database mysql as superuser and I try to create a new user on another database called PVE. I use : GRANT ALL PRIVILEGES ON PVE.* TO web@'%' IDENTIFIED BY 'PVEweb' WITH GRANT OPTION; The answer is : Query OK, 0 rows affected (0.00 sec) My first question is, is it normal to see 0 rows affected ? When I try to connect to the database as web, I can't use my password. [root@lipve php]# mysql -u web -p Enter password: ERROR 1045: Access denied for user: 'web@localhost' (Using password: YES) But without password it works... Do you know how to solve that problem ? (I already try to make a SET PASSWORD FOR web ... but it didn't change anything) Best regards Nicolas Beaumont Atos Origin - System Integration Tour Les Miroirs 18 Av. d'Alsace 92926 Paris La Défense Cedex France Phone : +33(0) 1 70 92 40 33 e-mail : [EMAIL PROTECTED] Website : www.atosorigin.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
New flavor of Got an error reading communication packets
Greetings - I believe I have a new flavor of this error. I am running mysql Ver 11.13 Distrib 3.23.36, for redhat-linux-gnu (i386) on Red Hat Linux release 7.0 (Guinness) Kernel 2.4.2-2smp on an i686 (the database host) I have two machines running mysql Ver 11.12 Distrib 3.23.32, for redhat-linux-gnu (i386) on Red Hat Linux release 7.0 (Guinness) Kernel 2.2.16-22 on an i586 (the client hosts) The 586 boxes are running ICRADIUS version 0.17b build Feb 1 2001 which (used to) connect through a Perl DBI interface. One of the client machines is connected on a full duplex 100MBps Fast Ethernet network, the second of the two is connected through dual frame relay T1's to our router and then through the Fast Ethernet. When RADIUS starts it continually tries to connect to the 686 database host. The .err file starts generating error messages similar to: 011127 4:39:31 Aborted connection 9961 to db: 'radius' user: 'radius' host: `radius2.aix.cc' (Got an error reading communication packets) Now for the new stuff -- If I attempt to connect to the database host via the mysql command line client with the same username and password it works from both the local and remote hosts. I can see the databases, select the desired database and run queries with expected results. I also use the database host to authenticate my mail users using pam_mysql and this works fine. This machine is running mysql Ver 11.12 Distrib 3.23.32, for redhat-linux-gnu (i386) on Red Hat Linux release 7.0 (Guinness) Kernel 2.2.19pre16ext3 on a 2-processor i686 It is connected on the same switched Fast Ethernet. I've searched the mail archives and found some answers indicating that the full duplex connection is the issue. This setup has worked perfectly for over a year on the existing hardware. We first started having issues with the remote machine (the one on the other end of the frame relay T1's) In an effort to fix that machine I restarted mysql on the database host (several times :-/ ) After the first restart the local machine (the one directly connected to the Fast Ethernet) started generating the same errors. To my knowledge the server and client configurations have not changed. I have power cycled all the affected equipment (database host, client host and the intervening Fast Ethernet Switch) all with no effect. I also tried upping the connect_timeout and net_read_timeout, again, with no effect. In an effort to get my system back up I tried ftp'ing the radius database files from the database host to the client host and use the local server. The local server also generates pretty much the same message: 011127 5:02:25 Aborted connection 211 to db: 'radius' user: 'radius' host: `localhost' (Got an error reading communication packets) I'm pretty sure that I *DON'T* have a network problem as I can telnet among the hosts just fine and I can connect to the web interface of ICRADIUS and see the same sad story in the logs. The ftp of some very large files (52MB and 92MB) ran normally with a consistent 840K/s transfer rate. I'm running out of rabbits here and didn't find the fix in the 223 hits that searching the mail list archives for Error reading communication packets will return. I thought I'd submit this to the list for your consideration. My users will be waking up soon and the are *NOT* going to be happy that they can't login. *ANY* assistance would be greatly appreciated. I'd prefer not to recompile this stuff...after all it worked before and restarted just fine *THREE* times last week...but I won't go into that 1/2g Please reply to [EMAIL PROTECTED] as I am not subscribed to the list. A virtual beverage to the clever person with the clue. Thank you, in advance, for your assistance. Regards Mike PS. I'm off to deja.com to try the same search as suggested in one of the posts to this list. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Yet another can't connect problem
Dennis Herndon writes: Ok, I have read through the manual and archives and still I cannot find this problem I'm having. can't connect to local MySql server through socket ' var/lib/mysql/mysql.sock' (2) Error code 2: No such file or directory Make sure the socket really there. Shouldn't it be /var/... and not var/... ? Oh, and unless your mail system had some sort of a problem, your date is off by almost exactly one day. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem to log to the database with password.
Nicolas BEAUMONT writes: I have problem to manage the users of my MySQL server. I connect to the database mysql as superuser and I try to create a new user on another database called PVE. I use : GRANT ALL PRIVILEGES ON PVE.* TO web@'%' IDENTIFIED BY 'PVEweb' WITH GRANT OPTION; The answer is : Query OK, 0 rows affected (0.00 sec) My first question is, is it normal to see 0 rows affected ? Yes. When I try to connect to the database as web, I can't use my password. [root@lipve php]# mysql -u web -p Enter password: ERROR 1045: Access denied for user: 'web@localhost' (Using password: YES) localhost is a special value not matched by wildcards. You need a separate row in the user table for web@localhost. It might also be wise not to allow access from every computer in the world, unless you have good reason to do so. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How Auto_INCREMENT works
Hi all, I have a question: I made a table with a field that is auto_increment. This field I made to be the primary key. In my opinion an auto_increment field should fill itself, without the intervention of the user. So if I have a table like: f1,f2 --field names, where f1 is auto_increment... than in the insert sql statement I should insert only the f2 field, and mysql should fill the f1 field with the apropriate value. Or I understood wrong the auto_increment property? If so, then what should I do to obtain such a behaviour I mentioned above; if not then what I do wrong, what should I do , or how the insert statement should look? Thank you.. best regards emil Jurj (xenon) :)) _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select into dumpfile
Hi all, For a project in my school, I have to store files into a MySql DB, and to retrieve them to special locations. My problem is that I'm unable to write the files anywhere else than in (something like) /var/lib/mysql (the standard location of MySql). The error message is cannot create/write a file, even if I'm logged as root !!! Is there really a way to write a file in ~/ ? Thanks you, and I hope that someone will give me an idea !!! CU, gluon - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How Auto_INCREMENT works
Hi all, I have a question: I made a table with a field that is auto_increment. This field I made to be the primary key. In my opinion an auto_increment field should fill itself, without the intervention of the user. So if I have a table like: f1,f2 --field names, where f1 is auto_increment... than in the insert sql statement I should insert only the f2 field, and mysql should fill the f1 field with the apropriate value. Exactly. Pass a null value to the auto_increment field, and it will kick off. INSERT INTO tablename (f2) VALUES (xyz); will automatically create values for f1. or INSERT INTO tablename (f1, f2) VALUES (NULL, xyz); will have the same effect. The difference is that in the first example the null value is implicit, while in the second one it is explicit. Bye Giuseppe Maxia Or I understood wrong the auto_increment property? If so, then what should I do to obtain such a behaviour I mentioned above; if not then what I do wrong, what should I do , or how the insert statement should look? Thank you.. best regards emil Jurj (xenon) :)) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
affected rows and found rows
Hi. i'm new to mysql and for now i'm only studying the manual. Studying the paragraph of the myODBC driver i've found the following phrase: The client can't handle that MySQL returns the true value of aected rows. If this ag is set then MySQL returns 'found rows' instead. Then in the section on C API for mySQL i found in the option section: return-found-rows, tell mysql_info() to return found rows instead of updated rows when using UPDATE. I would know in what cases found rows and updated rows could be different: what i think is that if i get no error code the two number would be the same, but if the update fails (example key violation) the two number would be different. i'm rigth ? == dott. Ivano Mario Luberti Archimede Informatica societa' cooperativa a r. l. Via G. Ferraris 12 - 56121- Ospedaletto- Pisa tel.: +39-050-3132300 fax: +39-050-3132301 e-mail: [EMAIL PROTECTED] web: http://www.archicoop.it - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: select into dumpfile
CRAUSAZ Yann writes: For a project in my school, I have to store files into a MySql DB, and to retrieve them to special locations. My problem is that I'm unable to write the files anywhere else than in (something like) /var/lib/mysql (the standard location of MySql). The error message is cannot create/write a file, even if I'm logged as root !!! Who you are logged in as is completely irrelevant, as the OUTFILE/ DUMPFILE file access is done entirely by mysqld and not by the client. Assuming that you have set up the FILE privilege in a safe and sensible way, you just need to consider the file system permissions. Make sure whatever file you try to dump to is really writable by whatever user mysqld runs as. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
affected rows and found rows
I would know in what cases found rows and updated rows could be different: what i think is that if i get no error code the two number would be the same, but if the update fails (example key violation) the two number would be different. Try this one, for a simple case: mysql create table test (id INT not null auto_increment primary key, title char(10)); Query OK, 0 rows affected (0.00 sec) mysql insert into test (title) values (first),(second),(third),(fourth); Query OK, 4 rows affected (0.00 sec) Records: 4 Duplicates: 0 Warnings: 0 mysql select * from test; +++ | id | title | +++ | 1 | first | | 2 | second | | 3 | third | | 4 | fourth | +++ 4 rows in set (0.00 sec) mysql update test set title = second where id 1; Query OK, 2 rows affected (0.00 sec) Rows matched: 3 Changed: 2 Warnings: 0 The query will match THREE rows (id 1), but only TWO will be affected, since one of the matching ones has already the value you wanted to enter. Hope it helps Giuseppe Maxia - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Is converting string to integer possible?
Hi I have 2 text columns VARCHAR 50 and I put a date in them in format mmddhhmmss All working well, but I need to compare this field with the curent date and it doesn't work, as both fields are string. Is there a way to force string to be read as interger (same as CINT in VBScript, but I need to to this in mySQL). Yours Jerry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: affected rows and found rows
Ivano Luberti writes: I would know in what cases found rows and updated rows could be different: what i think is that if i get no error code the two number would be the same, but if the update fails (example key violation) the two number would be different. From the manual: If you set a column to the value it currently has, MySQL notices this and doesn't update it. UPDATE returns the number of rows that were actually changed. In MySQL Version 3.22 or later, the C API function mysql_info() returns the number of rows that were matched and updated and the number of warnings that occurred during the UPDATE. I believe this answers your question. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Beginner - PHP Triad, PHP Amin and MySQL
Hi Anthony, The problem with Apache is because you are running from the command prompt. If you are on NT or equivalent you should start install Apache as a service, then use the service manager to start and stop it. If you must run from the command prompt, then use CTRL-C to close down apache. The second problem looks like you have passwords set for the user root, but are not passing them as part of the login. You might want to set the correct login and password in my.ini. Adam Anthony Evans wrote: Dear All, I have downloaded and installed PHP Triad (Apache, MySQL and PHP). I have two problems : 1. I can't see any obvious way of shutting down Apache. I can only seem to do it by closing the DOS session. When I restart Apache I get the '[warn] pid file /apache/logs/httpd.pid overwritten -- Unclean shutdown of previous Apache run ?' warning. How can I shut this down cleanly ? 2. When starting phpmyadmin I get the following warning : 'Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: NO) :\apache\htdocs\phpmyadmin\lib.inc.php on line 255 Error' Thankyou so much in advance if anyone can help. Regards, Anthony - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Adam Donnison email: [EMAIL PROTECTED] Saki Computer Services Pty. Ltd. 93 Kallista-Emerald Roadphone: +61 3 9752 1512 THE PATCH VIC 3792AUSTRALIAfax: +61 3 9752 1098 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is converting string to integer possible?
Jerry writes: Hi I have 2 text columns VARCHAR 50 and I put a date in them in format mmddhhmmss MySQL does have a DATETIME type, which would probably be better suited to your needs. Is there a way to force string to be read as interger (same as CINT in VBScript, but I need to to this in mySQL). Yes, string_expression+0 will be an integer. It's not pretty, but it works. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is converting string to integer possible?
Isn't it possible to make the collumn an INT.. ??? Or even better, a Date/Time.. ? What you can try thought (but I don'tthink that this will work) is to multiply the column(s) which contains the varchar with 1 when you do the query.. If it should be an varchar.. (can't think of a reason) you can maybe keep a second column with the INT version..? Bye David - Original Message - From: Jerry [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 04:00 Subject: Is converting string to integer possible? Hi I have 2 text columns VARCHAR 50 and I put a date in them in format mmddhhmmss All working well, but I need to compare this field with the curent date and it doesn't work, as both fields are string. Is there a way to force string to be read as interger (same as CINT in VBScript, but I need to to this in mySQL). Yours Jerry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is converting string to integer possible?
Hi I have 2 text columns VARCHAR 50 and I put a date in them in format mmddhhmmss MySQL does have a DATETIME type, which would probably be better suited to your needs. Hm, yes, but I have a forum set to this variable type (Snitz forum also has with VARCHAR not DATETIME). More compatible across different time formats I think. Is there a way to force string to be read as interger (same as CINT in VBScript, but I need to to this in mySQL). Yes, string_expression+0 will be an integer. It's not pretty, but it works. Hm, doesn't work :-( What is wrong here: SQLstmt = SELECT r_date+0 as date, count(*) as count SQLStmt = SQLstmt FROM forum_reply SQLStmt = SQLStmtWHERE topic_ID = topic_ID AND date session(date) Session(date) holds 20011126121222 Is 14 number digit to much to use or what is wrong? Thank you for your help. Yours Jerry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GROUP BY not returning what I expect.
First of all please CC me since I'm no longer a member. I had to unsubscribe since I receive too much already... Sorry about that. I think this is just me and my not so good understanding of the GROUP BY function. What happens is that it returns less lines than I expect it too. Perhaps it has something to do with this line I found in the manual which I don't understand fully probably. manual quote MySQL has extended the use of GROUP BY. You can use columns or calculations in the SELECT expressions that don't appear in the GROUP BY part. This stands for any possible value for this group. You can use this to get better performance by avoiding sorting and grouping on unnecessary items. For example, you don't need to group on customer.name in the following query: mysql select order.custid,customer.name,max(payments) from order,customer where order.custid = customer.custid GROUP BY order.custid; In ANSI SQL, you would have to add customer.name to the GROUP BY clause. In MySQL, the name is redundant if you don't run in ANSI mode. Don't use this feature if the columns you omit from the GROUP BY part aren't unique in the group! You will get unpredictable results. /manual quote first the two tables mysql describe Author; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | PRI | NULL| auto_increment | | FirstName | varchar(40) | YES | | NULL|| | LastName | varchar(60) | YES | | NULL|| +---+--+--+-+-++ 3 rows in set (0.00 sec) mysql select * from Author; ++-+---+ | ID | FirstName | LastName | ++-+---+ | 1 | Ferry | van Steen | | 2 | Erik| Zonneveld | | 3 | Abdel-Karim | Ghribi| | 4 | Miranda | van Loon | ++-+---+ mysql describe News; +---+--+--+-+-++ | Field | Type | Null | Key | Default | Extra | +---+--+--+-+-++ | ID| int(10) unsigned | | PRI | NULL| auto_increment | | news | text | YES | | NULL|| | date | date | YES | | NULL|| | AID | int(10) unsigned | | | 0 || +---+--+--+-+-++ 4 rows in set (0.00 sec) mysql select * from News; ++-++-+ | ID | news | date | AID | ++-++-+ | 1 | NAC heeft Ajax verslagen met een mooie 1-3 | 2000-06-06 | 3 | | 2 | Irak bombadeert Afghanistan na ongelukje met vliegtuig | 2001-01-22 | 1 | | 3 | Politiebericht: veel fiesten gestolen in januari vorig jaar | 2001-03-09 | 2 | | 4 | Financiele markt IT keldert nog steeds | 2001-05-09 | 4 | | 5 | Salarissen in Ziekenhuizen blijven stijgen, zorg personeel heeft niks te klagen | 2001-04-05 | 4 | ++-++-+ So far so good. What I want is to select all authors with their news so: This does what I expect it to, it returns 5 rows mysql select * from Author, News where AID=Author.ID; ++-+---++-++-+ | ID | FirstName | LastName | ID | news | date | AID | ++-+---++-++-+ | 3 | Abdel-Karim | Ghribi| 1 | NAC heeft Ajax verslagen met een mooie 1-3 | 2000-06-06 | 3 | | 1 | Ferry | van Steen | 2 | Irak bombadeert Afghanistan na ongelukje met vliegtuig | 2001-01-22 | 1 | | 2 | Erik| Zonneveld | 3 | Politiebericht: veel fiesten gestolen in januari vorig jaar | 2001-03-09 | 2 | | 4 | Miranda | van Loon | 4 | Financiele markt IT keldert nog steeds | 2001-05-09 | 4 | | 4 | Miranda | van Loon | 5 | Salarissen in Ziekenhuizen blijven stijgen, zorg personeel heeft niks te klagen |
MySQL and .Net
Sir, We are working in .Net environment. We could not use the MySQL database in the .Net platform. The MySQL ODBC (MYODBC) does not work with the OleDBProvider of .Net. We tried with the myOleDB provided by MySQL which is also not working in .Net environment. Please provide us a solution to work with MySQL and .Net regards, Vaijayanthi.M - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: java.sql.SQLException: Cannot disable AUTO_COMMIT exception
Hi, Basically, EJB require RDBMS which support Transaction. Please make sure that your MySQL support Transaction. "Dataware Consulting Inc." wrote: Hi, I am getting "java.sql.SQLException: Cannot disable AUTO_COMMIT" exception when using mySQL with Weblogic EJB (through org.gjt.mm.mysql.Driver JDBC driver). Can anybody help!! -- TAKAHASHI, Tomohiro - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: opinion - voating
Neil Zanella wrote: On Mon, 26 Nov 2001, Ken Kinder wrote: Oracle is certainly more full-featured, but if you know very little about databases, Oracle is not the right choice. I agree. First of all the system requirements are high. For instance the Oracle Universal Installer took something like three hours on a fairly high end PIII with 133MHz FSB! Must have been a pretty lousy system then. I managed to complete an 8i server install on a long obsolete dual-processor SPARCstation10 in about 45 minutes. I've heard rumblings that the Windoze implementation of Oracle is crap when compared to the Unix ones - maybe this is true? Have you ever used the sqlplus command line utility (which is the equivalent of the mysql command line tool or PostgreSQL's psql command line client). Well, sqlplus assumes your terminal is 24x80 even after you resize it. First day on my Oracle course they showed us how to change this (PAGESIZE LINESIZE settings). Granted that the mysql command line client is way better than the Oracle equivalent. Now my other point: Oracle8i is highly non-SQL compliant No it isn't! From the Oracle 8i documentation, appendix B In addition to full compliance at the Entry level, Oracle complies partially at the Transitional, Intermediate, and Full levels as described in Table B-1 (including both SQL-DDL and SQL-DML) [levels defined in ANSI document, X3.135-1992, Database Language SQL. ]. There are a quite a few areas of change, but most peoples problems seem to stem from using Oracle-own feature enhancements. Not sure what the SQL99 compliance is like, as I've not had a chance to get my hands on 9i yet. My last point about Oracle is that it is based on Java (see that JServer stuff when you start sqlplus?) and that is perhaps one of the reason it needs so much RAM. Oh no it isn't! A lot of the support tools use Java, (like all the trendy webby stuff), but the database ain't. As to RAM requirements my little SPARCstation test box only has 128MB of RAM, (sheez even my home PC has more than this), and it runs okay, (that said I wouldn't like to run a production system on this!!). We've got fairly chunky production systems running on Oracle in 512MB of RAM, (not on Intel kit I hasten to point out). Getting back to the original question, I don't think there is such a thing as a 'best' database. I use Oracle, DB2 and MySQL, and each has it's pros and cons. That said, the support on MySQL is just about the best I've seen. It's also about the best on smallish hardware, and master-slave replication is a piece of cake to setup, (unlike Oracle :-( ) Ah well, back to sleep . . . . (oh, and mysql is way quicker to install!!) Regards Bob Cross, DBA and developer. * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * This message is confidential. It may also be privileged or protected by other legal rules. It does not constitute an offer or acceptance of an offer, nor shall it form any part of a legally binding contract. If you have received this communication in error, please let us know by reply then destroy it. You should not use, print, copy the message or disclose its contents to anyone. E-mail is subject to possible data corruption, is not secure, and its content does not necessarily represent the opinion of this Company. No representation or warranty is made as to the accuracy or completeness of the information and no liability can be accepted for any loss arising from its use. This e-mail and any attachments are not guaranteed to be free from so-called computer viruses and it is recommended that you check for such viruses before down-loading it to your computer equipment. This Company has no control over other websites to which there may be hypertext links and no liability can be accepted in relation to those sites. Scottish Newcastle plc Registered in Scotland, Registered Number 16288 Registered Office: 33, Ellersly Road, Edinburgh, EH12 6HX * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Netscape not rendering correctly
At 22:53 2001-11-26 -0600, sherzodR wrote: Lotta peoploe do not use /td and /tr closing tags. It works fine in IE, as it closes them automaticly whenever it sees the opening td and tr. But Netscape turns up its nose. So it should do w/ the HTML syntax, I believe. You could post a link to that page, if you wish, or have HTML validator to validate it for you (available at w3c.org) In HTML 4.01, closing tags for td and tr are optional; the W3C validator won't give you an error if you leave them out - in fact many of the table examples in the standard itself leave out the optional closing tags. On the other hand, it *is* true that Netscape 4.x has problems when these optional closing tags are not present (that's a bug). And since as of XHTML 1.0 all tags must be closed anyway, it would be a good strategy to get used to closing all tags with optional closing tags all the time. Less trouble with browser bugs, more consistent code, everyone gains! -- (database,sql,query,table) GRRR!!! Marjolein Katsma [EMAIL PROTECTED] Java Woman - http://javawoman.com/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is converting string to integer possible?
Hi! On Nov 27, Jerry wrote: Hi I have 2 text columns VARCHAR 50 and I put a date in them in format mmddhhmmss Is there a way to force string to be read as interger (same as CINT in VBScript, but I need to to this in mySQL). Yes, string_expression+0 will be an integer. It's not pretty, but it works. Hm, doesn't work :-( What is wrong here: SQLstmt = SELECT r_date+0 as date, count(*) as count SQLStmt = SQLstmt FROM forum_reply SQLStmt = SQLStmtWHERE topic_ID = topic_ID AND date session(date) Session(date) holds 20011126121222 Is 14 number digit to much to use 14 digit number is ok - MySQL uses longlong internally and can handle up to 9223372036854775807. Of course, your platform should support it, but it, probably, does. or what is wrong? you had to write ... AND date+0 Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FROM clause order matters?
Hi! I have just tried the tet case with 3.23.45 and it failed too. I have tried on 3 different machines, I have tried to compile it myself and tried the RPM's from a MySQL mirror - it makes no difference. Though I have not tried a debug version, only production. BR Ole Dalgaard On tor, 2001-11-15 at 14:01, Sinisa Milivojevic wrote: Hi! I have tested your test case of two SELECT's producing different outputs depending on the table ordering. Those two queries were a problem : -:- SELECT link.titel,link.id FROM link, ord as o1,ord AS o2 WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; ++-+ | titel | id | ++-+ | Bethanias Unge | 404 | ++-+ 1 row in set (0.01 sec) mysql SELECT link.titel,link.id FROM ord as o1,ord AS o2, link WHERE o1.ord like 'bethanias%' AND o2.ord like 'unge%' AND o1.kilde_id=o2.kilde_id AND o1.kilde_id=link.id; Empty set (0.01 sec) -:- I have tested it all with the latest MySQL version and both queries produced the following result: ++-+ | titel | id | ++-+ | Bethanias Unge | 404 | ++-+ So, this must have been some bug that was fixed meanwhile. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyOLEDB problem
Dear members, Second time I am posting my problem to the list I am using mySQL in Windows platform. Through OLEDB I am connecting to the ASP page. For simple queries, it is working fine. If I want to use for outer join means it is giving error Provider could not support asynchronous execution Help me Thanks in advance yours Karthikeyan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Can not start Mysqld on Solaris 2.6 , SPARC
I've installed the binary distribution of mysql 3.23.36 on the Solaris Sparc MAchine when I extract the file mysql-3.23.39-sparc-sun-solaris2.6.tar to /usr/local/mysql-3.23.39-sparc-sun-solaris2.6 when I invoke the script mysql_install_db in ./scripts directory when it try to invoke the mydqld it said the error like this ld.so.1: mysql: fatal: libCrun.so.1: open failed: No such file or directory Killed please send me the solution or comment Thank yoy very much Wattanapol. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: patch: manpage safe_mysql(1)
Thank you for your contribution. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Can not start Mysqld on Solaris 2.6 , SPARC
Solaris 2.6 had the infamous 'tar' bug that caused tar to silently fail when path names exceeded some number of characters - about 125 characters, I think. Make sure you have your 'tar' patch level up to date. Regards, Kevin Wattanapol wrote: I've installed the binary distribution of mysql 3.23.36 on the Solaris Sparc MAchine when I extract the file mysql-3.23.39-sparc-sun-solaris2.6.tar to /usr/local/mysql-3.23.39-sparc-sun-solaris2.6 when I invoke the script mysql_install_db in ./scripts directory when it try to invoke the mydqld it said the error like this ld.so.1: mysql: fatal: libCrun.so.1: open failed: No such file or directory Killed please send me the solution or comment Thank yoy very much Wattanapol. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Is converting string to integer possible?
Sergei Golubchik writes: Is 14 number digit to much to use 14 digit number is ok - MySQL uses longlong internally and can handle up to 9223372036854775807. Of course, your platform should support it, but it, probably, does. That makes me wonder... what happens if longlong is not supported or is less than 64 bits wide? I have no experience of gcc on AmigaOS, but I would expect it to support longlong (heck, even SAS/C does as far as I can remember (don't quote me on that - although I own a copy of it I have hardly used it in a year or more)). On a side note to Jerry, I checked my uptime this morning, and my Amiga1200 has now been running for 59 days without a single reboot. I have yet to see windows do that. :-D And... oh my, it's freaking snowing outside. Aarrghhh! //C - doesn't like cold weather -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: Is converting string to integer possible?
That makes me wonder... what happens if longlong is not supported or is less than 64 bits wide? I have no experience of gcc on AmigaOS, but I would expect it to support longlong (heck, even SAS/C does as far as I can remember (don't quote me on that - although I own a copy of it I have hardly used it in a year or more)). On a side note to Jerry, I checked my uptime this morning, and my Amiga1200 has now been running for 59 days without a single reboot. I have yet to see windows do that. :-D And... oh my, it's freaking snowing outside. Aarrghhh! Just one question!!! How did you know I have an Amiga??? Jerry mySql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
can i do this with sql?
Hi all, I have a table like this: iddatayear 1 201 2 201 6 201 7 201 8 201 11201 What i want to be able to get is these results from an sql query (given that i want id's 1-9): iddatayear 1 201 2 201 3 00 4 00 5 00 6 201 7 201 8 201 9 00 Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt exist in the table. Can anybody give me any clues how to achieve this? Thanks, Jamie Burns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL v4 FTS speed
Hi! On Nov 26, Mike Wexler wrote: I'd like to understand this better. Lets say I have a table with 4 fields itemKey INT(10) unsigned auto_increment primary key status enum(forsale, sold) description TEXT price DECIMAL(10,2) And I do a SELECT * FROM table WHERE MATCH (description) AGAINST (A really nice toy) AND status=forsale ORDER BY MATCH (description) AGAINST (A really nice toy) LIMIT 10 And lets further state that there at 10,000,000 records in the table and 7,500,000 of them are for sale. Lets also say that the minimum word length is set to 3 (so toy is considered significant for the fulltext search) and there is a fulltext index on description. Lets also assume that there are 1,000,000 records with the word really , 1,000,000 records with the word nice and 500,000 records with the word toy. Lets say there are only 500 records with all three words. Approximately how many records will this query need to look at to return a result? Approximately how long will it take? First - fulltext engine does NOT looks at records at all for text search, it builds a list of documents purely from the index. Of course, MySQL can later retrieve actual rows, based on this list. So, I'll answer the question ...how many index entries will this query... The query, as you wrote it, will look at all the 2,500,000 index entries. How would the following query compare: SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy) AND status=forsale ORDER BY MATCH (description) AGAINST (+A +really +nice +toy) LIMIT 10 In 4.0.1 this query will be absolutely identical to the first one. As boolean fulltext search was not documented we took a liberty of changing the syntax slightly. Now it IS documented. The query SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) ORDER BY MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) LIMIT 10 will abort the search when it retrieves the 500th document with all the three words. It's impossible to say, how many index entries it will look at. The query SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) LIMIT 10 will abort the search when it retrieves the 10th document with all the three words. It's impossible to say, how many index entries it will look at. Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i do this with sql?
i belive you want..: SELECT * FROM your_table WHERE id 9 ORDER BY id LIMIT 9; But when it comes to generating missing items... I'm not sure if i got you right though, but they should exist. MySQL cannot make the rows that do not exist Jamie Burns wrote: : Hi all, : : I have a table like this: : : iddatayear : : 1 201 : 2 201 : 6 201 : 7 201 : 8 201 : 11201 : : What i want to be able to get is these results from an sql query (given that : i want id's 1-9): : : iddatayear : : 1 201 : 2 201 : 3 00 : 4 00 : 5 00 : 6 201 : 7 201 : 8 201 : 9 00 : : Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt : exist in the table. : : Can anybody give me any clues how to achieve this? : : Thanks, : : Jamie Burns. : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : -- sherzodR [EMAIL PROTECTED] use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i do this with sql?
i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. j - Original Message - From: sherzodR [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:24 PM Subject: Re: can i do this with sql? i belive you want..: SELECT * FROM your_table WHERE id 9 ORDER BY id LIMIT 9; But when it comes to generating missing items... I'm not sure if i got you right though, but they should exist. MySQL cannot make the rows that do not exist Jamie Burns wrote: : Hi all, : : I have a table like this: : : iddatayear : : 1 201 : 2 201 : 6 201 : 7 201 : 8 201 : 11201 : : What i want to be able to get is these results from an sql query (given that : i want id's 1-9): : : iddatayear : : 1 201 : 2 201 : 3 00 : 4 00 : 5 00 : 6 201 : 7 201 : 8 201 : 9 00 : : Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt : exist in the table. : : Can anybody give me any clues how to achieve this? : : Thanks, : : Jamie Burns. : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : -- sherzodR [EMAIL PROTECTED] use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Yet another can't connect problem
Thanks to another member of the list - I found I had ownership problems, #chown -R root /usr/local/mysql #chown -R mysql /usr/local/mysql/var #chown -R mysql /usr/local/mysql That solved it. And thanks for the heads up on my date. I live in Washington DC, so I'm behind the times :-) -Dennis Carl Troein wrote: Dennis Herndon writes: Ok, I have read through the manual and archives and still I cannot find this problem I'm having. can't connect to local MySql server through socket ' var/lib/mysql/mysql.sock' (2) Error code 2: No such file or directory Make sure the socket really there. Shouldn't it be /var/... and not var/... ? Oh, and unless your mail system had some sort of a problem, your date is off by almost exactly one day. //C - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
newbie. can't run safe_mysqld
Help ??? This command executes and immediately ends: safe_mysqld --log [2] 855 root@linux:/var/lib/mysql Starting mysqld daemon with databases from /var/lib/mysql 04 07:45:16 mysqld ended This command seems to run quite nicely: safe_mysqld --skip-grant-tables Now the manual informs me to From mysql, manually execute the SQL commands in mysql_install_db. 1) Huh ? (I am a Linux newbie - don't know how) 2) Can the mysql_install_db script be troubleshot and corrected ? 3) Will a newer version of MySQL run better on my SuSE Linux 7.1 machine ? Thanks! __ Do You Yahoo!? Yahoo! GeoCities - quick and easy web site hosting, just $8.95/month. http://geocities.yahoo.com/ps/info1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: can i do this with sql?
Hi, In MSQL you can do it a little dirty, create a table with rows with numbers where you want to match against (1, 2, 3, 4, 5, etc) Then do a JOIN WHERE NOT a=b; It's not a very nice one, and I don't know if it works for mySQL too. Greetinx Michaël Hompus Jamie Burns jamie.burns@dynamicexpressTo: sherzodR [EMAIL PROTECTED] ion.co.uk cc: [EMAIL PROTECTED] Subject: Re: can i do this with sql? 27-11-2001 14:28 i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. j - Original Message - From: sherzodR [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:24 PM Subject: Re: can i do this with sql? i belive you want..: SELECT * FROM your_table WHERE id 9 ORDER BY id LIMIT 9; But when it comes to generating missing items... I'm not sure if i got you right though, but they should exist. MySQL cannot make the rows that do not exist Jamie Burns wrote: : Hi all, : : I have a table like this: : : iddatayear : : 1 201 : 2 201 : 6 201 : 7 201 : 8 201 : 11201 : : What i want to be able to get is these results from an sql query (given that : i want id's 1-9): : : iddatayear : : 1 201 : 2 201 : 3 00 : 4 00 : 5 00 : 6 201 : 7 201 : 8 201 : 9 00 : : Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt : exist in the table. : : Can anybody give me any clues how to achieve this? : : Thanks, : : Jamie Burns. : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : -- sherzodR [EMAIL PROTECTED] use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SV: can i do this with sql?
I dont think you can do that, but if you create a table counter with one number column and insert all the numbers from 1 to 10 or what number would be appropriate, then you can left join your_table with counter on the id = number column. select * from counter left join your_table on counter.number = your_table.id order by your_table.id TK -Opprinnelig melding- Fra: Jamie Burns [mailto:[EMAIL PROTECTED]] Sendt: 27. november 2001 14:28 Til: sherzodR Kopi: [EMAIL PROTECTED] Emne: Re: can i do this with sql? i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. j - Original Message - From: sherzodR [EMAIL PROTECTED] To: Jamie Burns [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:24 PM Subject: Re: can i do this with sql? i belive you want..: SELECT * FROM your_table WHERE id 9 ORDER BY id LIMIT 9; But when it comes to generating missing items... I'm not sure if i got you right though, but they should exist. MySQL cannot make the rows that do not exist Jamie Burns wrote: : Hi all, : : I have a table like this: : : iddatayear : : 1 201 : 2 201 : 6 201 : 7 201 : 8 201 : 11201 : : What i want to be able to get is these results from an sql query (given that : i want id's 1-9): : : iddatayear : : 1 201 : 2 201 : 3 00 : 4 00 : 5 00 : 6 201 : 7 201 : 8 201 : 9 00 : : Basically it has made up the rows with id's 3,4, 5 and 9 because they didnt : exist in the table. : : Can anybody give me any clues how to achieve this? : : Thanks, : : Jamie Burns. : : : - : Before posting, please check: :http://www.mysql.com/manual.php (the manual) :http://lists.mysql.com/ (the list archive) : : To request this thread, e-mail [EMAIL PROTECTED] : To unsubscribe, e-mail [EMAIL PROTECTED] : Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php : -- sherzodR [EMAIL PROTECTED] use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and .Net
Vaijayanthi writes: Sir, We are working in .Net environment. We could not use the MySQL database in the .Net platform. The MySQL ODBC (MYODBC) does not work with the OleDBProvider of .Net. We tried with the myOleDB provided by MySQL which is also not working in .Net environment. Please provide us a solution to work with MySQL and .Net regards, Vaijayanthi.M Hi! You will find a link to the OleDB driver on our site. Try it out. We are planning of making a proper support for C# in the future. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL4 and SSL using Java API
The JDBC driver (MM.MySQL) does not currently support SSL, as it only stabilized in version 4 of MySQL (which is still in Alpha test). SSL is in the plans for MM.MySQL, but not high on the list, as most of my users use it in a middleware-type situation, where SSL would not be used. If anyone wants to contribute code to do it, I'd be glad to include it. It should be pretty simple with JSSE. -Mark Hi, Could anyone help me with where I could find more information about how to use SSL and let Java connect to MySQL using SSL?? Thanks, Sebastian - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re[2]: can i do this with sql?
Hi, i actually need the id's with no row returned as if they were there (but with default values)... they do not need to be inserted into the table, just returned as if they were in there. The only way I can think of would be to create a table with all the values (ids) you need, and then use LEFT JOIN to retrieve data... Table: IDS TABLE: IDSDATA id id data year 1 1 2 01 2 2 2 01 3 6 2 01 4 7 2 01 5 8 2 01 6 11 2 01 7 8 9 10 11 12 Then your query would look like this: SELECT IDS.ID, IDSDATA.DATA, IDSDATA.YEAR FROM IDS LEFT JOIN IDSDATA ON IDS.ID = IDSDATA.ID ORDER BY IDS.ID Regards, Sasa - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Too many connections (again) (could Mark answer this please)
I've answered this twice, both in personal e-mails, but I'll answer it
again, one last time :).
Three things, First, you're using an old version of the driver. Please
upgrade to the latest (you should do this when you suspect it might be the
driver. Always check http://mmmysql.sourceforge.net/ as this is
_the_only_official_ MM.MySQL download site. I can't vouch for anything you
download from somewhere else).
Second, you are not closing your connections in finally{} blocks, so you can
not guarantee that they are being closed! Many browsers terminate the
connection to the servlet/JSP early (IE for example), which can cause code
you think should be executing not to execute. You should _always_ get rid of
expensive resources in a finally{} block to make sure that it actually
happens.
Third, please subscribe to the [EMAIL PROTECTED] This forum is not the
correct place to ask these types of questions, and I only get it in digest
mode, so it takes me a while to read/get back to people who ask JDBC
questions in the mysql list.
-Mark
- Original Message -
Message-ID: 000701c17707$7d6a7610$7300a8c0@yilmaz
From: yilmaz [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Subject: Re: Too many connections (again) (could Mark answer this please)
Date: Tue, 27 Nov 2001 13:50:59 +0800
MIME-Version: 1.0
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: 7bit
Me , too, have the same problem.
it seems that every opened page establishes a connection but
those connections can't be closed, although i explicitly close in my code
I posted a message related with this problem a few days ago,
unfortunately couldn't get a satisfying answer. So, i request from Mark
Matthew
to help us with this problem , since he is the author of Mysql.
(my Mysql version is 3.23 , i use jdbc through tomcat 4 b7, on win 2000.)
Thanks in advance
cheers
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
OT (was: Is converting string to integer possible?)
Jerry writes: How did you know I have an Amiga??? I know everything about everybody. I'm god. :-) Or actually, I just looked at the mail headers. But hey, you can still call me god if you want to. ;-D //C - more diabolic than divine -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. mysql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: User Defined Functions.
http://www.mysql.com/doc/A/d/Adding_UDF.html thanks, -- Andrew - Original Message - From: Julio Faerman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 3:11 PM Subject: User Defined Functions. Can i create my own functions with mysql ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
a simple GUI to retrieve and enter MySQL data?
any favorites for a simple gui to retrieve and enter data using MYSQL. i am using redhat linux 7.2 and a beginner - Original Message - From: [EMAIL PROTECTED] To: Barbara Ferrell [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 8:59 AM Subject: Re: a simple GUI to retrieve and enter MySQL data? Your message cannot be posted because it appears to be either spam or simply off topic to our filter. To bypass the filter you must include one of the following words in your message: database,sql,query,table If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: i am using redhat linux 7.2 and a beginner - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Mac OS X and MySQL
alright alright already, i give. i was having a bad day yesterday and some @#$%'er got me started. Paul DuBois has pointed out my statement ...for the last 10 years. This was referencing the PC vs. Mac war that's been going on in every newsgroup for the last ten years. I have had my mailbox flooded with this crap for the last ten years and it appeared to me as though he was attempting to be the next to start it up again . Mailing list/News group you call it what you want. it's the same thing. can we just get back to MySQL and forget my referral to the everlasting Mac OS'? i apologize, sincerely, to the whole newsgroup... ...and hope not to have another day like Monday. -Original Message- From: NDPTAL85 [mailto:[EMAIL PROTECTED]] Sent: Monday, November 26, 2001 5:20 PM To: [EMAIL PROTECTED] Subject: Re: Mac OS X and MySQL John, Mac OS X isn't the same Mac OS that you've been going on for the last 10 years. Its a radical departure. Mac OS 1 thru 9 were basically the same technology. They had no protected memory and no pre-emptive multitasking. Mac OS X is based on NextSTEP. Its a Unix. A real Unix. So just about anything you would have on FreeBSD, NetBSD or OpenBSD you would have on Mac OS X/Darwin. The userland for Mac OS X is taken directly from FreeBSD, albeit an older version and Apple has recently hired the FreeBSD release engineer to help co-ordinate their Unix technologies. So I don't know why you would even presume to judge Mac OS X as crap based on the old Mac OS's history. Its not the same ol Mac OS. Its new and its better. As to the guy's response, I saw nothing in it that was defenseive or even argumentative. If you cannot tolerate someone asking you to clarify your position then you need to get off a mailing list. On Monday, November 26, 2001, at 05:48 PM, john wrote: that's a croc. You invited me and the world as soon as you posted to the web. I asked you a question, you reposted to the net my question so we could start the war on the crappy Mac OS' issue that's been going on for the last ten years to this newsgroup. You're one of those guys that always overload the mailboxes of others, aint you the big man -- coward. Shut up punk, I will speak only to Paul DuBois of my issues. -Original Message- From: Michael Collins [mailto:[EMAIL PROTECTED]] Sent: Monday, November 26, 2001 4:36 PM To: john Cc: [EMAIL PROTECTED] Subject: RE: Mac OS X and MySQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Netscape not rendering correctly
sherzodR wrote: Lotta peoploe do not use /td and /tr closing tags. It works fine in IE, as it closes them automaticly whenever it sees the opening td and tr. But Netscape turns up its nose. So it should do w/ the HTML syntax, I believe. You could post a link to that page, if you wish, or have HTML validator to validate it for you (available at w3c.org) Good luck -- sherzodR On Mon, 26 Nov 2001, Amer Neely wrote: I've read with interest the posts on Netscape not rendering HTML correctly. I can add my experience to that thread, but I use Perl not PHP to generate the HTML. Some very strange things happen with NS 4.77, but IE5 renders just fine. It isn't tables in my case - I've confirmed all table,tr,td, and form tags are matched. The strange thing is it works fine when I run the script on my home computer (WIN98, Apache, perl 5, MySQL 3.23.38). Upload to my ISP and Netscape gives intermittent results - sometimes it will come back with a complete page, other times (for the same query) it will stop partway through the HTML. An earlier post mentioned the possibility of the code having NUL characters in the HTML stream. I'll try to check into that and post the results. If someone can shed more light on this I know a lot of people would appreciate it. Thanks in advance. Here's the address: http://web.softouch.on.ca/mycd.pl Where NS seems to break down is doing either a 'View By' or 'View Tracks'. In 'View By', you get to look for an Artist, Title, or Track. Sometimes it will come up with the result, other times only a partial page. But like I said in the above post, if you view the source code, the HTML is mostly there. Now the twist. Do the same thing again - just hit the back button and choose 'Find It' again. Again, it will either show a complete page, or a partial one. But another 'view source' reveals that it has stopped again - but NOT AT THE SAME PLACE AS THE PREVIOUS request! I've done this up to about 10 times and each time get a different amount of HTML showing up. Of course the whole page is not appearing because it hasn't got to the closing /table tag. But why isn't the whole page being received? This is what boggles me. And besides, if it was an HTML issue, why does the script work fine on my home computer, as I also mentioned in the above post. In 'View Tracks' you get the same behaviour. I'm only posting here because of the MySQL factor. I've been using NS myself for years and know of it's flaky behaviour with tables and style sheets. I'm thinking my ISP may have some initial timing variables for MySQL set too low? -- Amer Neely [EMAIL PROTECTED] Softouch Information Services: www.softouch.on.ca/ Perl / CGI programming for shopping carts, data entry forms. We make web sites work! - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem of MySQL
Hello, My system is Linux Red Hat 6.2... So, Which version of MySQL ( RPM packages ) is good for work with RH 6.2 ? Thank for your help ! Edward. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb: request for change in manual :)))
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi, I've just rolled back half a gig's worth of data. :) it took some time. :) Right now, I know where I made the mistake, but I think if someone just looks through the innodb manual, it might not be so obvious at first. Could the list of commands that cause an implicit commit in innodb tables (section 8.5) be listed in a list format, or a table? Right now they're separated by spaces, saying alter table begin create index drop database drop table rename table truncate lock tables unlock tables, and it's hard to see which commands they really mean. (Or maybe it's just me, I dunno. :))) When I saw create listed there, I didn't read on, to figure out that it was actually part of create index, and no other create commands do this. Right now I've inserted a couple more commits in the dumpfile, just in case. :) Thanks, Attila ... The moment one gives close attention to anything, even a blade of grass, it becomes a mysterious, awesome, indescribably magnificent world in itself. - -Henry Miller, novelist (1891-1980) - --- Public key: http://civ.hu/attila.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8A7ntDeyfLhmXxQwRAo2EAJ4nZIhp3oRF7IJDIo1VG28yW68PdgCfQhuY tRx2Zf/umUjjIdkoPKnb82g= =qE/2 -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL and .Net
Hello Vaijayanthi.M! If you are successfull, please report to this list your solutions... Thanks, Fredrick Sinisa Milivojevic wrote: Vaijayanthi writes: Sir, We are working in .Net environment. We could not use the MySQL database in the .Net platform. The MySQL ODBC (MYODBC) does not work with the OleDBProvider of .Net. We tried with the myOleDB provided by MySQL which is also not working in .Net environment. Please provide us a solution to work with MySQL and .Net regards, Vaijayanthi.M Hi! You will find a link to the OleDB driver on our site. Try it out. We are planning of making a proper support for C# in the future. -- Regards, __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Sinisa Milivojevic [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Fulltime Developer /_/ /_/\_, /___/\___\_\___/ Larnaca, Cyprus ___/ www.mysql.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB
Hello, I've just install mysql 4.23.44. I created an InnoDB Database and add to it 4 tables. I read in the manual that I should get only .frm file for each table and that is what I got. But, now, when I am trying to connect to the Database, mysql still searching the MYD file. What should I do? Thanks Meital - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Manipulation.
On Mon, 26 Nov 2001, Julio Faerman wrote: Is there anything like a to_hours() function ? I need my query to to calculate how much time there is between two time values Julio, ok, we are trying to help you, but please make our lives a little bit easier and tell us exactly what you want to do. I guess you want a difference between two time values and I suppose you mean columns of type TIME. (Using DATETIME would offer a better range). Let's make a table: use test; create table timetest ( number int auto_increment primary key, start time, stop time ); Now we populate this table with some data: INSERT INTO timetest VALUES (NULL,'08:03:02','16:56:28'), (NULL,'19:03:02','11:56:28'); We have: mysql select * from timetest; ++--+--+ | number | start| stop | ++--+--+ | 1 | 08:03:02 | 16:56:28 | | 2 | 19:03:02 | 11:56:28 | ++--+--+ 2 rows in set (0.00 sec) and what you probably want is: mysql select TIME_FORMAT(stop-start,'%T') AS elapsed from timetest; +--+ | elapsed | +--+ | 08:53:26 | | NULL | +--+ 2 rows in set (0.00 sec) As you can see this works for positive intervals only, but we can do: mysql SELECT SEC_TO_TIME(ABS(TIME_TO_SEC(stop)-TIME_TO_SEC(start))) AS elapsed FROM timetest; +--+ | elapsed | +--+ | 08:53:26 | | 07:06:34 | +--+ 2 rows in set (0.00 sec) The result may be formatted in any desired way using the TIME_FORMAT(time,format) function. mysql SELECT TIME_FORMAT(SEC_TO_TIME(ABS(TIME_TO_SEC(stop)-TIME_TO_SEC(start))), '%H hours %i:%s') AS elapsed FROM timetest; ++ | elapsed| ++ | 08 hours 53:26 | | 07 hours 06:34 | ++ 2 rows in set (0.00 sec) I hope that's what you are looking for. Cheers, Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Date Manipulation.
HI ! First of all , thnx a lot for the attention. The EXACT query i need is . assume IN and OUT as date colums in the format -MM-DD HH:MM:SS I need my query to return: How many seconds are there between IN and OUT but outside the period 8:00 to 20:00 For example : IN =2001-10-20 02:30:00 OUT = 2001-10-20 04:00:00 should return 90 (mins) IN =2001-10-20 07:30:00 OUT = 2001-10-20 15:00:00 should return 30 (mins) IN =2001-10-20 23:30:00 OUT = 2001-10-21 00:30:00 should return 60 (mins) IN =2001-10-20 14:00:00 OUT = 2001-10-21 00:30:00 should return 270 (mins) Did i made myself clear ? is it possible to be done with mysql ? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Suggestion: Formatting TimeStamp columns
On Mon, 26 Nov 2001, Brent wrote: A Select statement will display the TmeStamp as 2005095105. Now I ask you, how many users will understand this format? Why not display TimeStamp in the same format as DateTime? At least then the TimeStamp is in a meaningful representation that users can understand. I'm not saying to change the input format. Inputting the TimeStamp data can be kept the same, but it should have a default display format similar to DateTime. The actual format for TimeStamp and DateTime could be stored in the my.cnf or my.ini file so the display format can be adjusted on the server. This format string could be the parameter used in the Date_Format function. What do you think? Changing the default output format is a very bad idea because it will break every client application depending on the current format. The default format has to be controlled by the client software at table creation time and not on a per user basis in the config files. The remaining option I can see is to ADD a new display format and define TIMESTAMP(1) or possibly TIMESTAMP(15) to display a string like a DATETIME column. Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL 3.23 Manual
On Mon, 26 Nov 2001, A. Clausen wrote: Where can I find a copy of the latest MySQL 3.23 manual? All I've been able to find is the newer 4.x. It is part of every source distribution and may be found in the ./Docs directory. Thomas - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANN: Data Architect 2.0
Cross-platform, cross-database, ERD based, data architecting tool. Screenshots http://www.thekompany.com/products/dataarchitect/screenshots.php3 Product Information http://www.thekompany.com/products/dataarchitect/ -- Peter Harvey - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
innodb producing strange error on a heavy-load system
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 hi, any ideas what could be causing this? this happens when apache has reached its max_client limit. using 3.23.45 011127 18:16:30 InnoDB: Started /home/attila/mysql-max-3.23.45-pc-linux-gnu-i686/bin/mysqld: ready for connecti 011127 18:35:45 read_const: Got error 146 when reading table ./h2_2/users 011127 18:35:48 read_const: Got error 146 when reading table ./h2_23/users InnoDB: Warning: a long semaphore wait: - --Thread 12976157 has waited at btr0cur.c line 348 for 127.00 seconds the semap X-lock on RW-latch at 50adcbb8 created in file buf0buf.c line 348 a writer (thread id 12976157) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file ../include/btr0btr.ic line 28 Last time write locked in file btr0pcur.c line 228 InnoDB: Warning: a long semaphore wait: - --Thread 13377598 has waited at ../include/btr0btr.ic line 28 for 121.00 second S-lock on RW-latch at 50adcbb8 created in file buf0buf.c line 348 a writer (thread id 12976157) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file ../include/btr0btr.ic line 28 Last time write locked in file btr0pcur.c line 228 InnoDB: ## Starts InnoDB Monitor for 30 secs to print diagnostic info: thanks, Attila ... There are many kinds of people in the world. Are you one of them? - --- Public key: http://civ.hu/attila.asc -BEGIN PGP SIGNATURE- Version: GnuPG v1.0.6 (GNU/Linux) Comment: For info see http://www.gnupg.org iD8DBQE8A9NADeyfLhmXxQwRAsXdAJ9ZH4It71cQTM1RAOekt4Yo4LC5NgCeOOce /0MvcStxUa459lRpNWLQvfg= =HEaZ -END PGP SIGNATURE- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL - Got Signal 11 - URGENT-RedHat-gcc2.96
Gordan Bobic [EMAIL PROTECTED] writes: On Tuesday 27 Nov 2001 04:11, Ady Wicaksono wrote: gcc 2.96 has bugs that affect MySQL, what bug ? A number of bugs, and they don't just affect MySQL - there is a number of packages that break when compiled using GCC-2.96. Some examples that I presonally ran into at some point are: mPlayer That is known to be mplayer problems. Same problems in their assembly as with gcc 3.0 InterBase DBD driver Haven't seen. There are more, I just can't remember right now. You will find that the kernel, for example, is compiled using kgcc under RedHat v7.X. kgcc is an older version of gcc (egcs, actually) that actually works. Try kgcc --version. RedHat 7.1 and RedHat 7.2 comes with gcc 2.96 And 7.0. 7.2 Also ships with GCC-3.0, which is actually a production release compiler, unlike the 2.96 version, which was a pre-release development snapshot. Uh... gcc 2.96RH is production quality, and the standard compiler. Gcc 3.0 is known to be buggy, and not of production quality. It's not used for anything, it's just a preview (it has more bugs, but it's also closer to the C++ standard than gcc 2.96RH). RedHat have been flamed repeatedly since the release of RH7 over this. By people not knowing what they're talking about. I'm not impressed by idiots who have bugs in their code, won't receive patches and requires you to type in untrue statements about the compiler (mplayer). Just download GCC-2.95.2.1 and the PGCC patches pgcc? Now that's a known good compiler. Not. -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL - Got Signal 11 - URGENT-RedHat-gcc2.96
Sasha Pachev [EMAIL PROTECTED] writes: On Monday 26 November 2001 03:40 pm, Ady Wicaksono wrote: gcc 2.96 has bugs that affect MySQL, what bug ? RedHat 7.1 and RedHat 7.2 comes with gcc 2.96 If we knew exactly what bug we would have given Red Hat a test case to fix it. All we know is that strange things happen under load when MySQL is compiled with 2.96, and they stop happening when the user switches to our binary, which is compiled with 2.91 This changes prototypes and C libraries as well (it's a compat compiler, for producing binaries running on RHL 6.2. It's not used for anything else) -- Trond Eivind Glomsrød Red Hat, Inc. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
inserting from one table into another
I thought the following SQL statement would work (both tables are the same schema): insert into table1 values ( select * from table2 ) ; ...or was I seeing magic posts in the wee hours of the morning? I didn't think that was a subselect, but maybe I am wrong there too. Any help would be greatly appreciated...as I comb thru bitbybit's FAQ regards, T - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
PHP install w/MySQL difference question
Hello, [The contents of this email contain quite an annoying amount of output from the command line, but please note that I have deleted most of the unnecessary output and tried to comment the parts that I felt were necessary.] I am curious as to what the difference will be if I choose to install PHP4 with MySQL functionality, using only the built-in MySQL support. If I specify the /path/to/mysql in my ./configure option --with-mysql, then I get the following output: localhost:~/tmp/apache_mod_php-6-2/php$ ./configure --with-xml --with-zlib --with-apxs=/usr/sbin/apxs --with-mysql=/usr/local/mysql creating cache ./config.cache ... ( for sake of space, most ./configure output omitted ) ... Generating files checking for working mkdir -p... yes creating config_vars.mk updating cache ./config.cache creating ./config.status creating php4.spec creating Zend/Makefile creating main/build-defs.h creating pear/scripts/pear creating pear/scripts/phpize creating pear/scripts/php-config creating TSRM/Makefile creating main/php_config.h creating sapi/Makefile creating ext/Makefile creating Makefile creating pear/Makefile creating main/Makefile creating ext/zlib/Makefile creating ext/mysql/Makefile creating ext/pcre/Makefile creating ext/pcre/pcrelib/Makefile creating ext/posix/Makefile creating ext/session/Makefile creating ext/standard/Makefile creating ext/xml/Makefile creating ext/xml/expat/Makefile creating ext/xml/expat/xmlparse/Makefile creating ext/xml/expat/xmltok/Makefile creating sapi/apache/Makefile creating regex/Makefile creating number.c creating number.h creating main/internal_functions.c ++ | License: | | This software is subject to the PHP License, available in this | | distribution in the file LICENSE. By continuing this installation | | process, you are bound by the terms of this license agreement. | | If you do not agree with the terms of this license, you must abort | | the installation process at this point.| ++ Thank you for using PHP. localhost:~/tmp/apache_mod_php-6-2/php$ But when I 'make' PHP, I get the following warnings (after it is done compiling): *** Warning: This library needs some functionality provided by -lmysqlclient. *** I have the capability to make that library automatically link in when *** you link to this library. But I can only do this if you have a *** shared version of the library, which you do not appear to have. *** Warning: This library needs some functionality provided by -lmysqlclient. *** I have the capability to make that library automatically link in when *** you link to this library. But I can only do this if you have a *** shared version of the library, which you do not appear to have. *** Warning: libtool could not satisfy all declared inter-library *** dependencies of module libphp4. Therefore, libtool will create *** a static module, that should work as long as the dlopening *** application is linked with the -dlopen flag. ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libZend.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libsapi.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libmain.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libregex.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libzlib.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libmysql.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libpcre.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libposix.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libsession.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libstandard.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libxml.al ~/tmp/apache_mod_php-6-2/php/.libs/libphp4.lax/libtsrm.al ~/tmp/apache_mod_php-6-2/php/.libs Making all in pear localhost:~/tmp/apache_mod_php-6-2/php$ So now it seems like I'm in the green -- it says that 'libtool will create a static module' for me. But when I try to 'make install', I get an error. localhost:~/tmp/apache_mod_php-6-2/php$ sudo make install password: ... ( for sake of space, most 'make install' output omitted ) ... Making install in . /Users/eprice/tmp/apache_mod_php-6-2/php/build/shtool mkdir -p /usr/libexec/httpd /usr/sbin/apxs -S LIBEXECDIR=/usr/libexec/httpd -i -a -n php4 libs/libphp4.so [activating module `php4' in /etc/httpd/httpd.conf] cp libs/libphp4.so /usr/libexec/httpd/libphp4.so cp: libs/libphp4.so: No such file or directory apxs:Break: Command failed with rc=1 make[1]: *** [install-sapi] Error 1 make: *** [install-recursive] Error 1 localhost:~/tmp/apache_mod_php-6-2/php$ There seems not to have been a 'libphp4.so' to copy into '/usr/libexec/httpd/libphp4.so'. I posted to this list earlier, asking for advice on making this 'libphp4.so' file, but it seems no one knew the answer. So, now I am wondering what
index question strange behavior
Questions about index mysql show index from listing; +-++-+--+-+- --+-+--+ | Table | Non_unique | Key_name| Seq_in_index | Column_name | Collation | Cardinality | Sub_part | +-++-+--+-+- --+-+--+ | listing | 0 | PRIMARY |1 | my_key | A | 137677 | NULL | | listing | 1 | mls_index |1 | mls_number | A | 137677 | NULL | | listing | 1 | sold_new|1 | sold_new| A | 2 | NULL | | listing | 1 | index3 |1 | city| A | 57 | NULL | | listing | 1 | stat_city_price |1 | stat| A | 45892 | NULL | | listing | 1 | stat_city_price |2 | city| A | 45892 | NULL | | listing | 1 | stat_city_price |3 | price | A | 45892 | NULL | +-++-+--+-+- --+-+--+ 7 rows in set (0.00 sec) stat and city are enum types price is an int. mysql explain select mls_number from listing where stat='A' and city='AH'; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 304 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Question - Why is key len,ref null ?? Is it not using my key? mysql explain select mls_number from listing where stat='S' and city='AH'; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) This is what I expect mysql explain select mls_number from listing where stat='S' and city='AH' and price0 and price50 order by price; +-+--++-+-+- -+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+--++-+-+- -+--+---+ | listing | ref | index3,stat_city_price | stat_city_price | 2 | S,AH | 689 | | +-+--++-+-+- -+--+---+ 1 row in set (0.00 sec) why didn't it use price in the key?? mysql explain select mls_number from listing where (stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 29 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) mysql explain select mls_number from listing where (stat='S' or stat='B') and city='AH' and price0 and price50 order by price; +-+---++-+-+ --+--+---+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +-+---++-+-+ --+--+---+ | listing | range | index3,stat_city_price | stat_city_price |NULL | NULL | 2535 | | +-+---++-+-+ --+--+---+ 1 row in set (0.00 sec) Why didn't mysql use the key and 689 +29 != 2535 for rows?? stat and city are enum types price is an int Any pointers would be helpful trying to speed up queries they currently take about 16 sec table has 134000 rows. Thanks, Rick [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try:
Re: inserting from one table into another
...I hate when this happens, I found it. For those who didn't know: insert into table1 select table2.* from table2; I know, I should be column specifc to accomodate table schema changes later in life, but this was a test. T On Tuesday 27 November 2001 12:46 pm, Tony wrote: I thought the following SQL statement would work (both tables are the same schema): insert into table1 values ( select * from table2 ) ; ...or was I seeing magic posts in the wee hours of the morning? I didn't think that was a subselect, but maybe I am wrong there too. Any help would be greatly appreciated...as I comb thru bitbybit's FAQ regards, T - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C and MysQL
Hello ALL
I'm making a program that will read a file that haves more or less 500.000 lines.
The program filter this lines and record the data in a MySQL database. It will
record more or less 500.000 records.
I am reading the data of an archive text, sequentially, more or less
like this:
while (fgets(linha, 255, arq) != NULL)
{ / / I break the string in the correct fields
strsep( .
/ / I record in the table... (insert into tablex values())
}
If my program only reads and process the data and dont try to insert it to the db,
the program runs ok.
But if I make it insert the data to the database, the mysqld process stills growing up
until the machine crashes.
I tried too, to use the insert delayed but it doesn't solved.
Then I change my program to write each 1000 records to a text file and execute the
LOAD DATA FROM FILE, but mysqld
stills crashing the machine.
In all the tests, the number of inserted records was around 97000.
Does anyone has some ideia of what I am making wrong? Or some ideia of
other modes to insert?
Sorry my bad English...
Thanks very much,
Wilson
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
C and MySQL
Hello ALL
I'm making a program that will read a file that haves more or less 500.000 lines.
The program filter this lines and record the data in a MySQL database. It will
record more or less 500.000 records.
I am reading the data of an archive text, sequentially, more or less
like this:
while (fgets(linha, 255, arq) != NULL)
{ / / I break the string in the correct fields
strsep( .
/ / I record in the table... (insert into tablex values())
}
If my program only reads and process the data and dont try to insert it to the db,
the program runs ok.
But if I make it insert the data to the database, the mysqld process stills growing up
until the machine crashes.
I tried too, to use the insert delayed but it doesn't solved.
Then I change my program to write each 1000 records to a text file and execute the
LOAD DATA FROM FILE, but mysqld
stills crashing the machine.
In all the tests, the number of inserted records was around 97000.
Does anyone has some ideia of what I am making wrong? Or some ideia of
other modes to insert?
Sorry my bad English
Thanks very much,
Wilson
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
data types for calculated columns
Hi, Here is how to reproduce my problem. 1 create table t1(a bigint); 2 insert into t1(a) values(2); 3 create table t2(b double); 4 insert into t2(b) values(3); 5 select t1.a/t2.b as c from t1,t2; +--+ | c| +--+ | 0.67 | +--+ 6 create table t3 select t1.a/t2.b as c from t1,t2; 7 select format(c,16) from t3; +--+ | format(c,16) | +--+ | 0.70 | +--+ Now you see 2/3 becomes 0.7. How can I keep the double precission here? thanks Larry Weng - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Connection limit per user
Hi All. Does anybody know how can I limit maximun connections number to mysqd for individual user, not for all users in sum, as it done by the variable max_connection ? I suppose it is nonstandard feature, and may be a patch for mysql is available ? -- regards Roman. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL - Got Signal 11 - UPDATE
Hello All, I believe I've found a reason for and a fix to a problem I've been experiencing for some time now. My MySQL server has been restarting frequently - once, twice, even three times a day for the past few months. If I shutdown MySQL and run myisamchk on the tables, I'd get: myisamchk: MyISAM file database/table.MYI myisamchk: warning: ## clients is using or hasn't closed the table properly Where ## could be anywhere from 1 to 2000, and this happened on almost every table. Looking at the error log, I also saw a lot of these: 011127 8:36:27 Aborted connection 5992 to db: 'database' user: 'root' host: `localhost' (Got an error reading communication packets) Usually happening every few minutes. After about 3-4 of them, the error log would say that mySQL suddenly got signal 11 and would restart without warning. The box was used exclusively for a large, cumbersome PHP application. The PHP application periodically restarts Apache for changes to its configuration file to take effect. This is where the problems all stem from. The PHP runs a sudo command to apachectl restart. When someone was surfing some of the hosted sites using MySQL, and Apache just restarted immediately, the PHP would not reach the end of its script and terminate its connection to MySQL. So, after a while, these dead connections accumulated, tables weren't being closed properly, and there were frequent errors when browsing the sites. I believe this caused corrupted indexes, and MySQL would attempt to restart. I changed the PHP-Sudo command to run apachectl graceful instead, allowing PHP scripts to finish completely and close their connections correctly. Since this morning, when this change was made, I have not had a single error for a couple hours. In the past, I would be expecting errors at least every few minutes. Now, here's ANOTHER possible problem that we fixed. We noticed that if there was a high CPU usage (99-100%), MySQL would restart about every 15 seconds. We also noticed that there were some queries that were not defined as being slow but they DID take up 50-60% CPU every time the queries were run. We looked at the tables, noticed that there were some useless indexes, and re-indexed one of our main tables, and now, MySQL remains below 12% at all times - usually below 2%. The pages load faster and MySQL is generally a lot faster overall. The trick was simply to index columns used frequently in WHERE conditions in queries. My two cents - hope it helps someone. - Jonathan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connection limit per user
In the changes to 3.23.34 http://www.mysql.com/doc/N/e/News-3.23.34.html o Added option max_user_connections to mysqld. and in the show variables doc http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html o max_user_connections The maximum number of active connections for a single user (0 = no limit). so this feature is readily available depending on which version of mysql you are using. thanks, -- Andrew - Original Message - From: Roman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:43 PM Subject: Connection limit per user Hi All. Does anybody know how can I limit maximun connections number to mysqd for individual user, not for all users in sum, as it done by the variable max_connection ? I suppose it is nonstandard feature, and may be a patch for mysql is available ? -- regards Roman. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem moving database datadir
Hello All, Hoping someone can point where I may have gone wrong on setting our client's databases within their own respective home directories. Here is one of the setups dir tree and settings: The database 'tracers' is symbolically linked from the datadir to the client's home directory. But for some reason, I keep getting the error message that the tables are 'read only'...! what did I go wrong here?? How can I get MySQL to read/write to the database in the client's home directory?? I thought I had all the owner/grp settings correct. Thanks mysql sql database. # client home dir /home/tracerta/tracers drwxrwxrwx2 tracerta mysql4096 Nov 25 22:57 ./ drwx--x--x 13 tracerta tracerta 4096 Nov 27 12:45 ../ -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 customers.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 customers.MYI -rw-rw-rw-1 tracerta mysql9010 Nov 25 22:57 customers.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 log.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 log.MYI -rw-rw-rw-1 tracerta mysql8610 Nov 25 22:57 log.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 membership.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 membership.MYI -rw-rw-rw-1 tracerta mysql8644 Nov 25 22:57 membership.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 onlinetag.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 onlinetag.MYI -rw-rw-rw-1 tracerta mysql8592 Nov 25 22:57 onlinetag.frm -rw-rw-rw-1 tracerta mysql 340 Nov 25 22:57 product_id.MYD -rw-rw-rw-1 tracerta mysql3072 Nov 25 22:57 product_id.MYI -rw-rw-rw-1 tracerta mysql8700 Nov 25 22:57 product_id.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 recovery.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 recovery.MYI -rw-rw-rw-1 tracerta mysql8990 Nov 25 22:57 recovery.frm -rw-rw-rw-1 tracerta mysql 14904 Nov 25 22:57 retailtag.MYD -rw-rw-rw-1 tracerta mysql 19456 Nov 25 22:57 retailtag.MYI -rw-rw-rw-1 tracerta mysql8616 Nov 25 22:57 retailtag.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 tags.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 tags.MYI -rw-rw-rw-1 tracerta mysql8652 Nov 25 22:57 tags.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 temptag.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 temptag.MYI -rw-rw-rw-1 tracerta mysql8602 Nov 25 22:57 temptag.frm # dir /var/lib/mysql/var drwx--2 mysqlmysql1024 Jul 30 19:32 jytinter_db/ -rw-r--r--1 mysqlmysql2276 Nov 14 09:56 my.cnf drwx--2 mysqlmysql1024 Jul 31 08:09 mysql/ -rw-rw-r--1 mysqlmysql 65962 Nov 27 13:51 mysql.log -rw-rw1 mysqlmysql 4 Nov 26 22:51 mysql.pid -rw-rw-r--1 mysqlroot 6450 Nov 26 22:51 mysql_log.err -rw-rw1 mysqlmysql 54739 Nov 26 22:51 mysql_update.log drwx--2 mysqlmysql1024 Jul 30 19:31 referra1/ lrwxrwxrwx1 mysqlmysql 22 Nov 25 23:00 tracers - /home/tracerta/tracers/ Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[Fwd: Re: No Data Truncation warning with MM JDBC driver]
Is it planned for this to be addressed in a future MySQL version?
Original Message
Subject: Re: No Data Truncation warning with MM JDBC driver
Date: Tue, 27 Nov 2001 12:15:08 -0600
From: Mark Matthews [EMAIL PROTECTED]
To: Steve Pellegrino [EMAIL PROTECTED]
References: [EMAIL PROTECTED]
This is a shortcoming with MySQL, as it reports warnings as a string,
which
varies from version-to-version and language-set to language-set. There
is no
way for the driver to reliably retrieve warnings from MySQL :(
I've tried to support this in the past and it never worked reliably.
Ask Monty to add it to the protocol, and I'll support it. For now, I'll
add
it to the known issues in the README file.
Thanks,
-Mark
- Original Message -
From: Steve Pellegrino [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, November 27, 2001 12:12 PM
Subject: No Data Truncation warning with MM JDBC driver
Hello,
When attempting to insert data into a VARCHAR field that exceeds the
length of that field, the JDBC is supposed to raise a DataTruncation
SQLWarning. This is not happening with the MM JDBC driver. I didn't
see it
as a documented bug. I've verified that the underlying MySQL database
is
indeed reporting a warning when data is truncated during an insert.
My configuration is:
MySQL database version 3.23.33 using MM JDBC driver 2.0.7.
Java 1.3.1.
// Ensure single row inserts produce warnings
mysql SET SQL_WARNINGS=1
// Create table syntax:
mysql CREATE TABLE test (name VARCHAR(5) NOT NULL PRIMARY KEY)
// Test valid data:
mysql INSERT INTO test VALUES ('valid');
Query OK, 1 row affected (0.00 sec)
// Long data inserted with truncation:
mysql INSERT INTO test VALUES ('this is too long');
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 1
// Show truncation:
mysql select * from test3;
+---+
| name |
+---+
| inval |
| valid |
+---+
2 rows in set (0.00 sec)
Of course, I'm executing the above INSERT statements from within my Java
program using Statement.executeUpdate(). I never get the DataTruncation
(or
any SQLException, for that matter) on the second INSERT.
Is this a known problem? Is there a workaround?
Best Regards,
Steve Pellegrino
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
How do temporary tables die?
How do tamporary tables die? Is there a time limit, og do I have to drop them manually? -- Morten Dreier http://www.pobox.com/~mdreier/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do temporary tables die?
How do tamporary tables die? Is there a time limit, og do I have to drop them manually? They are dropped when the client connection that created them disconnects. They can also be dropped manually. Ryan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SOURCE command somewhere in manual?
Hi. On Tue, Nov 20, 2001 at 09:07:58AM +0800, [EMAIL PROTECTED] wrote: The bitbybit.dk MySQL FAQ says: How do I use a batch file with MySQL? From within the MySQL client (v. 3.23.9 and above): mysql SOURCE filename Is there really a SOURCE command? An explanation why you did not simply try it, would be nice. Maybe that would have helped to get an early answer. Where is it in the official manual? This is part of the command line client, the belonging manual section is http://www.mysql.com/doc/m/y/mysql.html Also, if you have mysql set @var = xyz; mysql SOURCE filename can @var be used in filename? I am not certain about what you mean, but I presume you mean whether a command within the file may make use of @var? If so, the answer is yes. SOURCE runs the commands in 'filesname' just as if you had typed them by hand. It should be the same as if you would copypaste the commands from an editor to the client. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
SELECT INTO LCOAL OUTFILE
Hi, Is there a reason why, if I can LOAD DATA LOCAL INFILE 'file' INTO TABLE fred; why I can't SELECT * INTO LOCAL OUTFILE '/tmp/file.txt' FROM fred I know the data has to travel to the client, but it would make scripting easier! Quentin Bennett Transport Systems Division Infinity Solutions web http:\\www.infinity.co.nz mailto:[EMAIL PROTECTED] Phone : +64 9 358 9720 Fax : +64 9 309 4142 The information contained in this email is privileged and confidential and intended for the addressee only. If you are not the intended recipient, you are asked to respect that confidentiality and not disclose, copy or make use of its contents. If received in error you are asked to destroy this email and contact the sender immediately. Your assistance is appreciated. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Exporting Data
Hi. On Mon, Nov 19, 2001 at 11:32:58PM -0600, [EMAIL PROTECTED] wrote: Hello, Sorry about the OT, but I need to export data from various MySQL tables and import into Excell spread sheets. Was hoping someone could suggest the best format, TAB, CSV..etc delimited, which is the preferred format? Since Excel can read handle the listed formats and MySQL can export all of them, I see nor preference. I would use CSV because that is IMHO most common for transferring database content to non-SQL applications (and will also work if you need it in another app than Excel). mysqldump will do the work. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
[OT] Comments about my DB layout
Good afternoon, This is my first post on that list, I was looking for relational database mailing list, and found none. So I will post my question here since my database will be under mySQL and users on this list probably have good experiece with DB design. I designed my database, and before all coding the interface, I'd like some experts to have a quick look to it to at least tell me if there are major mistakes in the design. I read Database design for mere mortals a little while ago and this is my first attempt in a real-world and complex (well compared to what I done before) DB. I attached an image copy of the file made with Visio. Visio is made for Access, I managed to get the primary and foreign keys to be as my original sketches (playing with weak and strong tables and identifying or not links) So for the ones interested in my structure, let me know any questions you may have regarding the way everything works and any comments you have. Thanks a lot, Etienne -- Etienne Marcotte Specifications Management - Quality Control Imperial Tobacco Ltd. - Montreal (Qc) Canada 514.932.6161 x.4001 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: SELECT INTO LCOAL OUTFILE
At 8:28 AM +1300 11/28/01, Quentin Bennett wrote: Hi, Is there a reason why, if I can LOAD DATA LOCAL INFILE 'file' INTO TABLE fred; why I can't SELECT * INTO LOCAL OUTFILE '/tmp/file.txt' FROM fred Because it's just never been implemented. I know the data has to travel to the client, but it would make scripting easier! Quentin Bennett Transport Systems Division Infinity Solutions web http:\\www.infinity.co.nz mailto:[EMAIL PROTECTED] Phone : +64 9 358 9720 Fax : +64 9 309 4142 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
making a dsn-LESS connection
I wish to have my asp pages (on an NT 4.0 iis 4.0 machine) connect to a mysql database. I want to have the DSN-LESS type of a connection. This DSN version works: ConnectionString = DSN=myDSNname;User Id=myuserid;Password=aword; However this DSN-less version does not: ConnectionString = Provider=MySQL;datasource=localhost;Initial Catalog=acatalog;User Id=myuserid;Password=aword; The error number and description is this: 3706 : ADO could not find the specified provider Is this error saying that I dont have the NAME right? or is it that I don't actually have the provider (the ole db or whatever dll etc) on my machine ? More to the point, does anyone know what the correct connection string value should be? thanks Harry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: urgent questions need your help!!
Hi. There is no straightforward method to recover deleted entries. The backup (of course, you have backups, don't you) will be your best friend. Theoretically, there is a chance to recover deleted entries, because they are not really deleted, but only put in a list of records to be reused. But it will be a lot of work and you would have to be familiar with the low-level table format. If the content is very important to you, you may want to consider to make use of the payed support (http://www.mysql.com/doc/S/u/Support.html). Bye, Benjamin. On Mon, Nov 19, 2001 at 09:44:42PM -0800, [EMAIL PROTECTED] wrote: hi,everyone. i have wrong deleted the records from file; use the sql : delete from test where treeid like '%'; who knows how i can restore the records from the MYD file. i need your help. sincerely, zhangyun -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: problem in SQL
Hi. On Tue, Nov 20, 2001 at 11:38:01AM +0530, [EMAIL PROTECTED] wrote: Hello, I have a problem related SQL Query SELECT a.cAction_Name,a.nAction_ID FROM tbl_actionmaster a LEFT JOIN tbl_roleactionrelation b ON a.nAction_ID != b.nAction_ID WHERE b.nRole_ID = 3 group by a.cAction_Name In this query i wants to find Action name and Action Id from the Action Master table. As comparing with RoleActionReation table (field - nAction_ID , nRole_ID ) I need those Action Name which are not mapped with the Role Name . [...] To which part in the above query refers role name to? Note that a != b will match _every_ row, where a != b, i.e. if you have ids ranging from 1 to 3, you will get (1,2), (1,3), (2,1), (2,3), (3,1), (3,2). This is the inverse set to a = b, which is (1,1), (2,2), (3,3). If I understand you correctly, you want something like SELECT a.cAction_Name, a.nAction_ID FROM tbl_actionmaster a LEFT JOIN tbl_roleactionrelation b ON a.nAction_ID = b.nAction_ID AND b.nRole_ID = 3 WHEREb.nAction_ID IS NULL GROUP BY a.cAction_Name Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Error List
Hi. I am not sure, but I believe there is no such list. You can use perror to check what an error is about, e.g. shell perror 11 Error code 11: Resource temporarily unavailable Bye, Benjamin. On Tue, Nov 20, 2001 at 11:49:42AM +0530, [EMAIL PROTECTED] wrote: hello I wants to Know of web site where's i found the list of errors mysql have. [...] -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Connection limit per user
Thank you very much. It's a real solution for me!!! In the changes to 3.23.34 http://www.mysql.com/doc/N/e/News-3.23.34.html o Added option max_user_connections to mysqld. and in the show variables doc http://www.mysql.com/doc/S/H/SHOW_VARIABLES.html o max_user_connections The maximum number of active connections for a single user (0 = no limit). so this feature is readily available depending on which version of mysql you are using. thanks, -- Andrew - Original Message - From: Roman [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 1:43 PM Subject: Connection limit per user Hi All. Does anybody know how can I limit maximun connections number to mysqd for individual user, not for all users in sum, as it done by the variable max_connection ? I suppose it is nonstandard feature, and may be a patch for mysql is available ? -- regards Roman. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Re: undelete records ?
Hi. On Tue, Nov 20, 2001 at 09:26:51AM +, [EMAIL PROTECTED] wrote: Just to clarify; My understanding now, If I delete row 14 (example) the next time I create any data in my database it will be put in the now slack space that used to contain row 14 ? if it does not entirely fit in this space, the data that did not fit will be put in the next empty / slack space in my databases files, this will continue until I have no more slack / empty space, then the file will get grow ? eg if I create a 200 Meg database, then delete almost all the contents it will not reduce in size on my disk. If I run optimize table or another such command it will clear the data from the disk and my database will reduce in size (disk space). Am I now correct ? Yes, I think so. Also will Mysql clear the slack space / optimize my database (not sure of wording) :) after a set length of time ? No. or must I do this manually due to the length of time it must take ? [...] Yes. You only need to do this, if you notice performance problems, or you want to reclain the disk space. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: making a dsn-LESS connection
Hi Harry, Your ADO connection string should look something like this: Provider=MySqlProv;Data Source=yourdatabasename;Password=yourpassword;User ID=youruserid;Location=localhost Easiest way to build it is to createan empty text file on your desktop. Rename it 'Test.udl'. Double Click it. Select the 'Connection' tab. Select the MyOLEDB Provider for MySQL Click 'Next' Enter the db name and location. Click Test Connection. If it works, click OK, and open the UDL file with notepad. The third line is your connection string. Good luck, Bruce - Original Message - From: GRC [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, November 27, 2001 9:43 PM Subject: making a dsn-LESS connection I wish to have my asp pages (on an NT 4.0 iis 4.0 machine) connect to a mysql database. I want to have the DSN-LESS type of a connection. This DSN version works: ConnectionString = DSN=myDSNname;User Id=myuserid;Password=aword; However this DSN-less version does not: ConnectionString = Provider=MySQL;datasource=localhost;Initial Catalog=acatalog;User Id=myuserid;Password=aword; The error number and description is this: 3706 : ADO could not find the specified provider Is this error saying that I dont have the NAME right? or is it that I don't actually have the provider (the ole db or whatever dll etc) on my machine ? More to the point, does anyone know what the correct connection string value should be? thanks Harry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Could mysql count the datatraffic of DB's posted queries?
Hi all, I'm looking for a posibility to count the datatraffic of a database at my mysqldb-server. The clients should got only select-rights on thier databases. And I need to count the resultset datatraffic of their select-queries. I'm using mysql version 3.23.39. I was looking for some logfiles entries at the logging mode, but there were only the queries in the HOSTNAME.log file. Not the size of the resultsets. What could I do to reach my aim. I hope somebody know some good tutorials in the web, or could write me a posibility to reach that. Thank you in advance Perica Markovic - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: making a dsn-LESS connection
I believe your provider must be a valid db drivermake sure it exists on your machine. you may want to check out www.4guysfromrolla.com they have a great section on asp dsn/dsn-less connections. Hope it helps. Darren S. Ball Software QA Engineer Lucent Technologies - InterNetworking Systems 1 Robbins Road Westford, MA U.S.A 01886 Nothing fancy and nothing Microsoft -Original Message- From: GRC [mailto:[EMAIL PROTECTED]] Sent: Tuesday, November 27, 2001 2:44 PM To: [EMAIL PROTECTED] Subject: making a dsn-LESS connection I wish to have my asp pages (on an NT 4.0 iis 4.0 machine) connect to a mysql database. I want to have the DSN-LESS type of a connection. This DSN version works: ConnectionString = DSN=myDSNname;User Id=myuserid;Password=aword; However this DSN-less version does not: ConnectionString = Provider=MySQL;datasource=localhost;Initial Catalog=acatalog;User Id=myuserid;Password=aword; The error number and description is this: 3706 : ADO could not find the specified provider Is this error saying that I dont have the NAME right? or is it that I don't actually have the provider (the ole db or whatever dll etc) on my machine ? More to the point, does anyone know what the correct connection string value should be? thanks Harry - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL v4 FTS speed
Lets say I have a table with 4 fields itemKey INT(10) unsigned auto_increment primary key status enum(forsale, sold) description TEXT price DECIMAL(10,2) And I do a SELECT * FROM table WHERE MATCH (description) AGAINST (A really nice toy) AND status=forsale ORDER BY MATCH (description) AGAINST (A really nice toy) LIMIT 10 And lets further state that there at 10,000,000 records in the table and 7,500,000 of them are for sale. Lets also say that the minimum word length is set to 3 (so toy is considered significant for the fulltext search) and there is a fulltext index on description. Lets also assume that there are 1,000,000 records with the word really , 1,000,000 records with the word nice and 500,000 records with the word toy. Lets say there are only 500 records with all three words. Approximately how many records will this query need to look at to return a result? Approximately how long will it take? First - fulltext engine does NOT looks at records at all for text search, it builds a list of documents purely from the index. Of course, MySQL can later retrieve actual rows, based on this list. So, I'll answer the question ...how many index entries will this query... The query, as you wrote it, will look at all the 2,500,000 index entries. Ok. That is what I thought. How would the following query compare: SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy) AND status=forsale ORDER BY MATCH (description) AGAINST (+A +really +nice +toy) LIMIT 10 In 4.0.1 this query will be absolutely identical to the first one. As boolean fulltext search was not documented we took a liberty of changing the syntax slightly. Now it IS documented. So +, - etc don't do anything unless you switch to boolean mode. The query SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) ORDER BY MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) LIMIT 10 will abort the search when it retrieves the 500th document with all the three words. It's impossible to say, how many index entries it will look at. Where does the number 500 come from? Did you mean 10? The query SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) LIMIT 10 will abort the search when it retrieves the 10th document with all the three words. It's impossible to say, how many index entries it will look at. Regards, Sergei - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql question and sql request. Are they the same?
Hi. On Tue, Nov 20, 2001 at 02:23:31PM +0100, [EMAIL PROTECTED] wrote: Hei I asked this question sometime ago but I did not get any answer :-( I think the problem is that people are not sure what you are referring to (includes me). and I can not find any explanation of this in the mysql manual. Could anybody explain me the different between a question and a sql-request in mysql?. F.example: If I send a sql-request to mysql: select * from Table where column_A=1 and column_B=2 order by column_C; num.sql_request = 1 and num.questions = 1 Is this true? Can I send 1 sql_request that generate many questions? I am asking this because I am getting around 17.000.000 QUESTIONS a day in my DB (show status), but I am sure we don't send 17 mill sql-request to mysql. I assume the questions in the status of the MySql server refers to requests from clients. E.g. for me, with one db, questions is almost exactly the double of the number of (SQL) queries I issue. According to http://www.mysql.com/doc/S/H/SHOW_STATUS.html, questions refers to queries, which seems wrong. For my situation, I presume that the client library silently issues some query after opening the connection (like choosing the default database to work with). Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Could mysql count the datatraffic of DB's posted queries?
Hi all, I'm looking for a posibility to count the datatraffic of a database at my mysqldb-server. The clients should got only select-rights on thier databases. And I need to count the resultset datatraffic of their select-queries. I'm using mysql version 3.23.39. I was looking for some logfiles entries at the logging mode, but there were only the queries in the HOSTNAME.log file. Not the size of the resultsets. What could I do to reach my aim. I hope somebody know some good tutorials in the web, or could write me a posibility to reach that. SHOW STATUS will give you an indication on how much traffic is moved between your server and the clients (look for bytes sent and bytes received) - but only the totals for the server are displayed. I very much doubt you can get the data by client or connection. I'm curious as to *why* you're doing this. For billing? In that case the data traffic is a lousy metric for how many resources each client is hogging. The complexity of the queries is by far more important than the amount of data moved. / Carsten -- Carsten H. Pedersen keeper and maintainer of the bitbybit.dk MySQL FAQ http://www.bitbybit.dk/mysqlfaq - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem moving database datadir
Although I wouldn't suggest doing this, it seems to work fine for me. I suggest in moving the directory, do not make ANY changes to the permissions - any. Just do this as root: (Assuming you have redhat'ish directory structure) cd /var/lib/mysql mv dbname /home/user/database ln -s /home/user/database /var/lib/mysql/dbname This works fine in my environment. Just don't mess with the permissions or ownership, leave it as mysql/mysql. On Tuesday 27 November 2001 11:54 am, Mike(mickalo)Blezien wrote: Hello All, Hoping someone can point where I may have gone wrong on setting our client's databases within their own respective home directories. Here is one of the setups dir tree and settings: The database 'tracers' is symbolically linked from the datadir to the client's home directory. But for some reason, I keep getting the error message that the tables are 'read only'...! what did I go wrong here?? How can I get MySQL to read/write to the database in the client's home directory?? I thought I had all the owner/grp settings correct. Thanks mysql sql database. # client home dir /home/tracerta/tracers drwxrwxrwx2 tracerta mysql4096 Nov 25 22:57 ./ drwx--x--x 13 tracerta tracerta 4096 Nov 27 12:45 ../ -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 customers.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 customers.MYI -rw-rw-rw-1 tracerta mysql9010 Nov 25 22:57 customers.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 log.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 log.MYI -rw-rw-rw-1 tracerta mysql8610 Nov 25 22:57 log.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 membership.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 membership.MYI -rw-rw-rw-1 tracerta mysql8644 Nov 25 22:57 membership.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 onlinetag.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 onlinetag.MYI -rw-rw-rw-1 tracerta mysql8592 Nov 25 22:57 onlinetag.frm -rw-rw-rw-1 tracerta mysql 340 Nov 25 22:57 product_id.MYD -rw-rw-rw-1 tracerta mysql3072 Nov 25 22:57 product_id.MYI -rw-rw-rw-1 tracerta mysql8700 Nov 25 22:57 product_id.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 recovery.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 recovery.MYI -rw-rw-rw-1 tracerta mysql8990 Nov 25 22:57 recovery.frm -rw-rw-rw-1 tracerta mysql 14904 Nov 25 22:57 retailtag.MYD -rw-rw-rw-1 tracerta mysql 19456 Nov 25 22:57 retailtag.MYI -rw-rw-rw-1 tracerta mysql8616 Nov 25 22:57 retailtag.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 tags.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 tags.MYI -rw-rw-rw-1 tracerta mysql8652 Nov 25 22:57 tags.frm -rw-rw-rw-1 tracerta mysql 0 Nov 25 22:57 temptag.MYD -rw-rw-rw-1 tracerta mysql1024 Nov 25 22:57 temptag.MYI -rw-rw-rw-1 tracerta mysql8602 Nov 25 22:57 temptag.frm # dir /var/lib/mysql/var drwx--2 mysqlmysql1024 Jul 30 19:32 jytinter_db/ -rw-r--r--1 mysqlmysql2276 Nov 14 09:56 my.cnf drwx--2 mysqlmysql1024 Jul 31 08:09 mysql/ -rw-rw-r--1 mysqlmysql 65962 Nov 27 13:51 mysql.log -rw-rw1 mysqlmysql 4 Nov 26 22:51 mysql.pid -rw-rw-r--1 mysqlroot 6450 Nov 26 22:51 mysql_log.err -rw-rw1 mysqlmysql 54739 Nov 26 22:51 mysql_update.log drwx--2 mysqlmysql1024 Jul 30 19:31 referra1/ lrwxrwxrwx1 mysqlmysql 22 Nov 25 23:00 tracers - /home/tracerta/tracers/ Mike(mickalo)Blezien =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= Thunder Rain Internet Publishing Providing Internet Solutions that work! http://www.thunder-rain.com Tel: 1(225)686-2002 =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How do temporary tables die?
You mean how they are dropped? temp tables are created for each seperate process (session?). So as soon as the client (or you program) ends the session, they should be dropped Morten Dreier wrote: Morten: How do tamporary tables die? Is there a time limit, og do I have to drop Morten: them manually? Morten: -- sherzodR [EMAIL PROTECTED] use CGI::Session; - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL v4 FTS speed
On Tuesday 27 Nov 2001 20:17, Mike Wexler wrote: The query SELECT * FROM table WHERE MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) ORDER BY MATCH (description) AGAINST (+A +really +nice +toy IN BOOLEAN MODE) LIMIT 10 will abort the search when it retrieves the 500th document with all the three words. It's impossible to say, how many index entries it will look at. I take it the IN BOOLEAN MODE part of the AGAINST() is going to be new to 4.0.1. Incidentally, how are the WHERE clauses handled when MATCH/AGAINST is used for FTS? Given that I am seeing a fairly linear increase in query time with the increase in number of matched terms, I would guess that the FTS is performed first. Especially since limiting other constraints in the WHERE clause produces no noticeable reduction in query time. This seems to be wasteful. Considering that FTS is likely the slowest part of the query, it would probably be beneficial in terms of performance to have it execute last, with all other simpler constraints being satisfied first, so fewer records need to be searched. Another question - is there a way to acquire a list of words in the FTS index? Someting like SELECT Word, count(*) AS Frequency FROMFTSIndex GROUP BYWord ORDER BYFREQUENCY ASC LIMIT 100; This would allow for easier overview of what dead words are being indexed, and therefore allow for easier isolation of new stop words, and reduction in unnecessary searching that FTS would have to perform, thus increasing performance. Considering that I'm really after SELECT speed, would more careful tuning of stop words be likeky to yield signifficant performance improvements? It would also be REALLY nice to have a dynamic list of stop words. I know you said that this is definitely planned, but it would be nice to know how soon... Another thing - it would probably be useful to gather some statistics about FTS queries performed. For example, say the terms 'perl' or 'linux' appear 1 times in the queries (since the last checkpoint interval, or whatever). But the words 'nt' and 'vb' appear 0 times. It might be beneficial to tune the FTS in a slightly dynamic way, so that the frequently searched terms have a sort of keep-alive, while the words that are never searched for (but are indexed) get added to the stop-word list. OTOH, if a stop word does end up being searched for a number of times that exceeds some threshold (say, 0.1%), it could be removed from the stop word list. The FTS index would then need to be updated, but if the database structure is dynamic (for example a search engine of some sort) where the records get deleted and inserted all the time, the database would be able to learn what are start words and what are stop words. These statistics could be analyzed and acted upon when, say, ANALYZE is issued. It would make the FTS self-tuning. Of course, the FTS feature in the TODO that allows specifiying explicitly strings that get indexed as they are becomes much more useful, then, as it can prevent things from being automatically added to the stop word list during ANALYZE/OPTIMIZE stage. Has any of this been at least thought about? I've just checked the TODO, and it doesn't appear to be there... Looking forward to 4.0.1. BTW, will the file formats be compatible? Or will it require a dump + restore of the database, when going from 4.0.0 to 4.0.1? Regards. Gordan - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Using SUM() and LEFT JOIN gives me incorrect values for SUM()
Hi. On Tue, Nov 20, 2001 at 10:40:25AM -0500, [EMAIL PROTECTED] wrote: I will try to be as complete as possible here. This is the query that I'm trying to run: SELECT [...] art_id, art_title, art_description, typ_id, SUM(arp_visits) AS visits, COUNT(fed_id) AS comments FROM users, articles, article_pages, types LEFT JOIN feedback ON fed_typ_target_id=art_id AND fed_typ_id=typ_id WHERE usr_id=art_usr_id AND usr_status='active' AND art_status='approved' AND art_id=arp_art_id AND typ_name='articles' GROUP BY art_id ORDER BY art_created_date DESC LIMIT 0, 10 Anyway, it all comes down to the following: I'm trying to select the total number of comments and visits for a specific 'article', as symbolized as art_id. I currently only have 1 article in the 'articles' table, and 3 comments related to it on the 'feedback' table. This 'article' have 49 visits currently. The problem is that the number of visits that I receive from this query is actually the result of the multiplication of (comments * visits), which is 147. Well, that's actually what this SQL query asks for. It's easy to see: Just drop the GROUP BY and replace SUM and COUNT by the resp. columns and have a look at the output. You should get 3 rows, one for each comment, paired with all columns of the article. That's why COUNT(fed_id) is 3, like you want it. The SUM over the 3 rows, with all of them having arp_visit 49, will be 147. The perceived problem stems from the fact that you try to mix a grouping fuction intended for article with a grouping function for feedback. You are actually grouping in a way correct for getting count of feedback, so grouping an article datum will fail. In your case, the solution is to simply take in count that you will SUM the visits multiple times: SUM(arp_visits)/COUNT(arp_visits) AS visits [...] Cheers, Joao Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Beginner - Updating a Table from a File
Can you update a table from a file? if so where can I find some syntax? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fw: Could mysql count the datatraffic of DB's posted queries?
SHOW STATUS will give you an indication on how much traffic is moved between your server and the clients (look for bytes sent and bytes received) - but only the totals for the server are displayed. I very much doubt you can get the data by client or connection. I'm curious as to *why* you're doing this. For billing? In that case the data traffic is a lousy metric for how many resources each client is hogging. The complexity of the queries is by far more important than the amount of data moved. I have to explain much better. I want to provide the content of my db-server and want to aloud the users(mysql user) to get the content only by using the select-rights. The mysql users could have big public commercial webapplications with much users on their websites. I'm scared about the traffic costs of my hoster. I want to transfer the costs to the users but I must have some datasize of the resultsets of the sended queries. The Bytes_received and Bytes_sent are general values but I want to know on each table or user? Perica Markovic - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Beginner - Updating a Table from a File
Elaborate, please. Raju On Tuesday 27 November 2001 16:47, raezor wrote: Can you update a table from a file? if so where can I find some syntax? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Beginner - Updating a Table from a File
Raju, I want to be able to do an update from a text file I have placed in the /tmp dir. I have searched the Manual and it does not state how to do this. I have three book on this subject and non of them state explicitly how to accomplish the task of import non-existing lines from /tmp/my_file.txt, then update (replace, if necessary) the existing lines. -Original Message- From: CK Raju [mailto:[EMAIL PROTECTED]] Sent: Wednesday, November 28, 2001 2:44 AM To: raezor; MySQL Lists Subject: Re: Beginner - Updating a Table from a File Elaborate, please. Raju On Tuesday 27 November 2001 16:47, raezor wrote: Can you update a table from a file? if so where can I find some syntax? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Auto-normalization, and mysql.host table
Hi. On Tue, Nov 20, 2001 at 04:59:38PM -0500, [EMAIL PROTECTED] wrote: [...] I'd like to normalize these fields by having a separate 'filenames' table with an integer key that's used in the 'transfers' table. But since I don't know what all the files will be until I load the table, I have to do several passes through the files to load all the data, or populate it as I go -- and in either case, I have to maintain a local associative array to load the appropriate keys into each record, which means that perl's doing most of the work that it seems mysql should, and it's much more time consuming to load and maintain. Well, that depends on the point of view. IMHO it's not the task of the database to pre-process data, but the one of the application. Is there any way to autoload data while normalizing what gets inserted? This seems like a relatively common situation. As far as I know, there is no reasonable way to get the database to do this automatically. You can do this by database means, if you want. E.g. CREATE TABLE filename ( id INT AUTO_INCREMENT PRIMARY KEY, filename VARCHAR(255), UNIQUE(filename) ) SELECT NULL, DISTINCT(filename) FROM data; [the same for other fields] CREATE TABLE data_neu ( filename_id INT, when_id INT, [other fields] ) SELECT f.id, w.id [, ..., other fields] FROM filename f, when w [, ...], data WHERE f.filename = data.filename AND w.when_id = data.when AND [...] Well, I think you get the idea. If that will be better than a solution within your application, I don't know. [...] The docs are sparse on using the mysql.host table, See http://www.mysql.com/doc/P/r/Privileges.html, especially the following: -- * The user table scope fields determine whether to allow or reject incoming connections. For allowed connections, any privileges granted in the user table indicate the user's global (superuser) privileges. These privileges apply to all databases on the server. * The db and host tables are used together: * The db table scope fields determine which users can access which databases from which hosts. The privilege fields determine which operations are allowed. * The host table is used as an extension of the db table when you want a given db table entry to apply to several hosts. For example, if you want a user to be able to use a database from several hosts in your network, leave the Host value empty in the user's db table entry, then populate the host table with an entry for each of those hosts. This mechanism is described more detail in section 4.2.9 Access Control, Stage 2: Request Verification. -- Also see http://www.mysql.com/doc/R/e/Request_access.html but it seems that I can make this work by leaving the Host column blank for a user in the mysql.user table and putting all the allowed IPs in the mysql.host table ... but this doesn't work -- any IP that can get through the firewall can log in to the server with the appropriate password. Yes, as explained above, only the user table is used to determine whether to allow or reject incoming connections. The host table is only used to check access privileges. Using DNS wildcards doesn't work because of our weird local DNS config. Well, it seems, that this is your real problem. IMHO it's not the task of privilege system is not intended to remedy a weird DNS config. The usual advice in your situation would indeed be to use wildcards either for IPs or host names. Aren't you able to at least specify IP aliases on the database server itself in the hosts table? I can work around this by putting [EMAIL PROTECTED]@ip6 for each user in the mysql.user table, but this is a maintenance headache -- password changes need to be duped to each record, and each time we add a server or change an IP, it requires multiple new records. Am I missing how the mysql.host table works? Well, there is no way around listing all hosts in the user table, because that is checked on incoming connections. You can use the hosts table to avoid listing all ips again in the db table. Additionally, one can use the hosts table to create groups of hosts of different trust levels, because the privileges in the hosts tables are ANDed, i.e. you can set the hosts table so that users coming from the internet will have less privileges than users connecting from the intranet. Bye, Benjamin. -- [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL
