ODBC Stored procedures
Hi, may be my english is poor, sorry for this. I'll be trying with odbc 3.51 and sp on 5.0 that returns out params and didn't work (at least for me). In parameters works fine. From mysql client command line out parameters works fine. The sp makes about 50 selects, this increase a lot the traffic on my lan if I didn't use it. The way to do this work through odbc was create a temporary table into the sp and fill one record with the out values (this exists only for the connection who call the sp). From the odbc client, I call the sp then make a select over the temp table and then drop it. Someone try out params with odbc and works? any other idea? odbc 3.52 support out params? when odbc 3.52 will be released? Thanks, Alejandro mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SPs ODBC
I'll be trying to make a SP on 5.0 that returns values through odbc but it doesn't work, mysql client works fine; there is a limitation or odbc not support return values yet? ODBC Version 3.51.06 -- Greetings, Alejandro mailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re[2]: Mysql growing pains, 4 days to create index on one table!
matt on Thursday, July 15, 2004, 11:58:31 AM, wrote: Consider replicating to some slave servers and dividing reads among them. mr I already replicate to slaves, and sites will do read only queries off mr these slaves mr 99.9 % of the tables are read only anyway, the only tables we update or mr insert into, are very very small and fast. mr These big tables are daily extracts from IBM DB2 sites, in ebcdic mr format, we archive the data and users then query our site which is mr faster, unless they start doing multiple query options, then things get mr slow. mr If you query only one feild its FAST, but if you query two feilds, its mr slow, very slow, need multiple key per query support in mysql. One thing that can help (at least for me) is working with temporary tables, spliting one complex query in many littles. A simple example: 2 tables: header and details CREATE TEMPORARY TABLE tmp TYPE=HEAP SELECT hdr_code FROM header WHERE ...; ALTER TABLE tmp ADD PRIMARY KEY(hdr_code); SELECT d.* FROM details d INNER JOIN tmp ON d.hdr_code=tmp.hdr_code WHERE ...; DROP TABLE TMP; -- Alejandro D. Burne Departamento de Sistemas Asociación Médica Rosario España 401 - S2000SBI Rosario - Santa Fe - Argentina +54-0341-4252313 Interno 145 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What is the difference Between the mysql HEAP Table type and Views
A heaptable isn't aview,it's just a tablein memory, when you stop mysql thetable disappear. Are usefullfor speed selects; you don't need access hd. In the otherhand views are definitions from other(s) table(s) stored in the db, are permanent and can be updated Alejandro. ---Mensaje original--- De: Victor Pendleton Fecha: 04/13/04 11:30:05 Para: 'Abiola Aluko '; '[EMAIL PROTECTED] ' Asunto: RE: What is the difference Between the mysql HEAP Table type and Views Have you tried to update an underlying heap table? The heap table will not be updated. A view is updated when any of the underlying table(s) are updated. -Original Message- From: Abiola Aluko To: [EMAIL PROTECTED] Sent: 4/13/04 8:01 AM Subject: What is the difference Between the mysql HEAP Table type and Views I know this might sound like a rather funny question to many gurus out here, but I'm a bit confused. The example give in the mysql manual is: mysql CREATE TABLE test TYPE=HEAP SELECT ip,SUM(downloads) AS down - FROM log_table GROUP BY ip; mysql SELECT COUNT(ip),AVG(down) FROM test; mysql DROP TABLE test; ironically the example given in the postgresql manual for views looks like it does the same things as the above sql statements: CREATE VIEW myview AS SELECT city, temp_lo, temp_hi, prcp, date, location FROM weather, cities WHERE city = name; SELECT * FROM myview; Please enlighten me. Thanks Abiola Aluko. Lycos Email has 10 MB of FREE storage space. http://mail.lycos.co.uk ATT145709.txt -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Re: Problem deleteing records
Vinay, u cancreatea temporary table CREATE TEMPORARY TABLE TMP Select * from ORIGINAL WHERE 'records to preserve'; TRUNCATE TABLE ORIGINAL; INSERT INTO ORIGINAL SELECT * FROM TMP; DROP TABLE TMP; Alejandro. ---Mensaje original--- De: Vinay Fecha: 06/02/04 08:32:04 Para: [EMAIL PROTECTED] Asunto: Problem deleteing records I have a problem with a table that is too big it contains around 35,000,000 lines and each end of month i have to take out about 20,000,000 lines from it so my delete command is : delete from tablewhere column_value** on a column that is indexed. But each time i do that i have mysql that have "too many connection problem" and i have to kill and restart mysql in the middle of the process. I finish up with a huge table that i must repaire and that takes time and sometime it does not even work. i have" max connections=1000" and even this does not seem to be enough. Does anyone has a better way so that i can delete my records without damaging my table and having to restart mysql.. V!nay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] _ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Re: Problem creating sp
Victoria, sorry for waste your time with this =(
Just is a EMS MySQL Manager 2.0.1.4's error, from command line (mysql) works fine; the typo was writing the mail.
Alejandro.
---Mensaje original---
De: Victoria Reznichenko
Fecha: 01/13/04 11:04:50
Para: [EMAIL PROTECTED]
Asunto: Re: Problem creating sp
"adburne" [EMAIL PROTECTED] wrote:
I'll be trying to work with sp on win32/5.0.0-alpha-max-debug; I take the
mysql's page example to test but makes an error:
mysql delimeter |
You made a typo. You should write 'delimiter'.
- create function hello (s char(20)) returns char(50)
- return concat('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax.Check the manual
that
corresponds to your MySQL server version for the right syntax to use near
deli
meter |
create function hello (s char(20)) returns char(50)
return concat('H' at line 1
--
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.net http://www.ensita.net/
_ ___ __
/|//_ __/ __/ __ \/ /Victoria Reznichenko
/ /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED]
/_//_/\_, /___/\___\_\___/ MySQL AB / Ensita.net
___/ www.mysql.com
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
_ IncrediMail - El Email ha evolucionado finalmente - Haga clic aquí
Problem creating sp
I'll be trying to work with sp on win32/5.0.0-alpha-max-debug; I take the
mysql's page example to test but makes an error:
mysql delimeter |
- create function hello (s char(20)) returns char(50)
- return concat('Hello, ',s,'!');
ERROR 1064 (42000): You have an error in your SQL syntax. Check the manual
that
corresponds to your MySQL server version for the right syntax to use near
deli
meter |
create function hello (s char(20)) returns char(50)
return concat('H' at line 1
Additionally I create the proc table on mysql database:
CREATE TABLE IF NOT EXISTS proc (
dbchar(64) binary DEFAULT '' NOT NULL,
name char(64) binary DEFAULT '' NOT NULL,
type enum('FUNCTION','PROCEDURE') NOT NULL,
specific_name char(64) binary DEFAULT '' NOT NULL,
language enum('SQL') DEFAULT 'SQL' NOT NULL,
sql_data_access enum('CONTAINS_SQL') DEFAULT 'CONTAINS_SQL' NOT NULL,
is_deterministic enum('YES','NO') DEFAULT 'NO' NOT NULL,
security_type enum('INVOKER','DEFINER') DEFAULT 'DEFINER' NOT NULL,
param_listblob DEFAULT '' NOT NULL,
returns char(64) DEFAULT '' NOT NULL,
body blob DEFAULT '' NOT NULL,
definer char(77) binary DEFAULT '' NOT NULL,
created timestamp,
modified timestamp,
sql_mode set(
'REAL_AS_FLOAT',
'PIPES_AS_CONCAT',
'ANSI_QUOTES',
'IGNORE_SPACE',
'NOT_USED',
'ONLY_FULL_GROUP_BY',
'NO_UNSIGNED_SUBTRACTION',
'NO_DIR_IN_CREATE',
'POSTGRESQL',
'ORACLE',
'MSSQL',
'DB2',
'MAXDB',
'NO_KEY_OPTIONS',
'NO_TABLE_OPTIONS',
'NO_FIELD_OPTIONS',
'MYSQL323',
'MYSQL40',
'ANSI',
'NO_AUTO_VALUE_ON_ZERO'
) DEFAULT 0 NOT NULL,
comment char(64) binary DEFAULT '' NOT NULL,
PRIMARY KEY (db,name,type)
) comment='Stored Procedures';
Any clue? Thanks, Alejandro
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary table rights
Someone can give me a hand with this: I'll be fighting with rights over tmp tables time ago, basically I want give full temporary tables management but not for other all tables, including select right. The only way to do that work was insert in tables_priv for each user a user/tmp_table_name record granting full privileges on that temporary table There is a problem with this because 'TMP%' as table_name doesn't work in tables_priv, you must insert many rows as many different temporary tables names you think use. The question is: There is a way to improve tmp rights management? I think this is a weak side of mysql. Thnks! Alejandro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL 5.0.0 has been released
Where can I findscripts or documentation to makeSPs? Thnx! Alejandro ---Mensaje original--- De: [EMAIL PROTECTED] Fecha: miércoles 24 de diciembre de 2003 05:10:36 A: [EMAIL PROTECTED] CC: [EMAIL PROTECTED]; [EMAIL PROTECTED] Asunto: MySQL 5.0.0 has been released Hi, MySQL 5.0.0, a new version of the popular Open Source/Free Software Database Management System, has been released. It is now available in source and binary form for a number of platforms from our download pages at http://www.mysql.com/downloads/ and mirror sites. Do to an unfortunate build error in the last minute we can't provide -max binaries for 5.0.0 at this time. Sorry about that. Note that not all mirror sites may be up to date at this point in time - if you can't find this version on some mirror, please try again later or choose another download site. This is the first Alpha development release of the 5.0 tree, adding many new features (see below). As this code is currently labeled "Alpha", we do not recommend that this version be used in production environments yet! It does however pass our test suite on all our build platforms and all old features should be resonable stable. However, we encourage you to test and evaluate it and, more importantly, report any bugs or observations to our bug tracking database at http://bugs.mysql.com/. Please note, that for us to resolve a bug report, a reproducible test is required. See "How to report a bug" at http://bugs.mysql.com/how-to-report.php for more details before filing a bug report. We appreciate your support! The most prominent new feature of MySQL 5.0 is probably Basic support for stored procedures (SQL-99 style). However, there are several additional enhancements, which are planned to be implemented before MySQL 5.0 reaches beta status. Our development plan is to continue to add new features to 5.0 at least until 4.1 reaches 'gamma' status after which we will move new development to 5.1. Our man goal is to have more major releases with shorter time intervals to get out new stable features faster. THe MySQL 4.1 branch seams to be relatively stable and we will, if we don't find any new unexpected hard bugs that will require a new design decisions, make a beta release of 4.1 in January followed by a gamma release ASAP. Merry Christmas, Lenz Grimmer Michael Widenius - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany Michael Widenius [EMAIL PROTECTED] MySQL AB, CTO Helsinki, Finland -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] . _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: Temporary tables rights
Matt, thanks for your reply,this weekend I was thinking the way to do this work, because I use many times the same temporary table name for differentscriptswith differenttable structure.
All my temp tables begin with 'TMP', I thought grant something like 'TMP%' on tables_priv.table_name but looking in mysql's manual this doesn't work.
But there is a tricky,if you know thenames of your temp tables you can make an insert on tables_priv with the table name:
INSERT INTO tables_priv (host, db, user, table_name, grantor, table_priv, column_priv)VALUES ('host.localdomain','db1','user1','TMP_Liq', USER(), 'Select,Insert,Update,Delete,Alter,Drop,Index','')
and this work!
Alejandro
---Mensaje original---
De: Matt W
Fecha: sábado 13 de diciembre de 2003 22:36:54
A: adburne; [EMAIL PROTECTED]
Asunto: Re: Temporary tables rights
Hi Alejandro,
Yeah, this issue has come up before. It's not possible to GRANT DROP on
temp tables without GRANTing DROP on the whole database.
The temp tables will be dropped when the client disconnects you know,
right? And if you want to empty the table or reuse it, you should be
able to TRUNCATE it, I think.
And actually, if you can TRUNCATE the other tables (if the DELETE
privilege allows it), isn't that just as bad as DROPping them? :-)
Matt
- Original Message -
From: adburne
To: [EMAIL PROTECTED]
Sent: Friday, December 12, 2003 11:31 AM
Subject: Temporary tables rights
Hi, I'm granting users to use temporary tables as:
GRANT CREATE TEMPORARY TABLES ON db1.* TO user1;
and having grants on many other tables as:
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table1 TO user1;
GRANT SELECT, INSERT, UPDATE, DELETE ON db1.table2 TO user1;
.
but how make this work
CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1;
ok
SELECT * FROM tmp1;
Error: select command denied to user: [EMAIL PROTECTED] for table tmp1
also:
DROP TABLE tmp1;
Error: drop command denied to user: [EMAIL PROTECTED] for table tmp1
I don't want grant select and "drop" global privilege over db1 but I
want use temporary tables, there is a way to do this work?
Alejandro
.
_ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Temporary tables rights
Hi, I'm granting users to use temporary tables as: GRANT CREATE TEMPORARY TABLES ON db1.* TO user1; and having grants on many other tables as: GRANT SELECT,INSERT, UPDATE, DELETEON db1.table1 TO user1; GRANT SELECT,INSERT, UPDATE, DELETEON db1.table2 TO user1; but how make this work CREATE TEMPORARY TABLE tmp1 AS SELECT * FROM table1; ok SELECT * FROM tmp1; Error: select command denied to user: [EMAIL PROTECTED] for table tmp1 also: DROP TABLE tmp1; Error:drop command denied to user: [EMAIL PROTECTED] for table tmp1 I don't want grant select and "drop" global privilege over db1 but I want use temporary tables, there is a way to do this work? Alejandro _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: OT: MySQL NAT
Right,I've a linux box running apache and another running mysql both on my private lan andI want to connect frominternetthrough a firewall, to do that I'm using nat and forward with iptables. First I try setup it for apache (just for test) and things go right, all it's ok; but with mysql I can't connect. MySQL client don't return any error,just stay waitiing.. If I don't make nat, mysql client return: ERROR 2003: Can't connect to MySQL server on xxx.xxx.xxx.xxx (111). Obviously =) If someone can help, thanks. Alejandro ---Mensaje original--- De: [EMAIL PROTECTED] Fecha: miércoles 19 de noviembre de 2003 00:00:16 A: [EMAIL PROTECTED] Asunto: Re: OT: MySQL NAT What exactly do you mean, you want to forward a port to a mysq server in your lan? Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. . _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Re: Execute shell script
Thanks Paul for your reply,I extend a little more myquestion and try to be more specific: There is a command or function to call a shell script through a mysql server on a linux boxusing odbc as client? ---Mensaje original--- De: Paul DuBois Fecha: martes 18 de noviembre de 2003 18:41:48 A: adburne; [EMAIL PROTECTED] Asunto: Re: Execute shell script At 14:05 -0600 11/18/03, Paul DuBois wrote: At 4:47 PM -0300 11/18/03, adburne wrote: There is a command or function to call a shell script through mysql? Invoke mysql, then issue a \h command and look in the output for the line that begins with "system". I forgot to mention: The system command is Unix-only. -- Paul DuBois, Senior Technical Writer Madison, Wisconsin, USA MySQL AB, www.mysql.com Are you MySQL certified? http://www.mysql.com/certification/ . _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
OT: MySQL NAT
Someone can make nat with mysql? I can do it with apache without problems, but mysql client freeze trying to connect. Thanx, Alejandro. _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
Execute shell script
There is a command or function to call a shell script through mysql? _ IncrediMail - El E-mail ha evolucionado finalmente - Haga clic aquí
