World: r3wp

Oldes: are you sure you're using the 1.3 beta mysql:// driver ? You 
can find it here : http://softinnov.org/tmp/mysql-protocol-41.r

Calling stored procedures is not supported in driver versions < 1.3.

I thought I'm, but the version was older.. now it's working fine:)

But there must be a bug anyway .... the first one shows that..  next 
sql query after the procedure call returns none.

I've send more info into private in R3-alpha... anyway.. I wonder 
where it's possible to find some mysql protocol spec. Do you just 
do reverse engineering?

found it http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol

I used to reverse-engineer, but since 5.0(?) MySQL AB documents the 
protocol with sometimes (intentional?) blocking errors.

After analyzing the report you sent to me about issue with sproc. 
MySQL has a odd behaviour, it seems to always return 2 result sets 
for a sproc even when you're expecting only one (the second will 
then be empty). So, after calling a sproc you have to call COPY twice 
(or once after a SEND-SQL) to flush the remaining data. I'm looking 
in the driver to see if I can automate this flushing process.

Improved version of MySQL driver 1.3 beta released at http://softinnov.org/tmp/mysql-protocol-41.r

Changes:


- Improved automatic flushing of unread data. Now you can forgot 
the second COPY
  on sproc calls (should be robust, but needs testing).
  

- Multiple SQL requests are now sent as one packet by default. No 
more need to
  change the db/locals/delimiter value for 4.1+ servers.

I might drop out the SQL requests splitting code (and also the delimiter 
option) from the driver as now everything is sent as-is to the server. 
It might still be useful for big SQL files, not sure what the best 
strategy should be in such case (send file as one big packet, or 
split it and send each request one by one) ?

Bugfix revision for MySQL driver 1.3 beta at http://softinnov.org/tmp/mysql-protocol-41.r

Changes:


- Fixed a regression bug appearing when trying to open a connection 
without a database name.

- Fixed "port not open" error after automatic reconnection.

The change I did on [Fri 21:21] version on the way SQL requests with 
multiples statements are sent to the server, might not be a good 
idea for sending big SQL batch files to the server.  The previous 
method (slicing SQL requests and sending them one by one to the server) 
wasn't that bad (could allow streaming the reading of a big SQL file 
from disk). Maybe it should be good to let the user choose how the 
driver should send multiple SQL queries.

Thanks Dock! 8)

http://softinnov.org/tmp/mysql-protocol-41.r

here is updated wrapper, http://reboot.ch/rebol/mysql-wrapper.txt

Will, what does the wrapper do compared to the MySQL driver?

it is just a utilities wrapper that works on top of the mysql-protocol.r, 
it makes queriing mysql more rebolish

;without wrapper:
do http://softinnov.org/tmp/mysql-protocol-41.r
db: open mysql://localhost/db1
nodes: send-sql/named {SELECT `id` `name` `data` FROM `node`}

node: send-sql/named {SELECT `id` `name` `data` FROM `node` WHERE 
`id`='1'}

;with wrapper:
do http://softinnov.org/tmp/mysql-protocol-41.r
do http://reboot.ch/rebol/mysql-wrapper.txt
.db/databases: [
	db1 mysql://localhost/db1
	db2 mysql://localhost/db2
]
.db/use 'db1
nodes: .db/get/all 'node [id name data] none
node: .db/get 'node [id name data] [{`id`=?} 1]
;if you follow the rule to name your primary-key "id",
;you can use this shorter version:
node: .db/get 'node [id name data] 1

;/debug is your friend, use it to see the generated query

.db/get/debug 'node 'id 1   
;   "SELECT `id` FROM `node` WHERE id=1.0 LIMIT 0,1"

thanks - is there a page somewhere describing it in more detail?

sorry it is still rought and there is no docs, thought some comments 
in the code exists, I use it in production so, solid it must be.

and I will be glad to answer any questions and add feature requests 
8)

use /debug it will print query to the console without sending it 
to the server so you can play and understand how it works

just installed mysql on ubuntu 8.1
but can't login ...

get "Access denied for user ['root'-:-'localhost'] (using password: No 
)

I thought root was automatically created with no password??

What does read dns://127.0.0.1 give you? It may be related to a mismatch 
in the localhost name mapping, so you have to add access rights for 
root@... (or [root-:-127-:-0-:-0-:-1]) in MySQL to workaround that issue.

I think that someone had setup a password ... so I purged mysql, 
deleted my.cnf and reinstalled.  That worked.

Doc, the read-packet function in mysql-protocol.r is missing one 
word in the /local list. Line 538:

