Re: Syntax problem: mysql 3.23 vs 4.13
Hello. Select syntax is correct on my 4.1.10a MySQL instance. EXPLAIN is telling me 'Impossible WHERE' until I put values in the tables so the query could produce at least several rows. After that everything was OK. Graham Anderson [EMAIL PROTECTED] wrote: this sql works on mysql version 3.23.58...my remote server SELECT c.City, r.Region, co.Country FROM subnets s, cities c, regions r, countries co WHERE c.CityId = s.CityId AND c.RegionID = r.RegionID AND c.CountryID = co.CountryId AND s.SubNetAddress = '24.24.172' LIMIT 0 , 30 but the same syntax fails on mysql version 4.1.3-beta ...my local computer When I EXPLAIN the sql, I get the error: Impossible WHERE noticed after reading const table... anyone know what this could be ? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The best way to transfer data to another server
Privet:) See: http://dev.mysql.com/doc/mysql/en/upgrading-to-arch.html http://dev.mysql.com/doc/mysql/en/moving.html Denis Gerasimov [EMAIL PROTECTED] wrote: Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) Best regards, Denis Gerasimov Outsourcing Services Manager, VEKOS, Ltd. www.vekos.ru -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Character Set problem
Hello. If you can reproduce this problem on several different installations, you may open a new bug (because #312 is closed) and leave there a note about bug #312. Stephen Moretti (cfmaster) [EMAIL PROTECTED] wrote: Gleb Paharenko wrote: Thanks for the reply. See: http://dev.mysql.com/doc/mysql/en/problems-with-character-sets.html Yeah Thanks - seen that already. Check that you have the charsets directory in c:\mysql\share. Again, thanks, but that doesn't actually solve the issue. There are entries in the Index file for the appropriate language number. There isn't, however, an xml file for the language (utf8 in this instance). I've tried changing the server default character set to cp1251. I've recreated complete databases from scratch making sure that the character set it uses is cp1251. None of the above have worked. Any other thoughts? This is mySQL 4.1.10-nt on win2003 server giving : File 'c:\mysql\share\charsets\?.conf' not found (Errcode: 22) ^GCharacter set '#33' is not a compiled character set and is not specified in the 'c:\mysql\share\charsets\Index' file which is classified as Bug number 312 (http://bugs.mysql.com/bug.php?id=312) Regards Stephen -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ERROR WHILE LOADING
hai, i was trying to install mysql 4 server on windows 2000 when configuringthe server i received the below error in mysql server instance configuration "could not start the service mysql error:0" can i know how to solve the error regards p rao No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 266.8.4 - Release Date: 3/27/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Central UDF project at mysql.com?
Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: The best way to transfer data to another server
- Original Message - From: Denis Gerasimov [EMAIL PROTECTED] To: MySQL General List mysql@lists.mysql.com Sent: Wednesday, March 30, 2005 1:12 AM Subject: The best way to transfer data to another server Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) It's not clear from your question whether you want to transfer the data once only or if you want to transfer it repeatedly, perhaps daily or weekly. If you mean the latter, you may want to look into MySQL Replication, which enables all the changes made to the data on one server to be automatically made to the other server as well. I haven't used MySQL Replication but I know it exists and is documented in the MySQL manual. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: power loss scenario
Wow, you are asking a lot, especially since an inexpensive UPS could be had for less than $50. You don't need one to keep the system up for a long time, just long enough for writes to finish. A few minutes should be plenty. I don't see a problem with IDE drives. Your drive access patterns are pretty straight forward. SCSI's big advantage is command queueing, which may not even come into play with your access patterns. Whatever file system you use, I would most definitely use journaling. First and foremost you need the system in a good state, then the DB. Journaling in the file system will also help in keeping the database intact. Raw partitions would buy you so little performance gain, it's really not worth the hassle. On the flip side, software mirroring of two IDE drives would give you such a little performance hit, it would be worth it for safety. And you'll get better read performance to boot. InnoDB would probably be better than MyISAM since InndoDB supports transactions. On Mar 30, 2005, at 2:47 AM, Florin Andrei wrote: Again the logging server i mentioned before: it's like syslog logging to a DB, lots of INSERTs, perhaps a few SELECTs every now and then, the tables are append-only and are rotated about once a day. For reasons that i am not going to discuss here, the machine has no uninterruptible power supply. Therefore, if the power goes down, bad things might happen to the database. Also, i don't have money for funky solutions such as solid-state disks. In fact, the disks will most likely be IDE (not even SCSI). What are the techniques that work best in such a situation to increase the chances for the database to survive a crash in a consistent state? Loosing a few recent INSERTs is not a problem (since some data will not be logged anyway while the server is down), but the DB in an inconsistent state is a big problem (the system has to boot up again unattended). I do not want to do such extreme things like turning off the write cache on the disk, because that would probably kill the performance. But how about Ext3 with data=journal? Using InnoDB would be better than MyISAM? How about raw partitions? Any other tips? -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Central UDF project at mysql.com?
That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Still, having them all in one place could be nothing but a good thing. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Mark Papadakis Head of RD Phaistos Networks, S.A -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Help with 813-MDB File
- Original Message - From: David Blomstrom [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 29, 2005 11:29 PM Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) Access is *not* a spreadsheet, it's a database. I don't have it either but I've used it. I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? Apparently, this is simple to do if you have Access; I believe you can just do an Export to CSV (or other formats) within Access. If you don't have Access, it could get somewhat more complex depending on what tools you have. I was curious about your question so I did a Google newsgroup search using these three terms: export MDB CSV and got lots of hits. Some of them talked about ways to read MDB files without having Access, with Perl for example. So I would suggest that you do a similar search, perhaps adding in some additional keywords that reflect the tools/languages you do have, to find out which ways are available to you. I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. I have no idea if that can be done without Access, let alone how to do it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-5.0.3 has been released
Hi! MySQL-5.0.3 is probably the most important new MySQL release in several years. On the MySQL side, a vast number of new features, like stored procedures and views, have been implemented in 5.0. On the InnoDB side, we changed the table format to a more space-saving one, and implemented the 2-phase commit XA protocol. Since 5.0.3 contains a lot of new features, we cannot yet recommend it for production use. This is a beta release for testing and developing your applications that take advantage of MySQL's new powerful features. InnoDB is the MySQL table type that supports foreign key constraints, transactions, row-level locking, non-locking consistent read (MVCC), all four SQL-1992 isolation levels of transactions, multiple tablespaces, asynchronous unbuffered disk I/O on Windows, and a non-free hot online backup tool. You can download mySQL-5.0.3 from http://dev.mysql.com/downloads/mysql/5.0.html Functionality added or changed: * Introduced a compact record format that does not store the number of columns or the lengths of fixed-size columns. The old format can be requested by specifying ROW_FORMAT=REDUNDANT. The new format (ROW_FORMAT=COMPACT) is the default. This typically saves 20 % of space compared to the old InnoDB table format. Note that the old tables that you have will still have the old table format. There is no automatic conversion when you upgrade to 5.0.3. New tables will by default have the new table format. * MySQL/InnoDB now supports two-phase commit of transactions, and the associated XA protocol. * Upgrading from 4.1: The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * When MySQL/InnoDB is compiled on Mac OS X 10.2 or earlier, detect the operating system version at run time and use the fcntl() file flush method on Mac OS X versions 10.3 and later. Apple had disabled fsync() in Mac OS X for internal disk drives, which caused corruption at power outages. * Implemented fast TRUNCATE TABLE. The old approach (deleting rows one by one) may be used if the table is being referenced by foreign keys. (Bug #7150) * Setting the initial AUTO_INCREMENT value for an InnoDB table using CREATE TABLE ... AUTO_INCREMENT = n now works, and ALTER TABLE ... AUTO_INCREMENT = n resets the current value. * Commit after every 10,000 copied rows when executing ALTER TABLE, CREATE INDEX, DROP INDEX or OPTIMIZE TABLE. This makes it much faster to recover from an aborted operation. * Added several InnoDB status variables. * A shared record lock (LOCK_REC_NOT_GAP) is now taken for a matching record in the foreign key check because inserts can be allowed into gaps. * Relaxed locking in INSERT...SELECT, single table UPDATE...SELECT and single table DELETE...SELECT clauses when innobase_locks_unsafe_for_binlog is used and isolation level of the transaction is not serializable. InnoDB uses consistent read in these cases for a selected table. * Added a new global system variable slave_transaction_retries: if the replication slave SQL thread fails to execute a transaction because of an InnoDB deadlock or exceeded InnoDB's innodb_lock_wait_timeout, it automatically retries slave_transaction_retries times before stopping with an error. The default is 10. Bugs fixed: * All the bug fixes from the MySQL-4.0 and 4.1 branches. Outstanding bugs: * If an SQL statement fails because an error, MySQL may fail to roll back the statement automatically. It should be rolled back or the whole transaction rolled back according to the ANSI SQL standards. This bug that was introduced in 5.0.3 and will probably be fixed in upcoming 5.0.4. * Column prefix PRIMARY KEYs do not work properly for multi-byte character sets. Upgrading from 4.1: * Starting from 5.0.3, a VARCHAR in MySQL is a 'true' VARCHAR. It remembers the number of spaces that there were at the end of the string. Previously, MySQL at storage trimmed the end spaces from a VARCHAR. Tables created with 5.0.3 will remain to have the old VARCHAR semantics, while new tables will have the new semantics. * The sorting order for end-space in TEXT columns for InnoDB and MyISAM tables has changed. Starting from 5.0.3, InnoDB compares TEXT columns as space-padded at the end. If you have a non-unique index on a TEXT column, you should run CHECK TABLE on it, and run OPTIMIZE TABLE if the check reports errors. If you have a UNIQUE INDEX on a TEXT column, you should rebuild the table with OPTIMIZE TABLE. * The sorting order of BINARY and VARBINARY may still change in some 5.0.x version. * There is a bug in the InnoDB sorting order of ENUMs if the collation of the ENUM is not latin1
RE: Need Help with 813-MDB File
Is there a description of tables anywhere on CD-ROM for Access database. Mikhail -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 9:10 AM To: David Blomstrom; mysql@lists.mysql.com Subject: Re: Need Help with 813-MDB File - Original Message - From: David Blomstrom [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 29, 2005 11:29 PM Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) Access is *not* a spreadsheet, it's a database. I don't have it either but I've used it. I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? Apparently, this is simple to do if you have Access; I believe you can just do an Export to CSV (or other formats) within Access. If you don't have Access, it could get somewhat more complex depending on what tools you have. I was curious about your question so I did a Google newsgroup search using these three terms: export MDB CSV and got lots of hits. Some of them talked about ways to read MDB files without having Access, with Perl for example. So I would suggest that you do a similar search, perhaps adding in some additional keywords that reflect the tools/languages you do have, to find out which ways are available to you. I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. I have no idea if that can be done without Access, let alone how to do it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- 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: The best way to transfer data to another server
Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) It's not clear from your question whether you want to transfer the data once only or if you want to transfer it repeatedly, perhaps daily or weekly. If you mean the latter, you may want to look into MySQL Replication, which enables all the changes made to the data on one server to be automatically made to the other server as well. Well, I need this only time to time, just for transferring data when I need. I assume that that is a bad idea to copy db files from one server to another. mysqldump is not an exciting option too... But there is a simple solution when using MySQL Administrator: make backup of desired data to SQL file, then connect to another server and restore it! I haven't used MySQL Replication but I know it exists and is documented in the MySQL manual. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- 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: The best way to transfer data to another server
On Wed, 30 Mar 2005, Denis Gerasimov wrote: Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) It's not clear from your question whether you want to transfer the data once only or if you want to transfer it repeatedly, perhaps daily or weekly. If you mean the latter, you may want to look into MySQL Replication, which enables all the changes made to the data on one server to be automatically made to the other server as well. Well, I need this only time to time, just for transferring data when I need. I assume that that is a bad idea to copy db files from one server to another. mysqldump is not an exciting option too... But there is a simple solution when using MySQL Administrator: make backup of desired data to SQL file, then connect to another server and restore it! The perl package 'mysqlhotcopy' looks good. I didn't try it yet. I haven't used MySQL Replication but I know it exists and is documented in the MySQL manual. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- 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: power loss scenario
On Wednesday 30 March 2005 10:49, Brent Baisley wrote: Wow, you are asking a lot, especially since an inexpensive UPS could be had for less than $50. You don't need one to keep the system up for a long time, just long enough for writes to finish. A few minutes should be plenty. Yeah, remember to put only the DB below UPS to force the logging hardware stop before DB. --rengolin -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Central UDF project at mysql.com?
On Wed, 30 Mar 2005, Mark Papadakis wrote: That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Cheers :) I think with the right infra more people would start using and developing UDF's, especially if the work was a part of 'MySQL' proper and not just different groups of individuals. I know it wouldn't make any *real* difference, but it would make a psychological difference. Also a central project would be a way to boost visibility for different UDF projects, for example good (ongoing) work could be showcased in the MySQL news letter. Still, having them all in one place could be nothing but a good thing. Yeah, I totally agree :) Especially if resources like the MySQL bugtracker and mailing lists could be shared. Is this the right forum for requesting such things? All the best, Dan. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- 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: Need Help with 813-MDB File
Thanks for all the tips. I was able to import it with Navicat. In fact, I was amazed at how smoothly it went. I think it imported about 30 separate tables - nearly 4 million rows - before Navicat froze. But I was working on some other programs and probably ran out of memory. It recorded over 8,000 errors - duplicate keys, etc. - but most of those seemed to be associated with viruses (REAL viruses, not computer viruses), which I'm not concerned about. Thanks again. --- Berman, Mikhail [EMAIL PROTECTED] wrote: Is there a description of tables anywhere on CD-ROM for Access database. Mikhail -Original Message- From: Rhino [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 9:10 AM To: David Blomstrom; mysql@lists.mysql.com Subject: Re: Need Help with 813-MDB File - Original Message - From: David Blomstrom [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, March 29, 2005 11:29 PM Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) Access is *not* a spreadsheet, it's a database. I don't have it either but I've used it. I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? Apparently, this is simple to do if you have Access; I believe you can just do an Export to CSV (or other formats) within Access. If you don't have Access, it could get somewhat more complex depending on what tools you have. I was curious about your question so I did a Google newsgroup search using these three terms: export MDB CSV and got lots of hits. Some of them talked about ways to read MDB files without having Access, with Perl for example. So I would suggest that you do a similar search, perhaps adding in some additional keywords that reflect the tools/languages you do have, to find out which ways are available to you. I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. I have no idea if that can be done without Access, let alone how to do it. Rhino -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.308 / Virus Database: 266.8.3 - Release Date: 25/03/2005 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Central UDF project at mysql.com?
I also think this would be a good idea. I have written some UDF's for specific internal use and often wondered if they would be useful to others. It would be nice if there was a centralized place to search for UDFs, so you don't have to re-invent the wheel everytime you need a function. I think that the community list may be the appropriate list for disscussion on how to get this going. I believe it is run by Arjen Lentz. I have CC'd him on this message. Maybe he can give some input. Regards, -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa On Wednesday, March 30, 2005 09:12, Dan Bolser wrote: On Wed, 30 Mar 2005, Mark Papadakis wrote: That is a really good idea - though I am not sure there is much of those out there to justify the cause, nor many developers actually using UDFs. Cheers :) I think with the right infra more people would start using and developing UDF's, especially if the work was a part of 'MySQL' proper and not just different groups of individuals. I know it wouldn't make any *real* difference, but it would make a psychological difference. Also a central project would be a way to boost visibility for different UDF projects, for example good (ongoing) work could be showcased in the MySQL news letter. Still, having them all in one place could be nothing but a good thing. Yeah, I totally agree :) Especially if resources like the MySQL bugtracker and mailing lists could be shared. Is this the right forum for requesting such things? All the best, Dan. MarkP On Wed, 30 Mar 2005 14:39:11 +0100 (BST), Dan Bolser [EMAIL PROTECTED] wrote: Hi, I searched for previous discussion on this topic, but didn't find any. I would like to see a centralized MySQL hosted UDF archive and development project. The only existing 'archives' seem to be somewhat poorly maintained (sorry), and suffer for their duplicated efforts and being loosely distributed throughout the web. The best I can find are here (ranked according to Google)... http://empyrean.lib.ndsu.nodak.edu/~nem/mysql/udf/ http://www.oreillynet.com/pub/wlg/2292 http://www.thecodeproject.com/Purgatory/mygroupconcat.asp http://mysql-udf.sourceforge.net/ http://www.linuxjournal.com/article/6841 I think a centralized project would do wonders for the UDF community, allowing UDF's to be discussed, suggested and developed under one roof. A first step should be to create a [EMAIL PROTECTED] mailing list. Without such a central list the UDF community can't communicate effectively. Who better than MySQL to organize the MySQL UDF community? A simple not officially supported statement is all that is needed. Good UDF's could become part of MySQL proper, and a UDF 'bundle' would be a great development. MySQL programmers could help build UDF's, and the community could vote on 'wanted' functions. You could probably guess where all this is going, and that is towards my own UDF request (where to ask?), but I will leave that for later. Any comments? Any postings that I have missed? Any reason that their is no udf mailing list? I think that their are tons of UDF's waiting to happen, given the right conditions. All the best, Dan. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: upgrading mysql on RH fedora core 3
hey... i now have FC2 running mysql-4.1.10a, php5, apache 2.0.51. for some reason, the mysql rpms that i got from rpmfind.net/rpm.pbone, etc... didn't seem to work, as i kept getting weird lib related errors... i started using mysql-3.23 on FC2. i basically followed the path/instructions from http://www.whoopis.com/howtos/php5-mysql4-FC3-rpm.html, replacing my system/requirements where applicable. basic steps: 1) use 'rpm -qa | grep -i mysql' to get all rpms dealing with mysql 2) remove these rpms 'rpm -e ...' 3) get the replacement rpms from www.mysql.com 4) upgrade using 'rpm -Uvh ' 5) resolve any errors/issues... for my system, i only needed to focus on the mysql, as i already had php5 up/running. i chose this approach, as i didn't want to deal with rebuilding from source at this time. good luck!! -Original Message- From: Florin Andrei [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 8:40 PM To: mysql@lists.mysql.com Subject: Re: upgrading mysql on RH fedora core 3 On Tue, 29 Mar 2005 19:28:56 -0800, bruce [EMAIL PROTECTED] wrote: we're trying to install mysql/mysql-server (4.1.10a-1.i386) and are running into some serious problems. we had mysql/server (3.23.52-3.i386) running, but needed to go to the higher version... I was about to attempt the same thing. My thinking was to grab the mysql src.rpm from Fedora Core 4 test 1 and rebuild it on FC3: rpmbuild --rebuild mysql...src.rpm Please try that and see how it goes. also, this has to be running with apache/php/perl/etc... My feeling is that apache does not need to be rebuilt. PHP certainly does, from src.rpm, after the new mysql is installed (including mysql-devel). I am not sure whether it would just work to grab the PHP src.rpm from FC3 updates and rebuild it on top of the new mysql, or get the PHP src.rpm from FC4-test and rebuild. Perl is in the same situation, but it's probably even more complex. If i were you, i would probably post on the fedora-test mailing list and ask the same question. Anyway, good luck and let us know how it goes. -- Florin Andrei http://florin.myip.org/ -- 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]
Index on boolean column
Hello, maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? TIA, Dusan
Re: Index on boolean column
Du?an Pavlica [EMAIL PROTECTED] wrote on 30/03/2005 16:35:40: Hello, maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Since I believe that MySQL ignores indexes if it expects to get more than 30% hits, it will probably be ignored unless the distribution of 0s and 1s is very skewed. If you only have a tiny fraction of (say) 1s, it might be useful to extract that tiny fraction - but useless for the opposite. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY, ORDER BY clauses
Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on boolean column
maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Perhaps, instead of the index, you might revise your schema a bit. Why do you have this boolean column? What are you trying to achieve? With regards, Martijn Tonies Database Workbench - developer tool for InterBase, Firebird, MySQL MS SQL Server Upscene Productions http://www.upscene.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
if statement help
I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 09:49, Christopher Vaughan wrote: I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: if statement help
On Wednesday, March 30, 2005 10:24, Christopher Vaughan wrote: Tom Crimmins on Wednesday, March 30, 2005 at 11:10 AM -0500 wrote: If you convert it to a time field you can use mysql built-in functions to do what you want. You are limited to the range -838:59:59 to 838:59:59 though. http://dev.mysql.com/doc/mysql/en/time.html http://dev.mysql.com/doc/mysql/en/date-and-time-functions.html I'm not sure that this is going to work. Since the length of the data ranges from 8-9 characters in length and I also need to sum all the times on hour, minute and second. I looked at the addtime function but for the version of MySQL we have installed it does not work and upgrading it would be an unwanted hassle. -Chris Vaughan www.clusters.umaine.edu Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
I think I remember a match_at(:) or pat_index(:) UDF which would return the position of the first : for you, but I can't find it if it does exist. On Wed, 30 Mar 2005, Christopher Vaughan wrote: I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. Further info MySQL 3.23.58 running on RedHat 9 -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL inserts and disk full - how to handle gracefully?
Hi All, When you do a insert into a MySQL database and the disk is full, the insert just hangs waiting for that table to become available. This is fine for applications that care about data integrity. In this case I care more about availability and speed and would prefer it if the inserts gracefully returned a nice error instead of waiting forever. However - if the system is running a bit slow and the inserts are queuing and taking a few seconds I wouldn't want them to fail in that case. Does anyone have any experience in this kind of circumventing the data-integrity protecting hang-on disk-full condition? I would love to hear your thoughts and ideas.. Cheers for the help, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: if statement help
Christopher Vaughan [EMAIL PROTECTED] wrote on 30/03/2005 16:48:47: I have data in a table listed as 44:22:22 333:33:33 It stands for hhh:mm:ss I want to break each part of the data into different parts based on the ':' to separate them. Then I want to take that data and sum it. I wrote an if statement to parse through this table but I can't get it to work. I am not sure If my syntax is wrong because I can't find anything to check against it. Here is the syntax: IF (SELECT job_walltime FROM time WHERE CHAR_LENGTH( job_walltime ) =9) THEN (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 4, 2 )) , sum( right( job_walltime, '2' ) ) seconds FROM `time`) ELSE (SELECT sum( left( job_walltime, '3' ) ) hours, sum(mid( `job_walltime` , 3, 2 ) ), sum( right( job_walltime, '2' ) ) seconds FROM `time`) END I know this isn't the only way to do this but this but this is the first suggestion that comes to mind. Any input would be great. IF is an operator, not a command, so it comes after the SELECT. In C terms, it is more like the ?: operator than an if()...else. Thus you can do SELECT x, IF (x y, IS BIGGER THAN, IS SMALLER THAN), y FROM table. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: if statement help
Just forwarding this to the list. On Wednesday, March 30, 2005 10:43, Christopher Vaughan wrote: Tom Crimmins on Wednesday, March 30, 2005 at 11:31 AM -0500 wrote: Look at the functions HOUR(time), MINUTE(time), SECOND(time). These will give you interger output for each part of the time field. Also TIME_TO_SEC(time) may be useful for you. I believe that all of these are supported in 3.23. If your times aren't greater than 838:59:59 this should work for you. Thanks for the help: SELECT sum( HOUR ( job_walltime ) ) Hours, sum( MINUTE ( job_walltime ) ) Minutes, sum( SECOND ( job_walltime ) ) Seconds FROM `Jobs` This cold medicine that I'm on has slowed me down a bit. -Chris Vaughan www.clusters.umaine.edu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ERROR WHILE LOADING
Hello. See: http://dev.mysql.com/doc/mysql/en/windows-troubleshooting.html Search in archives at http://lists.mysql.com/mysql for more advices about solving this problem. For example see: http://lists.mysql.com/mysql/178152 prathima rao [EMAIL PROTECTED] wrote: [-- multipart/alternative, encoding 7bit, 0 lines --] [-- text/plain, encoding quoted-printable, charset: iso-8859-1, 18 lines --] hai, i was trying to install mysql 4 server on windows 2000 when configuring the server i received the below error in mysql server instance configuration could not start the service mysql error:0 can i know how to solve the error regards p rao [-- text/html, encoding quoted-printable, charset: iso-8859-1, 19 lines --] [-- text/plain, encoding quoted-printable, charset: us-ascii, 5 lines --] [-- Description: AVG certification --] No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.0.296 / Virus Database: 266.8.4 - Release Date: 3/27/2005 [-- text/plain, encoding 7bit, charset: us-ascii, 4 lines --] -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Index on boolean column
Duan Pavlica writes: maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Most of the time I'd say such an index would not be real useful. If the distribution of this column's values is equally distributed between these 2 values, then you will be accessing the rows via an index for half the values. Index accesses in such a manner are usually more expensive in a table scan. The rule of thumb that I've used and seen is about 20% of the table. So if you have evenly distributed values within the column, you'd want a cardinality of at least 5 for this guideline to be fulfilled. Especially when it comes to large queries, I've seen an index based group by take 4 times as long as a full table scan query (31 hours vs 7 hours). Most likely due to the seeks that slow down reading the table from the disk. Brad Eacker ([EMAIL PROTECTED]) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The best way to transfer data to another server
My response intermixed... Denis Gerasimov [EMAIL PROTECTED] wrote on 03/30/2005 09:29:00 AM: Hello list, I have two MySQL 4.1 servers, one local and one remote. I need to transfer database from one server to another. What actually is the best way of handling this task? Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) Have you looked at mysqldump and mysqlhotcopy? It's not clear from your question whether you want to transfer the data once only or if you want to transfer it repeatedly, perhaps daily or weekly. If you mean the latter, you may want to look into MySQL Replication, which enables all the changes made to the data on one server to be automatically made to the other server as well. Well, I need this only time to time, just for transferring data when I need. I assume that that is a bad idea to copy db files from one server to another. mysqldump is not an exciting option too... But there is a simple solution when using MySQL Administrator: make backup of desired data to SQL file, then connect to another server and restore it! That is exactly what mysqldump does for you. I haven't used MySQL Replication but I know it exists and is documented in the MySQL manual. Rhino Replication would be a possible solution if you needed to keep the two databases in synch. Since this is only a periodic update, replication is probably overkill for this issue. I seriously think you should check out mysqldump. For one-off or infrequent transfers, I think it works just fine. Especially since you already said that you think that writing the schema and data to a SQL file was a preferable idea Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug'
Hi, I'm getting this strange error when there are more than 1100 mysql connections connected to the same server. [EMAIL PROTECTED] mysql]# bin/mysql bin/mysql: connect to server at 'localhost' failed error: 'Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent bug' I've had this running fine in the past with MySQL 4.0.17 and Red Hat 7.3 (linux 2.4..) but with the same hardware and MySQL versions using Fedora core 2 (linux 2.6) I am getting these problems. I have checked max_connections and others in my.cnf and all is good. I'm running 'out of the box' linux and 'out of the box' MySQL binaries. Has anyone had this before? I would love to hear your thoughts and ideas.. Cheers for the help, Andrew SQL, Query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Asad Habib [EMAIL PROTECTED] wrote on 03/30/2005 10:53:38 AM: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad I hope you don't expect your query to actually work. The SELECT clause is the only place where you can use the * wildcard to mean all columns. If you wanted to frame a sample query but leave out information, I have been using an ellipsis (three dots together) to indicate the missing piece(s) like this: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... However, I generally do not leave out EVERYTHING in a query, like the one above. I usually only leave out the parts that aren't important to the information I am trying to convey. If I want to indicate that there was something specific the user needs to replace, I put that inside of angle brackets like this SELECT a list of columns from your table , a list of aggregate functions on columns from your table FROM a table name GROUP BY all of the columns in your SELECT clause that are not part of an aggregate function These are just my conventions. Use them only if you like them. I am not nor will I ever become the style police for this list. I just thought you could use a little help in creating better sample queries :-) You asked if you can use GROUP BY and ORDER BY in the same query. Absolutely!! I do it frequently. You also asked if GROUP BY works on different column types. Absolutely!! You can group on any type of field or combination of datatypes supported by MySQL (with the exception of TEXT and BLOB fields as they usually contain more data than is practical to use to form aggregates. I would recommend that you do not use a BLOB or TEXT field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you should manually specify what portion of the field to use) May I suggest some reading? http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html http://dev.mysql.com/doc/mysql/en/blob.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: power loss scenario
On Wed, 30 Mar 2005 08:49:13 -0500, Brent Baisley [EMAIL PROTECTED] wrote: Wow, you are asking a lot, especially since an inexpensive UPS could be had for less than $50. You don't need one to keep the system up for a long time, just long enough for writes to finish. A few minutes should be plenty. I know it's weird. It's not about technical issues or money. It's just one of those different situations. Whatever file system you use, I would most definitely use journaling. First and foremost you need the system in a good state, then the DB. There are two disks in the system, one with the OS, the other with the database. The OS drive is, i believe, almost fail-proof: the write cache is turned off, plus it's Ext3 with data=journal. Those settings bring a major performance hit, but that's ok on the OS drive, which is sparsely used. But i cannot force the same settings on the DB drive without risking the performance to drop through the floor. Well, maybe data=journal (i have to experiment). Journaling in the file system will also help in keeping the database intact. Raw partitions would buy you so little performance gain, it's really not worth the hassle. Wouldn't raw partitions fail less often if the power is yanked, just because there are fewer components to fail? I mean, if the database is on top of a FS, it's the database and the FS that can fail. On a raw partition, it's just the database. Or am i missing something? -- Florin Andrei http://florin.myip.org/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need Help with 813-MDB File
David Please also have a look at MYdbPAL for MySQL. Freeware again - this will sort out any schema conversions you might need - auto corrections. Plus you can model the schema. MDB conversion is shown in the tutorials. Tim Hayes -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: 30 March 2005 06:23 To: mysql@lists.mysql.com Subject: RE: Need Help with 813-MDB File Thanks for both your tips. I discovered by chance that Navicat (which I have) will do the conversion - very easily. Whether or not it will be a success is hard to say; it's loaded nearly 3 million rows so far, with over 8,000 errors recorded. But I'm going to download DB Tools, as I have frequent need for data conversion tools. Thanks. --- J.R. Bullington [EMAIL PROTECTED] wrote: DB Tools software will convert the file for you. You can download it at http://dbtools.com.br/EN/index.php. All you have to do is download and install the FreeWare version and then use the TOOLS DAO Import Wizard. J.R. -Original Message- From: David Blomstrom [mailto:[EMAIL PROTECTED] Sent: Tuesday, March 29, 2005 11:29 PM To: mysql@lists.mysql.com Subject: Need Help with 813-MDB File I acquired a CD-ROM that lists many thousands of animal species. The main file is a 813-MB MDB file. I'm not sure if it's a spreadsheet or database, but it's apparently designed to work with Microsoft Access, which I THINK is a spreadsheet. (I don't have it.) I have just enough memory to open the file in WordPad, but it doesn't do any good because much of the data consists of unintelligible characters. Anyway, I need to figure out a way to import this monster into MySQL. Are you aware of any freeware programs that can open up files designed for Access? Is there a way to convert a MDB file directly into a csv file, which could then be imported into MySQL? I'll probably eventually break it into sections. At the very least, I'll probably divide it between vertebrates (which I'll use the most) and invertebrates. Thanks. __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- 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: Regarding NULL and '' (null string) treatment in MYSQL
Peter Brawley wrote: Perhaps Oracle also has such a setting too. MySQL doesn't. As a matter of fact, Oracle goes the other way in that if you store '' into a VARCHAR field, it actually stores a NULL there. But it's inconsistent in that doesn't consider a NULL varchar column to be = '' (a literal '', I mean; or even a PL/SQL variable assigned a '' value). -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Changed Logging Table from InnoDB to MyIsam, what memory buffers to tune?
We finally split our huge logging aspect of an application to its own system, and we changed the table type from InnoDB to MyIsam. This is a logging table, 1 log writer, many, many selects from customer care reps looking up the last 500 or so records. These are logs of timed events, so they always need to see the most recent 500 records. The memory buffers that are set for now are as follows: ### OPERATIONAL SETTINGS key_buffer_size=416M myisam_sort_buffer_size=128M join_buffer_size=128M read_buffer_size=16M sort_buffer=256M read_rnd_buffer_size=32M query_cache_size=32M The box has 4 GB, it is Linux, so I am thinking I can use up to 3 GB. I did not size these initial settings but the more I see on what others use, I learn of all kinds of buffers I never thought of using before. I understand that some of these buffers will degrade performance if too large, while others can grow to the sky, and only improve performance. I would appreciate some additional input on these settings, and advice how I could optimize these even further to obtain as much through-put as possible from the system. Are there any other buffers I could set? The majority of all action are inserts from the log writer, and selects from customer care reps. One other thing, what can be optimized if the log writer is split into multiple threads, and many insert processes are happening at once? Regards, Thomas A. Lekai Vonage Holdings [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
JDBC driver problem with sql LIMIT
Using MySql Connect/J (mysql-connector-java-3.1.7.tar) on Linux Fedora 2. I use a Statement object and do the following query: String myquery = SELECT * FROM term WHERE name REGEXP (\'+ name +\') and term_type=\'+ ontology +\' LIMIT 20; table term has 6 columns. MySql connect/J use 700mB to retrieve hundreds of rows when it should retrieve only 20. Actually it crashes with: Exception in thread AWT-EventQueue-0 java.lang.OutOfMemoryError: Java heap space Exception on Toolkit thread: java.lang.OutOfMemoryError: Java heap space java.lang.OutOfMemoryError: Java heap space Is LIMIT supported with the JDBC driver? thank you everygody Laurie -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: JDBC driver problem with sql LIMIT
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 l'eau wrote: Using MySql Connect/J (mysql-connector-java-3.1.7.tar) on Linux Fedora 2. I use a Statement object and do the following query: String myquery = SELECT * FROM term WHERE name REGEXP (\'+ name +\') and term_type=\'+ ontology +\' LIMIT 20; table term has 6 columns. MySql connect/J use 700mB to retrieve hundreds of rows when it should retrieve only 20. Actually it crashes with: Exception in thread AWT-EventQueue-0 java.lang.OutOfMemoryError: Java heap space Exception on Toolkit thread: java.lang.OutOfMemoryError: Java heap space java.lang.OutOfMemoryError: Java heap space Is LIMIT supported with the JDBC driver? thank you everygody Laurie Laurie, Yes, the driver supports the 'LIMIT' keyword, because it relies on the server to do it (the driver does not rewrite queries or really do any processing on queries, other than sending them to the server, and preparing the results to be treated like java.sql.ResultSets). If the driver is reading 100's of megabytes of rows, it's because the server is sending them. What happens if you run the _exact_ same query in the MySQL commandline client? -Mark - -- Mark Matthews MySQL AB, Software Development Manager - Connectivity www.mysql.com MySQL User Conference (Santa Clara CA, 18-21 April 2005) -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.6 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFCSvtEtvXNTca6JD8RAqEtAKDHWTmillMzO7fPoHzxI8HH9tCSbgCgnV5D A0lyMaCMhzs+zhx+tbL2EBs= =PVeS -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General Table Locking Question
I've got a good deal of experience using mysql, but never in a large production environment with many concurrent users. Using the InnoDB engine, what is the general practice for ensuring data integrity when multiple users are writing to the same table? Should I explicitly lock the table before I write to it, or does mysql do this automatically? Thanks, sk -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
innodb hot backup (ibbackup) error
Hi all, I'm getting the following error when attempting to run a hot backup of a 4.0.18 MySQL database using ibbackup: ibbackup: We wait 10 seconds before starting copying the data files... 050330 14:26:33 ibbackup: Copying /mysqldata/mysqld1/ibdata1 ibbackup: Error: log scan was only able to reach to 0 561225562, ibbackup: but a copied database page was modified at 0 575415967 Any idea what this means? Thanks for any help.
PACK_KEYS not packing keys?
Hi,
I tried searching for this problem, but I couldn't find any reference
to it, so here goes...
using mysql 4.0.23, I created two tables, one of them whose key is
packed. My goal is to see the effect on the size of the index file.
mysql show create table Unpacked;
+--+-+
| Table| Create Table
|
+--+-+
| Unpacked | CREATE TABLE `Unpacked` (
`key_` int(10) unsigned NOT NULL auto_increment,
`value` int(10) unsigned default NULL,
PRIMARY KEY (`key_`)
) TYPE=MyISAM |
+--+-+
1 row in set (0.00 sec)
mysql show create table Packed;
++---+
| Table | Create Table
|
++---+
| Packed | CREATE TABLE `Packed` (
`key_` int(10) unsigned NOT NULL auto_increment,
`value` int(10) unsigned default NULL,
PRIMARY KEY (`key_`)
) TYPE=MyISAM PACK_KEYS=1 |
++---+
1 row in set (0.00 sec)
mysql show index from Packed;
+++--+--+-+---+-+--++--++-+
| Table | Non_unique | Key_name | Seq_in_index | Column_name |
Collation | Cardinality | Sub_part | Packed | Null | Index_type |
Comment |
+++--+--+-+---+-+--++--++-+
| Packed | 0 | PRIMARY |1 | key_| A
| 10 | NULL | NULL | | BTREE | |
+++--+--+-+---+-+--++--++-+
1 row in set (0.00 sec)
Here's the core of a perl script that inserted 100K rows:
for (my $i = 0; $i 10; $i++) { $dbh-do(INSERT INTO Packed
(value) VALUES ( . int(rand(100)) . )) or die bah .
$dbh-err; }
Here's the size of the files after all's said and done:
-rw-rw 1 mysql users 90 Mar 30 11:18 Packed.MYD
-rw-rw 1 mysql users 821248 Mar 30 11:18 Packed.MYI
-rw-rw 1 mysql users8580 Mar 30 11:17 Packed.frm
-rw-rw 1 mysql users 90 Mar 30 11:17 Unpacked.MYD
-rw-rw 1 mysql users 821248 Mar 30 11:18 Unpacked.MYI
-rw-rw 1 mysql users8580 Mar 30 11:17 Unpacked.frm
If I understand correctly, PACK_KEYS should reduce the size of the
index file. Does the fact that SHOW INDEX on Packed shows packed=NULL
indicate something's wrong? Does there need to be more variation in
the values of the key? (I would think that pack_keys helps most when
keys are very similar)
Thanks,
Julian
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Sorry for the confusion. In this case I am using the * to denote a field name instead of the wild card character. - Asad On Wed, 30 Mar 2005 [EMAIL PROTECTED] wrote: Asad Habib [EMAIL PROTECTED] wrote on 03/30/2005 10:53:38 AM: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad I hope you don't expect your query to actually work. The SELECT clause is the only place where you can use the * wildcard to mean all columns. If you wanted to frame a sample query but leave out information, I have been using an ellipsis (three dots together) to indicate the missing piece(s) like this: SELECT ... FROM ... WHERE ... GROUP BY ... ORDER BY ... However, I generally do not leave out EVERYTHING in a query, like the one above. I usually only leave out the parts that aren't important to the information I am trying to convey. If I want to indicate that there was something specific the user needs to replace, I put that inside of angle brackets like this SELECT a list of columns from your table , a list of aggregate functions on columns from your table FROM a table name GROUP BY all of the columns in your SELECT clause that are not part of an aggregate function These are just my conventions. Use them only if you like them. I am not nor will I ever become the style police for this list. I just thought you could use a little help in creating better sample queries :-) You asked if you can use GROUP BY and ORDER BY in the same query. Absolutely!! I do it frequently. You also asked if GROUP BY works on different column types. Absolutely!! You can group on any type of field or combination of datatypes supported by MySQL (with the exception of TEXT and BLOB fields as they usually contain more data than is practical to use to form aggregates. I would recommend that you do not use a BLOB or TEXT field in a GROUP BY unless it is unavoidable. IF you MUST do it, then you should manually specify what portion of the field to use) May I suggest some reading? http://dev.mysql.com/doc/mysql/en/select.html http://dev.mysql.com/doc/mysql/en/group-by-functions-and-modifiers.html http://dev.mysql.com/doc/mysql/en/blob.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: power loss scenario
If the power is yanked a journaled file system knows exactly what it was doing at the time of failure, what didn't finish, and can recover from any errors caused by the failure. A non-journaled file system would need to run a check to see if everything is ok. This could take a long time on a big drive. How could you even tell if something was wrong on a raw partition? There isn't a whole lot of metadata to check for problems against like there is in a filesystem. It's up to the application to recover from errors. Raw partitions used to be used for performance, not for safety. Hardware has gotten so fast, that there really is no difference in performance between a file system and a raw partition. Hardware fails, software has bugs. On Mar 30, 2005, at 1:09 PM, Florin Andrei wrote: Wouldn't raw partitions fail less often if the power is yanked, just because there are fewer components to fail? I mean, if the database is on top of a FS, it's the database and the FS that can fail. On a raw partition, it's just the database. Or am i missing something? -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: PACK_KEYS not packing keys?
In the last episode (Mar 30), Julian Pellico said:
I tried searching for this problem, but I couldn't find any reference
to it, so here goes...
using mysql 4.0.23, I created two tables, one of them whose key is
packed. My goal is to see the effect on the size of the index file.
for (my $i = 0; $i 10; $i++) { $dbh-do(INSERT INTO Packed
(value) VALUES ( . int(rand(100)) . )) or die bah .
$dbh-err; }
Here's the size of the files after all's said and done:
-rw-rw 1 mysql users 90 Mar 30 11:18 Packed.MYD
-rw-rw 1 mysql users 821248 Mar 30 11:18 Packed.MYI
-rw-rw 1 mysql users8580 Mar 30 11:17 Packed.frm
-rw-rw 1 mysql users 90 Mar 30 11:17 Unpacked.MYD
-rw-rw 1 mysql users 821248 Mar 30 11:18 Unpacked.MYI
-rw-rw 1 mysql users8580 Mar 30 11:17 Unpacked.frm
Primary keys won't pack well, since there's little redundancy to pack
out. From the manual (under CREATE TABLE Syntax):
When packing binary number keys, MySQL uses prefix compression:
* Every key needs one extra byte to indicate how many bytes of the
previous key are the same for the next key.
* The pointer to the row is stored in high-byte-first order
directly after the key, to improve compression.
This means that if you have many equal keys on two consecutive rows,
all following ``same'' keys usually only take two bytes (including
the pointer to the row). Compare this to the ordinary case where the
following keys takes storage_size_for_key + pointer_size (where the
pointer size is usually 4). Conversely, you get a big benefit from
prefix compression only if you have many numbers that are the same.
If all keys are totally different, you use one byte more per key, if
the key isn't a key that can have NULL values. (In this case, the
packed key length is stored in the same byte that is used to mark if
a key is NULL.)
Now from this description, I would have expected a 2-byte savings per
record (since the three most-significant bytes of each key should get
compressed out, but you lose one byte to store the same-bytes value,
for a total savings of 2). Try running myisamchk -eis on each table
and see what the usage and packing percentages are for each index. It
may be that your 'packed' table is using the same number of keyblocks,
just less-densely filled.
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Stepping back in time: copying data from 4.0.20 to 4.0.17, ok?
Hi all, I need to move a database from a 4.0.20 MySQL server to a 4.0.17 server. I expect it will work fine when simply scp-ing the whole /var/lib/mysql/MY-DATABASE to the other server's /var/lib/mysql directory, with only a minor version difference. Can anyone confirm this is going to work and will 4.0.17 be able to read the 4.0.20 generated data files without problems? I'd love to upgrade the 4.0.17 server but it's running another database that really can't go offline. Thanks in advance, Bas. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to find out which rows gets deleted by the mysqlimport
Hi, Anyone know how to find out what are the rows that are reported by mysqlimport as deleted? Thanks HT -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GROUP BY, ORDER BY clauses
Just keep in mind that the ORDER BY will require MySQL to take the full resultset and reorder it in a temporary table. MySQL has extended the GROUP BY clause as of version 3.23.34 so that you can also specify ASC and DESC after columns named in the clause. On Wed, 30 Mar 2005 10:53:38 -0500 (EST), Asad Habib [EMAIL PROTECTED] wrote: Does MySQL 4.1 support the use of GROUP BY and ORDER BY used in conjunction with one another? I have tried to execute several queries with both these clauses but the result set I get is different from what I expect. My queries read as follows: SELECT *, *, * FROM * WHERE * GROUP BY * ORDER BY * Also, does GROUP BY only work on fields that are strings (i.e. CHAR, VARCHAR, TEXT, etc.). Thanks in advance. - Asad -- 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: PACK_KEYS not packing keys?
Thanks Dan, I ran myisamchk as you suggested. The detailed results are below, but in summary there's no difference between the two indices. Perhaps mysql doesn't want to pack the key for some reason? I'm running FreeBSD 4.1. Checking MyISAM file: /home/y/var/mysql/data/Test/Unpacked.MYI Data records: 10 Deleted blocks: 0 myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed:0% Max levels: 3 Total:Keyblocks used: 98% Packed:0% - check records and index references Records:10M.recordlength:9 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 10Delete blocks: 0 Record data:90Deleted data: 0 Lost space: 0Linkdata: 0 MyISAM-table '/home/y/var/mysql/data/Test/Unpacked.MYI' is usable but should be fixed User time 0.25, System time 0.00 Maximum resident set size 3044, Integral resident set size 580664 Non-physical pagefaults 660, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 9 out 0, Signals 0 Voluntary context switches 8, Involuntary context switches 135 Checking MyISAM file: /home/y/var/mysql/data/Test/Packed.MYI Data records: 10 Deleted blocks: 0 myisamchk: warning: 1 client is using or hasn't closed the table properly - check file-size - check record delete-chain - check key delete-chain - check index reference - check data record references index: 1 Key: 1: Keyblocks used: 98% Packed:0% Max levels: 3 Total:Keyblocks used: 98% Packed:0% - check records and index references Records:10M.recordlength:9 Packed: 0% Recordspace used: 100% Empty space: 0% Blocks/Record: 1.00 Record blocks: 10Delete blocks: 0 Record data:90Deleted data: 0 Lost space: 0Linkdata: 0 MyISAM-table '/home/y/var/mysql/data/Test/Packed.MYI' is usable but should be fixed User time 0.24, System time 0.01 Maximum resident set size 3052, Integral resident set size 08 Non-physical pagefaults 660, Physical pagefaults 0, Swaps 0 Blocks in 0 out 0, Messages in 9 out 0, Signals 0 Voluntary context switches 8, Involuntary context switches 141 If anyone has any ideas about thisplease let me know. - Julian Now from this description, I would have expected a 2-byte savings per record (since the three most-significant bytes of each key should get compressed out, but you lose one byte to store the same-bytes value, for a total savings of 2). Try running myisamchk -eis on each table and see what the usage and packing percentages are for each index. It may be that your 'packed' table is using the same number of keyblocks, just less-densely filled. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
lock tables and sql cache
If I try to read table 'b' after locking table 'a', I expect to get
the error message Table 'b' was not locked with LOCK TABLES.
However, if my query that accesses table b is stored in the query
cache, I don't get the error. This causes a problem in the following
scenario:
User 1:
LOCK TABLES a
SELECT SQL_CACHE COUNT(*) FROM b
(assume it was already cached)
User 2:
INSERT b VALUES('value');
SELECT SQL_CACHE COUNT(*) FROM b
(the SELECT puts the query back into the cache)
User 1:
SELECT SQL_CACHE COUNT(*) FROM b
(now he gets a different result)
UNLOCK TABLES
User 1 thinks that everything he's doing is safe inside of an
emulated transaction. But the data in table b has changed between
the LOCK and the UNLOCK, and User 1 isn't notified that he is doing
anything wrong.
I think an appropriate fix would be to force User 1 to lock table b
even though the results of that query are stored in the query cache.
Is this possible?
Thanks,
-Bob
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqlconnecernet not closing connections w/2003
I posted this late last Friday. I'm hoping everyone missed it and that someone has an answer. :-) We have a quote server on both a 2003 server and an XP machine. The quote server is an asp.net server. The XP machine is closing the connections without a problem. For some reason the mysqlconnecernet is not closing the connections when used on the 2003 server. It connection count climbs to 100 our maximum connection setting, and then we get a Time out when attempting to get a connection from the pool Has anyone run into this problem? Any help you can give use will be greatly appreciated. TIA Dan
What's up with this syntax?
update _cached_LinesNotTolling LNT inner join TelecomLinePosting TLP on LNT.Line=TLP.Line inner join TelecomAccountPosting TAP on TLP.TelecomLinePostingID=TAP.DanPK inner join PhoneTypes on TLP.LineType=PhoneTypes.ID set AnnualService=sum(TLP.Service)/1*12, LNT.PhoneType=SitRepDesc, MaxOfInvDate=InvDate where TAP.DanPK=41675 group by TLP.Line It's giving me: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by TLP.Line' Looks right to me... -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Character set information not found
Description: I installed MySQL-server-4.0.15-0 and am getting the following error message: 050330 12:09:32 [ERROR] Character set information not found in '/usr/share/mysql/english/errmsg.sys'. Please install the latest version of this file. 050330 12:09:32 [ERROR] Aborting How-To-Repeat: Any time I try to start mysql Fix: Submitter-Id: submitter ID Originator:root Organization: MySQL support: [none] Synopsis: english character set not found on startup Severity: Priority: Category: mysql Class: Release: mysql-4.0.15-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux server.kenward.org 2.4.20-31.9 #1 Tue Apr 13 18:04:23 EDT 2004 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.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='g++' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Apr 4 2004 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1561228 Nov 12 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2332200 Nov 12 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Nov 12 2003 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--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' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce-felide-constructors -fno-exceptions -fno-rtti ' 'CXX=' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What's up with this syntax?
On Wednesday, March 30, 2005 22:25, Daniel Kasak wrote: update _cached_LinesNotTolling LNT inner join TelecomLinePosting TLP on LNT.Line=TLP.Line inner join TelecomAccountPosting TAP on TLP.TelecomLinePostingID=TAP.DanPK inner join PhoneTypes on TLP.LineType=PhoneTypes.ID set AnnualService=sum(TLP.Service)/1*12, LNT.PhoneType=SitRepDesc, MaxOfInvDate=InvDate where TAP.DanPK=41675 group by TLP.Line It's giving me: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by TLP.Line' Looks right to me... Remove the group by. A group by is used to group rows returned by a select statement. -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: license question
As my company and I understand it, if you intend on distributing mySQL on this appliance and the appliance is a sealed box with your own proprietary code (like PHP or C or Java or whatever) that interfaces to the STOCK/Untouched RDBMS, you NEED a mySQL Commercial License. This license is a ridiculous $600 per unit which makes it completely unrealistic for any large scale deployment!!! I mean, I don't mind paying someone for their work, but I was thinking more like $50 per unit, not 10 times that. If someone from mySQL can clarify that would be great, but this is how I read the license and that's why we've stuck to v4.0.18 which was GPL. http://www.mysql.com/company/legal/licensing/opensource-license.html Our software is 100% GPL (General Public License); if yours is 100% GPL compliant, then you have no obligation to pay us for the licenses. Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not. If you are a private individual you are free to use MySQL software for your personal applications as long as you do not distribute them. If you distribute them, you must make a decision between the Commercial License and the GPL. http://www.mysql.com/company/legal/licensing/commercial-license.html Building a hardware system that includes MySQL and selling that hardware system to customers for installation at their own locations. If you include the MySQL server with an application that is not licensed under the GPL or GPL-compatible license, you need a commercial license for the MySQL server. -Original Message- From: Pat Ballard [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 4:03 PM To: mysql@lists.mysql.com Subject: license question Suppose i distribute MySQL-4.1 with an appliance, which is a sealed x86 machine running a Linux distribution made by another entity (ok, it's Red Hat). I don't write any code that's directly linked to MySQL, I'm only using the existing php-mysql, etc., packages already provided by the distribution, plus some third-party apps that are under GPL and link to MySQL (applications that access MySQL, not written by me, but are Open Source GPL projects off SourceForge and other places - i just bundle them with the appliance). Any code that I write personally is PHP and sits on top of the php-mysql module provided by Red Hat. The end-user has no direct visibility to the database, in fact, the end-user might never know it's MySQL - all that is visible is the PHP interface, via Apache. In this case, what's the license? Is MySQL still free (under GPL)? -- Pat Ballard -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql not starting at boot
Hi All,
below is my /etc/init.d/mysql content, but, mysql is not starting at
boot on Fedora3. Have I missed something fundamental.? I need mysql to
start at boot for snort to connect to it. Currently, snort gives an
error stating it can't connect. Cheers.
Mark Sargent.
[EMAIL PROTECTED] ~]# cat /etc/init.d/mysql
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB Monty Program KB Detron HB
# This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 20
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /usr/local/mysql, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=path-to-mysql-installation-directory
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir variable
# below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
basedir=
# The following variables are only set for letting mysql.server find things.
# Set some defaults
datadir=/usr/local/mysql/data
pid_file=
if test -z $basedir
then
basedir=/usr/local/mysql
bindir=./bin
else
bindir=$basedir/bin
fi
PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
export PATH
mode=$1# start or stop
case `echo testing\c`,`echo -n testing` in
*c*,-n*) echo_n= echo_c= ;;
*c*,*) echo_n=-n echo_c= ;;
*) echo_n= echo_c='\c' ;;
esac
parse_arguments() {
for arg do
case $arg in
--basedir=*) basedir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--datadir=*) datadir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--pid-file=*) pid_file=`echo $arg | sed -e 's/^[^=]*=//'` ;;
esac
done
}
# Get arguments from the my.cnf file,
# groups [mysqld] [mysql_server] and [mysql.server]
if test -x ./bin/my_print_defaults
then
print_defaults=./bin/my_print_defaults
elif test -x $bindir/my_print_defaults
then
print_defaults=$bindir/my_print_defaults
elif test -x $bindir/mysql_print_defaults
then
print_defaults=$bindir/mysql_print_defaults
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e /$subpat/!d -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x $d/bin/my_print_defaults
then
print_defaults=$d/bin/my_print_defaults
break
fi
if test -x $d/bin/mysql_print_defaults
then
print_defaults=$d/bin/mysql_print_defaults
break
fi
done
fi
# Hope it's in the PATH ... but I doubt it
test -z $print_defaults print_defaults=my_print_defaults
fi
#
# Test if someone changed datadir; In this case we should also read the
# default arguments from this directory
#
extra_args=
if test $datadir != /usr/local/mysql/data
then
extra_args=-e $datadir/my.cnf
fi
parse_arguments `$print_defaults $extra_args mysqld server mysql_server
mysql.se rver`
#
# Set pid file if not given
#
if test -z $pid_file
then
pid_file=$datadir/`/bin/hostname`.pid
else
case $pid_file in
/* ) ;;
* ) pid_file=$datadir/$pid_file ;;
esac
fi
# Safeguard (relative paths, core dumps..)
cd $basedir
case $mode in
'start')
# Start daemon
if test -x $bindir/mysqld_safe
then
# Give extra arguments to mysqld with the my.cnf file. This script may
# be overwritten at next upgrade.
$bindir/mysqld_safe --datadir=$datadir --pid-file=$pid_file
/dev/null 2 1
# Make lock for RedHat / SuSE
if test -w /var/lock/subsys
then
touch /var/lock/subsys/mysql
fi
else
echo Can't execute $bindir/mysqld_safe from dir $basedir
fi
;;
'stop')
# Stop daemon. We use a signal here to avoid having to know the
# root password.
if test -s $pid_file
then
mysqld_pid=`cat $pid_file`
echo Killing mysqld with pid $mysqld_pid
kill $mysqld_pid
# mysqld should remove the pid_file when it exits, so wait for it.
sleep 1
RE: license question
MySql loses money from many vendors on this very point. Of which they do not budge. We have a Point of Sale software company who can distribute Oracle cheaper. They only require a percentage of the final product price that their product is packaged with. When the company explained they would rather use MySql an pay them the same rates MySql refused. Thanks Donny Lairson President 29 GunMuse Lane P.O. box 166 Lakewood NM 88254 http://www.gunmuse.com 469 228 2183 -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 10:18 PM To: 'Pat Ballard'; mysql@lists.mysql.com Subject: RE: license question As my company and I understand it, if you intend on distributing mySQL on this appliance and the appliance is a sealed box with your own proprietary code (like PHP or C or Java or whatever) that interfaces to the STOCK/Untouched RDBMS, you NEED a mySQL Commercial License. This license is a ridiculous $600 per unit which makes it completely unrealistic for any large scale deployment!!! I mean, I don't mind paying someone for their work, but I was thinking more like $50 per unit, not 10 times that. If someone from mySQL can clarify that would be great, but this is how I read the license and that's why we've stuck to v4.0.18 which was GPL. http://www.mysql.com/company/legal/licensing/opensource-license.html Our software is 100% GPL (General Public License); if yours is 100% GPL compliant, then you have no obligation to pay us for the licenses. Free use for those who never copy, modify or distribute. As long as you never distribute the MySQL Software in any way, you are free to use it for powering your application, irrespective of whether your application is under GPL license or not. If you are a private individual you are free to use MySQL software for your personal applications as long as you do not distribute them. If you distribute them, you must make a decision between the Commercial License and the GPL. http://www.mysql.com/company/legal/licensing/commercial-license.html Building a hardware system that includes MySQL and selling that hardware system to customers for installation at their own locations. If you include the MySQL server with an application that is not licensed under the GPL or GPL-compatible license, you need a commercial license for the MySQL server. -Original Message- From: Pat Ballard [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 4:03 PM To: mysql@lists.mysql.com Subject: license question Suppose i distribute MySQL-4.1 with an appliance, which is a sealed x86 machine running a Linux distribution made by another entity (ok, it's Red Hat). I don't write any code that's directly linked to MySQL, I'm only using the existing php-mysql, etc., packages already provided by the distribution, plus some third-party apps that are under GPL and link to MySQL (applications that access MySQL, not written by me, but are Open Source GPL projects off SourceForge and other places - i just bundle them with the appliance). Any code that I write personally is PHP and sits on top of the php-mysql module provided by Red Hat. The end-user has no direct visibility to the database, in fact, the end-user might never know it's MySQL - all that is visible is the PHP interface, via Apache. In this case, what's the license? Is MySQL still free (under GPL)? -- Pat Ballard -- 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 not starting at boot
On Wednesday, March 30, 2005 23:26, Mark Sargent wrote: Hi All, below is my /etc/init.d/mysql content, but, mysql is not starting at boot on Fedora3. Have I missed something fundamental.? I need mysql to start at boot for snort to connect to it. Currently, snort gives an error stating it can't connect. Cheers. Mark Sargent. I assume you are able to start it after boot using 'service mysql start'. Run: chkconfig --list mysql This should show a list of runlevels with on and off. If not run: chkconfig --add mysql -- Tom Crimmins Interface Specialist Pottawattamie County, Iowa -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: license question
Yeah. It's silly. The whole hardware x86 1U rack mount we use with 2.4Ghz proc, 256MB, 40GB HD, dual Gbps NICs is only $500. I don't know what crack the mySQL AB guys are smoking to think that they are competitive. We've already started to wrap our product SQL calls in our own API so we can migrate to Postgress (or something with an acceptable license). -Original Message- MySql loses money from many vendors on this very point. Of which they do not budge. We have a Point of Sale software company who can distribute Oracle cheaper. They only require a percentage of the final product price that their product is packaged with. When the company explained they would rather use MySql an pay them the same rates MySql refused. This license is a ridiculous $600 per unit which makes it completely unrealistic for any large scale deployment!!! I mean, I don't mind paying someone for their work, but I was thinking more like $50 per unit, not 10 times that. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: license question
Hi Folks, Take a deep breath and see how much an Oracle license is for a Solaris box with 4 cpus, AFAIK start looking in the thousands of dollars. Try SQL server, Sybase or Informix. None of the above mentioned are particularly cheap, some of these are costing over $595.00 per seat not per unit. IMHO $595.00 for an unlimited user configuration is not bad at all. Granted most users tend to be in the X86 world where hardware is cheap but lets look at the commercial reality of it all, $595.00 is not that bad considering the general backup and support along with the feature set that you receive. BTW it is only $295.00 if you don't want InnoDB. Regards David Logan Database Administrator HP Managed Services 148 Frome Street, Adelaide 5000 Australia +61 8 8408 4273 - Work +61 417 268 665 - Mobile +61 8 8408 4259 - Fax -Original Message- From: Daevid Vincent [mailto:[EMAIL PROTECTED] Sent: Thursday, 31 March 2005 3:11 PM To: [EMAIL PROTECTED]; 'Pat Ballard'; mysql@lists.mysql.com Subject: RE: license question Yeah. It's silly. The whole hardware x86 1U rack mount we use with 2.4Ghz proc, 256MB, 40GB HD, dual Gbps NICs is only $500. I don't know what crack the mySQL AB guys are smoking to think that they are competitive. We've already started to wrap our product SQL calls in our own API so we can migrate to Postgress (or something with an acceptable license). -Original Message- MySql loses money from many vendors on this very point. Of which they do not budge. We have a Point of Sale software company who can distribute Oracle cheaper. They only require a percentage of the final product price that their product is packaged with. When the company explained they would rather use MySql an pay them the same rates MySql refused. This license is a ridiculous $600 per unit which makes it completely unrealistic for any large scale deployment!!! I mean, I don't mind paying someone for their work, but I was thinking more like $50 per unit, not 10 times that. -- 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: What's up with this syntax?
Tom Crimmins wrote: On Wednesday, March 30, 2005 22:25, Daniel Kasak wrote: update _cached_LinesNotTolling LNT inner join TelecomLinePosting TLP on LNT.Line=TLP.Line inner join TelecomAccountPosting TAP on TLP.TelecomLinePostingID=TAP.DanPK inner join PhoneTypes on TLP.LineType=PhoneTypes.ID set AnnualService=sum(TLP.Service)/1*12, LNT.PhoneType=SitRepDesc, MaxOfInvDate=InvDate where TAP.DanPK=41675 group by TLP.Line It's giving me: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'group by TLP.Line' Looks right to me... Remove the group by. A group by is used to group rows returned by a select statement. Doh! I need a sum(). I take it I have to do it in 2 steps then. -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql not starting at boot
Tom Crimmins wrote: On Wednesday, March 30, 2005 23:26, Mark Sargent wrote: Hi All, below is my /etc/init.d/mysql content, but, mysql is not starting at boot on Fedora3. Have I missed something fundamental.? I need mysql to start at boot for snort to connect to it. Currently, snort gives an error stating it can't connect. Cheers. Mark Sargent. I assume you are able to start it after boot using 'service mysql start'. Run: chkconfig --list mysql This should show a list of runlevels with on and off. If not run: chkconfig --add mysql Hi All, thanx, that did it. Cheers. Mark Sargent. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: The best way to transfer data to another server
Are there any standard MySQL tools available for doing that (I mean MySQL Administrator/Query Browser etc.) Have you looked at mysqldump and mysqlhotcopy? I know about standard mysqldump utility but I actually needed something with GUI interface. I assume that that is a bad idea to copy db files from one server to another. mysqldump is not an exciting option too... But there is a simple solution when using MySQL Administrator: make backup of desired data to SQL file, then connect to another server and restore it! That is exactly what mysqldump does for you. Yes, but again it has no GUI. I haven't used MySQL Replication but I know it exists and is documented in the MySQL manual. Replication would be a possible solution if you needed to keep the two databases in synch. Since this is only a periodic update, replication is probably overkill for this issue. Agree. I seriously think you should check out mysqldump. For one-off or infrequent transfers, I think it works just fine. Especially since you already said that you think that writing the schema and data to a SQL file was a preferable idea Yes, it is also a good idea because there is phpMyAdmin installed on the remote server, so I can just run this SQL file with it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB problems ...
Hi, I'm working with JBoss and MySQL 4.0.22 (and 4.0.18 on Testsystem). But under the load, I get sometimes Exceptions like this: java.sql.SQLException: Deadlock found when trying to get lock; Try restarting transaction message from server: Lock wait timeout exceeded; Try restarting transaction It looks like InnoDB problem. Is there a way to find out why this happens? This is my mysql-ds.xml: ?xml version=1.0 encoding=UTF-8? datasources local-tx-datasource jndi-nameOmaDS/jndi-name connection-urljdbc:mysql://localhost/ofa/connection-url driver-classcom.mysql.jdbc.Driver/driver-class user-nameuser/user-name passwordxxx/password connection-property name=autoReconnecttrue/connection-property !-- transaction-isolationTRANSACTION_READ_COMMITTED/transaction-isolation -- !--pooling parameters-- min-pool-size25/min-pool-size max-pool-size100/max-pool-size blocking-timeout-millis5000/blocking-timeout-millis idle-timeout-minutes15/idle-timeout-minutes prepared-statement-cache-size1000/prepared-statement-cache-size /local-tx-datasource /datasources Our beans are configured to make SELECT ... FOR UPDATE calls. Best Regards, Rafal -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
upgrade from v. 4.0.15 to v.4.0.24 (InnoDB)
Hi! last night we try to upgrade from 4.0.15 to 4.0.24. We thought, that it is possible without dumping data. We upgrade only server (clients were still 4.0.15). In changelogs in versions higher than 4.0.15 there were no info about some incompatible changes and those bound with InnoDB were connected with engine. Unfortunately after restarting mysql daemon didn't see some tables, there were problems with recognition of innoDB format. I wonder if anybody has some experience with similar upgrades? Is it possible to upgrade only server? Or do we need also dump data and load again? Any success stories? Best regards Remigiusz -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql not starting at boot
To have mysql to start at boot you need to inform the inetd (daemon) that
mysql should be launched at one of the 6 levels:
Use chkconfig to have mysql start in one of the level (2,3,4,5,6)
5 being X windows.
There are two ways to check that mysql will be loaded:
- 1 - by checking the file in /etc/rc.d/rcX.d (X being a number: 1,2,3,4,5,6)
- 2 - by executing chkconfig
execute the following to see if mysql runs at any level
/sbin/chkconfig --list | grep mysql
you should see at which level mysql is running.
mysql 0:off 1:off 2:off 3:off 4:off 5:off 6:off
If it is all off then execute:
/sbin/chkconfig --level 2345 mysql on
(this will make sure that mysql runs at level 2, 3, 4 and 5)
On my machine (Fedora 2) it runs at 2,3,4 and 5.
Once you have it run at a level you can verify it by looking at rcX.d (X being
a level):
for example to check that it runs at level 4:
go to /etc/rc.d/rc4.d/
and look at a file named ...mysql
S20mysql
(S means it will be picked up by the inetd daemon)
K30mysql
(K means Kill and mysql wont start)
good luck
Laurie
On Wednesday 30 March 2005 09:26 pm, Mark Sargent wrote:
Hi All,
below is my /etc/init.d/mysql content, but, mysql is not starting at
boot on Fedora3. Have I missed something fundamental.? I need mysql to
start at boot for snort to connect to it. Currently, snort gives an
error stating it can't connect. Cheers.
Mark Sargent.
[EMAIL PROTECTED] ~]# cat /etc/init.d/mysql
#!/bin/sh
# Copyright Abandoned 1996 TCX DataKonsult AB Monty Program KB Detron
HB # This file is public domain and comes with NO WARRANTY of any kind
# MySQL daemon start/stop script.
# Usually this is put in /etc/init.d (at least on machines SYSV R4 based
# systems) and linked to /etc/rc3.d/S99mysql and /etc/rc0.d/K01mysql.
# When this is done the mysql server will be started when the machine is
# started and shut down when the systems goes down.
# Comments to support chkconfig on RedHat Linux
# chkconfig: 2345 90 20
# description: A very fast and reliable SQL database engine.
# Comments to support LSB init script conventions
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $local_fs $network $remote_fs
# Required-Stop: $local_fs $network $remote_fs
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: start and stop MySQL
# Description: MySQL is a very fast and reliable SQL database engine.
### END INIT INFO
# If you install MySQL on some other places than /usr/local/mysql, then you
# have to do one of the following things for this script to work:
#
# - Run this script from within the MySQL installation directory
# - Create a /etc/my.cnf file with the following information:
# [mysqld]
# basedir=path-to-mysql-installation-directory
# - Add the above to any other configuration file (for example ~/.my.ini)
# and copy my_print_defaults to /usr/bin
# - Add the path to the mysql-installation-directory to the basedir
variable # below.
#
# If you want to affect other MySQL variables, you should make your changes
# in the /etc/my.cnf, ~/.my.cnf or other MySQL configuration files.
basedir=
# The following variables are only set for letting mysql.server find
things.
# Set some defaults
datadir=/usr/local/mysql/data
pid_file=
if test -z $basedir
then
basedir=/usr/local/mysql
bindir=./bin
else
bindir=$basedir/bin
fi
PATH=/sbin:/usr/sbin:/bin:/usr/bin:$basedir/bin
export PATH
mode=$1# start or stop
case `echo testing\c`,`echo -n testing` in
*c*,-n*) echo_n= echo_c= ;;
*c*,*) echo_n=-n echo_c= ;;
*) echo_n= echo_c='\c' ;;
esac
parse_arguments() {
for arg do
case $arg in
--basedir=*) basedir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--datadir=*) datadir=`echo $arg | sed -e 's/^[^=]*=//'` ;;
--pid-file=*) pid_file=`echo $arg | sed -e 's/^[^=]*=//'` ;;
esac
done
}
# Get arguments from the my.cnf file,
# groups [mysqld] [mysql_server] and [mysql.server]
if test -x ./bin/my_print_defaults
then
print_defaults=./bin/my_print_defaults
elif test -x $bindir/my_print_defaults
then
print_defaults=$bindir/my_print_defaults
elif test -x $bindir/mysql_print_defaults
then
print_defaults=$bindir/mysql_print_defaults
else
# Try to find basedir in /etc/my.cnf
conf=/etc/my.cnf
print_defaults=
if test -r $conf
then
subpat='^[^=]*basedir[^=]*=\(.*\)$'
dirs=`sed -e /$subpat/!d -e 's//\1/' $conf`
for d in $dirs
do
d=`echo $d | sed -e 's/[ ]//g'`
if test -x $d/bin/my_print_defaults
then
print_defaults=$d/bin/my_print_defaults
break
fi
if test -x $d/bin/mysql_print_defaults
then
print_defaults=$d/bin/mysql_print_defaults
break
fi
done
fi
# Hope it's in the PATH ... but I doubt it
test -z $print_defaults print_defaults=my_print_defaults
fi
#
# Test if someone changed datadir; In
Re: Index on boolean column
maybe this is a silly question but how useful it is to create indexes on columns containing only values 0 and 1 (true and false)? Perhaps, instead of the index, you might revise your schema a bit. Why do you have this boolean column? What are you trying to achieve? I use boolean columns as a flags mostly to mark records as 'exported'. For example I have table 'Transactions' and I have to export data for some of our customers (never for all of them). Dusan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: license question
--- Daevid Vincent [EMAIL PROTECTED] wrote: As my company and I understand it, if you intend on distributing mySQL on this appliance and the appliance is a sealed box yes with your own proprietary code (like PHP or C or Java or whatever) that interfaces to the STOCK/Untouched RDBMS It's like this: my_code -- stock PHP/Apache -- stock MySQL you NEED a mySQL Commercial License. wowza! :-( This license is a ridiculous $600 per unit which makes it completely unrealistic for any large scale deployment!!! Well, it means your profit per unit (not counting SQL expenses) must be significantly higher than $600 Not easy, given the fierce competition in the current market. If someone from mySQL can clarify that would be great I agree that the license is murky. That's why i actually asked mysql.com a question through official channels. I'm waiting their response. -- Pat Ballard __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Counting number of values in a SET column
Okay so I'm stumped. What I want to do is run a query that can tell me
the average number of selected values in a set column.
With the column:
mySetCol SET ('cat','dog','mouse','giraffe','lion')
A row with the value dog,mouse,lion would return 3
A row with the value cat,giraffe would return 2
I've been able to get the MySQL server to give me a binary version of
the data...
Code:
SELECT RPAD(BIN(mySetCol+0),5,'0') AS bin from projects;
+---+
| bin |
+---+
| 1 |
| 1 |
| 11000 |
| 1 |
| 10110 |
+---+
If there was some way to count the occurence of the number 1 within
the string I'd have my number but I don't know of anyway to that within
MySQL.
Anyone have thoughts about how I can do this?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysqlconnecernet not closing connections w/2003
Dan, What is a 2003 server? Do you mean Win2K? I'm not aware of any OS that references 2003. Perhaps that is part of the reason for no response? I don't have any MySQL systems running on Windows machines, so I can't answer your question, but perhaps there are those out there who can, if you ask the right question Bob Dunlop -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqlconnecernet not closing connections w/2003
Robert- I apologize for not being clearer. The operating system is Windows 2003 Server. Dan -Original Message- From: Robert Dunlop [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 3:09 PM To: Daniel Cummings; mysql@lists.mysql.com Subject: Re: mysqlconnecernet not closing connections w/2003 Dan, What is a 2003 server? Do you mean Win2K? I'm not aware of any OS that references 2003. Perhaps that is part of the reason for no response? I don't have any MySQL systems running on Windows machines, so I can't answer your question, but perhaps there are those out there who can, if you ask the right question Bob Dunlop -- 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: mysqlconnecernet not closing connections w/2003
Dan, My fault. I've drifted away from MS implementations; I wasn't aware of the Win 2003 server designation. Bob - Original Message - From: Daniel Cummings [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 30, 2005 3:18 PM Subject: RE: mysqlconnecernet not closing connections w/2003 Robert- I apologize for not being clearer. The operating system is Windows 2003 Server. Dan -Original Message- From: Robert Dunlop [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 30, 2005 3:09 PM To: Daniel Cummings; mysql@lists.mysql.com Subject: Re: mysqlconnecernet not closing connections w/2003 Dan, What is a 2003 server? Do you mean Win2K? I'm not aware of any OS that references 2003. Perhaps that is part of the reason for no response? I don't have any MySQL systems running on Windows machines, so I can't answer your question, but perhaps there are those out there who can, if you ask the right question Bob Dunlop -- 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
license question
Suppose i distribute MySQL-4.1 with an appliance, which is a sealed x86 machine running a Linux distribution made by another entity (ok, it's Red Hat). I don't write any code that's directly linked to MySQL, I'm only using the existing php-mysql, etc., packages already provided by the distribution, plus some third-party apps that are under GPL and link to MySQL (applications that access MySQL, not written by me, but are Open Source GPL projects off SourceForge and other places - i just bundle them with the appliance). Any code that I write personally is PHP and sits on top of the php-mysql module provided by Red Hat. The end-user has no direct visibility to the database, in fact, the end-user might never know it's MySQL - all that is visible is the PHP interface, via Apache. In this case, what's the license? Is MySQL still free (under GPL)? -- Pat Ballard __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting number of values in a SET column
Luke Bowerman wrote:
With the column:
mySetCol SET ('cat','dog','mouse','giraffe','lion')
A row with the value dog,mouse,lion would return 3
A row with the value cat,giraffe would return 2
I've been able to get the MySQL server to give me a binary version of
the data...
SELECT RPAD(BIN(mySetCol+0),5,'0') AS bin from projects;
+---+
| bin |
+---+
| 1 |
| 1 |
| 11000 |
| 1 |
| 10110 |
+---+
If there was some way to count the occurence of the number 1 within
the string I'd have my number but I don't know of anyway to that within
MySQL.
I believe SELECT LENGTH(REPLACE(BIN(mySetCol+0),0,'')) FROM projects
will give you what you want...
HTH!
--
Hassan Schroeder - [EMAIL PROTECTED]
Webtuitive Design === (+1) 408-938-0567 === http://webtuitive.com
dream. code.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Counting number of values in a SET column
At 16:06 -0800 3/30/05, Hassan Schroeder wrote:
Luke Bowerman wrote:
With the column:
mySetCol SET ('cat','dog','mouse','giraffe','lion')
A row with the value dog,mouse,lion would return 3
A row with the value cat,giraffe would return 2
I've been able to get the MySQL server to give me a binary version
of the data...
SELECT RPAD(BIN(mySetCol+0),5,'0') AS bin from projects;
+---+
| bin |
+---+
| 1 |
| 1 |
| 11000 |
| 1 |
| 10110 |
+---+
If there was some way to count the occurence of the number 1
within the string I'd have my number but I don't know of anyway to
that within MySQL.
I believe SELECT LENGTH(REPLACE(BIN(mySetCol+0),0,'')) FROM projects
will give you what you want...
Easier to use SELECT BIT_COUNT(mySetCol+0).
With BIT_COUNT(), you may not need the +0, either.
Example:
drop table if exists t;
create table t (s set('a','b','c','d'));
insert into t set s = 'a';
insert into t set s = 'a,b';
insert into t set s = 'a,c';
insert into t set s = 'b,c,d';
insert into t set s = 'a,b,c,d';
select s, bit_count(s) from t;
Result:
+-+--+
| s | bit_count(s) |
+-+--+
| a |1 |
| a,b |2 |
| a,c |2 |
| b,c,d |3 |
| a,b,c,d |4 |
+-+--+
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: license question
Hi Pat, I thought that was discussed before over and over on this mailinglist. I am surprised that many people have isues with that topic. Basically it boils down to 2 questions. 1) Will you include and ditsribute the source code and the changes (if any) and the GPL license in your product ? If yes, than you DONT need a commercial license. if NO, (in other words you dont want to publish any changes you made to the code) then you need a commercial license. 2) Are you selling the product or a service ? If you are trying to sell the customer the very same MySQL product for $$ that he can download, then you must be good at sales, no questions asked. If you are selling a service (Consulting, Installation and setup etc. than you also dont need a commercial license ( + same as under 1 applies). Summary: You only need the commercial license if you change the code and want to distribute it as closed source. You can however at any time make a support contract or buy a commercial license to show your gratitude for the MySQL guys. That is usually a nice gesture, gets you support and backup when you need it and last but not least makes you feel good (peace of mind ;-). I hope that makes things clearer. Nils Valentin Tokyo / Japan http://www.be-known-online.com Suppose i distribute MySQL-4.1 with an appliance, which is a sealed x86 machine running a Linux distribution made by another entity (ok, it's Red Hat). I don't write any code that's directly linked to MySQL, I'm only using the existing php-mysql, etc., packages already provided by the distribution, plus some third-party apps that are under GPL and link to MySQL (applications that access MySQL, not written by me, but are Open Source GPL projects off SourceForge and other places - i just bundle them with the appliance). Any code that I write personally is PHP and sits on top of the php-mysql module provided by Red Hat. The end-user has no direct visibility to the database, in fact, the end-user might never know it's MySQL - all that is visible is the PHP interface, via Apache. In this case, what's the license? Is MySQL still free (under GPL)? -- Pat Ballard __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- 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: license question
--- [EMAIL PROTECTED] wrote: I thought that was discussed before over and over on this mailinglist. I am surprised that many people have isues with that topic. Well... 1. Licensing in general can be quite confusing for a non-lawyer geek 2. I want to make 101% sure I don't take any wrong steps before hitting the market. 1) Will you include and ditsribute the source code and the changes (if any) and the GPL license in your product ? The MySQL source code, you mean? Well, it's a sealed appliance, a black box from the customer's p.o.v. (duh, think of it as a VCR or a toaster), but sure, i can throw in a CD with source code and stuff if it's necessary. I don't plan to make any changes or write any code that even remotely touches MySQL. The only connection between the code that I write and MySQL is via php-mysql / httpd If yes, than you DONT need a commercial license. if NO, (in other words you dont want to publish any changes you made to the code) then you need a commercial license. I'm not making any changes to MySQL whatsoever. 2) Are you selling the product or a service ? If you are trying to sell the customer the very same MySQL product for $$ that he can download, then you must be good at sales, no questions asked. Same reasoning would apply to the hundreds, if not thousands other appliances currently on the market which are also running Linux (which is also something that the customer can download for free). Are all of those companies just good at sales? Case in point: the Linksys routers which everyone owns and which run Linux. My appliance is the same. It just happens it needs a SQL backend. Might be MySQL. Might be PostgreSQL if either/or it's faster in my particular case or more liberally licensed than MySQL (which are things I'm still investigating). Might be something else. shrug -- Pat Ballard __ Do you Yahoo!? Yahoo! Small Business - Try our new resources site! http://smallbusiness.yahoo.com/resources/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
