World: r3wp

;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

I have a new mysql server but I can't connect to it with %mysql-protocol 
v1.2.1 :

>> open mysql://user:[traiteur-:-localhost]/testjmg
connecting to: localhost

** User Error: ERROR 1251 : Client does not support authentication 
protocol requested by server; consider upgrading MySQL client
** Near: open mysql://user:[traiteur-:-localhost]/testjmg


I know I have to change something in the mysql server configuration, 
but does anyone can point me to what it is ?

there are two types of authentication schemes ... IIRC, onwards from 
4.1 or 4.0.1 mysql switched to stronger authentication. So - if you 
upgraded or used older DBs, you have to explicitly set old auth method 
...

It's MySQL - 5.0.51a
Do you know how to do that ?

I'm using 5.0.18 and don't have such issues. Anyway to find a fix, 
have a look here : http://dev.mysql.com/doc/refman/5.1/en/old-client.html

Start mysqld with the --old-passwords option. 
 seems the simpliest way to workaround that.

Btw, you should check if your code is not using an older version 
of %mysql-protocol.r (just in case you're reusing old source files).

Just installed 5.0.77 community server on Windows, no problem to 
log in using default password mode with mysql protocol v1.2.1.

OK, old-password is off in the server variable. Do you know where 
is the config file where this variables are sets (Ubuntu) ?

Client version is the last one.

(I just look at the variable in phpmyadmin.

Does protocol v1.2.1 works with new password method ? If so, maybe 
I just need to update the user password int he db ?*

v1.2.1 is supposed to work with both old and new passwords.

MySQL config file (my.cnf) can be in one of these places :
- /etc/
- $HOME/ 
- MySQL data folder

Ok, it works now. My bad, I put the last protocol in a folder but 
I where still loading the old one from another folder !

Can you create a database remotely?

Anyone know the syntax?

I tried:
insert db [create-db "test2"]

with db=port but does not seem to work

Is there a limit to the number of tables in a database?

Anyone know SOP for this situation:

 I have hundreds of users (not yet really) )and I need to store several 
 tables of user data for each. Should I create a seperate database 
 for each user or is it better to use a naming scheme for the tables 
 and store all of them in one database? or is it just personal preference?