4.1 x 5.0 INSERT Performance issues
Hi, My software use to run with mySQL 4.1, but now i have to use some of the new features of mySQL 5.0 i did dump all databases, uninistalled mysql4.1, installed the mysql5.0 and start loading the .sql file. This operation use to take about 4 hours but know it is taking 1day!!! Did anything happen with INSERT on InnoDB tables? What's wrong? Thanks, Dirso
Copiando uma base de dados
Bom dia, Fiz um arquivo de instalacao para o meu sistema usando o install shield e estou com algumas duvidas qto a instalar a base de dados. Segue o que eu jah fiz: (1) Meu script de instalacao vai replicar o diretorio C:\mysql\bin na maquina do usuario (2) Copiarah tb libmysql.dll do diretorio C:\mysql\lib\opt para o diretorio system do cliente (3) Instalei meu sistema e as dlls dele - logico (4) se for WindowNT ou compativel roda o comando C:\mysql\mysqld-nt --install se nao adiciona um link na pasta inicializar do menu iniciar para c:\mysqld Agora o que falta: Estou usando tabelas innodb, como faco para copiar meus dados nos diretorios do mysql? Grato, Dirso.
Innodb Error
Hi, I was converting my table from MyISAM to Innodb, but i had a trouble. I had to kill an alter table command (It was an ADD INDEX in a DATETIME field) but it didn't stop. So i restarted the mysql but the mysql starts only if i disable innodb. Did i lost all my innodb tables? Is there anyway to save the data or should i recreate the innodb space? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] (19) 3256-3577 ICQ 136192234 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyISAM - IN
Hi, I'm converting my tables from MyISAM to InnoDB. One of them returns an error Error: 1114 - The table '#sql-7b67_321' is full - How can i avoid this error? Table Structure- wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM with 220945 records Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] (19) 3256-3577 ICQ 136192234 sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MyISAM - Innodb
Hi, I have a lot of MyISAM table that i wanna convert ot Innodb. They don't have any foreign keys nor BLOB or TEXT column as index. How should i do this? Should i just ALTER TABLE mytable TYPE=Innodb? Is there any risk? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] (19) 3256-3577 ICQ 136192234 sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Parsing strings
Hi, What characters should i parse when using an INSERT statement that has string values? So far, i had troubles with single quotes and minus signs. What should i do with them? Is there any others? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior [EMAIL PROTECTED] (19) 3256-3577 ICQ 136192234 sql,query - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query slow (again)
Hi, I have changed my query but it is so slow :( Please, help me! Thank u very much! Edilson. - Query - SELECT a.idemail, a.fklastresp FROM wmkt_email a, wmkt_client b, wmkt_maillist_client c LEFT JOIN wmkt_email_sent d ON a.idemail=d.fkemail WHERE d.fkemail IS NULL AND c.bActive AND c.fkmaillist IN (2) AND a.idemail=b.fkemail AND c.fkclient=b.idclient ORDER BY fklastresp LIMIT 1000 - Tables structures - Table,Create Table wmkt_email,CREATE TABLE `wmkt_email` ( `idemail` int(11) NOT NULL auto_increment, `email` varchar(255) NOT NULL default '', `fklastresp` int(11) NOT NULL default '0', PRIMARY KEY (`idemail`), UNIQUE KEY `ixEmail` (`email`) ) TYPE=MyISAM Table,Create Table wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM - Table,Create Table wmkt_maillist_client,CREATE TABLE `wmkt_maillist_client` ( `fkmaillist` int(11) NOT NULL default '0', `fkclient` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `bActive` tinyint(4) NOT NULL default '1', KEY `ixEmailList` (`fkmaillist`,`fkclient`) ) TYPE=MyISAM - Table,Create Table wmkt_email_sent,CREATE TABLE `wmkt_email_sent` ( `idemailsent` int(11) NOT NULL auto_increment, `fkpbl` int(11) NOT NULL default '0', `fkemail` int(11) NOT NULL default '0', `dtSend` datetime default NULL, `nResult` int(11) NOT NULL default '0', `dtLastUp` datetime default NULL, `nMachine` int(11) NOT NULL default '0', PRIMARY KEY (`idemailsent`), UNIQUE KEY `ixUEmailPbl` (`fkemail`,`fkpbl`), KEY `ixnMacPbl` (`nMachine`,`fkpbl`,`fkemail`) ) TYPE=MyISAM - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query too slow
Hi, I have the following query and everytime i try to run it, it returns an error Mysql server has gone away. Is it beacause the query is very slow? If so, how can i speed it up? Query - SELECT a.idemail, a.fklastresp FROM wmkt_email a, wmkt_client b, wmkt_maillist_client c WHERE bActive AND b.fkemail NOT IN (1, 2 - about 130.000 values - ) AND c.fkmaillist IN (2) AND a.idemail=b.fkemail AND c.fkclient=b.idclient ORDER BY fklastresp LIMIT 5000 Tables structures - Table,Create Table wmkt_email,CREATE TABLE `wmkt_email` ( `idemail` int(11) NOT NULL auto_increment, `email` varchar(255) NOT NULL default '', `fklastresp` int(11) NOT NULL default '0', PRIMARY KEY (`idemail`), UNIQUE KEY `ixEmail` (`email`) ) TYPE=MyISAM Table,Create Table wmkt_client,CREATE TABLE `wmkt_client` ( `idclient` int(11) NOT NULL auto_increment, `realname` varchar(80) NOT NULL default '', `fkemail` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `dtLastUp` datetime default NULL, `fkuser` int(11) NOT NULL default '1', PRIMARY KEY (`idclient`), KEY `ixFkemail` (`fkemail`) ) TYPE=MyISAM - Table,Create Table wmkt_maillist_client,CREATE TABLE `wmkt_maillist_client` ( `fkmaillist` int(11) NOT NULL default '0', `fkclient` int(11) NOT NULL default '0', `dtInsert` datetime default NULL, `bActive` tinyint(4) NOT NULL default '1', KEY `ixEmailList` (`fkmaillist`,`fkclient`) ) TYPE=MyISAM Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
NOW()
Hi, I have a table with a timestamp field called dtins. When i insert a row to the table, the value of this field is setted to NOW() just for the inserted the record. My problem is when i update some record, its dtinst value is change again, without a ask to :( How can i avoid this? Is there another function that i can use to get the current datetime? Thank u very much, Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 sql,query --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Multiple indexes
Hi, A field can belongs to 2 or more indexes? Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 sql, query --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Innodb
Hi, What versions of mysql support innodb tables? What about 3.23.45 or 3.23.47? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Length Limit
Hi, Using the C API, does the sql parameter in the mysql_query function a length limit? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: Innodb
Hi, The server is not mine :( How do i know if the source was compiled with --with-innodb option? Thank u very much, Edilson. -Mensagem original- De: Paul DuBois [mailto:[EMAIL PROTECTED]] Enviada em: sexta-feira, 10 de maio de 2002 17:47 Para: Edilson Vasconcelos de Melo Junior; MYSQL Assunto: Re: Innodb At 17:26 -0300 5/10/02, Edilson Vasconcelos de Melo Junior wrote: Hi, What versions of mysql support innodb tables? What about 3.23.45 or 3.23.47? Both versions, but only if you - Compile from source using the --with-innodb option when you configure MySQL - Use a -max distribution, if you use a binary distribution Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br [EMAIL PROTECTED] Fone: (19) 3256-3577 --- Incoming mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.361 / Virus Database: 199 - Release Date: 7/5/2002 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Number of affected rows
Hi, I'm using the C API for mysql and i wanna know how can i get the number of affected rows after a UPDATE statement. Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
My Query is TOOOOOOOOOOOO Slow :(
Hi, My query is running very very VERY slowly and this is creating a lot of troubles. Please, see it below and if u find something useful and faster i'll be very grateful. MYSQL SELECT idemailsent, email, idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 the tables structures are: mysql show fields FROM wmkt_email_sent; +-+---+--+-+-++ | Field | Type | Null | Key | Default | Extra | +-+---+--+-+-++ | idemailsent | int(11) | | PRI | NULL| auto_increment | | fkpbl | int(11) | | | 0 || | fkemail | int(11) | | | 0 || | dtSend | timestamp(14) | YES | | NULL|| | nResult | int(11) | | | 0 || | dtLastUp| timestamp(14) | YES | | NULL|| | nMachine| int(11) | | | 0 || +-+---+--+-+-++ mysql show fields FROM wmkt_client; +--+---+--+-+-++ | Field| Type | Null | Key | Default | Extra | +--+---+--+-+-++ | idclient | int(11) | | PRI | NULL| auto_increment | | realname | varchar(80) | | | || | fkemail | int(11) | | | 0 || | dtInsert | timestamp(14) | YES | | NULL|| | dtLastUp | timestamp(14) | YES | | NULL|| | fkuser | int(11) | | | 1 || +--+---+--+-+-++ mysql show fields FROM wmkt_email; ++--+--+-+-++ | Field | Type | Null | Key | Default | Extra | ++--+--+-+-++ | idemail| int(11) | | PRI | NULL| auto_increment | | email | varchar(255) | | UNI | || | fklastresp | int(11) | | | 0 || ++--+--+-+-++ Thank u very much, Edilson. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RES: My Query is TOOOOOOOOOOOO Slow :(
Hi, Thank u very much!! It helped a lot... I also created two indexes: one in wmkt_email_sent for (nmachine e fkpbl) and another in wmkt_client for fkEmail (both sugested by Luciano Barcaro). About the size of the resultset: all these table have about 100.000 records and they can be about 10.000.000 (in the end of this year). So if u have more ideas, i'll be very grateful. Thank u very much, Edilson. -Mensagem original- De: Jay Blanchard [mailto:[EMAIL PROTECTED]] Enviada em: sexta-feira, 3 de maio de 2002 14:18 Para: 'MYSQL' Assunto: RE: My Query is T Slow :( You have written the following: [snip arrange] SELECT idemailsent, email, idclient FROMwmkt_email_sent a, wmkt_client b, wmkt_email c WHERE nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND fkuser=1 AND fkpbl=23 ORDER BY email LIMIT 1000 [/snip] Shouldn't it be SELECT a.idemailsent, c.email, b.idclient and WHERE a.nMachine = 0 (I am being slightly picky here, but alias' are there for a reason). Also, it looks like your AND's should be something like AND c.idemail=b.fkemail AND b.fkemail=a.fkemail note the order of processing. You didn't say how many total records. Can you do this; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND c.idemail=a.fkemail AND b.fkemail=a.fkemail AND b.fkuser=1 AND a.fkpbl=23 ORDER BY c.email LIMIT 1000 and mail thos results back? Plus, I would re-arrange the query, putting conditions with values ahead of conditions with matches and move from left to right in the table order; EXPLAIN SELECT a.idemailsent, c.email, b.idclient FROM wmkt_email_sent a, wmkt_client b, wmkt_email c WHERE a.nMachine = 0 AND a.fkpbl=23 AND b.fkuser=1 AND a.fkemail=b.fkemail AND b.fkemail=c.idemail ORDER BY c.email LIMIT 1000 Looks like you can use a couple of thingsdenormalization and INDEXES. HTH! Jay - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Group by counted records...
Hi, My table have two fields (iduser INT, dtaccess TIMESTAMP) with the following data: iduser | dtaccess --- 10 | '2000-10-02' 3 | '2000-12-04' 4 | '2000-10-09' 3 | '2000-12-04' 5 | '2001-02-10' 10 | '2001-03-01' 10 | '2001-01-10' 10 | '2001-01-11' 3 | '2000-12-10' I need to group the records by the number of time some user (iduser) appears in the table, and i need to know how many users appears once, how many appears twice and so on. Example: for these data, the resultset must bring: num_times | num_users - 1 | 2 3 | 1 4 | 1 - i.e: 2 users appears twice, one user - three times, one user - four times. Does anyone knows how can i do that? Thank u very much, Edilson. mysql, sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Should i repair?
Hi, I have a table that has now about 10 records (about 100bytes each one). My queries were too slow and some time a lost connection. But now i can't do anything with this table :( Even SELECT * FROM mytable LIMIT 1 doesn't return a value... Is my table crashed? What should i do now? I can't drop the table because some data (about 100records) is important. Thank u very much, Dirso. mysql. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Locking Tables
Hi, What error occurs if i try to access (read or write) a table that was locked (LOCK TABLES mytable WRITE) by another thread? Dirso. PS: MYSQL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Many clients working with distinct records
Hi, I'm building an appl that will be running in at least 10 machines in the same time connected to the same mysql server. They will working on the same table this way: 1) A client ask for 100 records, work with them and update them 2) Each record must be used just once (cause this, the table has a tinyint field called updated that is setted to 1 by a client after the job is done) 3) Each client should get distinct records. (Example: machine01 get the 001-100 records, machine02 get the 101-200, and so on) 4) The clients don't comunicate one to another. Anyone here has an idea about how can i do that? BTW: i'm using the C API Thank u very much, Dirso. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Count and Group By
Hi, I hava a table tblA that has two fields (id, description) and a table tblB with two fields (fk_tblA, dtInsert). My problem is that i need to count records in tblB grouping them by (id, description) to know how many children each record from tblA has in tblB. The sql i'm using is: SELECT COUNT(*), id, description FROM tblA, tblB WHERE id=fk_tblA GROUP BY id, description ORDER BY description But if a tblA record doesn't have children in tblB, i wanna it be shown as 0 Zero Something like... COUNT(*)| id | description -- 13 | 1 | first value 0 | 2 | empty list 126 | 3 | something else and so on... Any ideas? Thank u very much, Dirso. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Number of connections to mysql server
Hi, I have a client BD application that access a remote MYSQL server and its job is 4-7hours long and it should be connected to the server all this time :( My question is: how many simultaneous connections the mysql server can handle? And what does happen when this limit is overstepped? Thank u very much, Edilson. Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br - Portal JR [EMAIL PROTECTED] Fone: (+55)(19)3256-3577 Cel : (+55)(19)9111-5873 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
GetLastError in MYSQL
Hi, How do i get the last error message while using mysql C API? Thank u very much, Dirso Edilson Vasconcelos de Melo Junior www.jrsoftwares.com.br - Portal JR [EMAIL PROTECTED] Fone: (+55)(19)3256-3577 Cel : (+55)(19)9111-5873 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
