Re: performance between InnoDB vs MyISAM
Hi Foo, MyISAM impress me on insert speed, however on many case MyISAM is not better than Innodb. If you can't use combination of them, better your break down your need to decide which one to use. AFAIK, sub query is better in innodb rather than myisam, and if you have only 200.000 records with huge amount of text, innodb is good enough, just make sure you have enough memory to increase performance. Do you need fulltext SEARCH? If yes, myisam is support this :D not innodb. Foo Ji-Haw wrote: Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like 90 secs. But in InnoDB it is usually 8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Table with multiple primary keys - How
It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith create table members ( logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, email_addr varchar(30), ??? last_name varchar(30), ??? member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, first_name varchar(30), addr1 varchar(30), addr2 varchar(30), cityvarchar(20), state varchar(20), zip varchar(15), phone_home varchar(15), phone_officevarchar(15), phone_cell varchar(15), mothers_maiden_name varchar(30), ip_of_useratsignup varchar(16), primary key(login_id, email_addr, last_name) ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select substring_index(user(),_utf8'@',-1) not return ip on win2000/XP ?
Hi, If mysql database server installed on Linux without problem, but on win2000/XP: select substring_index(user(),_utf8'@',-1) return the local windows station computer name instead of IP ? Best regard! Shuming Wang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Webminars
Up until now I have missed all 3 webminars I registered... because of good various reasons... I was wondering weather the presentations from those webminars can appear online at: http://dev.mysql.com/tech-resources/presentations ??? That would be great... a lot of people can access them that way ! -- Gabriel PREDA Senior Web Developer
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: How can I observe mysqld?
Am Dienstag, den 07.03.2006, 17:00 -0600 schrieb Ryan Stille: What else can I do to observe myslqd? mytop may be of some use: http://jeremy.zawodny.com/mysql/mytop/ Hello Ryan, mytop is indeed very nice. :-) Thank you for that. One question: Is it possible to oberserve more than one database at once with mytop? I read the mytop-documentation and tried mytop -d, mytop -d db1 -d db2 and mytop -d db1,db2 but it didn't work. Bye Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
mytop measures the overall performance of the server... it's not daatabase bound ! For itself it uses the test database... but that has no meaning... it's not measuring that database performance... You can see queries that take to long aprearing in the list... It uses SHOW STATUS - to get and/or compute the statistics you see on the top part of the screen ! SHOW FULL PROCESSLIST - to get a list of running proceses ( SELECT, INSERT, UPDATE, CREATE ... ) So it cannot be database bound ! -- Gabriel PREDA Senior Web Developer
Not Saving
Please, i'm a crazy ! i dont can write in a table, please anybody can help me
try find the error
Thanks
?php
$ipt_nome = $HTTP_POST_VARS[nome];
$ipt_telefone = $HTTP_POST_VARS[telefones];
$ipt_nascimento = $HTTP_POST_VARS[nascimento];
$ipt_estado = $HTTP_POST_VARS[estado];
$ipt_cidade = $HTTP_POST_VARS[cidade];
$ipt_bairro = $HTTP_POST_VARS[bairro];
$ipt_cep = $HTTP_POST_VARS[codcep];
$ipt_endereco = $HTTP_POST_VARS[endereco];
$ipt_email = $HTTP_POST_VARS[email];
$ipt_site = $HTTP_POST_VARS[site];
$ipt_estuda = $HTTP_POST_VARS[estuda];
$ipt_curso = $HTTP_POST_VARS[curso];
$ipt_periodo = $HTTP_POST_VARS[periodo];
$ipt_faculdade = $HTTP_POST_VARS[faculdade];
$ipt_trabalha = $HTTP_POST_VARS[trabalha];
$ipt_ultima = $HTTP_POST_VARS[ultimaempresa];
$ipt_ultima_desde = $HTTP_POST_VARS[ultimadesde];
$ipt_ultima_ate = $HTTP_POST_VARS[ultimaate];
$ipt_ultima_cargo = $HTTP_POST_VARS[ultimacargo];
$ipt_ultima_descricao = $HTTP_POST_VARS[ultimadescricao];
$ipt_penultima = $HTTP_POST_VARS[penempresa];
$ipt_penultima_desde = $HTTP_POST_VARS[pendesde];
$ipt_penultima_ate = $HTTP_POST_VARS[penate];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_descricao = $HTTP_POST_VARS[pendescricao];
$ipt_ante_penultima = $HTTP_POST_VARS[apempresa];
$ipt_ante_penultima_desde = $HTTP_POST_VARS[apdesde];
$ipt_ante_penultima_ate = $HTTP_POST_VARS[adate];
$ipt_ante_penultima_cargo = $HTTP_POST_VARS[apcargo];
$ipt_ante_penultima_descricao = $HTTP_POST_VARS[apdescricao];
$ipt_horario = $HTTP_POST_VARS[horario];
$ipt_interesse = $HTTP_POST_VARS[areainteresse];
$ipt_ingles = $HTTP_POST_VARS[ingles];
$ipt_ult_obs = $HTTP_POST_VARS[ultobs];
//Inicio da Linha de kleiton
$conexao = mysql_connect(localhost , admcurriculos , pcs2006) or
print mysql_error();
mysql_query(insert into curtos (nome, telcel, nascimento, estado, cida
de, bairro, codcep, endereco, email, site, estuda, curso, periodo, faculdade,
tr
abalha, ultimaempresa, ultimadesde, ultimaate, ultimacargo, ultimadescricao,
pen
empresa, pendesde, penate, pencargo, pendescricao, apempresa, apdesde, apate,
ap
cargo, apdescricao, horario, ingles, areainteresse, ultob) values
('$ipt_nome','
$ipt_telefone','$ipt_nascimento','$ipt_estado','$ipt_cidade','$ipt_bairro','$ipt
_cep','$ipt_endereco','$ipt_email','$ipt_site','$ipt_estuda','$ipt_curso','$ipt_
periodo','$ipt_faculdade','$ipt_trabalha','$ipt_ultima','$ipt_ultima_desde','$ip
t_ultima_ate','$ipt_ultima_cargo','$ipt_ultima_descricao','$ipt_penultima','$ipt
_penultima_desde','$ipt_penultima_ate','$ipt_penultima_cargo','$ipt_penultima_de
scricao','$ipt_ante_penultima','$ipt_ante_penultima_desde','$ipt_ante_penultima_
ate','$ipt_ante_penultima_cargo','$ipt_ante_penultima_descricao','$ipt_horario',
'$ipt_ingles','$ipt_interesse','$ipt_ult_obs'), $conexao) or print
mysql_error(
);
mysql_close($conexao) or print mysql_error();
mysql_close($conexao) or print mysql_error();
--
---
---
Kleiton L R Soares (21-2620-0735 | 21-9639-5829)
PCS - Processos Consultoria Soluções
www.pcs.com.br / [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
Am Mittwoch, den 08.03.2006, 12:22 +0200 schrieb Gabriel PREDA: Hello Gabriel, mytop measures the overall performance of the server... it's not daatabase bound ! you are right. For itself it uses the test database... but that has no meaning... maybe it is meant to confuse clueless people like me. ;-) SHOW FULL PROCESSLIST - to get a list of running proceses ( SELECT, INSERT, UPDATE, CREATE ... ) in *every* database - now I see it because I caused a little bit traffic in two databases and mytop shows all processes in all databases. Bye Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
query problem
I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross
Re: query problem
one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
On 3/8/06, Adrian Bruce [EMAIL PROTECTED] wrote: one solution (may not be the best but would work) would be to use 'like' instead of '=' and then put wildcards %%$var % around the variable so that if it is not there then it wount effect the query. Yeah, I use this kind of trick for SELECTs based on user submited forms. So your query would be: SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area LIKE '%$area%' AND interest LIKE '%$interest%' ORDER BY fname $type; Ade [EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? Thanks, Ross -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Best way to design one to many queries
Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Not Saving
You are doing this mysql_connect mysql_query And you need to do this mysql_connect mysql_select_db mysql_query -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Not Saving
I guess you are Not selecting the database .. any error msg ?
eg : mysql_select_db( '' ) or die( 'Could not select database' );
--Praj
On Wed, 8 Mar 2006 07:45:20 -0300
Kleiton L R Soares [EMAIL PROTECTED] wrote:
Please, i'm a crazy ! i dont can write in a table, please anybody can help me
try find the error
Thanks
?php
$ipt_nome = $HTTP_POST_VARS[nome];
$ipt_telefone = $HTTP_POST_VARS[telefones];
$ipt_nascimento = $HTTP_POST_VARS[nascimento];
$ipt_estado = $HTTP_POST_VARS[estado];
$ipt_cidade = $HTTP_POST_VARS[cidade];
$ipt_bairro = $HTTP_POST_VARS[bairro];
$ipt_cep = $HTTP_POST_VARS[codcep];
$ipt_endereco = $HTTP_POST_VARS[endereco];
$ipt_email = $HTTP_POST_VARS[email];
$ipt_site = $HTTP_POST_VARS[site];
$ipt_estuda = $HTTP_POST_VARS[estuda];
$ipt_curso = $HTTP_POST_VARS[curso];
$ipt_periodo = $HTTP_POST_VARS[periodo];
$ipt_faculdade = $HTTP_POST_VARS[faculdade];
$ipt_trabalha = $HTTP_POST_VARS[trabalha];
$ipt_ultima = $HTTP_POST_VARS[ultimaempresa];
$ipt_ultima_desde = $HTTP_POST_VARS[ultimadesde];
$ipt_ultima_ate = $HTTP_POST_VARS[ultimaate];
$ipt_ultima_cargo = $HTTP_POST_VARS[ultimacargo];
$ipt_ultima_descricao = $HTTP_POST_VARS[ultimadescricao];
$ipt_penultima = $HTTP_POST_VARS[penempresa];
$ipt_penultima_desde = $HTTP_POST_VARS[pendesde];
$ipt_penultima_ate = $HTTP_POST_VARS[penate];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_descricao = $HTTP_POST_VARS[pendescricao];
$ipt_ante_penultima = $HTTP_POST_VARS[apempresa];
$ipt_ante_penultima_desde = $HTTP_POST_VARS[apdesde];
$ipt_ante_penultima_ate = $HTTP_POST_VARS[adate];
$ipt_ante_penultima_cargo = $HTTP_POST_VARS[apcargo];
$ipt_ante_penultima_descricao = $HTTP_POST_VARS[apdescricao];
$ipt_horario = $HTTP_POST_VARS[horario];
$ipt_interesse = $HTTP_POST_VARS[areainteresse];
$ipt_ingles = $HTTP_POST_VARS[ingles];
$ipt_ult_obs = $HTTP_POST_VARS[ultobs];
//Inicio da Linha de kleiton
$conexao = mysql_connect(localhost , admcurriculos , pcs2006)
or
print mysql_error();
mysql_query(insert into curtos (nome, telcel, nascimento, estado, cida
de, bairro, codcep, endereco, email, site, estuda, curso, periodo, faculdade,
tr
abalha, ultimaempresa, ultimadesde, ultimaate, ultimacargo, ultimadescricao,
pen
empresa, pendesde, penate, pencargo, pendescricao, apempresa, apdesde, apate,
ap
cargo, apdescricao, horario, ingles, areainteresse, ultob) values
('$ipt_nome','
$ipt_telefone','$ipt_nascimento','$ipt_estado','$ipt_cidade','$ipt_bairro','$ipt
_cep','$ipt_endereco','$ipt_email','$ipt_site','$ipt_estuda','$ipt_curso','$ipt_
periodo','$ipt_faculdade','$ipt_trabalha','$ipt_ultima','$ipt_ultima_desde','$ip
t_ultima_ate','$ipt_ultima_cargo','$ipt_ultima_descricao','$ipt_penultima','$ipt
_penultima_desde','$ipt_penultima_ate','$ipt_penultima_cargo','$ipt_penultima_de
scricao','$ipt_ante_penultima','$ipt_ante_penultima_desde','$ipt_ante_penultima_
ate','$ipt_ante_penultima_cargo','$ipt_ante_penultima_descricao','$ipt_horario',
'$ipt_ingles','$ipt_interesse','$ipt_ult_obs'), $conexao) or print
mysql_error(
);
mysql_close($conexao) or print mysql_error();
mysql_close($conexao) or print mysql_error();
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Charset questions
I'm still hoping someone can else can share their input on this. What do other people usually do as far as the collation setting? Thanks, -Ryan Ryan Stille wrote: When we migrated to MySQL from MS SQL, I left everything set to the default as far as collations - latin1_swedish_ci. This was based on digging through the manual and google. But now I am migrating the application to a newer version of ColdFusion and am running into some issues with charsets on some ColdFusion functions. I am wondering if my database charset has anything to do with it. What do other people usually do as far as the collation setting? We are in the US, but do have a few sites that make use of German and Spanish characters. Thanks, -Ryan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: query problem
[EMAIL PROTECTED] wrote: I am fairly new to sql and am now getting into the area of slightly more complex queries. At present my query is $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' ORDER BY fname $type; but I have two other filters which may or may not be chosen. (area, and interest). $query = SELECT * FROM $table_name WHERE sname LIKE '$search_string%' AND area='area' AND interest='interest' ORDER BY fname $type; but what if nether is chosen, or only one? Is there an AND/OR operator or similar in mysql? No AND/OR. It's the job of the front-end, ie your app, to assemble to correct number of Where clauses. PB -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 268.2.0/275 - Release Date: 3/6/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How can I observe mysqld?
although mytop would give you overall performance statistics , you can monitor the processes/queries/threads for a specific database using the switch d ( if thats what you are looking for) JFYI: type in ? in the mytop window and it would give you a full list of options to choose from... Kishore Jalleda On 3/8/06, Karl Schock [EMAIL PROTECTED] wrote: Am Mittwoch, den 08.03.2006, 12:22 +0200 schrieb Gabriel PREDA: Hello Gabriel, mytop measures the overall performance of the server... it's not daatabase bound ! you are right. For itself it uses the test database... but that has no meaning... maybe it is meant to confuse clueless people like me. ;-) SHOW FULL PROCESSLIST - to get a list of running proceses ( SELECT, INSERT, UPDATE, CREATE ... ) in *every* database - now I see it because I caused a little bit traffic in two databases and mytop shows all processes in all databases. Bye Karl -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Database Corruption and MySQL
Yes thats very true, as the corrupt slave would pick up from where it left and its quite posssible that the record was already updated on the healthy slave from the master, causing the replication to fail, but having said that may be you could do this 1) stop the healthy slave 2) put a global read lock on master or stop it for a few mins (if you can) 3) clear the *.info and the *-bin.* files on corrupt slave 4) run rsync between the corrupt slave and master so now they are in sync 5) now start the master, and both the slaves and you are good to go ---All this could be easily scripted to make the task easier. ---Also my soultion may not be any better than what you are doing currently, except for the time saved in the rsync process instead of copying the whole data folder to the slave .. Hope this helps , just my 2 cents Kishore Jalleda On 3/7/06, Michael Jeung [EMAIL PROTECTED] wrote: Hi Kishore, That's an interesting idea. However, given that the healthy slave and the corrupt slave now have different values for Exec_Master_Log_Pos, would restoring the tables from the healthy slave necessarily be a good move? I would be worried that the corrupt slave's counter position would be mismatched with the table. Regards, Michael Jeung On Mar 7, 2006, at 3:20 PM, Kishore Jalleda wrote: an rsync with the right options from the unaffected slave to the corrupt one might prove to be an elegant technique Kishore Jalleda On 3/7/06, Michael Jeung [EMAIL PROTECTED] wrote: Hi Folks, We've got a Single Master/Multiple Slave environment. Recently, we had some corruption on one of the slaves and I had to repair the affected tables. After the repair completed, some of the rows on the slave had been deleted - so the Master and the Slave weren't exactly in synch. The slave was missing some records. I verified this myself with a select count(*) on the table. Now, I know this is the normal way for MySQL to repair it's database. However, what I'd like to do is restore these missing rows to the Slave without doing a dump and reload from my backups. Is this possible? The way that we normally approach this problem is to take an unaffected slave and copy the mysql directory from an unaffected slave over to the corrupt slave. Then we restart MySQL on the corrupt slave and things work out without too much difficulty. However, this has always seemed like a terrible way to restore a corrupt slave to me. Is there a more elegant way to do it? Regards, Michael Jeung -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql? [EMAIL PROTECTED]
RE: Best way to design one to many queries
In this case, cnelson, you can only have a one to one relationship. He wants to allow a one to many relationship. Using one field to store all the attribute ids in the product table is what he is trying to do and it is not the best idea. Better use a table in the middle, with product ids and attribute ids, this way he can have one more than one attribute per product. It allows more than one attribute per category of attribute. For instance, languages available in a DVD: DVD titles: id title 1 2 3 Attributes: id type_id description 1 1 English 2 1 French 3 1 Spanish Attribute types: id description 1 language attribute links: dvd_id attribute_id 1 1 1 2 1 3 To list all the languages in plain English for DVD #1: Select attributes.description From attributes_links On attributes_links.attribute_id = attributes.id Where attributes_links.dvd_id = 1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 5:41 AM To: Scott Haneda Cc: MySql Subject: Re: Best way to design one to many queries Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: performance between InnoDB vs MyISAM
Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like 90 secs. But in InnoDB it is usually 8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? Innodb clusters the table data around the primary key... which is what you're searching on. So your query is able to go right to the spot and read the whole row, as opposed to myisam which would need to look it up in the index to find the position in the row, then go read the table itself to get the row. among other reasons I suppose. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: Best way to design one to many queries
In this case, cnelson, you can only have a one to one relationship. Why? If the Attribute table keyed on (ProductID,Attribute), surely a 1:M is possible. He wants to allow a one to many relationship. Yes, I know. Using one field to store all the attribute ids in the product table is what he is trying to do and it is not the best idea. I agree! Better use a table in the middle, with product ids and attribute ids, this way he can have one more than one attribute per product. That's what I was getting at with my second suggestion. It allows more than one attribute per category of attribute. For instance, languages available in a DVD: DVD titles: idtitle 1 2 3 Attributes: idtype_id description 1 1 English 2 1 French 3 1 Spanish Attribute types: iddescription 1 language attribute links: dvd_idattribute_id 1 1 1 2 1 3 To list all the languages in plain English for DVD #1: Select attributes.description From attributes_links On attributes_links.attribute_id = attributes.id Where attributes_links.dvd_id = 1 -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 5:41 AM To: Scott Haneda Cc: MySql Subject: Re: Best way to design one to many queries Been wondering this for a while now, I can explain how I do it, but I am sure there are better ways. ... Maybe I'm really missing something here but I think that your querying problem arises from bad data design. This is what I'd do: Product table: ProductID some other stuff Attribute table: ProductID Attribute which gives: SELECT Product.something Attribute.Attribute FROM Product LEFT JOIN Attribute ON (Product.ProductID = Attribute.ProductID); Or Product table: ProductID product stuff ProdAttr table: ProductID AttributeID Attribute table: AttributeID other attribute stuff Which makes the query a little more complicated but the attributes a little more flexible. -- 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 5.0 too new?
Webhosts are telling me they dont support 5.0 because it is too new and potentially unstable without reliability documentation. Im having difficulty finding a webhost that will support 5.0. Is earlier versions of mysql available? Any help here appreciated -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Commit question
Hi, we have a db with myisam and a single innodb table. 2 separate processes are inserting data into myisam and the innodb tables at the same time. Intermittently, we have noticed an issue where commits to the innodb table appear to be delayed until the process inserting into the myisam tables is finished. Has anyone else noticed this behavior? What could be causing this? Any help appreciated Rob Brooks The Brian Group LLC
RE: Not Saving
Error message?
Also you seem to take save date from a HTML form.
How do you treat quote signs? And entries too large for a field?
-Original Message-
From: Kleiton L R Soares [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 08, 2006 3:45 AM
To: mysql@lists.mysql.com
Subject: Not Saving
Please, i'm a crazy ! i dont can write in a table, please anybody can
help me
try find the error
Thanks
?php
$ipt_nome = $HTTP_POST_VARS[nome];
$ipt_telefone = $HTTP_POST_VARS[telefones];
$ipt_nascimento = $HTTP_POST_VARS[nascimento];
$ipt_estado = $HTTP_POST_VARS[estado];
$ipt_cidade = $HTTP_POST_VARS[cidade];
$ipt_bairro = $HTTP_POST_VARS[bairro];
$ipt_cep = $HTTP_POST_VARS[codcep];
$ipt_endereco = $HTTP_POST_VARS[endereco];
$ipt_email = $HTTP_POST_VARS[email];
$ipt_site = $HTTP_POST_VARS[site];
$ipt_estuda = $HTTP_POST_VARS[estuda];
$ipt_curso = $HTTP_POST_VARS[curso];
$ipt_periodo = $HTTP_POST_VARS[periodo];
$ipt_faculdade = $HTTP_POST_VARS[faculdade];
$ipt_trabalha = $HTTP_POST_VARS[trabalha];
$ipt_ultima = $HTTP_POST_VARS[ultimaempresa];
$ipt_ultima_desde = $HTTP_POST_VARS[ultimadesde];
$ipt_ultima_ate = $HTTP_POST_VARS[ultimaate];
$ipt_ultima_cargo = $HTTP_POST_VARS[ultimacargo];
$ipt_ultima_descricao = $HTTP_POST_VARS[ultimadescricao];
$ipt_penultima = $HTTP_POST_VARS[penempresa];
$ipt_penultima_desde = $HTTP_POST_VARS[pendesde];
$ipt_penultima_ate = $HTTP_POST_VARS[penate];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_descricao = $HTTP_POST_VARS[pendescricao];
$ipt_ante_penultima = $HTTP_POST_VARS[apempresa];
$ipt_ante_penultima_desde = $HTTP_POST_VARS[apdesde];
$ipt_ante_penultima_ate = $HTTP_POST_VARS[adate];
$ipt_ante_penultima_cargo = $HTTP_POST_VARS[apcargo];
$ipt_ante_penultima_descricao = $HTTP_POST_VARS[apdescricao];
$ipt_horario = $HTTP_POST_VARS[horario];
$ipt_interesse = $HTTP_POST_VARS[areainteresse];
$ipt_ingles = $HTTP_POST_VARS[ingles];
$ipt_ult_obs = $HTTP_POST_VARS[ultobs];
//Inicio da Linha de kleiton
$conexao = mysql_connect(localhost , admcurriculos ,
pcs2006) or
print mysql_error();
mysql_query(insert into curtos (nome, telcel, nascimento, estado, cida
de, bairro, codcep, endereco, email, site, estuda, curso, periodo,
faculdade,
tr
abalha, ultimaempresa, ultimadesde, ultimaate, ultimacargo,
ultimadescricao,
pen
empresa, pendesde, penate, pencargo, pendescricao, apempresa, apdesde,
apate,
ap
cargo, apdescricao, horario, ingles, areainteresse, ultob) values
('$ipt_nome','
$ipt_telefone','$ipt_nascimento','$ipt_estado','$ipt_cidade','$ipt_bairr
o','$ipt
_cep','$ipt_endereco','$ipt_email','$ipt_site','$ipt_estuda','$ipt_curso
','$ipt_
periodo','$ipt_faculdade','$ipt_trabalha','$ipt_ultima','$ipt_ultima_des
de','$ip
t_ultima_ate','$ipt_ultima_cargo','$ipt_ultima_descricao','$ipt_penultim
a','$ipt
_penultima_desde','$ipt_penultima_ate','$ipt_penultima_cargo','$ipt_penu
ltima_de
scricao','$ipt_ante_penultima','$ipt_ante_penultima_desde','$ipt_ante_pe
nultima_
ate','$ipt_ante_penultima_cargo','$ipt_ante_penultima_descricao','$ipt_h
orario',
'$ipt_ingles','$ipt_interesse','$ipt_ult_obs'), $conexao) or print
mysql_error(
);
mysql_close($conexao) or print mysql_error();
mysql_close($conexao) or print mysql_error();
--
---
---
Kleiton L R Soares (21-2620-0735 | 21-9639-5829)
PCS - Processos Consultoria Soluções
www.pcs.com.br / [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Tuning server variables
Dear all, Two questions about server variables tuning. 1) key_buffer_size stores MyISAM indexes; innodb_buffer_pool_size stores InnoDB indexes and rows. So the innoDB buffer would need more space in general. It sounds like a good idea, if the number and size of MyISAM and InnoDB tables is roughly the same, to set innodb_buffer_pool_size larger than key_buffer_size. 2) Assume that there are many LIKE '%...%' queries being performed on the server. Variables to (carefully) tune in this case would be read_buffer_size and read_rnd_buffer_size. Does this make sense? Thanks, Jan Pieter Kunst -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Not Saving
Error message?
Also you seem to take save date from a HTML form.
How do you treat quote signs? And entries too large for a field?
-Original Message-
From: Kleiton L R Soares [mailto:[EMAIL PROTECTED]
Sent: Wednesday, March 08, 2006 3:45 AM
To: mysql@lists.mysql.com
Subject: Not Saving
Please, i'm a crazy ! i dont can write in a table, please anybody can
help me
try find the error
Thanks
?php
$ipt_nome = $HTTP_POST_VARS[nome];
$ipt_telefone = $HTTP_POST_VARS[telefones];
$ipt_nascimento = $HTTP_POST_VARS[nascimento];
$ipt_estado = $HTTP_POST_VARS[estado];
$ipt_cidade = $HTTP_POST_VARS[cidade];
$ipt_bairro = $HTTP_POST_VARS[bairro];
$ipt_cep = $HTTP_POST_VARS[codcep];
$ipt_endereco = $HTTP_POST_VARS[endereco];
$ipt_email = $HTTP_POST_VARS[email];
$ipt_site = $HTTP_POST_VARS[site];
$ipt_estuda = $HTTP_POST_VARS[estuda];
$ipt_curso = $HTTP_POST_VARS[curso];
$ipt_periodo = $HTTP_POST_VARS[periodo];
$ipt_faculdade = $HTTP_POST_VARS[faculdade];
$ipt_trabalha = $HTTP_POST_VARS[trabalha];
$ipt_ultima = $HTTP_POST_VARS[ultimaempresa];
$ipt_ultima_desde = $HTTP_POST_VARS[ultimadesde];
$ipt_ultima_ate = $HTTP_POST_VARS[ultimaate];
$ipt_ultima_cargo = $HTTP_POST_VARS[ultimacargo];
$ipt_ultima_descricao = $HTTP_POST_VARS[ultimadescricao];
$ipt_penultima = $HTTP_POST_VARS[penempresa];
$ipt_penultima_desde = $HTTP_POST_VARS[pendesde];
$ipt_penultima_ate = $HTTP_POST_VARS[penate];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_cargo = $HTTP_POST_VARS[pencargo];
$ipt_penultima_descricao = $HTTP_POST_VARS[pendescricao];
$ipt_ante_penultima = $HTTP_POST_VARS[apempresa];
$ipt_ante_penultima_desde = $HTTP_POST_VARS[apdesde];
$ipt_ante_penultima_ate = $HTTP_POST_VARS[adate];
$ipt_ante_penultima_cargo = $HTTP_POST_VARS[apcargo];
$ipt_ante_penultima_descricao = $HTTP_POST_VARS[apdescricao];
$ipt_horario = $HTTP_POST_VARS[horario];
$ipt_interesse = $HTTP_POST_VARS[areainteresse];
$ipt_ingles = $HTTP_POST_VARS[ingles];
$ipt_ult_obs = $HTTP_POST_VARS[ultobs];
//Inicio da Linha de kleiton
$conexao = mysql_connect(localhost , admcurriculos ,
pcs2006) or
print mysql_error();
mysql_query(insert into curtos (nome, telcel, nascimento, estado, cida
de, bairro, codcep, endereco, email, site, estuda, curso, periodo,
faculdade,
tr
abalha, ultimaempresa, ultimadesde, ultimaate, ultimacargo,
ultimadescricao,
pen
empresa, pendesde, penate, pencargo, pendescricao, apempresa, apdesde,
apate,
ap
cargo, apdescricao, horario, ingles, areainteresse, ultob) values
('$ipt_nome','
$ipt_telefone','$ipt_nascimento','$ipt_estado','$ipt_cidade','$ipt_bairr
o','$ipt
_cep','$ipt_endereco','$ipt_email','$ipt_site','$ipt_estuda','$ipt_curso
','$ipt_
periodo','$ipt_faculdade','$ipt_trabalha','$ipt_ultima','$ipt_ultima_des
de','$ip
t_ultima_ate','$ipt_ultima_cargo','$ipt_ultima_descricao','$ipt_penultim
a','$ipt
_penultima_desde','$ipt_penultima_ate','$ipt_penultima_cargo','$ipt_penu
ltima_de
scricao','$ipt_ante_penultima','$ipt_ante_penultima_desde','$ipt_ante_pe
nultima_
ate','$ipt_ante_penultima_cargo','$ipt_ante_penultima_descricao','$ipt_h
orario',
'$ipt_ingles','$ipt_interesse','$ipt_ult_obs'), $conexao) or print
mysql_error(
);
mysql_close($conexao) or print mysql_error();
mysql_close($conexao) or print mysql_error();
--
---
---
Kleiton L R Soares (21-2620-0735 | 21-9639-5829)
PCS - Processos Consultoria Soluções
www.pcs.com.br / [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How to Stop Runaway Query (automatically)?
Hello Everyone, I am discovering that my MySQL server tends to get locked up by runaway queries, that are continuously sending data. Even after all night long. (The origin of these queries is from report development.) Without having to kill each connection manually on the MySQL server, is there an easier way to automatically stop these runaway queries in the configuration file? I can't find any options for direct timeouts of active connections. Instead, the closest thing that I can find is to limit the packet size. See below for MySQL documentation. However, tt's confusing on what is a single packet. But would this stop runaway queries from running all night long?? http://dev.mysql.com/doc/refman/4.1/en/packet-too-large.html A communication packet is a single SQL statement sent to the MySQL server or a single row that is sent to the client. In MySQL 3.23, the largest possible packet is 16MB, due to limits in the client/server protocol. In MySQL 4.0.1 and up, the limit is 1GB. Any help would be greatly appreciated. Thanks! Henry
Re: Webminars
Gabriel, Try the on-demand webinar page at: http://www.mysql.com/news-and-events/on-demand-webinars/ -Sheeri On 3/8/06, Gabriel PREDA [EMAIL PROTECTED] wrote: Up until now I have missed all 3 webminars I registered... because of good various reasons... I was wondering weather the presentations from those webminars can appear online at: http://dev.mysql.com/tech-resources/presentations ??? That would be great... a lot of people can access them that way ! -- Gabriel PREDA Senior Web Developer -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Table with multiple primary keys - How
Thank you. I went with your solution and the create table worked. Tested inserting on primary key and it worked. Need to load more data before I can test alt indexes. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 3:39 AM To: mysql@lists.mysql.com Subject: Re: Table with multiple primary keys - How It is not possible to have more than one PRIMARY key per table. Maybe you need to use one PRIMARY key as the main index into the table, then use UNIQUE or KEY which is a synonym for INDEX on the other two columns. This book will help you ALOT with designing tables. It will also teach you how to normalise (refactor) your tables into a more efficient form. http://www.apress.com/book/bookDisplay.html?bID=338 It is also more efficient IMHO to index on integer values if you can, rather than character text. Regards Keith create table members ( logon_idMEDIUMINT UNSIGNED NOT NULL AUTO_INCREMENT, email_addr varchar(30), ??? last_name varchar(30), ??? member_type char(1), email_verified char(1), logon_pwvarchar(15), date_added date, last_login timestamp, first_name varchar(30), addr1 varchar(30), addr2 varchar(30), cityvarchar(20), state varchar(20), zip varchar(15), phone_home varchar(15), phone_officevarchar(15), phone_cell varchar(15), mothers_maiden_name varchar(30), ip_of_useratsignup varchar(16), primary key(login_id, email_addr, last_name) ); primary key login_id (login_id), key email_addr (email_addr), key last_name (last_name) ); -- 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]
Signal 11 crashes on MySQL V5
I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently upgraded to V5 on all servers. Now MySQL is crashing regularly (several times per day, some days) with 'got signal 11'. My 3 Linux servers are very different machines running different software a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386 , a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL 5.0.18-0.glibc23.x86_64 , a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386 The windows machines are not having a problem. All 6 are running essentially the same application. It seems unlikely to be a hardware problem because its on 3 machines at once. It looks like a MySQL V5 problem but I can't pin it down to anything specific enough to report a bug. Anyone had similar experiences with MySQL V5? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: mysql 5.0 too new?
Many big guys like the one in Scottsdale, AZ, still have MySQL 3.23. I myself make my SQL 3.23 compatible. Yes, it is too new, and it is sad, because stored procedures will make our job much easier. I also program for MS SQL and use SPs extensively for all the good reasons. -Original Message- From: RICHARD J. SWEENEY [mailto:[EMAIL PROTECTED] Sent: Sunday, March 05, 2006 6:54 AM To: mysql@lists.mysql.com Subject: mysql 5.0 too new? Webhosts are telling me they dont support 5.0 because it is too new and potentially unstable without reliability documentation. Im having difficulty finding a webhost that will support 5.0. Is earlier versions of mysql available? Any help here 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]
replication after editing bin logs
Hello, Another division in my organization is maintaining a mysql database. For various reasons, data is deleted from this database after it has aged more than a month. My division has a need for long term storage of the same data, so I am trying the following strategy: 1. Create a snapshot (slave) of the master database. 2. Enable binary logging on the master database. 3. Periodically, run the binary logs through the mysqlbinlog utility to produce SQL text. 4. Parse the SQL text to remove DELETE's of aged data. 5. Apply the SQL text to the snapshot (slave). Now data older than one month is being saved in the slave. When I came to step 5, I submitted the SQL text file as a batch job: mysql -u root -p -e source binlogs_045_052_parsed After many hours I received the following error message: ERROR 1036 (HY000) at line 1387471 in file: 'binlogs_045_052_parsed': Table 'AB4539p2' is read only At this point, the mysql batch job terminated. Has anyone come across a similar error under similar circumstances? Some background: I am running mysql 5.0 on a Windows 2003 server. The SQL text file, binlogs_045_052_parsed, contains only three kinds of statements: millions of LOAD DATA LOCAL INFILEs, a handful of DROP TABLEs, and a few CREATE TABLEs. The LOAD DATA LOCAL INFILEs are doing inserts into about 5000 tables in the database. Before I received the error, many inserts into table 'AB4539p2' succeeded without any error. I have a few more basic questions: 1. Will all the LOAD DATA LOCAL INFILES submitted before the error have been committed, or will all the statements from the batch job have rolled back once the error occurred? (I want to know if I need to resubmit all of the file binlogs_045_052_parsed to mysql, or just the portion from the error.) 2. I notice that mysqlbinlog puts ROLLBACK statements at the beginning and end of the SQL text file. I don't understand the purpose of these ROLLBACKs. Thank you, Eric
Re: Signal 11 crashes on MySQL V5
could you tell us if these 6 are in a cluster or in a replication set up, and u also said the 3 linux bixes all crash at once, did u check the logs, do they crash under load, what about the OS, is it stable when mysql crashes Kishore Jalleda On 3/8/06, Dave Pullin [EMAIL PROTECTED] wrote: I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently upgraded to V5 on all servers. Now MySQL is crashing regularly (several times per day, some days) with 'got signal 11'. My 3 Linux servers are very different machines running different software a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386 , a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL 5.0.18-0.glibc23.x86_64 , a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386 The windows machines are not having a problem. All 6 are running essentially the same application. It seems unlikely to be a hardware problem because its on 3 machines at once. It looks like a MySQL V5 problem but I can't pin it down to anything specific enough to report a bug. Anyone had similar experiences with MySQL V5? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql 5.0 too new?
On 3/8/06, Nicolas Verhaeghe [EMAIL PROTECTED] wrote: Many big guys like the one in Scottsdale, AZ, still have MySQL 3.23. I myself make my SQL 3.23 compatible. Well, it all depends on what you'll do with the system, if its a development desktop, a server for commercial stuff, etc. MySQL 5 is not too new, it has been beta tested and alfa released. OK, it has a few bugs, but hey, that's evolution baby. We must keep in mind that not using the new features we won't step into new bugs, or am I wrong? IMHO if you have been upgrading since the first version used till now, you'll have no problems, most steps for migrating would have been covered and documentations is there for it. But most admins simply configure it and think: Its working, lets keep it in this version. If you run a commercial server or a big server for a big app, this is a big mistake, eventually a client will request a feature of a newer vesion, the app will be rewritten/rebuild to use new features, and you'll have a huge database that must go into a complex migration for about 4 versions. That's why I kept upgrading from 3 to last 4.1.x, and I plan in a few months to upgrade to 5. Yes, it is too new, and it is sad, because stored procedures will make our job much easier. I don't think its too new for the reasons mentioned above. About SP, tell me about it, if I had this a few years ago in a opensource database, today I would have more hair. I also program for MS SQL and use SPs extensively for all the good reasons. Most enterprises choose it because their developers require a good environment, MySQL is becoming another choice now, and I'm happy for it... -Original Message- From: RICHARD J. SWEENEY [mailto:[EMAIL PROTECTED] Sent: Sunday, March 05, 2006 6:54 AM To: mysql@lists.mysql.com Subject: mysql 5.0 too new? Webhosts are telling me they dont support 5.0 because it is too new and potentially unstable without reliability documentation. Im having difficulty finding a webhost that will support 5.0. Is earlier versions of mysql available? Any help here 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] -- Daniel da Veiga Computer Operator - RS - Brazil -BEGIN GEEK CODE BLOCK- Version: 3.1 GCM/IT/P/O d-? s:- a? C++$ UBLA++ P+ L++ E--- W+++$ N o+ K- w O M- V- PS PE Y PGP- t+ 5 X+++ R+* tv b+ DI+++ D+ G+ e h+ r+ y++ --END GEEK CODE BLOCK-- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring a database from a complete MySQL dump
I have a MySQL 4.1.x installation. It was upgraded to MySQL 5.0.x and then downgraded again, back to 4.1.14. I took a complete dump of all databases/tables into a .sql file, and now I would like to restore just specific databases or tables from it. Is this possible without restoring everything or without having to restore everything somewhere else, and then make new back-ups of the specific tables/databases?
Re: Restoring a database from a complete MySQL dump
You should be able to pull each table (create + inserts) out into a stand alone file table1.sql table2.sql etc... and then you can restore table by table. This might be harder depending on the size of your database tables. If you have a million rows, chances are your .sql file is huge and it would require a pretty robust text editor that could handle such a big file. In vi you could just locate the 1st line of the CREATE and the last line of the last insert (before the next create) and then do : 1,100 w table1.sql - Original Message - From: kent stand [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, March 08, 2006 1:17 PM Subject: Restoring a database from a complete MySQL dump I have a MySQL 4.1.x installation. It was upgraded to MySQL 5.0.x and then downgraded again, back to 4.1.14. I took a complete dump of all databases/tables into a .sql file, and now I would like to restore just specific databases or tables from it. Is this possible without restoring everything or without having to restore everything somewhere else, and then make new back-ups of the specific tables/databases? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Aggregate functions in ORDER BY
Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1);
Re: performance between InnoDB vs MyISAM
Hey there Ady, Philip, Thanks for the suggestions for the phenomenon. I also notice something along the course of optimisation: 1. Sorting records with huge fields (ie: blobs, text) is significantly slower than if you extract the blobs/ text fields into a separate table. The record size makes a difference in the sorting performance? 2. Sorting by int desc, int desc is much slower than int asc, int asc, where 'int' is an integer field. If (1) is generally true, perhaps to accommodate the potential increase of the table into a million records, I have to break the table into 2 tables: the original table will hold only enough fields for sorting, and the huge fields in the other. What do you guys think? Ady Wicaksono wrote: Hi Foo, MyISAM impress me on insert speed, however on many case MyISAM is not better than Innodb. If you can't use combination of them, better your break down your need to decide which one to use. AFAIK, sub query is better in innodb rather than myisam, and if you have only 200.000 records with huge amount of text, innodb is good enough, just make sure you have enough memory to increase performance. Do you need fulltext SEARCH? If yes, myisam is support this :D not innodb. Foo Ji-Haw wrote: Hi all, Just want to share and confirm my findings on a performance issue I've been experiencing. My database is strictly non-transactional, but it's got about 200,000 records in this particular table. The table has a primary index, and 2 integers - one for the date and the other for the time. Among the other fields there's a text field which usually stores a huge amount of text. One thing I notice, is that under MyISAM running the following sql: select id from mytable where id in (#subselect to extract a set of ids) order by mydate desc, mytime desc The time taken is really bad, like 90 secs. But in InnoDB it is usually 8 secs. The time difference is too crazy to ignore. Can anyone explain this? Is there something in InnoDB that creates the magic? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aggregate functions in ORDER BY
All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Signal 11 crashes on MySQL V5
On 9/03/2006 8:42 a.m., Dave Pullin wrote: I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently upgraded to V5 on all servers. Now MySQL is crashing regularly (several times per day, some days) with 'got signal 11'. My 3 Linux servers are very different machines running different software a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386 , a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL 5.0.18-0.glibc23.x86_64 , a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386 The windows machines are not having a problem. All 6 are running essentially the same application. It seems unlikely to be a hardware problem because its on 3 machines at once. It looks like a MySQL V5 problem but I can't pin it down to anything specific enough to report a bug. Anyone had similar experiences with MySQL V5? Yep, we were seeing the same thing with 5.0. We had to roll back to 4.1 the other day, 5.0 was just too unstable. I haven't reported a bug though because I couldn't narrow down the cause of the problem. Just seemed to be random crashes, several times per day (more often when under load). -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need help with a Basic Query.
Hey Guys, I was wondering if I could get some assistance with building a Simple Import Script using PHP and MySQL. Basically I'm trying to pull info out of one Table and Insert it into the other Table. Can anyone show me a simple example of this? I can figure out how to do the rest if I had a simple example. Thanks! - Clint -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Need help with a Basic Query.
That SQL 101. It's a basic INSERT / SELECT. http://dev.mysql.com/doc/refman/4.1/en/insert-select.html Scroll down for the examples. -Original Message- From: clint lenard [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 8:54 PM To: mysql@lists.mysql.com Subject: Need help with a Basic Query. Hey Guys, I was wondering if I could get some assistance with building a Simple Import Script using PHP and MySQL. Basically I'm trying to pull info out of one Table and Insert it into the other Table. Can anyone show me a simple example of this? I can figure out how to do the rest if I had a simple example. Thanks! - Clint -- 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: Aggregate functions in ORDER BY
Many thanks for that Dave. Do you know whether it's possible for MySQL to return fully qualified column names by default? For example, I'd like select * from foo; to return ++--+ | foo.a | foo.b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ instead of ++--+ | a | b| ++--+ | 1 | Rex | | 2 | Buddy| | 3 | Sam | | 4 | Lucky| | 5 | Tiny | | 6 | Snoopy | | 7 | Lady | | 8 | Tiger| | 9 | Speedy | ++--+ Thanks, Yasir All you are doing when you quote the expression like `COUNT(pets.pet_id)` is referencing the column in the select by its default column name (which is the same as the expression.) That's why it only works when the expression is a column. If you dont want the order by column to appear in the results, make your select a subselect. select a,b,c from ( select a,b,c, COUNT(pets.pet_id) as count from etc order by count ) as subtable Dave -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 9:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
bytefx native provider for .NET and mysql5
Hi there, ive been tyring to find a native provider for .NET , as I had followed the .NET example via an article on the mysql site which uses the ODBC connector, however it states on the mysql .NET info page that ODBC is slower than a native provider. My issue is the bytefx provider has an old client, possibly for Mysql 4.0 so it cant authenticate properly. Im running Mysql5 , is there a version of this that is compatible with Mysql5 ? let me know thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Aggregate functions in ORDER BY
Can you run without the Order By at all? If not, you may need to properly join the tables. I could reproduce the issue with MySQL 4.1 as well and I would go around it myself by creating a temporaty table, populating it with the aggregate, doing a select on the temp table and then of course dropping it. -Original Message- From: Yasir Assam [mailto:[EMAIL PROTECTED] Sent: Wednesday, March 08, 2006 7:10 PM To: mysql@lists.mysql.com Subject: Aggregate functions in ORDER BY Hello, I noticed something strange in MySQL (I'm using 4.1.15). If I use an aggregate function in the ORDER BY clause I get an error. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY COUNT(pets.pet_id) DESC; gives me the error: ERROR (HY000): Invalid use of group function To get round this I can quote the ORDER BY column, e.g. SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `COUNT(pets.pet_id)` DESC; This isn't an ideal solution though - in order for it to work I have to put that expression in the SELECT column list. If I want to use an aggregate function that isn't being displayed it just doesn't work because it can't find that column: SELECT men.man_name, COUNT(pets.pet_id) FROM men, pets WHERE men.man_id = pets.pet_man_id GROUP BY men.man_id ORDER BY `AVG(pets.pet_age)` DESC; gives the result: ERROR 1054 (42S22): Unknown column 'AVG(pets.pet_age)' in 'order clause' I don't want to have to put the expression I want to order by in the SELECT column list - is there any way round this? Below is the SQL code used to create the toy DB I'm using if anyone wants to experiment: Thanks, Yasir DROP TABLE IF EXISTS men; CREATE TABLE men ( man_id INT UNSIGNED PRIMARY KEY, man_name VARCHAR(255) ); INSERT INTO men (man_id, man_name) VALUES (1, 'Alan'), (2, 'Bob'), (3, 'Colin'); DROP TABLE IF EXISTS pets; CREATE TABLE pets ( pet_id INT UNSIGNED PRIMARY KEY, pet_name VARCHAR(255), pet_man_id INT UNSIGNED, pet_age INT ); INSERT INTO pets (pet_id, pet_name, pet_man_id, pet_age) VALUES (1, 'Rex', 1, 5), (2, 'Buddy', 1, 10), (3, 'Sam', 1, 13), (4, 'Lucky', 1, 3), (5, 'Tiny', 1, 7), (6, 'Snoopy', 2, 4), (7, 'Lady', 2, 9), (8, 'Tiger', 2, 6), (9, 'Speedy', 3, 1); -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Signal 11 crashes on MySQL V5
Dave What common transaction you have? INSERT? SELECT? UPDATE? DELETE? Are you using InnoDB? I have my personal experience with InnoDB and MySQL 5, with kernel 2.4 (as you use on RedHat 9) MySQL 5 is unstable on high load insert/update/delete with kernel 2.6 (CentOS, Fedora, ...) you'll get more stability but it still not stable enough. Please see http://forums.mysql.com/read.php?22,74279,74279#msg-74279 It has been confirmed as bug on MySQL 5. Dave Pullin wrote: I am running MySQL on 6 servers - 3 Linux and 3 Windows. I recently upgraded to V5 on all servers. Now MySQL is crashing regularly (several times per day, some days) with 'got signal 11'. My 3 Linux servers are very different machines running different software a uniprocessor Pentium with 512MB running Redhat9 with MySQL 5.0.18-0.i386 , a new dual XEON with 8GB running Fedora Core 4 with 64bit MySQL 5.0.18-0.glibc23.x86_64 , a old quad XEON with 4GB running Fedora Core 4 with MySQL 5.0.18-0.i386 The windows machines are not having a problem. All 6 are running essentially the same application. It seems unlikely to be a hardware problem because its on 3 machines at once. It looks like a MySQL V5 problem but I can't pin it down to anything specific enough to report a bug. Anyone had similar experiences with MySQL V5? Dave -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need help with a Basic Query.
13.1.5.1. Silent Column Specification Changes CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name [(create_definition,...)] [table_options] [select_statement] ^^^ Regards Keith In theory, theory and practice are the same; In practice they are not. On Wed, 8 Mar 2006, clint lenard wrote: To: mysql@lists.mysql.com From: clint lenard [EMAIL PROTECTED] Subject: Need help with a Basic Query. Hey Guys, I was wondering if I could get some assistance with building a Simple Import Script using PHP and MySQL. Basically I'm trying to pull info out of one Table and Insert it into the other Table. Can anyone show me a simple example of this? I can figure out how to do the rest if I had a simple example. Thanks! - Clint -- 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 5.0 too new?
Many big guys like the one in Scottsdale, AZ, still have MySQL 3.23. I myself make my SQL 3.23 compatible. Part of the reasons could be the license change in v4, of course. MySQL no longer comes for free in many cases. Martijn Tonies Database Workbench - development tool for MySQL, and more! Upscene Productions http://www.upscene.com My thoughts: http://blog.upscene.com/martijn/ Database development questions? Check the forum! http://www.databasedevelopmentforum.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bytefx native provider for .NET and mysql5
http://dev.mysql.com/downloads/connector/net/1.0.html Looks like it got implemented into an official provoider, does this mean I dont need MyODBC ? On 09/03/2006, at 3:34 PM, Dan Rossi wrote: Hi there, ive been tyring to find a native provider for .NET , as I had followed the .NET example via an article on the mysql site which uses the ODBC connector, however it states on the mysql .NET info page that ODBC is slower than a native provider. My issue is the bytefx provider has an old client, possibly for Mysql 4.0 so it cant authenticate properly. Im running Mysql5 , is there a version of this that is compatible with Mysql5 ? let me know thanks. -- 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]
