REBOL3 - MySQL ([web-public])

Return to Index Page
Most recent messages (300 max) are listed first.

#UserMessageDate
1196WillUnderstanding and control of MySQL query optimizer http://assets.en.oreilly.com/1/event/21/Understanding%20and%20Control%20of%20MySQL%20Query%20Optimizer_%20Traditional%20and%20Novel%20Tools%20and%20Techniques%20Presentation.pdf28-Sep-09 3:30
1195GrahamAs for waiting for Qtask to decide on their licensing ... well, enuff said.5-Jul-09 23:07
1194Grahamto communicate with sphinx.5-Jul-09 23:06
1193GrahamI don't use mysql .. but Firebird. And I'm waiting for the sphinx integration into firebird before I try doc's mysql driver.5-Jul-09 23:06
1192Willfrom the changelog, " Fixed parsing bug with \\. Now expression like "'\\', 'wo;rd'" will be parsed correctly."5-Jul-09 22:00
1191Willhere is the latest http://softinnov.org/tmp/mysql-protocol-41.r give it a try5-Jul-09 21:57
1190WillIIRC previous version had a problem with escaping correctly {'} in some circumstances, that may be a cause of truncation.5-Jul-09 21:54
1189WillGraham: no, do you? how is your experience with it?5-Jul-09 21:40
1188Willare you using latest 1.3beta versionof the driver? previus version may have a problem and truncate..5-Jul-09 21:39
1187amacleodI'm sending much larger blobs (image files) and it works just text is giving the problem but I'll need to look further. Thanks all.5-Jul-09 16:00
1186amacleodI check it out thanks, Doc.5-Jul-09 15:59
1185DockimbelAmacleod: There's no internal limit at 10000 bytes in mysql driver (single packet default limit is 1MB). Are you using prepared statements? If not, are you sure that your data is correctly encoded? Anyway, try using TRACE mode : trace/net on. Look at the sent packets size to see if there's a truncation in the mysql driver.5-Jul-09 11:13
1184GrahamWill did you not try the mysql driver for sphinx??5-Jul-09 10:28
1183WillMaarten: is there any news about your Sphinx stuff? Reichart "promised" to release it soon, that was some time ago 8)5-Jul-09 9:19
1182WillI have no problem inserting much bigger data to text fields using Dock's driver, thought I can't suggest anything else then wiresharking for debugging5-Jul-09 9:13
1181Grahamtry wireshark to do a tcp trace to make sure all the packets are getting thru5-Jul-09 3:35
1180Grahamcan you use any other formats? Does mysql have text blobs ?5-Jul-09 3:25
1179amacleodI tried to insert a longer string using phpMyAdmin and it inserted fine...no truncation. I guess its mysqlprotocol.r problem.

Anyone else encounter this? Any work arounds?

5-Jul-09 1:54
1178amacleodAny reason why my data in a 'text' column is getting truncated at 9999 characters? I tried 'longtext' but there was no change..

I thought 'text' holds up to 4 gigs from what I read...

I saw something about longtext being limited by the way a client handles packets..could this be a problem with MySQL protocol?

