AltME groups: search

If some of you still have troubles connecting to a v4+ MySQL server 
with the last driver v1.0.6, please give a try to a new beta version 
(1.0.7) found here : http://softinnov.org/tmp/mysql-protocol.r

If you've set up your MySQL server using a PHP tool like phpMyAdmin, 
your users are probably created with the OLD_PASSWORD mode, even 
if you're using v5+ servers (it's a PHP issue). My driver v1.0.7 
should allow you to connect flawlessly to such configured server. 
Please try it and give me some feedback.

docKimble, I just thought of something: what if, for MySQL, a time 
out is not the same as reconnecting to a restarted server?

if you restart mysql, there are no prior connections to handle for 
mysql. if your connection times out, I wonder if mysql then does 
something different to create the connection again.

If you restart mysql, there are no prior connections to handle for 
mysql, thus the test above works every time. If your connection times 
out, I wonder if mysql then does something different to create the 
connection again and then sometimes fails.

I would be easier to understand if you replace the word "mysql", 
by either "client" or "server". ;-) But, anyway, I think I got the 
picture.

I am using mysql-wrapper.r . In function db-last-insert-id there 
should be one less "first" command to get the result. I think this 
is a bug, pls confirm

if the behavior of the mysql-protocol has changed lately, then yes. 
when i wrote that, it needed that "first". (i used that function 
a lot so I know it worked)

Mysql Performance Tuning Best Practices:

http://video.google.com/videoplay?docid=2524524540025172110&q=google+engedu

I have some sql that works fine using mysql db < file but fails with 
syntax error when using insert db reduce [query var1 var2]

the mysql program does this automatically when you give it a file 
(using ; as separator)

I was just on the mySQL site and did a search for REBOL and was surprised 
to find 0 hits.

yes, we have very good mysql driver, I wonder why it is not submitted 
there. IIRC I talked to Doc about it, and he told me, that some time 
ago it was there, but then mySQL IIRC redesigned site or something 
like that. He wated to wait for 1.0 release. It is now released, 
works with latest incarnations, so maybe we could ask Doc to submit 
it to mysql.com folks? Or just to get his approval to do so? It would 
be nice, if rebol would be listed there ...

The new MySQL web site is much less third-parties friendly. It looks 
like MySQL doesn't want to give much attention on alternatives drivers. 
They want ppl to use their own drivers and nothing other. The new 
MySQL protocol licence is more restrictive than before. They described 
a GPLed protocol concept (which a IMO, a very stupid concept...) 
See : http://dev.mysql.com/doc/internals/en/licensing-notice.html

Btw, I've released an alpha version of a new MySQL driver implementation 
: complete rewrite from scratch to be fully async under UniServe, 
 optimized decoding in speed (faster code, streaming decoding, etc...), 
supports only servers v5+. You'll find it in the latest UniServe 
archive (see UniServe group for link)

It's event based, so the API is different from the current MySQL 
driver.

uniserve/boot/no-loop/with [protocols [MySQL]]

open-port mysql://[root-:-localhost]/test [
	on-login-ok: func [db][
		insert-port db "select * from test"
	]
	on-field: func [db col][
		print ["Field:" col/name]
	]
	on-completed: func [db rows][
		probe rows
	]
]
do-events

Oh... I'm using MySQL 4.1.9 and I have the same problem with both 
0.9.9 and 1.0.7 protocol versions.

3) Upgrade your MySQL server to latest 4.1.x version, or try with 
the latest 5.x version.

Actually what I posted in Chat applies directly to MySQL, if anyone 
happens to know.

I use the protocol with MySQL 4.1.5 and 5.0.22 and never noticed 
such a problem.

I'm preparing to switch my OS to a slack partition with MySQL 4.1 
(I believe)

MySQL Driver 1.1.0 will be released today. The new features are :

[maybe we should switch to Library -- we're off-topic for MySQL]

MySQL Driver for REBOL version 1.1.0 released.


    Changes : http://softinnov.org/rebol/mysql-usage.html#sect2.1.
    Download: http://rebol.softinnov.org/mysql/

not sure but with latest mysql-driver

Beta release v1.1.1 with a fix for the semicolon issue in SQL strings 
: http://softinnov.org/tmp/mysql-protocol.r

Public release of MySQL Driver v.1.1.1 (bugfix release) :

- Multi-statements queries parsing improved. Semi-colons included 
in quoted strings are now ignored.

- Email!, url! and other any-string! values are now correctly escaped 
in prepared statements.

- Empty block! values are now converted to "()" instead of "(NULL)".

http://rebol.softinnov.org/mysql

need help from mysql gurus ;-)  have this where clause:
WHERE MATCH (data) AGAINST ('"musŽe"' IN BOOLEAN MODE)

