Re: TINYINT(1) vs ENUM?
On Mon, Nov 24, 2003 at 02:49:12PM -0700, Jeff Mathis wrote:
: maybe one more particle of information is that enums are actually
: strings, not numbers, so you may have to do a conversion somewhere. this
: is a pain for us, so we use tiny ints.
The bigger problem I've had with ENUMs as strings is that if you
insert a value that isn't one of your ENUM values (say someone
inserts a '2' instead of a '0' or '1', a blank string ('') is
entered instead. That's no better than someone actually entering
a '2'.
MySQL, SQL, query, etc.
* Philip Molter
* Texas.Net Internet
* http://www.texas.net/
* [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Cannot create InnoDB table
I have a situation using InnoDB where I cannot create even the most basic of tables with a given name. mysql create table card_batch ( a int ); ERROR 1005: Can't create table './test_gn/card_batch.frm' (errno: 121) mysql show tables like 'card%'; Empty set (0.01 sec) mysql show variables like 'version'; +---+-+ | Variable_name | Value | +---+-+ | version | 4.0.15a-log | +---+-+ 1 row in set (0.00 sec) In another database on the same server, using the same InnoDB files, I can create the table just fine. Why does it keep telling me that it cannot create the table because of Duplicate key on write or update? The MySQL version os 4.0.15a. The operating system is Solaris 9. Philip * Philip Molter * Texas.Net Internet * http://www.texas.net/ * [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 3.23.56 Memory Usage Problem
On Mon, Aug 18, 2003 at 12:10:16PM -0400, Tom Mattison wrote: : Greetings List, : : I'm running a dedicated MySQL server on a Dell PE 2550, dual proc. 4GB : RAM. Its using the Dell PERC 3/Di Scsi RAID controller with 18GB disks. I : running RH 7.3 with the latest updates(Kernel 2.4.20-19.7smp). The MySQL : version is the rpm currently being provided by the RH updates. I have been : trying to tune the MySql daemon for a week now to prevent swapping. No : matter what I do, I still end up with about 60GB of used swap space. : : Any suggestions on what I'm doing wrong? TIA for any suggestions/help! That's not MySQL causing the problem, that's Linux's memory management. Of course, in your data below, you have 60MB of used swap, not 60GB. If you want to track the issue with Redhat, check out bug 89226 on their bugzilla website. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB Assertion Error
THE SITUATION: We're running MySQL 3.23.51. We have a table which has a primary-keyed field 'sid'. We have four tables what have foreign key references on that field. We deleted all rows from those four tables that had values in that foreign key index and then tried to delete the row in the main table. The main table delete failed with a parent row reference error. Fine, MySQL/InnoDB have gone through a few revisions and perhaps this bug is fixed. Certainly, running this old version isn't going to help us. THE PROBLEM: After downloading and compiling MySQL 3.23.55 (we use the compile flags from MySQL's web site, but we compiled our own), we switched our config over to the new compilation (same data, same config, etc. just a new binary) and started up. On a table scan (SHOW TABLE STATUS, `mysql` without -A, etc.), MySQL crashes with an assertion: 030307 21:08:02 mysqld restarted 030307 21:08:03 InnoDB: Started /usr/local/mysql-3.23.55/libexec/mysqld: ready for connections 030307 21:08:21 InnoDB: Assertion failure in thread 15 in file dict0load.c line 677 InnoDB: We intentionally generate a memory trap. InnoDB: Send a detailed bug report to [EMAIL PROTECTED] 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=33550336 record_buffer=131072 sort_buffer=16777208 max_used_connections=3 max_connections=500 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (record_buffer + sort_buffer)*max_connections = 4094456 K bytes of memory Hope that's ok, if not, decrease some variables in the equation 030307 21:08:21 mysqld restarted In the MySQL client it looks like this: mysql use tx; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Didn't find any fields in table 'task' Didn't find any fields in table 'taxbase' Didn't find any fields in table 'terms' Didn't find any fields in table 'ticket' Didn't find any fields in table 'ticket_master' Didn't find any fields in table 'ticket_perm_bits' Didn't find any fields in table 'traffic_service_history' Didn't find any fields in table 'uid' Database changed If it's reading the tables in order, it's having a problem with one of the following tables: CREATE TABLE sync_times ( system varchar(48) NOT NULL default '', host varchar(255) NOT NULL default '', tstamp datetime default NULL, PRIMARY KEY (system,host) ) TYPE=InnoDB; CREATE TABLE task ( task int(11) NOT NULL auto_increment, flags varchar(255) default NULL, depends int(11) default NULL, type varchar(40) default NULL, name varchar(100) default NULL, description text, department varchar(20) default NULL, owner varchar(20) default NULL, customer int(11) default NULL, contact int(11) default NULL, origin varchar(255) default NULL, status varchar(20) default NULL, assigned varchar(20) default NULL, entry_time datetime default NULL, due_time datetime default NULL, finish_time datetime default NULL, tstamp timestamp(14) NOT NULL, PRIMARY KEY (task), KEY _task_ (task), KEY _customer_ (customer), KEY _contact_ (contact), KEY _depends_ (depends), KEY _name_ (name), FOREIGN KEY (`depends`) REFERENCES `tx.task` (`task`), FOREIGN KEY (`customer`) REFERENCES `tx.customer` (`customer`), FOREIGN KEY (`contact`) REFERENCES `tx.contact` (`contact_id`) ) TYPE=InnoDB; Backing out to the old version works fine. The only glaringly obvious thing I see there is a table putting a foreign key onto itself, but that should be allowed, no? Thanks for any assistance. Philip * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Transaction History Logging
MySQL, SQL, Query ... Does anyone know of a transaction history utility for MySQL? What I am looking for is something that logs a before/after entry anytime a change occurs in a MySQL table. I want to be able to say definitively these changes occurred at these times. There are, of course, two current ways to do this: a) Do it in userland code The obvious caveat is that you don't catch changes made at the MySQL command prompt or by anything that doesn't connect through your userland code. b) Use binlogging, rollback your log to the specified point in time, and then compare the databases before and after and log The caveat there is that it's incredibly resource intensive, and AFIAK, there is no way to easily diff a database Has anyone maybe written an extension to MySQL to support this? I realize that a system running such an extension would probably be much busier, but for some things, the binlog just doesn't contain enough information to logically say, Okay, this is the change that was made and this was what was changed as a result. Thanks, Philip * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Disable autocommit by default
I want to disable autocommit for all command-line client connections by default (through the mysql prompt). I believe I can do this in the [client] section of the my.cnf config file, but I have no clue how. Three questions: 1) How? 2) Will it affect other client processes, such as those spawned by perl through DBD::mysql? 3) Is there a list of all the possible config options for the my.cnf file somewhere? Thanks, Philip sql, mysql * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Error compiling 3.23.53
Compiling on Solaris 8 x86 - mysql 3.23.53 - gcc version 2.95.2 19991024 (release) CC=gcc CFLAGS=-O3 -fno-omit-frame-pointer CXX=gcc CXXFLAGS=-O3 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti ./configure --prefix=/usr/local/mysql-3.23.53 --with-extra-charsets=complex --enable-safe-client --enable-local-infile --enable-assembler --with-innodb --without-readline I get the error: Making all in strings make[2]: Entering directory `/tmp/mysql-3.23.53/strings' gcc -c -o strings-x86.o strings-x86.s Assembler: strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Syntax error strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 1 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Syntax error strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Syntax error strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 2 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Syntax error strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic strings-x86.s, line 3 : Illegal mnemonic Too many errors - Goodbye make[2]: *** [strings-x86.o] Error 1 make[2]: Leaving directory `/tmp/mysql-3.23.53/strings' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/tmp/mysql-3.23.53' make: *** [all-recursive-am] Error 2 sql, query * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
More Detailed EXPLAIN
If you just reply to this message, and include the entire text of it in the reply, your reply will go through. However, you should first review the text of the message to make sure it has something to do with MySQL. Just typing the word MySQL once will be sufficient, for example. You have written the following: Is there a way to get a more detailed explain statement? If not, maybe someone can answer this for me. Say I have a statement like this: EXPLAIN SELECT /*! STRAIGHT_JOIN */ t1.* FROM table2 t2 INNER JOIN table1 t1 /*! USE INDEX ( field1 ) */ ON t1.field1=t2.field1 AND t2.field2 IS NOT NULL INNER JOIN table3 t3 ON t1.field2=t3.field2 AND t3.field1=1 WHERE MOD(t1.field1,5)=3 AND t1.field3=0 AND t1.field4NOW(); This produces an explain of +---++-+-+-+---+--+-+ | table | type | possible_keys | key | key_len | ref | rows || Extra | +---++-+-+-+---+--+-+ | t2| index | PRIMARY,field1 | field1 | 7 | NULL | 30 || where used; Using index | | t1| ref| field2,field1,field4,field3 | field1 | 4 | t2.field1 | 508 || where used | | t3| eq_ref | PRIMARY,field1 | PRIMARY | 4 | t1.field2 |1 || where used | +---++-+-+-+---+--+-+ That's the exact same explain as (no MOD() expression): EXPLAIN SELECT /*! STRAIGHT_JOIN */ t1.* FROM table2 t2 INNER JOIN table1 t1 /*! USE INDEX ( field1 ) */ ON t1.field1=t2.field1 AND t2.field2 IS NOT NULL INNER JOIN table3 t3 ON t1.field2=t3.field2 AND t3.field1=1 WHERE t1.field3=0 AND t1.field4NOW(); My question is, when is that MOD() performed and used? Is it going to join t1, t2, and t3, find the fields that match on keys, and then sift out the rows that don't pass the MOD() or is it going to join t2 - t1, sift out the MOD() entries, then join t3 into the mix on the remaining rows? sql, query * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL/InnoDB-4.0.4 is released
On Wed, Oct 02, 2002 at 02:12:23PM +0300, Heikki Tuuri wrote: : Hi! : : InnoDB is a table type which provides transactions, row level locking, : foreign key constraints, and a non-free hot backup tool to MySQL. : : InnoDB is included in both downloadable versions of MySQL-4.0: MySQL-Pro and : MySQL-Max. : : Release 4.0.4-beta is mainly a bug fix release. Will the bugfixes for this release (not necessarily the new/updated features) be backported to the 3.23.x series? Given that MySQL 4 isn't marked as stable yet, many of us using InnoDB no doubt still use 3.23.x. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: 3.23.52 hangs sometimes
On Tue, Sep 24, 2002 at 08:21:36PM -0700, Andrew Maltsev wrote: : Any suggestions how to approach the problem? How and what to test? It : happens randomly, can work for a day or two with no problems and then : hang three times in one hour. And obviously I can't reproduce it in my : test environment however hard I stress test it. When it happens, what does the system look like? Is CPU pegged? Is MySQL using a lot of CPU. What does iostat tell you in terms of drive activity. Are the drives actively seeking or does the system seem relatively quiet? You'll have to do *some* troubleshooting. We had a similar problem on RedHat 7.2 (2.4.7) that we traced to poor memory/swap management in the kernel. Updating the kernel fixed it. Then we had another similar problem which we traced to our fibrechannel card. Replacing the card solved that. What does a 'show processlist' during the pause tell you? Are there any queries running extremely long? You can log those. Now, if we ever have a problem like that, it's usually I/O related (the disks are ultra busy, usually because of a backup or something; throttling the backup bandwidth usually solves that). Do some more research. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Deleting foreign keys with Innodb tables
On Thu, Jul 18, 2002 at 03:41:01PM +0300, Victoria Reznichenko wrote: : Archbold, : Thursday, July 18, 2002, 12:18:09 AM, you wrote: : : A I'm having a problem dropping a foreign key constraint from an Innodb table. I am :using MySQL 3.23.51 for Win32. Any help would be greatly appreciated : : A I have a simple set up like the one below: : : A CREATE TABLE mastertable : A ( : A masterkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT : A ) TYPE=innodb; : : A CREATE TABLE othertable : A ( : A otherkey INT NOT NULL PRIMARY KEY AUTO_INCREMENT : A masterkey INT NOT NULL, : A INDEX (masterkey), : A FOREIGN KEY (masterkey) REFERENCES mastertable (masterkey) ON DELETE CASCADE : A ) TYPE=innodb; : : Use DROP TABLE and then CREATE TABLE to remove foreign key : constraints. Maybe Heikki can expand on this, because this is really an unacceptable way to remove a foreign key. What do you do with a table that has tens or hundreds of millions of rows of data? Do you copy out the data, recreate the table, then copy it back in? Do you realize what type of performance/time/service hit that would place on a database, just to remove a foreign key? Are there any plans to introduce this seemingly basic functionality? Philip sql, query Please, please, the filter needs to be updated to allow more than just posts with sql or query in them. I replied to a post on coming from this list and it was rejected by the query. That's simply asinine. I can't believe it couldn't even pick out. Please update the filter with words/phrases such as innodb, index, foreign key, or table so we don't have to keep resending replies to the list. Either that, or turn the list into subscriber only. I realize that it may discourage some people from asking for help, but it's definitely more discouraging to have your posts rejected because you didn't use one of *two* keywords. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL: Problem Installing on RH 7.3
On Thu, Jul 18, 2002 at 11:44:14AM -0400, Richard Fox wrote: : The mysqld.log file says: : : 020717 13:05:05 mysqld started : 020717 13:05:05 /usr/local/libexec/mysqld: Can't find file: : './mysql/host.frm' (errno: 13) : 020717 13:05:05 mysqld ended : : But I do see the host.frm file: : : -rw-rw1 root root 8958 Jul 17 13:04 : /var/lib/mysql/mysql/host.frm : : How did you fix this problem? Permissions are wrong. MySQL typically runs as mysql:mysql, not root:root. If I remember correctly, there may have been a problem with the setup scripts that handled this. Check the RH bug reports. The fix is simply to change owner/groups on the necessary files/directories. But Trond can give you specifics. It still may be user error. :) : I do have the /var/lib/mysql/mysql.sock file... * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL: Problem Installing on RH 7.3
On Wed, Jul 17, 2002 at 04:40:50PM -0400, Richard Fox wrote: : : The MySQL daemon does not run. I installed the binary RPM's from the RH cd, : MySQL 3.23.49. I run mysql_install_db, outputs some text and appears to : execute correctly. The I run safe_mysqld , and I get: : : [root@thor rfox]# /usr/bin/safe_mysqld : [1] 2212 : [root@thor rfox]# Starting mysqld daemon with databases from /var/lib/mysql : 020717 15:21:28 mysqld ended : : That's it! I tried both the rpm and compiling it myself from source with : BOTH gcc 2.96 and gcc 3.0.4. Exact same behavior If it's the RPMs from RH, have you tried /etc/init.d/mysqld start? That's the standard way of starting services on RH. Have you looked in /var/log/mysqld.log for any errors? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication from InnoDB - MyISAM
I have a 3.23.51 server with InnoDB tables. I want to replicate one of the databases over to some 3.23.49 servers with MyISAM tables. The tables do have some auto-increment columns. I expect that only valid data is going to be written to the binlog, so there shouldn't be an issue with invalid data appearing in my MyISAM tables. Can I expect any problems with this setup? sql, query, stupid filter (perhaps the filter can be expanded to look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Replication from InnoDB - MyISAM
On Mon, Jul 15, 2002 at 02:40:14PM -0400, Nilesh Shah wrote: : How does transaction works in this scenario?. Is rollbacked transaction : written into bin-log files?. To quote Heikki from below: MySQL only writes committed transactions to the binlog, and consequently only replicates committed transactions. : - Original Message - : From: Philip Molter [EMAIL PROTECTED] : Newsgroups: mailing.database.mysql : Sent: Monday, July 15, 2002 5:55 PM : Subject: Replication from InnoDB - MyISAM : : : I have a 3.23.51 server with InnoDB tables. I want to replicate : one of the databases over to some 3.23.49 servers with MyISAM tables. : The tables do have some auto-increment columns. I expect that only : valid data is going to be written to the binlog, so there shouldn't : be an issue with invalid data appearing in my MyISAM tables. Can : I expect any problems with this setup? : : MySQL only writes committed transactions to the binlog, and consequently : only replicates committed transactions. : : Fortunately the MySQL replication puts commands : : SET INSERT_ID = ... : : to the binlog to replicate inserts to tables with auto-increment : columns. : Thus small differences in auto-increment algorithms in different tables : types do not cause problems in replication. : : Thus replication InnoDB table - MyISAM table should always work without : problems. : : sql, query, stupid filter (perhaps the filter can be expanded to : look for common MySQL words, like 'MyISAM, InnoDB, 3.23.x etc.'). : : * Philip Molter : * Texas.net Internet : * http://www.texas.net/ : * [EMAIL PROTECTED] : : Best regards, : : Heikki : Innobase Oy * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Keyfile size
I have a MySQL server that has hit it's keyfile size limit (apparently 64M). I can't believe that a value this low would be the absolute limit, but I can't find any documentation about increasing this size. How do I do it? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Keyfile size
On Mon, Jul 08, 2002 at 09:38:56AM -0500, Paul DuBois wrote: : At 8:45 -0500 7/8/02, Philip Molter wrote: : I have a MySQL server that has hit it's keyfile size limit (apparently : 64M). I can't believe that a value this low would be the absolute : limit, but I can't find any documentation about increasing this : size. How do I do it? : : What do you mean by keyfile? The index file for a MySQL table? : Or do you mean one of the server's memory caches? I mean the keyfile. If I do a CHECK TABLE on that able i get this back: mysql check table log; +--+---+--++ | Table| Op| Msg_type | Msg_text || +--+---+--++ | peace_keeper.log | check | warning | 5 clients is using or hasn't closed the table |properly | | peace_keeper.log | check | warning | Keyfile is almost full, 67107839 of 67107839 |used | | peace_keeper.log | check | error| Found 1940729 keys of 1940740 || | peace_keeper.log | check | error| Corrupt || +--+---+--++ 4 rows in set (1 min 14.16 sec) I need to know how to make that keyfile bigger than 64M in size. I have keyfiles on other (newer) systems that have keyfiles above 200M, and the file size limit on the system is at least 2GB. I can't find any documentation on how to change that value, just on how to fetch that value. And yes, it's corrupt because the keyfile is at its limit. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Keyfile size
On Mon, Jul 08, 2002 at 09:50:43AM -0500, Dan Nelson wrote: : In the last episode (Jul 08), Philip Molter said: : I have a MySQL server that has hit it's keyfile size limit : (apparently 64M). I can't believe that a value this low would be the : absolute limit, but I can't find any documentation about increasing : this size. How do I do it? : : The keyfile size limit for Mysql 3.23 is 4GB. What error are you : getting that suggests otherwise? See my response to Paul. The keyfile limit, at least for the table in question, is definitely 64M. The version of MySQL was 3.23.21. I upgraded to 3.23.51 and the index was still limited to 64M. I'm in the process of reconstructing the table to see if that will end this problem. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innodb rollbacks
On Thu, May 30, 2002 at 10:43:58AM -0700, Jeremy Zawodny wrote: : Heikki, : : That reminds me of an InnoDB wish-list item I have. I like the level : of detail provided in the InnoDB monitor output. However, I'd really : like to be available via MySQL rather than just in the logs. That : will make it a lot easier to collect the data remotely and write apps : that can monitor and make use of the data. : : Have you given any thought to that? (I have no idea what the effort : would be like, but it's can't hurt to ask...) Just for the record, we second that notion. It's a pain in the ass using the InnoDB table monitor simply because it dumps so much output into the log files. There are times when we just want to grab the status at a particular moment, like how one would do SHOW TABLE STATUS or SHOW PROCESSLIST. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Disable replication from command line
I want to ignore a database from bin-logging. I know that in my.cnf, I can set binlog-ignore-db=database_name but for various reasons, that's not available to me. How would I pass that option in on the command line via safe_mysqld? Passing it in as an option or a set-variable option causes MySQL to not start up. Thanks, Philip MySQL, SQL, database * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Qoute
On Fri, May 10, 2002 at 05:49:12PM +0200, Mark wrote: : This is too weird; I can quote until I see blew in the face, but I cannot : seem to make MySQL understand that the name 'group-name' is valid to : select. It keeps blabbing You have an error in your SQL syntax. I mean, : what is the purpose of its own quote function if it can not even quote : properly? Every other name works fine, except when a WHERE clause looks : for a column whose value has a dash in it. [/snip] : : Can we see the query? : : Jay : : Sure. : : $sth = $dbh - prepare (SELECT expiration from newsgroups WHERE : newsgroup='$group'); : $sth - execute; Try: $sth = $dbh-prepare( 'SELECT expiration FROM newsgroups WHERE newsgroup=?' ); $sth-execute( $group ); ... * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Optimizing InnoDB tables
If I have an InnoDB table, how can I optimize it (like OPTIMIZE TABLE for MyISAM tables)? I've seen previous posts from Heikki saying to use ALTER TABLE, as that effectively dumps and reimports the table, but is that true with the new 3.23.50 version that actually has a working ALTER TABLE? I ask because I have a long-running database and as records are getting inserted and deleted, system CPU usage is slowly climbing, presumably because MySQL is doing more work to look up information. Thanks, Philip * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Upgrade of mySQL on a Red Hat 7.2 - box
On Thu, Apr 25, 2002 at 09:44:48AM -0400, Shaun Bramley wrote: : Ladies and Gentleman of the list, : : I have been a member of the list for about two weeks now and I must say that : I have learned a lot. Needless to say I am relatively new to the whole db : scene. : : My question is that I installed mySQL onto my linux box (Red Hat 7.2) as : part of the installation. I am currently running 3.23.42?? I would like to : upgrade my installation to the latest and greatest, however: : a) I do not know which files I have to download server, libraries, : benchmark/test suites, etc. : b) I do not know if I should downlaod and install 4.0.1, or 3.23.49a/50 : c) I do not know if I should download and install mySQL or mySQL-max : c) Is the installation as simple as killing the sqld and then : installing the rpm(s). If not what else will I have to do? I take the latest rawhide mysql source RPMs available from RedHat, build them on my system (rpm -ba), and then upgrade with the resulting RPM files. I haven't had a problem with them yet. The upgrade shuts down any running server, so be sure to start it (with /etc/init.d/mysql start) when you're done. I don't recommend taking the MySQL available RPMs for RH7.2 systems, because the RH ones integrate better with everything else that goes on with RH (regarding startup scripts, file locations, etc.). MySQL AB may want you to for support reasons, but I find it's better to stick with RedHat standard installation methods rather than using the outside RPMs. It'd be great if MySQL released RPMs specifically for RedHat systems that integrate with the system according to the standards that RH has defined for their various versions. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Pre-release of MySQL 3.23.50
On Tue, Apr 23, 2002 at 10:26:04AM +0300, Michael Widenius wrote: : : Hi! : : 3.23.50 is basicly just a bug fix release compare do 3.23.49a : : There is however two things one should be aware of regarding 3.23.50 : (both only affecting our Linux x86 binaries (normal and RPM's): : : - We have switched to a new updated glib library, because we found a : critical memory corruption bug (introduced by us) in the old glibc : library that we used to compile 3.23.49. : (older MySQL binaries are not affected by this problem) : - We have changed compiler to gcc 3.0.4 (because the old gcc compiler : we used couldn't compile with an alternative glibc library). Does this affect people who compile MySQL on their own on Linux? If I have my RH7.2 box and I compile my own version of MySQL, am I going to hit these memory corruption issues? sql, query, mysql * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Msql-Mysql install problem...
On Wed, Apr 03, 2002 at 06:10:02PM +0300, Egor Egorov wrote: : Murali, : Wednesday, April 03, 2002, 1:55:57 PM, you wrote: You know, I don't meant to be a bother, but can you folks at Ensita reply to the list using standard reply conventions? All of your replies have the exact same subject as the message they're replying to. Since the messages don't seem to have any sort of reference headers and since you're not prefixing them with 'Re:' (or some variant thereof), most mail clients that group by thread (which is probably one of the easiest ways to read such an active list) can't group them. It's really quite annoying, not to mention the fact that you can't tell from the subject that it's a reply. Do you think the Ensita gang there can set their mail clients to prefix replies with 'Re:'? Otherwise, the list as it appears in my (and probably many others') mail clients is extremely hard to follow. Thanks, Philip sql, query, mysql * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Foreign keys in mysqldump?
On Tue, Mar 12, 2002 at 11:50:12AM +0200, Heikki Tuuri wrote: : Bob, : : I have now improved foreign key support so that version 3.23.50 does : : 1. show the FOREIGN KEY definitions when you call SHOW CREATE TABLE; this : should also show them in mysqldumps; : 2. preserve FOREIGN KEY definitions in ALTER TABLE and CREATE INDEX; : 3. allow backquotes around column and table names in foreign key : definitions: backquotes are produced by SHOW CREATE TABLE; : 4. allow adding a new foreign key constraint ot a table through : ALTER TABLE ... ADD CONSTRAINT FOREIGN KEY (...) REFERENCES ... (...); : remember though that you first have to create the appropriate indexes on the : parent and child table so that InnoDB approves the constraint. Heikki, That is fantastic news! As far as everyday usage of InnoDB tables goes, those are the number one issues we have to work around. Thanks for taking the time to implement that. Speaking of InnoDB development, what is the status on the InnoDB backup utility? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Double foreign key references?
On Fri, Jan 25, 2002 at 01:25:10PM -0600, Philip Molter wrote: : I have a table with two fields that reference the same field in : another table. Is this allowed (I'm not sure if it is). mysql : 3.23.46 allows this, but apparently, mysql 3.23.47 does not. Create : it with just one key and it's fine. Reference different tables : and it's fine. : : mysql-3.23.47 InnoDB tables under Sparc Solaris 8 : : : : mysql create table test_base ( fld int not null ); : Query OK, 0 rows affected (0.07 sec) : : mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key :(fld1) references test_base(fld), foreign key (fld2 references test_base(fld) ); : ERROR 1064: You have an error in your SQL syntax near 'references test_base(fld) )' :at line 1 : mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key :(fld1) references test_base(fld), foreign key (fld2) references test_base(fld) ); : ERROR 1005: Can't create table './test/test_fk.frm' (errno: 150) : mysql I'm wondering, has anyone been able to verify this? Is this a bug in mysql or is it a problem with an incorrect implementation turning into a correct one? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Double foreign key references?
On Mon, Jan 28, 2002 at 06:48:30PM +0200, Heikki Tuuri wrote: : Philip. : : you must have an index on the referenced key in the parent table. Now : test_base has no indexes at all. Apparently, you also need indexes on the child table: mysql create table test_bk ( fld int not null, primary key (fld) ); Query OK, 0 rows affected (0.00 sec) mysql create table test_fkey ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_bk(fld), foreign key (fld2) references test_bk(fld)); ERROR 1005: Can't create table './test/test_fkey.frm' (errno: 150) mysql create table test_fkey ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_bk(fld)); ERROR 1005: Can't create table './test/test_fkey.frm' (errno: 150) mysql create table test_fkey ( fld1 int not null, fld2 int not null, index (fld1), foreign key (fld1) references test_bk(fld)); Query OK, 0 rows affected (0.00 sec) mysql drop table test_fkey; Query OK, 0 rows affected (0.00 sec) mysql create table test_fkey ( fld1 int not null, fld2 int not null, index (fld1), index (fld2), foreign key (fld1) references test_bk(fld), foreign key (fld2) references test_bk(fld)); Query OK, 0 rows affected (0.01 sec) * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: quick questions about redhat and mysql
On Fri, Jan 25, 2002 at 10:18:08AM -0500, John Kemp wrote: : Hank, : : 3. Do not use the RH mysql version. Get it from the Mysql website, and : use 3.23.47-max (which includes Innodb support) or higher. What's wrong with the RH mysql version? I've been using it for 5 months without a problem. It has built-in support for both BDB and InnoDB tables and doesn't appear to have any problems with enterprise-class systems. Then again, we're using InnoDB tables, so we're not limited by file-size. Is there a reason to use MySQL's version over RH's? The only difference I can see is that RH's compiles it to work with different (read: standard RH) filesystem layouts, uses the standard RH initialization files, and has some RH-tuned config files. They're not patching the source at all, and my understanding is that the compile options are verified for the specific RedHat version. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Double foreign key references?
I have a table with two fields that reference the same field in another table. Is this allowed (I'm not sure if it is). mysql 3.23.46 allows this, but apparently, mysql 3.23.47 does not. Create it with just one key and it's fine. Reference different tables and it's fine. mysql-3.23.47 InnoDB tables under Sparc Solaris 8 mysql create table test_base ( fld int not null ); Query OK, 0 rows affected (0.07 sec) mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2 references test_base(fld) ); ERROR 1064: You have an error in your SQL syntax near 'references test_base(fld) )' at line 1 mysql create table test_fk ( fld1 int not null, fld2 int not null, foreign key (fld1) references test_base(fld), foreign key (fld2) references test_base(fld) ); ERROR 1005: Can't create table './test/test_fk.frm' (errno: 150) mysql * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Disabling foreign keys
Is there a way to disable foreign keys temporarily? Specifically, I'm running 3.23.47 with InnoDB tables, and I need to periodically dump and reload a table that has foreign key dependencies on it. Thanks, Philip * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: current development
On Wed, Jan 16, 2002 at 04:51:55PM +0100, Bruno Haller wrote: : Hello, : : I did not follow mySQL development really in the last months / year, so : I can't estimate that: When can we expect foreign key support in mySQL? : I think it's the most important feature for me that is missing, : cascading deletes and this stuff. With InnoDB, FK support is in the 3.23.x branch. That's a stable branch. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: innoDB confusion
On Tue, Jan 15, 2002 at 08:17:53PM +0200, Heikki Tuuri wrote: : Hi! : : Weaver, Walt wrote in message ... : Do a show table status at the mysql prompt. Under type you should see : InnoDB. : : Yes, and in really problematic situations, where you have lost your .frm : files, for example, you can use innodb_table_monitor as explained in the : InnoDB online manual. This brings up an interesting point. If you've lost your .frm files, are you totally screwed, or does InnoDB contain enough information to restore those .frm files? It's not a big deal if it doesn't. That's why God gave us backups, after all. I know oftentimes, when we're doing testing with very large tables, rather than go through the hassle of cleaning stuff out, we just drop the .frm files, which causes InnoDB to lose the tables. Then we can recreate just fine. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FOREIGN KEYs and ALTER TABLE
On Thu, Jan 03, 2002 at 01:48:21PM +0200, Heikki Tuuri wrote: : Hi! : : This is a feature (= documented bug). Look at : http://www.innodb.com/ibman.html: : ... : Updated December 13, 2001: Added a note that you should not do an ALTER : TABLE to a table which has or is referenced in a foreign key constraint, but : use DROP TABLE + CREATE TABLE to modify the schema Hrmm. Is that a feature that's planned to be fixed. Obviously, if you have a table with thousands or millions of rows in it, ALTER TABLE is a lot easier than copying the table to a temp table, dropping the original table, creating a new table, and copying the data back in. Also, we've been using the DROP/CREATE methodology, and keys in other tables referencing the altered table fail (they don't stop working, they refuse to take entries, citing a failed foreign key constraint). We have to DROP/CREATE every table in the key chain to get it done properly. Is that how that's supposed to work? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: FOREIGN KEYs and ALTER TABLE
On Thu, Jan 03, 2002 at 05:21:49PM +0200, Heikki Tuuri wrote: : mysql CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; : Query OK, 0 rows affected (0.11 sec) : : mysql CREATE TABLE child(id INT, parent_id INT, INDEX par_ind (parent_id), : - FOREIGN KEY (parent_id) REFERENCES parent(id)) : TYPE=INN : ODB; : Query OK, 0 rows affected (0.03 sec) : : mysql insert into parent values (10); : Query OK, 1 row affected (0.00 sec) : : mysql insert into child values (5, 10); : Query OK, 1 row affected (0.00 sec) : : mysql drop table parent; : Query OK, 0 rows affected (0.01 sec) : : mysql CREATE TABLE parent(id INT NOT NULL, PRIMARY KEY (id)) TYPE=INNODB; : Query OK, 0 rows affected (0.01 sec) : : mysql insert into parent values (10); : Query OK, 1 row affected (0.00 sec) : : mysql insert into parent values (20); : Query OK, 1 row affected (0.01 sec) : : mysql insert into child values (6, 20); : Query OK, 1 row affected (0.00 sec) Well, the problem right here with this methodology is that if parent is a large table, you have to reinsert all the data back into that table. That may be fine for small tables, but for 100,000 to 1,000,000+ row tables, that's pretty annoying. So we make a copy of our data first, so that the reinsertion goes much easier. I do this by simply renaming the table to get it out of the way. Then I make my new table with the same name as my old one, insert into it out of my old (renamed) table and then drop the renamed. Thus, I'm sure my data is correct. The problem appears to be that the child foreign key follows it's parent table around even after the rename, so when I create my new table with the same name as the old one, the child is no longer pointing there. On first thought, I would expect the child not to follow on a rename (because of the explicit pointer to a table named 'parent'). On the flip-side, I can see how it might be beneficial, but foreign keys in the abstract sense are references to keys by table names, not references to keys by table object. Using this methodology (listed below) should be significantly faster if you want to preserve the data in your table. Using the method you detailed, if you wanted to preserve the contents of the table being altered, you'd first have to dump your data into a file or another temporary table, then reinsert it. As an aside, the reverse method of this (create the new table, populate it with data from the old table, drop the old table, rename the new table) also fails on a foreign key constraint. mysql create table parent( id INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; Query OK, 0 rows affected (0.02 sec) mysql create table child( id INT, parent_id INT, INDEX par_ind (parent_id), FOREIGN KEY (parent_id) REFERENCES parent(id)); Query OK, 0 rows affected (0.01 sec) mysql insert into parent values (10); Query OK, 1 row affected (0.00 sec) mysql insert into child values (5, 10); Query OK, 1 row affected (0.00 sec) mysql alter table parent rename parent_temp; Query OK, 0 rows affected (0.02 sec) mysql create table parent( id INT NOT NULL, id2 INT NOT NULL, PRIMARY KEY (id)) TYPE=InnoDB; Query OK, 0 rows affected (0.01 sec) mysql insert into parent select id, 0 FROM parent_temp; Query OK, 1 row affected (0.00 sec) Records: 1 Duplicates: 0 Warnings: 0 mysql drop table parent_temp; Query OK, 0 rows affected (0.00 sec) mysql insert into parent values (20, 0); Query OK, 1 row affected (0.00 sec) mysql insert into child values (6,20); ERROR 1216: Cannot add a child row: a foreign key constraint fails mysql select * from parent; ++-+ | id | id2 | ++-+ | 10 | 0 | | 20 | 0 | ++-+ 2 rows in set (0.00 sec) mysql select * from child; +--+---+ | id | parent_id | +--+---+ |5 |10 | +--+---+ 1 row in set (0.00 sec) * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
FOREIGN KEYs and ALTER TABLE
With InnoDB tables under 3.23.4x, if you perform an ALTER TABLE on a table, any foreign key declarations that point to that table fail to work (they always return a failure). Is this a known bug? I don't see it on the InnoDB todo or bug list, but I seem to remember hearing about it already. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MyISAM/InnoDB/Oracle comparison, and a InnoDB table locking question
On Thu, Dec 27, 2001 at 12:18:38AM +0200, Heikki Tuuri wrote: : The MyISAM table obviously fit in the OS file cache, otherwise 1750 inserts : per second would not be possible. Did the table fit in the buffer pool of : InnoDB or the SGA of Oracle? Did you commit each insert individually in : InnoDB and Oracle? : : Setting : : innodb_flush_log_at_trx_commit=0 : : in my.cnf will speed up individual inserts if you can afford losing a few of : the last transactions in a crash. Did you configure the log files big enough : for InnoDB and Oracle? Are there guides out there for configuring these things? What is a big enough log file? Honestly, on a lot of stuff, I'm just guessing, but it takes a lot of time to fiddle with values, clean out the database, and then shove in 1 million rows to see the results. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problems in Foreign Key Syntax
On Mon, Dec 24, 2001 at 11:31:45PM +0200, Heikki Tuuri wrote: : Use the following: : : CREATE TABLE `wishlist` ( : `wishlist_id` int(11) NOT NULL auto_increment, : `user_id` int(11) NOT NULL, : PRIMARY KEY (`wishlist_id`), : INDEX `wishlist_user_id_ind` (`user_id`), : FOREIGN KEY (user_id) REFERENCES user (user_id) : ) TYPE = innodb; : : That will work. By the way, why do many people write table and column names : in quotes? What is the origin of that convention? Avoiding reserved words? Case sensitivity on some databases (ones that I've worked with include PostgreSQL) require quotes. Otherwise, things go in all lowercase. Many people write code to be database agnostic because it makes upgrading backends easier. I believe the quotes are allowed under the SQL92 standard, but don't quote me on that. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Table Locking...
On Fri, Dec 21, 2001 at 12:45:16PM -0800, Shannon Kendrick wrote: : Whats the drawback of using InnoDB instead of MySAM : tables? Tables aren't kept in separate files, disk space preallocated, little less mature (but no less stable, I've found). For most people, those aren't drawbacks, just differences. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Help! Inserts way too slow in big db
On Thu, Dec 20, 2001 at 08:32:55PM +0100, Steve Rapaport wrote: : Okay, here's a tuning/opt problem for you experts: : : I have several CD's worth of records that are being converted : and then inserted into our phone listing database. the total : db size will be about 22 million listings. What version of MySQL are you using, what kind of system are you running on, and what kind of table handler (MyISAM, InnoDB, BDB, etc.) are you using for this table? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB still gives poor analysis to the optimizer
On Mon, Dec 17, 2001 at 10:03:58PM -0600, Philip Molter wrote: : Is there any upcoming fix for this recurring problem? The table : handler is just giving poor data to the optimizer and the optimizer : is making bad decisions because of it. It appears to come and go, : depending on data that is in the table, what's been done, etc. Here's some specific data, because writing e-mails isn't too bright a thing to do late in the evening after struggling with a problem for the better part of the night. The crux of all this (for us, anyway) revolves around three tables in our join sequence: percept, hosts, and perceptType. percept.hid = hosts.hid (INNER) percept.ptid = perceptType.ptid (INNER) Other tables are joined in via LEFT JOINS, but they shouldn't (and don't) affect the optimization because they're just being joined in for ancillary data. So the three table structure is what I'm most concerned with. Everything is keyed properly, and this query optimizes correctly 100% of the time under MyISAM tables, and, for that matter, under PostgreSQL (which this is also being designed to run under, although Pg is worse for overall performance). So here's some row counts from the data: mysql select count(*) from hosts; = 38 mysql select count(*) from hosts where active=1;= 31 mysql select count(*) from perceptType; = 26 mysql select count(*) from perceptType where runinterval is not null;= 12 mysql select count(*) from percept; = 11305 mysql select count(*) from percept where deleted=0; = 10647 mysql select count(*) from percept p, hosts h where p.hid=h.hid and h.active=1 and p.deleted=0;= 9064 mysql select count(*) from percept p, perceptType pt where p.ptid=pt.ptid and pt.runinterval is not null and p.deleted=0;= 939 mysql select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0;= 816 Here are some explains. These were taken literally 30 seconds apart as I wrote this e-mail and the optimization switched. Here is the EXPLAIN for a good match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-+-+--+-+ | table | type | possible_keys| key | key_len | ref | rows | Extra || +---++--+-+-+-+--+-+ | pt| index | PRIMARY,ptid | ptid| 7 | NULL| 26 | where used; |Using index | | p | ref| deleted,hid,ptid | ptid| 4 | pt.ptid | 412 | where used || | h | eq_ref | PRIMARY,active | PRIMARY | 4 | p.hid |1 | where used || +---++--+-+-+-+--+-+ 3 rows in set (0.00 sec) Here is the EXPLAIN for a bad match: mysql explain select count(*) from percept p, perceptType pt, hosts h where p.hid=h.hid and p.ptid=pt.ptid and h.active=1 and pt.runinterval is not null and p.deleted=0; +---++--+-+-++--+-+ | table | type | possible_keys| key | key_len | ref| rows | Extra | | +---++--+-+-++--+-+ | h | index | PRIMARY,active | active | 4 | NULL | 38 | where used; |Using index | | p | ref| deleted,hid,ptid | hid | 4 | h.hid | 502 | where used | | | pt| eq_ref | PRIMARY,ptid | PRIMARY | 4 | p.ptid |1 | where used | | +---++--+-+-++--+-+ 3 rows in set (0.00 sec Given everything seen here, is there any way to understand why these poor decisions are being made? Not only is the first query information clearly better, but the underlying table data (row counts, etc.) clearly shows it to be better. I can't fathom how InnoDB is passing the optimizer information that is this far off. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php
Re: InnoDB still gives poor analysis to the optimizer
On Tue, Dec 18, 2001 at 11:19:06PM +0200, Heikki Tuuri wrote: : Philip, : : I recommend using the MySQL STRAIGHT JOIN and USE INDEX clauses to manually : force the best plan. I'd love to, but I also have to maintain a spec of database agnosticism (which I could do in code, of course, but I shouldn't be forced into MySQL-specific SQL when perfectly valid SQL should [and sometimes does] work just as well). That still doesn't rectify the situation that InnoDB queries aren't being properly optimized, and I have seen it on other databases under other circumstances. : You could also use innodb_table_monitor to check that the key value set : cardinality estimates are approximately right for the tables h, pt, and p. The cardinality estimates are accurate, which makes it all the more baffling how InnoDB/the optimizer are arriving at these conclusions. : But to put it the other way, how could the optimizer know beforehand the : cardinality of the h,p partial join versus the pt,p partial join? You're right, the optimizer can't know beforehand the cardinality of the h,p vs pt,p partial join, *but* that cardinality shouldn't be changing, especially not on tables whose index fields aren't being updated. Records in those tables are being affected, yes, but those specific column entries aren't, so the h,p vs. pt,p cardinality should never be an issue once the optimal plan has been found. That's why, under MyISAM, you issue an ANALYZE TABLE on your table(s) after you've populated them with a good chunk of your data and then you generally don't need to worry about it again. Unless your data set radically shifts and you absolutely need to switch to another index, your optimizer plan doesn't need to change. ANALYZE TABLE isn't an option under InnoDB (although sometimes, it appears to help). This stuff is shifting back and forth, and it's *VERY FAR* off when it shifts the wrong way. My question is Why? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
InnoDB still gives poor analysis to the optimizer
Is there any upcoming fix for this recurring problem? The table handler is just giving poor data to the optimizer and the optimizer is making bad decisions because of it. It appears to come and go, depending on data that is in the table, what's been done, etc. To give one example, with one of our queries that does a good deal of joining (roughly 10 tables), an optimized version needs to sift through approximately 6500 rows. The unoptimized version needs to sift through 8600 rows, a 32% increase that results (in our case) to a 20% increase in CPU usage on a dual CPU system. Right now, the tables are small, but we want them to get bigger, and the unoptimized version scales *much* worse than the optimized version. Furthermore, I thought sticking more data into the tables might eliminate the problem as the two plans data dispersals grew further apart, but it looks like that isn't the case. Sometimes, converting the tables to MyISAM (where the optimization *always* works) and then back to InnoDB fixes it, but obviously, that's not something you want to do on a running system. Is there any headway being made into this problem? I think I first reported it back around .41 or .42. This isn't really something a bug report can be filed on, because it seems to be the result of a varying data group and InnoDB's corresponding analysis, but if there's some bit of data that will help short of the data in my database, I'll gladly pass it on. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: How Huge of your mySQL database or table in your former Instance
On Sun, Dec 16, 2001 at 02:00:58AM -0800, Jeremy Zawodny wrote: : On Mon, Dec 10, 2001 at 01:25:07PM +0800, Ares Liu wrote: : : So, could you give me some advice that if it is feasible ? Or show : me your successful cases of using mySQL which is supporting very : large DB or tables with details ? : : How about a table with 260 million rows? We've got one that is very, : very quick for indexed selects. How quick is it for inserts? And what table type? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql forum or newsgroup
On Fri, Dec 14, 2001 at 07:16:40AM -0800, Jason Rowski wrote: : Hi, : : I willing to build and host a free mySQL discussion : forum/newsgroup for the mySQL community. I have web : space available and can easily build a discussion : forum using vbulletin and mySQL in a couple of days. : : Does the mySQL community think that we need a : discussion forum where all the mysql messages are : archived and offer superor search options ? Any : comments and feedback is appreciated. Why not just stick a web-frontend on to the mailing list? That way, there aren't two separate areas of MySQL support. There's already archives of the mailing list, and it sounds like what people want is the functionality of the mailing list without the inbox clutter. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySql forum or newsgroup
On Fri, Dec 14, 2001 at 11:14:00AM -0500, Etienne Marcotte wrote: : Sorry but I've never seen a mailing list archive really working.. That's a fault of mailing list archive designers, not mailing list archives. : No good search feature, hard to follow a thread (next by thread, next by : date, etc) Searching is easy for an archive. Several archives already have this functionality (MARC, eGroups, etc.). As for 'hard to follow a thread', if the display is threaded, and you get an opportunity to click on a thread and read the whole thing, what more can you ask for? If anything, forums often oversimplify this feature or reduce the ease of use of threaded designs. : Plus to post you need to be member of the mailing list, therefore : receive 400 emails a day... There are already other list-joining methods, like digest, that prevent the 400-email-a-day problem. I'm sure another registration method could be setup for a mailing-list front-end, to not send e-mails to people but allow them to post (hell, there may already be one). : On a forum you register, you post only when you have question, you have : separate areas (installation, query problem, design problems, innoDB : problem, let's say) and you can do specific searches. You can also have : email notice when a reply is made to a thread you started asking a : question. On mailing lists, you register, and you typically only post when you have a question. If someone replies to your post, a mailing-list archive frontend can easily detect that that reply was to you, and send you a message (if the original replier didn't already do it from his e-mail program or from the same frontend). As for separate areas, a front-end could easily manage that through an X-Header for messages posted from it and some creative processing (keywords, etc.) to handle messages to the mailing list that didn't come through the front-end (of course, once a thread is started, it falls into it's original area, so you'd only have to do this processing for the initial message in a thread). I mean, in essence, a forum is a prettified mailing list. The thing is, there's already a great mailing list that exists, and splitting off a forum means dividiing that knowledge so that people on the list don't know what's going on on the forum and vice versa (unless they follow both, which is even more time-consuming). Is that a *better* form of support? I would argue no, and since a solution is easily envisioned, why wouldn't one choose to go with it? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Default 1 == Default 16777216?
On Wed, Dec 12, 2001 at 02:35:15PM +0200, Sinisa Milivojevic wrote: : Philip Molter writes: : Description: : : When I do this: : :mysqlcreate table test_default ( testint int not null default 1 ); :mysqldesc test_default; : : I get: : :+-+-+--+-+--+---+ :| Field | Type| Null | Key | Default | Extra | :+-+-+--+-+--+---+ :| testint | int(11) | | | 16777216 | | :+-+-+--+-+--+---+ :1 row in set (0.00 sec) : : Why does my default not go in as '1'. I've tried it with and : without quotes. This is vexing. Is there something obvious that : I'm just not doing correctly? : : How-To-Repeat: : : : Hi! : : As there is a binary for your SPARC Solaris 2.8, please try it out : and see if you get the same result. Yeah, let me amend this. It's not happening with MyISAM tables, only with InnoDB tables, and it is happening with the pre-compiled binaries from the web site. It's definitely an endian issue. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Default 1 == Default 16777216 ?
On Mon, Dec 10, 2001 at 02:55:04PM -0700, Nathan wrote: : Dave Burgess had a good suggestion; I suppose you could specify the default in :hex... : (default = 0x1) - as long as this is NOT in quotes it will input the hex value 1. I compiled a 3.23.46 last night and I have the same problem. I tried using 0x1, and the default is still set to 16777216 (and values get input with that value as well). Can anyone else verify that this is the case? I'm seeing it both with MyISAM tables and InnoDB tables, so I doubt it's a table handler. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Default 1 == Default 16777216?
Description: When I do this: mysqlcreate table test_default ( testint int not null default 1 ); mysqldesc test_default; I get: +-+-+--+-+--+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+--+---+ | testint | int(11) | | | 16777216 | | +-+-+--+-+--+---+ 1 row in set (0.00 sec) Why does my default not go in as '1'. I've tried it with and without quotes. This is vexing. Is there something obvious that I'm just not doing correctly? How-To-Repeat: See above Fix: Don't know. I'm looking for verification that the problem exists. Submitter-Id: submitter ID Originator: Philip Molter Organization: Texas.Net MySQL support: none Synopsis: Default 1 == Default 16777216 Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.46 (Source distribution) Environment: System: SunOS 5.8 Generic_108528-12 sun4u sparc SUNW,Ultra-80 Architecture: sun4 Some paths: /usr/bin/perl /usr/ccs/bin/make /usr/local/bin/gcc /usr/ucb/cc GCC: Reading specs from /usr/local/lib/gcc-lib/sparc-sun-solaris2.8/2.95.3/specs gcc version 2.95.3 20010315 (release) Compilation info: CC='gcc' CFLAGS='' CXX='gcc' CXXFLAGS='' LDFLAGS='' This build was compiled with the specs used on MySQL's web site for Solaris (SunOS, actually) builds. LIBC: -rw-r--r-- 1 root bin 1763908 Nov 16 16:42 /lib/libc.a lrwxrwxrwx 1 root root 11 Nov 20 13:29 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Nov 16 16:42 /lib/libc.so.1 -rw-r--r-- 1 root bin 1763908 Nov 16 16:42 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Nov 20 13:29 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1136748 Nov 16 16:42 /usr/lib/libc.so.1 Configure command: ./configure --prefix=/usr/local/mysql-3.23.46 --with-low-memory --enable-assembler --enable-assembler --with-innodb - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Default 1 == Default 16777216?
On Tue, Dec 11, 2001 at 04:24:40PM -0600, Dave Burgess wrote: : Obvious stupid question: : : What happens when you specify the value as 16777216? Since you : are running on a SPARC, I'm sure you will end up with : the correct value (1) in the space. If that works, there is : definitely an endism problem. You are correct, sir. It's an endian issue. Hopefully, that can get fixed in either the next release or in my build process somewhere. MySQL, database, and other spam-detection words. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Default 1 == Default 16777216 ?
I have a 3.23.45 database running with InnoDB tables. When I do this: create table test_default ( testint int not null default 1 ); desc test_default; I get: +-+-+--+-+--+---+ | Field | Type| Null | Key | Default | Extra | +-+-+--+-+--+---+ | testint | int(11) | | | 16777216 | | +-+-+--+-+--+---+ 1 row in set (0.00 sec) Why does my default not go in as '1'. I've tried it with and without quotes. This is vexing. Is there something obvious that I'm just not doing correctly? * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Default 1 == Default 16777216 ?
On Mon, Dec 10, 2001 at 03:49:34PM -0600, Dave Burgess wrote: : You wouldn't happen to be crossing an architecture boundary, would you? : : Say from a Macintosh client to a PC server, or a PC client to a Sun Server? : : 16777216 Decimal = 100 Hex which would make perfect sense if there was an : 'endism' problem. No, the client is on the same machine as the database. MySQL, database, and help Maybe that will get me by the spam-filter. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: New binary installation problems.
On Wed, Dec 05, 2001 at 04:28:58PM -0500, David Hudgins wrote: : For some reason the binary install install of mysql on redhat linux is : putting the database files in /var/lib/mysql instead of : /usr/local/mysql/data. The data dir is present and holds mysql and test : databases, but when we populated the data base the new databases where put : into /var/lib/mysql. Will this cause problems? If so how can we change the : default data directory to /usr/local/mysql/data? Any assistance would be : greatly appreciated. Please reply to [EMAIL PROTECTED] Thanks very much. That's just how redhat does things. They're crazy like that. They also have more money than you or I, so they can do what they please. You should have an /etc/my.cnf file. You can edit the location of various files/directories in that file. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Keeping track of database changes
We've got a fairly critical database system that we're setting up and we need to know if there's an easy way to track changes made to the database (hopefully at a fairly low level). In essence, what we want, is a log of what fields and values are changed by the primary key on each table, including timestamps of said changes. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Hacked Servers
On Mon, Dec 03, 2001 at 05:25:38PM -, [EMAIL PROTECTED] wrote: : Hi : : We have 2 Redhat 6.1 servers and MySQL 3.22.32 and both boxes : appear to have been hacked on Friday last and MYSQL client just hangs : when connecting to the localhost MYSQL server. : : MySQL is running on both boxes and suffer the same problems. : : We also have to use kill -9 pid number to kill the server(s). : : No MySQL client can connect remotely to either of these machines however the : local MySQL client on the hacked server(s) can connect to other remote MySQL : servers. : : We have re-installed MySQL server on this hacked server and still the client : just hangs and no : errors in the logs appear. : : We have Intrusion software but its very long winded trying to find how to : fix it - and ultimately we will re-install. : (but first I have 600 clients per server to please!) : : Please HELP we and all our tech guys are stumped. Well, hopefully you have backups. If you don't, the data in your database is most likely safe, so back it up. Then completely wipe the box and start over from scratch. You should jut be able to copy the data files to back them up. I wouldn't recommend copying the mysql/ tables, though. They're probably tainted. The only way you're going to be sure that your box is safe is if you wipe and reinstall. Most hacker kits will install backdoors and exploitable holes. Using a hacked server is just a risk to everything you do. * Philip Molter * Texas.net Internet * http://www.texas.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: compile problems on Solaris8
On Fri, Nov 30, 2001 at 11:56:05AM -0500, Michael Stassen wrote:
:
: This keeps coming up. So far (in my reading of this list since spring,
: and searches on Google),
:
: * everyone who has reported this was using the precompiled binary of gcc
: (either 2.95.2 or 2.95.3) from Sunfreeware to build MySQL for Solaris 8
: sparc.
:
: * No one has spoken up to report success with this combination.
:
: * No one who built gcc from source has reported this problem.
I will say that I have built MySQL 3.23.x several times on Solaris
8 sparc machines with gcc-2.95.2 and gcc-2.95.3 from sunfreeware.com
with absolutely no problems. I use the same options that AB uses
to compile their binary distributions, and I make test every time
with no issues.
From the configure file:
cat conftest.$ac_ext EOF
#line 1460 configure
#include confdefs.h
main(){return(0);}
EOF
if { (eval echo configure:1465: \$ac_link\) 15; (eval $ac_link) 25; } test -s
conftest${ac_exeext}; then
ac_cv_prog_cc_works=yes
# If we can't run a trivial program, we are probably using a cross compiler.
if (./conftest; exit) 2/dev/null; then
ac_cv_prog_cc_cross=no
else
ac_cv_prog_cc_cross=yes
fi
else
echo configure: failed program was: 5
cat conftest.$ac_ext 5
ac_cv_prog_cc_works=no
fi
It looks like the test to determine whether a cross-compiler is
being dealt with is very simple. Perhaps, for some reason, that
simple program isn't being compiled correctly. Test and see.
That would be where I would start.
* Philip Molter
* Texas.net Internet
* http://www.texas.net/
* [EMAIL PROTECTED]
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/ (the list archive)
To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: java.util.Date, jdbc, and DATETIME
On Wed, Oct 31, 2001 at 09:35:49AM -0500, [EMAIL PROTECTED] wrote: : This question was asked back in 1999 and wasn't answered, so hopefully : someone will answer it now. : : I am using JBoss and they provide a JDBC to mySQL type mapping that maps : java.util.Dates to a jdbc-type of DATE and mySQL type of DATETIME. Well, : only the date portion (10-31-2001) is being stored and the time is set to : 00:00:00. It is a very simple question, why isn't the time being stored? I : am using the MM jdbc driver. Is it the driver code? Do something like this: statement.setTimestamp( 4, new Timestamp( myDate.getTime() ) ); The JDBC standard is for setDate() to store only DATE information. To set the entire timestamp, you need to use setTimestamp, which takes a java.sql.Timestamp instance. You can still fetch with getDate( int ), though. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: InnoDB??
On Tue, Sep 25, 2001 at 04:11:02PM -0600, Adam Douglas wrote: : I've just recently heard about InnoDB. Correct me if I'm wrong here : but does InnoDB just enable MySQL to have transactions and row level : locking? Is there more to InnoDB.. I went to the web site and didn't see any : explanation of what InnoDB is other then the title on the first page. Well, transactions, row-level locking, multi-version concurrency. The list goes on. Oh yeah, it's blazingly fast for most applications. We've been pounding on it for a couple of months now and the performance is incredible. It's young, though. Bugs are still being found and not everything is implemented perfectly yet. * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: fulltext indexes for innodb?
On Thu, Aug 30, 2001 at 01:44:56PM +0200, Sergei Golubchik wrote: : Hi! : : On Aug 30, Alexander wrote: : Hello, mysql team! : : Any chance (in the near future) to get FullText indexing working with innodb : table handler? : : Alexander : : I doubt it will be done it the near future : (unless a customer would like to pay for the feature, of course). How much would such a feature cost? Would the MySQL development team implement that or Heikki? * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL Web Site Changes
The new web site looks great and none of the old functionality has been lost. Except ... On the documentation listing for one-page-per-chapter and one-big-page, the keywords and commands, like 'SELECT' and 'GRANT' used to be in a different color (purple in that case) which made it very easy to scan through the document to find the command you needed (especially when the order was changed so that the commands weren't all listed in Chapter 7). Can that color-change be reimplemented? It's incredibly useful for day-to-day use of those chapters. And let's put in the word 'MySQL' to get around the spam filters. Thanks, Philip * Philip Molter * DataFoundry.net * http://www.datafoundry.net/ * [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