5-Jul-09 1:46
1177amacleodI know..a little off topic..moving to core8-Jun-09 0:52
1176amacleodRebol must get the zone from the computer but why is it different?8-Jun-09 0:34
1175amacleodI tried again and they are now synced...atleast to the minute...8-Jun-09 0:32
1174amacleodI just did an update to my server time and my other computer.. I used time.nist.gove for the server and time.windows.com for the other computer and they were two minutes apart8-Jun-09 0:31
1173amacleodok, but what is rebol reading to get the time?8-Jun-09 0:29
1172Grahamdaylight saving?8-Jun-09 0:28
1171amacleodQuick question about computer time,,, Eastern Time is GMT-5 which my computer clock is set to... why does rebol give me time with -4:00? 7-Jun-2009/20:24:01-4:008-Jun-09 0:27
1170amacleodinsure7-Jun-09 20:58
1169amacleodFrom what I have been able to read about timestamps and mysql I might as well insert the client's time... But then I would need to issure clietn's clocks are correct.... What is SOP for this situation?7-Jun-09 20:58
1168amacleodright...duh!7-Jun-09 19:07
1167Jankoload "[... ]"7-Jun-09 18:03
1166Jankohm.. I think you use load to get rebol data back from string7-Jun-09 18:03
1165amacleodAlso, i have some datestamp issues. rebol attaches the zone code to now when getting the date/time but when using mysql timestamp I do not get a time zone attached and its screwing me up. Is there a way to add time zone to datestamp in mysql?7-Jun-09 17:42
1164amacleodI'm using 'mold/only' saving to mysql and 'to-block' to re-block it... is there a cleaner way like an 'unmold'?7-Jun-09 17:39
1163Jankoyes you need to deserialize it after retrieval.7-Jun-09 17:24
1162amacleodyeah, my app crashes I need to convert back to block...7-Jun-09 17:10
1161amacleodI molded it and it inserted into mysql but now I need to see if after selecting it that it behaves properly in my app.... thanks for the help7-Jun-09 17:08
1160JankoSQL command is a string so IMHO you have to mold it and enquote it as normal text and then there should be no problems .. what kind of errors does it throw?7-Jun-09 17:04
1159amacleodtext has no issues in sqlite7-Jun-09 17:03
1158amacleodI've tried text and varchar7-Jun-09 17:03
1157Jankomaybe sqlite molds it automagically ?7-Jun-09 17:03
1156Jankodo you "mold" it? what kind of field do you try to store it in?7-Jun-09 17:03
1155amacleodfor example: [["edfefwf" 3.4.5]["gdegerg" 4.5.6]]7-Jun-09 17:02
1154Jankoups.. wrong channel7-Jun-09 17:00
1153Jankono, where can I find it?7-Jun-09 17:00
1152Jankoyou mean molded nested block - text?7-Jun-09 16:59
1151amacleodIs there any reason mysql will not accept a nested block in a text field? I have no problem with sqlite storing the same data...7-Jun-09 16:40
1150amacleodNice, Thanks.29-May-09 16:36
1149SunandaOne table will be much easier to manage. Two thoughts:

If you use Views to create "virtual tables" for each user, then you can (fairly) easily change the underlying structure without disrupting the application too much should you need to for performance reasons.

If your data does grow large, then partitioning my help performance -- ie having one table split across multiple drives according to data values (such as user name): http://dev.mysql.com/doc/refman/5.1/en/partitioning-overview.html

29-May-09 15:36
1148amacleodNo, not really... One table would be much easier and I do not think the db would get so large that I would see major performance problems. (I always underestimate the speed of these db's.)

Thanks for the advice, Sunanda.

29-May-09 15:22
1147SunandaThen it'd be normal to have one large table. But (and I iguess this is your question): are there worthwhile (perhaps essential) performance improvements by denormalising? Perhaps partitioning different users on different drives or servers. Sadly, normalisation theory is silent on that subject.

Can you experiment with some generated fullsize data and see?

29-May-09 15:18
1146amacleodI guess fixed fields29-May-09 15:15
1145amacleodEach table will be the exact same stucture29-May-09 15:14
1144SunandaIs the users' data freeformat, or fixed fields? ie do they each have the same data fields (but with different values), or can they define whatever data they want to store?29-May-09 15:14
1143amacleodNeed some advice on db structure. I'm not sure which would be better for this senario:

I want to store user generated data which could consist of thousands of rows. But I also will want to be able to search across each users data.

I could create a seperate table for each user and join tables when searching through all user data or I could make one large database with a user field to seperate out that user's data when needed.

There could be many hundred users.

What is SOP in such a case?

29-May-09 15:11
1142DockimbelThanks :-)25-May-09 12:33
1141TomBonyes, will use the prepared statement. it is also more elegant. with escaping I need to handle all fields to be save. btw. many thx to provide such a cool, free and very important driver!25-May-09 10:20
1140DockimbelThere's also an alternative approach, the SQL-ESCAPE encoding function of MySQL driver is exported in the global context, so you can use it when forming SQL queries directly :

send-sql db join "UPDATE table SET field=" sql-escape string

The prepared statement approach is recommended, because the driver will care about that for you automatically (so less risks that you forget to encode one value, opening a hole for SQL injection attacks).

25-May-09 10:11
1139TomBonahh...thx doc. I formed the insert classical without question marks.25-May-09 10:02
1138DockimbelHi TomBon, when using prepared statements, special characters are encoded in passed values.

send-sql db ["UPDATE table SET field=?" string] ; string value will be encoded here