now here they say that doublecuoting should make the match accent-sensitive, 
but noo 8-(
http://forums.mysql.com/read.php?107,27969,29346#msg-29346
have set everything utf8 ..
thanks!

MySQL driver release v.1.1.2 Download at : http://rebol.softinnov.org/mysql

- Fix for an infinite loop issue when the server times out the connection 
(unix platforms).

- TCP keepalive option activated by default (for longstanding idle 
connections).

- Send-cmd function optimized to be a little bit faster and use less 
memory.
- Added new-lines markers to resulting recorsets.

- Added option to switch on/off new-lines marker through port/locals/newlines? 
flag.

- Recycle call removed from read-rows function. Should speed up the 
results a little bit.
- Minor source code cleanup.

MySQL driver bugtracker : http://softinnov.org:8000/curecode/project.rsp?id=5

I just found that there is a serious bug in sql-escape functions 
which is part of mysql-protocol...  new versions of MySQL for example 
autoconvert "'" to ' which MUST be escaped or you will get error 
or your query may be injected! Current sql-escape function do not 
escape such a cases.

hm... now I see there is some discussion above about injection issues.. 
maybe I'm older version of mysql and this issue is already fixed

New MySQL release 1.1.3 beta : http://softinnov.org/tmp/mysql-protocol.r

	o Fix for parsing correctly quoted empty strings. (Fix by Oldes)
		

 o Fixed an issue in case of automatic reconnection, the driver looses 
 local
	  flags like 'flat, auto-ping?, etc...(thanks to Will Arp)


 o Fix a remanence issue on /flat and /raw flags in 'send-sql after
	  automatic reconnection. (thanks to Will Arp)


 o Improved port recovery support after a failed request upon a shutdown 
 server.
	  Now the port will resume working once the server is up again.


 o An init SQL string can now be specified for an opened port. That 
 init string

   will be executed after automatic reconnections. (Useful to set, e.g., 
   a
	  specific charset value). Example : 

		  db-port/locals/init: "SET NAMES 'latin2'"

MySQL driver bugtracker : http://softinnov.org:8000/curecode/project.rsp?id=5

How to connect to DB when I need to use sock ( for example: localhost:/home/hmm/mysql/mysql.sock 
)

New MySQL release 1.2.0 beta : http://softinnov.org/tmp/mysql-protocol.r

o Changed behaviour and syntax of the 'read command :

	read mysql://[root-:-localhost]/
	==> return the list of databases

	read mysql://[root-:-localhost]/db
	==> return the list of tables in database "db"

	read mysql://[root-:-localhost]/db/tbl
	==> return the description of table "tbl" in database "db"

	read/custom mysql://[root-:-localhost]/db ["...sql query..."]

 ==> execute the sql query on database "db" and return the resultset.

MySQL driver release v.1.2.0 - Download at : http://rebol.softinnov.org/mysql
- Changed behaviour and syntax of the READ function.
- Fix for parsing correctly quoted empty strings. (Fix by Oldes)

- Fix for the driver loosing local flags, like 'flat, 'auto-ping?,... 
when reconnecting (thanks to Will Arp)

- Fix a remanence issue on /flat and /raw flags in 'send-sql after 
automatic reconnection. (thanks to Will Arp)

- Improved port recovery support after a failed request upon a shutdown 
server.
- An init SQL string can now be specified for an opened port.

Read the comments: The CEO of MySQL sets the story straight.

Here's a handy function to test if a MySQL server is up :

mysql-alive?: func [/host ip /port n /local p][
	either attempt [
		p: open/direct/no-wait join tcp:// [
			either host [ip][127.0.0.1]
			#":"
			either port [n][3306]
		]
	][
		close p
		yes
	][
		no
	]
]

(i.e. the search client protocol that can talk to Sphinx). You can 
also compile a client in mysql and then use sql for your queries.

here is a short and concise presentation about sphinx http://www.scribd.com/doc/2670976/Sphinx-High-Performance-Full-Text-Search-for-MySQL-Presentation

Guys, what do you think about Tim Johnson's ml remark? Could we help 
him somehow?

-----------------------------------

I've been using mysql-protocol (Mysql Scheme) and cgi for many years 
now. I 

really like it, but I think that /command would bring some advantages, 
if
it can directly access the "C" API for MySQL.


Example: Using the mysql scheme on a tcp/ip connection, I have to 
code

