AltME groups: search

something like a altme:// protocol loadable within rebol, similar 
to the mySql protocol by Nenad, would be really effective.  reading 
your post from a file browser and saving new posts to it from ANY 
rebol app.    :-)

A real-world example of this is.. someone going to a party.

The party is in full-swing and the newcomer would venture from group-discussion 
to group-discussion.

i.e. Now that I am at the party, I know there is repeated 'hot discussions' 
going on with that group over there (altMe).. while the group in 
the corner (mysql) isn't talking.. they are just eating their chips 
and sitting quietly.

And to parallel this.. when I join AltMe for the day.. I would not 
be flagged of old-conversations that happened while I was logged 
off.. I would only be flagged of conversations that are happening 
since I logged on.

Now, that doesn't keep me from going to those other groups and seeing 
the messages that have been posted while I was logged off, it just 
keeps me in touch with the party.

can someone tell me if they are also getting erratic behavior from 
the (EXCELLENT!) mySql Client from DocKimbel ?

it gives me an access denied error, from the mysql server, yet I 
am right in the midst of a heavy bunch of requests...

we are using v4 of mySql ...

I was thinking there might be a timeout value which is set a little 
too short, but I am such a neophyte when it comes to tweaking ports 
and with mySql in general...

hum, I am calling  wait  0.01  pretty often within the script to 
allow the asynchronous rebol core some breathing time which REALLY 
rocks btw... scanning multiple dirs, while letting the UI and mySql 
to work at the same time , I'd not be able to get it running so easily 
in any other language.... :-)

CGI are slow, I'm not sure that you can easily overload a MySQL server 
that way. Did anyone experienced such issues (access deny errors) 
from CGI ?

Jaime: i have functions to do that, you'll find them in the mysql-wrapper.r 
script that comes with mysql-protocol.r

Like Gabriele, I have sometimes error with CGI connecting MySql but 
the server seems to not be loaded.

I am having problems connecting to mysql on windows XP. The error 
message I get is "Client does not support authentication protocol". 
I'm using localhost as the host and have granted all priviliges for 
['user''-:-'localhost'']  identified by ''password' .... etc. I'm used 
to linux, and it''s been

hmm.. and you are using Mysql-prot.r ?

(or whatever the Rebol mysql protocol is called)?

There are some gotchas using 'localhost'. Now on my linux machine, 
when I changed my I.P. from 192.168.1.1 to 192.168.1.6, I then had 
to connect using my machine's hostname (which was "linus") . If I 
attempt to connect using this machine's hostname (lucy) I get : MYSQL 
ERROR 1045 : Access denied for user ['tim'-:-'lucy'] (using password: 
YES). And I *have* also granted privileges to [tim-:-lucy] indentified 
by 'password''

Tim: MySQL 4.1 uses a new encryption method for user passwords, try 
add a new user assigning the password with the function OLD_PASSWORD('pass') 
rather than PASSWORD('pass'), and attempt   open mysql:// ... etc. 
 with the new user. Hope this helps.

I'm going to try it with the mysql-protocol module that I am using 
on my desktop. Just a minute...

yes gabriele, it works fine for this ->  char *mysql_info(MYSQL *mysql) 
 points to a char, but this -> MYSQL_RES *mysql_list_tables(MYSQL 
*mysql, const char *wild) delivers the integer pointer again as string. 
I know I have to provid a struct! which will be filled by passing 
the pointer to the routine but can I pass just a empty block instead 
a struct! ?, or better pass the recieved integer pointer later to 
a block ?

i think you should declare the return value as a struct. how is MYSQL_RES 
defined?

Dummy question: is it possible to insert more than one SQL statement 
in one time ?
ie:

insert mysql-db {INSERT INTO table VALUES (1, "USA"); INSERT INTO 
table VALUES (2, "FRANCE"); INSERT INTO table VALUES (3, "ITALY")}

This works with some SQLs -- not trie dit with mySQL
 INSERT INTO table
   select (1, "USA")
   union all  select (2, "FRANCE")
   union all  select (3, "ITALY")

According MySQL doc, I can use this syntax for INSERT :

But I can't do that for UPDATE !!

So I need to know it the Rebol MySql protocol allow to pass several 
SQL statements in one time.

Apologies on Insert -- I'd assumed from the fact you'd asked the 
question that MySQL did not support the Values syntax.


Update is trickier as you are potentially changing a whole load of 
values.