25-May-09 9:56
1137TomBonhi doc, is there any trick or encoding to prevent inserting errors due to strings containing special characters like ' or / etc?25-May-09 9:45
1136RobertS.22-May-09 15:55
1135Grahamwow ... two for the price of one!14-May-09 21:04
1134DockimbelI think so.14-May-09 20:37
1133Grahamis doc's mysql protocol the binary one they talk about?14-May-09 20:12
1132MaartenInteresting indeed. I'll pick this up with Reichart; we are all in favour, it merely is a matter of time (pressure). The libs are nice, but I am sure others can help improving them.14-May-09 14:00
1131GrahamHere's an interesting conversation ! http://www.sphx.org/forum/view.html?id=121014-May-09 11:55
1130GrahamThat was a year ago ...14-May-09 11:53
1129WillMaarten has one that's waiting Reichart approval to get released 8)14-May-09 11:18
1128JankoI will put it on rebol.org14-May-09 9:51
1127GrahamThis is interesting http://sphinxsearch.com/news/37.html .. you can now connect to the sphinx searchd daemon using the mysql binary protocol ... so doc's mysql driver can also now connect to sphinx??14-May-09 9:13
1126GrahamCan you put it on rebol.org in the library.14-May-09 9:04
1125Jankoit's not much though - but I can share without problem if anyone needs14-May-09 8:56
1124JankoI have made a primitive client libs to the SOLR search engine, if that helps anyone14-May-09 8:55
1123GrahamThere is this API document being worked on http://sphinxclient.codeplex.com/SourceControl/ListDownloadableCommits.aspx14-May-09 8:42
1122GrahamHas anyone released a sphinx Rebol client api implementation?14-May-09 7:28
1121amacleodThanks anyway...10-May-09 14:11
1120DockimbelI'm just understanding now what you're trying to do. READ-NET is not what you want to use, it's for downloading files! You have to write your own progress bar function. About the total size of result set, AFAIK, MySQL server doesn't send that information, it just marks the last record of a result set.9-May-09 8:33
1119amacleodDoc, I played around with read-net but no luck. Would not read-net need to know total size of query result to work? Does the server send this info first?

also, read-net wants a URL. Where am I pointing it to? Is this the same as the db port i'm using?

9-May-09 6:55
1118DockimbelThanks for the additional info. The main change in 1.2.0 is the new READ syntax, and it's documented here : http://softinnov.org/rebol/mysql-usage.html#sect2.2.29-Apr-09 22:02
1117amacleodI'm hosting a large number of text docs broken up by sections in a mysql db. Once all the material is on line the changes (and thus downloads) wil be smal for the most part. But in the mean time large amounts of material are still to be uploaded to the db and when a client finds this new material it will download it and any changes to previous material. The material contains a lot of image data too so it could add up. Right now I have about 40 megs of data and I have about 10X more to upload.

If its too tuff for me I will just display some animated gif but I would prefer something more tangible..

I'll look into your suggestion, Thanks Doc...

BTW I tried the latest mysql-protocol and it broke my app. I have not had a chance to look into it but I think you changed some syntax for selects?? I'm using Version: 1.1.2 at the moment.

29-Apr-09 20:33
1116DockimbelNo, there's no such callback. But you can easily add one in my driver by inserting a call to "read-net" in the FOREVER loop inside READ-ROWS function. Can you describe briefly the situation where you need to get large query results and display a progress bar (just curious)?29-Apr-09 20:07
1115amacleodfor large query results is there a way to show a progress bar as an indicator of the download progress?

Is there some type of callback method like "read-net"?

29-Apr-09 19:11
1114DockimbelGood to know! Thanks.21-Apr-09 21:53
1113WillI use your driver with 5.1.x no problem 8-)21-Apr-09 21:15
1112MaartenYeah, but you're driver has the source. Also, in the ancient times before COmmand 2.x and your mysql:// I did a library interface, it's still on rebol.org18-Apr-09 12:59
1111DockimbelBtw, I've never tested my driver with a 5.1.x server.17-Apr-09 20:22
1110DockimbelDon't know, I think it doesn't support anything above 3.x but I can't find the full /Command feature list on rebol.com to confirm.17-Apr-09 20:20
1109BrianHDoes the /Command client support MySQL 5.1?17-Apr-09 20:08
1108DockimbelYou should try with the official command line mysql client. If you notice a different behaviour than the mysql:// driver, please report it here.17-Apr-09 19:56
1107BrianHThe DDL in question is create table statements where some obscure semantic rule is violated. By obscure, I mean it took me a day to track down the error in the MySQL manuals.17-Apr-09 19:54
1106BrianHI've run into situations where MySQL will fail to do DDL without throwing an error, at least not one the mysql:// driver notices. Haven't tested with the /Command driver or qcmysql:// though.17-Apr-09 19:49
1105Dockimbelinsert db [create-db "test2"] works flawlessly here....You've probably connected to the server with a user that didn't had enough rights for creating databases, but it's hard to figure out from a "does not seem to work" issue description.