the escaping of special characters for insert and updates and code 
the
unescaping of special characters for retrieval. 


Using the C API from other scripting languages, the mysql_real_escape_string()
function handles the escaping _appropriate_ to the version, and
queries are similarly unescaped.

about escaping, Nenad's driver has such functions already, so i don't 
see that as an advantage for the native driver. Qtask has a native 
driver too (for speed reason with large record sets), but remember 
that when you use the mysql c library either your app has to be GPL 
or you need a license from MySQL (Qtask has a license).

I have a mysql wrapper that I use in production but haven't released 
because it need cleaning and docs, but if there is interest I could 
release for testing as is. id does things like:

print .db/get/all/sort/debug [mailing/m mailingLog/ml] [ml.dc m.email 
ml.url] [{ml._mailing=m.id}] [m.email asc ml.dc desc]

SELECT `ml`.`dc`,`m`.`email`,`ml`.`url` FROM `mailing` `m`,`mailingLog` 
`ml` WHERE ml._mailing=m.id ORDER BY `m`.`email`,`ml`.`dc` DESC
print .db/set/debug 'mailing 12 reduce['active false] 
UPDATE `mailing` SET `active`=0 WHERE id=12.0

print .db/get/all/flat/debug '_node_tree [_node _media] [{_tree=? 
AND _issue=?} 5443 22]

SELECT `_node`,`_media` FROM `_node_tree` WHERE _tree='5443' AND 
_issue='22'
..

graham, i'm not entirely sure, in principle the GPL2 is as you say, 
however i'm not sure if MySQL has a different interpretation here. 
iirc the GPL3 states that being a server for users is the same as 
releasing the software.

well, not all of qtask, but again i don't really know. i know they 
talked to mysql and all is fine. i'm not implying they paid or are 
paying money, since i don't know. my point is that Qtask has permission 
from MySQL to do what they do, while you may not if you were to use 
the same code. Is that more clear?

Never used mysql before .. and just downloaded Nenad's driver.

docs say \_ is a space

>> insert db "select middle\_name from patientrecord"

** User Error: ERROR 1064 : You have an error in your SQL syntax; 
check the manual that corresponds to your MySQL server ve
rsion for th...
** Near: insert db "select middle\_name from patientrecord"

MySQL driver v1.2.1 released. See more at http://softinnov.org

For a long time, I was reluctant to change the date zero behaviour 
(error throwing), because this "feature" of MySQL look, to me,  like 
very bad design. But recently, I hit the problem too when I inserted 
badly formed dates in a table and wasn't able to read the records 
anymore with my driver.

James: sorry for the delay, still not found time for docs but her 
it is "as is.."  /debug is your friend, get started, load Dock driver 
, then load  http://reboot.ch/rebol/mysql-wrapper.txtthen:
;set a list of connections:
.db/databases: [
  ;local
  mydb1.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb1
  mydb2.local mysql://user:[password-:-127-:-0-:-0-:-1]/mydb2

  ;online
  mydb1 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb1
  mydb2 mysql://user:[password-:-127-:-0-:-0-:-1]:3307/mydb2
]
;use a db (if not open will open it automatically):	
.db/use 'mydb1

I've just upload a new experimental 1.3 version of MySQL driver supporting 
mutiple queries, stored procedures and multiple result sets. Reading 
mutiple results is done by calling COPY for each result. Download 
at http://softinnov.org/tmp/mysql-protocol-41.r

New version 1.3 of new MySQL driver released as beta. (same URL).

Changes:


- Fixed the multiple result sets end-of-stream issue. Now an extra 
COPY will
  return NONE, marking the end of the data stream.

- Fixed error message parsing for protocol 4.1.


- Added SQL request delimiter property to port/locals/delimiter (default: 
#";")

Here's an example showing the new possibilities combined (multiple 
queries support, setting custom delimiter and multiple results support) 
:

db: open mysql://[root-:-localhost]/mysql
db/locals/delimiter: "*;*"
insert db "show tables; show databases"
copy db ; <= returns tables block!
copy db ; <= returns databases block!
copy db ; <= returns none!

It looks, that there is something wrong with MySQL procedures:
using mysql.r
mysql> CREATE PROCEDURE myproc() SELECT 'it works!';
mysql> call myproc();
+-----------+
| it works! |
+-----------+
| it works! |
+-----------+
1 row(s) in set
mysql> call myproc();
mysql> 

On the second run it returns nothing.

using it in cheyenne gives error:
Error Code :  	800
Description : 	user error !

