Data corruption following a power failure.
Hi. I've noticed that when a server running MySQL crashes and comes back to life, the MyISAM tables typically need to be repaired to account for the fact that they were not flushed when the server went down. However, I've also noticed that when the database is recovered from a crash, the tables can be inconsistent with regards to the temporal relationship of the most recent updates performed on them. For example, it's possible that after a crash, UPDATE A, UPDATE B, and UPDATE C (which were performed sequentially before the crash) will not all be present in the repaired table. It may be that only updates B and C were stored to disk before the server went down. UPDATE A, which happened before the other updates, may nonetheless be missing. Is this the true behavior or does it just appear to work this way? And if so, is there any way to force MySQL to automatically save data to disk in a way that is chronologically correct? This would make it much easier for me to recover from an unexpected crash. Thanks.
MySQL User Conference Computer Lab Sessions and Early Bird Sessions
Greetings All, If you are interested in attending the conference in person (or virtually), please read on. First, in addition to our regularly scheduled conference sessions, we are going to be having about a dozen training sessions. The sessions will let small groups of attendees (about 10 per session) get 30 to 90 minutes of hands-on training with MySQL staff and MySQL community leaders. We will be covering topics like query and server optimization, coaching on new (and old) feature use and providing step-by-step demos of some cool new software. Perhaps the most exciting session for the veteran hackers on this list will be a 90 minute workshop with some of our developers (including Monty) where they will walk you through the major architectural components (and the corresponding source) of the MySQL database and MyISAM storage engine. While MySQL is providing the speakers for the sessions, Apple is graciously providing the computers - students will be sharing a shiny new G5 during the training. We are currently looking at ways to let people participate virtually in some of these sessions and are going to be testing the use of VNC and other remote technologies. If you want to participate in the testing, please write me personally and let me know. These sessions will be included in the conference fees, but space is limited and attendees will have to register to participate. A fixed list of sessions and registration for them should be posted next week. The conference team will send out an informational message at that time. Speaking of conference fees, the $100 early bird discount ends on February 14th. To take advantage of the discount, visit https://order.mysql.com/?sub=pgpg_no=14 Also, as a reward for the people who help keep Free Software and Open Source going, I am happy to announce that we will be offering a discount to the conference fee to those who actively contribute to the software and community commons. I will post the guidelines for eligibility early next week. If you have any questions, feel free to drop me a line - though please note that I will be traveling for the next week and may take a day or three to respond. Finally, if the mention of the conference comes as a surprise to you, visit http://www.mysql.com/events/uc2004/ for an overview. Cheers! --zak -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0 crashed; Please help
Can someone help me find out what caused this crash? My system is a Red Hat 7.3 on a HP Netserver LC2000, dual PIII 800mhz with 2.5G ram. 3 disks in raid 5. The MySQL database is 4.0.17 040212 19:27:36 read_const: Got error 126 when reading table ./exp4/sql_cache Number of processes running now: 0 040212 19:28:04 mysqld restarted InnoDB: Fatal error: cannot allocate 1073758208 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 24482732 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=0 max_connections=800 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 291705 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8450b80 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe718, stack_bottom=0x58575655, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x54535251 is invalid pointer thd-thread_id=1601790316 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040212 19:28:06 mysqld ended -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: didn't find any fields error
Reading a hint in the net I created a dbfile with mysqldump on the source pc (WinXP): mysqldump dbfile mysqldbname Now I have to take this file and create the database in the dest pc (Linux). I should use mysqladmin (is it true?) but what's the right command? Thanks Mic. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Collation and LONGBLOB fields
Matt Mastrangelo [EMAIL PROTECTED] wrote: Here is the create statement and the resulting table: mysql create table test( - myblob longblob) default charset latin1 default collate latin1_general_cs; Query OK, 0 rows affected (0.08 sec) mysql show create table test \G *** 1. row *** Table: test Create Table: CREATE TABLE `test` ( `myblob` longtext character set latin1 collate latin1_general_cs ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) Thank you for bug report! -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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]
Can mysql do DESC indexes?
Hello, can the new version of mysql 5 do reversed indexes? CREATE INDEX table ON (name(10) DESC); In manual it is described to be a fortcoming feature but and to be ignored in present version. regards /Jonas
Re: MySQL Lost 3 Weeks of Data
Michael, check all your connectivity settings (host and port) of all of the software that you use (backup scripts for example) especially if you connect via tcp/ip instead of via sockets. Check the error log, too. Did you move something on January, 20th (database)? From our own experiences it sounds to me it is most likely a failure in the human interface but I may be totally wrong. Good Luck! Regards, Frank. Schmuck, Michael schrieb: Hello I've got a big problem. My MySQL server has yesterday lost data since 20th january. Fact, we got a daily tape backup. All our tapes (monday - friday tapes, oldest one is we 04th feb) are holding the database of 19th january evening. Yesterday at about 14 o'clock we resartet the demon on our bsd server since september 03. I belive the deamon didn't wrote the data into the files. At the restart of the database he loaded the old files and lost erverything newer then 20th. Everyone in my company says thats impossible (lost during restart of deamon). Everyone else says somthing wrong with the database update software, but the user worked all feb with this data. Wed morning he says he could work with it, but after our restart the database was like 19th jan. Has someone ideas why this happened? I couldn't explan to me why this happened! grds, Michael Schmuck p.s. sorry for bad english -- Dr. Frank Ullrich, DBA Netzwerkadministration Heise Zeitschriften Verlag GmbH Co KG, Helstorfer Str. 7, D-30625 Hannover E-Mail: [EMAIL PROTECTED] Phone: +49 511 5352 587; FAX: +49 511 5352 538 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.0.18 has been released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.0.18, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Fixed processing of `LOAD DATA' by `mysqlbinlog' in remote mode. (Bug #1378) * New utility program `myisam_ftdump' was added to binary distributions. * `mysqlhotcopy' now works on NetWare. * `ENGINE' is now a synonym for the `TYPE' option for `CREATE TABLE' and `ALTER TABLE'. * `lower_case_table_names' system variable now can take a value of `2', to store table names in mixed case on case-insensitive filesystems. * For replication of `HEAP' tables: Made the master automatically write a `DELETE FROM' statement to its binary log when a `HEAP' table is opened for the first time since master's startup. This is for the case where the slave has replicated a non-empty `HEAP' table, then the master is shut down and restarted: the table is now empty on master; the `DELETE FROM' empties it on slave too. Note that even with this fix, between the master's restart and the first use of the table on master, the slave still has out-of-date data in the table. But if you use the `init-file' option to populate the `HEAP' table on the master at startup, it ensures that the failing time interval is zero. (Bug #2477) * Optimizer is now better tuned for the case where the first used key part (of many) is a constant. (Bug #1679) * Removed old non-working `--old-rpl-compat' server option, which was a holdover from the very first 4.0.x versions. (Bug #2428) Bugs fixed: * Fixed bug when -init-file crashes MySQL if contains large select (Bug #2526) * `SHOW KEYS' now shows `NULL' in `Sub_part' column for `FULLTEXT' indexes. * The size of the signal thread's stack was increased to enable `mysqld' to run on Debian/ia64 with a TLS-enabled glibc. (Bug #2599) * Now one need only `SELECT' privilege for tables that are only read in `UPDATE' statements with many tables. (Bug #2377). * Give proper error message if one uses `LOCK TABLES ... ; INSERT ... SELECT' and one used the same table in the `INSERT' and `SELECT' part. (Bug #2296) * `SELECT INTO ... DUMPFILE' now deletes the generated file on error. * Fixed foreign key reference handling to allow references to column names that contain spaces. (Bug #1725) * Fixed problem with index reads on character fields with `BDB' tables. The symptom was that data could be returned in wrong lettercase. (Bug #2509) * Fixed a spurious table corruption problem that could sometimes appear on tables with indexed `TEXT' columns if these columns happened to contain values having trailing spaces. This bug was introduced in 4.0.17. * Fixed a problem where some queries could hang if a condition like `indexed_TEXT_column = expr' was present and the column contained values having trailing spaces. This bug was introduced in 4.0.17. * Fixed a bug that could cause incorrect results from a query that involved range conditions on indexed `TEXT' columns that happened to contain values having trailing spaces. This bug was introduced in 4.0.17. (Bug #2295) * Fixed incorrect path names in some of the manual pages. (Bug #2270) * Fixed spurious table corrupted errors in parallel repair operations. *Note `myisam_repair_threads': SHOW VARIABLES. * Fixed a crashing bug in parallel repair operations. *Note `myisam_repair_threads': SHOW VARIABLES. * Fixed bug in updating `MyISAM' tables for `BLOB' values longer than 16M. (Bug #2159) * Fixed bug in `mysqld_safe' when running multiple instances of MySQL. (Bug #2114) * Fixed a bug in using `HANDLER' statement with tables not from a current database. (Bug #2304) * Fix for a crashing bug that occurred due to the fact that multiple-table `UPDATE' statements did not check that there was only one table to be updated. (Bug #2103) * Fix for a crashing bug that occurred due to `BLOB' column type index size being calculated incorrectly in `MIN()' and `MAX()' optimizations. (Bug #2189) * Fix for a bug with incorrect syntax for `LOCK TABLES' in `mysqldump'. (Bug #2242) * Fixed a bug in `mysqld_safe' that caused
Re: didn't find any fields error
I solved!! The dbfile was a sequence of sql commnds, so the command to import that file was mysql dbname dbfile after creating dbname. My job chair is saved (maybe)! - Original Message - From: michele digioia [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 13, 2004 10:15 AM Subject: Re: didn't find any fields error Reading a hint in the net I created a dbfile with mysqldump on the source pc (WinXP): mysqldump dbfile mysqldbname Now I have to take this file and create the database in the dest pc (Linux). I should use mysqladmin (is it true?) but what's the right command? Thanks Mic. -- 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: Can mysql do DESC indexes?
Jonas Lind?n [EMAIL PROTECTED] wrote: Hello, can the new version of mysql 5 do reversed indexes? CREATE INDEX table ON (name(10) DESC); In manual it is described to be a fortcoming feature but and to be ignored in present version. Currently it does nothing. Indexes are stored in the ascending order. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe Database Problem
Mike Miller [EMAIL PROTECTED] wrote: Hi, I believe I've done everything from the book and have been fighting with the same problem for about 6 hours thus far. I just upgraded from MySQL 3.23.56 to 4.0.17. In the old system, I prevented show databases from ordinary users using skip-show-databases. In order to upgrade I used the same data directory and ran the fix_privs script as instructed to all all of the required fields to the mysql.* tables as it did. ALl of this is successful and I can see it all there when I browse these tables. I did a flush privs, reloaded the whole daemon a few times to no avail. All users besides root have been revoked (actually they never had) show databases permission, yet all users are still able to execute show databses and receive a list of hundreds of databases. SHOW VARIABLES keeps telling me that skip show database is OFF, yet I can't seem to change it with any combo of startup parameters or set commands due to it being a 'depreciated variable' in all the docs. I don't see what I'm missing. Is there a trick with the new versions which I'm not getting? Check privileges of those users. If they have such global privileges (on all databases) as SELECT, UPDATE etc. or CREATE TEMPORARY TABLES, LOCK TABLES, they also can see databases in the output of SHOW DATABASES. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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: MySQL Lost 3 Weeks of Data
Schmuck, Michael wrote: I've got a big problem. My MySQL server has yesterday lost data since 20th january. Yesterday at about 14 o'clock we resartet the demon on our bsd server since september 03. I belive the deamon didn't wrote the data into the files. At the restart of the database he loaded the old files and lost erverything newer then 20th. What do you mean: he loaded the old files and lost everything newer than 20th. Did he restore from a backup tape? If so, then it's probably a problem with your backup system not properly backing up the mysql tables. While the Mysql process is running, the files are in-use by Mysqld, and many tape-backup programs cannot properly copy files that are in-use. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Another beginner question
Marty Ray [EMAIL PROTECTED] wrote: Brace yourself for another beginner question... When I try to use the SHOW command (logged in as root) , I get an error: mysql show databases; ERROR: No query specified What version of MySQL do you use? What OS? There is a bug on Mac OS X in MySQL 4.1.1: http://bugs.mysql.com/bug.php?id=2058 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Too many columns error when altering column type to ENUM?
I've seen a number of references to the Too many columns error, but I'm encountering something that appears to be different. Basically, I'm trying to change a single column in a table (that has 20 columns) from VARCHAR(255) to ENUM(...) where the enum list contains about 900 entries, each a string of about 50 characters or so. From what I understand the enum limit is about 65000, and the number of enum columns in a single table is about 255, but since I'm looking at values of 900 and ~5, respectively, I don't think I'm hitting these limits. Any ideas why I'm getting this error? I'm using version 4.0.13. Thanks.
MySQL Administrator certain options only valid at localhost
Hi. I've downloaded MySql Administrator 1.0.1a alpha for Windows and tried it. It has options only enabled if MySQL Admnistrator is connected to localhost. I'm connected as root to other host (which is Linux). For example the Startup Variables. Some of these options are the most interesting options and I want to use MySQL Administrator for connect to remote hosts. These remote hosts are Linux that could be in the same LAN but that doesn't have X server installed. What is the reason for this? In the future these options will be enabled? Iago. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Strange query behavior
Whilst trying to figure out why one of my queries wasn't working I ran into the following phenomenon which I'd be grateful if someone could explain. (I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4 mysql control center) The following query returns the expected result SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type it returns 1008 ,212 for error_type and occurrence respectively. When I change the query to SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO, active_odds AS AO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type the query executes BUT doesn't return anything at all. I can't quite figure out why. The only difference between the two queries is that the second one has active_odds AS AO added to the FROM clause. Even though I don't use AO at all in the query the results still differ. Anyone have an explanation ? Having a superflous table in the WHERE clause shouldn't affect the query outcome should it ? //Linus Nikander - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Administrator certain options only valid at localhost
A probable reason is that these are in startup files and hence the interface needs file system access. AFAIAA There are plans to have dynamic settings that can be changed at runtime, but these are still plans. No idea on the rest of it thou. Neat tool, and looks great! Like the backup tools. The scheduled bit of this is blank on mine so I assume that this is a bug as it should display the Local host only message. Greg -Original Message- From: Iago Sineiro [mailto:[EMAIL PROTECTED] Sent: 13 February 2004 12:10 To: MySql Mail List Subject: MySQL Administrator certain options only valid at localhost Hi. I've downloaded MySql Administrator 1.0.1a alpha for Windows and tried it. It has options only enabled if MySQL Admnistrator is connected to localhost. I'm connected as root to other host (which is Linux). For example the Startup Variables. Some of these options are the most interesting options and I want to use MySQL Administrator for connect to remote hosts. These remote hosts are Linux that could be in the same LAN but that doesn't have X server installed. What is the reason for this? In the future these options will be enabled? Iago. -- 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: Strange query behavior
Linus Nikander wrote: Whilst trying to figure out why one of my queries wasn't working I ran into the following phenomenon which I'd be grateful if someone could explain. (I'm running mySQL 4.0.17 on a win2003 server, querying using the 0.9.4 mysql control center) The following query returns the expected result SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type it returns 1008 ,212 for error_type and occurrence respectively. When I change the query to SELECT distinct(PO.error_type) AS error_type, COUNT(PO.error_type) AS occurrence FROM problematic_odds AS PO, active_odds AS AO This cartesian product will be empty if active_odds is empty. WHERE NOT (PO.error_type = 100) GROUP BY PO.error_type the query executes BUT doesn't return anything at all. I can't quite figure out why. The only difference between the two queries is that the second one has active_odds AS AO added to the FROM clause. Even though I don't use AO at all in the query the results still differ. Anyone have an explanation ? Having a superflous table in the WHERE clause shouldn't affect the query outcome should it ? //Linus Nikander - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL/InnoDB-4.0.18 is released
Hi! InnoDB is a MySQL table type which provides transactions, row-level locking, foreign key constraints, and a non-free hot backup tool for backing up InnoDB tables. InnoDB is included in all MySQL-4.0, 4.1, and 5.0 downloads, and also in the MySQL Pro commercial, non-GPL MySQL license. Release 4.0.18 is mainly a bugfix release, but there are some functional changes in FOREIGN KEY handling as well. The full changelog: * Do not allow dropping a table referenced by a FOREIGN KEY constraint, unless the user does SET FOREIGN_KEY_CHECKS=0. The error message here is somewhat misleading 'Cannot delete or update a parent row...', and must be changed in a future version 4.1.x. * Make InnoDB to remember the CONSTRAINT name given by a user to a FOREIGN KEY. * Change print format of FOREIGN KEY constraints spanning multiple databases to backquotedatabasenamebackquote.backquotetablenamebackquote. But when parsing them we must also accept backquotedatabasename.tablenamebackquote, because that was the output format in 4.0.18. * An optimization in locking: if AUTOCOMMIT=1, then we do not need to make a plain SELECT set shared locks even on the SERIALIZABLE isolation level, because we know the transaction is read-only: a read-only transaction can always be performed on the REPEATABLE READ level, and that does not endanger the serializability. * Implement an automatic downgrade from = 4.1.1 - 4.0.18 if the user has not created tables in .ibd files or used other 4.1.x features. CONSULT the manual section http://www.innodb.com/ibman.php#Multiple.tablespaces carefully if you want to downgrade! * Fixed a bug: MySQL should not let REPLACE to perform internally an UPDATE if the table is referenced by a FOREIGN KEY. The MySQL manual says that REPLACE must resolve a duplicate key error semantically with DELETE(s) + INSERT, and not by an UPDATE. In versions 4.0.18 and 4.1.2 MySQL could resolve a duplicate key conflict in REPLACE by doing an UPDATE on the existing row, and FOREIGN KEY checks could behave in a semantically wrong way. (Bug #2418) * Fixed a bug: generate foreign key identifiers locally for each table, in the form 'databasename/tablename_ibfk_number'. If the user gives the constraint name explicitly, then remember it. These changes should ensure that foreign key id's in a slave are the same as in the master, and DROP FOREIGN KEY does not break replication. (Bug #2167) * Fixed a bug: allow quoting of identifiers in InnoDB's FOREIGN KEY definitions with backquote (`) and double quote (). You can now use also spaces in table and column names, if you quote the identifiers. (Bug #1725) (Bug #2424) * Fixed a bug: FOREIGN KEY ... ON UPDATE/DELETE NO ACTION must check the foreign key constraint, not ignore it. Since we do not have defered constraints in InnoDB, this bug fix makes InnoDB to check NO ACTION constraints immediately, like it checks RESTRICT constraints. * Fixed a bug: InnoDB crashed in RENAME TABLE if 'databasename.tablename' is shorter than 5 characters. (Bug #2689) * Fixed a bug: InnoDB row count and index cardinality estimates wrapped around at 512 million in 32-bit computers. Note that unless MySQL is compiled with the BIG_TABLES option, they will still wrpa around at 4 billion. * Fixed a bug: with InnoDB, UNIQUE secondary index, and NULL values in that unique index; with the IS NULL predicate, InnoDB returned only the first matching row, though there can be many. This bug was introduced in 4.0.16. (Bug #2483) Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL 4.0.18 has been released
Hi, Can anyone tell where to find documentation on myisam_ftdump? I would like to know what myisam_ftdump is and how it differs from mysqldump. John -Original Message- From: Lenz Grimmer [mailto:[EMAIL PROTECTED] Sent: Friday, February 13, 2004 4:42 AM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: MySQL 4.0.18 has been released -BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL 4.0.18, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is a bugfix release for the current production version. Please refer to our bug database at http://bugs.mysql.com/ for more details about the individual bugs fixed in this version. News from the ChangeLog: Functionality added or changed: * Fixed processing of `LOAD DATA' by `mysqlbinlog' in remote mode. (Bug #1378) * New utility program `myisam_ftdump' was added to binary distributions. * `mysqlhotcopy' now works on NetWare. * `ENGINE' is now a synonym for the `TYPE' option for `CREATE TABLE' and `ALTER TABLE'. * `lower_case_table_names' system variable now can take a value of `2', to store table names in mixed case on case-insensitive filesystems. * For replication of `HEAP' tables: Made the master automatically write a `DELETE FROM' statement to its binary log when a `HEAP' table is opened for the first time since master's startup. This is for the case where the slave has replicated a non-empty `HEAP' table, then the master is shut down and restarted: the table is now empty on master; the `DELETE FROM' empties it on slave too. Note that even with this fix, between the master's restart and the first use of the table on master, the slave still has out-of-date data in the table. But if you use the `init-file' option to populate the `HEAP' table on the master at startup, it ensures that the failing time interval is zero. (Bug #2477) * Optimizer is now better tuned for the case where the first used key part (of many) is a constant. (Bug #1679) * Removed old non-working `--old-rpl-compat' server option, which was a holdover from the very first 4.0.x versions. (Bug #2428) Bugs fixed: * Fixed bug when -init-file crashes MySQL if contains large select (Bug #2526) * `SHOW KEYS' now shows `NULL' in `Sub_part' column for `FULLTEXT' indexes. * The size of the signal thread's stack was increased to enable `mysqld' to run on Debian/ia64 with a TLS-enabled glibc. (Bug #2599) * Now one need only `SELECT' privilege for tables that are only read in `UPDATE' statements with many tables. (Bug #2377). * Give proper error message if one uses `LOCK TABLES ... ; INSERT ... SELECT' and one used the same table in the `INSERT' and `SELECT' part. (Bug #2296) * `SELECT INTO ... DUMPFILE' now deletes the generated file on error. * Fixed foreign key reference handling to allow references to column names that contain spaces. (Bug #1725) * Fixed problem with index reads on character fields with `BDB' tables. The symptom was that data could be returned in wrong lettercase. (Bug #2509) * Fixed a spurious table corruption problem that could sometimes appear on tables with indexed `TEXT' columns if these columns happened to contain values having trailing spaces. This bug was introduced in 4.0.17. * Fixed a problem where some queries could hang if a condition like `indexed_TEXT_column = expr' was present and the column contained values having trailing spaces. This bug was introduced in 4.0.17. * Fixed a bug that could cause incorrect results from a query that involved range conditions on indexed `TEXT' columns that happened to contain values having trailing spaces. This bug was introduced in 4.0.17. (Bug #2295) * Fixed incorrect path names in some of the manual pages. (Bug #2270) * Fixed spurious table corrupted errors in parallel repair operations. *Note `myisam_repair_threads': SHOW VARIABLES. * Fixed a crashing bug in parallel repair operations. *Note `myisam_repair_threads': SHOW VARIABLES. * Fixed bug in updating `MyISAM' tables for `BLOB' values longer than 16M. (Bug #2159) * Fixed bug in `mysqld_safe' when running multiple instances of MySQL. (Bug #2114) * Fixed a bug in using `HANDLER' statement with tables not from a current database. (Bug #2304) * Fix for a crashing bug that occurred due to the fact that multiple-table `UPDATE' statements did not check that
Timestamp plus 365 days
Am I able to add 365 days to an already existing TIMESTAMP on a subscription service? If so I was going to use a form on a web page to update it and in the insert statement use Now() + INTERVAL 365 DAY but after some consideration, this would be wrong. This would cause a problem if a current subscriber updated their subscription for another year before their present subscription ran out. In such a case, how do I take a date, eg: 1st March 2004 and add 365 days to it so that no matter when I edit it, it returns the date 1st March 2005? Do I have to change the column type in the database or is it some clever sql - or both? Thanks Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 crashed; Please help
Did you perform a stack trace? Original Message On 2/13/04, 2:36:42 AM, Jacob Friis Larsen [Eksperten admin] [EMAIL PROTECTED] wrote regarding MySQL 4.0 crashed; Please help: Can someone help me find out what caused this crash? My system is a Red Hat 7.3 on a HP Netserver LC2000, dual PIII 800mhz with 2.5G ram. 3 disks in raid 5. The MySQL database is 4.0.17 040212 19:27:36 read_const: Got error 126 when reading table ./exp4/sql_cache Number of processes running now: 0 040212 19:28:04 mysqld restarted InnoDB: Fatal error: cannot allocate 1073758208 bytes of InnoDB: memory with malloc! Total allocated memory InnoDB: by InnoDB 24482732 bytes. Operating system errno: 12 InnoDB: Cannot continue operation! InnoDB: Check if you should increase the swap file or InnoDB: ulimits of your operating system. InnoDB: On FreeBSD check you have compiled the OS with InnoDB: a big enough maximum process size. InnoDB: We now intentionally generate a seg fault so that InnoDB: on Linux we get a stack trace. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=402653184 read_buffer_size=1044480 max_used_connections=0 max_connections=800 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 291705 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x8450b80 Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Bogus stack limit or frame pointer, fp=0xbfffe718, stack_bottom=0x58575655, thread_stack=126976, aborting backtrace. Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x54535251 is invalid pointer thd-thread_id=1601790316 The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 040212 19:28:06 mysqld ended -- 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: Data corruption following a power failure.
Andrew Iles wrote: Is this the true behavior or does it just appear to work this way? And if so, is there any way to force MySQL to automatically save data to disk in a way that is chronologically correct? This would make it much easier for me to recover from an unexpected crash. What os/version? What mysql version? 1. Invest in a UPS (Uninterruptable Power Supply) to greatly reduce the problems with random power loss. 2. Periodically run fsync to flush the filesystem cache to disk. 3. Use a transaction/journaled database (innodb). 4. Use a transaction/journaled filesystem (jfs, xfs). -- jimoe at sohnen-moe dot com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Unique IDs
On 12 Feb 2004 at 22:57, Bill Easton wrote: You can use + 1 instead of + interval 1 second, but it may give different results some day if MySQL changes the precision of timestamp. Actually, that won't work in all cases. If the current timestamp is 20040213114859, then adding 1 (rather than INTERVAL 1 SECOND), gives 20040213114860, which gets converted to 20040213114800 on insert. If you've already used all the timestamps for that minute, then you're never going to get to the next minute (and thus never going to get a usable ID) by repeatedly adding 1. -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOTBACKUP INNODB
Title: Message I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values:innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING Arnoldus Th.J. KoelemanPrincipal Engineer NetNumber Inc.650 Suffolk Streetsuite 307Lowell MA, 01854 [EMAIL PROTECTED] tel: mobile: 978-848-2836978-397-1434 Powered by Plaxo Want a signature like this? Add me to your address book...
Re: Timestamp plus 365 days
My first question would be, Why is this column is TIMESTAMP?. It seems to me that if its purpose is to store the subscription start date, it should be of type DATE. Usually, you use a TIMESTAMP column to automatically keep track of the last updated time for a row. Either way (DATE or TIMESTAMP), if you want to add a year to the start_date, you should do just that. Something like UPDATE subscriptions SET start_date = start_date + INTERVAL 1 YEAR WHERE customer_id = ... Don't use +365 days, as some years (leap years) have 366. mysql SELECT CURDATE(), - CURDATE() + INTERVAL 365 DAY `+365 days`, - CURDATE() + INTERVAL 1 YEAR `+1 year`; ++++ | CURDATE() | +365 days | +1 year| ++++ | 2004-02-13 | 2005-02-12 | 2005-02-13 | ++++ 1 row in set (0.01 sec) You also need to decide what to do if a customer renews his or her subscription after it expires. Will you add 1 year to the previous start date, or will you start from the renewal date? Michael Matthew Stuart wrote: Am I able to add 365 days to an already existing TIMESTAMP on a subscription service? If so I was going to use a form on a web page to update it and in the insert statement use Now() + INTERVAL 365 DAY but after some consideration, this would be wrong. This would cause a problem if a current subscriber updated their subscription for another year before their present subscription ran out. In such a case, how do I take a date, eg: 1st March 2004 and add 365 days to it so that no matter when I edit it, it returns the date 1st March 2005? Do I have to change the column type in the database or is it some clever sql - or both? Thanks Mat -- Matthew Stuart 11 Yew Tree Close Middleton Cheney Banbury Oxon OX17 2SU 01295 713813 07803 207734 -- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
HOTBACKUP
I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values: innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING
RE: MySQL Administrator certain options only valid at localhost
Thanks for the information. I also think it's a great tool. Iago. -Mensaje original- De: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Enviado el: viernes, 13 de febrero de 2004 14:30 Para: [EMAIL PROTECTED] Asunto: RE: MySQL Administrator certain options only valid at localhost A probable reason is that these are in startup files and hence the interface needs file system access. AFAIAA There are plans to have dynamic settings that can be changed at runtime, but these are still plans. No idea on the rest of it thou. Neat tool, and looks great! Like the backup tools. The scheduled bit of this is blank on mine so I assume that this is a bug as it should display the Local host only message. Greg -Original Message- From: Iago Sineiro [mailto:[EMAIL PROTECTED] Sent: 13 February 2004 12:10 To: MySql Mail List Subject: MySQL Administrator certain options only valid at localhost Hi. I've downloaded MySql Administrator 1.0.1a alpha for Windows and tried it. It has options only enabled if MySQL Admnistrator is connected to localhost. I'm connected as root to other host (which is Linux). For example the Startup Variables. Some of these options are the most interesting options and I want to use MySQL Administrator for connect to remote hosts. These remote hosts are Linux that could be in the same LAN but that doesn't have X server installed. What is the reason for this? In the future these options will be enabled? Iago. -- 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]
Query fast via mysql CLI, Same query slow via Perl DBI
Hi,
When executing this query through the 'mysql' command line utility, the
result is returned from the database server immediately and the database
server does not create a temporary file.
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = accounts.username AND
zone_records.type = 'username'
WHERE domains.host = 1)
UNION
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = domains.domain AND
zone_records.type = 'domain'
WHERE domains.host = 1)
ORDER BY domains.domain,zone_records.type
LIMIT 871;
When executing the exact same query in a Perl script via the DBI
interface, the database server takes minutes to return the results,
using large amounts of CPU whilst computing the results and creating a
temporary file in /var/tmp.
I have turned on query logging and verified that the query logged by the
server is the same when executed with both the mysql and perl clients so
it does not seem that the mysql command line is optimising the query in
any way.
I have restarted the database server between queries to ensure that the
results are not being cached.
The two clients are connecting from the same server using the same
username and password to login.
The structure of the tables concerned is as follows:
CREATE TABLE `accounts` (
`username` varchar(20) NOT NULL default '',
`owner` varchar(20) NOT NULL default '',
`type` varchar(10) default NULL,
`server` tinyint(3) default NULL,
`winserver` tinyint(3) default NULL,
PRIMARY KEY (`username`),
KEY `owner` (`owner`),
) TYPE=InnoDB
CREATE TABLE `domains` (
`domain` varchar(255) NOT NULL default '',
`username` varchar(20) NOT NULL default '',
`host` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`domain`),
KEY `username` (`username`)
) TYPE=InnoDB
CREATE TABLE `zone_records` (
`zone` varchar(255) NOT NULL default '',
`type` enum('username','domain','component','default') NOT NULL
default 'username',
`record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A',
`ttl` smallint(5) unsigned default NULL,
`data` varchar(255) NOT NULL default '',
KEY `zone` (`zone`),
KEY `zone_type` (`zone`,`type`)
) TYPE=InnoDB
Software versions:
mysql-4.0.12-standard-log
DBI-1.30
DBD-mysql-2.1018
I am really at a loss as to what could be causing this and what to do to
correct the problem. Any assistance or advice you can offer is greatly
appretiated.
Thanks in advance.
Dan Conlon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Inconsistant TIMESTAMP behavoir
Hi, I am experiencing strange behavior from a TIMESTAMP column in an
InnoDB table on my MySQL 4.0.16 server. Specifically, it fails to
select certain rows by this column. The table is defined as:
CREATE TABLE log (
s_id int(11) not null,
p_id int(11) not null,
action enum('A', 'B', 'C'),
tstamp timestamp(14),
index ( s_id, p_id ),
index ( tstamp )
);
My problem exists for the first few rows in this table. For example,
take the first row by date:
mysql select min(tstamp) from log;
++
| min(tstamp)|
++
| 20031026010002 |
++
Now consider the following attempts to select this row:
mysql select * from log where tstamp = 20031026010002;
mysql select * from log where tstamp = '20031026010002';
mysql select * from log where tstamp = 20031026010002;
mysql select * from log where tstamp = 2003-10-26 01:00:02;
mysql select * from log where tstamp 20031026010001
- and tstamp 20031026010003;
None of these return any rows. To add insult to injury, this database
is replicated, and on the *replicated* database, ALL of these queries
work. The only query by date that work on the main database is:
mysql select * from log where tstamp 20031026010003
Which doesn't make any sense considering that the date range query
didn't work.
It seemed like this table must be corrupted somehow, so I ran a 'check
table' on it which return a status of OK.
Unfortunately I cannot replicate this problem, so my hope is that
someone else has experienced this as well, or that someone can suggest a
method to repair this table.
Some final notes. While this problem may have existed previously, I
only noticed it when I began deleting old log data by date via a
script. Furthermore, only the first 2000 or so earliest dates cannot be
selected. All dates after this 'blackout' period can be selected
without a problem.
--
|- Garth Webb -|
|- [EMAIL PROTECTED] -|
signature.asc
Description: This is a digitally signed message part
HOTBACKUP
I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values: innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING
FW: Installation issue on SuSE 9.0 Pro box
Title: FW: Installation issue on SuSE 9.0 Pro box I was curious if anyone has experienced this same error and even more curious to see if anyone might offer some advice. Thanks! Dave -Original Message- From: David Christensen [mailto:[EMAIL PROTECTED]] Sent: Thursday, February 12, 2004 11:22 AM To: mysql Subject: Installation issue on SuSE 9.0 Pro box Good Morning! I'm trying to get MySQL 4.0.15 running on a new SuSE linux PC. This is my first attempt installing on SuSE after setting up a large number of Red Hat machines without problem. Right now, however, I'm continually receiving errors that I just don't understand where they are coming from. I've even copied the .cnf file from my primary MySQL server (which has been running for months) and get the errors when it's trying to fire up. Errors received are: 040211 17:31:37 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/data/mysql/' 040211 17:31:37 mysqld ended To get started, I copied the my-large.cnf to /etc as my.cnf and have edited the file to reflect the directories I'm using to store data and logs. The file I am using is as follows: (See attachment) I just don't get this one. All of the syntax seems okay, and I'm actually using the file on a Red Hat 9.0 machine. Thanks! Dave Christensen # *** # *** MySQL Database Parameters for Brokers International, LX01 *** # *** === *** # *** Author: D Christensen Date: April 2, 2003 *** # *** Production Databases*** # *** # The following options will be passed to all MySQL clients [client] #password = your_password port= 3306 socket = /var/data/mysql/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port= 3306 socket = /var/data/mysql/mysql.sock skip-locking set-variable= key_buffer=16M set-variable= max_allowed_packet=16M set-variable= table_cache=256 set-variable= sort_buffer=16M set-variable= record_buffer=16M set-variable= myisam_sort_buffer_size=64M set-variable= thread_cache=8 # Try number of CPU's*2 for thread_concurrency set-variable= thread_concurrency=4 # log-bin server-id = 1 # basedir = /var/lib/mysql datadir = /var/data/mysql innodb_data_home_dir = /var/data/mysql innodb_data_file_path = ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata4:2000M;ibdata5:2000M:autoextend innodb_log_group_home_dir = /var/data/logs/mysql/ innodb_log_arch_dir = /var/data/logs/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high set-variable = innodb_buffer_pool_size=256M set-variable = innodb_additional_mem_pool_size=20M # Set .._log_file_size to 25 % of buffer pool size set-variable = innodb_log_file_size=64M set-variable = innodb_log_buffer_size=8M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 # Point the following paths to different dedicated disks tmpdir = /usr/tmp/ log-update = /var/data/logs/mysql/LX01 [mysqldump] quick set-variable= max_allowed_packet=16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [isamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [myisamchk] set-variable= key_buffer=128M set-variable= sort_buffer=128M set-variable= read_buffer=2M set-variable= write_buffer=2M [mysqlhotcopy] interactive-timeout -- 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]
[4.1.1] 1062 errors on non-unique index during data load
In the last few days I've been doing some prototyping on mysql 4.1.1 (mainly because I want fulltext indexes against utf-8 data). I have a table, artists_search_A, in which I want to load ~100K records. My load process will routinely load 14783 records successfully. After 14783 inserts, any insert attempt results in a 1062 error, duplicate entry; specifically, Duplicate entry 'en-us' for key 2 error 1062 recorded That's on the lang_code column. Looking through my source data, I have precisely 4 distinct lang_codes, and at this point _only_ en-us data have been loaded. The prior 14783 records should not have loaded successfully if the index were truly unique. A quick check in the output of 'show index' (pasted below) shows the lang_code index to be non-unique. Out of ~20 separate attempts in the last few days (each time I tweak something hoping to find a remedy), in only one case did all the data load. Sadly, I have no idea what was unique about that run. All other attempts bomb out after precisely 14783 records... Anyone else encountering this? It sounds a lot like bug 2401 http://bugs.mysql.com/bug.php?id=2401 except that it happens even if no other thread accesses the table during the load, and it always occurs after exactly the same number of inserts. - mark mysql desc artists_search_A; +--+--+--+-+-+---+ | Field| Type | Null | Key | Default | Extra | +--+--+--+-+-+---+ | artist_id| int(11) | | PRI | 0 | | | lang_code| varchar(5) | | PRI | | | | name | varchar(128) | | MUL | | | | major_cat| smallint(6) | | MUL | 0 | | | minor_cat| smallint(6) | | MUL | 0 | | | events_scheduled | char(1) | | MUL | n | | | unmapped | char(1) | YES | MUL | NULL| | | team | char(1) | YES | MUL | NULL| | | dma_ids | varchar(255) | YES | MUL | NULL| | | national_ids | varchar(64) | YES | MUL | NULL| | | keywords | text | YES | MUL | NULL| | | tmol_modified| timestamp| YES | | NULL| | +--+--+--+-+-+---+ 12 rows in set (0.00 sec) This is the second index on lang_code, snipped from show index... +--++--+--+--+---+-+--++--++-+ | Table| Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +--++--+--+--+---+-+--++--++-+ | artists_search_A | 0 | PRIMARY |1 | artist_id| A |NULL | NULL | NULL | | BTREE | | | artists_search_A | 0 | PRIMARY |2 | lang_code| A | 14783 | NULL | NULL | | BTREE | | | artists_search_A | 1 | lang_code|1 | lang_code| A |NULL | NULL | NULL | | BTREE | | [snip] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query fast via mysql CLI, Same query slow via Perl DBI
Daniel J. Conlon wrote:
Hi,
When executing this query through the 'mysql' command line utility, the
result is returned from the database server immediately and the database
server does not create a temporary file.
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = accounts.username AND
zone_records.type = 'username'
WHERE domains.host = 1)
UNION
(SELECT
domains.domain,accounts.owner,accounts.type,accounts.server,accounts.win
server,zone_records.record,zone_records.ttl,zone_records.data,zone_recor
ds.type
FROM domains
LEFT JOIN accounts USING(username)
LEFT JOIN zone_records ON zone_records.zone = domains.domain AND
zone_records.type = 'domain'
WHERE domains.host = 1)
ORDER BY domains.domain,zone_records.type
LIMIT 871;
When executing the exact same query in a Perl script via the DBI
interface, the database server takes minutes to return the results,
using large amounts of CPU whilst computing the results and creating a
temporary file in /var/tmp.
Perhaps perl is taking up so much memory that a temporary file is needed
to hold the results.
I have turned on query logging and verified that the query logged by the
server is the same when executed with both the mysql and perl clients so
it does not seem that the mysql command line is optimising the query in
any way.
I have restarted the database server between queries to ensure that the
results are not being cached.
The two clients are connecting from the same server using the same
username and password to login.
The structure of the tables concerned is as follows:
CREATE TABLE `accounts` (
`username` varchar(20) NOT NULL default '',
`owner` varchar(20) NOT NULL default '',
`type` varchar(10) default NULL,
`server` tinyint(3) default NULL,
`winserver` tinyint(3) default NULL,
PRIMARY KEY (`username`),
KEY `owner` (`owner`),
) TYPE=InnoDB
CREATE TABLE `domains` (
`domain` varchar(255) NOT NULL default '',
`username` varchar(20) NOT NULL default '',
`host` tinyint(1) unsigned NOT NULL default '1',
PRIMARY KEY (`domain`),
KEY `username` (`username`)
) TYPE=InnoDB
CREATE TABLE `zone_records` (
`zone` varchar(255) NOT NULL default '',
`type` enum('username','domain','component','default') NOT NULL
default 'username',
`record` enum('A','MX','CNAME','PTR','NS','SOA') NOT NULL default 'A',
`ttl` smallint(5) unsigned default NULL,
`data` varchar(255) NOT NULL default '',
KEY `zone` (`zone`),
KEY `zone_type` (`zone`,`type`)
) TYPE=InnoDB
Software versions:
mysql-4.0.12-standard-log
DBI-1.30
DBD-mysql-2.1018
I am really at a loss as to what could be causing this and what to do to
correct the problem. Any assistance or advice you can offer is greatly
appretiated.
Thanks in advance.
Dan Conlon
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL Administrator certain options only valid at localhost
Hi As Greg mentioned, the options that are disabled for remote servers need access to the underlying system where the server run (file system, process control etc). There are plans for supporting those in remote servers, but that is still under development. Cheers, -- Alfredo Kojima, GUI Developer MySQL AB, www.mysql.com Buenos Aires, Argentina Want to swim with the dolphins? (April 14-16, 2004) http://www.mysql.com/uc2004/ On Fri, 13 Feb 2004 [EMAIL PROTECTED] wrote: A probable reason is that these are in startup files and hence the interface needs file system access. AFAIAA There are plans to have dynamic settings that can be changed at runtime, but these are still plans. No idea on the rest of it thou. Neat tool, and looks great! Like the backup tools. The scheduled bit of this is blank on mine so I assume that this is a bug as it should display the Local host only message. Greg -Original Message- From: Iago Sineiro [mailto:[EMAIL PROTECTED] Sent: 13 February 2004 12:10 To: MySql Mail List Subject: MySQL Administrator certain options only valid at localhost Hi. I've downloaded MySql Administrator 1.0.1a alpha for Windows and tried it. It has options only enabled if MySQL Admnistrator is connected to localhost. I'm connected as root to other host (which is Linux). For example the Startup Variables. Some of these options are the most interesting options and I want to use MySQL Administrator for connect to remote hosts. These remote hosts are Linux that could be in the same LAN but that doesn't have X server installed. What is the reason for this? In the future these options will be enabled? Iago. -- 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]
Re: FW: Installation issue on SuSE 9.0 Pro box
Just a guess but do you any space between the key and value? innodb_data_home_dir = /var/data/mysql Original Message dated 2/13/04, 1:21:26 PM Author: [EMAIL PROTECTED] Re: FW: Installation issue on SuSE 9.0 Pro box: I was curious if anyone has experienced this same error and even more curious to see if anyone might offer some advice. Thanks! Dave -Original Message- From: David Christensen [mailto:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 11:22 AM To: mysql Subject: Installation issue on SuSE 9.0 Pro box Good Morning! I'm trying to get MySQL 4.0.15 running on a new SuSE linux PC. This is my first attempt installing on SuSE after setting up a large number of Red Hat machines without problem. Right now, however, I'm continually receiving errors that I just don't understand where they are coming from. I've even copied the .cnf file from my primary MySQL server (which has been running for months) and get the errors when it's trying to fire up. Errors received are: 040211 17:31:37 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/data/mysql/' 040211 17:31:37 mysqld ended To get started, I copied the my-large.cnf to /etc as my.cnf and have edited the file to reflect the directories I'm using to store data and logs. The file I am using is as follows: (See attachment) I just don't get this one. All of the syntax seems okay, and I'm actually using the file on a Red Hat 9.0 machine. Thanks! Dave Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Safe Database Problem
Oh Jeeze- why didn't I think of removing this. When I upgraded the default is to have these two as 'Y', however, for some reason, that means that show databases shows all databases despite no permissions. That doesn't seem logical on one hand (if they don't have permissions to the database nor its tables, how could they create/lock on its tables), but on the other (they need to be able to see the databases to do a tmp table and a lock). In any case, problem fixed. I'm going to add a note to the docs to save people my troubles. Create_tmp_table_priv: Y Lock_tables_priv: Y Cheers; -M From: Victoria Reznichenko [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: Safe Database Problem Date: Fri, 13 Feb 2004 13:38:50 +0200 Mike Miller [EMAIL PROTECTED] wrote: Hi, I believe I've done everything from the book and have been fighting with the same problem for about 6 hours thus far. I just upgraded from MySQL 3.23.56 to 4.0.17. In the old system, I prevented show databases from ordinary users using skip-show-databases. In order to upgrade I used the same data directory and ran the fix_privs script as instructed to all all of the required fields to the mysql.* tables as it did. ALl of this is successful and I can see it all there when I browse these tables. I did a flush privs, reloaded the whole daemon a few times to no avail. All users besides root have been revoked (actually they never had) show databases permission, yet all users are still able to execute show databses and receive a list of hundreds of databases. SHOW VARIABLES keeps telling me that skip show database is OFF, yet I can't seem to change it with any combo of startup parameters or set commands due to it being a 'depreciated variable' in all the docs. I don't see what I'm missing. Is there a trick with the new versions which I'm not getting? Check privileges of those users. If they have such global privileges (on all databases) as SELECT, UPDATE etc. or CREATE TEMPORARY TABLES, LOCK TABLES, they also can see databases in the output of SHOW DATABASES. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [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] _ STOP MORE SPAM with the new MSN 8 and get 2 months FREE* http://join.msn.com/?page=dept/bcommpgmarket=en-caRU=http%3a%2f%2fjoin.msn.com%2f%3fpage%3dmisc%2fspecialoffers%26pgmarket%3den-ca -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: Installation issue on SuSE 9.0 Pro box
Yes, there are spaces/tabs before and after the equal sign. One interesting thing is that I uncommented the lines in the stock .cnf files for the bdb options and the error moved to the first of the bdb options. That would indicate to me that it's in the datadir line, but I can't see an error there. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 13, 2004 1:55 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: FW: Installation issue on SuSE 9.0 Pro box Just a guess but do you any space between the key and value? innodb_data_home_dir = /var/data/mysql Original Message dated 2/13/04, 1:21:26 PM Author: [EMAIL PROTECTED] Re: FW: Installation issue on SuSE 9.0 Pro box: I was curious if anyone has experienced this same error and even more curious to see if anyone might offer some advice. Thanks! Dave -Original Message- From: David Christensen [mailto:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 11:22 AM To: mysql Subject: Installation issue on SuSE 9.0 Pro box Good Morning! I'm trying to get MySQL 4.0.15 running on a new SuSE linux PC. This is my first attempt installing on SuSE after setting up a large number of Red Hat machines without problem. Right now, however, I'm continually receiving errors that I just don't understand where they are coming from. I've even copied the .cnf file from my primary MySQL server (which has been running for months) and get the errors when it's trying to fire up. Errors received are: 040211 17:31:37 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/data/mysql/' 040211 17:31:37 mysqld ended To get started, I copied the my-large.cnf to /etc as my.cnf and have edited the file to reflect the directories I'm using to store data and logs. The file I am using is as follows: (See attachment) I just don't get this one. All of the syntax seems okay, and I'm actually using the file on a Red Hat 9.0 machine. Thanks! Dave Christensen
mysql++ gcc 3.3.1 patch
Please for ward to the appropriate party. I have attached a gcc 3.3 patch for MySQL++ 1.7.9 which may be applied following the gcc 3.0, 3.2, and 3.2.2 patches provided on the MySQL website. I have only used this patch on Mandrake 9.2, which comes standard with gcc 3.3.1. If you have questions, feel free to contact me. Thank you, Robert Oldham CTO Rhino Maeo mysql++-gcc-3.3.patch.gz Description: GNU Zip compressed data signature.asc Description: This is a digitally signed message part
Re: MySQL 4.0.18 has been released
In the last episode (Feb 13), John Griffin said:
Can anyone tell where to find documentation on myisam_ftdump? I would
like to know what myisam_ftdump is and how it differs from mysqldump.
It is a tool to dump the contents of a fulltext index. Mainly useful
for debugging.
Use: ft_dump table_name index_no
-d, --dump Dump index (incl. data offsets and word weights)
-s, --stats Report global stats
-v, --verbose Be verbose
-c, --count Calculate per-word stats (counts and global weights)
-l, --lengthReport length distribution
-e, --execute=name Execute given query
-h, --help Display help and exit
-?, --help Synonym for -h
Variables (--variable-name=value)
and boolean options {FALSE|TRUE} Value (after reading options)
- -
verbose FALSE
execute (No default value)
--
Dan Nelson
[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
order by: more that one field
Have some questions about Order By: Can a mysql query be order by more than one field? If this two queries will give different result or not? #1 select * from t1 Order by id, username; #2 select * from t2 Order by id _ Choose now from 4 levels of MSN Hotmail Extra Storage - no more account overload! http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: FW: Installation issue on SuSE 9.0 Pro box
Would you mind posting your cnf. This may be a syntactical error. Original Message On 2/13/04, 2:08:40 PM, [EMAIL PROTECTED] wrote regarding RE: FW: Installation issue on SuSE 9.0 Pro box: Yes, there are spaces/tabs before and after the equal sign. One interesting thing is that I uncommented the lines in the stock .cnf files for the bdb options and the error moved to the first of the bdb options. That would indicate to me that it's in the datadir line, but I can't see an error there. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Friday, February 13, 2004 1:55 PM To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Subject: Re: FW: Installation issue on SuSE 9.0 Pro box Just a guess but do you any space between the key and value? innodb_data_home_dir = /var/data/mysql Original Message dated 2/13/04, 1:21:26 PM Author: [EMAIL PROTECTED] Re: FW: Installation issue on SuSE 9.0 Pro box: I was curious if anyone has experienced this same error and even more curious to see if anyone might offer some advice. Thanks! Dave -Original Message- From: David Christensen [mailto:[EMAIL PROTECTED] Sent: Thursday, February 12, 2004 11:22 AM To: mysql Subject: Installation issue on SuSE 9.0 Pro box Good Morning! I'm trying to get MySQL 4.0.15 running on a new SuSE linux PC. This is my first attempt installing on SuSE after setting up a large number of Red Hat machines without problem. Right now, however, I'm continually receiving errors that I just don't understand where they are coming from. I've even copied the .cnf file from my primary MySQL server (which has been running for months) and get the errors when it's trying to fire up. Errors received are: 040211 17:31:37 mysqld started /usr/sbin/mysqld: ERROR: unknown variable 'innodb_data_home_dir=/var/data/mysql/' 040211 17:31:37 mysqld ended To get started, I copied the my-large.cnf to /etc as my.cnf and have edited the file to reflect the directories I'm using to store data and logs. The file I am using is as follows: (See attachment) I just don't get this one. All of the syntax seems okay, and I'm actually using the file on a Red Hat 9.0 machine. Thanks! Dave Christensen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ft_boolean_default
Is this a legitimate variable? It seems like it'd be useful, but when I tried it (on 4.0.12-nt and 4.0.17-nt) it didn't work. Anybody know what's going on? Brad [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 crashed; Please help
[EMAIL PROTECTED] wrote: Did you perform a stack trace? No, how do I do that? /Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: order by: more that one field
Mike Mapsnac said: Have some questions about Order By: Can a mysql query be order by more than one field? If this two queries will give different result or not? #1 select * from t1 Order by id, username; #2 select * from t2 Order by id _ Choose now from 4 levels of MSN Hotmail Extra Storage - no more account overload! http://click.atdmt.com/AVE/go/onm00200362ave/direct/01/ yes. William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 4.0 crashed; Please help
Here are the instructions for doing a stack trace. http://www.mysql.com/doc/en/Using_stack_trace.html Original Message On 2/13/04, 2:24:30 PM, Jacob Friis Larsen (Eksperten admin) [EMAIL PROTECTED] wrote regarding Re: MySQL 4.0 crashed; Please help: [EMAIL PROTECTED] wrote: Did you perform a stack trace? No, how do I do that? /Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SSL connection error
Description: I'm using Mysql with SSL. It has been working perfectly until today and now I have an ERROR 2026: SSL connection error when I try to connect to Mysql. I do not understand why, my SSL certificat is valid until 8 jan 2005. What does this error mean exactly ? Does anyone experiment this error ? Thanks by advance ! How-To-Repeat: none Fix: none Submitter-Id: Frederic Deshaies Originator: Organization: MySQL support: [none | licence | email support | extended email support ] Synopsis: SSL connection error Severity: serious Priority: medium Category: mysql Class: support Release: mysql-4.0.17 (Source distribution) Server: ./mysqladmin Ver 8.40 Distrib 4.0.17, for sun-solaris2.8 on sparc Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL license Server version 4.0.17 Protocol version10 Connection Localhost via UNIX socket UNIX socket /tmp/mysql.sock Uptime: 32 days 6 hours 2 min 52 sec Threads: 1 Questions: 3576 Slow queries: 0 Opens: 155 Flush tables: 1 Open tables: 10 Queries per second avg: 0.001 C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: SunOS sgdt-ci 5.8 Generic_108528-11 sun4u sparc SUNW,Ultra-4 Architecture: sun4 Some paths: /Produits/publics/sparc.SunOS.5.8/bin/perl /usr/ccs/bin/make /Produits/publics/sparc.SunOS.5.8/bin/gmake /Produits/publics/sparc.SunOS.5.7/gcc/2.95.2/bin/gcc /usr/ucb/cc GCC: Reading specs from /Produits/publics/sparc.SunOS.5.7/gcc/2.95.2/lib/gcc-lib/sparc-sun-solaris2.7/2.95.2/specs gcc version 2.95.2 19991024 (release) Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='ccache gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' ASFLAGS='' LIBC: -rw-r--r-- 1 root bin 1760276 Jul 27 2001 /lib/libc.a lrwxrwxrwx 1 root root 11 Nov 6 2001 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136744 Jul 27 2001 /lib/libc.so.1 -rw-r--r-- 1 root bin 1760276 Jul 27 2001 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Nov 6 2001 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136744 Jul 27 2001 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=complex' '--enable-thread-safe-client' '--with-innodb' '--with-berkeley-db' '--with-embedded-server' '--with-openssl' '--with-vio' '--enable-local-infile' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=ccache gcc' Perl: This is perl, version 5.005_03 built for sun4-solaris-thread -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Default UTF-8 Encoding
Hello- Is there a way to change the default mysql encoding to be something else, say UTF-16LE at the session level? As always, thanks! __ David Perron Sales Professional Services Consultant P: 212-624-9600 ext. 221 C: 917-678-2081
Re: InnoDB Hot Backup + MySQL embedded?
Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, February 12, 2004 2:26 PM Subject: Re: InnoDB Hot Backup + MySQL embedded? Dear Heikki, Thanks for the quick response! It never ceases to amaze me that such compartively small teams at Innobase Oy and MySQL AB produce such incredibly high-quality software. thank you for the praise :). Being a final-year Software Engineering student, I'm curious as to what you consider the most difficult problem to solve in building InnoDB and ibbackup has been. Of technical matters, multiversioning (consistent read) has been the most difficult to get working reliably. I wrote it probably around 1996, and still last year a bug was found in it. Adaptive hash indexes and the insert buffer have also been difficult to debug. The reason is obvious: in these 3 things parallel execution of threads as well as background cleanup operations complicate things. One-threaded, deterministic processing is much easier to debug than multithreaded nondeterministic execution. Given the fact that you set your goals extremely high for InnoDB (and have acheived them quite handily), I want to ensure that if anything remotely similar comes up in my final-year project that I either put my hand up for it and take on the challenge or run screaming in the other direction and have a somewhat easier year. In academic circles it is best not to put too much emphasis on programming work :). Referees tend to favor papers written in plain English, and want algorithms in pseudocode. They are not willing to dive into the full complexity of a real-world implementation. There has been quite a lot of debate and criticism around this gap between typical university research and practical applications, but things will probably not change in the next 2500 years. Regards, Chris Best regards, Heikki Heikki Tuuri wrote: Chris, - Original Message - From: Chris Nolan [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, February 12, 2004 2:45 AM Subject: InnoDB Hot Backup + MySQL embedded? Hi all, I'm looking at developing an (open source) server-style application with the embedded MySQL library to be employed as the primary data store. Has anyone attempted to use InnoDB Hot Backup for such a beast? I ask as I do not know whether communication between ibbackup and the database engine is required (thus requiring a few changes to the embedded lib before compilation). A quick scan through the docs involved doesn't seem to point me in the right direction for a definitive answer on this. ibbackup can be used with the MySQL Embedded Server Library, no problem in that. ibbackup does not communicate with the mysqld server at all. It just reads the InnoDB data and log files. On the other hand, the innobackup Perl script does start a mysql connection to the server. Regards, Chris Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Connector/J 3.1.1 ALPHA Has Been Released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, MySQL Connector/J 3.1.1, a new version of the Type-IV all-Java JDBC driver for MySQL has been released. Version 3.1.1 is a new feature release for the ALPHA tree that is suitable for use with either MySQL-4.1 or MySQL-5.0. It is now available in source and binary form from the Connector/J download pages at http://www.mysql.com/downloads/api-jdbc-stable.html and mirror sites (note that not all mirror sites may be up to date at this point of time - if you can't find this version on some mirror, please try again later or choose another download site.) The major new feature of this release aside from performance optimizations and bug fixes from 3.1.0 is support for the stored procedures in MySQL-5.0 via the java.sql.CallableStatement interface, and the inclusion of the docbook-based documentation that has been available on www.mysql.com for awhile. The new documentation is located in the 'doc' subdirectory of the distribution. For those of you who want to try out the stored procedures support, I suggest that you see the documentation (http://www.mysql.com/documentation/connector-j/index.html#id2803184) as well as the JDBC spec and APIDOCs for java.sql.CallableStatement. You might also be interested in hearing me talk about the new features in Connector/J 3.1 as well as other Java and MySQL-related topics in person at the MySQL Users' Conference, which will be held in Orlando, April 14th-16th, 2004 (see http://www.mysql.com/events/uc2004/ for more information). Have fun with this release, and looking forward to meeting you in Orlando if you can make it! -Mark - From the changelog: 02-14-04 - Version 3.1.1-alpha - Fixed bug with UpdatableResultSets not using client-side prepared statements. - Fixed character encoding issues when converting bytes to ASCII when MySQL doesn't provide the character set, and the JVM is set to a multibyte encoding (usually affecting retrieval of numeric values). - Unpack 'unknown' data types from server prepared statements as Strings. - Implemented long data (Blobs, Clobs, InputStreams, Readers) for server prepared statements. - Implemented Statement.getWarnings() for MySQL-4.1 and newer (using 'SHOW WARNINGS'). - Default result set type changed to TYPE_FORWARD_ONLY (JDBC compliance). - Centralized setting of result set type and concurrency. - Re-factored how connection properties are set and exposed as DriverPropertyInfo as well as Connection and DataSource properties. - Support for NIO. Use 'useNIO=true' on platforms that support NIO. - Support for SAVEPOINTs (MySQL = 4.0.14 or 4.1.1). - Support for mysql_change_user()...See the changeUser() method in com.mysql.jdbc.Connection. - Reduced number of methods called in average query to be more efficient. - Prepared Statements will be re-prepared on auto-reconnect. Any errors encountered are postponed until first attempt to re-execute the re-prepared statement. - Ensure that warnings are cleared before executing queries on prepared statements, as-per JDBC spec (now that we support warnings). - Support 'old' profileSql capitalization in ConnectionProperties. This property is deprecated, you should use 'profileSQL' if possible. - Optimized Buffer.readLenByteArray() to return shared empty byte array when length is 0. - Allow contents of PreparedStatement.setBlob() to be retained between calls to .execute*(). - Deal with 0-length tokens in EscapeProcessor (caused by callable statement escape syntax). - Check for closed connection on delete/update/insert row operations in UpdatableResultSet. - Fix support for table aliases when checking for all primary keys in UpdatableResultSet. - Removed useFastDates connection property. - Correctly initialize datasource properties from JNDI Refs, including explicitly specified URLs. - DatabaseMetaData now reports supportsStoredProcedures() for MySQL versions = 5.0.0 - Fixed stack overflow in Connection.prepareCall() (bad merge). - Fixed IllegalAccessError to Calendar.getTimeInMillis() in DateTimeValue (for JDK 1.4). - Fix for BUG#1673, where DatabaseMetaData.getColumns() is not returning correct column ordinal info for non '%' column name patterns. - Merged fix of datatype mapping from MySQL type 'FLOAT' to java.sql.Types.REAL from 3.0 branch. - Detect collation of column for RSMD.isCaseSensitive(). - Fixed sending of queries 16M. - Added named and indexed input/output parameter support to CallableStatement. MySQL-5.0.x or newer. - Fixed NullPointerException in ServerPreparedStatement.setTimestamp(), as well as year and month descrepencies in ServerPreparedStatement.setTimestamp(),
Re: HOTBACKUP
Arnoldus, you should add log-bin to the [mysqld] section of my.cnf, so that mysqld writes the binlog. Please see the MySQL online manual about this option. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Arnoldus Th.J. Koeleman [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, February 13, 2004 8:34 PM Subject: HOTBACKUP --=_NextPart_000_0041_01C3F235.BEE2A9E0 Content-Type: text/plain; charset=us-ascii Content-Transfer-Encoding: 7bit I was reading the manual for Hot Backup for InnoDB and i was missing one fundamental thing in the doc and that is setting the log-bin The document only speaks about The my.cnf files must contain the following parameter values: innodb_data_home_dir=... innodb_data_file_path=... innodb_log_group_home_dir=... innodb_log_files_in_group=... innodb_log_file_size=... And for recovering purpose log-bin in the [mysqld] section am I correct for POINT IN TIME RECOVERING --=_NextPart_000_0041_01C3F235.BEE2A9E0-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
options file /root/.my.cnf ignored ???
I have inherited several mysql v4x servers. All but one of them works as expected with /root/.my.cnf files. That last server is driving me nuts ; I have RTFM, and I do not find this particular problem anywhere. Pointers to TFM are welcome. # mysql -h localhost -u root ERROR 1045: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) # sudo cat /root/.my.cnf [client] user = root password = This works OK: # mysql -h localhost -u root -p Enter password: Yes, I do know that password, and the one in /root/.my.cnf is the same, and I have changed the password for root with the same results. My own user, mds, works as expected via options file ~/.my.cnf . What am I missing? -- Best Regards, mds mds resource 877.596.8237 - Dare to fix things before they break . . . - Our capacity for understanding is inversely proportional to how much we think we know. The more I know, the more I know I don't know . . . -- signature.asc Description: Digital signature
Newbie Question
I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I amusing MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to createa pair ofInnoDB tables that are related to one
another via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--
use Sample;
drop table if exists dept;create table dept(deptno char(3) not
null,deptname varchar(36) not null,mgrno char(6),primary
key(deptno))Type=InnoDB;
drop table if exists emp;create table emp(empno char(6) not
null,firstnme char(12) not null,midinit char(1),lastname char(15)
not null,workdept char(3) not null,salary dec(9,2) not null,primary
key(empno),index(workdept),foreign key(workdept) references dept(deptno)
on delete restrict)Type=InnoDB;
insert into dept values('A00', 'Administration', '10'),('D11',
'Manufacturing', '20'),('E21', 'Education', '30');
insert into emp values('10', 'Christine', 'I',
'Haas','A00',5.00),('20', 'Cliff', ' ', 'Jones', 'D11',
3.00),('30', 'FK', ' ', 'Mistake', 'X99', 12345.67),('40',
'Brad', ' ', 'Dean', 'E21', 35000.00);
---
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type "MyISAM", *not* "InnoDB". If my
tables really are "MyISAM", then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at least as I
understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino---rhino1 AT sympatico DOT ca"If you want the best
seat in the house, you'll have to move the cat."
Re: Newbie Question
might be as simple as putting a space after your closing parenthesis on
the create table statement.
either that, or your mysql install somehow doesn't have innodb table
support. have you edited your my.cnf file and enabled the innodb
parameters, specifically log and data files?
Rhino wrote:
I'm new to MySQL but I have extensive experience with DB2 so I'm getting
quite confused about how MySQL is supposed to work.
I am using MySQL 4.0.11 on a Linux server running RedHat 9.2. I am
trying to create a pair of InnoDB tables that are related to one another
via a foreign key. I created the tables successfully but when I try to
insert a row into the child table that violates the foreign key, MySQL
loads the bad row, even though the foreign key doesn't exist!
Here is the script I used to create and populate the tables:
--
use Sample;
drop table if exists dept;
create table dept(
deptno char(3) not null,
deptname varchar(36) not null,
mgrno char(6),
primary key(deptno)
)Type=InnoDB;
drop table if exists emp;
create table emp(
empno char(6) not null,
firstnme char(12) not null,
midinit char(1),
lastname char(15) not null,
workdept char(3) not null,
salary dec(9,2) not null,
primary key(empno),
index(workdept),
foreign key(workdept) references dept(deptno) on delete restrict
)Type=InnoDB;
insert into dept values
('A00', 'Administration', '10'),
('D11', 'Manufacturing', '20'),
('E21', 'Education', '30');
insert into emp values
('10', 'Christine', 'I', 'Haas','A00',5.00),
('20', 'Cliff', ' ', 'Jones', 'D11', 3.00),
('30', 'FK', ' ', 'Mistake', 'X99', 12345.67),
('40', 'Brad', ' ', 'Dean', 'E21', 35000.00);
---
I got a very big clue when I ran this command:
show table status from Sample;
It showed that my two tables were type MyISAM, *not* InnoDB. If my
tables really are MyISAM, then I'm not surprised that the foreign key
constraint doesn't work since MyISAM doesn't support foreign keys, at
least as I understand the manual.
However, this doesn't answer the big question: *Why* aren't my tables
InnoDB since I explicitly defined them that way??
Can any MySQL veterans clear up this mystery for me?
Rhino
---
rhino1 AT sympatico DOT ca
If you want the best seat in the house, you'll have to move the cat.
--
Jeff Mathis, Ph.D. 505-955-1434
The Prediction Company [EMAIL PROTECTED]
525 Camino de los Marquez, Ste 6http://www.predict.com
Santa Fe, NM 87505
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binary Logs and Transactions (with InnoDB and MyISAM)
From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? How does this work with MyISAM? Thanks, David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Modifying Perl script to write to MySQL?
The Perl script I use is currently writing the
form results ($guestbookreal) to an html page
(http://usedflutes.com/new_listings_publish.html):
For example,
INSERT INTO `mysql_db` (`Title`, `Email`, `City`,
`State`, `Country`, `URL`, `Date`, `Description`,
`rid`, `dt_create`, `publish` ) VALUES ('Test',
'[EMAIL PROTECTED]', 'Sebastopol', 'Ca', 'USA ',
'http://bobafifi.com', 'Friday, February 13, 2004
', 'Test', '', 'NOW()', 0)
Can somebody please tell me how I need to change
the following code so that it will write the
results directly to MySQL instead??
$guestbookreal =
/home/flute/usedflutes-www/new_listings_publish.html;
Many thanks in advance,
-Bob
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
about bug 2654
Hi Mark et al, do you have any clue of when this bug is going to be fixed ? i saw at the bug system that it is a non-critical low priority bug, but i think this is major issue that most people using at least java mysql require for some applications. Bug #2654 JDBC Exception: Column 'column.table' not found when order by in query thanks in advance Carlos _ Add photos to your messages with MSN 8. Get 2 months FREE*. http://join.msn.com/?page=features/featuredemail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modifying Perl script to write to MySQL?
Wow! Thanks Rhino.
Yes, I already have http://www.usedflutes.com
running on MySQL (the INSERT statements I just
post manually using phpMyAdmin and they load fine
that way). However, I'd like to bypass the copy
paste step for entering ads and if possible,
have them go/execute directly into mysql instead
by modifying the current html send of the script.
Thanks again
-Bob
--- Rhino [EMAIL PROTECTED] wrote:
Bob,
I assume that you want to put the data from
your Insert statement into a
MySQL table, as opposed to inserting the text
of your Insert statement into
a table.
If I am right, then you'll need to do a few
things:
a) Create a database using the Create Database
statement.
b) Define a table that has the appropriate
columns and datatypes. You
already know what your columns need to be -
Title, Email, City, etc. - but
you need to choose an appropriate datatype and
size for each of these
columns. Use the Create Table statement to
create the empty table.
c) Write the Perl code to insert your data into
the table. I'm not at all
clear on how you propose to get the data. I'm
guessing that you will present
your users with an HTML input form and let them
fill in each of the values
for the row that you will create. If that is
the case, your Perl code will
have to display a form with input fields and
those fields will have to be
big enough for the largest value that you will
put in the column. For
example, if the CITY can never be more than 20
characters long, you'll need
your input field to be 20 characters long as
well. Your Perl code should
also validate the user's input. For example, if
someone leaves the CITY
blank or puts a phone number in that field,
your code should detect that and
display an error message to the user telling
them what's wrong and asking
them to try again. When the data is all valid,
you need to insert the data
into the table.
I haven't done very much Perl and what I did do
was 5 years ago. However,
this short example should get you going in the
right direction. It assumes
that a table already exists and has this
definition:
create table mytab
(id smallint not null,
name char(10) not null,
primary key(id))
#!/usr/local/bin/perl
###
# This example demonstrates:
# - making a connection
# - setting the DBI debug level
# - inserting a row of data
# - disconnecting
###
use DBI;
use DBD::DB2::Constants;
$DBI::dbi_debug=3; # increase the debug output
# Attempt to connect to the database.
$dbh =
DBI-connect(dbi:DB2:sample,logonID,passwd,
{RaiseError=0,AutoCommit=0});
# Commit so that rows can be added.
$dbh-commit();
# Populate the table.
$insert = insert into mytab values
(1000, 'Fred'),
(2000, 'Barney');
$sqlerrd3 = $dbh-do($insert);
print Inserted , $sqlerrd3, rows\n;
# Display the table contents.
display;
# Commit.
$dbh-commit();
# Reset the connection i.e. disconnect.
$dbh-disconnect();
exit;
sub display {
# Specify the info we want.
$stmt1 = select id, name
from mytab
order by id;
# Prepare the statement.
$sth = $dbh-prepare($stmt1);
# Execute the statement.
$sth-execute();
# Loop through the result set, parsing each
value into its own field.
while ( ( $id, $name ) = $sth-fetchrow() ) {
print ID: $id\tName: $name\n;
}
# Close the result set to free up resources.
$sth-finish();
}
Rhino
- Original Message -
From: Bob Afifi [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Friday, February 13, 2004 8:00 PM
Subject: Modifying Perl script to write to
MySQL?
The Perl script I use is currently writing
the
form results ($guestbookreal) to an html page
(http://usedflutes.com/new_listings_publish.html):
For example,
INSERT INTO `mysql_db` (`Title`, `Email`,
`City`,
`State`, `Country`, `URL`, `Date`,
`Description`,
`rid`, `dt_create`, `publish` ) VALUES
('Test',
'[EMAIL PROTECTED]', 'Sebastopol', 'Ca',
'USA ',
'http://bobafifi.com', 'Friday, February 13,
2004
', 'Test', '', 'NOW()', 0)
Can somebody please tell me how I need to
change
the following code so that it will write the
results directly to MySQL instead??
$guestbookreal =
/home/flute/usedflutes-www/new_listings_publish.html;
Many thanks in advance,
-Bob
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by
filing online.
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives:
http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing
Re: Binary Logs and Transactions (with InnoDB and MyISAM)
Thanks for the reply. So InnoDB (and even MyISAM) use transactions (expected with InnoDB) and slaves track their position in the binary log files as they are reading them in so as not to violate any constraints? David - Original Message - From: Chris Nolan [EMAIL PROTECTED] To: David Griffiths [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Friday, February 13, 2004 5:46 PM Subject: Re: Binary Logs and Transactions (with InnoDB and MyISAM) Hi David, David Griffiths wrote: From reading the docs, a binary log is an efficient representation of all data-modifying SQL that is run on the master database. I was unable to figure out what happens if a slave is interrupted while in the middle of processing a binary log. When a binary log is applied to a slave database, what happens if the machine or database dies half way through the log? Basically, the slave will try to catch up when it restarts. For example, with InnoDB, say the following statements are run and stored in the binary log: --- 1) INSERT INTO table_a (column_a, column_b, ...) VALUES (...); 2) COMMIT; 3) UPDATE table_a SET column_b = 'some_value' WHERE column_c = 'something_else'; 4) COMMIT; - The slave-machine (also using InnoDB tables) start processing the binary log. Statement 1 and 2 are processed, but it dies before Statement 3 (UPDATE table_a...) is executed. If I recall correctly, the binary log uses transactions as it's basic units. I'm not even sure if the slave will see statement 3 before statement 4, but I know it definitely will not act on it in any way. If you restart the slave, would it start the binary log back at the beginning, or is the offset inside the file stored in the database so that the slave database starts at Statement 3? Or would the whole log be re-processed (potentially causing problems with inserting rows with unique keys)? The slave won't try to reperform actions that are already processed. You can relax regarding unique attributes. How does this work with MyISAM? In essentially the same way. As each statement is basically bound with BEGIN and COMMIT statements, each statement is processed by the slave after it successfully completes on the master. Thanks, David Regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: about bug 2654
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Carlos Proal wrote: Hi Mark et al, do you have any clue of when this bug is going to be fixed ? i saw at the bug system that it is a non-critical low priority bug, but i think this is major issue that most people using at least java mysql require for some applications. Bug #2654 JDBC Exception: Column 'column.table' not found when order by in query Carlos, It's assigned to a developer currently, so that means it should be fixed soon. You could always add a comment that you've experienced too, and you will automatically be notified when progress is made. Regards, -Mark - -- Mr. Mark Matthews MySQL AB, Software Development Manager, J2EE and Windows Platforms Office: +1 708 332 0507 www.mysql.com Meet the MySQL Team! April 14-16, 2004 http://www.mysql.com/uc2004/ -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (MingW32) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFALYeAtvXNTca6JD8RAssmAKC/PShLXMsAuzZPuPeLnUK3fQJILQCghZNg L1cUVfycf91v5sZR+teHU4s= =B+ou -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Modifying Perl script to write to MySQL?
Just to clarify a bit more (perhaps...), I
already have:
#!/usr/bin/perl
use DBI;
=
and
=
$dbh = DBI-connect
(DBI:mysql:host=localhost;database=my_db,
my_id, my_password,
{PrintError = 0, RaiseError = 1});
=
preceding the
$guestbookreal =
/home/flute/usedflutes-www/new_listings_publish.html;
part of the script. Since the script is already
generating an INSERT statement (at least one I
can copy paste the results into the
phpMyAdmin interface) that works, I'm wondering
how (or if) it's possible to change the
/home/flute/usedflutes-www/new_listings_publish.html;
part of the code so that the INSERT statement
will execute instead?
Thanks again,
-Bob
__
Do you Yahoo!?
Yahoo! Finance: Get your refund fast by filing online.
http://taxes.yahoo.com/filing.html
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Values for inno_db_data_file_path
Hi, My DB server has 2 GB RAM, and 5 x 36 GB HDD in RAID 5. How can I tune inno_db_data_file_path for best performance? Is it better to split up the data, or just use 1 very big autoextend data? Thank you, Batara -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL Server - shutdown
Hello: We are using MySQL database on a FreeBSD server. The problem we are facing is sometimes the MySQL server is automatically shutting down. There is no error logged on the server. The entry in the log file mysite.com.err shows: 040213 11:28:59 /usr/local/mysql-3.23.49/libexec/mysqld: Normal shutdown 040213 11:29:04 /usr/local/mysql-3.23.49/libexec/mysqld: Shutdown Complete This happened more than once in the last 2 months. Can anybody help me to fix this problem on the server. Sheni R Meledath [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User Account errors on new installation
I have MySQL ver. 4.0.18 binarys for windows installation. I just finished installing mysqld-max-nt as a service on a WinXP box yesterday afternoon. Using the MySQL Administrator client/utility GUI, I configured the anonymous and root Users, then added a superuser level account for myself. I have DHCP enabled on our LAN, with the DHCP server enabled on the cable router. I created localhost and 1 workstation connections for my account. Next I tested the localhost login for my account and was successful. When testing the workstation connection however, I get a 1045 error: access denied for [EMAIL PROTECTED] name] message when using the password I created, in the Administrator, for the workstation connection. When I login from the workstation via MySQL Control Center without the use of a password, the connection is successful. I shows the connection exists when I check the server connection status window of the Administrator. In checking the status window in the control center on the workstation, it reports the connection as connected=no. What do I need to do with this? I havea password set for the account, but when I use it from the workstation that 1045 code indicates I'm using the wrong password, yet it works fine from the localhost connection. IncrediMail - Email has finally evolved - Click Here
