Re: innodb_lock_wait_timeout
On 11.10.2012 14:43, Johan De Meersman wrote: - Original Message - From: Markus Falb markus.f...@fasel.at But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? Well, raising the timeout would probably help, but may cause more concurrent connections to stay open until they block the server. The idea of retrying is that everything you've done before that particular transaction has already been committed, so you (probably) can't easily undo it. A retry gives you a chance to still finish what you were doing with no other loss than some time waiting. Regardless of how many retries fail, you will still be in the same position as you were when the first attempt failed. With a low timeout the connection will be terminated sooner, but if the application retries another connection is taken. I could have raised the timeout with the same effect on the db side (1 process is waiting) but maybe more performant (no new connection necessary) and with simpler logic on the application side (no retry logic) Maybe you imply that there is some kind of sleep before the retry, so that other statements could be fulfilled? I still don't get it. -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
innodb_lock_wait_timeout and replication
I encountered an error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 For this very statement an entry in the binlog was filed ... # Query_time: 52 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 ... Why is there an entry in the binlog if the statement failed? If it is in the binlog it will be replicated to a slave and on the slave it will succeed (maybe). Does this mean that a Lock wait timeout exceeded potentially drive the slave out of sync? -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
innodb_lock_wait_timeout
Hi, I have mostly myisam tables, only a few central used tables are innodb to avoid table locks. The innodb tables are used like myisam tables e.g. no explicit transactions. I encountered a error MySQL Error: Lock wait timeout exceeded; try restarting transaction MySQL Error No: 1205 My question is not what caused this timeout but how to handle it. Should I raise innodb_lock_wait_timeout? What are the disadvantages? What would be a sensible value? Should I retry application side? http://dev.mysql.com/doc/refman/5.0/en/innodb-error-handling.html says ...snip Both deadlocks and lock wait timeouts are normal on busy servers and it is necessary for applications to be aware that they may happen and handle them by retrying. snap... But why is retrying better than raising the value? So what is better, adjusting the timeout or retrying application side and why? -- Kind Regards, Markus Falb signature.asc Description: OpenPGP digital signature
Re: Transplanting table structure changes
Hi Jerry, there's a (commercial) product called SQLyog, with an excellent schema synchronization tool (at least in the enterprise version) which procudes ready-made alter table scripts. Probably other db tools have this functionality too ... HTH, regards, Markus - Original Message - From: Jerry Schwartz [EMAIL PROTECTED] To: 'Martijn Tonies' [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Friday, August 08, 2008 8:44 PM Subject: RE: Transplanting table structure changes You might want to try our Schema Compare tool inside Database Workbench. This tool also has a script recorder that can record any changes you make to (meta)data in order to help you keep your change scripts. [JS] Ah! I just discovered that if I hit what looks like the record button, I get a script that is just the ALTER TABLE DDL that I need. I thought I was going to have to record my own. Martijn Tonies Database Workbench - tool for InterBase, Firebird, MySQL, NexusDB, Oracle MS SQL Server Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] infoshop.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.138 / Virus Database: 270.6.0/1601 - Release Date: 8/8/2008 9:02 AM -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm unsubscribe to mysql@lists.mysql.com
Am Freitag 25 Juli 2008 21:19:56 schrieb [EMAIL PROTECTED]: To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please click on the following link: http://lists.mysql.com/u/mysql/488a275c5444d766/lists=feilner-it.net This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. We haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 24237 invoked by uid 48); 25 Jul 2008 19:19:55 - Date: 25 Jul 2008 19:19:55 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 84.56.89.14. -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: confirm unsubscribe to mysql@lists.mysql.com
Am Freitag 25 Juli 2008 21:19:53 schrieb [EMAIL PROTECTED]: To confirm that you would like [EMAIL PROTECTED] removed from the mysql mailing list, please click on the following link: http://lists.mysql.com/u/mysql/488a2759cd767dbb/lists=feilner-it.net This confirmation serves two purposes. First, it verifies that we are able to get mail through to you. Second, it protects you in case someone forges a subscription request in your name. We haven't checked whether your address is currently on the mailing list. To see what address you used to subscribe, look at the messages you are receiving from the mailing list. Each message has your address hidden inside its return path; for example, [EMAIL PROTECTED] receives messages with return path: mysql-return-number[EMAIL PROTECTED] --- Administrative commands for the mysql list --- I can handle administrative requests automatically. Please do not send them to the list address! Instead, send your message to the correct command address: To subscribe to the list, send a message to: [EMAIL PROTECTED] To remove your address from the list, just send a message to the address in the ``List-Unsubscribe'' header of any list message. If you haven't changed addresses since subscribing, you can also send a message to: [EMAIL PROTECTED] or for the digest to: [EMAIL PROTECTED] For addition or removal of addresses, I'll send a confirmation message to that address. When you receive it, simply reply to it to complete the transaction. If you need to get in touch with the human owner of this list, please send a message to: [EMAIL PROTECTED] Please include a FORWARDED list message with ALL HEADERS intact to make it easier to help you. --- Enclosed is a copy of the request I received. Received: (qmail 24222 invoked by uid 48); 25 Jul 2008 19:19:52 - Date: 25 Jul 2008 19:19:52 - Message-ID: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Unsubscribe request From: [EMAIL PROTECTED] This message was generated because of a request from 84.56.89.14. -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Authors wanted for German Linux Magazin
Hello List, As an editor for the german Linux Magazine I am looking for Authors who would want to write an article of about 5-7 pages (=15-2 characters) on MySQL Troubleshooting and a second on Clustering and Replication issues. The Article is planned for our forthcoming Linux Technical Review 09 - Datenbanken, which aims at IT-Managers, Technicians and Administrators at a very high skill level. The deadline is in four weeks. I would feel very happy if one or two of you would like to write for us, and if you have good ideas about relevant topics, please let me know. Please let me know, I'd be very happy if you could contribute... (Don't worry about language!) -- Best Regards - Mit freundlichen Gruessen Markus Feilner - Feilner IT Linux GIS Linux Solutions, Training, Seminare und Workshops - auch Inhouse Koetztingerstr 6c93057 Regensburg Telefon:+49 941 8 10 79 89 Mobil: +49 170 3 02 70 92 WWW: www.feilner-it.net mail: [EMAIL PROTECTED] -- My OpenVPN book - http://www.packtpub.com/openvpn/book OPENVPN : Building and Integrating Virtual Private Networks My new book - Out now: http://www.packtpub.com/scalix/book SCALIX Linux Administrator's Guide -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spontaneous corrupted tables on MyISAM with 5.0.x release
Hi Gerald, Michael, Gerald L. Clark wrote: Never put MySQl data files on an NFS exported share. Michael Dykman wrote: This is a bit of a generalization, but file locks are known to be pretty flakey and unreliable under NFS.. any kind of serious load begs races conditions which file locks normally sort out. I have had similar very bad luck using MySQL across NFS. Thanks for the input. I've talked with our Hoster, they told me that, technically, whether the mount points are directly mounted via NFS or whether they're VMware discs doesn't matter: the product operates solely on NFS, they're no real physical drives involved ... thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Spontaneous corrupted tables on MyISAM with 5.0.x release
Hi, our scenario (was): server1: 5.0.32-Debian_7etch1-log server2: 5.0.32-Debian_7etch1-log Hardware-wise (attention, Vmware, see below) they're equal: ~1GHz CPU at at minimum 2GB ram. Suddenly about 4 to 6 weeks ago, server1 started getting serious problems with spontaneous corrupted tables, so that in the end our hoster upgrade server1 to 5.0.51-2-log what we're currently running. Unfortunately, things haven't changed a bit. server2 is running different databases/applications, some tables are replicated from server1 to server2, some from server2 to server1. However, server2, as far as I can remember, never had those spontaneous table problems and still hasn't (yet). Both servers are running on VMware (I think ESX is the product our Hoster is using) and the MySQL data files are on a NFS exported share. Those share/fileserver is reported to be some kind of Ueber-Beast-Killer-Maschine. All servers running in Vmware don't contain virtual Vmware hard discs but have NFS mounted root and data, etc. partitions. We're very often, daily!, getting spontaneous corrupted tables on the server with the version which gets us really in trouble. The only pattern so far: 1) it only affects MyISAM tables 2) once converted to InnoDB, no troubles (so far) Unfortunately there are tables we weren't able to convert because they contain an fulltext index. Interestingly, once we converted all (except mentioned) tables in a database, the remaining MyISAM tables won't crash anymore (so far). Besides this, there's no distinct pattern, because we get crashes * on high and low traffic tables * on intensive and non-intensive write tables * on big (range between 100 and 150MB) and small tables It often occurred that a simple REPAIR table statement didn't always helped. Sometimes EXTENDED was required, sometimes an offline repair with the myisamchk had to be done. The tables didn't crash because the whole MySQL server went down, this was while the server was running. We've been running the applications using the databases for years. The were two major changes during the last year: * our moved the MySQL server from a physical machine to Vmware * we upgraded (better, let our Hoster upgrade) from some MySQL4 version to the mentioned versions above. We don't use any fancy stuff, more or less simple SELECT, DELETE, UPDATE, INSERT. No Subselects, no triggers, no stored procedures, no key constraints, etc, no locking, no REPLACE. Our Hoster refers to the following MySQL bugs http://bugs.mysql.com/bug.php?id=28154 http://bugs.mysql.com/bug.php?id=33596 However specially for 33596 I don't see any related information because this issue described there never applied. For 28154: Unfortunately I don't remember seeing this 127 error, however if it ever occurred, then only a long time ago. Recent errors are just corrupted tables once we start seeing problems in our web application. Our thread cache size is 128. Mentioned in #28154 is http://bugs.mysql.com/bug.php?id=29838 . I think that's the reason why our Hoster upgraded to 5.0.51. For what it matters, I just can't believe that MyISAM is to blame completely at fault. If it had that problems I just couldn't believe this was in a stable product. I'm really curious to just to fix the problems but also find out what the cause really is. I would be glad for any help on this matter and I'm happy to provide any information you want. thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problem with ~0.5 GB tabel
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hello, I've learned a bit about the environment this server is running in. It's VMware with root NFS and storage NFS mount points for MySQL. I've been told the throughput over NFS for my Server is from 20 to 30 MB/s. The server has 3GB ram. I'm not sure about it's CPU performance, but the information I've is that when connections start hanging and the effect basically multiplies, the CPU load goes aup. Does this sound like a possible bottleneck? thanks, - - Markus Markus Fischer wrote: Hi, I'm using phorum [1] and made some custom queries against their database. My query looks like this: SELECT message_id, subject, datestamp, forum_id, thread FROM phorum_messages WHERE forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND thread != 0 AND status = 2 AND closed = 0 ORDER BY datestamp DESC LIMIT 3 The table phorum_message is about 500MB in size. The problem is that such a query often starts to hang in the Sorting result phase. This can take up to minutes and during this time problems really start: more and more such queries are coming in, each of them hanging for the same reason too and after a few minutes the maximum of connections are reached (currently 170) and everything is dead. Only killing the queries manually helps. My guess is that the filesort is problematic and so I tried to avoid it with the following things. When I use explain on the query I get back the following: id: 1 select_type: SIMPLE table: phorum_messages type: range possible_keys: thread_message, thread_forum, status_forum, list_page_float, list_page_flat, dup_check, last_post_time, forum_max_message, post_count key: post_count key_len: 9 ref: NULL rows: 1311 Extra: Using where; Using filesort When I remove the ORDER BY statements, the query is *not* using filesort. However, as you can guess, it is necessary. The goal of the query is to get the top-most posters in the selected forums. The MySQL documentation [2] says that under certain cases it should be possible to create appropriate keys so that even an ORDER BY can take advantage of, but I was unable to come up with such an. Is there a recommendation how to go for it? thanks, - Markus [1] http://www.phorum.org/ [2] http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFHfCl+1nS0RcInK9ARAqEaAJ9JsofQIzoVBfCJQRKE/8X6wW1/SwCg0+en 0HDQBTAB4U87Nuua/h4pDiU= =utDe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance problem with ~0.5 GB tabel
Hi, I'm using phorum [1] and made some custom queries against their database. My query looks like this: SELECT message_id, subject, datestamp, forum_id, thread FROM phorum_messages WHERE forum_id IN (1, 2, 3, 4) AND parent_id = 0 AND thread != 0 AND status = 2 AND closed = 0 ORDER BY datestamp DESC LIMIT 3 The table phorum_message is about 500MB in size. The problem is that such a query often starts to hang in the Sorting result phase. This can take up to minutes and during this time problems really start: more and more such queries are coming in, each of them hanging for the same reason too and after a few minutes the maximum of connections are reached (currently 170) and everything is dead. Only killing the queries manually helps. My guess is that the filesort is problematic and so I tried to avoid it with the following things. When I use explain on the query I get back the following: id: 1 select_type: SIMPLE table: phorum_messages type: range possible_keys: thread_message, thread_forum, status_forum, list_page_float, list_page_flat, dup_check, last_post_time, forum_max_message, post_count key: post_count key_len: 9 ref: NULL rows: 1311 Extra: Using where; Using filesort When I remove the ORDER BY statements, the query is *not* using filesort. However, as you can guess, it is necessary. The goal of the query is to get the top-most posters in the selected forums. The MySQL documentation [2] says that under certain cases it should be possible to create appropriate keys so that even an ORDER BY can take advantage of, but I was unable to come up with such an. Is there a recommendation how to go for it? thanks, - Markus [1] http://www.phorum.org/ [2] http://dev.mysql.com/doc/refman/5.0/en/order-by-optimization.html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problems with migration from 4.0.24 to 5.0.32 with unique varchar
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I'm having trouble with the following: In the MySQL 4 database I had the following values in an unique varchar and there was no problem: Gross Groß (the latter has the german sharp s character) Now whenever I import this into the MySQL5 database I get a duplicate key error; I tried the collcations utf8_bin and latin1_bin already (started out with latin1_german1 and latin1_german2). How can I solve this? thanks, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFF7XxV1nS0RcInK9ARAjNeAKDYU0y+Np0ssQt+bhpEwNb5jb7IWgCfZG1H AfEHWvhiwlrLlCmaEpcxZJ0= =O7H4 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Logging from mysql only from certain applications
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, we've enabled /var/log/mysql.log on our server (5.0.24a on Debian testing). However I only want to log commands from certain application, or better say, certain users. When I look at some log output, it looks like this: 060919 9:53:38 15945 Connect [EMAIL PROTECTED] on 15945 Query SET AUTOCOMMIT=0 15945 Init DB somedb 15945 Query SELECT * FROM [...] 15945 Query SELECT * FROM [..] 15945 Query UPDATE [...] 15945 Query COMMIT The way I read this is that once I have a Connect command, the ID before uniquely identifies all further SQL commands belonging to this connection - to this mysql user. Now what I need is something similar to tail -f but only for selected connected user. We've so much going on the server that it's impossible to look over all the data without a tool. Before I re-invent the wheel I'ld like to know if there are tools/helper like this available ... ? thanks, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFD6Py1nS0RcInK9ARAloJAJ4s/7ckWeE/s4VO92yVgeUebOrRKwCguCvZ AlJvzmq1VVCCfGj2eIZ3oME= =aqc3 -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Logging from mysql only from certain applications
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Markus Fischer wrote: Before I re-invent the wheel I'ld like to know if there are tools/helper like this available ... ? Being impatient and in need of this I've written my own version. Maybe it is valuable to others. http://markus.fischer.name/lab/php/mysqltail.php.txt Basic usage: php mysqltail.php /var/log/mysql.log userToTrace Stop application with usualy CTRL-C. - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.3 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFFEAK01nS0RcInK9ARArYiAJ9MH9wGoolDEuU7mW+3O3SNg1XP+QCghjX6 wnLcE+Jb6rnJG/yBYO/W4eg= =N5PM -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: does DISTINCT kill ORDER BY?
Just for the archives: looks like I bumped into bug #21456: http://bugs.mysql.com/bug.php?id=21456 This bug has been fixed in 4.1.22 and 5.0.25, so I'll just have to upgrade. Thanks anyway Markus Markus Hoenicka [EMAIL PROTECTED] was heard to say: Chris [EMAIL PROTECTED] was heard to say: That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. That doesn't make sense. How many rows fit that criteria? SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0; = 784 SELECT COUNT(DISTINCT t_refdb.refdb_id) FROM t_refdb WHERE t_refdb.refdb_id0; = 784 In this case refdb_type does not further restrict the result set. However, I've tried queries where a modified clause further restricts the results selected by refdb_id with the same effect regarding the sorting. BTW the above results were obtained with mysql.exe Ver 14.7 Distrib 4.1.10, for Win95/Win98 (i32) so the problem is neither specific to the FreeBSD port nor to a particular 4.1.x version. regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
does DISTINCT kill ORDER BY?
Hi, is the following behaviour intended? Are my queries wrong? The output shows only the Extra field as the other fields are identical in all cases. EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using index (results are sorted) EXPLAIN SELECT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where; Using filesort (results are sorted) EXPLAIN SELECT DISTINCT t_refdb.refdb_id FROM t_refdb WHERE refdb_type!='DUMMY' AND t_refdb.refdb_id0 ORDER BY t_refdb.refdb_id; = Using where (results are not sorted) That is, if MySQL can't use an index to sort the result, DISTINCT queries won't be sorted at all. These results were obtained with: mysql Ver 14.7 Distrib 4.1.21, for portbld-freebsd6.1 (i386) using 5.0 FreeBSD yeti.mininet 6.1-RELEASE FreeBSD 6.1-RELEASE #1: Mon Aug 28 22:24:48 CEST 2006 [EMAIL PROTECTED]:/usr/src/sys/i386/compile/YETI i386 regards, Markus -- Markus Hoenicka [EMAIL PROTECTED] (Spam-protected email: replace the quadrupeds with mhoenicka) http://www.mhoenicka.de -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb-safe-binlog
Hi, I want to use --innodb-safe-binlog with mysql, but in the newest Version of Mysql it is obsolet: Note: --innodb-safe-binlog is not needed in MySQL 5.1, having been made obsolete by the introduction of XA transaction support in MySQL 5.0. See Section 13.4.7, XA Transactions. but how can I use safe binlogs with new mysql's and without changing my SQL-Code? thanks in advance Markus Wenke -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Matching of german umlauts with LIKE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, I realized this also. Martin Schwarz off list suggested to me to use REGEXP for that purpose. That means that I'm rewriting the user input übersee behind the scenes to ^(ü|ue)bersee.* which works quote well. The only gotcha is that the user may enter any special regexp characters also, so I had to make a list of them and escape them prior to modifying the string. thanks, - - Markus sheeri kritzer wrote: MySQL doesn't have anything like that. You can use the wildcard characters instead of the umlauts if you want, such as SELECT * from person where name like %bersee which would get übersee and uebersee but also a whole lot more. But doing something like SELECT * from person where name like _bersee or name like __bersee might work -- the underscore means 1 of any character, so here the only noise you'd get are other folks whose names are _ _ bersee So there's still a margin for error. Unfortunately, there's no special case for hey, when you're looking at LIKE, I want to define that x=y -- particularly when x and y have differing #'s of characters. -Sheeri On 3/22/06, Markus Fischer [EMAIL PROTECTED] wrote: Hi, what is the best way to match german umlauts like 'ä' also their alternative writing 'ae'? For example I'm searching for übersee and I also want to find the word uebersee in the database. The words are actually names of persons. One possibility is to dynamically expand the SQL statement if such special characters are found. So the search term übersee will be expanded to SELECT * FROM person WHERE name LIKE 'übersee%' AND name LIKE 'uebersee%' but this is getting dirty and very very long if multiple umlauts are used to cover all cases ... So the other idea is to have the name twice in the database for every person and the second version of the name is a normalized for where all special characters are replaced with their alternative writing. E.g. I store the field name übersee and also name2 uebersee and when matching I match against name2. If the field would container more special characters it still would work without much more work, e.g. name is überseemöbel then name2 would be ueberseemoebel and when the term überseemö is entered it's also normalized to ueberseemoe and the LIKE statement will still match. Basically this is some kind of primitive stemming like lucene does it. Is there maybe some built-in support from MySQL for such special cases? thanks for any pointers, - Markus - -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEI4BC1nS0RcInK9ARAnMrAJ9jZ5LAxS1S2bjqrPvIUBSiTGsxxQCgv+5l xyxQhd7B9HTnc8sTa7Tsekk= =uycm -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Matching of german umlauts with LIKE
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, what is the best way to match german umlauts like 'ä' also their alternative writing 'ae'? For example I'm searching for übersee and I also want to find the word uebersee in the database. The words are actually names of persons. One possibility is to dynamically expand the SQL statement if such special characters are found. So the search term übersee will be expanded to SELECT * FROM person WHERE name LIKE 'übersee%' AND name LIKE 'uebersee%' but this is getting dirty and very very long if multiple umlauts are used to cover all cases ... So the other idea is to have the name twice in the database for every person and the second version of the name is a normalized for where all special characters are replaced with their alternative writing. E.g. I store the field name übersee and also name2 uebersee and when matching I match against name2. If the field would container more special characters it still would work without much more work, e.g. name is überseemöbel then name2 would be ueberseemoebel and when the term überseemö is entered it's also normalized to ueberseemoe and the LIKE statement will still match. Basically this is some kind of primitive stemming like lucene does it. Is there maybe some built-in support from MySQL for such special cases? thanks for any pointers, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.2 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEIWDH1nS0RcInK9ARAkzyAKCyoPPVd1YRfhs1p/p8kY465/QPVQCfa5uj r2ZarPZvsJp5FPNDsdhAN7E= =5ADZ -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Index and multiple fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, is there, performance wise, any difference whether I create one index for multiple fields or each field with its own index? I'm running 4.0.16. thx, - - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEGnNH1nS0RcInK9ARAq9FAJ427uJXMuujd6Etnq7fhTSOqmISKgCg2Tn4 Qpytyz4PD4CPGSMEPX4ABbI= =cyqe -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index and multiple fields
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, thanks, somehow I wasn't able to find those pages. Basically, this means if I've a table like this id1 id2 id3 id4 id5 and I've two different select statements: select * from ... where id1 = .. and id2 = .. and the other being select * from ... where id3 = .. and id4 = .. I would create two indexes, one for id1/id2 and the other for id3/id4 , right? again, thanks - - Markus Косов Евгений wrote: Hi, Mark! Of course, it depends on queries you are running. I beleive you can find all anwers here: http://dev.mysql.com/doc/refman/5.0/en/indexes.html http://dev.mysql.com/doc/refman/5.0/en/multiple-column-indexes.html http://dev.mysql.com/doc/refman/5.0/en/mysql-indexes.html Markus Fischer пишет: Hi, is there, performance wise, any difference whether I create one index for multiple fields or each field with its own index? I'm running 4.0.16. thx, - Markus -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.2.1 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFEGo1p1nS0RcInK9ARAjOsAJsGmgh1VVI3RCG1ci7sr2vBKR7VgQCgpvg8 k3wTpe1bqh7BIHaDGze+ttY= =ZqNR -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld in an eternal loop
Hi listers 1. environment [EMAIL PROTECTED] ~ uname -a Linux myhost.mydom.tld 2.6.12-1.1390_FC4_cubbi4_swsusp2 #1 Sat Jul 9 12:34:47 CEST 2005 i686 i686 i386 GNU/Linux [EMAIL PROTECTED] ~ 2. mysql [EMAIL PROTECTED] ~ rpm -qa |grep -i mysql MySQL-server-4.1.14-0 MySQL-shared-4.1.14-0 MySQL-devel-4.1.14-0 MySQL-client-4.1.14-0 [EMAIL PROTECTED] ~ 3. mysql tables two tables: localhost.addresses2 describe first_table; +--+--+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | name | text | YES | | NULL || | title| text | YES | | NULL || | phone| text | YES | | NULL || | desc | text | YES | | NULL || | comment | text | YES | | NULL || | status | text | YES | | NULL || | url | text | YES | | NULL || | businesscategory | text | YES | | NULL || | address | text | YES | | NULL || | kanton | text | YES | | NULL || | reply_1 | text | YES | | NULL || | reply_2 | text | YES | | NULL || | reply_3 | text | YES | | NULL || | reply_date | datetime | YES | | NULL || | ip_address | text | YES | | NULL || | firm | text | YES | | NULL || | served | datetime | YES | | NULL || | addon| text | YES | | NULL || | givenname| text | YES | | NULL || | history | text | YES | | NULL || | favorit | text | YES | | NULL || | last_update | timestamp| YES | | -00-00 00:00:00 || | task_link| int(11) | YES | | NULL || | counter | int(10) unsigned | | PRI | NULL | auto_increment | +--+--+--+-+-++ 24 rows in set (0.00 sec) localhost.addresses2 localhost.addresses2 describe second_table; +--+--+--+-+---++ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+---++ | name | text | YES | | NULL || | title| text | YES | | NULL || | phone| text | YES | | NULL || | desc | text | YES | | NULL || | comment | text | YES | | NULL || | status | text | YES | | NULL || | url | text | YES | | NULL || | businesscategory | text | YES | | NULL || | address | text | YES | | NULL || | kanton | text | YES | | NULL || | reply_1 | text | YES | | NULL || | reply_2 | text | YES | | NULL || | reply_3 | text | YES | | NULL || | reply_date | datetime | YES | | NULL || | firm | text | YES | | NULL || | served | datetime | YES | | NULL || | addon| text | YES | | NULL || | givenname| text | YES | |
MySQL Binlog/Replication and SET @variables
Hello, i have a problem with my replication setup and SET statments. The SET statments seem to be not logged with the queries. An example: CREATE TABLE blablub (ID INT NOT NULL auto_increment PRIMARY KEY, bla text, blub text); SET @bla='bla'; SET @blub='blub'; INSERT INTO blablub VALUES(NULL, @bla, @blub); Now the table blablub on the Master: ++--+--+ | ID | bla | blub | ++--+--+ | 1 | bla | blub | ++--+--+ And on the Slave: ++--+--+ | ID | bla | blub | ++--+--+ | 2 | NULL | NULL | ++--+--+ Here's the binlog entry for the INSERT: #050624 16:46:54 server id 1 log_pos 9162 Intvar SET INSERT_ID=1; # at 9190 #050624 16:46:54 server id 1 log_pos 9190 Query thread_id=7 exec_tim e=0 error_code=0 SET TIMESTAMP=1119624414; INSERT INTO blablub VALUES(NULL, @bla, @blub); Same should happen when the binlog is replayed while restoring a backup. I'm using mysql release 4.0.24. regards, Markus -- Siemens AG ITO AS 4 Internetteam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
instable behaviour of mysql
Hi mysql-listers mysql select version(); ++ | version() | ++ | 4.1.7-standard-log | ++ 1 row in set (0.10 sec) mysql [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.6.4-54.5-default #1 Fri May 7 21:43:10 UTC 2004 i686 i686 i386 GNU/Linux [EMAIL PROTECTED]:~ following mysql-behaviour made me loose several hours yesterday: 1. a root user is installed with password having overall permission over the database. i can access the db through this root user in the following manner: [EMAIL PROTECTED]:~ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 29 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 2. for the sake of an application i added a second user named root, but this one from the host rosetta. i added the password for this second root user using use mysql; update user set password ... where user = root and host = rosetta.ayni.com; flush privileges; the password i attributed to this root-user is the same as that one for the original root user. my intention was to access the database in the following manner: [EMAIL PROTECTED]:~ mysql -h rosetta -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 30 to server version: 4.1.7-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql 3. this second root-user, however, presented the following strange behaviour: i could access the database in the manner described in 2. several times. and then, at once, access was denied, and i had to redo the flush privileges; from then on, i could access the db again several times as described in 2., but again after some time, access was denied and i had to redo the flush privileges; 4. what i did then: 4.a took a mysqldump of the entire mysql installation. 4.b stopped the mysql daemon 4.c removed the entire directory where the database had been 4.d uninstalled mysql-server 4.e re-installed mysql-server 4.f re-created the database from the mysqldump 5. remains to be seen if now mysql is stable suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with date field
What's going on here? God knows ! Maybe providing some information (OS, version, host app(if any), code example, db description, etc.) would permit some humble humans to take a guess ... - Original Message - From: Steve Grosz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, November 05, 2004 8:13 AM Subject: Problem with date field Why am I having a problem getting a date field to hold date? I will store 2004-10-15, save the record. Reopen the record, and there is just -00-00 What's going on here? Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Egor Egorov wrote: Bug filled: http://bugs.mysql.com/bug.php?id=5784 Thank you! Nice, thanks to you too ;-) regards, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Hi, Egor Egorov wrote: Can you create a test case? I.e. a .sql file which is supposed to drop the table well but instead fails? This will help us determine if it's a bug and fix if it is. Thanks, this hit a pretty interesting nail for me: I can dump it, but I can't load the dump into the database again. I get: ERROR 1005 at line 28: Can't create table './test1/aktionen_produkte.frm' (errno: 150) The definition of the Table is: CREATE TABLE `aktionen_produkte` ( `ap_id` int(10) unsigned NOT NULL auto_increment, `ap_pr_id_produkt` int(10) unsigned NOT NULL default '0', `ap_ak_id_aktion` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`ap_id`), KEY `aktionen_produkte_FKIndex1` (`ap_ak_id_aktion`), KEY `aktionen_produkte_FKIndex2` (`ap_pr_id_produkt`), CONSTRAINT `aktionen_produkte_ibfk_1` FOREIGN KEY (`ap_ak_id_aktion`) REFERENCES `aktionen` (`ak_id`) ON DELETE NO ACTION ON UPDATE NO ACTION, CONSTRAINT `aktionen_produkte_ibfk_2` FOREIGN KEY (`ap_pr_id_produkt`) REFERENCES `produkte` (`pr_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; I guess I can see where the problem is: the order of creation of the tables is important, because if tries to create contraints to table which do not exist (yet) because they a further down in the dump file. Manually reordering doesn't seem very sexy to me. I read the mysqldump manpage but it doesn't tell me an option how to have mysqldump respect the ordering. Any ideas? thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unable top drop table, error 1051
Hi, Egor Egorov wrote: Can you create a test case? I.e. a .sql file which is supposed to drop the table well but instead fails? Nevermind my last post, I found the workaround to disable foregin_key_checks during import; interesting. Here is a small example: set foreign_key_checks=0; CREATE TABLE `produkt_kategorie` ( `pk_id` int(10) unsigned NOT NULL auto_increment, `pk_pt_id_typ` int(10) unsigned NOT NULL default '0', `pk_kategorie` varchar(255) default NULL, `pk_sortid` int(10) unsigned default NULL, PRIMARY KEY (`pk_id`), KEY `produk_kategorie_FKIndex1` (`pk_pt_id_typ`), CONSTRAINT `produkt_kategorie_ibfk_1` FOREIGN KEY (`pk_pt_id_typ`) REFERENCES `produktkategorie_typ` (`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION ) TYPE=InnoDB; CREATE TABLE `produktkategorie_typ` ( `pt_id` int(10) unsigned NOT NULL auto_increment, `pt_name` varchar(255) default NULL, `pt_sortid` int(10) unsigned NOT NULL default '0', PRIMARY KEY (`pt_id`) ) TYPE=InnoDB; set foreign_key_checks=1; When I now try to drop the table produktkategorie_typ I get the error: #1051 - Unknown table 'produktkategorie_typ' I think it's because of the constraint, but then the error message is missldeading. So basically I would need to disable the foreign_key_checks every time I drop a table which has constraints? I see the bigger picture now I guess, just didn't while trying to achive this with phpmyadmin. thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable top drop table, error 1051
Hi, I've a problem that I can't drop a certain table, always get back the error unknown table. Version: 4.0.21 (Debian Testing) Table-Type: InnoDB mysql show tables; [...] | produkt_kategorie| mysql drop table produkt_kategorie; ERROR 1051: Unknown table 'produkt_kategorie' The ownerships/permission seem right to me too: -rw-rw 1 mysql mysql 8670 Sep 21 11:23 produkt_kategorie.frm mysql describe produkt_kategorie; | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-++ | pk_id| int(10) unsigned | | PRI | NULL| auto_increment | | pk_pt_id_typ | int(10) unsigned | | MUL | 0 || | pk_kategorie | varchar(255) | YES | | NULL|| | pk_sortid| int(10) unsigned | YES | | NULL|| pk_pt_id_typ is from a 1:n relation to the table produktkategorie_typ. I do the operation as user 'root' which has all access-rights. When I view the table in phpmyadmin (using 2.5.7pl1) I also see this additional information: InnoDB free: 44032 kB; (`pk_pt_id_typ`) REFER `produktkategorie_typ`(`pt_id`) ON DELETE NO ACTION ON UPDATE NO ACTION I've used DbDesigner4 to design the table and then use the synchronisation feature to create the tables in the database. I'm using a 1:n relation from another table, produktkategorie_typ, to this table. I've created the Reference Definitions, but yet haven't assigned any actions (thus there's NO ACTION defined as seen above). I believe it has to do with the relation/reference definitions from InnoDB, so to me the message seems missleading. The only way for me to drop the table is to completely drop the database and recreate. thanks for any hints - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select data in one order, print in another order
Hi list environment: host:/ # uname -a Linux host 2.4.19-4GB #1 Fri Sep 13 13:14:56 UTC 2002 i686 unknown host:/ # host:/ # /usr/local/mysql/bin/mysqld -v /usr/local/mysql/bin/mysqld Ver 4.0.12-max for pc-linux on i686 host:/ # an php application of ours prepares customer data on the screen in a normal way: select name, b, c from tabx left outer join taby on tabx.cx = taby.cy where tabx.name like %username% order by tabx.cx; this works fine and incredibly fast and the output suits the needs of our cusstomers. now, customers keep telling me that they would like to have a printed output of their screen-lists. and they would very much prefer that the printed output were ordered by tabx.name, and - of course - that they have exactly the same data on the screen as later on paper. if i just change the order by clause, i will usually not have the same data on the screen like on the paper. i can do it with a temporary table: mysql drop temporary table if exists temptab; mysql create temporary table temptab select name, b, c, d from tabx where name like %username% order by tabx.cx; mysql select * from temptab order by name; that gave the customers the same data on paper like on the screen, and the paper data are ordered by name ONLY THAT i must give alle customers create-temporay-table rights, and this is not exactly, what i want. can i do this another way round, i.e. without going through a temporary table? i thought about using a table alias an do a union, but the table alias is not known any more in the second statement of the union. i also tried to google but the situation cannot be explained in two words which would probably lead to a google-success. you might tell me, that i could do the same select and afterwards sort the result using php-sort. that is neither what i want because our customers are used to the incredible speed of mysql. any suggestion is very much appreciated. thanks in advance suomi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to show comments/annotations in MySQL client output
Michael, which version are you using ? I didn't find SYSTEM in the manual ... Thanks, Markus - Original Message - From: Michael Stassen [EMAIL PROTECTED] To: Richard Mixon (qwest) [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Tuesday, July 27, 2004 6:09 PM Subject: Re: How to show comments/annotations in MySQL client output One more thought: Your mention of echo jogged my memory. You can use SYSTEM to pass what follows to the system and display the result. So, SYSTEM echo ## The following output should only contain two rows.; results in ## The following output should only contain two rows. assuming echo is a command which makes sense to your system. Michael Michael Stassen wrote: Richard Mixon (qwest) wrote: Harald Fuchs wrote: In article [EMAIL PROTECTED], Michael Stassen [EMAIL PROTECTED] writes: Richard Mixon (qwest) wrote: I run some mysql command files (just SQL statements in a file I read from standard input) and need to place some annotiations/comments in the output. If I place standard SQL comments (-- comment text) or MySQL comments (# comment text) they do not show up in the mysql client output. Well, in a way that makes sense - they are comments. I have tried using select ' comment text' ; and that works, but I get many, many lines instead of my one simple annotation - e.g.: -- select First comment ... -- +---+ First comment ... | +---+ First comment ... | +---+ 1 row in set (0.00 sec) Any/all ideas are appreciated - Richard SELECT First comment ...; will give exactly the output you show, but SELECT First comment ... FROM sometable; will return that string once for each row of the table. Is that what you're doing? I guess he's talking about the column headers produced by the mysql client program. These can be suppressed by using mysql -N. Michael/Harald, Thanks for the tip. But: 1) Yes, I am just issuing: SELECT First comment ...; and 2) The problem is that instead of getting a single line of text (i.e. my annotation/comment), I get many, for example the command select COMMENT 3; produces the following (even using the -N flag when I startup mySQL): -- SELECT COMMENT 3 -- +---+ | COMMENT 3 | +---+ 1 row in set (0.00 sec) The problem with -N is that is suppresses all of the headers. What I really need is an echo or print command. The idea is to guide the reader of the mysql client output with some comments. The SQL might look like the following: SELECT The following output should only contain two rows for status ...; SELECT status,count(*) FROM PoClass GROUP BY status; SELECT The following output should only contain three rows for status ...; SELECT status,count(*) FROM PoClassMeasurement GROUP BY status; Thank you - Richard Well, it's not exactly what you want, but you could use an alias to give each comment a standard header: SELECT 'The following output should only contain two rows for status.' AS COMMENT; +---+ | COMMENT | +---+ | The following output should only contain two rows for status. | +---+ 1 row in set (0.00 sec) You still get some extra lines, but perhaps that looks a little better. You could also change the alias according to the type of comment: SELECT 'The following output *must* contain only two rows!' AS `WARNING!`; ++ | WARNING! | ++ | The following output *must* contain only two rows! | ++ 1 row in set (0.00 sec) Michael -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] My Inbox is protected by SPAMfighter 1396 spam mails have been blocked so far. Download free www.spamfighter.com today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and where clause - help
Hi, | BT-1034.02 mysql SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.2'; I think you have a type .. instead of SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.2' try SELECT * FROM balloon_txt WHERE access_no = 'BT-1034.02' HTH, Markus - Original Message - From: leegold [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, July 07, 2004 4:51 AM Subject: select and where clause - help I am trying to get a select statement with WHERE to work and I can't seem to do it. Below find some output. I try a select w/WHERE by book but it doesn't work. Originally I tried in PHP and didn't work, so now I just want the MYSQL to work first, I could be overlooking something simple since I'm new to MYSQL. Thanks, Lee G. mysql show columns from balloon.balloon_txt; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | access_no | varchar(20) | | PRI | | | | recs_txt | text| YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM balloon_txt limit 0,1; +--+ -- -- -- -- --+ | access_no| recs_txt | +--+ -- -- -- -- --+ | BT-1034.02 | Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longe r duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL
Re: JOIN or UNION or both?
Matthias, That would have been an elegant solution but unfotunately Im not running 4.1 yet, which is required for GROUP_CONCAT() (no chance of an upgrade We still don't know which version you're using, but have a look at http://www.codeproject.com/Purgatory/mygroupconcat.asp It worked for me (MySQL 4.0.x) HTH, Markus - Original Message - From: Matthias Kritz [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, June 24, 2004 7:33 PM Subject: RE: JOIN or UNION or both? Shawn, Thank you for your reply. That would have been an elegant solution but unfotunately Im not running 4.1 yet, which is required for GROUP_CONCAT() (no chance of an upgrade either). Is there a possibility of running perhaps two queries, one nested within the other? This way I could pull out all presentation info first, and then the relating speaker info (with a php loop through the recordset)? Or could I do this with a temp table? I dont know the in's and out's, so any help is greatly appreciated! Thank you, Matthias Kritz _ Digital Samba, S.L. http://www.digitalsamba.com -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Donnerstag, 24. Juni 2004 17:25 To: Matthias Kritz Cc: [EMAIL PROTECTED] Subject: Re: JOIN or UNION or both? Have you looked at the GROUP_CONCAT() function? http://dev.mysql.com/doc/mysql/en/GROUP-BY-Functions.html It returns a string result with the concatenated values from a group. If you didn't do this you would end up with a ragged-right data set that is just a nightmare to manage with SQL. In one row you would get 2 columns of data, in another you could have 10. This way you end up with two columns, one of them is a list of values. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Matthias Kritz [EMAIL PROTECTED]To: [EMAIL PROTECTED] x.net cc: Fax to: 06/24/2004 10:56 Subject: JOIN or UNION or both? AM Hi, I have the following table structure: tbl_speakers sid fname lname tbl_presentations pid name desc tbl_speakers_presentations sid pid I would like to display all presentations, (but each only once), and display the speakers' names for each presentation. With my current syntax (see below), if a presentation has, say, 3 speakers I receive 3 distinct records - I just want 1 with 3 speakers columns. //PULL OUT EVERY PRESENTATION WITH A SPEAKER AS SEPERATE RECORD SELECT * FROM tbl_presentations, tbl_speakers, tbl_speakers_presentations WHERE tbl_speakers.sid = tbl_speakers_presentations.sid AND tbl_presentations.pid = tbl_speakers_presentations.pid I cant figure out the right JOIN or UNION or AS syntax - still learning SQL :-) Any help is greatly appreciated! Thank you, Matthias Kritz _ Digital Samba, S.L. http://www.digitalsamba.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] My Inbox is protected by SPAMfighter 933 spam mails have been blocked so far. Download free www.spamfighter.com today! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
UPDATE...WHERE ... IN (SELECT...)
Hi all, Can someone tell me what is wrong with this statement? UPDATE nummer SET gold = '1' WHERE anr IN (SELECT no FROM gull); I use MySQL 3.23.49. Thanks Markus Källander
Dual-installation: MySQl 3 + 4 ?
Hello! Is there a possibility to install MySQL 3.X and MySQL 4.X parellel on one FreeBSD-Machine? I would like to work with the new version, but a lot of utilities published as free php-software don't run with MySQL withoutn massive changings. With best regards Mark Schanovsky -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error at server startup
Hi! After some tries i finally managed to compile mysql 4.0.17 with ssl-support (openssl 0.9.7c, compiled with minGW 3.1.0.1) under windows2000. SHOW VARIABLES LIKE 'have_openssl' returns YES. when starting the server the following happens: $mysqld.exe --port=3306 --ssl-ca=cacert.pem --ssl-cert=server-cert.pem --ssl-key=server-key.pem --debug=trace --standalone my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path my_load_path _mymalloc _mymalloc _mymalloc _mymalloc set_default_charset_by_name | init_dynamic_array | | _mymalloc | | _mymalloc | init_dynamic_array | get_charsets_dir | get_charsets_dir | my_fopen | | _mymalloc | | _mymalloc | my_fopen | init_dynamic_array | | _mymalloc | | _mymalloc | init_dynamic_array | my_fclose | | _myfree | | _myfree | my_fclose | _myfree | _myfree set_default_charset_by_name init_dynamic_string | _mymalloc | _mymalloc init_dynamic_string get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat get_charsets_dir get_charsets_dir my_stat my_stat _mymalloc _mymalloc _myfree _myfree new_VioSSLAcceptorFd | _mymalloc | _mymalloc | kill_server Anyone who can help? Regards Wulle -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
AW: Error: 127
Hello Terence, Terence wrote: Just do a check and repair: (or just repair to fix it) CHECK TABLE your_table REPAIR TABLE your_table I´ve got the same error like Jacob, but this error appears over and over at my database files. Do you know WHY this error occurs ? Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
non-ASCII characters in LIKE and regexp comparisons?
Hi mysql-list, 1. my installation: [EMAIL PROTECTED]:~ uname -a Linux rosetta 2.4.19-4GB #1 Fri Sep 13 13:14:56 UTC 2002 i686 unknown [EMAIL PROTECTED]:~ mysql show global variables like version; +---++ | Variable_name | Value | +---++ | version | 4.0.12-max-log | +---++ 1 row in set (0.00 sec) mysql mysql show global variables like character_set; +---++ | Variable_name | Value | +---++ | character_set | latin1 | +---++ 1 row in set (0.00 sec) mysql show global variables like character_sets; ++---+ | Variable_name | Value | ++---+ | character_sets | latin1 big5 czech euc_kr gb2312 gbk latin1_de sjis tis620 ujis dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | ++---+ 1 row in set (0.00 sec) mysql 2. my problem: when i am looking for entries with a name containing an u-umlaut by means of the LIKE construct, i get all chunk result-rows: mysql select name from contacts where name like %ü% order by name; +---+ | name | +---+ | A Côte beauty treats | | AA Anonyme Alkoholiker| | AA Anonyme Alkoholiker| | AAA All Activities Agency AG | | Adèle Uldry | | Adolf Deyhle | | Alain Barbey | | Alain et Dorothée Ryser | | Alain et Thérèse Sauty| | Alex et Silvia Fritschy | | Alexandre Stransky| ... ... | Yves Moullet | | Yves Zehfus | | Yves-André et Jennifer Cornioley | | Yves-François Weill | | Yves-Pierre Poscia| | Yvonne Badel | | Yvonne Frédérique Maeder | | Yvonne Malka | | Yvonne Quartenoud | | Zekarias Araya| | Zero Gravity | | Zürich| +---+ 485 rows in set (0.02 sec) mysql if i, however, do the search with a regexp, it seams to work: mysql select name from contacts where name regexp ü order by name; +---+ | name | +---+ | Alfred Zürcher| | Alig Grossküchen | | Alois Kühner | | Ambulante Spitex Hausbetreuungsdienst für Stadt und Land | | Andreas Bühlmann | | Anton Bühlmann| | Arnd Küchel | | Aurélia et Yann Füllemann | | Bachblütenberatung| | Bahnhofleitung, Fundbüro, | | Batigroup AG Graubünden | | Beratungsbüro f. Personalmanagement | | Berghüsli | | Beyeler AG Drogerie-Parfümerie| | Brigitte Schüller
Re: Performance problems using 1GB Linux server and trying to avoid swapping
Hi, On Tue, Dec 16, 2003 at 10:23:05PM +1100, Chris Nolan wrote : How heavy is your usage of TEMPORARY TABLES? I don't use them much myself, but I'm sure that the others on the list will have something to say in that regard. Here are the relevant numbers: Created_tmp_disk_tables| 21911 Created_tmp_tables | 329778 This is from an uptime of about 40 hours which makes the Created_tmp_disk_tables around 6% during this time. To get a better look at MySQL's usage of memory, you could try looking at the output of SHOW STATUS . I don't want to look silly, but actually having the show status output in front of me doesn't tell me much about the memory usage; at least I can't find it ;) There are things like Qcache_lowmem_prunes Qcache_free_memory but this are the only ones related to memory. Can you advise me where to look at exactly? - Markus Ps: I'm using version 4.0.14 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
On Tue, Dec 16, 2003 at 10:38:14AM -0600, Dan Nelson wrote : In the last episode (Dec 16), Markus Fischer said: I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. [mysqld] set-variable= key_buffer=16M Way too low; this says only cache 16MB of index data. Try 256MB. set-variable= max_allowed_packet=200M Probably way too high, but doesn't hurt since it won't actually allocate that much memory unless you have a 200MB field someplace. Thanks for pointing this out. I've compensated max_allowed_packet and increased the key_buffer. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? show status like 'created_tmp%'; Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. From what I read from the manual, sort_buffer_size is only used for the isamchk tools, isn't it? I've adapted join_buffer_size though; thanks. - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Performance problems using 1GB Linux server and trying to avoid swapping
On Thu, Dec 18, 2003 at 10:37:46AM -0600, Dan Nelson wrote : In the last episode (Dec 18), Markus Fischer said: On Tue, Dec 16, 2003 at 10:38:14AM -0600, Dan Nelson wrote : Raising sort_buffer_size and join_buffer_size may also help if your queries pull a lot of records. From what I read from the manual, sort_buffer_size is only used for the isamchk tools, isn't it? I've adapted join_buffer_size though; thanks. It's used for any sorting: * `sort_buffer_size' Each thread that needs to do a sort allocates a buffer of this size. Increase this value for faster `ORDER BY' or `GROUP BY' operations. *Note Temporary files::. Of course, sorry, I got confused then. thanks, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Performance problems using 1GB Linux server and trying to avoid swapping
Hello, I'm investigating a performance problem with mysql server set up. The server is running linux with 1GB ram. I'ld like to tune the configuration of the server to use as much RAM as possible without swapping to the disc because of the big slow down. The current configuration is as follows (I've just pasted the relevant pieces): [mysqld] skip-innodb skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=200M set-variable= thread_stack=128K bind-address= 127.0.0.1 port= 3306 skip-networking set-variable = query_cache_type=1 set-variable = query_cache_size=64M set-variable = tmp_table_size=50M The main purpose of the database is a heavily modified forum application based on vBulletin. One of the biggest table is the 'post' table with consumes about 617M currently; this table is often used in queries for index pages which are the most frequent visited pages; indexes for the relevant column in the WHERE statement are all created. The next tables in size are using 22M and most of them much less. I'm also having problems in determining accurately how much memory MySQL uses when running on the system; the information provided by ps doesn't seem relieable due the many processes/threads. Getting the currently used memory and whether MySQL needed to swap would be very valueable in chasing down this issue. I'm also not sure whether the database is swapping temporary tables to the disc or not; is there a way to verify if this is happening? Other beneficial things would be to know how much memory certain queries need (mostly the queries on index pages). Besides more physical memory, are the other ways to better tune the server for the 1GB ram? What performance analyses tool are recommended for use with MySQL? thanks for any advice, - Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
synchronizing over eth1
hi list, i have 2 database server with replication on over the eth0 device (z.b. 192.168.0.5) and say, eth1 is 192.168.1.5. if i want to change replication to do over eth1 (which is now on eth0), must i just change the rights in mysql with change master=192.168.1.5...? greetings from the lake contance markus -- --- Suchtreffer AG Bleicherstrasse 20 D-78467 Konstanz Germany fon: +49-(0)7531-89207-17 fax: +49-(0)7531-89207-13 e-mail: [EMAIL PROTECTED] internet: http://www.suchtreffer.de --- In a world without walls and fences, who need gates? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PHP or Perl?
Hello I am no PHP expert but I find it quite easy to move from Perl to PHP. PHP too has a huge standard library with lots of useful functions and it has PEAR, something similar to CPAN. but when I develop MySQL appliction (mainly for myself at the moment) I use Python or Ruby. they are also easy to learn and are very clean languages. and Ruby is quite similar to Perl but fully OOP. and Ruby and Python also have great standard libraries. in Python there is for example urlib and urllib2 as a replacement for LWP. and Ruby also hat stuff for this in the standard library and the Ruby Application Archive. just my experiences. check out http://www.ruby-lang.org http://www.rubycentral.com http://www.python.org Markus On Sat, 2003-08-16 at 07:17, Mark Healey wrote: As part of my own learning mysql project I'm planning to build databases for all my books and DVD's. Stephen Hawking is probably a better typist than I am so I plan to use barcodes to get the info ,taking the information from various web sites. People tell me that PHP is THE way to do database work with mysql. The thing is, I'm familiar with Perl and it has all kinds of neat string manipulation stuff and LWP. Does PHP have comprable libraries (especially LWP) and how difficult is it to move from Perl. I hope I don't start a relegious war here I just want some advice. Mark Healey [EMAIL PROTECTED] This account is only for lists to which I've subscribed. Any spammers invite the worst revenge I think I can get away with. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0.14 mysql_create_db()... II
hello
for mysql_create_db() use mysql_query() and give an
SQL Statement (CREATE DATABASE XYZ) as an argument.
the same for mysql_drop_db (DROP DATABASE).
for mysql_connect: use mysql_real_connect.
the same is valid if you are using the Ruby MySQL
Modul. but it is very easy to extend the Ruby MySQL
class with a create_db function
require mysql
class Mysql
def create_db(db)
query(CREATE DATABASE #{db})
end
end
see
http://www.rubywizard.net/ruby-mysql.html
the Ruby API is similar to the C Api, but developmet
time is faster. so if you do not have to use C maybe
you should consider Ruby. there is also Ruby/DBI
ruby-dbi.sf.net
Markus
--
- Miguel Cardenas [EMAIL PROTECTED] schrieb:
Hello again...
I opened mysql.h and found this...
---
#ifdef USE_OLD_FUNCTIONS
MYSQL * STDCALL mysql_connect(MYSQL *mysql, const
char *host,
const char *user, const char *passwd);
int STDCALL mysql_create_db(MYSQL *mysql, const
char *DB);
int STDCALL mysql_drop_db(MYSQL *mysql, const char
*DB);
#definemysql_reload(mysql)
mysql_refresh((mysql),REFRESH_GRANT)
#endif
---
why old functions? should I use new ones instead?
I #define'd
USE_OLD_FUNCTIONS before #include'ing mysql.h and
now compiles fine, but I
think it is not the best way...
any comments or suggestions?
thnx!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Gesendet von Yahoo! Mail - http://mail.yahoo.de
Logos und Klingeltöne fürs Handy bei http://sms.yahoo.de
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
pre-compiled libmysqlclient.a for MinGW?
Hello, I'm currently trying to install the MySQL++ API on a MinGW system. Unfortunately, I'm lacking of a libmysqlclient.a for that sake. In the web, I found some (broken) links to a site named http://www.johnydog.cz/projects.php, where there ought to be a MinGW port of libmysqlclient for mysql-3.23.43. However, the site doesn't seem to exist anymore. Does anyone have the port and could pass it over to me? Or does someone know if the site still exists, and where it has moved? I'll appreciate any help, Markus -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changing the library path in libsqlplus.la
Hello, when linking to libsqlplus from an own project, I found that it was looking for libmysqlclient.la in /usr/lib (instead of /usr/local/lib, where it is really installed). As it seems, this path to libmysqlclient is taken from the libsqlplus.la, where it is put down hard-coded. Is there a way to tell the binary at installation where to look for the libmysqlclient? Or do I have to put a link resp. change the path in libsqlplus.la by hand? Thanks, Markus - 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
Build problem in mysql++-1.7.9
Hello, when building mysql++-1.7.9 from the source distribution, I get an error message: const_string1.hh:50: `out_of_range' undeclared (first use this function) I checked the source at that line, and indeed the exception `out_of_range' seems to be incorrectly used: The `std' namespace isn't defined, neither in a `using namespace' phrase, nor like `std::out_of_range'. Is there a way to fix this error except for changing it in the source code? Thanks, Markus - 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 table allways crashes
Description: We have to tables: t_daten(autoincrement on dat_id) and t_daten_detail. For each record in t_daten are on or more records in t_daten. The records are filled in from a java program via jdbc. An another program selects the data. At the the data will be deleted with: delete from t_daten, t_daten_detail using t_daten, t_daten_detail where t_daten.dat_id = t_daten_detail.ddet_dat_id and dat_id xxx. Once a day the table crashes because of the error 145: If I repair the the the following information is printed:(I do not know to complete text) link points outside .. of file.. I think that the indexfile is corrupted. Is it possible that there is a bug in the new version with the multi-table delete-statement? There are about 155 inserts in 3 minutes in t_daten and about 370 inserts in t_daten_detail in 3 minutes. How-To-Repeat: inserting many rows in both tables and parallel deleting rows of this tables with the following statemnt: delete from t_daten, t_daten_detail using t_daten, t_daten_detail where t_daten.dat_id = t_daten_detail.ddet_dat_id and dat_id xxx; t_daten: create table t_daten ( dat_id bigint not null AUTO_INCREMENT, dat_ts timestamp, dat_imp_ts timestamp, dat_boe_id varchar(5), dat_wp varchar(30), dat_action varchar(5), dat_exch_ident varchar(8), primary key (dat_id) ); t_daten_detail: create table t_daten_detail ( ddet_dat_id bigint not null, ddet_feld varchar(30) not null, ddet_wert varchar(40), primary key ddet_dat_id, ddet_feld ); Fix: make two deletes on each table could work. We still haven't tested. Submitter-Id: submitter ID Originator:Markus Hager Organization: BLUeBULL Capital Markets Consulting GmbH MySQL support: [none ] Synopsis: sorry, I don't know what to write Severity: Priority: Category: mysql Class: Release: mysql-4.0.9-gamma (Official MySQL RPM) Server: /usr/bin/mysqladmin Ver 8.39 Distrib 4.0.9-gamma, for pc-linux on i686 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.9-gamma Protocol version10 Connection Localhost via UNIX socket UNIX socket /var/lib/mysql/mysql.sock Uptime: 21 days 3 hours 28 min 4 sec Threads: 6 Questions: 12508338 Slow queries: 76 Opens: 39656 Flush tables: 1 Open tables: 4 Queries per second avg: 6.847 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux arger.in.bluebull.com 2.4.18-24.8.0 #1 Fri Jan 31 06:51:30 EST 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Jan 31 14:31 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x2 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-embedded-server' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request
Starting mysqld within SuSE 8.0
Hello, can someone tell me how to start mysqld at boot on a SuSE 8.0 system? Starting it manually with safe_mysqld runs fine. But I didn't find a way in YaST to start the daemon on boot, and manually adding it to the /etc/init.d/boot.d/ lead me into a crash. Thanks in advance, Markus - 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: Starting mysqld within SuSE 8.0
Hi Lutz, The easiest way would be using Yast, then choosing System, Runlevel-Editor. There you find Runlevel-Properties (Runlevel-Eigenschaften if you choose German Language for Yast). In the upcoming window for the services (Dienste) you can set markers, at which runlevels mysql should start. That did it. :-) Thank you very much! Greetings from Rheinland-Pfalz/Germany Markus -- Markus Gerwinski, IT-Berater An der Brunnenstube 15/3, 55120 Mainz Tel.: 06131-945852, mobil: 0177-3552099 http://www.it-beratung.gerwinski.de - 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: Starting mysqld within SuSE 8.0
Thomas Spahni wrote: Starting it manually with safe_mysqld runs fine. But I didn't find a way in YaST to start the daemon on boot, and manually adding it to the /etc/init.d/boot.d/ lead me into a crash. You may enable the start of mysql with the yast2 runlevel editor. Works fine for me on SuSE 8.0 even after compiling and installing newer versions of MySQL. Yes, it worked fine with the runlevel editor -- i.e., after I removed the remains of a workaround I tried to install a few months ago. ;-) As it seems, this one caused the crash I had. However make sure to keep the same directory tree as SuSE. Thanks, that's good to know when I'm going to upgrade from the SuSE CD version of MySQL to a more recent version. Best, Markus - 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: Red Hat 8.0 compile problems with 4.0.10
hello afaik: turn off the optimizing options before compiling/making. ... or get a completely new glibc family and compile each and everything pertinent. ... or use rh 7.3 kr mr Mark Matthews [EMAIL PROTECTED] 02/16/03 14:43 PM -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark T. Dame wrote: On 2/15/03 1:25 AM, Duncan Maitland wrote: I am having trouble compiling a custom mysqld (version 4.0.10) under Red Hat 8.0 - I did email this list a few weeks ago and I have tried a few things since then but I am asking again in the hope that someone more knowledgeable might be able to help. This is a known problem (in that I've seen several people post about it, including myself), but I have not seen any answers other than use the binary distribution or binary RPMS. I have successfully tested the RPM distribution, but I would prefer to use the source. As far as I can tell this is a compiling or linking problem. From what I've been able to figure out, the MySQL server will start up, but when you attempt to connect to it, it seg vaults. I haven't had time to track it down, but it doesn't appear to be a simple resolver library problem, as I've seen suggested. Also, 4.0.10 is increases the thread-stack size, supposedly to fix this (or maybe a related) problem. It hasn't helped, as far as I can tell. Anyway, I would be interested in an answer to this as well. I just tried compiling and using MySQL-4.0.11 (from the BitKeeper repository) on RedHat 8.0, and this problem seems to have gone away: * LIBC Version: glibc-2.2.93-5 * MySQL server started using mysqld_safe with 'default' options [mmatthew@computer ~]$ /usr/local/mysql/mysql-4.0/bin/mysql -h 127.0.0.1 Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 7 to server version: 4.0.11-gamma Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql status - -- /usr/local/mysql/mysql-4.0/bin/mysql Ver 12.18 Distrib 4.0.11-gamma, for intel-linux (i686) Connection id: 7 Current database: Current user: [EMAIL PROTECTED] SSL:Not in use Current pager: stdout Using outfile: '' Server version: 4.0.11-gamma Protocol version: 10 Connection: 127.0.0.1 via TCP/IP Client characterset:latin1 Server characterset:latin1 TCP port: 3307 Uptime: 11 hours 8 min 55 sec Threads: 1 Questions: 7 Slow queries: 0 Opens: 6 Flush tables: 1 Open tables: 0 Queries per second avg: 0.000 - -- mysql If you want to use 4.0.10, you have to explicitly set the stack size by passing '--thread-stack=192K' to mysqld on startup (it is not done by default). I have also found that sometimes running 'nscd' on RedHat-8.0 alleviates some of these resolver problems. -Mark - -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ For technical support contracts, visit https://order.mysql.com/?ref=mmma __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mark Matthews [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Full-Time Developer - JDBC/Java /_/ /_/\_, /___/\___\_\___/ Flossmoor (Chicago), IL USA ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.1.90 (MingW32) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD4DBQE+T5TmtvXNTca6JD8RAnPOAJ9WbXQTWmw/hkIe7eZLcIuJGPravQCXSlrH QqhJ7nUFeNBHLquLXyuO8w== =2J8p -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 - 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
error 2013 on rh8.0
this is probably an issue already solved or at least treated on this list - so please forgive. nevertheless I encountered the error 2013 when i tried to connect from another machine to the machine holding the mysqlserver running with the standard mysql package resp. the updated mysql package and the updated kernel ..2.4.18-24.8.0smp I know a workaround by starting mysql with --skip-name-resolve. This works. I know MySQL from www.mysql.com version 3.23.55. This works too. The first apears to me as an ugly approach, the latter means I have to recompile everything for connectivity of apache/php/mysql, because standard rpm's from RH don't go with this. If anyone knows how to fix this issue with RedHat packages - please drop me a line. MReger - 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 2013 on rh8.0
Heinz great hint - poor success actually installed is 2.2.93 if i force it in maybe mysql functions - other thing will fail in bulk. kr mr Stefan Hinz [EMAIL PROTECTED] 02/12/03 15:41 PM Markus, If you're lucky, you might be able to use this RPM: ftp://ftp.suse.com/pub/suse/i386/update/8.1/rpm/i586/glibc-2.2.5-161.i58 Once again, a broken or not compatible glibc, I guess. Regards, -- Stefan Hinz [EMAIL PROTECTED] iConnect GmbH http://iConnect.de Heesestr. 6, 12169 Berlin (Germany) Telefon: +49 30 7970948-0 Fax: +49 30 7970948-3 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: How to speed things up in MySQL ?
Thomas,
Just a thought: In your Delphi/cgi script, how do you define/call the
database-server? Meaning, do you use the IP address, localhost, a domain
name, a server name etc.? Can it be that it's not the query but the time to
establish a connection or resolve the name that is so long? I've seen that
on a Windows SAP DB server once, and it turned out to be a DNS issue. Once
fixed, everything ran perfectly well.
Markus
-Original Message-
From: Thomas Kvamme [mailto:[EMAIL PROTECTED]]
Sent: Saturday, January 25, 2003 6:27 PM
To: Bhavin Vyas; [EMAIL PROTECTED]
Subject: Re: How to speed things up in MySQL ?
Hi Bhavin.
Thanks for taking interest in my problem...
Here is the table definition as requested.
I'm sorry I could take a full dump... Each record consist of approx 600 KB
(just as in the Paradox table) so the file would be very big. (200 MB).
Kind Regards
Thomas Kvamme
[EMAIL PROTECTED]
BEGIN ***
-- MySQL dump 9.07
--
-- Host: localhostDatabase: tdd
-
-- Server version 4.0.9-gamma-nt
--
-- Table structure for table 'dvd'
--
CREATE TABLE dvd (
ID int(11) NOT NULL auto_increment,
Title varchar(100) default NULL,
ProductionYear varchar(4) default NULL,
TagLine varchar(200) default NULL,
NorwegianTitle varchar(100) default NULL,
NorwegianTagLine varchar(200) default '',
Director varchar(100) default '',
Story varchar(100) default '',
Producer varchar(100) default '',
Genre varchar(100) default '',
Production varchar(200) default '',
Distribution varchar(200) default '',
NorwegianDistribution varchar(200) default '',
ScreenFormat varchar(50) default '16:9 Anamorphic Widescreen',
AspectRatio varchar(10) default '2:35:1',
DVDDiscType char(2) default '9',
DVDRegion char(1) default '2',
RunningTime time default '00:00:00',
IMDB varchar(20) default '',
Starring blob,
SoundTracks blob,
Subtitles blob,
SpecialFeatures blob,
Comments blob,
PlotOutline blob,
FullCredits longblob,
Cover longblob,
THXCertified enum('False','True') default 'False',
DDEX enum('False','True') default 'False',
DTS enum('False','True') default 'False',
MPEG enum('False','True') default 'False',
Recommended enum('False','True') default 'False',
Added datetime default '-00-00 00:00:00',
Updated datetime default '-00-00 00:00:00',
PRIMARY KEY (ID),
KEY Added (Added),
KEY Title (Title,ProductionYear),
KEY NorwegianTitle (NorwegianTitle,ProductionYear)
) TYPE=MyISAM;
* END
- Original Message -
From: Bhavin Vyas [EMAIL PROTECTED]
To: Thomas Kvamme [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Saturday, January 25, 2003 5:15 PM
Subject: Re: How to speed things up in MySQL ?
Sounds weird. Can you e-mail us a 'mysqldump' of the table defination with
the data?
Thanks,
Bhavin.
- Original Message -
From: Thomas Kvamme [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Saturday, January 25, 2003 7:36 AM
Subject: How to speed things up in MySQL ?
Hello,
While reading this you may think this is off-topic... but please read
on..
I'll get on-topic in the end :-))
First of all I have Web Server on which I have a Paradox table with 320
records.
I also have a CGI Script (or program if you like) I made in Borland
Delphi
which I use to access the Table...
When I enter the address of my CGI-script in my Internet Browser the
following happens:
The CGI script on the Web Server is starting.
The CGI script reads the SQL command to execute passed on to the
script
through the HTTP protocol.
The CGI script establish connection with the Borland Database Engine
(Loading BDE dll's etc..)
The CGI script opens/initialize the Paradox Table.
The CGI script execute the SQL command sent to it.
The CGI script sends back the result of the SQL command to the
Browser
(HTTP protocol).
The CGI script closes down (shutting down db connection etc..).
My PC and my Web Server is connected on the same LAN (switched 100
MBit/s)
All of the above is completed in 1.5 seconds.
** here the fun begins **
I have now successfully installed MySQL (on the same WebServer) and
converted the Paradox Table to MySQL Table type: MyISAM.
Database Name: TDD
Table Name: DVD
This new Table contains the same 320 records using the same field
definition
as in the Paradox Table.
On my Web Server I go into \MySQL\bin\ and start mysql -uroot -p and
execute the same SQL command as sent to the Paradox table through the
CGI
script. see screen dump below for result.
SCREEN DUMP **
F:\MySQL\binmysql -uroot -p
Enter password: **
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3726 to server version: 4.0.9-gamma-nt
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql use tdd
Database changed
mysql select count(id
Re: Perl DBI secret command?
hi pls look into man DBI and find there this command. kr mr Jeff Snoxell [EMAIL PROTECTED] 01/18/03 10:45 AM Hi, just stumbled across the following piece of code: my $rowcount = $sth-rows(); And I checked it out compared to a count of the fetched rows from an SQL QUERY. AND It matched exactly! BUT, I can't find the command rows() in the DBI documentation. What's the story? Have I unearthed a conspiracy? Jeff - 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
MySQL v4.0.8 bug report
SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: precise description of the problem (multiple lines) How-To-Repeat: code/input/activities to reproduce the problem (multiple lines) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux testserver.suk.net 2.4.20-grsec #2 Mon Jan 13 14:27:39 CET 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-share d --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexi t Thread model: posix SEND-PR: -*- send-pr -*- SEND-PR: Lines starting with `SEND-PR' will be removed automatically, as SEND-PR: will all comments (text enclosed in `' and `'). SEND-PR: From: root To: [EMAIL PROTECTED] Subject: [50 character or so descriptive subject here (for reference)] Description: I've installed MySQL v4.0.8 chrooted like described in detail http://www.linuxquestions.org/questions/showthread.php?threadid=34338 Operating System is RedHat Linux v8.0 including ALL current available patches! As example I used the my-medium.cnf example configuration file ... and changed the path variables concluding to the variables ... the mysql root user did not have a passwort currently. The error occured right after I ran FLUSH PRIVILEGES (from the console) ... Kernel is v2.4.20 with the grsecurity patchset. How-To-Repeat: mysql -p use mysql; useradd ... flush privileges; (then the crash occurs right after flush privileges) Fix: how to correct or work around the problem, if known (multiple lines) Submitter-Id: submitter ID Originator:root Organization: organization of PR author (multiple lines) MySQL support: [none | licence | email support | extended email support ] Synopsis: synopsis of the problem (one line) Severity: [ non-critical | serious | critical ] (one line) Priority: [ low | medium | high ] (one line) Category: mysql Class: [ sw-bug | doc-bug | change-request | support ] (one line) Release: mysql-4.0.8-gamma-standard (Official MySQL-standard binary) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: machine, os, target, libraries (multiple lines) System: Linux testserver.suk.net 2.4.20-grsec #2 Mon Jan 13 14:27:39 CET 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=pentiumpro' CXX='gcc' CXXFLAGS='-O2 -mcpu=pentiumpro -felide-constructors' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Jan 10 15:50 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 6 01:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 6 00:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 6 00:50
insecure chrooting from mysqld
Hey, The chroot command line option of mysqld is insecure. Databases which are located for instance not in the chroot path can be accessed! More information can be found at http://www.linuxquestions.org/questions/showthread.php?s=threadid=34314 http://www.linuxquestions.org/questions/showthread.php?s=threadid=34338 -- __ Linux From Scratch user www.linuxfromscratch.org __ - 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: insecure chrooting from mysqld
Hey, Fixed, thanks. Good to know. Anyways I'll check it out in the next release! I always wondered why people spend so much time discussing a bug and developing various howto and workarounds, instead of submitting a bugreport and having the bug fixed in one hour. :)) I submitted the bug once I found it ... maybe my ISP has had some mail problems! Anyways good to know it's fixed and people care about security in MySQL ;) -- __ Linux From Scratch user #6371 May the sources be with you! www.linuxfromscratch.org __ - 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
mysql memory leak on SUN Solaris 5.7 when importing dump files
Description: Dump several big tables or entire databases with mysqldump, then import to same or another database with mysql -f db dumpfile or on mysql console by typeing source dumpfile. You can then see that the amount of free memory decreases quite fast, stopping the client or mysqld server does not free the mem. Can be reproduced with both V3.23.40 and V3.23.53. V3.23.40 was compiled with GCC 3.2, V3.23.53 installed as binary package. How-To-Repeat: Dump a database with around 250.000 to 3.000.000 records, record size is around 200 bytes. Import dump file and see memory eaten up. Fix: unknown Submitter-Id: submitter ID Originator:Markus Warg Organization: no organization MySQL support: none Synopsis: memory leak Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.40 (Source distribution) Server: /usr/local/mysql/bin/mysqladmin Ver 8.21 Distrib 3.23.40, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.40-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 6 hours 40 min 52 sec Threads: 3 Questions: 415761 Slow queries: 101 Opens: 885 Flush tables: 2 Open tables: 128 Queries per second avg: 17.286 Environment: SUN Solaris 5.7 with quite actual patch cluster installed System: SunOS hostname 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /local/bin/perl /local/bin/make /local/bin/gcc /usr/ucb/cc GCC: Reading specs from /local/bin/../lib/gcc-lib/sparc-sun-solaris2.7/3.2/specs Configured with: ../gcc-3.2/configure --enable-languages=c,c++ --disable-nls Thread model: posix gcc version 3.2 Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 bin 1711920 Jun 14 00:12 /lib/libc.a lrwxrwxrwx 1 root 11 Mar 10 1999 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin 1125872 Jun 14 00:12 /lib/libc.so.1 -rw-r--r-- 1 bin 1711920 Jun 14 00:12 /usr/lib/libc.a lrwxrwxrwx 1 root 11 Mar 10 1999 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin 1125872 Jun 14 00:12 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql Perl: This is perl, version 5.005_02 built for sun4-solaris - 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 memory leak on SUN Solaris 5.7 when importing dump files
Lars, the amount used increases until all free memory is used plus a fair amount of swap. On a machine with 2GB memory this can well grow up to 1GB. Any chance to get the current size of the malloc pool to check if this causes the effect? thx so far, Markus On Don, 21 Nov 2002, Lars Heidieker wrote: Does the memory amount used increases any further if repeating the dump import. If so there is a leak if not you just got fooled by the standard malloc of solaris which wont return memory free(d) to the system but instead the memory will be kept in a pool for the next malloc. Lars On Donnerstag, November 21, 2002, at 02:50 Uhr, [EMAIL PROTECTED] wrote: Description: Dump several big tables or entire databases with mysqldump, then import to same or another database with mysql -f db dumpfile or on mysql console by typeing source dumpfile. You can then see that the amount of free memory decreases quite fast, stopping the client or mysqld server does not free the mem. Can be reproduced with both V3.23.40 and V3.23.53. V3.23.40 was compiled with GCC 3.2, V3.23.53 installed as binary package. How-To-Repeat: Dump a database with around 250.000 to 3.000.000 records, record size is around 200 bytes. Import dump file and see memory eaten up. Fix: unknown Submitter-Id: submitter ID Originator:Markus Warg Organization: no organization MySQL support: none Synopsis: memory leak Severity: non-critical Priority: low Category: mysql Class: sw-bug Release: mysql-3.23.40 (Source distribution) Server: /usr/local/mysql/bin/mysqladmin Ver 8.21 Distrib 3.23.40, for sun-solaris2.7 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 3.23.40-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 6 hours 40 min 52 sec Threads: 3 Questions: 415761 Slow queries: 101 Opens: 885 Flush tables: 2 Open tables: 128 Queries per second avg: 17.286 Environment: SUN Solaris 5.7 with quite actual patch cluster installed System: SunOS hostname 5.7 Generic_106541-20 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /local/bin/perl /local/bin/make /local/bin/gcc /usr/ucb/cc GCC: Reading specs from /local/bin/../lib/gcc-lib/sparc-sun-solaris2.7/3.2/specs Configured with: ../gcc-3.2/configure --enable-languages=c,c++ --disable-nls Thread model: posix gcc version 3.2 Compilation info: CC='gcc' CFLAGS='' CXX='c++' CXXFLAGS='' LDFLAGS='' LIBC: -rw-r--r-- 1 bin 1711920 Jun 14 00:12 /lib/libc.a lrwxrwxrwx 1 root 11 Mar 10 1999 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin 1125872 Jun 14 00:12 /lib/libc.so.1 -rw-r--r-- 1 bin 1711920 Jun 14 00:12 /usr/lib/libc.a lrwxrwxrwx 1 root 11 Mar 10 1999 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 bin 1125872 Jun 14 00:12 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql Perl: This is perl, version 5.005_02 built for sun4-solaris - 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 -- this space is for rent - 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 memory leak on SUN Solaris 5.7 when importing dump files
Lars, uhm, installed that package and it confirms your suspicion: Total memory: 612 Megabytes Kernel Memory: 60 Megabytes Application: 89 Megabytes Executable libs: 32 Megabytes File Cache: 419 Megabytes Free, file cache: 6 Megabytes Free, free: 3 Megabytes However, I'm not quite lucky with the situation. The cache size only increases that fast and big during the use of mysql import. And overall performance of the machine goes down. However I'm absolutely not sure that I understood how priority_paging works and how it could help me. I'll have a closer look to that within the next days. Thx a lot for your hints, seems this really is not a mysql issue. regards, Markus On Don, 21 Nov 2002, Lars Heidieker wrote: [...] so you can see that a lot of the memory is used for file caching. In Solaris Versions below 8 there will be no Free, file cache. Most of the file cache can and probably will normaly be on the Free, file cache list on Solaris 7 (not in this example is dirty file cache block will be on the File Cache list and they move to the Free, file cache after sync etc) So there is nothing to worry about if the freemem drops because of an increasing filecache if you want to the system to scan more aggressively for file cache memory than for any memory if memory is tight it helps to activate priority_paging. Search for Solaris and priority_paging. I think the right syntax is to put a line in /etc/system like this: set priority_paging = 1 this is usefull for pre Solaris 8 systems and never set this varibale fo Solaris versions 7 -- this space is for rent - 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
Why does altering the order alter my result?
Dear Readers, I have a little mySQL problem here. Maybe it's a bug, maybe I'm just too stupid to read the documentation correctly, who knows... I'm sorry if I bother anyone with my stupidity. I run a simpy query like: SELECT INTERVAL((UNIX_TIMESTAMP(ReceivedDateTime) - UNIX_TIMESTAMP(StartDateTime) - 86400) / 60, 360, 720, 1440, 2880, 5760) FROM Station, File WHERE File.StationId=Station.StationId ORDER BY File.FileId First I calculate the time between two DATETIME fields (in seconds), then I substract 84600 (that's the number of seconds a single day has) and divide the rest by 60 to get minutes (is there an easier way to do this? Let me know please). In the end I get a time difference in minutes that I want to compare with a set of numbers, that's what I use the INTERVAL for. If there are less 360 minutes, it should return 0, if there are less than 720 it should return 1, ... and if there are more than 5760, it should return 5. And guess what, that's exactly what it does! :-) But the result is sorted by the FileId. I don't want it to be sorted by the FileId of the file, but by a Station ID that consists out of four characters. So I modify the query like this: SELECT INTERVAL((UNIX_TIMESTAMP(ReceivedDateTime) - UNIX_TIMESTAMP(StartDateTime) - 86400) / 60, 360, 720, 1440, 2880, 5760) FROM Station, File WHERE File.StationId=Station.StationId ORDER BY Station.FourCharacterId It's exactly the same query as before, I only altered the sort order, so I expect the same result, just in a different order. But it's not the same result at all. All of a sudden the INTERVAL function seems to always return 3, because all rows have 3 as result. WHY? :-/ I just changed the search order, so how can it have such a big influence on the result? And how can I get the desired order and still get the desired result for each row? Can anyone shine a light on this issue please? -- Best Regards, Markus Hanauska - 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
mysql++ optimize 4 gcc3.2
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi .. I have a question about mysql++. give it a version of mysql++ that is optimize for the gcc 3.2 Version or have someone a tip to compile that with the gcc 3.2 ?? Thanks a lot - -- Markus Schnelle - Email: [EMAIL PROTECTED] ICQ: 63 70 67 35 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE9mVT+h6Sm0pGHy68RAiXvAKCYckT5HcbP0bG3D9AdlrqEyBuVKgCeLgLB tMLuh5G63Zr3T1CvGpl2TL0= =We7X -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
granting grant option creates a (new) user ?!
MySQL : it seems that a user who has grant privilege can grant access to a(his) database to :another user although this other user doen't exist - in this case the other user is :newly created! can cause a mess in your usertable when you have a site with some :databases to administer - 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
Threads or Prozesses on Linux, and how to control them?
[Please CC: directly to me, I'm not subscribed] Hi, maybe a pointer to the documentation is sufficient, but does mysqld uses linux-threads or processes? And is there way to control them? After I start up mysqld I see 3 processes with ps. After some time (e.h. 12 hours) I spotted about 24 (!) of them. I've enabled sql logging and see that only every few seconds requests are comming, even more time. I never see the number if processes shrinking and I don't remember setting a limit somewhere (compared to apache where I can control this). On http://www.mysql.com/doc/M/y/MySQL_threads.html I found Every connection has its own thread. A few senteces above on this page there were talks about tcp/ip, but I'm only using sockets for the whole time, so this applies there too? How long is the life-time of a connection if there's no request comming anymore? The mysqld is acesssed with PHP and I'm using normal mysql_connect() so connections aren't persisten so they should go after some time? thx for any hints, - Markus - 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: Threads or Prozesses on Linux, and how to control them?
On Sun, Jun 23, 2002 at 08:02:45PM +0100, Paul Roberts wrote : php will close the connection when the script finishes unless you are using mysql_pconnect which opens a persistent connection. Yup, ever resource besides persistent ones are cleaned up upon script termination. From: Joseph Bueno [EMAIL PROTECTED] To: Markus Fischer [EMAIL PROTECTED] mysqld uses threads (you already found the right documentation). How long is the life-time of a connection if there's no request comming anymore? The mysqld is acesssed with PHP and I'm using normal mysql_connect() so connections aren't persisten so they should go after some time? The thread is created when a client opens a new connection and destroyed when the client closes the connection (unless you explicitely ask mysqld to keep some of them, see 'thread_cache_size' server parameter). Ok, I do not use thread_cache_size and looking into the mysql documentation the default thread_cache_size is 0. Since PHP then closes the connection, how is it possible that there are about 22-24 threads running? This site is my all means not a busy site. If I check the sql log it takes up to 5 to 10 seconds before a new query occurs which means that I seldomly get two connection attempts in the same second ... It doesnt seem to exceed the 24 limit though. just curious and thanks for the inforamtion, - Markus - 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
mysqladmin segfaults on Solaris 8
Hello list! While monkeying around with my testserver I noticedm that giving goofy parameters to mysqladmin causes it to segfault and dump core : (haven't got mail configured on the server, so I just snatched the info I thought was of importance) bash-2.03# uname -a SunOS simon 5.8 Generic_108529-11 i86pc i386 i86pc bash-2.03# uptime 12:57pm up 119 day(s), 20 min(s), 2 users, load average: 0.02, 0.03, 0.02 bash-2.03# perl -v This is perl, version 5.005_03 built for i86pc-solaris bash-2.03# mysqladmin -V mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-solaris2.8 on i386 bbash-2.03# mysqladmin version mysqladmin Ver 8.23 Distrib 4.0.1-alpha, for pc-solaris2.8 on i386 Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.1-alpha-log Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 3 min 11 sec Threads: 1 Questions: 140 Slow queries: 0 Opens: 42 Flush tables: 1 Open tables: 36 Queries per second avg: 0.733 bash-2.03# mysqladmin -E -i 2 extended-status Segmentation Fault (core dumped) MySQL was compiled from source and has been working flawlessly for the past 118 days. I realize that -E and extended-status is kind of... well, goofy, but it illustrates my point. Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library +358-6-325 3589 / +358-40-832 6709 - 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: GRANTs and %
On Tuesday 11 Jun 2002 11:04 am, Kaan Oglakci wrote: Thanks Dan And Okan for replying to my email but I have tried what you have said but I still get the same problem. GRANT ALL ON newstesting.* 'clients'@'%' IDENTIFIED BY '123'; ERROR 1064: You have an error in your SQL syntax near ''clients'@'%' IDENTIFIED BY '123'' at line 1 GRANT ALL PRIVILEGES ON newstesting.* TO 'clients'@'%' IDENTIFIED BY '123'; You're missing the 'TO'. Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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 foreign key constraints
Hello list!
I'm having a bit of trouble getting foreign key constraints to work.
I'm running MySQL 2.23.50-Max.
Here's what I got:
mysql SHOW CREATE TABLE conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(10) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(80) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
mysql SHOW CREATE TABLE ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`id`),
UNIQUE KEY `name_id` (`name_id`),
KEY `ip_idx` (`name_id`)
) TYPE=InnoDB
1 row in set (0.00 sec)
Here's what I get:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn(name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
I know error 1005 with an errno 150 means the foreign key would be incorrectly
formed, but I can't just figure out what's wrong.
I've been reading TFM, and the sentence there must be an index where the
foreign key and the referenced key are listed as the first columns seems to
have something to do with my problem, I just find the above a bit... well...
cryptic. If anyone could lend me a hand, I'd be more than happy.
Thank's in advance!
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:17 am, you wrote:
First of all the referenced key must be on PRIMARY KEY.
...which means my 'id' -field can't be a primary key, right?
But I've seen in your table definition a quite strange thing. You have a
UNIQUE and an ORDINARY key definition on the same field.
Here:
UNIQUE KEY `name_id` (`name_id`), - THIS IS THE FIRST DEFINITION
UNIQUE KEY `comp_name` (`comp_name`),
KEY `conn_idx` (`name_id`) - AND THIS IS THE SECOND ONE
I've probably messed something up while fooling around with indexes and trying
to get it to work. Now, I took the advices I got, but I still can't get it to
work. I altered the table to make the 'id' -field an ordinary index, and
changed the name_id -field to a primary key, without luck. So now my
SHOW CREATE TABLE gives:
mysql show create table ip_name_tbl\G
*** 1. row ***
Table: ip_name_tbl
Create Table: CREATE TABLE `ip_name_tbl` (
`id` int(10) unsigned NOT NULL auto_increment,
`comp_loc` smallint(5) unsigned NOT NULL default '0',
`comp_sub_loc` smallint(5) unsigned NOT NULL default '0',
`ip_stat_dyn` enum('DHCP','STATIC') NOT NULL default 'DHCP',
`IP` varchar(15) default NULL,
`MAC` varchar(17) NOT NULL default '',
`network` enum('Hallinto','Asiakas') NOT NULL default 'Hallinto',
`name_id` int(11) NOT NULL default '0',
`comments` text,
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
...and
mysql show create table conn\G
*** 1. row ***
Table: conn
Create Table: CREATE TABLE `conn` (
`id` int(10) unsigned NOT NULL auto_increment,
`wall_nr` int(6) unsigned NOT NULL default '0',
`hub_switch` varchar(20) NOT NULL default '',
`comp_name` varchar(50) NOT NULL default '',
`name_id` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`name_id`),
KEY `id` (`id`)
) TYPE=InnoDB
and, again:
mysql ALTER TABLE ip_name_tbl
- ADD CONSTRAINT FOREIGN KEY (name_id)
- REFERENCES conn (name_id)
- ON DELETE CASCADE;
ERROR 1005: Can't create table './koneet/#sql-355_4.frm' (errno: 150)
*sigh*
Cheers,
Markus
--
Markus Lervik
Linux-administrator
Vaasa City Library - Regional Library, Finland
[EMAIL PROTECTED]
+358-6-325 3589/+358-40-832 6709
-
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: InnoDB foreign key constraints
On Monday 10 Jun 2002 11:44 am, Markus Lervik wrote: mysql show create table ip_name_tbl\G *** 1. row *** Table: ip_name_tbl Create Table: CREATE TABLE `ip_name_tbl` ( [snip] `name_id` int(11) NOT NULL default '0', [snip] mysql show create table conn\G *** 1. row *** Table: conn Create Table: CREATE TABLE `conn` ( [snip] `name_id` int(10) unsigned NOT NULL default '0', [snip] Ok, thank's to Jocelyn the problem is solved and can be seen above. Kinda makes sence that both fields should be the same. : ) Slipped my attention ; ) Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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 not supporting php.
On Monday 03 Jun 2002 9:50 am, David Grant wrote: mysql support is not available to php on this server This is the error I receive. MySQL is working fine, but I need to know how get php working with sql. PHP isn't compiled with MySQL support. Recompile php and add --with-mysql to the configure script. Also, does anyone know the command to list all the db's? thanks. SHOW DATABASES; Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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: INSERT IF NOT EXISTS?
(feed the hungry filters: sql, query) On Monday 27 May 2002 2:14 pm, Victoria Reznichenko wrote: ML Is there a simple way to do something like INSERT IF NOT EXISTS, ML other than first doing a SELECT and checking if it returns any rows? ML If not, that would be pretty high up on my whish-list : ) You can use IGNORE keyword in INSERT statement, but it's not quite the same as INSERT IF NOT EXIST. If you specify IGNORE, any rows that duplicate an existing PRIMARY or UNIQUE key in the table will be ignored in INSERT: Yes, I was aware of the INSERT IGNORE statement, but I've only got one primary/unique key, which happens to be the id. Perhaps I should elaborate further. I've got three tables; ip_name_tbl, loc_tbl and conn_tbl. ip_name_tbl has got fields id, comp_loc, ip_stat_dyn, ip, mac, network, name and comments. loc_tbl has got fileds id, comp_loc and loc_name and conn has got fileds id, wall_nr, hub_switch_nr, comp_id. ip_name_tbl.comp_loc points to loc_tbl.comp_loc=20 conn.comp_id points to ip_name_tbl.id. I want to be sure that while inserting values, say NULL (since id is AUTO_INCREMENT), 1 and Administration, administration isn't already in the table. Rob's idea is of course a solution, but that sort of brings me back to my original question : is there a -SIMPLE- way? ; ) INSERT IGNORE wouldn't, if I understood the manual correctly, help me here, since the other rows in the table aren't UNIQUE, no? Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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
INSERT IF NOT EXISTS?
(filter fodder: sql, query) Hello group! Quick question: Is there a simple way to do something like INSERT IF NOT EXISTS, other than first doing a SELECT and checking if it returns any rows? If not, that would be pretty high up on my whish-list : ) Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library, Finland [EMAIL PROTECTED] +358-6-325 3589/+358-40-832 6709 - 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
mysqladmin hanging on Solaris 8/Intel
Hello! I have a strange problem with mysqladmin on Solaris 8. I can't seem to shut down the server via mysqladmin -uroot -p shutdown. It just... well... sits there. Hitting ctrl-c gives bash-2.03$ mysqladmin shutdown ^CWarning; Aborted waiting on pid file: '/opt/db/simon.pid' after 121 seconds bash-2.03$ The Pidfile says 7962. ps -ef says mysqld is running with PID 7962. Connecting to the mysql server after running mysqladmin and terminating it works, but the uptime has been reset. I really wouldn't want to get rough with it, so how do I take it down gracefully with minimum danger of losing data? Cheers, Markus -- Markus Lervik Linux-administrator Vaasa City Library - Regional Library +358-6-325 3589 / +358-40-832 6709 - 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
AW: newbie problem
Hi there's a 'data' directory in your mysql directory. This one contains one subdirectory for each of your databases. Just copy the one you need to your cd and place it in the data directory on the othe computer. markus -Ursprüngliche Nachricht- Von: Lalit Nagpal [SMTP:[EMAIL PROTECTED]] Gesendet am: Freitag, 10. Mai 2002 10:11 An: [EMAIL PROTECTED] Betreff: newbie problem hello friends, mysql .. i just want to move my database from one computer to another ... no lan ... can be taken on a cd ... how to plz tell me thanx a lot Lalit Nagpal - 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/JOIN problems
Hello, list! (filter-fodder: sql,query) I've got a problem that's been bothering me for quite some time: If I've got two tables, where I list people who's paid their membership fee, year, etc., and another table with the names tbl_membernames : name_id, firstname, lastname tbl_paid: name_id, year how do I get the members who hasn't paid a particular years fee? So I'd somehow need to do something like SELECT m.firstname,m.lastname FROM tbl_membernames AS m, tbl_paid AS p WHERE (m.name_id=p.name_id AND m.name_id isn't in tbl_paid when tbp_paid.year=2000) I've tried just about anything I can think about, but nada. I'd assume that if it's at all possible it'd be some join-trickery, but I just can't figure it out. (yes, I know it'd be easier db-wise to have a 'paid ENUM(y,n)' -field, but this is just a curiosity. I want to know. ;) Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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
AW: AW: MySQL on OS X
Hi everyone, hi Erik With the instructions and the software package provided on http://www.entropy.ch/software/macosx/mysql/ it was really easy to get a running version of MySQL (my preliminary goal). I'm not sure if I can follow all your explanations about the server and client differences of MySQL but as long as I can work with MySQL it's fine for me. The true reason why I'm not able to compile sql is still a big question for me, but as mentioned before I've reached my goal. Finally I have to say thank you for the fast respond and I'm convinced it was not my last question about MySQL on OS X Markus -Ursprungliche Nachricht- Von: Erik Price [mailto:[EMAIL PROTECTED]] Gesendet: Donnerstag, 25. April 2002 17:33 An: Markus Ungersboeck Cc: [EMAIL PROTECTED] Betreff: Re: AW: MySQL on OS X Greetings from Massachusetts (USA), On Thursday, April 25, 2002, at 11:04 AM, Markus Ungersboeck wrote: I've tried both kinds, the binary package and the source installation. I've also the Developer Tools 10.3.1 (but a few days ago I've upgraded my OS to 10.1.4) on my computer and I have the libpthread.dylib library in the /usr/lib/ directory, but not libpthread.A.dylib. As I'm a complete newbe concerning the commandline installation of software I've tried it first with the binary without success. Maybe I should reinstall the Developer Tools. Just to make sure we're on the same level, I'm going to tell you some information you probably already know: - Developer tools provide you with a compiler that you need to build C and C++ (and other languages) source code into binary code - There are two different ways to put MySQL on your Mac OS X system: Install the precompiled binary, or build your own binary from source code - The MySQL binary on the web site, that is built for Mac OS X, does NOT need to be compiled, so you do NOT need the developer tools to use it Erik - what version of SQL (and installation method) have you used? I've successfully installed MySQL 3.23.45 from source code on 10.1.2 using the September 2001 Developer Tools, but that version had a bug when used on OS X that prevented it from shutting down properly. So when 3.23.46 came out, I built that to replace the other one. Shortly afterward, MySQL started providing pre-compiled binary packages on their site for Mac OS X users (as they had been doing for Linux and other Unix users). With this, nobody even needs to build their own unless there is something specific they have in mind. Just to clarify -- MySQL is the name of a database management system, and includes both a server and client programs. You never directly use the server, rather, you use client programs like mysql (note the case difference) to access the server in a human-friendly way. When you install (either the source or the binary) MySQL, you get both. After learning how to use MySQL on my Mac OS X machine, I built it a third time on a Linux box (3.23.46), and removed the Mac OS X installation for disk space. But rather than ssh/telnet to the Linux box to access the mysql client program, you can use the mysql client program remotely. So I downloaded the binary package of MySQL for Mac OS X, and although I don't use the server that is included in it, I use the client all the time. And the beauty of this is that I didn't have to go through the hassle of compiling. The moral of the story? You can build from source, or you can just use a binary. I've tried both, and unless you have a special need for a customized source install, there is really no difference. The link I sent you that I got from the other guy (for www.entropy.ch) is one place that you can get a pre-compiled MySQL binary package, although I haven't used it many people say it is a good package. I can vouch for the binary package provided on the MySQL web site, which is built specifically for Mac OS X. (I've only used it on 10.1.3, but there is hardly any difference between 10.1.3 and 10.1.4 for these purposes.) I recommend you just use a binary. Erik PS: if you have any other questions please don't hesitate to ask. Erik Price Web Developer Temp Media Lab, H.H. Brown [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
MySQL on OS X
I've tried to install the current release of MySQL on my Macintosh under OS X (10.1.4) and I've got the following error message: dyld: ./bin/mysqld can't open library: /usr/lib/libpthread.A.dylib (No such file or directory, errno = 2) Installation of grant tables failed! Where can I get this library for my system? Tx Markus - 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
AW: MySQL on OS X
I've tried both kinds, the binary package and the source installation. I've also the Developer Tools 10.3.1 (but a few days ago I've upgraded my OS to 10.1.4) on my computer and I have the libpthread.dylib library in the /usr/lib/ directory, but not libpthread.A.dylib. As I'm a complete newbe concerning the commandline installation of software I've tried it first with the binary without success. Maybe I should reinstall the Developer Tools. Erik - what version of SQL (and installation method) have you used? Greetings Markus - On Thursday, April 25, 2002, at 03:23 AM, Markus Ungersboeck wrote: I've tried to install the current release of MySQL on my Macintosh under OS X (10.1.4) and I've got the following error message: dyld: ./bin/mysqld can't open library: /usr/lib/libpthread.A.dylib (No such file or directory, errno = 2) Installation of grant tables failed! Where can I get this library for my system? Are you trying to do a source install, or the binary provided on the MySQL web site? On my system, /usr/lib/libpthread.dylib is a link to /usr/lib/libSystem.dylib, which is a link to /usr/lib/libSystem.B.dylib I'm running 10.1.3, with the Dec2001 Developer Tools installed. Perhaps it was provided by those, do you have them installed? Erik Erik Price Web Developer Temp Media Lab, H.H. Brown [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
A bit OT: ColdFusion can't connect to MySQL via ODBC (newbie)
Hello, list! I'm not quite sure that this is the right place to ask, but I'm having problems getting MySQL and ColdFusion Server 5 to work together. I have a ColdFusion server that connects to MySQL via ODBC. I've configured the Merant MySQL ODBC driver via the ColdFusion administrator, and it is able to connect to my MySQL database (at least it claims that the connection was successful when clicking verify in the CF admin). The datasource name is hpTest. However, trying to connect to the database in an CFM doesn't seem to work at all. My error message is ODBC Error Code = 08003 (Connection not open) [MERANT][ODBC lib] Connection not open The error occurred while processing an element with a general identifier of (CFQUERY), occupying document position (2:1) to (2:114). and the code goes like this: cfquery name=Test dbtype=ODBC dbname=hpTest datasource=hpTest SELECT * FROM hpTest /cfquery cfoutput query=hpTest#FieldName1#, #FieldName2#br/cfoutput I've been reading TFM, browsing TFW, reading TFML and I've even been to (*ghasp*) Microsofts ODBC-pages looking for an answer (and you know it's -bad- when that happens!g). Reading the manual I got the impression that coldfusion would open the connection when using a cfquery-tag. Any help is greatly appreciated. Cheers, Markus, tearing big lumps of hair out. -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: mysqld cannot find host.frm even though it is in data/mysql
On Sun, 2002-03-03 at 08:39, root wrote: Description: Down loaded bin/mysqld Ver 3.23.49-max for pc-linux-gnu on i686 from U of Wisc site gunzipped and untarred in /usr/local ran mysql_install_db then ran ./bin/safe_mysqld from install directory kept coming up with error 020303 5:20:41 /usr/local/mysql/bin/mysqld: Can't find file: './mysql/host.frm ' (errno: 13). Did you read the README file? [mle@hal9000 rip]$ perror 13 Error code 13: Permission denied chown -R mysql.mysql mysql/ ought to sort it out. -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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 error
On Sat, 2002-02-23 at 01:52, theOtherOne wrote: Hi, I've got this problem when i try to connect to a mysql server with a PHP code Warning: MySQL Connection Failed: Can't connect to local MySQL server through socket '/tmp/mysql.sock' (111) [mle@hal9000 data]$ perror 111 Error code 111: Connection refused That would indicate that you haven't got a mysql-server running. If it is running, check that /tmp/ actually is where the socket-file is, and check your my.cnf-file in case the [mysqld] section contains skip-networking. If it does, remove it (naturally). Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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
myisampack, CHECK TABLE REPAIR TABLE
Hello all, I would like to compress some tables which have an archive purpose and thus will not suffer from the restriction of using myisampack, which implies that the tables will be read only after packing. However, we also have a database problem detection program, which will check each table in specified intervals with CHECK TABLE and if that reports a problem with REPAIR TABLE. Is it possible without a problem to run these two commands on a packed table? I couldn't find anything specific on this topic in the manual. Or is it a grey area which has not been tested yet? The MYSQL server version in use is: 3.23.47-log on Linux thanks, Markus - 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: for You
On Wednesday 30 January 2002 13:02, Marjolein Katsma wrote: At 01:13 2002-01-30, Engr Duke Paul [EMAIL PROTECTED] wrote: NIGERIAN NATIONAL PETROLEUM CORPORATION (DEPARTMENT OF PETROLEUM RESOURCES) And he subscribed to this list, right? Grrr: database,sql,query,table Stop griping. I'm annoyed about it too, but every mail complaining about spam becomes quite quickly spam itself. You've got three options as I see it (and this goes for all of you anti-spam-spammers, too): a) Ignore the spam. It's about two-three mails per day. It's not that big a deal. Just hit the delete-button, and it's been taken care of. b) Set up mail-filtering. A filter that directs mails containing NIGERIAN PETROLEUM CORPORATION straight to the waste bin. A few days, a few spam-mails and you'll have a working anti-spam-filter right at home. (See? If you'd have that, you wouldn't even get this mail.) c) Unsubscribe from the list if it bothers you that much. We've (unfortunately) seen at least one individual unsubscribing, not too long ago, because of the spam. Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: Porting from MS SQL to MySQL
On Monday 21 January 2002 17:13, you wrote: I have to agree with the below. Clearly they want to get you into mssql and keep you there. There's really no point in switching databases, so (I know, this is too neutral) if you like these guys for some reason (good haircuts or whatnot), then go with mssql. If you can find people with good haircuts who will do it for mysql, all the better (and sometimes even cheaper). Regardless, don't migrate to a different database unless there's some huge reason to do so. Particulary as I want to get rid of our only Windows 2000 server : ) As I stated before, in our request for offers on the database (whatever the correct term might be), we specifically said that we wanted MySQL or PostgreSQL, which makes even more stupid to offer us MS SQL, because they (ought to) know that we won't take them in that case. Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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
MySQL server not listening on port?
Hi! For some strange reason MySQL doesn't listen for incoming connections on port 3306. I have four servers, two of which are 4.0.1 and two are 3.23.47. I can connect between three of them freely, but one, shodan (yes. *g*) doesn't allow it. localhost logins are apparently ok, but not from another machine. [mle@hal9000 mle]$ mysql -uroot -p -hshodan Enter password: ERROR 2003: Can't connect to MySQL server on 'shodan' (111) perror says the following : Error code 111: Connection refused. Checking netstat on shodan gives the following : mle@shodan:/$ sudo netstat --ip -p -a Active Internet connections (w/o servers) Proto Recv-Q Send-Q Local Address Foreign Address State PID/Program name tcp0 0 localhost:ldap localhost:1081 ESTABLISHED 649/stunnel [snip away truckloads of stunnels and other stuff] I just can't figure out why mysql doesn't show up. Here's the netstat --ip -a output on hal9000 : Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp0 0 *:mysql *:* LISTEN tcp0 0 *:ssh *:* LISTEN [snip] and if I throw in a -p, too, we clearly see that MySQL is listening. tcp0 0 *:mysql *:* LISTEN 3079/mysqld-max Summa summarum : For one reason or the other, the MySQL server on shodan doesn't accept incoming connections. hal9000 accepts them, xerxes accepts them and skynet accepts them, but not shodan. Shodan and skynet are identical machines, with debian 2.2 apt-get dist-upgraded once and I just plainly apt-got (*g*) mysql from a mirror. The configurationfiles are just about identical on the two machines. Ideas? Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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 server not listening on port?
On Tuesday 22 January 2002 14:32, Markus Lervik wrote: Hi! For some strange reason MySQL doesn't listen for incoming connections on port 3306. I have four servers, two of which are 4.0.1 and two are 3.23.47. I can connect between three of them freely, but one, shodan (yes. *g*) doesn't allow it. localhost logins are apparently ok, but not from another machine. mle@shodan:/$ sudo netstat --ip -p -a Active Internet connections (w/o servers) Sorry, that's actually supposed to be mle@shodan:~$ sudo netstat --ip -a Active Internet connections (servers and established) Proto Recv-Q Send-Q Local Address Foreign Address State tcp0 0 *:ldap *:* LISTEN [snip again, no mysql nor port 3306 anywhere to be seen] I might add that everything else works fine, ssh, ldap, nfs, you name it, and shodan is on the same physical network as the rest of the computers, so there's no firewall between them, nor do I have iptables/ipchains enabled on the machine. Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: Porting from MS SQL to MySQL
On Friday 18 January 2002 17:30, you wrote: We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? 2) If you want MySQL or PostgreSQL, why exactly would you want to install MSSQL, and then upgrade later? Both PostgreSQL and MySQL will run on Windows (although again, I'm not sure why you would want to do that if you have a choice). That's the point. I have no idea whatsoever as to why this company wants to FIRST do the database on MS SQL and THEN port it to MySQL. Unless they've got an ready made database that they think will fit our needs and then after a while port that to MySQL. But concidering the number of planning and workhours they put in to the offer, it really can't be true, because other companies had the same amount (or less) work hours for making it directly MySQL. Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: Porting from MS SQL to MySQL
(food for ill-working-anti-spam-filters : database,sql,query,table) Just wanted to thank everyone who got involved in this (at times rather heated) discussion. You've given me quite a few pointers to think about and stuff to concider and take up with our project manager. Cheers mates! Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: compiling and/or running 64-bit MySQL on Solaris 8/sparc?
Forgot to CC to the list. Here it goes, in case anyone else has these problems: -- Forwarded Message -- Subject: Re: compiling and/or running 64-bit MySQL on Solaris 8/sparc? Date: Fri, 18 Jan 2002 15:06:57 +0200 From: Markus Lervik [EMAIL PROTECTED] To: Hatton Steven [EMAIL PROTECTED] On Friday 18 January 2002 13:57, you wrote: [BIG snip] checking return type of sprintf... configure: error: can not run test program while cross compiling bash-2.03# [/snip] Try adding export LDFLAGS='-R/usr/local/lib' or export LDFLAGS='-R/opt/sfw/lib' before ./configure. Cheers, Markus -- Markus Lervik UNIX-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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
Porting from MS SQL to MySQL
Hello all! We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: Porting from MS SQL to MySQL
On Friday 18 January 2002 15:58, you wrote: On Fri, Jan 18, 2002 at 03:16:15PM +0200, Markus Lervik wrote: We've requested a database from different companies, and specifically said we wanted MySQL or PostgreSQL because of the open source angle and we're a library. One company offered MS SQL as the platform and said that they can later on port it to MySQL. For this they wanted 18 000 euro. Now, what I want to know is, how easy is it to port a (fairly complicated) database from MS SQL to MySQL? It can't be work worth 18 000 euro, now can it? That's a bit strange. If the app is built with MySQL in mind, porting it should be very, very easy. But if they're going to build the app with MySQL in mind anyway, it doesn't make much sense to do so on a platform other than MySQL, does it? One wouldn't think it does. It raises a few questions. Mainly one of trying to rip us off. The main reason we want the database on MySQL or PostgreSQL (apart from the speed issue) is that our Win2K server is going to be buried and a nice, shiney Linux-server is taking it's place next to the one we already got. Being involved in this project, one becomes amazed with the amout of eye-pissing companies really can get away with. And respected companies too. (I won't mention any names, I don't want any lawsuits : ) For instance, one offered to come and sit down and discuss our needs with us (they calculated that it'd take about 40h for us to repeat what we've already said) and wanted 28 000 euro for it. Cheers, Markus -- Markus Lervik Linux-administrator with a kungfoo grip Vaasa City Library - Regional Library [EMAIL PROTECTED] +358-6-325 3589 / +358-40-832 6709 - 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: C# and MySQL without ODBC
On Mon, Jan 14, 2002 at 03:03:11PM +0200, Sinisa Milivojevic wrote : Markus Fischer writes: Does someone know if there's a native C# implementation available OR a wrapper around libmysqlclient for C#? As far as I know, no not yet. Ok, thx. But we are preparing our strategy on how to tackle the entire issue. Hmm .. could you be a bit more elaborative (sp?) please what you mean with this? thank you - Markus - 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 without ODBC
[Please CC me, I'm not subscribed] Does someone know if there's a native C# implementation available OR a wrapper around libmysqlclient for C#? thx, Markus - 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
MySQL dies
Hi, I'm in desperate need of help. MySQL keeps dying and I just can't figure out why... Here goes: Clients connect from Windows machines via VB and Delphi apps through myodbc doing queries on databases with *big* tables containing over 1.6 million records Often they're losing connection because mysql dies, queries have to be done again. Error log says that mysqld restarted, i.e. safe_mysqld immediately restarts the server. Only mysql is running on the machine. Queries may look like this: CREATE TEMPORARY TABLE tmpTable(FRM varchar(5),ARKL varchar(5),KNR varchar(10),ZWT float(11,5),VPN float(11,5),GRS varchar(32),MNG float(11,5),BRW float(11,5), NTW float(11,5)); INSERT INTO tmpTable(FRM,ARKL,KNR,ZWT,VPN,GRS,MNG,BRW, NTW)SELECT left(MNR,5),right(MNR,5),KNR,ZWT/MNG,VPN,substring_index(GRS,'X',1)* right(substring_index(GRS,'X',2),length(substring_index(GRS,'X',2))-length(s ubstring_index(GRS,'X',1))-1)* right(GRS,length(GRS)-length(substring_index(GRS,'X',2))-1)/1000,MNG,BRW,NTW FROM MASTER WHERE (BLDAT = 20010901 AND BLT = 20011231 AND SRT = '04' AND SRT = '06') MASTER is one *big* table containing 1.6 Million records... Info on the system: netbsd 1.5.2, 512 mb RAM mysql --version mysql Ver 11.13 Distrib 3.23.35, for -netbsd (i386) mysqladmin variables: +-+- --+ | Variable_name | Value | +-+- --+ | ansi_mode | OFF | | back_log| 50 | | basedir | /usr/pkg/ | | binlog_cache_size | 32768 | | character_set | latin1 | | character_sets | latin1 dec8 dos german1 hp8 koi8_ru latin2 swe7 usa7 cp1251 danish hebrew win1251 estonia hungarian koi8_ukr win1251ukr greek win1250 croat cp1257 latin5 | | concurrent_insert | ON | | connect_timeout | 10 | | datadir | /usr/var/mysql/ | | delay_key_write | ON | | delayed_insert_limit| 100 | | delayed_insert_timeout | 300 | | delayed_queue_size | 1000 | | flush | OFF | | flush_time | 0 | | have_bdb| NO | | have_gemini | NO | | have_innobase | NO | | have_isam | YES | | have_raid | NO | | have_ssl| NO | | init_file | | | interactive_timeout | 28800 | | join_buffer_size| 131072 | | key_buffer_size | 16773120 | | language| /usr/pkg/share/mysql/english/ | | large_files_support | ON | | locked_in_memory| OFF | | log | ON | | log_update | OFF | | log_bin | OFF | | log_slave_updates | OFF | | long_query_time | 10 | | low_priority_updates| OFF | | lower_case_table_names | 0 | | max_allowed_packet | 1047552 | | max_binlog_cache_size | 4294967295 | | max_binlog_size | 1073741824 | | max_connections | 100 | | max_connect_errors | 10 | | max_delayed_threads | 20 | | max_heap_table_size | 16777216 | | max_join_size | 4294967295 | | max_sort_length | 1024 | | max_user_connections| 0 | | max_tmp_tables | 32 | | max_write_lock_count| 4294967295 | | myisam_recover_options | OFF | | myisam_sort_buffer_size | 8388608 | | net_buffer_length | 7168 | | net_read_timeout| 120 | | net_retry_count | 40 | | net_write_timeout | 240 | | open_files_limit| 0 | | pid_file| /usr/var/mysql/unixtest.pid | | port| 3306 | | protocol_version| 10 | | record_buffer | 131072 | | query_buffer_size | 1044480 | | safe_show_database | OFF | | server_id | 1 | | skip_locking| ON | | skip_networking | OFF | | skip_show_database | OFF | | slow_launch_time| 2 | | socket | /tmp/mysql.sock | | sort_buffer | 524280 | | table_cache | 64 | | table_type | MYISAM | | thread_cache_size | 0 | | thread_stack| 65536 | | timezone| CET | | tmp_table_size | 1048576 | | tmpdir | /var/tmp/ | | version | 3.23.35-log | | wait_timeout| 115200 | +-+- --+ | core dumps available... (they're BIG -21M) Help is very much appreciated... Marc - Before posting, please check:
SELECT ... INTO
I have been assigned the fun little job of porting a (very badly designed) database from Access97 to MySQL so that we'd finally get rid of that pesky Win2000-server. The database contains a list of magazines currently in our basement waiting for someone to borrow them. There are, at the moment, just over 4500 records in the database, but when someone starts inserting magazines, it'll be a tad over 100 000 records. Now, the problem is that I'd somehow like to get all the magazine names into another table, and references to that table in the first table without having to change all the records by hand. I have gotten the names of the magazines into a table with an auto_incremented primary id field, but how could I get all the same names away from both tables and only leave unique names? -- Markus Lervik Linux administrator with a kungfoo grip Vasa City Library - Regional Library - 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