I start to better understand Carl's willings for filtered communication channels that improve the signal to noise ratio in order to save some valuable working time...

17-Apr-09 19:45
1104amacleodI got it!17-Apr-09 18:06
1103amacleodI still can't create db but never mind as I'm going with tables...Thanks again17-Apr-09 17:55
1102OldesI think there is no limit on number of tables. A database in MySQL is implemented as a directory containing files that correspond to tables in the database.17-Apr-09 17:52
1101amacleodOldes17-Apr-09 17:52
1100amacleodThanks olds...I think that will making everything simpler for me too.17-Apr-09 17:52
1099OldesI don't know details, but I think one database should be enough17-Apr-09 17:51
1098Oldesforget the first one:)17-Apr-09 17:50
1097Oldesinsert db "CREATE DATABASE test2"17-Apr-09 17:50
1096Oldesinsert db "SELECT * FROM user"17-Apr-09 17:49
1095amacleodAnyone 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?17-Apr-09 17:46
1094amacleodIs there a limit to the number of tables in a database?17-Apr-09 17:41
1093amacleodCan 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

17-Apr-09 17:22
1092DideCOk, it works now. My bad, I put the last protocol in a folder but I where still loading the old one from another folder !1-Apr-09 7:52
1091DockimbelMySQL config file (my.cnf) can be in one of these places : - /etc/ - $HOME/ - MySQL data folder30-Mar-09 14:17
1090Dockimbelv1.2.1 is supposed to work with both old and new passwords.30-Mar-09 14:14
1089DideCDoes protocol v1.2.1 works with new password method ? If so, maybe I just need to update the user password int he db ?*30-Mar-09 13:51
1088DideC(I just look at the variable in phpmyadmin.30-Mar-09 13:48
1087DideCClient version is the last one.30-Mar-09 13:48
1086DideCOK, old-password is off in the server variable. Do you know where is the config file where this variables are sets (Ubuntu) ?30-Mar-09 13:47
1085DockimbelJust installed 5.0.77 community server on Windows, no problem to log in using default password mode with mysql protocol v1.2.1.30-Mar-09 12:46
1084DockimbelBtw, 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).30-Mar-09 12:24
1083Dockimbel"Start mysqld with the --old-passwords option. " seems the simpliest way to workaround that.30-Mar-09 12:22
1082DockimbelI'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.html30-Mar-09 12:21
1081DideCIt's MySQL - 5.0.51a Do you know how to do that ?30-Mar-09 11:46
1080Pekrthere 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 ...30-Mar-09 11:31
1079DideCI 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 ?

30-Mar-09 11:16
1078Davide> 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

2-Mar-09 18:49
1077BrianHAlso, change the count(*) to count(1), so MySQL doesn't push the full contents of the join through the count processor.2-Mar-09 17:16
1076MaartenDavide, 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. HTH2-Mar-09 14:28
1075Davide> 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.

2-Mar-09 13:12
1074Oldesis 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.html2-Mar-09 10:57
1073DavideIs 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 ?

2-Mar-09 0:48
1072amacleodI 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!

