Re: Consulta Cursores y Codigo dinamico
Ay Caramba. I think you'll find it a bit, well, pointless to post spanish in the generic MySQL list, that's what mysql-es is for. Now if you'd care to translate... :-) 2010/6/29 Fernando Siguenza > > Amigos tengo una consulta, hay como ejecutar codigo dinamico en un > procedimiento almacenado, por ejemplo en un procedimiento almacenado tengo > una variable la cual almacena una consulta algo asi: > > > > set strConsulta='Select * from clientes where ..' > > > > como ejecuto la consulta que almacena la variable strConsulta?? > > > > Otra duda que tengo es con los cursores, puedo almacenar en un cursor el > resultado de un procedimiento almacenado?? > > > > tengo un procedimiento almacenado llamado spDisponibles que me retorna > varios registros y quiero dentro de otro procedimiento almacenado llamado > spTotalDisponibles llamar al pocedimiento almacenado spDisponibles y este > resultado almacenar en un cursor para poder recorrer los registros que me > retorna este y hacer algunos calculos. > > > > > > Mi ultma consulta como puedo hacer para retornar un cursor o varios > registros calculados desde un procedimiento almacenado, por ejemplo > > > > tengo un procedimiento que me tiene que retornar el listado de articulos > con las catidades de cada uno disponibles para la venta, para lo cual tengo > una consulta que quiero que este en un procedimiento almacenado que me > retorna todos los articulos disponibles > > > > articulo1 5 > > articulo2 6 > > articulo3 9 > > > > luego en otro procedimiento caso cuantos tengo reservados > > > > articulo1 2 > > articulo3 1 > > > > con lo cual tengo que hacer la resta de cada uno y retornar el saldo > disponible, y me deberia retornar lo siguiente > > > > articulo1 3 > > articulo2 6 > > articulo3 8 > > > > Como podria hacer lo que necesito espero me puedan ayudar > > > > Saludos > Fernando > > _ > Discover the new Windows Vista > http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE > -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel
Re: Two Primary Keys
This isn't true for innodb. I think the only requirement is that you need to have a unique index on the auto increment column. We created a composite primary key + auto_increment to take advantage of clustering by primary key while satisfying unique constraint for the primary key. It worked out well for us except for the sheer size of the indexes. Kyong 2010/6/29 João Cândido de Souza Neto : > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > > > João Cândido. > > "Victor Subervi" escreveu na mensagem > news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... >> Hi; >> I have the following: >> >> create table pics ( >>  picture_id int auto_increment primary key, >>  product_sku int not null primary key, >>  picture_num int not null, >>  picture_desc varchar(100), >>  picture_data longblob >> ); >> >> which doesn't work I need to auto_increment and declare primary key on two >> fields. How do? >> TIA, >> Victor >> > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:   http://lists.mysql.com/mysql?unsub=kykim...@gmail.com > > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Uninstalling MySQL 6.x from Windows
Hello I have an old windows installation with MySQL 6.x and want to remove it so I can install latest version 5.1.48 but I tried once before with another computer and newer installation never worked, had to reinstall whole windows to make a new clean installation. How can I remove completely version 6.x so it does not affect the new installation? The problem with uninstall and new installation was that the MySQL SERVICE was unable to be installed/activated for some reason, and if I tried to run it manually the same error was produced, unable to start service... Thanks for your help
Re: Updating from 4 to 5
There are binary differences between v4.x and 5.x (5.1.x recommended).. The cleanest approach would be to do mysqldump on the old database and inject that into your new server. - michael dykman On Tue, Jun 29, 2010 at 4:04 PM, Grant Peel wrote: > nilnandan, > > I meant to say Mysqlupgrade :-) > > Further, I can use the old mysql tables, (the mysql gant tables, > user/passwd/host tables etc) and the mysqlupgrade will update them as > necessary? > > -Grant > > - Original Message - > From: Nilnandan Joshi > To: Grant Peel > Cc: mysql@lists.mysql.com > Sent: Tuesday, June 29, 2010 4:00 AM > Subject: Re: Updating from 4 to 5 > > > Grant, > > I think, you should run mysqlupgrade after copying old data in new servers. > > > Regards, > nilnandan > > > Grant Peel wrote: > Hi all, > > I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server > version will be changed from 4 to 5. > > I am assuming I can load all the users tables, and the mysql database (grant > tables and all) to the new server, then run mysqlupdate, and everything that > needs to be updated will be. > > Any comments? > > -Grant > -- - michael dykman - mdyk...@gmail.com May the Source be with you. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Updating from 4 to 5
nilnandan, I meant to say Mysqlupgrade :-) Further, I can use the old mysql tables, (the mysql gant tables, user/passwd/host tables etc) and the mysqlupgrade will update them as necessary? -Grant - Original Message - From: Nilnandan Joshi To: Grant Peel Cc: mysql@lists.mysql.com Sent: Tuesday, June 29, 2010 4:00 AM Subject: Re: Updating from 4 to 5 Grant, I think, you should run mysqlupgrade after copying old data in new servers. Regards, nilnandan Grant Peel wrote: Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
RE: Two Primary Keys
@Dušan Pavlica; I must say thank you. This is really interesting, and in the 7 years I've been using mysql and sql, I've never know this (or had, but didn't realize what I had done). This little bit of information could make for some interesting changes to a couple of my projects I am working on, where I've done this, but done it in code, rather than in mysql. Steven Staples > -Original Message- > From: Dušan Pavlica [mailto:pavl...@unidataz.cz] > Sent: June 29, 2010 11:26 AM > To: Victor Subervi > Cc: mysql@lists.mysql.com > Subject: Re: Two Primary Keys > > Hi, > > try this and you will see exactly how autoincrement behaves in MyISAM > tables when it is part of primary key. > > 1) declare table like this: > CREATE TABLE `test_tbl` ( > `field1` int(10) unsigned NOT NULL default '0', > `field2` int(10) unsigned NOT NULL auto_increment, > `field3` char(10) NOT NULL default '', > PRIMARY KEY (`field1`,`field2`) > ) ENGINE=MyISAM; > > 2) then insert some values > INSERT INTO test_tbl (field1, field3) > VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); > > 3) see what's in the table > SELECT * FROM test_tbl ORDER BY field1; > > result is: > 1, 1, 'test1' > 1, 2, 'test3' > 2, 1, 'test2' > 2, 2, 'test4' > > field2 is unique only in context of field1. > > Hth, > Dusan > > > > Victor Subervi napsal(a): > > 2010/6/29 João Cândido de Souza Neto > > > > > >> As far as I know, if you have an auto_increment primary key, you cant > have > >> any other field in its primary key. > >> > >> > > > > Makes sense. Actually, I was just copying what someone else gave me and > > adding the auto_increment, then I got to wondering, what is the purpose > of > > having two primary keys? > > TIA, > > V > > > > > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql?unsub=sstap...@mnsi.net > > No virus found in this incoming message. > Checked by AVG - www.avg.com > Version: 9.0.830 / Virus Database: 271.1.1/2917 - Release Date: 06/29/10 > 02:35:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Consulta Cursores y Codigo dinamico
Amigos tengo una consulta, hay como ejecutar codigo dinamico en un procedimiento almacenado, por ejemplo en un procedimiento almacenado tengo una variable la cual almacena una consulta algo asi: set strConsulta='Select * from clientes where ..' como ejecuto la consulta que almacena la variable strConsulta?? Otra duda que tengo es con los cursores, puedo almacenar en un cursor el resultado de un procedimiento almacenado?? tengo un procedimiento almacenado llamado spDisponibles que me retorna varios registros y quiero dentro de otro procedimiento almacenado llamado spTotalDisponibles llamar al pocedimiento almacenado spDisponibles y este resultado almacenar en un cursor para poder recorrer los registros que me retorna este y hacer algunos calculos. Mi ultma consulta como puedo hacer para retornar un cursor o varios registros calculados desde un procedimiento almacenado, por ejemplo tengo un procedimiento que me tiene que retornar el listado de articulos con las catidades de cada uno disponibles para la venta, para lo cual tengo una consulta que quiero que este en un procedimiento almacenado que me retorna todos los articulos disponibles articulo1 5 articulo2 6 articulo3 9 luego en otro procedimiento caso cuantos tengo reservados articulo1 2 articulo3 1 con lo cual tengo que hacer la resta de cada uno y retornar el saldo disponible, y me deberia retornar lo siguiente articulo1 3 articulo2 6 articulo3 8 Como podria hacer lo que necesito espero me puedan ayudar Saludos Fernando _ Discover the new Windows Vista http://search.msn.com/results.aspx?q=windows+vista&mkt=en-US&form=QBRE
Re: Two Primary Keys
You were talking about multiple fields in the primary key, not multiple primary keys. On 06/29/2010 03:51 PM, Johan De Meersman wrote: Correct, but you still can't have more than one primary key. Kind of defeats the idea of it being primary, really. On Tue, Jun 29, 2010 at 3:36 PM, petya mailto:pe...@petya.org.hu>> wrote: If you use innodb, primary key lookups are far faster than secondary indexes. Peter On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote: I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=vegiv...@tuxera.be -- Bier met grenadyn Is als mosterd by den wyn Sy die't drinkt, is eene kwezel Hy die't drinkt, is ras een ezel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: The query doesn't use the specified indexes
If cardinality is high (i.e large number of rows returned in the set for your query), then mysql may need to resort to filesort. - Ashish 2010/6/21 Octavian Rasnita > Hi, > > I have made an InnoDB table and I am trying to search using some keys, but > they are not used, and the query takes a very long time. > > Here is a test table: > > CREATE TABLE `test` ( > `id` int(10) unsigned NOT NULL AUTO_INCREMENT, > `symbol` varchar(20) NOT NULL, > `market` varchar(20) NOT NULL, > `id_symbol` int(10) unsigned NOT NULL, > `id_market` int(10) unsigned NOT NULL, > PRIMARY KEY (`id`), > KEY `symbol` (`symbol`), > KEY `market` (`market`), > KEY `id_symbol` (`id_symbol`), > KEY `id_market` (`id_market`) > ) ENGINE=InnoDB DEFAULT CHARSET=utf8 > > The search query is: > > mysql> explain select * from test where symbol='etc' order by market limit > 20\G > *** 1. row *** > id: 1 > select_type: SIMPLE > table: test > type: ref > possible_keys: symbol > key: symbol > key_len: 62 > ref: const > rows: 1 > Extra: Using where; Using filesort > > > The bad part is "Using filesort", and I thought that this is because it > doesn't like varchar or char columns for indexes, so I tried to use columns > that contain integers: > > mysql> explain select * from test where id_symbol=2 order by id_market > limit 20\G > *** 1. row *** > id: 1 > select_type: SIMPLE > table: test > type: ref > possible_keys: id_symbol > key: id_symbol > key_len: 4 > ref: const > rows: 1 > Extra: Using where; Using filesort > > It still uses "Using filesort" and it doesn't use the index id_market in > the query. > > So I tried to force using the indexes: > > mysql> explain select * from test force index(symbol, market) where > symbol='etc' > order by market limit 20\G > *** 1. row *** > id: 1 > select_type: SIMPLE > table: test > type: ref > possible_keys: symbol > key: symbol > key_len: 62 > ref: const > rows: 1 > Extra: Using where; Using filesort > > > So, no matter I do, the query doesn't want to use the specified index. > Please tell me what am I doing wrong. Or it is a MySQL/InnoDB bug? > > The current table I am testing has no records. I have also tried this on a > table that has more than 10 million records, with exactly the same results. > > Please tell me what can I do. > > Thanks. > > -- > Octavian > > > > __ Information from ESET NOD32 Antivirus, version of virus > signature database 5214 (20100621) __ > > The message was checked by ESET NOD32 Antivirus. > > http://www.eset.com > >
Re: Two Primary Keys
Hi, try this and you will see exactly how autoincrement behaves in MyISAM tables when it is part of primary key. 1) declare table like this: CREATE TABLE `test_tbl` ( `field1` int(10) unsigned NOT NULL default '0', `field2` int(10) unsigned NOT NULL auto_increment, `field3` char(10) NOT NULL default '', PRIMARY KEY (`field1`,`field2`) ) ENGINE=MyISAM; 2) then insert some values INSERT INTO test_tbl (field1, field3) VALUES(1,'test1'),(2,'test2'),(1,'test3'),(2,'test4'); 3) see what's in the table SELECT * FROM test_tbl ORDER BY field1; result is: 1, 1, 'test1' 1, 2, 'test3' 2, 1, 'test2' 2, 2, 'test4' field2 is unique only in context of field1. Hth, Dusan Victor Subervi napsal(a): 2010/6/29 João Cândido de Souza Neto As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto > I think the best, or may be the right way is to use picture_id as primary > key and a unique index to product_sku. > Yes, sounds good. So the purpose, then, is to speed lookups on fields commonly accessed. I'd forgotten that. Thanks, V
Re: Two Primary Keys
I think the best, or may be the right way is to use picture_id as primary key and a unique index to product_sku. -- João Cândido de Souza Neto "Victor Subervi" escreveu na mensagem news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... > Hi; > I have the following: > > create table pics ( > picture_id int auto_increment primary key, > product_sku int not null primary key, > picture_num int not null, > picture_desc varchar(100), > picture_data longblob > ); > > which doesn't work I need to auto_increment and declare primary key on two > fields. How do? > TIA, > Victor > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
On 6/29/2010 9:24 AM, Victor Subervi wrote: Hi; I have the following: create table pics ( picture_id int auto_increment primary key, product_sku int not null primary key, picture_num int not null, picture_desc varchar(100), picture_data longblob ); which doesn't work I need to auto_increment and declare primary key on two fields. How do? TIA, Victor Use the other syntax for defining keys: create table pics ( picture_id int auto_increment, product_sku int not null, picture_num int not null, picture_desc varchar(100), picture_data longblob, PRIMARY KEY(product_sku_int, picture_id) ); -- Shawn Green MySQL Principle Technical Support Engineer Oracle USA, Inc. Office: Blountville, TN -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
If you use innodb, primary key lookups are far faster than secondary indexes. Peter On 06/29/2010 03:34 PM, João Cândido de Souza Neto wrote: I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
I think the real question is: What´s the purpose of any other field in my primary key if the first one is an auto_increment and will never repeat? -- João Cândido de Souza Neto "Victor Subervi" escreveu na mensagem news:aanlktinyaaps4jmbbjald6kdok7lfhxlykwq0tmpt...@mail.gmail.com... 2010/6/29 João Cândido de Souza Neto > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Two Primary Keys
2010/6/29 João Cândido de Souza Neto > As far as I know, if you have an auto_increment primary key, you cant have > any other field in its primary key. > Makes sense. Actually, I was just copying what someone else gave me and adding the auto_increment, then I got to wondering, what is the purpose of having two primary keys? TIA, V
Re: Two Primary Keys
As far as I know, if you have an auto_increment primary key, you cant have any other field in its primary key. João Cândido. "Victor Subervi" escreveu na mensagem news:aanlktikzksmbx5hue0x_q3hx_68gicndghpkjdrna...@mail.gmail.com... > Hi; > I have the following: > > create table pics ( > picture_id int auto_increment primary key, > product_sku int not null primary key, > picture_num int not null, > picture_desc varchar(100), > picture_data longblob > ); > > which doesn't work I need to auto_increment and declare primary key on two > fields. How do? > TIA, > Victor > -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Two Primary Keys
Hi; I have the following: create table pics ( picture_id int auto_increment primary key, product_sku int not null primary key, picture_num int not null, picture_desc varchar(100), picture_data longblob ); which doesn't work I need to auto_increment and declare primary key on two fields. How do? TIA, Victor
RE: Mysql error causing database to not accept threads
Hi Yes, we did check the amount of free O/S memory and this was the same as during normal operating times when the system is working. Even the load averages on the system was very low. At the moment of this incident there was only 300 odd connections from the website where there are usually about 2000 + connections. As stated, the only other process running at that stage was the oracle import process, however after speaking to the person who implemented the imports scripts / process it was stated that this import makes one single connection and then imports relevant data within a single transaction. This import runs every 30 minutes so the amount of data being import is not that much and have not increased drastically so I am not yet sue as to where else to look. The only log created was that there was that no more threads could be created, even when trying to shutdown the MySQL database. I am not sure whether this could make a difference, however, but the server also runs an oracle database on the same server (not the database being imported from, only a slave oracle database)... I have also checked the ulimit settings and it seems that all the options in forums found thus far has been set to unlimited or to more than what is being suggested. Machiel Richards -Original Message- From: Benedikt Schackenberg [mailto:schackenb...@termindoc.de] Sent: 29 June 2010 10:57 AM To: mysql@lists.mysql.com Subject: Re: Mysql error causing database to not accept threads Note that some of the memory settings are per connection, if you have 63 connections, you may have up to 63 copies of the buffers, and you have pretty huge buffers even with 30GB of memory. Join buffers close to 1 G, tmp_table_size of 2 G, 63 connections could mean upto 63 copies of these, and if each connection uses the full buffers, thats close to 200GB! You should adjust these to cater to the maximum number of connections expected. Did you try to check how much free memory you have when the problem occurs? Am 29.06.2010 10:17, schrieb Machiel Richards: > ERROR 1135 (0): Can't create a new thread -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=machiel.richa...@gmail.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Mysql error causing database to not accept threads
Note that some of the memory settings are per connection, if you have 63 connections, you may have up to 63 copies of the buffers, and you have pretty huge buffers even with 30GB of memory. Join buffers close to 1 G, tmp_table_size of 2 G, 63 connections could mean upto 63 copies of these, and if each connection uses the full buffers, thats close to 200GB! You should adjust these to cater to the maximum number of connections expected. Did you try to check how much free memory you have when the problem occurs? Am 29.06.2010 10:17, schrieb Machiel Richards: ERROR 1135 (0): Can't create a new thread -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Mysql error causing database to not accept threads
Good morning all Hope everyone is well We received an error on a MySQL database this morning which caused it to be unavailable for connections. Error: ERROR 1135 (0): Can't create a new thread (errno 11); if you are not out of available memory, you can consult the manual for a possible OS-dependent bug I checked the system memory but this seemed to be fine. When I tried to restart the database, the shutdown just kept on running the whole time without shutting down the database. We then went to the drastic measure of restarting the server itself which then seemed to have sorted out the problem. The amount of connections to via the website was very minimal at that stage, however there was a script running which takes a data dump from an oracle database and imports this into the Mysql database and I suspect that it has something to do with this. I am trying to find out which settings / variables/ etc... we should check to find out where the problem is. One forum suggested changing the thread_cache_size to 512 , currently this is on 256. Hoping that someone can assist with this and much appreciated. Regards Machiel
Re: Updating from 4 to 5
Grant, I think, you should run mysqlupgrade after copying old data in new servers. Regards, nilnandan Grant Peel wrote: Hi all, I am about to move from FreeBSD 6 to FreeBSD 8. With that, the mysql server version will be changed from 4 to 5. I am assuming I can load all the users tables, and the mysql database (grant tables and all) to the new server, then run mysqlupdate, and everything that needs to be updated will be. Any comments? -Grant
