upgraded, now nulls act differently
I am running RH 2.1 ES, i recently upgraded to 3.23.56-1.72. I am using mysql as a backend for postfix MTA. I use webmin to add new users to my database. Today, after upgrading, there was a distinct problem. I added a user, left a field blank, it was the relocate_to feild, which unless i put something there, i expect the field to be treated as null. Unfortunately when postfix checked the database, the new user had a relocated_to value, even though i left the field blank. The odd thing is..when i upgraded mysql, the null must have stuck from the upgrade, because the problem only presents itself when i add NEW users to the database. Any help as to how i can correct this? Thanks in advance, Aaron Martinez -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Newbye speed question
HI I am having a MYISAM database with 27 million rows and 19 fields all char between 15 and 1 characters long. yesterday i did a alter table mytablename add column (version char(2)); By now (18 hours later) it has not finished yet? Is there a way to somehow predict the time needed for this? Or to see the status? Or anyone has some experiense? thanx -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question
18hrs??? So, the database has been LOCKED for 18hrs - Original Message - From: Peer Reiser [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 03, 2003 6:57 PM Subject: Newbye speed question HI I am having a MYISAM database with 27 million rows and 19 fields all char between 15 and 1 characters long. yesterday i did a alter table mytablename add column (version char(2)); By now (18 hours later) it has not finished yet? Is there a way to somehow predict the time needed for this? Or to see the status? Or anyone has some experiense? thanx -- 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: How can i make mysql to print date and time automatically?
INSERT INTO your_table (field1, field2, field3) VALUES(DATE(), value2, TIME()) But check the manual for very simple things. Thanks Emery - Original Message - From: Emilio Ruben Estevez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 17:09 Subject: How can i make mysql to print date and time automatically? Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Thaks in advance. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- 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]
unexpected index behaviour...
Hello all, I asked a similar question earlier. Then I went and did tons of tests... but I am back where I started. Basicly I am trying to store pretty high volume of data (ip traffic) in a mysql database. The only choidce for an engine is MyIsam because I need the advantage of compressed tables. Also I need to be able to use indexes to optimize my queries in a table with 200 000 entries. The problem begins when a table that includes a timestamp() column grows over a given size any select from the table stops using the indexes. First, as the table gets filled with data, queries based on a where from the timestamp column refuse to use the index, then when the table grows even larger - all other indexes fail as well. From what I've read it seems that mysql has a system of determining when to use an index and when to fall back to reading the whole table. But i never found any documentation on how to control this mechanism. Playing with the key_buffer doesn't yield any results. Could you please point me to any information about an issue like this. Thanks Peter Below are some excerpts to make the picture brighter: mysql describe ulog; ++--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-+---+ | pkt_time | timestamp(12)| YES | MUL | NULL| | | oob_prefix | varchar(32) | YES | MUL | NULL| | | oob_mark | int(10) unsigned | YES | | NULL| | | oob_in | varchar(32) | YES | | NULL| | | oob_out| varchar(32) | YES | | NULL| | | ip_saddr | int(10) unsigned | YES | | NULL| | | ip_daddr | int(10) unsigned | YES | | NULL| | | ip_tos | tinyint(3) unsigned | YES | | NULL| | | ip_ttl | tinyint(3) unsigned | YES | | NULL| | | ip_totlen | smallint(5) unsigned | YES | | NULL| | | udp_sport | smallint(5) unsigned | YES | | NULL| | | udp_dport | smallint(5) unsigned | YES | | NULL| | ++--+--+-+-+---+ 12 rows in set (0.00 sec) mysql select * from ulog limit 300,1; +--++--++-+++++---+---+---+ | pkt_time | oob_prefix | oob_mark | oob_in | oob_out | ip_saddr | ip_daddr | ip_tos | ip_ttl | ip_totlen | udp_sport | udp_dport | +--++--++-+++++---+---+---+ | 031003023231 | fwin3 |0 | eth0 | eth2| 1079095811 | 3232235779 | 0 |112 | 170 | 27016 | 1817 | +--++--++-+++++---+---+---+ 1 row in set (0.00 sec) mysql show index from ulog; +---++--+--+-+---+-+--++--++-+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | +---++--+--+-+---+-+--++--++-+ | ulog | 1 | glbl |1 | pkt_time| A |NULL | NULL | NULL | | BTREE | | | ulog | 1 | glbl |2 | oob_prefix | A |NULL | NULL | NULL | YES | BTREE | | | ulog | 1 | prfx |1 | oob_prefix | A |NULL | NULL | NULL | YES | BTREE | | | ulog | 1 | tim |1 | pkt_time| A |NULL | NULL | NULL | | BTREE | | +---++--+--+-+---+-+--++--++-+ 4 rows in set (0.00 sec) mysql explain select * from ulog where pkt_time = 031003023231; +---+--+---+--+-+--+---+-+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+---+-+ | ulog | ALL | glbl,tim | NULL |NULL | NULL | 15323 | Using where | +---+--+---+--+-+--+---+-+ 1 row in set (0.00 sec) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question
Fortunately the database is not in production :-) but it should go as soon as possible :-( On viernes, octu 3, 2003, at 11:03 Europe/Madrid, Wang Feng wrote: 18hrs??? So, the database has been LOCKED for 18hrs - Original Message - From: Peer Reiser [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 03, 2003 6:57 PM Subject: Newbye speed question HI I am having a MYISAM database with 27 million rows and 19 fields all char between 15 and 1 characters long. yesterday i did a alter table mytablename add column (version char(2)); By now (18 hours later) it has not finished yet? Is there a way to somehow predict the time needed for this? Or to see the status? Or anyone has some experiense? thanx -- 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: Newbye speed question - which setup to use for indexing
Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? Is disk I/O more important ? The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). anyone tried indexing a large? database? thanks a lot -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql 4.0.15 and latest redhat enterprise AS Beta - segmention fault
HI List! mysql 4.0.15 won't start on the latest beta of the redhat enterpise AS. (will be released this month) i used the std. rpms for linux x86. i get the following error: Starting mysqld daemon with databases from /var/lib/mysql /usr/bin/mysqld_safe: line 339: 14874 Speicherzugriffsfehler $NOHUP_NICENESS $ledir/$MYSQLD $defaults --basedir=$MY_BASEDIR_VERSION --datadir=$DATADIR $USER_OPTION --pid-file=$pid_file --skip-locking $err_log 21 031003 10:42:17 mysqld ended i've compiled the SRPM package - everything ok. bye, thomas
unicode_support
Hi Everybody! I use MySQL with JavaNetBeans. Please tell me, if only the InnoDb support unicode, or MyISAM also supports, and how. best regards Laszlo Illyes Library-informatics Sapientia University (Csikszereda) Miercurea-Ciuc Tel:+40266317310 Fax:+40266317310/+40266371121 Mobil:+40740055706 E-mail: [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ran out of space for bin logs
Hi, 1. Can I safely delete the binlogs and clear the binlog index by hand? The binlog index maintains admistrative informations that the daemon use to manage the binlog files. If you delete it ( i don't recommended this) the daemon will recreate it . 2. How can I regulate the size of the binlogs to something manageable? you can set the variable max_binlog_size in the section mysqld of my.cnf file to a value of choice , example 5M . After the 5 Mo is reached, it switch to new binlog file. You can also move old binlogs files to another location to avoid eating all available space. Hope it helps Thierno 6C - Original Message - From: Christopher L. Everett [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, October 03, 2003 12:49 AM Subject: ran out of space for bin logs Aparrently my binlogs grew and grew and ate up all the space on their partition. At this point, I see this on the master mysql show master status; Empty set (0.00 sec) and this on the slave: mysql show slave status\G *** 1. row *** Master_Host: master-db Master_User: repl Master_Port: 3306 Connect_retry: 60 Master_Log_File: carbon-bin.09 Read_Master_Log_Pos: 201392116 Relay_Log_File: silicon-relay-bin.07 Relay_Log_Pos: 4 Relay_Master_Log_File: carbon-bin.09 Slave_IO_Running: No Slave_SQL_Running: Yes Replicate_do_db: Replicate_ignore_db: Last_errno: 0 Last_error: Skip_counter: 0 Exec_master_log_pos: 201392116 Relay_log_space: 4 which I think means my replications long past the point of retrieval. I'm pretty well reconciled to taking my system out of production, copying all the databases from the master to the slave, dropping the binlogs and restarting the replication. I have 2 questions: 1. Can I safely delete the binlogs and clear the binlog index by hand? 2. How can I regulate the size of the binlogs to something manageable? TIA for your help. -- 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: Newbye speed question - which setup to use for indexing
Peer, How big are the table and index files? Can your OS handle files bigger than 2/4Gb? I've got a table with 55 million rows with just 3 columns all floats. I've got three indexes with all the fields in various orders. My data file is 700Mb but my index file is over 4Gb, so yours could easily be so (as could your data file). Indexing my db takes under 2 hours on a sloow 400MHz Sun E250. I don't think the index process will use two processors, but I don't think your problem is related to processor speed. Andy -Original Message- From: Peer Reiser [mailto:[EMAIL PROTECTED] Sent: 03 October 2003 10:28 To: [EMAIL PROTECTED] Subject: Re: Newbye speed question - which setup to use for indexing Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? Is disk I/O more important ? The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). anyone tried indexing a large? database? thanks a lot -- 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: How can i make mysql to print date and time automatically?
I am sorry, the instruction I sent doesn't work in MySQL: You should use: INSERT INTO your_table (field1, field2, field3) VALUES(CURDATE(), value2, CURTIME()) Note the CUR (I think it stands for CURrent) Thanks Emery - Original Message - From: Director General: NEFACOMP [EMAIL PROTECTED] To: Emilio Ruben Estevez [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, October 02, 2003 22:16 Subject: Re: How can i make mysql to print date and time automatically? INSERT INTO your_table (field1, field2, field3) VALUES(DATE(), value2, TIME()) But check the manual for very simple things. Thanks Emery - Original Message - From: Emilio Ruben Estevez [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 17:09 Subject: How can i make mysql to print date and time automatically? Hi, im develping an application, and was wondering how can i make mysql get time and date from pc and print it automatically in the time field and date field so the user dont have to worry about entering the coorect time and date. Is this posible, ive created a databse with fields hour(time) and Date(date) like type but i dont know how to do the mysql to get time and date and print it! Any hints? Thaks in advance. Emilio. _ Add MSN 8 Internet Software to your existing Internet access and enjoy patented spam protection and more. Sign up now! http://join.msn.com/?page=dept/byoa -- 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]
How to show locks on a table?
Subject -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Why does mySQL violate NOT precedence?
I am reposting this in hopes of convincing the mySQL
implementors to change mySQL or to at least gain some
understanding as to why they violated the
specification.
Apparently, in MySQL, NOT has higher precedence than
the comparison operators, so
WHERE NOT name = 'Bob'
is equivalent to
WHERE (NOT name) = 'Bob'.
See example at the end of this message if you wish to
verify this.
Two questions (with justifications):
1. Is giving NOT higher precedence than comparison
operators a violation of the specification? My
answer: Yes.
Based on my reading of the specification, giving NOT a
higher precedence than comparison operators is not
standards compliant. At the end of this message is
the grammar for the WHERE clause from the 1999 SQL
specification. Note that the precedence order is
(from highest to lowest) comparison predicate, NOT,
AND, OR.
2. What does NOT x mean where x is non-NULL and
some type other than boolean? NOT NULL returning NULL
appears to be standards complaint, but how would you
get 1 or 0?
In addition, consider the following from the 1999
spec:
4.6.2.1 Operations on booleans that return booleans
The monadic boolean operator NOT and the dyadic
boolean operators AND and OR take boolean operands and
produce a boolean result.
From this, it seems that NOT only takes booleans
(unless there is another monadic boolean operator
not).
*
where clause ::= WHERE search condition
search condition ::=
boolean value expression
boolean value expression ::=
boolean term
| boolean value expression OR boolean term
boolean term ::=
boolean factor
| boolean term AND boolean factor
boolean factor ::=
[ NOT ] boolean test
boolean test ::=
boolean primary [ IS [ NOT ] truth value ]
truth value ::=
TRUE
| FALSE
| UNKNOWN
boolean primary ::=
predicate
| parenthesized boolean value expression
| nonparenthesized value expression primary
parenthesized boolean value expression ::=
left paren boolean value expression right paren
predicate ::=
comparison predicate
| between predicate
| in predicate
| like predicate
| null predicate
| quantified comparison predicate
| exists predicate
| unique predicate
| match predicate
| overlaps predicate
| similar predicate
| distinct predicate
| type predicate
comparison predicate ::=
row value expression comp op row value
expression
comp op ::=
equals operator
| not equals operator
| less than operator
| greater than operator
| less than or equals operator
| greater than or equals operator
Here's my schema and data:
create table person (name char(5));
insert into person values ('Bob');
insert into person values ('Jane');
In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the
following results:
mysql SELECT * FROM person WHERE NOT name = 'Bob';
Empty set (0.00 sec)
mysql SELECT * FROM person WHERE NOT (name = 'Bob')
+--+
| name |
+--+
| Jane |
+--+
1 row in set (0.00 sec)
__
Do you Yahoo!?
The New Yahoo! Shopping - with improved product search
http://shopping.yahoo.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Install/compile problems
?xml version=1.0 ?html head title/title /head body div align=leftfont face=Arialspan style=font-size:10ptHi, /span/font/div div align=leftbr//div div align=leftfont face=Tahomaspan style=font-size:10ptSYSTEM: Compaq Alpha, Tru64 5.1, mysql4.0.15a. /span/font/div div align=leftbr/ /div div align=leftfont face=Tahomaspan style=font-size:10ptI have having trouble compiling the source on the above system. I have tried a variety#160; of configure option, notable the ones offered in the install notes for Tru64 OSF but I#160; still seem to hit an error during the make. I also install gnumake in case the problem#160; was there but no luck.#160; /span/font/div div align=leftbr/ /div div align=leftfont face=Tahomaspan style=font-size:10ptThe error is below. I wonder if it would help changing my shell for the make part of#160; the compilation. And does anyone know why, what or if it should be using g++??#160; /span/font/div div align=leftbr//div div align=leftfont face=Tahomaspan style=font-size:10ptAny help would be much appreciated. /span/font/div div align=leftfont face=Tahomaspan style=font-size:10ptDp. /span/font/div div align=leftbr/ /div div align=leftbr/ /div div align=leftfont face=Arialspan style=font-size:10ptMaking all in client /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake[2]: Entering directory `/usr0/src/mysql-4.0.15a/client' /span/font/div div align=leftfont face=Arialspan style=font-size:10ptsource='mysql.cc' object='mysql.o' libtool=no \ /span/font/div div align=leftfont face=Arialspan style=font-size:10ptdepfile='.deps/mysql.Po' tmpdepfile='.deps/mysql.TPo' \ /span/font/div div align=leftfont face=Arialspan style=font-size:10ptdepmode=none /bin/ksh ../depcomp \ /span/font/div div align=leftfont face=Arialspan style=font-size:10ptg++ -DUNDEF_THREADS_HACK -I. -I. -I.. -I./../include -I../include - I./.. -I.. -I..#160;#160;#160;#160; -O#160; /span/font/div div align=leftfont face=Arialspan style=font-size:10pt-DDBUG_OFF#160;#160; -DUNDEF_HAVE_GETHOSTBYNAME_R - D_REENTRANT -c -o mysql.o#160; /span/font/div div align=leftfont face=Arialspan style=font-size:10pt`test -f mysql.cc || echo './'`mysql.cc /span/font/div div align=leftfont face=Arialspan style=font-size:10pt../depcomp[402]: g++:#160; not found /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake[2]: *** [mysql.o] Error 127 /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake[2]: Leaving directory `/usr0/src/mysql-4.0.15a/client' /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake[1]: *** [all-recursive] Error 1 /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake[1]: Leaving directory `/usr0/src/mysql-4.0.15a' /span/font/div div align=leftfont face=Arialspan style=font-size:10ptmake: *** [all] Error 2 /span/font/div div align=leftbr/ /div div align=leftfont face=Tahomaspan style=font-size:10ptDermot Paikkos/span/font/div div align=leftbr/ /div div align=leftfont face=Tahomaspan style=font-size:10pt[EMAIL PROTECTED]/span/font/div div align=leftfont face=Tahomaspan style=font-size:10ptNetwork Administrator @ Science Photo Library/span/font/div div align=leftfont face=Tahomaspan style=font-size:10ptPhone: 0207 432 1100 /span/font/div div align=leftfont face=Tahomaspan style=font-size:10ptFax: 0207 286 8668/span/font/div /body /html -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: grant by option on querys
Thank you very much Paul. The order by NULL clause sped the query up from 1.5 minutes to 10 seconds! This is what we were looking for. Thanks, Brad -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED] Sent: Thursday, October 02, 2003 9:59 PM To: Brad Teale; '[EMAIL PROTECTED]' Subject: Re: grant by option on querys At 21:07 -0500 10/2/03, Brad Teale wrote: Hi All, I asked earlier about a query being slow, possibly due to MySQL 'Using temporary; Using filesort' when processing the query. I have done some testing, and it appears that no matter what data set is used, MySQL always performs a select with a 'grant by' clause using the temporary and filesort methods. The only time I could force MySQL into not using these methods happened when a did a goup by on a column that absolutely contained the same information. Is this the standard behavior? Is there anyway to get around this? Is there a MySQL variable I can tweak? Try adding ORDER BY NULL to suppress the implicit sorting that GROUP BY does in MySQL. Of course, that means your results won't be sorted. If you really want them sorted, you might try indexing modelhr, the column you're grouping by. You might try indexing it anyway, in fact. That may give you quicker grouping. My example: mysql desc foo; +--+--+--+-++---+ | Field| Type | Null | Key | Default| Extra | +--+--+--+-++---+ | stn | char(4) | YES | MUL | NULL | | | modelhr | int(2) | YES | | NULL | | | f_temp | decimal(6,2) | YES | | NULL | | | m_temp | decimal(6,2) | YES | | NULL | | | yearmoda | date | | | -00-00 | | +--+--+--+-++---+ 5 rows in set (0.00 sec) mysql select * from foo; +--+-++++ | stn | modelhr | f_temp | m_temp | yearmoda | +--+-++++ | KHOU | 6 | 90.00 | 89.60 | 2003-06-01 | | KHOU | 6 | 76.00 | 71.60 | 2003-06-01 | | KHOU | 6 | 75.00 | 73.40 | 2003-06-01 | | KHOU | 6 | 88.00 | 87.80 | 2003-06-01 | +--+-++++ 4 rows in set (0.01 sec) mysql explain select stn, modelhr, m_temp from foo group by modelhr; +---+--+---+--+-+--+--+ - + | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+ - + | foo | ALL | NULL | NULL |NULL | NULL | 120 | Using temporary; Using filesort | +---+--+---+--+-+--+--+ - + 1 row in set (0.01 sec) mysql explain select stn, modelhr, m_temp from foo where stn='KHOU' and yearmoda = '2003-06-02' group by modelhr; +---+--+---+--+-+--+--+ - -+ | table | type | possible_keys | key | key_len | ref | rows | Extra | +---+--+---+--+-+--+--+ - -+ | foo | ALL | stn,stn_2 | NULL |NULL | NULL | 90 | Using where; Using temporary; Using filesort | +---+--+---+--+-+--+--+ - -+ 1 row in set (0.05 sec) -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
GROUP BY/ORDER BY Problem
Why doesn't the following work: mysql CREATE TABLE dog(id integer, breed char(20), age integer, weight integer) ; mysql SELECT breed, MIN(age) - FROM dog - GROUP BY breed - ORDER BY MIN(age); ERROR : Invalid use of group function but this does mysql SELECT breed, MIN(age) AS minage - FROM dog - GROUP BY breed - ORDER BY minage; __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld crash
Description: Upon starting mysql client with the following command: mysql -ppassword radiant Immediately error message where displayed, one per table, saying that the table existed, however no columns/fields where found. Unfortunately I do not have the logs of this, as these logs where to stdout, rather than the log file. However, the log file did contain a difference set of errors, at this time, and is included in this mail. Here is the .err file from the machine: 030819 15:42:06 mysqld started 030819 15:42:06 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 031002 13:20:35 InnoDB: Assertion failure in thread 800935949 in file ut0mem.c line 157 InnoDB: Failing assertion: block-magic_n == UT_MEM_MAGIC_N 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=8388600 read_buffer_size=131072 max_used_connections=22 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x41714fd8 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... Cannot determine thread, fp=0xbfe1f308, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x807474f 0x82a0ad8 0x8244c7d 0x8243028 0x8241c21 0x813c3d4 0x80d6899 0x8097901 0x809517c 0x828a3b5 0x80952ee 0x807ef22 0x80844ee 0x807d79f 0x829e28c 0x82d199a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructio ns on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x88bbc30 = thd-thread_id=9632717 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. Number of processes running now: 0 031002 13:20:36 mysqld restarted 031002 13:20:36 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 28715608 InnoDB: Doing recovery: scanned up to log sequence number 0 28715608 031002 13:20:36 InnoDB: Flushing modified pages from the buffer pool... 031002 13:20:36 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Here is the symbol dump of the backtrace: 0x807474f handle_segfault + 423 0x82a0ad8 pthread_sighandler + 184 0x8244c7d ut_free + 209 0x8243028 mem_area_free + 48 0x8241c21 mem_heap_block_free + 385 0x813c3d4 row_prebuilt_free + 444 0x80d6899 close__11ha_innobase + 25 0x8097901 closefrm__FP8st_table + 45 0x809517c free_cache_entry__FP8st_table + 36 0x828a3b5 hash_delete + 813 0x80952ee close_thread_tables__FP3THDb + 174 0x807ef22 dispatch_command__F19enum_server_commandP3THDPcUi + 3886 0x80844ee do_command__FP3THD + 154 0x807d79f handle_one_connection + 635 0x829e28c pthread_start_thread + 220 0x82d199a thread_start + 4 How-To-Repeat: We have not been able to recreate this problem. We have logged into the mysql client many times since, and have not seen this error again. Fix: None. Submitter-Id: Originator:root Organization: Richard Runds MySQL support: none Synopsis: mysqld on off crash Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-4.0.14-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: dell, linux redhat 9.0, i686 System: Linux radiant-cgh.rieo.cgh 2.4.20-19.9custom #3 Tue Aug 12 15:47:29 GMT 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit
Re: Why does mySQL violate NOT precedence?
I'm not sure why you would use the syntax you are trying to use. I think you are making a few assumptions that may be incorrect. If you do this query: SELECT not name='Bob' FROM person You'll see your result is set contains all 0's. Records where name='Bob' would return 1, but you are taking the opposite of 1, which would be 0. For those records that return 0, you want the opposite of 0, which is? Infinity? If you convert infinity to boolean, it would be false. So from my point of view, MySQL is using the correct precedence. If you want to find all records that are not Bob, why not just us this syntax: SELECT * FROM person WHERE name!='Bob' It's more efficient since there is only one comparison occurring instead of two, negating name='Bob'. After all, you are looking to negate the equal, not negate name. On Friday, October 3, 2003, at 07:42 AM, Ed Smith wrote: In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the following results: mysql SELECT * FROM person WHERE NOT name = 'Bob'; Empty set (0.00 sec) mysql SELECT * FROM person WHERE NOT (name = 'Bob') +--+ | name | +--+ | Jane | +--+ 1 row in set (0.00 sec) -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tomcat, Connection Pooling, and MySQL
Has anyone managed to setup Tomcat to use MySQL for database connection pooling? I've found this document which gives information on it: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasource-examples-howto.html However, when I try and run it I get messages about not being able to find the hsql driver class. From searching on Google, it seems that this is a common error, but there are no solutions posted. Has anyone managed to get it up and running successfully? I'd really appreciate any advice! Thanks! Steve Nakhla - Do you Yahoo!? The New Yahoo! Shopping - with improved product search
Locked connection doesn't disconnect
Hi. I've developed a program in Delphi 5 that uses ZeosLib components for connect to a MySQL database that uses MyISAM tables and inserts some records. If I finalize the program with Ctrl-Alt-Supr when it's inserting the records the programs disconnects from MySQL (a Disconnect method is called in Destroy event of main form in Delphi). But if the connection is locked by a query when I call the method Disconnect, the MySQL Server didn't disconnect the connection to the database. Any idea? Iago -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Relationships - Foreign Key
Hi, sorry for my english, but, how can I do relationships between tables? Is possible? Thanks, Paulo Daniel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why does mySQL violate NOT precedence?
Your point does not seem relevant to my question on mySQL compliance with the specification. In fact, your response doesn't even mention the specification. Perhaps I'm misunderstanding your question. You are wondering why I would ask a query like SELECT * FROM Person WHERE NOT name = 'Bob'? Might I ask such a query to get the list of all people who are not named Bob? Also, my question involves mySQL's conformance to the SQL specification. Your demonstration of what mySQL does with SELECT not name... seems irrelevant if it's mySQL's compliance that is in question. I'm not sure why you would use the syntax you are trying to use. I think you are making a few assumptions that may be incorrect. If you do this query: SELECT not name='Bob' FROM person You'll see your result is set contains all 0's. Records where name='Bob' would return 1, but you are taking the opposite of 1, which would be 0. For those records that return 0, you want the opposite of 0, which is? Infinity? If you convert infinity to boolean, it would be false. So from my point of view, MySQL is using the correct precedence. If you want to find all records that are not Bob, why not just us this syntax: SELECT * FROM person WHERE name!='Bob' It's more efficient since there is only one comparison occurring instead of two, negating name='Bob'. After all, you are looking to negate the equal, not negate name. On Friday, October 3, 2003, at 07:42 AM, Ed Smith wrote: In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the following results: mysql SELECT * FROM person WHERE NOT name = 'Bob'; Empty set (0.00 sec) mysql SELECT * FROM person WHERE NOT (name = 'Bob') +--+ | name | +--+ | Jane | +--+ 1 row in set (0.00 sec) -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Tomcat, Connection Pooling, and MySQL
I got it working... unfournately it's on my laptop at home, not here at work with me I think that the issues was that the class names given in the documentation for the jdbc driver for MySQL were wrong look at the listing of the contents of the jar file, and see if you can find the right one... Sorry that I'm being incredibly vague I set it up months ago Dan Greene -Original Message- From: Steven Nakhla [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 9:42 AM To: MySQL Subject: Tomcat, Connection Pooling, and MySQL Has anyone managed to setup Tomcat to use MySQL for database connection pooling? I've found this document which gives information on it: http://jakarta.apache.org/tomcat/tomcat-4.1-doc/jndi-datasourc e-examples-howto.html However, when I try and run it I get messages about not being able to find the hsql driver class. From searching on Google, it seems that this is a common error, but there are no solutions posted. Has anyone managed to get it up and running successfully? I'd really appreciate any advice! Thanks! Steve Nakhla - Do you Yahoo!? The New Yahoo! Shopping - with improved product search -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
General error: Incorrect key file for table: 'RoleM'. Try to repair it
Hello, I am facing a very strange problem, my application says General error: Incorrect key file for table: 'RoleM'. Try to repair it but when i trying myisamchk then it doesn't give any corruption error. I have tried many things but nothing seems to be of any help, i would really appreciate anhy hint/solution towards this problem. Regards, S L __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Off-line DB Re-synchronizing?
Greetings, We have 10 people that need to use MySQL on notebooks/tablets off-line, entering data, doing searches. When they reconnect to the Internet, the database needs to upload the new data to a server and retrieve a fresh copy of some common tables. I was thinking about the LOAD DATA feature, but I am new to MySQL and wonder if there is a better method to synchronize the databases? Much thanks, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: General error: Incorrect key file for table: 'RoleM'. Try to repair it
i googled your error and the first link looks like it may help google - General error: Incorrect key file for table www.vbulletin.com/forum/ showthread.php?t=5387goto=nextoldest hth Jeff shahanawaz lakhani To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: oo.com Subject: General error: Incorrect key file for table: 'RoleM'. Try to repair it 10/03/2003 11:13 AM Hello, I am facing a very strange problem, my application says General error: Incorrect key file for table: 'RoleM'. Try to repair it but when i trying myisamchk then it doesn't give any corruption error. I have tried many things but nothing seems to be of any help, i would really appreciate anhy hint/solution towards this problem. Regards, S L __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication
If I have replication already active for a single database ( already active between master and slave ) and I want to start replicating a new database on the master, what would the correct procedure be? I have a dump of the database I want to replicate. Below is the current setup for replication. Master my.cnf log-bin binlog-do-db=database1 server-id=1 Slave my.cnf master-host=master.address master-user=replicant2 master-password=password replicate-do-db=database1 server-id=3 Would I just do the following? Master my.cnf binlog-do-db=database2 (then restart master) load dump into slave db Slave my.cnf replicate-do-db=database2 (then restart slave) Luc -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Relationships - Foreign Key
* Paulo Hi, sorry for my english, but, how can I do relationships between tables? Is possible? This is done using different types of JOIN: URL: http://www.mysql.com/doc/en/JOIN.html -- Roger -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to write this query
Sean, Slight rewriting of Kevin's query--I assume you want to do the joins on A_ID. SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.A_ID LEFT JOIN C ON A.A_ID = C.A_ID WHERE A.A_ID = 4; This should work. For your example, the first left join gives a table with A.* and nulls for B.*. Then, the second left join gives you C.* for that A_ID; it doesn't matter that the B.* part contains nulls. Bill From: sean peters [EMAIL PROTECTED] To: Kevin Fries [EMAIL PROTECTED], [EMAIL PROTECTED] Subject: Re: How to write this query Date: Wed, 1 Oct 2003 16:22:46 -0500 Unfortunately that wont always work either. For instance, assume that there is an A record with A_ID = 4 And that there is a C record where A_ID = 4, but NO B record where A_ID = 4 So, executing the query: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; When A left joins B, there is no real B record, so any B columns are populated with null, as per left join. Then, table B is left joined to C on A_ID, which is null, and no C record will properly match the B.A_ID = NULL, so the C record is filled with nulls. If we were to join A to C then to B, a similar problem would occur if there was a cooresponding B record, but no C record. Thanks anyway. On Wednesday 01 October 2003 14:25, Kevin Fries wrote: You're on the right track with LEFT JOIN. Just continue the thought... Try: SELECT A_data, B_data, C_data FROM A LEFT JOIN B ON A.A_ID = B.B_ID LEFT JOIN C ON A.A_ID = C.C_ID WHERE A.A_ID = 4; -Original Message- From: sean peters [mailto:[EMAIL PROTECTED] Sent: Wednesday, October 01, 2003 12:07 PM To: [EMAIL PROTECTED] Subject: How to write this query I've run into a situation where i dont know how to best write a query. For a base example, consider these 3 tables: CREATE TABLE A ( A_ID INT NOT NULL PRIMARY KEY, A_data text ); CREATE TABLE B ( B_ID INT NOT NULL PRIMARY KEY, A_ID INT NOT NULL, B_data text ); CREATE TABLE C ( C_ID INT NOT NULL PRIMARY KEY, A_ID INT NOT NULL, C_data text ); So ive been running a query like: SELECT A_data, B_data, C_data FROM A, B, C WHERE A.A_ID = B.B_ID AND A.A_ID = C.C_ID AND A.A_ID = 4; What i really want is to get the A_data from A, and if there are cooresponding records in B and/or C, get B_data and/or C_data, respectively. This works fine if there are cooresponding records in tables B and C for each record in A, but if not, this returns nothing. So, short of querying each table, i cant come up with a good solution to my problem. If there were only 2 tables, a LEFT JOIN would work fine, but both B and C want to be left joined to A, which i dont know how to do. thanks sean peters [EMAIL PROTECTED] --- mysql, query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql? [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Why does mySQL violate NOT precedence?
I think the difference between mysql's precedence operation is significant to point out. Looking at the behavior of other databases, SQL Server and Oracle both respond to the query you gave with Recordss about users with names != 'Bob'. Jane returned. Significantly, this is inversion does not include NULL, in case anyone thought it would. Searching newsgroups, it appears this precendence in mySQL has been around for at least 5 years. I saw a post (written by Monty himself) asserting it. That might give us the poor-man's answer as to why the precendence is as it is: because it always has been. My suggestion? Work around the problem by always wrapping the predicate of NOT with parens. Also, submit the issue as a test within Crash-me. Potential converts to mysql will want to beware of the difference, even though I suspect most users will never notice the difference, because your usage is an unusual construct. IMHO, anyway. -Original Message- From: Ed Smith [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 7:22 AM To: [EMAIL PROTECTED] Subject: Re: Why does mySQL violate NOT precedence? Your point does not seem relevant to my question on mySQL compliance with the specification. In fact, your response doesn't even mention the specification. Perhaps I'm misunderstanding your question. You are wondering why I would ask a query like SELECT * FROM Person WHERE NOT name = 'Bob'? Might I ask such a query to get the list of all people who are not named Bob? Also, my question involves mySQL's conformance to the SQL specification. Your demonstration of what mySQL does with SELECT not name... seems irrelevant if it's mySQL's compliance that is in question. I'm not sure why you would use the syntax you are trying to use. I think you are making a few assumptions that may be incorrect. If you do this query: SELECT not name='Bob' FROM person You'll see your result is set contains all 0's. Records where name='Bob' would return 1, but you are taking the opposite of 1, which would be 0. For those records that return 0, you want the opposite of 0, which is? Infinity? If you convert infinity to boolean, it would be false. So from my point of view, MySQL is using the correct precedence. If you want to find all records that are not Bob, why not just us this syntax: SELECT * FROM person WHERE name!='Bob' It's more efficient since there is only one comparison occurring instead of two, negating name='Bob'. After all, you are looking to negate the equal, not negate name. On Friday, October 3, 2003, at 07:42 AM, Ed Smith wrote: In mySQL 4.1-alpha, 4.0.15a, and 3.23.58, I get the following results: mysql SELECT * FROM person WHERE NOT name = 'Bob'; Empty set (0.00 sec) mysql SELECT * FROM person WHERE NOT (name = 'Bob') +--+ | name | +--+ | Jane | +--+ 1 row in set (0.00 sec) -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Getting MySQL activity as a per database value
Folks, I've done a lot of searches, tried out the tools I could find (like phpMyAdmin/mtop). We have a server with about 30 DB's and we would LOVE to get a report that gives us queries/hour, cpu, etc.. against each DB so that we can measure activity. We are running 4.0.13 on Redhat 7.2 -- we can see some commands added to limit activity on a per DB basis. So I assume there is some counting going on somewhere. Is there any way to access those values? Right now the only brute force method we have is to turn on query logging for sample intervals and then count what we see there (but it is a gross solution). Does this exist somewhere? Are other adminstrators looking for this type of info on a per BD basis? We have considered actually contributing some engineering effort to get into the source code to make such data available. Best regards! -- John ___ John Murtari Software Workshop Inc. [EMAIL PROTECTED] 315.695.1301(x-211)TheBook.Com (TM) http://www.thebook.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication MASTER-SLAVE
Hi everbody! I' m trying to do a replication, and this is the first time!! My MySQL is running in Windows XP (master and slave). Where are my errors? Can I configure a Master in Linux and a Slave in Windows? #Master Configuration ### MySQL Server ### [mysqld] basedir=D:/Programas/mysql 4.0.15 #bind-address=192.168.10.30 datadir=D:/Programas/mysql 4.0.15/data #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=D:/Programas/mysql 4.0.15/bin/mysqld-max.exe user=user password=passwd ### Master ### log-bin=1 binlog-do-db=database server-id=1 #Slave Configuration ### MySQL Server ### [mysqld] basedir=C:/mysql #bind-address=192.168.10.31 datadir=C:/mysql/data #slow query log#= #tmpdir#= #port=3306 #set-variable=key_buffer=16M [WinMySQLadmin] Server=C:/mysql/bin/mysqld-max.exe user=user password=passwd ### Slave ### master-host=192.168.10.30 master-user=rep master-password=passwd server-id=2 replicate-do-db=database Thanks for you all!! Fernando Bernardino
Re: mysqld crash
Richard, looks like a clean case of memory corruption. Your log sequence number is only 29 MB. You got problems very quickly. I regularly run tests where gigabytes of log are generated, and no crash. Linux-2.4.20 has the reputation of being stable. This might even be a hardware problem. 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 MySQL Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Richard Runds [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Friday, October 03, 2003 4:24 PM Subject: mysqld crash Description: Upon starting mysql client with the following command: mysql -ppassword radiant Immediately error message where displayed, one per table, saying that the table existed, however no columns/fields where found. Unfortunately I do not have the logs of this, as these logs where to stdout, rather than the log file. However, the log file did contain a difference set of errors, at this time, and is included in this mail. Here is the .err file from the machine: 030819 15:42:06 mysqld started 030819 15:42:06 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 031002 13:20:35 InnoDB: Assertion failure in thread 800935949 in file ut0mem.c line 157 InnoDB: Failing assertion: block-magic_n == UT_MEM_MAGIC_N 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=8388600 read_buffer_size=131072 max_used_connections=22 max_connections=100 threads_connected=4 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=0x41714fd8 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... Cannot determine thread, fp=0xbfe1f308, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x807474f 0x82a0ad8 0x8244c7d 0x8243028 0x8241c21 0x813c3d4 0x80d6899 0x8097901 0x809517c 0x828a3b5 0x80952ee 0x807ef22 0x80844ee 0x807d79f 0x829e28c 0x82d199a New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://www.mysql.com/doc/en/Using_stack_trace.html and follow instructio ns on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it Trying to get some variables. Some pointers may be invalid and cause the dump to abort... thd-query at 0x88bbc30 = thd-thread_id=9632717 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. Number of processes running now: 0 031002 13:20:36 mysqld restarted 031002 13:20:36 InnoDB: Database was not shut down normally. InnoDB: Starting recovery from log files... InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 0 28715608 InnoDB: Doing recovery: scanned up to log sequence number 0 28715608 031002 13:20:36 InnoDB: Flushing modified pages from the buffer pool... 031002 13:20:36 InnoDB: Started /usr/sbin/mysqld: ready for connections. Version: '4.0.14-standard' socket: '/var/lib/mysql/mysql.sock' port: 3306 Here is the symbol dump of the backtrace: 0x807474f handle_segfault + 423 0x82a0ad8 pthread_sighandler + 184 0x8244c7d ut_free + 209 0x8243028 mem_area_free + 48 0x8241c21 mem_heap_block_free + 385 0x813c3d4 row_prebuilt_free + 444 0x80d6899 close__11ha_innobase + 25 0x8097901 closefrm__FP8st_table + 45 0x809517c free_cache_entry__FP8st_table + 36 0x828a3b5 hash_delete + 813 0x80952ee close_thread_tables__FP3THDb + 174 0x807ef22 dispatch_command__F19enum_server_commandP3THDPcUi + 3886 0x80844ee do_command__FP3THD + 154 0x807d79f handle_one_connection + 635 0x829e28c pthread_start_thread + 220 0x82d199a thread_start + 4 How-To-Repeat: We have not been able to recreate this problem. We have logged into the mysql client many times since, and have not seen this error again. Fix: None. Submitter-Id: Originator: root Organization: Richard Runds MySQL support: none Synopsis: mysqld on off crash Severity: critical Priority: high Category: mysql Class: sw-bug
Re: How to show locks on a table?
On Fri, Oct 03, 2003 at 03:28:31PM +0400, [EMAIL PROTECTED] wrote: Subject Body: There isn't a way. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 714,889,039 queries (419/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication
---Original Message- --From: Luc Foisy [mailto:[EMAIL PROTECTED] --Sent: Friday, October 03, 2003 8:45 AM --To: MYSQL-List (E-mail) --Subject: Replication -- -- --If I have replication already active for a single database ( already --active between master and slave ) and I want to start replicating a new --database on the master, what would the correct procedure be? --I have a dump of the database I want to replicate. Below is the current --setup for replication. -- --Master my.cnf --log-bin --binlog-do-db=database1 --server-id=1 -- --Slave my.cnf --master-host=master.address --master-user=replicant2 --master-password=password --replicate-do-db=database1 --server-id=3 -- -- --Would I just do the following? -- --Master my.cnf --binlog-do-db=database2 (then restart master) -- --load dump into slave db --Slave my.cnf --replicate-do-db=database2 (then restart slave) Sound correct. Just make sure you start the slave at the proper bin log position that the master left off with. -- --Luc -- --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]
changing TMPDIR for tempory file location
Hello all, My query is about changing the tmpdir on mysql. As stated in on the mysql.com site it states that if it is not set it uses the default, in my case, the default was /tmp. It also states that to change it you can do so in the mysqld_safe startup file. It makes no mention of changing it in the my.cnf file. My questions are: 1) Can you set the tmpdir in the conf file my.cnf? if so, how? 2) if you can only make that change in mysqld_safe, how do you? I did not mention the change at command line, e.g. mysqld_safe -tmpdir=/whatever because I don't want to have to change the startup scripts. Thanks for the help, dan
configure string for solaris?
Where can I find the configure string MySQL AB uses to compile their solaris binaries? -James -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Getting MySQL activity as a per database value
On Fri, Oct 03, 2003 at 08:29:17AM -0400, John Murtari wrote: Folks, I've done a lot of searches, tried out the tools I could find (like phpMyAdmin/mtop). We have a server with about 30 DB's and we would LOVE to get a report that gives us queries/hour, cpu, etc.. against each DB so that we can measure activity. Can't easily do that today, since MySQL doesn't track many per-db stats. We are running 4.0.13 on Redhat 7.2 -- we can see some commands added to limit activity on a per DB basis. So I assume there is some counting going on somewhere. Is there any way to access those values? Really? There are some per-user limits: http://www.mysql.com/doc/en/GRANT.html But I may have missed per-db limits. Where are they documented? Right now the only brute force method we have is to turn on query logging for sample intervals and then count what we see there (but it is a gross solution). Indeed, that's an ugly solution. Does this exist somewhere? Are other adminstrators looking for this type of info on a per BD basis? We have considered actually contributing some engineering effort to get into the source code to make such data available. That would be excellent. :-) -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 715,556,051 queries (418/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
thread problem
Hi list, I have the following problem and here is the message that displays the console when I try to connect Warning: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent Greetings Mikel _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
FW: changing TMPDIR for tempory file location
-Original Message- From: dan orlic [mailto:[EMAIL PROTECTED] Sent: Friday, October 03, 2003 1:20 PM To: '[EMAIL PROTECTED]' Subject: changing TMPDIR for tempory file location Hello all, My query is about changing the tmpdir on mysql. As stated in on the mysql.com site it states that if it is not set it uses the default, in my case, the default was /tmp. It also states that to change it you can do so in the mysqld_safe startup file. It makes no mention of changing it in the my.cnf file. My questions are: 1) Can you set the tmpdir in the conf file my.cnf? if so, how? 2) if you can only make that change in mysqld_safe, how do you? I did not mention the change at command line, e.g. mysqld_safe -tmpdir=/whatever because I don't want to have to change the startup scripts. Thanks for the help, dan
RE: changing TMPDIR for tempory file location
In my.cnf its called tmpdir change that location tmpdir=where you want tmp to be - Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: dan orlic [mailto:[EMAIL PROTECTED] --Sent: Friday, October 03, 2003 11:20 AM --To: [EMAIL PROTECTED] --Subject: changing TMPDIR for tempory file location -- --Hello all, -- -- My query is about changing the tmpdir on mysql. As stated in on the --mysql.com site it states that if it is not set it uses the default, in --my case, the default was /tmp. It also states that to change it you can --do so in the mysqld_safe startup file. It makes no mention of changing --it in the my.cnf file. -- --My questions are: -- --1) Can you set the tmpdir in the conf file my.cnf? if so, how? --2) if you can only make that change in mysqld_safe, how do you? -- --I did not mention the change at command line, e.g. mysqld_safe ---tmpdir=/whatever because I don't want to have to change the startup --scripts. -- --Thanks for the help, -- --dan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: thread problem
On Fri, Oct 03, 2003 at 02:37:27PM -0500, Mikel - wrote: Hi list, I have the following problem and here is the message that displays the console when I try to connect Warning: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent What OS are you using? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 719,013,228 queries (418/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Multiple-Column Unique Index Redundancy
I've been over a few books, the site, and the archives... Can someone please confirm that if I create a unique index over 2 columns, it will be redundant to create a regular index for the first? In other words, does the left-prefix rule apply to unique indexes that specify uniqueness over multiple columns? Thanks in advance for taking a few minutes for me! John Kornet -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Multiple-Column Unique Index Redundancy
On Fri, Oct 03, 2003 at 06:06:10PM -0400, John Kornet wrote: I've been over a few books, the site, and the archives... Can someone please confirm that if I create a unique index over 2 columns, it will be redundant to create a regular index for the first? It is redudndant, yes. In other words, does the left-prefix rule apply to unique indexes that specify uniqueness over multiple columns? Yes. A unique index is a normal index with an additional constraint. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 719,668,581 queries (418/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Is disk I/O more important ? Frequently. open a terminal window and run top while the query is running. If mysqld is using 100% of a cpu on a single process then the query is CPU bound. If it's using signficantyly less (e.g. 30%) then it's probably disk bound. The solutions to being disk bound can be lots of things: 1) Better indexing 2) More RAM (the G5 will help here as it can go past 2 GB) 3) Faster disks, the G5's faster drives and faster bus will help In general #1 is far and away the biggest factor, you can speed up queries by a factor of thousands or more. The bad temper of my boss seems to increase exponentially with time and he thinks that 2 weeks for importing the 27 million rows and indexing is too slow (he doesnt know anything about informatics, but as i am missing experience i cannot say if he is right or not). I don't know the structure, but that order of magnitude is doable in much less time. We imported 30 million records on a server running other queries in less than 3 hours. However, it was an InnoDB table and there were only numeric fields in it. You can probably improve things by tweaking your table structure and my.cnf file. There's a lot of detail in the mysql manual on the web. Good luck, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Newbye speed question - which setup to use for indexing
Andy Eastham wrote: How big are the table and index files? Can your OS handle files bigger than 2/4Gb? Yes, OS X can deal with files larger than 4 GB. --Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question
I wonder why you have 19 fields in a single table. I don't think there is really any way to predict it, since it depends on so many different factors. On Fri, 3 Oct 2003 11:19:56 +0200, Peer Reiser [EMAIL PROTECTED] wrote: | Fortunately the database is not in production :-) but it should go as | soon as possible :-( | | | On viernes, octu 3, 2003, at 11:03 Europe/Madrid, Wang Feng wrote: | | 18hrs??? So, the database has been LOCKED for 18hrs | | | - Original Message - | From: Peer Reiser [EMAIL PROTECTED] | To: [EMAIL PROTECTED] | Sent: Friday, October 03, 2003 6:57 PM | Subject: Newbye speed question | | | HI | | I am having a MYISAM database with 27 million rows and 19 fields all | char between 15 and 1 characters long. | | yesterday i did a | alter table mytablename add column (version char(2)); | By now (18 hours later) it has not finished yet? | Is there a way to somehow predict the time needed for this? Or to see | the status? | Or anyone has some experiense? | | thanx | | | -- | 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: Multiple-Column Unique Index Redundancy
- Dathan Vance Pattishall - Sr. Programmer and mySQL DBA for FriendFinder Inc. - http://friendfinder.com/go/p40688 ---Original Message- --From: John Kornet [mailto:[EMAIL PROTECTED] --Sent: Friday, October 03, 2003 3:06 PM --To: [EMAIL PROTECTED] --Subject: Multiple-Column Unique Index Redundancy -- --I've been over a few books, the site, and the archives... Can someone --please confirm that if I create a unique index over 2 columns, it will --be redundant to create a regular index for the first? It would be redundant. -- --In other words, does the left-prefix rule apply to unique indexes that --specify uniqueness over multiple columns? If col1 and col2 is a unique index col1 is just a reg index. http://www.mysql.com/doc/en/MySQL_indexes.html -- --Thanks in advance for taking a few minutes for me! --John Kornet -- -- --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: Newbye speed question - which setup to use for indexing
On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 719,887,477 queries (417/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: thread problem
I'm using linux, Red Hat 7.3 Linux version 2.4.22, and 3.23.55-Max-log. does This information is ok?, or I'm missing something. Greetings From: Jeremy Zawodny [EMAIL PROTECTED] Reply-To: [EMAIL PROTECTED] To: Mikel - [EMAIL PROTECTED] CC: [EMAIL PROTECTED] Subject: Re: thread problem Date: Fri, 3 Oct 2003 14:16:39 -0700 On Fri, Oct 03, 2003 at 02:37:27PM -0500, Mikel - wrote: Hi list, I have the following problem and here is the message that displays the console when I try to connect Warning: Can't create a new thread (errno 11). If you are not out of available memory, you can consult the manual for a possible OS-dependent What OS are you using? -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 19 days, processed 719,013,228 queries (418/sec. avg) _ MSN. Más Útil Cada Día http://www.msn.es/intmap/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just messed up my index on my table....ugh
now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
just messed up my index on my table....ugh
now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? :-) Thanks, Matt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(USING PASSWORD: NO) UGH!
I just reinstalled because of this same issue. Now root has no priviledges or functions within MySQL. Does anyone know how to fix this? My data isn't important -- i just started (and I am a hopeless newbie!) It doesn't let root do squat. I was trying to give 'root' a password. Entered the following command: mysqladmin -u root password Now root is gone. HELP! -Chris __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just messed up my index on my table....ugh
On Fri, Oct 03, 2003 at 04:23:56PM -0400, Matt Babineau wrote: now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? Drop and re-add the index? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ MySQL 4.0.15-Yahoo-SMP: up 20 days, processed 721,180,492 queries (417/sec. avg) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
(USING PASSWORD: NO) UGH 2nd try
I just reinstalled because of this same issue. Now root has no priviledges or functions within MySQL. Does anyone know how to fix this? My data isn't important -- i just started (and I am a hopeless newbie!) It doesn't let root do squat. I was trying to give 'root' a password. Entered the following command: mysqladmin -u root password Now root is gone. (probably not gone... but not working with a sh*t). HELP! OSX SERVER MYSQL Version 4.0.15 -Chris = Chris Ripley [EMAIL PROTECTED] KOZE Radio __ Do you Yahoo!? The New Yahoo! Shopping - with improved product search http://shopping.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: just messed up my index on my table....ugh
Its weird, I dropped the indexes off that table, and re-added them but the queries used to take 5 sections but now take much much longer. I did a little reading on the EXPLAIN command and it looks like my query without an index has 9427^15 rows to query. So thats where I am seeing the huge timeout come in. What are some general rules on indexing a table? I have a table like this: id, app_id, row_id, field_id, data int PRI KEY, int, int, int, blob when I query the table I am querying for an item in the data field, lets say I'm looking for 'widget' there are other records with the same row_id as widget so I also want to pull those out of the table too. I hope this makes some sense... Thanks, Matt On Fri, 2003-10-03 at 20:14, Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 04:23:56PM -0400, Matt Babineau wrote: now the query isn't finishing executing and its killing my cpu... any idea how to rebuild the index on a table? or how to get out of this mess? Drop and re-add the index? Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Newbye speed question - which setup to use for indexing
Jeremy Zawodny wrote: On Fri, Oct 03, 2003 at 06:23:24PM -0400, Ware Adams wrote: Peer Reiser wrote: Next week I will have access to a new PomerMac G5 with Dual 2GHZ processors, and i want to do some indexing. Does anyone know if MySQL will take advantage of dual processors if the only process running is the indexing process?? No, it won't directly. However, other processes going on will use the 2nd CPU (non-mysql processes) and if you run other queries they will use it. Really? About a year ago, when I asked an Apple engineer about theith SMP and threading support, he was able to convince me that it didn't suffer from the FreeBSD 4.x limitations. Have you seen documentation that really describes OS X's implementation? I'd love to know the truth. :-) I haven't seen any documentation, and I'm not sure I'm explaining things properly, but here's what I've observed running MySQL on a decent sized data set over almost a year on OS X: When only one query is active in MySQL (observed via show processlist, all connection IDs show 'sleep' except one) the mysqld process in top never shows more than 100% (or never more than 105-110% to be absolutely truthful) When multiple queries are active in MySQL the mysqld process frequently approaches 200% (assuming each can hit 100% when run on it's own) When a single MySQL query is active and another heavy load process is running on the machine (e.g. running rsync on a big directory) mysqld will go to 100% and the other process will approach the level it would hit without mysqld running This is on a G4 1.42GHz dual proc running OS X and hooked up to an XServe RAID. My conclusions from this were that MySQL on OS X cannot use more than one processor for a single query, but it uses multiple ones fine when it has multiple queries to process. Also, it performs fine sharing the two processors with other applications. Can MySQL use multiple processors for a single query on other OS's? It's pretty key for us as we tend to run relatively few, long duration queries as opposed to lots of quick ones. Thanks, Ware -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installing
I'm attempting to install using INSTALL-BINARIES and get this message while executing mysql_install_db: [cuppjr mysql]$ scripts/mysql_install_db scripts/mysql_install_db: ./bin/my_print_defaults: cannot execute binary file WARNING: The host 'raq2.homeunix.org' could not be looked up with resolveip. This probably means that your libc libraries are not 100 % compatible with this binary MySQL version. The MySQL deamon, mysqld, should work normally with the exception that host name resolving will not work. This means that you should use IP addresses instead of hostnames when specifying MySQL privileges ! Installing privilege tables scripts/mysql_install_db: ./bin/mysqld: cannot execute binary file Installation of grant tables failed! Examine the logs in ./data for more information. You can also try to start the mysqld daemon with: ./bin/mysqld --skip-grant You can use the command line tool ./bin/mysql to connect to the mysql database and look at the grant tables: shell ./bin/mysql -u root mysql mysql show tables Try 'mysqld --help' if you have problems with paths. Using --log gives you a log in ./data that may be helpful. The latest information about MySQL is available on the web at http://www.mysql.com Please consult the MySQL manual section: 'Problems running mysql_install_db', and the manual section that describes problems on your OS. Another information source is the MySQL email archive. Please check all of the above before mailing us! And if you do mail us, you MUST use the ./bin/mysqlbug script! Then if I try to run mysql or mysqld I get this: bash: /usr/local/mysql/bin/mysql: cannot execute binary file -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