14-Jan-09 2:32
1071Willehat -> what14-Jan-09 0:47
1070Willbtw, 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, etc14-Jan-09 0:46
1069Willcan you diff..14-Jan-09 0:41
1068Willhttp://en.wikipedia.org/wiki/Diff14-Jan-09 0:41
1067amacleoddiff?14-Jan-09 0:32
1066Willca 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?13-Jan-09 23:06
1065amacleodI'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?13-Jan-09 6:05
1064?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.23-Dec-08 18:43
1063ManuM. .23-Dec-08 15:18
1062DockimbelThanks for the report, I'll fix that in the next beta release. There's also other fixes from Will pending.18-Dec-08 9:43
1061Gabriele(mysql-protocol-41.r)18-Dec-08 9:39
1060Gabrieleread-packet: func [port [port!] /local packet-len pl status tmp][18-Dec-08 9:39
1059Gabrieleshould be:18-Dec-08 9:39
1058Gabrieleread-packet: func [port [port!] /local packet-len pl status][18-Dec-08 9:39
1057GabrieleDoc, the read-packet function in mysql-protocol.r is missing one word in the /local list. Line 538:18-Dec-08 9:39
1056GrahamI think that someone had setup a password ... so I purged mysql, deleted my.cnf and reinstalled. That worked.13-Dec-08 21:54
1055DockimbelWhat 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.13-Dec-08 21:19
1054GrahamI thought root was automatically created with no password??12-Dec-08 22:45
1053Grahamget "Access denied for user 'root'@'localhost' (using password: No )12-Dec-08 22:45
1052Grahamjust installed mysql on ubuntu 8.1 but can't login ...12-Dec-08 22:44
1051Willuse /debug it will print query to the console without sending it to the server so you can play and understand how it works3-Dec-08 21:30
1050Willand I will be glad to answer any questions and add feature requests 8)3-Dec-08 21:29
1049Willsorry 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.3-Dec-08 21:29
1048AdrianSthanks - is there a page somewhere describing it in more detail?3-Dec-08 21:25
1047Will.db/get/debug 'node 'id 1 ; "SELECT `id` FROM `node` WHERE id=1.0 LIMIT 0,1"3-Dec-08 21:21
1046Will;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

3-Dec-08 21:19
1045Willit is just a utilities wrapper that works on top of the mysql-protocol.r, it makes queriing mysql more rebolish3-Dec-08 21:03
1044AdrianSWill, what does the wrapper do compared to the MySQL driver?3-Dec-08 20:50
1043Willhere is updated wrapper, http://reboot.ch/rebol/mysql-wrapper.txt3-Dec-08 20:37
1042Gabrielehttp://softinnov.org/tmp/mysql-protocol-41.r3-Dec-08 18:51
1041WillThanks Dock! 8)3-Dec-08 16:28
1040DockimbelThe 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.3-Dec-08 15:47
1039DockimbelBugfix 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.

3-Dec-08 15:34
1038DockimbelI 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) ?28-Nov-08 20:28
1037DockimbelImproved 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.

28-Nov-08 20:21
1036DockimbelAfter 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.28-Nov-08 17:54
1035DockimbelI used to reverse-engineer, but since 5.0(?) MySQL AB documents the protocol with sometimes (intentional?) blocking errors.27-Nov-08 18:01
1034Oldesfound it http://forge.mysql.com/wiki/MySQL_Internals_ClientServer_Protocol27-Nov-08 16:38
1033OldesI'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?27-Nov-08 16:22
1032OldesBut there must be a bug anyway .... the first one shows that.. next sql query after the procedure call returns none.27-Nov-08 15:28
1031OldesI thought I'm, but the version was older.. now it's working fine:)27-Nov-08 15:13
1030DockimbelCalling stored procedures is not supported in driver versions < 1.3.27-Nov-08 12:54
1029DockimbelOldes: are you sure you're using the 1.3 beta mysql:// driver ? You can find it here : http://softinnov.org/tmp/mysql-protocol-41.r27-Nov-08 12:53
1028Gabrielethe cheyenne error is probably because you need the new version of mysql:// that Doc posted a few days ago27-Nov-08 5:03
1027OldesIt 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

27-Nov-08 1:49
1026DockimbelEnjoy ! :-)15-Nov-08 20:21
1025DockimbelHere'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!

15-Nov-08 20:20
1024DockimbelIt now passes all my non-regression tests, but I've done several deep changes, so I'll consider it beta until the end of the year before declaring it the new official version.15-Nov-08 20:16
1023DockimbelNew 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: #";")

15-Nov-08 20:14
1022GabrieleMaarten, you're working too much :) Nenad: thanks! If I have some time, I'll have a look too, though I will just be making guesses. :)13-Nov-08 9:52
1021DockimbelThanks for the link, that confirms that's possible to detect the last result set. I'm keep getting "more results"' status even for the last set, but now I think that it's caused by a bug in my code. I'll look at it this week end (too busy until there).12-Nov-08 18:56
1020MaartenI'm here. I think there is a C API call for the number of result sets. Primitive, but this is how to do it: http://dev.mysql.com/doc/refman/5.0/en/c-api-multiple-queries.html12-Nov-08 16:51
1019Gabriele(he can read the mail address listed here on altme from his ipod touch)12-Nov-08 14:08
1018GabrieleBTW, Maarten told me that he knows for sure that you can get the number of result sets in advance, if he doesn't show up here (he can't be much in front of a computer these days), maybe you could email him to get more info.12-Nov-08 14:07
1017Gabrielethis is going to be very useful :-)12-Nov-08 14:06
1016Gabrielethe above new version seems to work fine for simple tests, apart from the issues Nenad has already listed.12-Nov-08 14:05
1015WillThank you! 8-))12-Nov-08 0:52
1014DockimbelThank Gabriele for keep pushing me. ;-)11-Nov-08 15:01
1013Pekrgood news!11-Nov-08 14:56
1012DockimbelIt should be considered as a early beta, so test it well before using.11-Nov-08 14:51
1011DockimbelKeep in mind that if you're using SEND-SQL, it already does a implicit COPY.11-Nov-08 14:50
1010DockimbelI'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.r11-Nov-08 14:47
1009Alan.14-Sep-08 7:04
1008james_nakThanks Will.5-Aug-08 20:31
1007WillJames: 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.txt then: ;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

