super-smack: aborting on failed query
[EMAIL PROTECTED] super-smack update-select.smack 30 100 Table 'http_auth' does not meet condtions, will be dropped Creating table 'http_auth' Loading data from file '/var/smack-data/words.dat' into table 'http_auth' Error running query load data infile '/var/smack-data/words.dat' into table http_auth fields terminated by ',':Can't get stat of '/var/smack-data/words.dat' (Errcode: 2) super-smack: aborting on failed query After finally getting super-smack to compile on FreeBSD, I ran into the error above when executing it. I am trying to connect to another mysql server on the LAN. I created the account that super-smack is using like this: GRANT ALL PRIVILEGES ON *.* TO root@192.168.123.101 identified by 'thepassword'; Any ideas here? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL Privileges
[EMAIL PROTECTED] wrote: I am running MySQL 4.1.1-alpha-standard on RH Linux 9. I've found strange problem with privileges: mysql grant reload on *.* to 'fabackup'@'localhost'; Query OK, 0 rows affected (0.00 sec) mysql grant create, insert, drop on mysql.ibbackup_binlog_marker to 'fabackup'@localhost; Query OK, 0 rows affected (0.01 sec) mysql show grants for [EMAIL PROTECTED]; +--+ | Grants for [EMAIL PROTECTED] | +--+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | | GRANT INSERT, CREATE, DROP ON `mysql`.`ibbackup_binlog_marker` TO 'fabackup'@'localhost' | +--+ 2 rows in set (0.00 sec) mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.01 sec) After shutting down the database with: [EMAIL PROTECTED] init.d]# mysqladmin -p shutdown Enter password: 040517 13:32:44 mysqld ended and restarting it with: [EMAIL PROTECTED] init.d]# /etc/rc.d/init.d/mysql start [EMAIL PROTECTED] init.d]# Starting mysqld daemon with databases from /var/lib/mysql [EMAIL PROTECTED] init.d]# mysql -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql show grants for [EMAIL PROTECTED]; +---+ | Grants for [EMAIL PROTECTED] | +---+ | GRANT RELOAD ON *.* TO 'fabackup'@'localhost' | +---+ 1 row in set (0.00 sec) The create, insert, drop privilege on mysql.ibbackup_binlog_marker has disappeared but still reported in mysql.tables_priv table.??!!! mysql use mysql Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql select * from tables_priv; +---+---+--+++-++-+ | Host | Db| User | Table_name | Grantor| Timestamp | Table_priv | Column_priv | +---+---+--+++-++-+ | localhost | mysql | fabackup | ibbackup_binlog_marker | [EMAIL PROTECTED] | 2004-05-17 13:28:02 | Insert,Create,Drop | | +---+---+--+++-++-+ 1 row in set (0.00 sec) mysql exit Bye When logon as fabackup cannot even access mysql database. [EMAIL PROTECTED] init.d]# mysql -ufabackup Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 2 to server version: 4.1.1-alpha-standard Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql use mysql ERROR 1044 (42000): Access denied for user: 'fabackup'@'localhost' to database 'mysql' mysql Am I doing something wrong here, or this is a bug. I've searched bug database, but could not find this reported. Any help will be apprecieated. It's a known bug. Fixed in version 4.1.2: http://bugs.mysql.com/bug.php?id=2546 http://dev.mysql.com/doc/mysql/en/News-4.1.2.html -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Super-smack compile errors
At 03:59 AM 5/17/2004 -0700, you wrote: On Intel - FreeBSD 4.9 STABLE - using MySQL Ver 11.18 Distrib 3.23.55, for portbld-freebsd4.7 (i386) On AMD64 - FreeBSD 5.2 CURRENT - using Mysql Ver 14.3 Distrib 4.1.1-alpha, for portbld-freebsd5.2.1 (amd64) I get the same compile error: # make make all-recursive Making all in src c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/local/include/mysql-g -O2 -c super-smack.cc c++ -DHAVE_CONFIG_H -I. -I. -I.. -I/usr/local/include/mysql-g -O2 -c client.cc client.cc: In method `void Client::thread_sync()': client.cc:347: `ERESTART' undeclared (first use this function) client.cc:347: (Each undeclared identifier is reported only once client.cc:347: for each function it appears in.) *** Error code 1 Stop in /root/super-smack-1.2/src. *** Error code 1 Stop in /root/super-smack-1.2. *** Error code 1 For the client.cc problem there appears to be a typo in the client.cc source: #ifndef __FreeBSD #define SUPER_SMACK_RESTART ERESTART Should probably be: #ifndef __FreeBSD__ #define SUPER_SMACK_RESTART ERESTART -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: WHERE pk1 AND pk2 returns 0 records
Brian Shearer [EMAIL PROTECTED] wrote: I have a simple query: SELECT * FROM cart WHERE CartID=999 AND ProdID=333 The primary key is made of both CartID and ProdID, both integers. The query will not return the record in question This returns the row: SELECT * FROM cart WHERE CartID=999 And this returns the same row: SELECT * FROM cart WHERE ProdID=333 But this returns zero records: SELECT * FROM cart WHERE CartID=999 AND ProdID=333 Any ideas? Check that queries SELECT * FROM cart WHERE CartID=999 and SELECT * FROM cart WHERE ProdID=333 return the same row. If so, recreate indexes. If it doesn't help create a repeatable test case. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[3.23.43] left join limitation?
Hello all, Using two tables : R1 : id data R2 : id pid type data R2.pid reference R1.id I would like to have a result set that is all the rows from R1 which are not referenced by any row from R2, but all this only for a certain type (R2.type) I've done this : SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.id is NULL; The problem of such a statement is that the result set show me all the rows from R1 which does not have ANY references in R2, and i would like to have all rows from R1, which does not have any rows of a certain type in R2 (lets say R2.type=D). Since this version of MySql doesn't support select statement inside another statement, i really don't know how i can do this. Thanks you for your help. -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Log partition filled up, binary logging stopped. How to recover?
MySQL 4.0.18, Tru64 5.1B The log partition filled up on one of our instances. RESET MASTER fails with: ERROR 1186: Binlog closed, cannot RESET MASTER I deleted some older logs by hand, but RESET MASTER still fails. FLUSH LOGS appears to succeed, but no binary logs are being written, and RESET MASTER still fails. I have one comment and one question about this: Question: Is there any way out of this rather than restarting the server? Comment: Data-modifying queries are still continuing successfully. This worries me.Without binary logs, and with data updates continuing, we don't have a way to restore the instance should the hardware fail. And if there were a replication slave running against this instance, it presumably would by now be far out of date, with no way of updating it without a complete resynchronisation from scratch. Surely if the binary update log can't be written, inserts and updates should either block, or fail completely? Or am I missing something? Many thanks, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [3.23.43] left join limitation?
SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.id is NULL; The problem of such a statement is that the result set show me all the rows from R1 which does not have ANY references in R2, and i would like to have all rows from R1, which does not have any rows of a certain type in R2 (lets say R2.type=D). SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.type != D; AFAIK 'null' != D, so this should include rows without any references plus rows with a reference, but not of type=D. Regards, Jigal. I tried this, but the result set is not correct. I have rows that does not have any references of type D (thats correct) but also some that have references of such a type D. when doing a left join, the result set is the rows seleted from R1, and the result set is the rows from R1 that are included in the join of the two tables. Adding a NULL condition will return all rows from R1 which does not have any references in R2. Instead of doing a NULL condition, adding a != D statement will remove from the result set all the rows from the join where the type is D3, but will keep, for the same R1.id all rows which are in the join. What i need is to have a result that list only the R1 rows which does not have any references of type D. Thank you for your help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Show table status query
Daniel Kasak wrote: Andrew Barnes wrote: When I run the SHOW TABLE STATUS query against a database with 4 tables, 3 of the tables come have the correct row count, but 1 table changes the No of rows every time I run the query. The correct row count for this table is 313, but the query returns anywhere from 97 to 574. I am running 4.0.13 on MAC OS/X 10.3 and the table is an INNODB table. Has anybody else ever seen this? Regards Andy Yes that's normal behaviour for an InnoDB table. It's in the docs somewhere. Note that the statistics SHOW gives about InnoDB tables are only approximate. They are used in SQL optimization. Table and index reserved sizes in bytes are accurate, though. http://dev.mysql.com/doc/mysql/en/Using_InnoDB_tables.html Regards, Stefan Hinz -- Stefan Hinz [EMAIL PROTECTED] iConnect e-commerce solutions GmbH Taunusstr. 27, 12161 Berlin, Germany -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with INTO OUTFILE
I had similar problem on W2000 - the solution is not to use ... field terminated by ';' ... but ...fields terminated by '\t'. I don't know why semicolon causes this problem, but If I use \t then it works. Jirka Matejka - Original Message - From: Jochen Kaechelin [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 4:30 AM Subject: Problem with INTO OUTFILE Can somebody tell me what's wrong with this query: $sql=SELECT * INTO OUTFILE 'infoanforderung/$nameliste' FIELDS TERMINATED BY ';' LINES TERMINATED BY '\n' FROM net_contact WHERE nachname='Erbel'; I get no results! Without INTO OUTFILE everything works fine! -- Jochen Kaechelin -- 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: [3.23.43] left join limitation?
SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.id is NULL; What i need is to have a result that list only the R1 rows which does not have any references of type D. Aha, you have more than one record in R2 which is linked to R1. Hmmm.. you can select the pid's from R2 which do have a type=D into a temporary table. Then you can select the R1 records which do not have any references in the temporary table. You'll end up with the R1 records that do not have any references in R2 or do not have a reference with type=D CREATE TEMPORARY TABLE `unique_name` SELECT pid FROM R2 WHERE R2.type = D; SELECT * FROM R1 LEFT JOIN `unique_name` AS t1 ON (R1.id=t1.pid) WHERE t1.pid IS NULL; DROP TABLE `unique_name` Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [3.23.43] left join limitation?
SELECT R1.* from R1 left join R2 on (R1.id=R2.pid) where R2.id is NULL; What i need is to have a result that list only the R1 rows which does not have any references of type D. Aha, you have more than one record in R2 which is linked to R1. Hmmm.. you can select the pid's from R2 which do have a type=D into a temporary table. Then you can select the R1 records which do not have any references in the temporary table. You'll end up with the R1 records that do not have any references in R2 or do not have a reference with type=D CREATE TEMPORARY TABLE `unique_name` SELECT pid FROM R2 WHERE R2.type = D; SELECT * FROM R1 LEFT JOIN `unique_name` AS t1 ON (R1.id=t1.pid) WHERE t1.pid IS NULL; DROP TABLE `unique_name` Regards, Jigal. Yes, you're right. Maybe i was not clear enought in my explanation. I wanted to know a method to do this without creating a temporary table, in a single statement. However, this seems not be possible with this version. (It require to be able to do multiple select in a single statement : Select .. From ... NOT IN ( SELECT ... from .. where...) ... ? So i think i'll do as you suggest. Thank you for your help -- Vincent -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Select on Multiple columns
Hi all, I have a trouble with multiple-column indexes, I will try to describe it with an example. Suppose I have a table like the following one: +++---+---+ | id_example | x_uno | x_due | x_desc| +++---+---+ | 1 | 2004-01-01 | 2 | qweqe | | 2 | 2004-01-01 | 3 | q | | 3 | 2004-01-01 | 4 | weqweqweq | | 4 | 2004-01-02 | 1 | QWEQWE| | 5 | 2004-01-02 | 2 | A | | 6 | 2004-01-02 | 3 | a | | 7 | 2004-01-02 | 4 | | | 8 | 2004-01-03 | 2 | 0 | | 9 | 2004-01-03 | 5 | | +++---+---+ where id_example is the Primary Key, and there is an Index on x_uno, x_due. How can I write a query in order to select records using a combination of columns x_uno and x_due? I would like, for example, to get the records starting from row number 6 (without, of course, working with the primary key, where the numbers could not necessarily be sorted as here): +++---+---+ | id_example | x_uno | x_due | x_desc | +++---+---+ | 6 | 2004-01-02 | 3 | a | | 7 | 2004-01-02 | 4 | | | 8 | 2004-01-03 | 2 | 0 | | 9 | 2004-01-03 | 5 | | +++---+---+ In fact, a select with the following syntax: SELECT * FROM `example` where x_uno=20040102 and x_due = 3 ORDER BY x_uno ASC, x_due ASC does not return me row number 8 (as it is 23 on column x_due). Of course I could use a new (redundant) column composed by the two ones, so that I have: ++---+--+---+---+ | id_example | x_composed | x_uno | x_due | x_desc| ++---+--+---+---+ | 6 | 20040102003 | 2004-01-02 | 3 | a | | 7 | 20040102004 | 2004-01-02 | 4 | | | 8 | 20040103002 | 2004-01-03 | 2 | 0 | | 9 | 20040103005 | 2004-01-03 | 5 | | ++---+--+---+---+ Anyway, I would not like to choose such a solution, as this situation would be frequent with the tables I am going to use. Is there something I am missing with SQL syntax? Thank you, Rocco Castino M.C.S. Computer Software S.r.l. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DISTINCT over a large number of rows
Hi, is mysql the right kind of database for: 1) trying to add ~1 000 000 tiny rows per day (each row about 100 bytes of data, about 20 bytes key) 2) adding only those rows which were not previously added on the same day (some kind of UNIQUE index), leaving about 100 000 unique records per day 3) selecting sum of DISTINCT rows for week, month, year once per hour, using WITH ROLLUP feature available in 4.1? All this on single-cpu x86 with 1Gb RAM and 15k rpm SCSI drive. What kind of storage backend should I use? Does it make sense to hold data for at least one day in RAM? Best Wishes, Viktors -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: super-smack on FreeBSD?
On Mon, May 17, 2004 at 08:43:44PM -0700, JG wrote: Has ANYONE been able to get super-smack-1.2 to compile as-is on FreeBSD? ./configure --with-mysql make make install Without errors? No, it required various code changes. A colleague of mine made the changes, I can probably get them off him if you need them. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
LDAP and MySQL?
Has anyone fooled around with LDAP and MySQL? Could the standard user table in mysql be replaced with a LDAP binding? Have not done any research but I think I remeber people talking about this some time ago. Or maybe I am mistaken ;). Anyone tried something like this? /Jonas
Re: super-smack on FreeBSD?
At 11:31 AM 5/18/2004 +0100, you wrote: On Mon, May 17, 2004 at 08:43:44PM -0700, JG wrote: Has ANYONE been able to get super-smack-1.2 to compile as-is on FreeBSD? ./configure --with-mysql make make install Without errors? No, it required various code changes. A colleague of mine made the changes, I can probably get them off him if you need them. PLEASE do Chris... I actually got it to compile after wrestling with it for awhile. But I can't get it to work with a remote server that doesn't have supersmack installed. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: super-smack on FreeBSD?
On Tue, May 18, 2004 at 03:46:46AM -0700, JG wrote: At 11:31 AM 5/18/2004 +0100, you wrote: On Mon, May 17, 2004 at 08:43:44PM -0700, JG wrote: Has ANYONE been able to get super-smack-1.2 to compile as-is on FreeBSD? ./configure --with-mysql make make install Without errors? No, it required various code changes. A colleague of mine made the changes, I can probably get them off him if you need them. PLEASE do Chris... I actually got it to compile after wrestling with it for awhile. But I can't get it to work with a remote server that doesn't have supersmack installed. 12:05 Freaky client.cc, just remove the #ifdef and the stuff in the #else is apparently all that's involved.. We only tried it on a local server though, so I don't know how to fix it working on a remote server.. -- Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB case sensitive collation
Matt Mastrangelo [EMAIL PROTECTED] wrote:
How can an InnoDB table be created with case sensitive collation? The
example below creates two identical tables, one MyISAM and the other
InnoDB. The InnoDB fails when inserting primary keys that differ in case
only. What am I doing wrong?
Which version do you use?
Worked fine for me on 4.1.2:
mysql INSERT INTO `table_02` VALUES ('victoria'), ('Victoria');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM `table_02`;
+--+
| tst_key |
+--+
| Victoria |
| victoria |
+--+
2 rows in set (0.00 sec)
Thanks.
drop database test;
create database test default character set latin1 default collate
latin1_general_cs;
CREATE TABLE `table_01` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=MyISAM DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
CREATE TABLE `table_02` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=InnoDB DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing integrity of Foreign Keys
Well, in order to enforce referential integrity, I converted the tables to InnoDB. But referential integrity is still not being enforced. I use mysql version 3-23-54-nt, and the tables are generated through the use of dbdesigner (ver. 4.0.5.6 beta). Here is a snippet of a create script: CREATE TABLE Types ( Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(Event_type) ) TYPE=InnoDB; CREATE TABLE Events ( event_id INTEGER NOT NULL, Types_Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(event_id), INDEX Events_FKIndex1(Types_Event_type), FOREIGN KEY(Types_Event_type) REFERENCES Types(Event_type) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; == Is there anything still wrong with this? ZA _ MSN Toolbar provides one-click access to Hotmail from any Web page FREE download! http://toolbar.msn.click-url.com/go/onm00200413ave/direct/01/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
'show warnings' is not recognized
I appologize if this is redundant for anyone who follows google groups... Here's my post: Via the tutorial at http://www.analysisandsolutions.com/code/mybasic.htm, I downloaded mySQL and have been running most things with sucess. In case it is pertinent, I used the mysqld-max-nt --install command to install. I have version 4.1 However, when I run a query that produces warnings, I am still not able to view them. I get your typical Error 1064 for bad syntax. However, I've confirmed from mysql.com that the syntax is correct. Any help would be appreciated. Kathy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Dropping tables...
I have to re-create a table every month with a section of data from the main table. I suppose this would be an excellent situation to use views for but being as how MySQL doesn't have view capability yet I'm stuck with this. It's not a big deal but I just want to make sure that Creating, populating, using, dropping a table every month won't impact my database performance. Is there any specific optimization or maintainence I should be doing after dropping the table each month? Thanks, Jeff -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RES: 'show warnings' is not recognized
Hi Katherine, Sometimes happens, unfortunately, things as this: http://dev.mysql.com/doc/mysql/en/SHOW_WARNINGS.html: Note that the framework for warnings was added in MySQL 4.1.0, at which point many statements did not generate warnings. In 4.1.1, the situation is much improved, with warnings generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE. I don't understand why, but... Greetings, Renato. -Mensagem original- De: Katherine Haines [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 18 de maio de 2004 09:56 Para: [EMAIL PROTECTED] Assunto: 'show warnings' is not recognized I appologize if this is redundant for anyone who follows google groups... Here's my post: Via the tutorial at http://www.analysisandsolutions.com/code/mybasic.htm, I downloaded mySQL and have been running most things with sucess. In case it is pertinent, I used the mysqld-max-nt --install command to install. I have version 4.1 However, when I run a query that produces warnings, I am still not able to view them. I get your typical Error 1064 for bad syntax. However, I've confirmed from mysql.com that the syntax is correct. Any help would be appreciated. Kathy -- 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 limits.
Folks, I have a couple of questions that I could not find the answer at the MySQL docs or list archives. Hope you guys can help me. We have a database with approximately 135 tables (MyISAM). Most of them are small, but we have 5 tables, with 8.000.000 records. And that number is to increase at least 1.000.000 records per month (until the end of the year, the growing rate might surpass 2.000.000 records/month). So, today our database size is 6GB. The server handles about 35-40 concurrent connections. We have a lot of table locks, but that does not seem to be a problem. Most of the time it works really well. From time to time (2 weeks uptime or so), we have to face a Signal 11 crash (which is pretty scary, since we have to run a myisamchk that takes us offline for at least 1 hour). We believe this signal 11 is related to the MySQL server load (since we have changed OS's and hardware -- RAM mostly). Our server is one P4 3GHz, 2GB RAM (400mhz), SCSI Ultra160 36GB disks (database only) running on OpenBSD 3.5. We are aware that OpenBSD might not be the best OS for this application... at first, it was chosen by it's security. Now we are looking (if that helps) to a OS with LinuxThreads (FreeBSD perharps?). The fact is that we are running MySQL on a dedicated server, that keeps the load between 0.5 and 1.5. CPU definitively is not a problem. The memory could be a problem... our key_buffer is set to 384M, according to the recommendations at my-huge.cnf. So, it seems we have a lot of free memory. We have already tried to increase key_buffer (along with the other settings), but it does not seem to hurt or to improve our performance (although, the memory use increases). To track down this signal 11, we have just compiled MySQL with debugandreturned totheoriginal my-huge.cnf recommendations. Now it seems we are running on a overclocked 486 66mhz. Is there any way to prevent this signal 11 to happen or is it a message that we have exceeded MySQL capability? Is MySQL able to handle such load with no problems/turbulences at all? If so, what would be the best hardware/OS configuration? What is the largest DB known to MySQL community? If it's needed, I can provide DMESG, MySQL error log, compile options and some database statistics. Thanks a lot for your help! Best regards, RV Tec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Mysql 4.0.18 crashed6384512
Philipp,
this is probably the bug that I fixed last night. It is present in all 3.23,
4.0 and 4.1 versions.
[EMAIL PROTECTED], 2004-05-18 01:53:06+03:00, [EMAIL PROTECTED]
mem0pool.c:
Fix a memory corruption bug: in 32-bit computers, every 4 billionth
malloc
outside innodb_additional_mem_pool_size was mistreated when freeing the
memory;
this could corrupt the InnoDB additional mem pool and could have caused
crashes
anywhere, also inside MySQL, or even database corruption! the bug exists
also in
3.23 and 4.1; workaround: configure innodb_additional_mem_pool_size big
enough
Best regards,
Heikki
Innobase Oy
InnoDB - transactions, row level locking, and foreign keys for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL support from http://www.mysql.com/support/index.html
..
From: Heikki Tuuri ([EMAIL PROTECTED])
Subject: Re: Mysql 4.0.18 crashed6384512
View: Complete Thread (2 articles)
Original Format
Newsgroups: mailing.database.myodbc
Date: 2004-04-06 12:27:44 PST
Philipp,
- Original Message -
From: Philipp Steinkrüger [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Wednesday, March 31, 2004 3:59 PM
Subject: Mysql 4.0.18 crashed6384512
--=_NextPart_000_002D_01C4172E.C75E7F90
Content-Type: text/plain;
charset=iso-8859-1
Content-Transfer-Encoding: quoted-printable
Hi,
today our mysql server 4.0.18-standard (binary) crashed.
I found a similar report (InnoDB: Assertion failure in thread), but it
was with 4.0.16 and one reply said that the bug was fixed in 4.0.17.
Here is the report from mysql.err:
InnoDB: Started
/usr/local/mysql/bin/mysqld: ready for connections.
Version: '4.0.18-standard' socket: '/var/run/mysql/mysql.sock' port: =
3306
InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the
InnoDB: previous allocated area!
InnoDB: Apparent memory corruption: mem dump len 500; hex =
666e6f7264f77318750f0a0d0805140f06090a0f07080b0835fd1cc68f690=
0011c0e250280001239ffeb6be40d0d0f0f140f0a0b0f0d0a3131323234343535=
363638383838050004050b0003ff818181426c756d656e6b6=
96e64657280003d03140a0a0a008000190a00=
00633a2f44572f647700028d4d0085898587777318d70=
0d0f56d94d0f56da1d0f56daed0f56dbbd0f56dc8d0f56dd5d0f56de2=
d0f56defd0f56dfcd0f56d09d1f56d16d1f56d23d1f56d30d1f56d9900107=
bf26d9000f63d775f9268a520773073656c2e6300a208=
8000400041300=
1303c623e3c693e636869736c656167202d2030342e30332e323030342031383a32352055=
68723a3c2f693e3c2f423e3c62723e646174756d20e46e6465726ef56d9800d905000=
0008e4172f8e1ec6dd005f63d775fb307952d78307472782e63008b00640bf56d=
720bf56d800bf56d8813da70207bf26dc0050100c0054=
000fe0bf56d70bbdc700816da70c875f26d2500; asc =
fnord.s.u.5i..%9..k1122334455=
66...Blumenkinder=3D.=
...c:/DW/dw.M.ws.m...m...m...m...m...m...=
m...m...m...m...m...m#..m0..m.{.m.=3Dw_.h. =
[EMAIL PROTECTED]@0.0bichislea=
g - 04.03.2004 18:25 Uhr:/i/Bbrdatum =
.ndern.m..Ar...m.=3Dw_...-x0trx.c.d..mr..m...m...p =
[EMAIL PROTECTED];
InnoDB: Scanning backward trying to find previous allocated mem blocks
Mem block at - 600, file w0sel.c, line 2273
Freed mem block at - 1352, file x0trx.c, line 153
Freed mem block at - 1696, file x0trx.c, line 139
Freed mem block at - 21496, file w0sel.c, line 2210
Freed mem block at - 27600, file x0trx.c, line 139
Freed mem block at - 30632, file w0sel.c, line 2210
Freed mem block at - 40648, file w0sel.c, line 2210
Freed mem block at - 40840, file x0trx.c, line 153
Freed mem block at - 42088, file x0trx.c, line 153
Freed mem block at - 42432, file x0trx.c, line 139
InnoDB: Scanning forward trying to find next allocated mem blocks
Freed mem block at + 16, file w0sel.c, line 2210
Mem block at + 168, file x0trx.c, line 139
Mem block at + 1664, file x0trx.c, line 139
Freed mem block at + 4696, file w0sel.c, line 2210
Freed mem block at + 28800, file x0trx.c, line 139
Freed mem block at + 29144, file x0trx.c, line 153
Freed mem block at + 30712, file 0data.c, line 153
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
outer join
Hello, I am working through a sql tutorial, and would like to perform this (written for oracle) outer join using mysql. SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name I am reading the docs, but do not understand. Could someone please give me a hand with this? Thanks, Scott Purcell -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 1-Way or 2-Way Replication?
Luis Wrote On Mon, May 17, 2004 at 04:07:53PM -0400, [EMAIL PROTECTED] wrote: SORRY My typo--- it is NOT ready for any kind of testing. At least I have not heard of anything Heh :) But do you know if anyone is already working on it? Luis /Luis Wrote I don't know of anyone yet. I don't try to keep up with the MySQL internals list, only this general list. You might try asking there. Shawn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: outer join
The (+) indicates an OUTER JOIN. This should work: SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1 LEFT JOIN Store_Information A2 ON A1.store_name = A2.store_name GROUP BY A1.store_name; Scott Purcell wrote: Hello, I am working through a sql tutorial, and would like to perform this (written for oracle) outer join using mysql. SELECT A1.store_name, SUM(A2.Sales) SALES FROM Georgraphy A1, Store_Information A2 WHERE A1.store_name = A2.store_name (+) GROUP BY A1.store_name I am reading the docs, but do not understand. Could someone please give me a hand with this? Thanks, Scott Purcell -- Mike Hillyer, Technical Writer MySQL AB, www.mysql.com Office: +1 403-686- The Open Source movement has become a major force across the software industry, and MySQL is the world's most popular open source database. --Fortune Magazine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unknown error, urgent help please
Hi, Before I go on, I know nothing about mysql, but my db admin is out of town and I need to be pointed in the right direction please. Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a virtual hosting environment on Ensim webppliance-3.5.20-7. When using phpMyAdmin and attempting to upload a csv file, the following error is the result: _ Database domain_ca_-_stats - Table location running on localhost Error SQL-query : LOAD DATA INFILE '/tmp/php7T1P2m' INTO TABLE `location` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Back The user in question has the appropriate permissions. Thanks about al I know. Im sure this is such a simple problem, but unfortunately this molehill is my mountain today. Thanks in advance, and my apologies for posting what will be such a simple problem. Ronnie Regev System Administrator Microsoft Certified Professional MCP Daslweb Inc. [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB case sensitive collation
I'm using version 4.1.1-alpha, running on RedHat Linux 9.
Victoria Reznichenko wrote:
Matt Mastrangelo [EMAIL PROTECTED] wrote:
How can an InnoDB table be created with case sensitive collation? The
example below creates two identical tables, one MyISAM and the other
InnoDB. The InnoDB fails when inserting primary keys that differ in case
only. What am I doing wrong?
Which version do you use?
Worked fine for me on 4.1.2:
mysql INSERT INTO `table_02` VALUES ('victoria'), ('Victoria');
Query OK, 2 rows affected (0.04 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql SELECT * FROM `table_02`;
+--+
| tst_key |
+--+
| Victoria |
| victoria |
+--+
2 rows in set (0.00 sec)
Thanks.
drop database test;
create database test default character set latin1 default collate
latin1_general_cs;
CREATE TABLE `table_01` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=MyISAM DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
CREATE TABLE `table_02` (`tst_key` varchar(10), PRIMARY KEY(`tst_key`))
Type=InnoDB DEFAULT CHARSET latin1 COLLATE=latin1_general_cs;
--
Matt Mastrangelo
X2 Development Corporation
781-740-2679
Re: Unknown error, urgent help please
Ronnie Regev [EMAIL PROTECTED] wrote: Hi, Before I go on, I know nothing about mysql, but my db admin is out of town and I need to be pointed in the right direction please. Running red hat 7.3, mysql-3.23.58-1.73, phpMyAdmin 2.5.0-rc2, in a virtual hosting environment on Ensim webppliance-3.5.20-7. When using phpMyAdmin and attempting to upload a csv file, the following error is the result: _ Database domain_ca_-_stats - Table location running on localhost Error SQL-query : LOAD DATA INFILE '/tmp/php7T1P2m' INTO TABLE `location` FIELDS TERMINATED BY ',' ENCLOSED BY '\'' ESCAPED BY '\\' LINES TERMINATED BY '\r\n' MySQL said: Access denied for user: '[EMAIL PROTECTED]' (Using password: YES) Back The user in question has the appropriate permissions. Thanks about al I know. Im sure this is such a simple problem, but unfortunately this molehill is my mountain today. Thanks in advance, and my apologies for posting what will be such a simple problem. Does user '[EMAIL PROTECTED]' have FILE privilege? -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Enforcing integrity of Foreign Keys
Zachary Agatstein [EMAIL PROTECTED] wrote: Well, in order to enforce referential integrity, I converted the tables to InnoDB. But referential integrity is still not being enforced. I use mysql version 3-23-54-nt, and the tables are generated through the use of dbdesigner (ver. 4.0.5.6 beta). Here is a snippet of a create script: CREATE TABLE Types ( Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(Event_type) ) TYPE=InnoDB; CREATE TABLE Events ( event_id INTEGER NOT NULL, Types_Event_type VARCHAR(20) NOT NULL, PRIMARY KEY(event_id), INDEX Events_FKIndex1(Types_Event_type), FOREIGN KEY(Types_Event_type) REFERENCES Types(Event_type) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; == Is there anything still wrong with this? CREATE TABLE statements look like Ok, but you should run mysqld-max or mysqld-max-nt servers on Windows if you want to use InnoDB tables and specify innodb_data_file_path variable: http://dev.mysql.com/doc/mysql/en/InnoDB_in_MySQL_3.23.html Note: ON UPDATE CASCADE is supported from version 4.0.8. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Encrypting Data???
Hello everyone, Is there a way to have the data in a table encrypted so that the MYD file can't be opened with a text editor and read??? I need to encrypt or some how make illegible numeric, date, text, and vchar fields.. Any idea??? Thanks for the help, Tom T -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ENC: 'show warnings' is not recognized
I've been to that page (http://dev.mysql.com/doc/mysql/en/SHOW_WARNINGS.html) and as you said, there are supposed to be warnings now. Do you have any ideas why I can't view them? Kathy Renato Cramer [EMAIL PROTECTED] 05/18/04 9:30 AM Hi Kathy, Excuse me for duplicity. Renato. -Mensagem original- De: Renato Cramer Enviada em: terça-feira, 18 de maio de 2004 10:24 Para: '[EMAIL PROTECTED]' Assunto: RES: 'show warnings' is not recognized Hi Katherine, Sometimes happens, unfortunately, things as this: http://dev.mysql.com/doc/mysql/en/SHOW_WARNINGS.html: Note that the framework for warnings was added in MySQL 4.1.0, at which point many statements did not generate warnings. In 4.1.1, the situation is much improved, with warnings generated for statements such as LOAD DATA INFILE and DML statements such as INSERT, UPDATE, CREATE TABLE, and ALTER TABLE. I don't understand why, but... Greetings, Renato. -Mensagem original- De: Katherine Haines [mailto:[EMAIL PROTECTED] Enviada em: terça-feira, 18 de maio de 2004 09:56 Para: [EMAIL PROTECTED] Assunto: 'show warnings' is not recognized I appologize if this is redundant for anyone who follows google groups... Here's my post: Via the tutorial at http://www.analysisandsolutions.com/code/mybasic.htm, I downloaded mySQL and have been running most things with sucess. In case it is pertinent, I used the mysqld-max-nt --install command to install. I have version 4.1 However, when I run a query that produces warnings, I am still not able to view them. I get your typical Error 1064 for bad syntax. However, I've confirmed from mysql.com that the syntax is correct. Any help would be appreciated. Kathy -- 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/InnoDB-4.0.20 is released
Hi! InnoDB is a MySQL table type that provides FOREIGN KEY constraints, transactions, row level locking and multiversioned concurrency control to MySQL, as well as a commercial hot backup tool. Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Functionality added or changed: * Make MySQL table locks (LOCK TABLES ... ) aware of InnoDB row locks on the same table. The MySQL table lock request now has to wait until conflicting InnoDB row locks are released. (Bug #3299) * Better error message when the server has to crash because the buffer pool is exhausted by the lock table or the adaptive hash index. * Print always the count of pending pread() and pwrite() calls if there is a long semaphore wait. Often a mysqld hang is caused by bugs in the operating system, or a hardware fault, and this can reveal it. * Improve space utilization when rows of 1,500 to 8,000 bytes are inserted in the order of the primary key. * Remove potential buffer overflow errors by sending diagnostic output to stderr or files instead of stdout or fixed-size memory buffers. As a side effect, the output of SHOW INNODB STATUS will be written to a file `datadir/innodb.status.pid' every 15 seconds. An outstanding bug: * If you configure innodb_additional_mem_pool_size so small that InnoDB memory allocation spills over from it, then every 4 billionth spill may cause memory corruption. A symptom is a printout like below in the .err log. The fix to this bug will be in 4.0.21. The workaround is to make innodb_additional_mem_pool_size big enough to hold all memory allocation. Use SHOW INNODB STATUS to determine that there is plenty of free space available in the additional mem pool, and the total allocated memory stays rather constant. InnoDB: Error: Mem area size is 0. Possibly a memory overrun of the InnoDB: previous allocated area! InnoDB: Apparent memory corruption: mem dump len 500; hex Bugs fixed: * Fixed race conditions in SHOW INNODB STATUS: it could cause a seg fault in innobase_mysql_print_thd(). A similar bug may have caused seg faults in MySQL's SHOW PROCESSLIST (Bug #3596) * Fixed a bug: DROP DATABASE did not work if FOREIGN KEY references were defined within the database. (Bug #3058) * Remove unnecessary files, functions and variables. Many of these were needed in the standalone version of InnoDB. * Remove debug functions and variables from non-debug build. * Add diagnostic code to analyze an assertion failure in ha_innodb.cc on line 2020 reported by a user. (Bug #2903) * Fixed a bug: in a FOREIGN KEY, ON UPDATE CASCADE was not triggered if the update changed a string to another value identical in alphabetical ordering, e.g., `abc' - `aBc'. * Protect the reading of the latest foreign key error explanation buffer with a mutex; in theory, a race condition could cause SHOW INNODB STATUS print garbage characters after the error info. * Fixed a bug: The row count and key cardinality estimate was grossly too small if each clustered index page only contained one record. * Parse CONSTRAINT FOREIGN KEY correctly. (Bug #3332) * Fixed a memory corruption bug on Windows. The bug is present in all InnoDB versions in Windows, but it depends on how the linker places a static array in srv0srv.c, whether the bug shows itself. 4 bytes were overwritten with a pointer to a statically allocated string `get windows aio return value'. * Fix a glitch reported by Philippe Lewicki on the general mailing list: do not print a warning to the `.err' log if read_key fails with a lock wait timeout error 146. * Allow quotes to be embedded in strings in the private SQL parser of InnoDB, so that `'' can be used in InnoDB table and column names. Display quotes within identifiers properly. * Debugging: Allow UNIV_SYNC_DEBUG to be disabled while UNIV_DEBUG is enabled. * Debugging: Handle magic numbers in a more consistent way. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Encrypting Data???
On Tue, 18 May 2004 10:38:25 -0400 Thomas Trutt [EMAIL PROTECTED] wrote: Hello everyone, Is there a way to have the data in a table encrypted so that the MYD file can't be opened with a text editor and read??? I need to encrypt or some how make illegible numeric, date, text, and vchar fields.. Any idea??? http://dev.mysql.com/doc/mysql/en/Encryption_functions.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL limits.
On 18 May 2004, at 2:28 pm, RV Tec wrote: Is MySQL able to handle such load with no problems/turbulences at all? If so, what would be the best hardware/OS configuration? What is the largest DB known to MySQL community? We regularly run databases with around 200 GB of data per instance, and up to 1000 simultaneous clients. Admittedly on slightly beefier machines than yours - usually 4-way AlphaServers running Tru64. You didn't say what version of MySQL you were using? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL and NPTL
Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Thanks! Steve Meyers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Problem with like wildcard syntax
I'm running version 4.1.1-alpha. The 3 select statements below on the
following test table produce inconsitent results:
create table test (test varchar(20)) charset latin1 collate
latin1_general_cs;
insert into test values ('abcField1');
insert into test values ('abcField2');
insert into test values ('abcField3');
insert into test values ('xyzField1');
insert into test values ('xyzField2');
insert into test values ('xyzField3');
select * from test where test like '___Field%'; /* Works */
select * from test where test like '%Fie%'; /* Works */
select * from test where test like '%Field%'; /* Does NOT work */
Am I doing something wrong?
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Fully compliant GIS
Mysql AB, What is the roadmap for fully compliant GIS? The OpenGIS specification defines the following functions, which MySQL does not yet implement. They should appear in future releases. When implemented, they will provide full support for spatial analysis, not just MBR-based support. Thanks, Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqldump and auto_increment
Hello I am using MySQL v4.0.18. I am using the mysqldump program to do backups. I was wondering how, if at all, does mysqldump preserver the auto_increment value? When using phpmyadmin, there is an option to preserve the auto_increment value and the resulting file has something like: ) TYPE=MyISAM COMMENT='Defines assignments' AUTO_INCREMENT=15 ; in the create table definition. When I run mysqldump and look at the resulting file, I do not see anything like this. Nor, can I find documentation on what phpmyadmin is doing. Thanks in advance! Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysqldump and auto_increment
Gabe, There are actually some problems caused by how auto increments are tracked in the database. We do our backups by using mysqldump at night and preserving all of the log files that were created during the day. The problem occurs during recovery where auto increment values can be incremented even though a record wasn't stored. In our case, that causes a situation where we add a row in an agent table, then using the value of the agent-id we create a number of rows in other tables. However, when that gets replayed in circumstances where we restore a mysqldump (effectively placing us at a begin-of-day point), then replay logs, we will link the subsequent records with other people rather than with the agent to whom they are supposed to be linked. It would be nice to have that resolved, but for now we are redesigning some of our processes to avoid the issue. Dave Christensen -Original Message- From: Tucker, Gabriel [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 11:36 AM To: Mysql General (E-mail) Cc: Philip Antoniades (E-mail) Subject: mysqldump and auto_increment Hello I am using MySQL v4.0.18. I am using the mysqldump program to do backups. I was wondering how, if at all, does mysqldump preserver the auto_increment value? When using phpmyadmin, there is an option to preserve the auto_increment value and the resulting file has something like: ) TYPE=MyISAM COMMENT='Defines assignments' AUTO_INCREMENT=15 ; in the create table definition. When I run mysqldump and look at the resulting file, I do not see anything like this. Nor, can I find documentation on what phpmyadmin is doing. Thanks in advance! Gabe Arise Arise A Rose A Rose Gabriel Tucker 609 750 6668 - P 646 268 5681 - F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
Re: super-smack: aborting on failed query
At 09:15 AM 5/18/2004 -0700, you wrote: does the myslqd user (probably mysql) have permissions to read the /var/smack-data directory? Cheers, Trevor JG wrote: Trevor, Yes No. I am doing a remote smack and super-smack is not installed on the remote server. (Should it be?) On the local server, the smacker server, the /var/smack-data directory is world readable. I am thinking the problem could have something to do with what is mentioned here: http://www.faqts.com/knowledge_base/view.phtml/aid/2817 I can't get supersmack to pass configure on the remote server, it is a linux AMD64 optimized Mandrake install and apparently doesn't have a library that supersmack is looking for. I'm hoping that super-smack-1.3 will come out soon and fix some of these bugs. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Problem with like wildcard syntax
At 11:53 -0400 5/18/04, Matt Mastrangelo wrote:
I'm running version 4.1.1-alpha. The 3 select statements below on
the following test table produce inconsitent results:
create table test (test varchar(20)) charset latin1 collate latin1_general_cs;
insert into test values ('abcField1');
insert into test values ('abcField2');
insert into test values ('abcField3');
insert into test values ('xyzField1');
insert into test values ('xyzField2');
insert into test values ('xyzField3');
select * from test where test like '___Field%'; /* Works */
select * from test where test like '%Fie%'; /* Works */
select * from test where test like '%Field%'; /* Does NOT work */
Am I doing something wrong?
Thanks.
I get the same results in 4.1.1, but in 4.1.2 the third SELECT returns
all six rows. I don't see any applicable bugfix item in the 4.1.2
change notes: http://dev.mysql.com/doc/mysql/en/News-4.1.2.html
However, so many problems are fixed that I may have missed it.
--
Paul DuBois, MySQL Documentation Team
Madison, Wisconsin, USA
MySQL AB, www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
setting default value based on another field value
I was wondering if there's a way to set a field to be a pattern of another field in the same table, ie: numberfile 1 xyz_1.jpg 2 xyz_2.jpg 3 xyz_3.jpg . . nxyz_n.jpg I'm achieving this through php but was curious to know if you could do it directly in MySQL as it would probably be much more efficient. rgds James
Re: setting default value based on another field value
James Tusini [EMAIL PROTECTED] wrote: I was wondering if there's a way to set a field to be a pattern of another field in the same table, ie: numberfile 1 xyz_1.jpg 2 xyz_2.jpg 3 xyz_3.jpg . . nxyz_n.jpg I'm achieving this through php but was curious to know if you could do it directly in MySQL as it would probably be much more efficient. No. Default values must be constant. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help with date queries
I'm looking for some queries or resources to help me create queries with these date ranges. IE, I have a DB that I want to pull things out of if they fall within this range. Since the beginning of this week Since the beginning of this business week 7 days from today This month to date Last Month A month (30 days) Since the beginning of this quarter Last Quarter Since the beginning of this year A year (365 days) Last Year Any assistance would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: setting default value based on another field value
At 18:07 +0100 5/18/04, James Tusini wrote: I was wondering if there's a way to set a field to be a pattern of another field in the same table, ie: numberfile 1 xyz_1.jpg 2 xyz_2.jpg 3 xyz_3.jpg . . nxyz_n.jpg I'm achieving this through php but was curious to know if you could do it directly in MySQL as it would probably be much more efficient. You have to do it yourself. However, if there is a fixed relationship between the two columns, there is probably little to gain by storing both of them. Just generate the filename from the number when you issue a retrieval statement. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL on embedded systems
Hi, I am installing MySQL to a single board computer with the ARM7 processor and 32M of RAM, running Linux. I was able to successfully cross compile and now run the MySQL server, but even with my-small.cnf and tweaking values down it consumes all but a few megs of my RAM, and I need at least 15M for other software that wasn't running during my testing. Here is my configure command: CC=arm-linux-gcc CXX=arm-linux-g++ CPP=arm-linux-cpp ./configure --host=arm-linux --prefix=/mnt/mmc/mysql --without-debug --without-extra-tools --without-docs --without-bench --without-query-cache And several of the make files had to be tweaked for it to compile. Here is a ps excerpt: 585 root812 S /bin/sh ./bin/mysqld_safe 636 root 23560 S /mnt/mmc/mysql/libexec/mysqld 637 root 23560 S /mnt/mmc/mysql/libexec/mysqld 638 root 23560 S /mnt/mmc/mysql/libexec/mysqld 639 root 23560 S /mnt/mmc/mysql/libexec/mysqld 640 root 23560 S /mnt/mmc/mysql/libexec/mysqld 641 root 23560 S /mnt/mmc/mysql/libexec/mysqld 643 root 23560 S /mnt/mmc/mysql/libexec/mysqld 644 root 23560 S /mnt/mmc/mysql/libexec/mysqld 647 root 23560 S /mnt/mmc/mysql/libexec/mysqld 648 root 23560 S /mnt/mmc/mysql/libexec/mysqld Any help/suggestions would be a huge help. Thanks! - Joe -- =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= [ Joseph Monti] [ [EMAIL PROTECTED] ] [ http://www.smartrobots.com/ ] =-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-= -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Alter table primary key and foreign keys
I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich
Re: MySQL limits.
Folks, Tim, Oops! Forgot to mention that... we are running MySQL 4.0.18. Thanks a lot! Best regards, RV Tec On Tue, 18 May 2004, Tim Cutts wrote: On 18 May 2004, at 2:28 pm, RV Tec wrote: Is MySQL able to handle such load with no problems/turbulences at all? If so, what would be the best hardware/OS configuration? What is the largest DB known to MySQL community? We regularly run databases with around 200 GB of data per instance, and up to 1000 simultaneous clients. Admittedly on slightly beefier machines than yours - usually 4-way AlphaServers running Tru64. You didn't say what version of MySQL you were using? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
Steve Meyers wrote: Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Steve: I have not heard much good about NPTL threads when used with MySQL at least :-) If LD_ASSUME_KERNEL trick does not do the job, I would recommend compiling a virgin 2.4 kernel from kernel.org and putting it on all of your servers. That might actually be a good idea anyway even if the trick does work. In three years of doing MySQL support I have learned this simple formula: RH kernel from 7.0 and newer + MySQL + high load = highly probable instability -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
authenticating mysql users from another source
I do a small web hosting company and one of the ongoing issues we have is keeping mysql user's passwords in sync with ldap/local passwords. Right now I have a script in place to try and keep things in sync when they change their local ldap password but it doesn't always work and it feels like a major security issue too. What would be nice is if there was a way to get mysql to either use pam or ldap to retrieve its passwords. This way no hacked sync'ing is needed. Anyone else have any suggestions. As far as I'm able to find, nothing like this exists and unfortunately I'm no programmer. Thanks -jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter table primary key and foreign keys
I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table is unaware of, and drops the original table. Have you tried rebuilding the child table? -Original Message- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Help with date queries
MySQL manual Chapter 13 Section 5 -Original Message- From: Charles King To: [EMAIL PROTECTED] Sent: 5/18/04 12:32 PM Subject: Help with date queries I'm looking for some queries or resources to help me create queries with these date ranges. IE, I have a DB that I want to pull things out of if they fall within this range. Since the beginning of this week Since the beginning of this business week 7 days from today This month to date Last Month A month (30 days) Since the beginning of this quarter Last Quarter Since the beginning of this year A year (365 days) Last Year Any assistance would be greatly appreciated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
Steve, I have had this same problem exept the entire server does not hang, only one thread which experiences a signficiat slowdown. This happens a couple times a week for our 4 servers. I believe this is a thread scheduling issue. The mysql support team suggests against using NPTL. However, I use mysqld-max on redhat linux 9.0 and have found a 10-20% performance difference at HIGH (hundreds of connections) load levels. The support team claims the difference in performance between linux threads and NPTL is never significant. Incidentally I have experinced this same thread starvation issue on redhat as 3.0 amd64 while using Linux threads. Theory is that glic 2.3 is to blame ( redhat 9.0 - glibc-2.3.2-11.9 , redhat as 3.0 - glibc-2.3.2-95.6 ). So the version of glibc looks to be the culprit. As I understand it Mysql is working with Redhat to determine the issues with glibc 2.3. I haven't tried the export line. Trevor Steve Meyers wrote: Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Thanks! Steve Meyers -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
Sasha Pachev said: Steve Meyers wrote: Has anyone else experienced this bug? http://bugs.mysql.com/bug.php?id=868 We've been seeing this problem on several of our servers (see the last comment to the bug). MySQL just hangs occasionally, it happens about 3-4 times per month. We have 13 database servers, so that unfortunately increases our odds. I just wondered how many other people have seen it, and if the suggested export LD_ASSUME_KERNEL=2.2.5; mysqld_safe has worked for anyone. In order to turn that on, I will need to take our site down completely, which is (of course) not desirable. Steve: I have not heard much good about NPTL threads when used with MySQL at least :-) If LD_ASSUME_KERNEL trick does not do the job, I would recommend compiling a virgin 2.4 kernel from kernel.org and putting it on all of your servers. That might actually be a good idea anyway even if the trick does work. In three years of doing MySQL support I have learned this simple formula: RH kernel from 7.0 and newer + MySQL + high load = highly probable instability -- Sasha Pachev Create online surveys at http://www.surveyz.com/ Any particular reason to use 2.4.x vs 2.6.x from kernel for base? -- William R. Mussatto, Senior Systems Engineer Ph. 909-920-9154 ext. 27 FAX. 909-608-7061 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
Heikki Tuuri wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Simple table, 1.7 million rows, very slow SELECTs
Here's my table:
CREATE TABLE 'data' (
'junk' char(10) NOT NULL default '',
PRIMARY KEY ('junk')
) TYPE=MyISAM;
There are about 1.7 million 10-character long strings. A query like this one
takes about 5 seconds:
SELECT junk FROM data WHERE junk='xx';
Subsequent queries for the same string return right away.
This is MySQL 4.0.18-5 from Debian testing on a dual Xeon 1.8Ghz with 512
ram and hardware raid5. Load from other services on this machine is minimal.
There is no other MySQL traffic at this time.
Is it normal for this to take so long? Grepping against a flat text file
representing my data takes a far less than a second. Any thoughts, folks?
--
Jacob Elder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: using host name option
Can you use the actual IP address and get it to work? If so, then ping localhost and ping tux and make sure the IP that you believe it should be is actually being returned. Lou - Original Message - From: Timothy Waters [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 12:24 AM Subject: using host name option I am having trouble using the -h option in my setup of MySQL. If I use '-h localhost' in the command it will work, but my hostname on my box is tux. If i use '-h tux' for the hostname option, it will not work. I double checked my /etc/hosts and everything is as it should be there. Is there anything I am doing wrong? What should I do to be able to connect to it on my LAN? Tim -- __ Check out the latest SMS services @ http://www.linuxmail.org This allows you to send and receive SMS through your mailbox. Powered by Outblaze -- 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: Simple table, 1.7 million rows, very slow SELECTs
What is the cardinality of the `junk` column? What does an EXPLAIN Plan
show?
-Original Message-
From: Jacob Elder
To: [EMAIL PROTECTED]
Sent: 5/18/04 1:22 PM
Subject: Simple table, 1.7 million rows, very slow SELECTs
Here's my table:
CREATE TABLE 'data' (
'junk' char(10) NOT NULL default '',
PRIMARY KEY ('junk')
) TYPE=MyISAM;
There are about 1.7 million 10-character long strings. A query like this
one
takes about 5 seconds:
SELECT junk FROM data WHERE junk='xx';
Subsequent queries for the same string return right away.
This is MySQL 4.0.18-5 from Debian testing on a dual Xeon 1.8Ghz with
512
ram and hardware raid5. Load from other services on this machine is
minimal.
There is no other MySQL traffic at this time.
Is it normal for this to take so long? Grepping against a flat text file
representing my data takes a far less than a second. Any thoughts,
folks?
--
Jacob Elder
--
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: Simple table, 1.7 million rows, very slow SELECTs
There are about 1.7 million 10-character long strings. A query like this one takes about 5 seconds: SELECT junk FROM data WHERE junk='xx'; Subsequent queries for the same string return right away. That's because you have the mysql query cache enabled and mysql can return the answer immediately without running the query at all. Is it normal for this to take so long? Grepping against a flat text file representing my data takes a far less than a second. Any thoughts, folks? - What is the output of EXPLAIN SELECT junk FROM data WHERE junk='xx'; ? - What if you OPTIMIZE data; ? Does it get any faster? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 08:49:05PM +0200, Jigal van Hemert wrote: There are about 1.7 million 10-character long strings. A query like this one takes about 5 seconds: SELECT junk FROM data WHERE junk='xx'; Subsequent queries for the same string return right away. That's because you have the mysql query cache enabled and mysql can return the answer immediately without running the query at all. Figured as much. Is it normal for this to take so long? Grepping against a flat text file representing my data takes a far less than a second. Any thoughts, folks? - What is the output of EXPLAIN SELECT junk FROM data WHERE junk='xx'; ? - What if you OPTIMIZE data; ? Does it get any faster? Regards, Jigal. I optimized the table, but the difference in speed is not signifigant. Average query times are still between 4.45 and 5.0 seconds. I've worked with MySQL in the past but never with so many rows. Is 1.7 million a lot for MySQL? Why would this be so much slower than grep? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 01:47:25PM -0500, Victor Pendleton wrote: What is the cardinality of the `junk` column? What does an EXPLAIN Plan show? mysql explain select junk from data where junk='xx'; +-+---+---+-+-+--+-+--+ | table | type | possible_keys | key | key_len | ref | rows| Extra | +-+---+---+-+-+--+-+--+ | junk| index | PRIMARY | PRIMARY | 10 | NULL | 1797425 | Using where; Using index | +-+---+---+-+-+--+-+--+ 1 row in set (0.00 sec) -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark wrote: | Heikki Tuuri wrote: | | |Release 4.0.20 is mainly a bugfix release, but there are also some |important functional changes. Release 4.0.19 was completely skipped |over because Bug #3596 might have caused segmentation faults on some |platforms. The changelog below lists all the changes since 4.0.18. | | | Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) | | - Mark | | There shouldn't be any problems with Perl BDB =) - -- ~ |...| ~ | _ _|Victor Medina M | ~ |\ \ \| | _ \ / \ |Linux - Java - MySQL | ~ | \ \ \ _| | |_) / _ \ |Dpto. Sistemas - Ferreteria EPA | ~ | / / / |___| __/ ___ \ |[EMAIL PROTECTED] | ~ |/_/_/|_|_| /_/ \_\|Tel: +58-241-8507325 - ext. 325 | ~ ||Cel: +58-412-8859934 | ~ ||geek by nature - linux by choice | ~ |...| - --- .- Este mensaje está digitalmente firmado para garantizar ~ su origen .- El intercambio de llaves públicas se realiza a petición ~ de las partes interesadas via e-mail - --- .- This message has been digitally signed .- Public Key (PGP or GPG) available upon request -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAql2Z8WJSBCrOXJ4RAgTXAKCrJDOV2vYXGrG61N3fYgYzjVe/MQCfcE41 GiZe0vHEYSHGyjHW9zPA6tk= =1zbO -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple table, 1.7 million rows, very slow SELECTs
From this explain plan it appears a full table scan will be done. What is the cardinality of this index? -Original Message- From: [EMAIL PROTECTED] To: Victor Pendleton Cc: '[EMAIL PROTECTED] ' Sent: 5/18/04 1:54 PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 01:47:25PM -0500, Victor Pendleton wrote: What is the cardinality of the `junk` column? What does an EXPLAIN Plan show? mysql explain select junk from data where junk='xx'; +-+---+---+-+-+--+-- ---+--+ | table | type | possible_keys | key | key_len | ref | rows | Extra| +-+---+---+-+-+--+-- ---+--+ | junk| index | PRIMARY | PRIMARY | 10 | NULL | 1797425 | Using where; Using index | +-+---+---+-+-+--+-- ---+--+ 1 row in set (0.00 sec) -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and NPTL
Any particular reason to use 2.4.x vs 2.6.x from kernel for base? 2.6 has not been around long enough to prove itself, in my opinion. I know 2.4 will work well, but I cannot say the same about 2.6 with the same degree of confidence. -- Sasha Pachev Create online surveys at http://www.surveyz.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter table primary key and foreign keys
The error log shows nothing when the binary dies. I can't rebuild the child table - anything that touches the child table after the alter stops the binary. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 2:07 PM To: 'Rich Schramm '; '[EMAIL PROTECTED] ' Subject: RE: Alter table primary key and foreign keys I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table is unaware of, and drops the original table. Have you tried rebuilding the child table? -Original Message- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich -- 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: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 02:03:55PM -0500, Victor Pendleton wrote: From this explain plan it appears a full table scan will be done. What is the cardinality of this index? Actually, they are phone numbers. I wasn't sure how this list would feel about this but I am wring a National Do-Not-Call Registry complaince tool for internal use at my company. We are a real estate agency, not telemarketers. We don't make a ton of cold calls but we don't want to get sued either. My table contains all do-not-call entries in Massachusetts. They are inserted in numeric order. Would it be faster if I broke it into columns for area code, exchange, and subscriber? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Doing MySQl DB File backups
I am working on doing backups of a MySQl server that is running approx 20 databases. Our failover is to do a nightly scp of all /data directory from one server to the backup. I know that replication or mysqldump would be best, but I do not want to get into that. My question is what is the best way to make sure that the files that are transferred are the latest data at the time of the scp? Does MySQL write to the data files when changes are made? Are they buffered? I though that a mysqladmin refresh before doing the scp might make a difference but I cannot verify it. Can someone enlighten me on this? Thanks, Ben Ricker - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Mark wrote: | Heikki Tuuri wrote: | | |Release 4.0.20 is mainly a bugfix release, but there are also some |important functional changes. Release 4.0.19 was completely skipped |over because Bug #3596 might have caused segmentation faults on some |platforms. The changelog below lists all the changes since 4.0.18. | | | Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) | | - Mark | | There shouldn't be any problems with Perl BDB =) -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.2 (GNU/Linux) Comment: Using GnuPG with Thunderbird - http://enigmail.mozdev.org iD8DBQFAqmHW8WJSBCrOXJ4RArrBAJ0fAxYqrL97+AIMjxOckIfLmk/4lACgp9H1 6836Z0JQKjc8st3BzKaD4vQ= =3Cqb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
Is that the only table in your MySQL installation?
MyISAM primary keys are put in a b-tree index, which is cached by MySQL
in memory in the key_buffer_size parameter. What is it set to on your
system (the my.cnf file, probably in /etc or /var)?
The second time you run it, the index is definately in memory, which is
why it is so fast.
Perhaps the OS is swapping MySQL pages out to disk, or perhaps there is
not enough memory allocated to the key-buffer to keep the index in memory.
The more frequently you access data, the more likely it is to be cached
by the OS or the database. Not sure what is running on your system or
how it is configured, but the amount of memory you have looks a bit
light. Databases are much faster with more memory.
David.
Jacob Elder wrote:
Here's my table:
CREATE TABLE 'data' (
'junk' char(10) NOT NULL default '',
PRIMARY KEY ('junk')
) TYPE=MyISAM;
There are about 1.7 million 10-character long strings. A query like this one
takes about 5 seconds:
SELECT junk FROM data WHERE junk='xx';
Subsequent queries for the same string return right away.
This is MySQL 4.0.18-5 from Debian testing on a dual Xeon 1.8Ghz with 512
ram and hardware raid5. Load from other services on this machine is minimal.
There is no other MySQL traffic at this time.
Is it normal for this to take so long? Grepping against a flat text file
representing my data takes a far less than a second. Any thoughts, folks?
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Change ft_min_word_len require fulltext index rebuild
Mysqlians, I just discovered that a server has ft_min_word_len=4 when I want ft_min_word_len=3 . Do I have to rebuild all the fulltext indicies so that searches on three-letter words will work correctly? Trevor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Change ft_min_word_len require fulltext index rebuild
At 12:30 -0700 5/18/04, Trevor Price wrote: Mysqlians, I just discovered that a server has ft_min_word_len=4 when I want ft_min_word_len=3 . Do I have to rebuild all the fulltext indicies so that searches on three-letter words will work correctly? Yes. Question: Did you find some place in the manual that suggests otherwise? If so, where was it? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Simple table, 1.7 million rows, very slow SELECTs
In this case creating separate columns and making the values integers as oppesed to varchar will increase your query speed. -Original Message- From: Jacob Elder To: Victor Pendleton Cc: '[EMAIL PROTECTED] '; ''[EMAIL PROTECTED] ' ' Sent: 5/18/04 2:11 PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 02:03:55PM -0500, Victor Pendleton wrote: From this explain plan it appears a full table scan will be done. What is the cardinality of this index? Actually, they are phone numbers. I wasn't sure how this list would feel about this but I am wring a National Do-Not-Call Registry complaince tool for internal use at my company. We are a real estate agency, not telemarketers. We don't make a ton of cold calls but we don't want to get sued either. My table contains all do-not-call entries in Massachusetts. They are inserted in numeric order. Would it be faster if I broke it into columns for area code, exchange, and subscriber? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Alter table primary key and foreign keys
Can you mysqldump the table then rebuild the table from the dump file? -Original Message- From: Rich Schramm To: 'Victor Pendleton'; [EMAIL PROTECTED] Sent: 5/18/04 2:04 PM Subject: RE: Alter table primary key and foreign keys The error log shows nothing when the binary dies. I can't rebuild the child table - anything that touches the child table after the alter stops the binary. -Original Message- From: Victor Pendleton [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 2:07 PM To: 'Rich Schramm '; '[EMAIL PROTECTED] ' Subject: RE: Alter table primary key and foreign keys I would first see if an upgrade to a later version of InnoDB tables is possible. What is being written to the error log? The ALTER TABLE statement subtly creates a new table, with new contraint names that the child table is unaware of, and drops the original table. Have you tried rebuilding the child table? -Original Message- From: Rich Schramm To: [EMAIL PROTECTED] Sent: 5/18/04 12:43 PM Subject: Alter table primary key and foreign keys I am using mysql 4.0.12 max-nt on Windows XP. I have a master table with an int column as a primary key (bom_id) and a second table that has a foreign key reference to the master column and uses it as part of a composite key (bom_id, fc_date). Example: ** bom_mstr primary key(bom_id) ** ** forecast primary key(bom_id, fc_date) FOREIGN KEY (bom_id) REFERENCES bom_mstr(bom_id) ON DELETE CASCADE ON UPDATE CASCADE ) TYPE=InnoDB; ** This works fine. I then altered the table so that the primary key in the master table is now an auto_increment: alter table bom_mstr modify bom_id int(10) auto_increment; Having done this, column is updated and the values for the records are set. I can insert into it and query it with no problem. However, when I try to do anything with the secondary table at this point, it crashes the entire mysql.exe process. Anything that touches the second table crashes the binary: select count(*) from forecast describe forecast delete from bom_mstr (which cascades to forecast). All of these crash the binary. I have also tried truncating the data in forecast before altering bom_mstr and I get the same result. Anyone seen this before or have any idea??? Thanks, Rich -- 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: Doing MySQl DB File backups
Are you using only MyISAM or InnoDB tables? With the InnoDB tables you have the possibility of data not yet being committed. Either way, unless you can flush, lock and perform the dump and transfer all in one swoop you risk incosistency using this method. -Original Message- From: Ben Ricker To: [EMAIL PROTECTED] Sent: 5/18/04 2:14 PM Subject: Doing MySQl DB File backups I am working on doing backups of a MySQl server that is running approx 20 databases. Our failover is to do a nightly scp of all /data directory from one server to the backup. I know that replication or mysqldump would be best, but I do not want to get into that. My question is what is the best way to make sure that the files that are transferred are the latest data at the time of the scp? Does MySQL write to the data files when changes are made? Are they buffered? I though that a mysqladmin refresh before doing the scp might make a difference but I cannot verify it. Can someone enlighten me on this? Thanks, Ben Ricker - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- 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: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 12:26:41PM -0700, David Griffiths wrote: Is that the only table in your MySQL installation? Yes, and no one has access to it yet but me. MyISAM primary keys are put in a b-tree index, which is cached by MySQL in memory in the key_buffer_size parameter. What is it set to on your system (the my.cnf file, probably in /etc or /var)? key_buffer_size does not appear in my.cnf. Is the default sensible for my setup? The second time you run it, the index is definately in memory, which is why it is so fast. Perhaps the OS is swapping MySQL pages out to disk, or perhaps there is not enough memory allocated to the key-buffer to keep the index in memory. The more frequently you access data, the more likely it is to be cached by the OS or the database. Not sure what is running on your system or how it is configured, but the amount of memory you have looks a bit light. Databases are much faster with more memory. David. There are other services on this machine, but the load is rarely above 0.05. I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here? The chances of a given row being returned more than once per day is very small, so caching the result doesn't help a lot. -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 02:48:45PM -0500, Victor Pendleton wrote: In this case creating separate columns and making the values integers as oppesed to varchar will increase your query speed. Okay, I'll give that a try. How do I set up my column types so that a leading 0 won't be stripped off? -Original Message- From: Jacob Elder To: Victor Pendleton Cc: '[EMAIL PROTECTED] '; ''[EMAIL PROTECTED] ' ' Sent: 5/18/04 2:11 PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 02:03:55PM -0500, Victor Pendleton wrote: From this explain plan it appears a full table scan will be done. What is the cardinality of this index? Actually, they are phone numbers. I wasn't sure how this list would feel about this but I am wring a National Do-Not-Call Registry complaince tool for internal use at my company. We are a real estate agency, not telemarketers. We don't make a ton of cold calls but we don't want to get sued either. My table contains all do-not-call entries in Massachusetts. They are inserted in numeric order. Would it be faster if I broke it into columns for area code, exchange, and subscriber? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Doing MySQl DB File backups
I believe all the tables are MyISAM...the DB is 3.2.x. I know there will be an inconsistency as we only do nightly backups; any transactions that occur before the scp will not be there. If we failover before the backup, we can have up to 24 hours of data missing. I am concerned that we will have MORE then 24 hours data missing because the DB may not (or may) write the data that has changed to the data files. Do you knif the mysqladmin refresh will force a write to the DB data files? Thanks, Ben Ricker Victor Pendleton [EMAIL PROTECTED]To: 'Ben Ricker ' [EMAIL PROTECTED], rs.com '[EMAIL PROTECTED] ' [EMAIL PROTECTED] cc: 05/18/2004 02:56 Subject: RE: Doing MySQl DB File backups PM Are you using only MyISAM or InnoDB tables? With the InnoDB tables you have the possibility of data not yet being committed. Either way, unless you can flush, lock and perform the dump and transfer all in one swoop you risk incosistency using this method. -Original Message- From: Ben Ricker To: [EMAIL PROTECTED] Sent: 5/18/04 2:14 PM Subject: Doing MySQl DB File backups I am working on doing backups of a MySQl server that is running approx 20 databases. Our failover is to do a nightly scp of all /data directory from one server to the backup. I know that replication or mysqldump would be best, but I do not want to get into that. My question is what is the best way to make sure that the files that are transferred are the latest data at the time of the scp? Does MySQL write to the data files when changes are made? Are they buffered? I though that a mysqladmin refresh before doing the scp might make a difference but I cannot verify it. Can someone enlighten me on this? Thanks, Ben Ricker - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running more than one level of MySQL
At 15:46 -0400 5/18/04, Robert A. Rosenberg wrote: I have a site that is being hosted by an ISP which is running version 3.23.52. When I questioned why that downlevel version and not a 4.0 version (such as 4.0.18 or the just released 4.0.20), I was told Unfortunately, when versions change on MySQL, they also drop features and change security settings. This can cause many problems system wide. Before I go further with my discussion and renew my request for a 4.0 Database, I would like to know if it is even possible to have more than one level active (and if so, what is involved in the set-up). It's perfectly possible. I have dozens of versions installed, though not all necessarily running at the same time. :-) http://dev.mysql.com/doc/mysql/en/Multiple_servers.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Running more than one level of MySQL
I just did an upgrade from 3.23.54a to 4.0.18 for 4 DB servers. It was a piece of cake. Some hints: 1) Run through the docs on changes from previous versions. Someone who understands the DBs you will be moving would be helpful to look at it as well. 2) that stuff about security settings is baloney. The higher the version, the more likely you are to have a MORE secure MySQL as they apply security and bug fixes to the later versions. 3) I ran both versions side-by-side listening on different ports. We did all the testing we needed on the different port and then all we had to do was bring down the old and bring up the new on the old port. 4). You will have some down time to do exports of the old database and to import to the new one. We used a mysqldump --opt --all-databases and then imported it after taking down the front-end. When it came back up, it was working transparently. Good luck. Ben Ricker -- Ben Ricker Web Administrator Mastercard International, Inc. 904 North Third (636) 722-4697 Robert A. Rosenberg To: [EMAIL PROTECTED] [EMAIL PROTECTED]cc: (bcc: Ben Ricker/STL/MASTERCARD) Subject: Running more than one level of MySQL 05/18/2004 02:46 PM I have a site that is being hosted by an ISP which is running version 3.23.52. When I questioned why that downlevel version and not a 4.0 version (such as 4.0.18 or the just released 4.0.20), I was told Unfortunately, when versions change on MySQL, they also drop features and change security settings. This can cause many problems system wide. Before I go further with my discussion and renew my request for a 4.0 Database, I would like to know if it is even possible to have more than one level active (and if so, what is involved in the set-up). Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] - CONFIDENTIALITY NOTICE This e-mail message and any attachments are only for the use of the intended recipient and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If you are not the intended recipient, any disclosure, distribution or other use of this e-mail message or attachments is prohibited. If you have received this e-mail message in error, please delete and notify the sender immediately. Thank you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. Do you remember your old DBD driver's version? Or was that only Win32 problem? - Original Message - From: Mark [EMAIL PROTECTED] To: Heikki Tuuri [EMAIL PROTECTED]; [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 2:21 PM Subject: Re: MySQL/InnoDB-4.0.20 is released Heikki Tuuri wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) - Mark -- 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: Simple table, 1.7 million rows, very slow SELECTs
If I am correct, the NUMERIC data type is a string representation and will
contain leading zeroes if input as a string.
...
CREATE TABLE numbersTest (numCol NUMERIC(3));
INSERT INTO numbersTest VALUES('212'), ('069'), ('070');
...
The values with leading zeroes should be returned.
...
Create an index and see if your speed increases.
-Original Message-
From: 'Jacob Elder '
To: '''[EMAIL PROTECTED] ' ' '
Sent: 5/18/04 3:05 PM
Subject: Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 02:48:45PM -0500, Victor Pendleton wrote:
In this case creating separate columns and making the values integers
as
oppesed to varchar will increase your query speed.
Okay, I'll give that a try. How do I set up my column types so that a
leading 0 won't be stripped off?
-Original Message-
From: Jacob Elder
To: Victor Pendleton
Cc: '[EMAIL PROTECTED] '; ''[EMAIL PROTECTED] ' '
Sent: 5/18/04 2:11 PM
Subject: Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 02:03:55PM -0500, Victor Pendleton wrote:
From this explain plan it appears a full table scan will be done.
What
is
the cardinality of this index?
Actually, they are phone numbers. I wasn't sure how this list would
feel
about this but I am wring a National Do-Not-Call Registry complaince
tool
for internal use at my company. We are a real estate agency, not
telemarketers. We don't make a ton of cold calls but we don't want to
get
sued either.
My table contains all do-not-call entries in Massachusetts. They are
inserted in numeric order. Would it be faster if I broke it into
columns
for
area code, exchange, and subscriber?
--
Jacob Elder
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
--
Jacob Elder
--
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: Simple table, 1.7 million rows, very slow SELECTs
Sorry, the variable is actually key_buffer_size (I don't use MyISAM); I'm not sure what it defaults to, but the typical recommendation is 25% of your memory. You can tell if your cache is effective by looking at the key_reads and key_read_requests (from the MySQL window, type SHOW STATUS without the quotes). If the key_reads/key_read_requests is = .01 then you need to allocate more memory to the key_buffer_size. For example our SHOW STATUS on a test database gives us: | Key_read_requests| 156689872 | | Key_reads| 445700 | Which is (445700 / 156689872), or 0.00284 (truncated), which is fine. One other thing I would recommend is turn off your query cache (I can almost hear the gasps from other members of this list). The query cache is designed to return the results of frequently executed queries (assuming you have enough memory allocated to the query cache to store the results). From the sounds of your database (one table with 1.7 million records), it sounds like no two identical queries will be run with any frequency (I am guessing that a fairly even distribution of rows will be selected - you'll rarely-if-ever select the same row out 8 times in 5 minutes outside of testing). If that's the case, turn off the query cache (query_cache_type = OFF in your my.cnf) and give that memory to something else. I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here? Your query has to be parsed, the index paged in from disk, a lookup done on the index, the disk accessed to find the row, format it, and return it. Plus there is the overhead of puttting the query and the result into the query cache. Grep just spins through the file. For a non-complicated task like this, grep is fast. When selecting hundreds of rows from dozens of tables with all sorts of criteria in the where clause, grep is not usable. MyISAM is fine for this sort of work (though I prefer InnoDB for the row-locking, etc). David Jacob Elder wrote: On Tue 18 May 02004 at 12:26:41PM -0700, David Griffiths wrote: Is that the only table in your MySQL installation? Yes, and no one has access to it yet but me. MyISAM primary keys are put in a b-tree index, which is cached by MySQL in memory in the key_buffer_size parameter. What is it set to on your system (the my.cnf file, probably in /etc or /var)? key_buffer_size does not appear in my.cnf. Is the default sensible for my setup? The second time you run it, the index is definately in memory, which is why it is so fast. Perhaps the OS is swapping MySQL pages out to disk, or perhaps there is not enough memory allocated to the key-buffer to keep the index in memory. The more frequently you access data, the more likely it is to be cached by the OS or the database. Not sure what is running on your system or how it is configured, but the amount of memory you have looks a bit light. Databases are much faster with more memory. David. There are other services on this machine, but the load is rarely above 0.05. I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here? The chances of a given row being returned more than once per day is very small, so caching the result doesn't help a lot. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
Jacob, Don't worry about storing the leading the zeroes. Just left pad the subscriber column to be 4 digits on output and you should be golden. MySQL has a function just to generate left-padded numbers: LPAD(subscriber,4,'0') Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine 'Jacob Elder ' [EMAIL PROTECTED] To: '''[EMAIL PROTECTED] ' ' ' [EMAIL PROTECTED] cc: 05/18/2004 04:05 Fax to: PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 02:48:45PM -0500, Victor Pendleton wrote: In this case creating separate columns and making the values integers as oppesed to varchar will increase your query speed. Okay, I'll give that a try. How do I set up my column types so that a leading 0 won't be stripped off? -Original Message- From: Jacob Elder To: Victor Pendleton Cc: '[EMAIL PROTECTED] '; ''[EMAIL PROTECTED] ' ' Sent: 5/18/04 2:11 PM Subject: Re: Simple table, 1.7 million rows, very slow SELECTs On Tue 18 May 02004 at 02:03:55PM -0500, Victor Pendleton wrote: From this explain plan it appears a full table scan will be done. What is the cardinality of this index? Actually, they are phone numbers. I wasn't sure how this list would feel about this but I am wring a National Do-Not-Call Registry complaince tool for internal use at my company. We are a real estate agency, not telemarketers. We don't make a ton of cold calls but we don't want to get sued either. My table contains all do-not-call entries in Massachusetts. They are inserted in numeric order. Would it be faster if I broke it into columns for area code, exchange, and subscriber? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL/InnoDB-4.0.20 is released
Mihail Manolov wrote: Release 4.0.20 is mainly a bugfix release, but there are also some important functional changes. Release 4.0.19 was completely skipped over because Bug #3596 might have caused segmentation faults on some platforms. The changelog below lists all the changes since 4.0.18. Will that require an upgrade from Perl DBD drivers as well? (like when I did when I moved from 3.23.58 to 4.0.18). I really hope not. :) Hmm... I didn't have to upgrade our DBD drivers when we moved from 3.23.57 to 4.0.18. Strange you had to. I very distinctly remember reading the onsite documentation which stated that, since the C headers were changed, relative to 3.23.x, that I needed to reinstall the DBD drivers as well (not just DBI). Which I did. Do you remember your old DBD driver's version? Not sure any more. But, like I said, I believe it was the header changing stuff that made upgrading a necessity. Or was that only Win32 problem? Dunno. I am running FreeBSD 4.9R. ;) - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Storage Solution Question
We have a MySQL server that is a backend processing server that in about 60 days will probably run out of disk space. The data cannot be archived off, because it is always used and changed many times a day. The server currently has 6 72 gig SCSI 15k drives in it. We have it raided with 2 drives together for the OS and tmp. And then the data drive is 204 gigs. We currently have 66 gigs free, and we are adding about 1.2 gigs of new data per day. I don't think we would ever get over a terabyte of data, but you never know. What are my options? What are solutions that people have used, that have worked? I just know whatever the solution is has to be fast! Because we do thousands of inserts and selects at the same time. Thanks. Donny
Re: Simple table, 1.7 million rows, very slow SELECTs
On Tue 18 May 02004 at 04:53:52PM -0400, [EMAIL PROTECTED] wrote: Jacob, Don't worry about storing the leading the zeroes. Just left pad the subscriber column to be 4 digits on output and you should be golden. MySQL has a function just to generate left-padded numbers: LPAD(subscriber,4,'0') Yours, Shawn Green Database Administrator Unimin Corporation - Spruce Pine I just came across ZEROFILL in the manual. When would I want to use LPAD rather than ZEROFILL? -- Jacob Elder -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Data Formatting - Newbie Questions
I think I've got most of the basics of MySQL under control now, and I'd like to ask a question about formatting table data. I've learned to insert html in my tables. For example, the following cell includes the code for proper apostrophes and quotation marks: emLand of the Midnight Sun/em, emThe Last Frontier/em, and emThe Great Land /em have replaced Seward#8217;s Folly and emSeward#8217;s Ice Box/em But my question regards differentiating between narrative descriptions and pure data. The example above is what I might call a narrative account. If I wanted to focus on just state nicknames alone, I might make table cells, each with its own entry: 1. Land of the Midnight Sun 2. The Last Frontier 3. The Great Land 4. Seward's Folly 5. Seward's Ice Box But that gets confusing, too, because some states have just one nickname and would therefore require a single column, versus five fields for Alaska. So, suppose I want to have my data available in two formats - the narrative form I offered in the first example, and pure data that I can sort alphabetically and manipulate in other ways. What's the best strategy for doing this? Should I make two fields - one for the narrative, and another with values separated by commas... The Great Land, Land of the Midnight Sun, Seward's Folly Or is there a way to dump all your data into one cell, then choose between displaying everything in that cell and just displaying (and manipulating) isolated bits of data? Thanks. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Doing MySQl DB File backups
Ben Ricker wrote: I believe all the tables are MyISAM...the DB is 3.2.x. I know there will be an inconsistency as we only do nightly backups; any transactions that occur before the scp will not be there. If we failover before the backup, we can have up to 24 hours of data missing. I am concerned that we will have MORE then 24 hours data missing because the DB may not (or may) write the data that has changed to the data files. Do you knif the mysqladmin refresh will force a write to the DB data files? i don't think it will. the safest thing to do is run a php or perl script that basically does this: connect to mysql execute on mysql: FLUSH TABLES WITH READ LOCK; scp files exit this way you are guaranteed that the tables are flushed and won't have any new inserts or updates done while the scp is in process. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Question
When I query I dont see the value of some primary key, I dont understand why, Can you tell me? Thank You Sid Taleb Information TechnologyManager Credico Marketing 100 Alexis-Nihon, Suite 650 Montréal, Québec H4M 2P2, CANADA Tel: 1-866-CREDICO (1-866-273-3426) ext. 252 Local: (514) 747-1575 ext 252 Cell: (514) 702-3351 Fax: (514) 747-2736 http://www.clegg.ca
Multi-threading problems in MySql
We have one query which takes approximately 2 minutes. MySql seems to be unresponsive to any other threads until this query has completed. Are there some settings that aid with this kind of problem? TIA Dan
which table type does not have the maximum columns limitation?
Dear List: I wonder if there exist a type of table which can allow for unlimited or at least more than 1024 columns? I know MaxDB has 1024 max columns. Please give me a hint if you could. Thanks in advance! Hongyu -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Database Insert Help
Hi, I need some help with an insert issue. I have two tables, organizations and contacts. Every contact relates back to an organization Organizations has org_id, org_name, and org_address, etc Contacts has contact_id, contact_name, contact_address and a foreign key called c_org_id. contact_id and org_id are both auto increment integers. I would like to set it up, via a web page, or a third party control, so that when a user is adding new contact information, thy don't have to know which number to enter to relate back to an organization, but instead could just pull down the organizations name from a list box. Behind the scenes, that would be associated with the organization's unique id and that would be entered into the table. Can someone show me an example of how to do this in mysql? Since this will be on a web page, I can combine with perl to make this easier to do. And please, the more detailed and explicit the better. Thank you, Taylor -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Is 255 Characters Really the Limit?
I was hoping to store some fairly large blocks of text in a MySQL table, like brief accounts for all 50 states, such as this: South Dakota was long known as the emSunshine State/em because it#8217;s state motto, which was depicted on the state flag, was #8220;Sunshine State.#8221; In 1980, the motto, flag, and nickname was changed to #8220;The Mount Rushmore State#8221; because it was felt South Dakota couldn#8217;t compete with the other emSunshine State/em, Florida, which enhanced its reputation for sunshine with the nickname emOrange State/em. The nickname emCoyote State/em was actually inspired by a swift horse. Other old nicknames are emBlizzard State/em and emArtesian State/em (for artesian wells) I checked The Manual, which confirmed that 255 characters is indeed the limit for text entries. But I wondered if there might be some workaround. If not, I'll just limit myself to smaller bytes! __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Simple table, 1.7 million rows, very slow SELECTs
I hear what you're saying about memory, but I really don't understand why a btree lookup would be so dramatically slow compared to a linear search with grep. Would something other than MyISAM be more appropriate here? If you feel that a linear search is faster, why not try to let MySQL IGNORE INDEX(PRIMARY): SELECT * FROM table IGNORE INDEX(PRIMARY) WHERE ...; If you do an EXPLAIN of such a query it should state that NULL index was used, etc. Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is 255 Characters Really the Limit?
Use the blob/text, mediumblob/mediumtext or longblob/longtext types. Since you are just storing text, use the text versions. It sounds like when you say text what you really mean is the varchar() type. If you declare a field as a text type, you can store 2^16 characters, or 65,536 characters. From http://dev.mysql.com/doc/mysql/en/Storage_requirements.html the text types are: tinytext = 2^8 = 256 characters (same as the max for varchar()) text=2^16=65,536 characters = 65 kilobytes (the old maximum size document Notepad was capable of opening, pre-windows 2000) mediumtext=2^24=16,777,216 characters=16 megabytes longtext=2^32=4,294,967,296 characters= 4 gigabytes of data If you wanted to store pictures or mp3's or other binary data in the database, you'd use blob types instead of text with the same storage capabilities. bob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Is 255 Characters Really the Limit?
I checked The Manual, which confirmed that 255 characters is indeed the limit for text entries. But I wondered if there might be some workaround. If not, I'll just limit myself to smaller bytes! At http://dev.mysql.com/doc/mysql/en/BLOB.html you can read about the BLOB and TEXT column types. As you can see in http://dev.mysql.com/doc/mysql/en/Storage_requirements.html there are several variations which can each hold a different amount of data: TINYTEXT: 2^8 chars (256) TEXT : 2^16 (65,536) MEDIUMTEXT : 2^24 (16,777,216) LONGTEXT : 2^32 (4,294,967,296) More than enough for your purposes I gues ;-) Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Foreign Key problems
Hello Everyone, I am having problems with foreign keys in MySQL InnoDB type databases. For some reason, when adding a new record within an MS Access subform (based on a query of two tables (parent table and child table), the corresponding foreign key column in the child table not getting populated. The rest of the columns are being populated. I have rebuilt the indexes and foreign keys and this problem still occurs. Is there anything that I could be doing wrong? All tables have time stamps, the primary key is auto-numbered integer and the foreign key is an integer. Thanks, SM
RE: MySQL limits.
Let's see if I can give you some ideas. -Original Message- From: RV Tec [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 18, 2004 8:28 AM To: [EMAIL PROTECTED] Subject: MySQL limits. We have a database with approximately 135 tables (MyISAM). Most of them are small, but we have 5 tables, with 8.000.000 records. And that number is to increase at least 1.000.000 records per month (until the end of the year, the growing rate might surpass 2.000.000 records/month). So, today our database size is 6GB. That's an average size for most applications. The server handles about 35-40 concurrent connections. We have a lot of table locks, but that does not seem to be a problem. Most of the time it works really well. Table locks in my opinion are bad. Especially with 35 concurrent connections. On one of my servers we currently have 1498 threads running, we are averaging 2044.431 queries per second, and 1 slow query for the past month. I restarted mysql on the wrong box on accident. But I would still consider these numbers to be nothing compared to some others around here. From time to time (2 weeks uptime or so), we have to face a Signal 11 crash (which is pretty scary, since we have to run a myisamchk that takes us offline for at least 1 hour). We believe this signal 11 is related to the MySQL server load (since we have changed OS's and hardware -- RAM mostly). What does it say in the mysql_error_log when this happens? Mysql will usually dump the reason out in the error log and it's pretty easy to solve after that. Have you considered using the binary version of MySQL instead of compiling from source? Our server is one P4 3GHz, 2GB RAM (400mhz), SCSI Ultra160 36GB disks (database only) running on OpenBSD 3.5. We are aware that OpenBSD might not be the best OS for this application... at first, it was chosen by it's security. Now we are looking (if that helps) to a OS with LinuxThreads (FreeBSD perharps?). Sorry, can't help you with BSD. Linux for me all of the way. The fact is that we are running MySQL on a dedicated server, that keeps the load between 0.5 and 1.5. CPU definitively is not a problem. The memory could be a problem... our key_buffer is set to 384M, according to the recommendations at my-huge.cnf. So, it seems we have a lot of free memory. We have already tried to increase key_buffer (along with the other settings), but it does not seem to hurt or to improve our performance (although, the memory use increases). 384 for key_buffer is probably fine with 2gigs of memory. Some will say that you can go up to 1/2 of the memory, but I like to stay around 400 myself. But it really varies based on what you are doing. We had to do a lot of testing of our application to find the right number. To track down this signal 11, we have just compiled MySQL with debugandreturned totheoriginal my-huge.cnf recommendations. Now it seems we are running on a overclocked 486 66mhz. That's what debug does. Use the binary, that's my recommendation. Is there any way to prevent this signal 11 to happen or is it a message that we have exceeded MySQL capability? Exceeded MySQL's capability? I don't think you have scratched the surface yet. Error messages are just that, an error of some type. Without knowing the version of MySQL you are running, it's even harder to know. Is MySQL able to handle such load with no problems/turbulences at all? If so, what would be the best hardware/OS configuration? For me, I buy dual proc xeons with hyperthreading. 2 or 4 gigs of memory. Fedora Linux, RPM install of mysql 4.1.1 (4.1.2 is getting close!) Apache 2.x, and php. I install apache and php on all of our servers no matter what, because you never know when you need them. I know many people will tell you to buy opteron's, we just haven't bought one yet, since our vendor of choice doesn't offer them yet. What is the largest DB known to MySQL community? I've heard that cox communications is fairly large, at least according to this: http://www.mysql.com/news-and-events/press-release/release_2003_21.html It says theirs is about 600 gigs. But I am sure there are larger ones around. On one server we have about 170 gigs right now of databases. Donny If it's needed, I can provide DMESG, MySQL error log, compile options and some database statistics. Thanks a lot for your help! Best regards, RV Tec -- 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]