ERROR 1312 : PROCEDURE webcore.myproc can't return a result set in 
the given context
Near : 	[do-sql 'webcore "call myproc();" txt 3195]
Where : 	none

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

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.

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.

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

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

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:

(mysql-protocol-41.r)

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?

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

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 ?

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 ?

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).

Command adds encryption, ODBC, Oracle, (slower native) MySQL, and 
SSL.

I'll intend to use Rugby + mysql/postgres unless RT brings out LNS 
in time.

I'm getting errors when I try to insert a binary file into sqlite


I have no problem when I read/binary an image from disk and insert 
it but when I download it from a mysql db its saving as those crazy 
text characters.


I'm converting it back to binary with "to-binary" and when I probe 
it it looks right but it keeps converting back to the original mysql 
output...

Any ideas what might be going on?


I can view the outputed image  from mysql when I use to-binary so 
I know that its not currupted.

I'm able to get a large set of results from mysql and use it (View 
the images in a layout) but when I try to insert this data into sqlite 
it seems to get currupted...
It sounds like a sqlite problem...

I understood the text in link that if you get a lock, you wait for 
a while and try again.. and that by this method even it scales to 
quite big number of concurr processes compared to mysql for example

He tried only 100 times (by looking at the example he gave) .. and 
I don't know what he means by 500 >>> in line with mysql 

and others- at 500+ simultaneous users you start to get about a 10% 
drop 
because of lock overhead. <<< 


Most probably not 500 processes writing all the time ...  without 
delays

I saw wil reported 200 reqs/sec with an Ajax/DB (mysql) app on a 
dualcore

off-topic, but - has anyone thought about abstracting rebol db access 
already? kind of rdbc (odbc, jdbc)? Especially with R3 model coming? 
db.device? - use rif, mysql, sqlite, rebdb, posgress, mssql, etc. 
The question is, if it is possible to support them all via one interface?

I'll add Cheyenne and my other projects (MySQL driver, ...) within 
CureCode once I fix all these annoying little bugs we've found yesterday. 
So I'll wait til the next CureCode release, which should happen by 
the end of this week.

modules [
;	userdir
	internal
	static
	action
	fastcgi
	rsp
	ssi
	alias
;	embed
]

globals [
	bind SSI to [.shtml .shtm]
	bind fastcgi to [.php .php3 .php4]
	
	bind-extern CGI to [.cgi .r]
	bind-extern RSP to [.j .rsp]
	
;	user  nobody
;	group nobody

]

default [
	root-dir  %/c/!Docs-private/Xidys/cheyenne-r0914/Cheyenne/www/
	
	default [%index.html %index.rsp %index.php]
	
	on-status-code [
		404	  "/custom404.html"
	]
	
	webapp [
		virtual-root "/testapp"
		root-dir %www/testapp/
		auth "/testapp/login.rsp"
		debug
	]
	
;	"/" [
;		redirect http://softinnov.org
;	]
	
;	extern fastcgi [
;		command	 "php -b $port"
;		pool 	 min 1 max 4
;		server	 127.0.0.1 ; port 1234
;		root-dir "/home/dk/fcgi/"
;	]
] 

;rebol.si.org [
;	root-dir %//dev/si-org/old/
;	default %main.html
;	
;	redirect 301 "/mysql*" "http://si.org/rebol/mysql.shtml"
;	redirect 301 "/rebox*" "http://si.org/rebol/rebox.shtml"
;	redirect 301 "/"	   "http://si.org"
;]

; ###
; To add a new virtual host just duplicate and fill the following
; example host :
;
; my.vhost.com [
;	root-dir %/www/			; documents root directory
;	default [...]			; default files
; ]	
; ###

Does your RSP script use my MySQL driver ? I've fixed a bug related 
to that today : http://softinnov.org:8000/curecode/ticket.rsp?id=10031

Reichart - I am ok with testing :-) i don't need Cheyenne right now, 
but I want to help it get running. And later on, I will have my coding 
environment with me. Now someone suggest how to make mySQL non installable 
distribution, which would start db server withCheyenne start. Being 
able to do that, we would have something really cool! :-)

Doc - currently on vacation with my Vista notebook. That one line 
fixes the problem! Tested under IE 7.0 and FF 2.0! Thanks a lot. 
Now the last thing is to get mySQL into non-installable version :-)

I'm glad to hear that it's finally working on your Vista laptop. 
Why not use SQLite instead of MySQL ?

BTW, there's a MySQL version without installer (in ZIP format for 
Windows), see : http://dev.mysql.com/downloads/mysql/5.1.html