5-Aug-08 6:18
1006DockimbelVersion 1.2.1 re-released. There was a small error in the 'name-fields function preventing it from working. Thanks to Will for reporting it.14-Jul-08 12:29
1005DockimbelFor 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.14-Jul-08 12:19
1004WillThank you Dock!14-Jul-08 2:40
1003GrahamThanks for fixing the date zero error.13-Jul-08 19:46
1002DockimbelMySQL driver v1.2.1 released. See more at http://softinnov.org13-Jul-08 19:04
1001Grahamok, worked it out

insert db "select `middle name` from patientrecord"

12-Jul-08 22:58
1000Grahamdocs 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"

12-Jul-08 22:55
999GrahamWhy would anyone put a space in a field name??12-Jul-08 22:53
998Grahamummm.. how do you do a select on a field where the field name has a space in it?12-Jul-08 22:50
997Grahamok, this works round it

insert db "select cast( birthdate as char) from patientrecord"

12-Jul-08 22:42
996Grahamlooks like some of the patient records have 0000-00-00 as the date of birth in them.12-Jul-08 22:39
995GrahamGetting this ...

>> insert db "select * from patientrecord" == none >> result: copy db ** Script Error: Invalid argument: 0000-00-00 ** Where: convert-types ** Near: change at row 3 to >>

12-Jul-08 22:36
994GrahamNever used mysql before .. and just downloaded Nenad's driver.12-Jul-08 22:36
993ReichartQtask might do this in the future...11-Jul-08 17:27
992GabrieleWhy wouldn't Qtask want to talk to Postgres too if they had chosen it?8-Jul-08 8:58
991james_nakThanks.7-Jul-08 22:58
990WillJames, thanks for your interest, I' ll upload it in a couple days 8)7-Jul-08 22:28
989GrahamWith two open source databases to choose from of approximately similar functionality and stability, it is hard to understand why one would choose the one that has the restrictive license ... I can only imagine it is a mind share thing.7-Jul-08 21:19
988MaartenI agree, your viewpoint wasn't economics....7-Jul-08 20:58
987Grahambecause of a limited viewpoint7-Jul-08 20:49
986MaartenWhy is the world flat?7-Jul-08 17:23
985GrahamWhy didn't qtask use postgresql instead??7-Jul-08 9:36
984Gabrielewell, 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?7-Jul-08 9:35
983GrahamHe's often mentioned that they are considering open sourcing qtask in which again the license was not needed!7-Jul-08 9:30
982Gabrielei don't think you can buy it right now, and the focus has shifted to the SaaS model, but otherwise there are no major tech obstacles iirc. ask reichart for more details, i'm not really up to date on the marketing/sales stuff :)7-Jul-08 9:29
981GrahamHas that happened yet?7-Jul-08 9:26
980GabrieleQtask? no, it does, because you can also put qtask on your own server.7-Jul-08 9:21
979GrahamSo, looks like you didn't need a license7-Jul-08 6:54
978BrianHThey took that clause out of the regular GPL3 during the review process, after many protested it.7-Jul-08 3:33
977BrianHNo, that's the Affero GPL.7-Jul-08 3:31
976Gabrielegraham, 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.6-Jul-08 10:03
975GrahamGabriele, I thought you only needed a license if you are releasing your software ....5-Jul-08 22:35
974james_nakWill, that would be sweet.3-Jul-08 23:43
973WillI 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' ..3-Jul-08 10:09
972Gabrieleabout 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).3-Jul-08 9:39
971PekrIsn't REBOL fast enough for most operations in regards to MySQL? Wouldn't it be sufficient to write such escaping fun in REBOL?3-Jul-08 6:44
970PekrGuys, 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.