read-packet: func [port [port!] /local packet-len pl status][

should be:

read-packet: func [port [port!] /local packet-len pl status tmp][

(mysql-protocol-41.r)

Thanks for the report, I'll fix that in the next beta release. There's 
also other fixes from Will pending.

. .

I have installed mySQL and Oracle as part of my studies for improving 
TretbaseDB.  I'm curious from others that use mySQL with REBOL as 
to what they dislike about the interoperablility or what they like 
most about it.

I'm trying to upload a binary (image) file to a mysql DB. When I 
retrieve it teh binary data seems to have changed and I can not display 
the image. I was able to do it with sqlite with no problem.
I'm using the mediumblob field type for the image data... 
Any ideas what I might be doing wrong?

ca you diff the binary data of the image before you send it to db 
and after retriving it? also once the data is inserted thru mysql-protocol 
can you retrive the image from the db with another client and see 
if it works?

diff?

http://en.wikipedia.org/wiki/Diff

can you diff..

btw, I suggest not to store images in the db, store it somewhere 
on your hardrive and put in the db a pointer to the file, if you 
really need it and do not find ehat's wrong, ping me in the weekend 
and I can test that here, althougt I use other binary data with mysql-protocol 
with no problem, not sure if it apply but check also encodings for 
mysql storage, etc

ehat -> what

I got it....
I have to convert it back to binary. ("to-binary") 

I assumed that if I was giving it a binary file it would remain in 
binary just as sqlite treated it....The field attributes state "binary".

Thanks for the help, Will!

Is there a MySQL guru around ? I need to optimize this query:


SELECT A.cod, A.date , SUM(B.amount) AS amount, COUNT( * ) AS numrow 
FROM A INNER JOIN A AS B ON A.cod=B.cod AND B.date <= A.date WHERE 
A.cod IS NOT NULL GROUP BY A.cod, A.date


This return "running sums" (partial sum for every date and every 
cod). 
Both cod and date are indexed.


In a table of about 100'000 records it takes 104 sec to complete 
while in SQL server 2005 (the same query, on the same data, on the 
same index ) it takes 3 sec !

I've tried to use MyIsam, InnoDB, MEMORY storage, and used BOTH btree 
and hash index.
I've tried to FORCE Index  for Join and Group too.

Any suggest ?

is there any reason why you join A on A? also.. have you tried to 
EXPLAIN the query? http://dev.mysql.com/doc/refman/5.0/en/explain.html

> is there any reason why you join A on A?

If I have these records:

cod 	date		amount
A	2009/03/01	10	
A	2009/03/03	30
A	2009/03/04	20
A	2009/03/07	5
B	2009/03/02	17
B	2009/03/10	5


That query give me the sum of previous amounts for every date/cod: 

cod 	date		amount	NumRow
A	2009/03/01	10		1	
A	2009/03/03	40		2
A	2009/03/04	60		3
A	2009/03/07	65		4
B	2009/03/02	17		1
B	2009/03/10	22		2		

I don't know if there's a better method without  using join

> also.. have you tried to EXPLAIN the query?


Yes, explain returns that  the correct indices are used. Not very 
informative.

Davide, first: NULL value are evil (as are duplicate rows).My guess 
the cost is the fact that your query probably runs a fulll table 
scan as it needs to sum all of the rows... So table partitioning 
will help a lot. My guess is also that Oracl, SQL Server and perhaps 
PostGreSQL are smarter with their table optimziers and query rewriters. 
HTH

Also, change the count(*) to count(1), so MySQL doesn't push the 
full contents of the join through the count processor.

> My guess is also that Oracl, SQL Server and perhaps PostGreSQL 
are smarter with their table optimziers and query rewriters


Postgres is about 2 times faster than MySQL in this query, but the 
execution time grow alot as num of records increase. 
So I really don't know how good is compared to MySQL.
Oracle, I would try, but I have no time :-P

> Also, change the count(*) to count(1)

Thanks, good one.


The real tables will be more large (5 M record or more), so small 
optimizations would be not sufficient
I'm tryng a different approach, using one simple stored function:

create function running_total (cod VARCHAR(50), adder DECIMAL)
RETURNS DECIMAL
	BEGIN
		IF @last_cod <> cod THEN
			BEGIN
				SET @running_total = 0;
				SET @last_cod = cod;
				SET @num_row = 0;
			END;
		END IF;
		SET @running_total = @running_total + adder;
		SET @num_row = @num_row + 1;
		RETURN @running_total;
	END

and using as select:

SET @last_cod = ''; 
SET @running_total = 0; 
SET @num_row = 0; 

SELECT cod , date, running_total(cod , amount), @num_row FROM a ORDER 
BY cod, date;


This approach seems really fast : processing and inserting 100'000 
records  took less than 1 sec. instead of  281,73 sec. with SQL join