Best way is to have the update data in a 2nd table, and then select 
as appropriate.

This is the way I'd do it -- don't know if it'd work for mySQL (assumes 
product is identified by 'prod-code)
insert  ignore into Commands
             select * from products;

update commands 
    where not prod-code in 

         select prod-code  from product  where command.prod-code = products.prod-code
          set quantity = ????;

I think I mean 
   where prod-code in
not NOT in
(the code is not tested, and may not be possible in mySQL)

Joe Celko's SQL for Smarties is probably the best book for getting 
up to speed.

dunno if newer mysql supports subselects, but it didn't in the past

4.1 seems to support them. Dunno if they work though :-)
http://dev.mysql.com/doc/mysql/en/subqueries.html

I have a problem when the connection times out and mysql-protocol.r 
wants to reconnect. It looks like it hangs, but on closer inspection 
with trace/net on just gives an infinite amount of low-level reads 
of length 0 bytes.

I can close and reopen the connection, and it works again, but I 
can only do this 5-6 times before it refuses to connect to the mysql 
server. Is this a known problem?

But I'm not sure if it's not problem of the MySQL itself (for example 
some way how to awoid attacks

MySQL server version: 3.23.37

Tom: the select above is actually safe. MySQL keeps the last insert 
id on a per-connection basis

Hello, is there anyone working with Rebol Command and Mysql 4.1.x 
? It appears that the authentication protocol has changed and Rebol/Command 
can not connect to MySQL 4.1.x. But it works fine with MySql 4.0.x.

have you tried free mySQL driver from DocKimbel?

Is there any conclusion about DocKimbel's mySQL driver and 4.11 server 
release? Does it work or no? In terms of one project, where we exchange 
data, client agrees to our proposed way, only if we import data to 
their tables on their server. It is Debian linux, mySQL 4.11 or so, 
so before I produce some script and give it to them, I would like 
to ask if the problem of connection/authentication is resolved? Thanks 
a lot ....

Hmm, so noone uses mySQL here? :-)

btw - other site will use rebol plus mysql script in terms of Cron 
job - how should I launch it? I hope they know how to define Cron 
job, as I don't know ... should it be similar to CGI script? Or? 
Simply "cron script" calls something like \path\to\rebol\rebol \path\to\my-script\myscript.r? 
What arguments eventually? -s?

Hi Petr, 


Not sure if this is what you are asking about mySQL "Can I update 
particular fields (columns) in table?" but if you want to change 
a value you use SET.  e.g.  in mySQL protocol format


query: rejoin[{update logons set rs='}keyOrder{' where userName = 
'}cgi/userName{'}]
insert db reduce query

I have the new MySQL Version 5 Beta installed on my development Notebook 
(WinXP). I was devoloping a Script acessing a current Version 4.1 
of MySQL without any problems and i thought about continuing dev 
work at home. There i ran into an error saying that the autthetification 
protocol of the client is not supported and i should update my client, 
which is the mysql-protocol from DocKimble, when acessing the local 
version 5 Beta of MySQL. Is this a known problem and does anybody 
knows to work around this?? Does anybody know what changed to authenfication 
that is not supported by the protocol??

Re: MySQL Auth.   IIRC  http://dev.mysql.com/doc/mysql/en/old-client.html
I think we used this option "Tell the server to use the older password 
hashing algorithm" and continued without a house call.  If that does 
not work, let me know and on Monday I will ask the programmer who 
did it what did work.

I use mySQL admin utility, which sets old-passwords item in my.ini 
file, yet Doc's mySQL driver does not work ... now I am at the end 
....

the trouble also is, remote site may not be willing to use old-fashioned 
passwords ... what then? No guru to adapt mySQL driver? Any pointers 
to new authentication methods?

Seems one can enable passwords on a per-user-base? search on the 
page: 
mysql> SET PASSWORD FOR
-> ['some_user'-:-'some_host'] = OLD_PASSWORD('newpwd');

huh, ok. Finally can test ... while I have set old-passwords in my.ini, 
restarted service, it is not enough - so I installed mysql administrator 
app and changed user password, server probably needs to regenerate 
them to behave correctly ..

Here is something more: http://dev.mysql.com/doc/mysql/en/password-hashing.html

what is your common rebol syntax you use mySQL driver with? I find 
it a bit difficult to use Doc's block mode, as I have to provide 
it with exactly the same amount of question marks, as there is amount 
of columns in the table (talking of insert here)