3-Jul-08 6:43
969WillPlease 8P2-Jul-08 14:18
968MaartenWill: I'll have to ask Reichart.2-Jul-08 9:11
967Willhere is a short and concise presentation about sphinx http://www.scribd.com/doc/2670976/Sphinx-High-Performance-Full-Text-Search-for-MySQL-Presentation2-Jul-08 8:23
966Willbad question, but it would be cool to program stored procedures in rebol2-Jul-08 2:22
965Willwould there be any advantage having rebol as an external language plugin compared to Dock sheme? http://forge.mysql.com/wiki/ProjectPage_External_Language_Stored_Procedures1-Jul-08 13:46
964WillMaarten: that is great news! Do you plan on releasing it for the rest of us?1-Jul-08 13:45
963Maarten(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.28-Jun-08 7:31
962MaartenWill, I have, and for Qtask I have even implemented the search protocol in REBOL. It's fast.28-Jun-08 7:30
961james_nakYou rock Doc. Thanks. That was an answer several years in the making!27-Jun-08 23:43
960DockimbelYes if the server is just restarted, no problem, the driver will reconnect.27-Jun-08 19:36
959Willok but your driver should reconnect automagically 8-)27-Jun-08 19:35
958Dockimbelyou can also use a simple : port: attempt [ open mysql://... ]27-Jun-08 19:34
957Dockimbelmysql-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 ] ]27-Jun-08 19:31
956DockimbelHere's a handy function to test if a MySQL server is up :27-Jun-08 19:31
955WillDoes anyone experimented with the sphinx search engine? http://www.sphinxsearch.com/13-Jun-08 7:12
954BrianHRead the comments: The CEO of MySQL sets the story straight.18-Apr-08 3:03
953Willhttp://developers.slashdot.org/article.pl?sid=08/04/16/2337224 long life postgresql 8)18-Apr-08 3:02
952Willsome nice performance tips http://www.scribd.com/doc/393792/MySQL-performance-coding23-Mar-08 1:35
951james_nakDoc, first of all, thanks for the driver. It is amazing. Secondly, I've tried a few ways to be able to detect for an open port without the program erroring out. I've just settled on a "error? attempt [db/locals]" Do you have any recommendations?3-Oct-07 17:17
950DockimbelMySQL 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.3-Oct-07 10:30
949?Congrats Doc.3-Sep-07 2:32
948WillThank you Dock!2-Sep-07 23:53
947DockimbelThis new 'read implementation avoids the SQL encoding issues encountered in previous versions. The side effect it that it will break compatibility with previous sources using 'read.2-Sep-07 17:03
946DockimbelNew 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.

2-Sep-07 17:01
945Oldeshm... already found how to get the correct port number so now it's not important30-Aug-07 19:06
944OldesHow to connect to DB when I need to use sock ( for example: localhost:/home/hmm/mysql/mysql.sock )30-Aug-07 18:37
943DockimbelMySQL driver bugtracker : http://softinnov.org:8000/curecode/project.rsp?id=512-Aug-07 18:16
942DockimbelThis new beta fixes mostly auto-reconnection bugs or issues. Please test it and report any bugs in CureCode bugtracker and issues here.12-Aug-07 18:16
941DockimbelNew 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'"

12-Aug-07 18:15
940DockimbelThanks for the report, I'm adding your fix in the driver source.14-Jul-07 12:54
939Oldesthere should be: #"'" any ["\'" | "''" | not-squote] #"'" |{"} any [{\"} | {""} | not-dquote] {"}14-Jul-07 10:43
938Oldesthe problem is with the empty column in the query14-Jul-07 10:38
937Oldesand now I have a prove:

not-squote: complement charset "'" not-dquote: complement charset {"} ws: charset " ^-^M^/" data: {INSERT INTO hmm_kalendar_akce VALUES (NULL,9,'posledni-skotsky-kral',NULL,'(Last King of Scotland, The)','Poslední skotský král',NULL,NULL,'','http://www.kinoartbrno.cz/?stranka=film&amp;film=posledni-skotsky-kral',0,NULL)}

;the parse part from insert-all-queries function: parse/all s: data [ any [ #"'" some ["\'" | "''" | not-squote] #"'" |{"} some [{\"} | {""} | not-dquote] {"} | #"`" thru #"`" | e: #";" ( probe "next query?" probe e ) any [ws] s: | skip ] ]

14-Jul-07 10:37
936OldesNow I'm sure there is something wrong in insert-all-queries function as it divides my query where is #";" (but inside data)14-Jul-07 10:32
935OldesVersion: 1.1.214-Jul-07 10:27
934Rebolekand are you sure you're using latest version? ;o)14-Jul-07 10:23
933OldesI have still some problems with semicolons:-(14-Jul-07 10:21
932Oldesit's safe13-Jul-07 17:58
931Dockimbel:-) so, does the driver need to be fixed for "&#039" sequences or is it safe let them passed untouched to the server ?13-Jul-07 14:20
930Oldesno... my mistake.. I'm just still using old version... the new one is fine:]13-Jul-07 13:31
929Oldeshmm.. so it's not so bad.. the problem is just with semicolon: insert db {insert into esctest values ("a;")} ;=== User Error: ERROR 106413-Jul-07 13:20
928Oldes(now = no, I'm = I've....etc.:-)13-Jul-07 13:09
927Oldesnow... it's an issue with the latest version as well!13-Jul-07 13:09
926Oldeshm... now I see there is some discussion above about injection issues.. maybe I'm older version of mysql and this issue is already fixed13-Jul-07 13:02
925Oldesinsert db "create table esctest (text TINYTEXT)" insert db "insert into esctest values ('a')" ;correct insert db "insert into esctest values ('a&#039;')" ;=== User Error: ERROR 106413-Jul-07 12:58
924OldesI 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 "&#039;" 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.13-Jul-07 12:53
923DockimbelMySQL driver bugtracker : http://softinnov.org:8000/curecode/project.rsp?id=55-Jul-07 13:33
922amacleodThat did not work either but I seem to have got it. I had previously copyed the database to a word like: data_info: copy db. But that gave me an error saying data_info is a set word. I changed it to data_info: send-sql/flat db "select * from jobs" THis seems to work. Thanks20-Jun-07 21:19
921Dockimbeldoes this line return a none! value too : send-sql db "SELECT * FROM jobs" ?20-Jun-07 9:09
920amacleodTrying to use the new [/flat] option for Doc's diver. If I use: "Insert db "SELECT * FROM jobs"I get the expected blocks withing block. But if I use the command: "SEND-SQL/FLAT db "SELECT * FROM jobs" I get "NONE"20-Jun-07 8:31
919DockimbelThanks, I didn't expected an almost x2 increase in speed! Cool ;-)16-Jun-07 18:52
918WillVERY NICE JOB Paul!16-Jun-07 18:38
917Will..1:37.92 minutes with latest version!16-Jun-07 18:38
916WillGREAT NEW, a basis benchmark with about 50'000 queries runs in 2:52.26 minutes and..16-Jun-07 18:37
915DockimbelMySQL 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.16-Jun-07 17:47
914Willneed 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!8-Jun-07 21:00
913DockimbelPublic 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

26-May-07 17:25
912WillThank you Dock!22-May-07 22:52
911DockimbelPlease let me know if there are still issues with multi-statements parsing.22-May-07 22:47
910DockimbelBeta release v1.1.1 with a fix for the semicolon issue in SQL strings : http://softinnov.org/tmp/mysql-protocol.r22-May-07 22:44
909Terrynevermind22-May-07 7:12
908TerryAm I missing something wihen querying multiple columns... seems I get the results as a single string.. ie: send-sql "select a, b from table";

returns something like "aresultbresult" ?

22-May-07 6:27
907DockimbelHi Joe, I should have time tomorrow to make that fix.20-May-07 20:06
906JoeHi Doc, When will you release the multi-statement fix ?19-May-07 14:33
905WillThank you Dock!13-May-07 14:53
904DockimbelInteresting case, you're right, it may confuse the multistatement processing, I'll make a fix for that asap.13-May-07 12:03
903Willit worked before the new multiple statements feature13-May-07 8:35
902Willthis doesn't: send-sql db "INSERT INTO `engine` (`id`,`style`) VALUES ('12','ciao;oo')" will send: {INSERT INTO `engine` (`id`,`style`) VALUES ('12','ciao}13-May-07 8:33
901Willthis works: send-sql db ["INSERT INTO `engine` (`id`,`style`) VALUES ('12',?)" "ciao;oo"] will send {INSERT INTO `engine` (`id`,`style`) VALUES ('12','ciao;oo')}13-May-07 8:33
900Willnot sure but with latest mysql-driver13-May-07 8:32
899Willcolumn names8-May-07 0:07
898Willquoted columns.. 8)8-May-07 0:06
897Will{SELECT `id`,`name`,`data` FROM `node` WHERE id='1' LIMIT 0,1}8-May-07 0:06

Return to Index Page