does anyone use non-rebolish, but string mode (eg. using 'compose), 
with direct mySQL syntax? (which allows constructs as for e.g. insert 
into mytable (col1, col8, col9) values ("A", "B", "C") ... so you 
don't need to care about the rest of arguments for e.g.?

hmm,  maybe I am already decided, thanks for any input, but it seems 
to me easier to directly compose valid mySQL syntax, than to think 
how to overcome rebol block conversion syntax, as I can't know what 
cases Doc's driver is able to parse ...

This is related, I think, to my notes about VID and MySQL in the 
View section. In a test that we ran in 2004 we were able to load 
1,000,000 rows in under 30 minutes.  We did not investigate further 
but we thought we could improve this by running parallel loads and 
putting it on a real server instead of a laptop.  This volume was 
equal to the annual volume of  the transactions we were interested 
in so would represent a journal of everything that happened to this 
app as a keyed transaction in one year.   


From that 1,000,000 row database, we were able to create an HTML 
report based on some selected criteria in 2.5 seconds.  

All tests done with REBOL View using Doc's mySQL protocol.

I am reopening the issue of mySQL protocol not being able to connect 
to newer >= 4.11 mySQL version database. I decided to consider this 
task as being a proof if the community is, or is not able to effectively 
resort some issues, which clearly show as a show stopper for some 
rebollers (as can be seen on ml). If we are not able to effectively 
resolve this issue, then I have to ask - what actually are we able 
to sort?

Yesterday afternoon I spent investigating Doc's scheme, and here 
are my conclusions:


- The part of code responsible for password communication with the 
server is in the 'scrambler object. The function whish establishes 
connection with server is 'do-handshake


- rebol's mysql protocol seems to distinguish protocol version 9 
and 10, and I was not able to find out, what does it mean on mysql's 
side of things. It also seems to me, there is long-password item 
already in the stack, but dunno if related, it is just osme constant. 
Protocol version 9 and 10 use different hash functions and different 
crypt functions. I really don't know, where does DocKimbel find out 
how to implement those functions, maybe by looking into mySQL source, 
so I downloaded them


- to read more about passwords in mysql, go here - http://dev.mysql.com/doc/refman/5.0/en/password-hashing.html
. Maybe it is not protocol 9 and 10 related, as you may use old password 
scheme even with new databases. The difference is as follows - starting 
from mySQL 4.11, passwords are stored in 41 bytes wide field, whereas 
with older versions, it was 16bytes. New passwords always start with 
* (asterisk) char, to be easily distinguished ...


- how things might work? Mysql sends scrambled password, and Doc's 
scheme stores it in 'crypt-seed variable.  Data is being sent by 
the protocol by 'send-packet function, where for the password part, 
scrambler/scramble function is used, which calls crypt-v10 or crypt-v9, 
according to protocol versions. Those function use hash-v10(9), floor 
and other functions ...


I think that we somehow need to find out, how to compute hash for 
not 16bytes, but for those 41bytes ...

Now - sorry if I am breaking some licenses, but I will post some 
stuff to my website, and remove it once we are finished:

http://www.rebol.cz/mysql/mysql-protocol.r
http://www.rebol.cz/mysql/password.c

maybe this is better description:

The password is saved (in user.password) by using the PASSWORD() 
function in
  mysql.


  This is .c file because it's used in libmysqlclient, which is entirely 
  in C.
  (we need it to be portable to a variety of systems).
  Example:
    update user set password=PASSWORD("hello") where user="test"
  This saves a hashed number as a string in the password field.

  The new authentication is performed in following manner:

  SERVER:  public_seed=create_random_string()
           send(public_seed)

  CLIENT:  recv(public_seed)
           hash_stage1=sha1("password")
           hash_stage2=sha1(hash_stage1)
           reply=xor(hash_stage1, sha1(public_seed,hash_stage2)

           // this three steps are done in scramble() 

           send(reply)

     
  SERVER:  recv(reply)
           hash_stage1=xor(reply, sha1(public_seed,hash_stage2))
           candidate_hash2=sha1(hash_stage1)
           check(candidate_hash2==hash_stage2)

           // this three steps are done in check_scramble()

So - I did some homework here, but I am at my limits (well, maybe 
not, but I will be very slow from now on), whereas we have ppl knowing 
C here, and ppl who did some hashing etc. for Beer (Ladislav), so 
guys, if you find some 10 minutes of spare time, please at least 
try to give me some pointers here. As I said - the world is upgrading 
mySQL to 5.0 now, so 4.11 is older, not to mention 4.0.1 or 3.23. 
It is about having mysql free scheme for rebol, or not. And don't 
think every admin will be willing to set old-password parameter for 
his server, as this can be regarded a security risk ...

There must be quite a few rebol users using mysql.

I don't know any other language, which would not have mysql scheme 
....

I don't understand what do you mean here. I probably know, from its 
help, that it supports md5 and sha1, but dunno how to use such fact 
in regards to mysql scheme. Why doc coded his own functions then? 
Or is it just that older auth schemes did not use typical sha1 hashing?

Now - sorry if I am breaking some licenses, but I will post some 
stuff to my website, and remove it once we are finished:

http://www.rebol.cz/mysql/mysql-protocol.r
http://www.rebol.cz/mysql/password.c

maybe this is better description:

The password is saved (in user.password) by using the PASSWORD() 
function in
  mysql.


  This is .c file because it's used in libmysqlclient, which is entirely 
  in C.
  (we need it to be portable to a variety of systems).
  Example:
    update user set password=PASSWORD("hello") where user="test"
  This saves a hashed number as a string in the password field.

  The new authentication is performed in following manner:

  SERVER:  public_seed=create_random_string()
           send(public_seed)

  CLIENT:  recv(public_seed)
           hash_stage1=sha1("password")
           hash_stage2=sha1(hash_stage1)
           reply=xor(hash_stage1, sha1(public_seed,hash_stage2)

           // this three steps are done in scramble() 

           send(reply)

     
  SERVER:  recv(reply)
           hash_stage1=xor(reply, sha1(public_seed,hash_stage2))
           candidate_hash2=sha1(hash_stage1)
           check(candidate_hash2==hash_stage2)

           // this three steps are done in check_scramble()

does it mean mySQL new password method is even more rebol friendly 
as it was in the past? hmm, if so, should not be difficult to proceed 
from this point?

I just wonder why Doc implemented scrambler in such complicated way 
then? Maybe older mysql did so too (some non standard mechanism). 
Do implemented 'floor, crypt-v9, crypt-v10, scramble, hash-v9, hash-v10 
in his scrambler object...

i think it would be enough, if you figure out how Doc sends data 
to server. Have no experience with mysql unfortunally., always using 
files..

I was not able to find-out, what just does v9 or V10 protocol mean? 
maybe mySQL protocol version ...

Yes. "It supports server protocols v9 and v10, so it should work 
with all versions of mySQL." http://softinnov.org/rebol/mysql-usage.html

how do I automatically distinguish what version is mySQL communicating 
with me when sending me a seed is unknown to me yet ...

I just know that mySQL distinguishes it for itself simply by using 
asterisk as a first char in password ...

Does this help? http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html

i hope so. reminds on mysql 4.1

although - I just try to connect to /mysql table ... but passwd is 
user dependant, and as a root, I can see, it is long already :-)

I found sha1.c funcs in mySQL distro, I will post it in two hours 
to ftp, as I don't have account access here ...

just to let you know, Doc is looking into mysql 5 issues 8)

I have a couple of hours to hack someething for MySQL v5 support.

I'll try to reuse your code for scrambling, it will save me time 
looking in the sources of MySQL server.

http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html

Now - sorry if I am breaking some licenses, but I will post some 
stuff to my website, and remove it once we are finished:

http://www.rebol.cz/mysql/mysql-protocol.r
http://www.rebol.cz/mysql/password.c

MySQL client sources

Did you implement your scrambler according some earlier mysql sources?

so basically checksum/secure checksum/secure "mypass" gives exactly 
the same result as in mysql doc page posted above.....

cool! ppl will surely appreciate it - rebol without free mySQL scheme 
is kind of show stopper for some of them :-)

sha_input: http://www.distlab.dk/mysql-4.1/html/mysys_2sha1_8c-source.html#l00179

Seems the two inputs are like a join. Usefull this way if one hashes 
files withput loading everything in memory.

without your hint to protocol internals, plust the 2x checksum/secure 
being identical to mysql docs, nothing like that would be possible 
on my side ....

it seems that there is a license issue with the new MySQL version 
:

/*

  This file is the net layer API for the MySQL client/server protocol,

  which is a tightly coupled, proprietary protocol owned by MySQL AB.
  Any re-implementations of this protocol must also be under GPL
  unless one has got an license from MySQL AB stating otherwise.
*/

I don't have problem moving my mysql driver to GPL but it may cause 
later issues for users...

Their protocol can be reimplemented using - http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html

It was from mysql-5.0.18\libmysqld\net_serv.cpp

Jaime - with rebol, you even can't to use it, can you? So you use 
mysql-protocol.r, which is let's say GPL. Then you do some app using 
it. You can open-source your app, but you can't open-source one part 
of your app - REBOL itself ... well, I am weak in licensing stuff, 
so I really don't have clear opinion on that issue - just trying 
to use common-sense ...

http://www.rebol.cz/mysql/mysql-protocol-new.r

my 'mysql' db is v5, it's only the 'test' db who was copied from 
v3.

Doc, are you sure your 'docode table is ok? I mean defs/client ....

client [
			long-password		1		; new more secure passwords
			found-rows			2		; Found instead of affected rows
			long-flag			4		; Get all column flags
			connect-with-db		8		; One can specify db on connect
			no-schema			16		; Don't allow db.table.column
			compress			32		; Can use compression protcol
			odbc				64		; Odbc client
			local-files			128		; Can use LOAD DATA LOCAL
			ignore-space		256		; Ignore spaces before '('
			change-user			512		; Support the mysql_change_user()
			interactive			1024	; This is an interactive client
			ssl					2048	; Switch to SSL after handshake
			ignore-sigpipe		4096	; IGNORE sigpipes
			transactions		8196	; Client knows about transactions
		]

While in protocol description, there is:

MySQL uses the following codes:
Capability name	Value	Meaning
LONG_PASSWORD	1	New more secure passwords
FOUND_ROWS	2	Found instead of affected rows
LONG_FLAG	4	Get all column flags
CONNECT_WITH_DB	8	One can specify db on connect
NO_SCHEMA	16	Don't allow database.table.column
COMPRESS	32	Can use compression protocol
ODBC	64	ODBC client
LOCAL_FILES	128	Can use LOAD DATA LOCAL
IGNORE_SPACE	256	Ignore spaces before '('
PROTOCOL_41	512	Support the 4.1 protocol
INTERACTIVE	1024	This is an interactive client
SSL	2048	Switch to SSL after handshake
IGNORE_SIGPIPE	4096	IGNORE sigpipes
TRANSACTIONS	8192	Client knows about transactions
SECURE_CONNECTION	32768	New 4.1 authentication
MULTI_STATEMENTS	65536	Multi-statement support
MULTI_RESULTS	131072	Multi-results

My old MySQL code is inpired by mm_mysql driver (now obsolete). The 
driver source code in C# you're pointing too is inspired by later 
evolutions of the mm_mysql driver.

MySQL server protocol has changed since 4.1.0 (with a big evolution 
starting from 4.1.1) and is not compatible with the older protocol. 
Strangely, server v5 allow clients to connect with the older (pre-4.1) 
protocol providing the good flags...(that's odd). Need more investigation 
to see if the old driver can still be used reliably with v5.

so this is not accurate? http://www.redferni.uklinux.net/mysql/MySQL-Protocol.html

or do you think that checksum/secure could be used even for older 
mysql versions? I wonder if their scheme used sha1 in old days?

latest version: http://www.rebol.cz/mysql/mysql-protocol-new.r

New MySQL driver version 1.0.0 supporting server v4.1.1+ : http://softinnov.org/tmp/mysql-protocol.r

well, it is really handy ... it is SQL, that is importand ... and 
it is completly portable - imagine having small apps, you just need 
cgi-bin site and you can have your rebol app wherever .... no need 
for admin to configure mysql for you, etc., I like it. For small 
to middle stuff it is pretty ok ...

I set up a connection to MySQL from linux using MyODBC drivers back 
in 2002.  I'm sure if you wanted to you could get an ODBC set up 
running that would allow you to use Firebird from Linux.

I know I had REBOL connecting to MySQL via ODBC on Linux...  I was 
playing with WINE heavily at the time though so I might have been 
running a windows version of REBOL on Linux to do it. ;-)

I like SAX model, because IIRC it allows to work on things in a "streamed" 
way, whereas DOM requires you load everything in memory? Sorry if 
I oversimpilifed it :-) IIRC Doc used such aproach in his Postgress 
SQL driver, in opposite to his mySQL one ...