AltME groups: search

It's looking like I need to ditch odbc and move to using doc's mysql 
instead.

I'm planning doing a database - in year it will be about 3000 records 
(30 fields each)

What to use - some kind of  rebol blocks (as here http://www.rebol.net/cookbook/recipes/0012.html
) or should I doing it with "mysql-protocol" by DocKimbel?

REBOL.org holds nearly 42,000 emails in some kind of REBOL block 
-- plus various indexes. About 75meg (uncompressed) of data.
So it's doable.

Depends how much fun you want to have. Databases like MySQL are boring 
but straight-forward. Also *may* limit the platform you can run the 
application on.

Dock, if you publish a new version of mysql-protocol.r, it's time 
to make the correction of the bug in the init function, the longlong 
conversion and in the data reading initialization.

New version 1.0.1 including these fixes available (http://softinnov.org/tmp/mysql-protocol.r).

Exists a "standalone" mysql version? Or a standalone database that 
is mysql compatible?

there is an embeddable version of mysql as a dll

There is this apache + x which Terry likes. IIRC mysql is included 
too?

so you need to contact mysql ab for use in non-gpl apps

Robert - you might be better looking somewhere else. Mysql embedded 
does not have tcp/ip api, just some library aproach ...

Doc - you once told me that your postgress driver is some xy percent 
faster, because of continuous parsing or so, maybe you could rewrite 
mysql to use similar principles?

but mysql protocol is not your priority, is it? It simply works now 
:-)

The new 4.1.1+ protocol fixes that kind of issue by sending the string 
size first (like in Pascal language). The current driver doesn't 
implement the new protocol. This will be the main feature of the 
v2 of the MySQL driver.

With v3.x.y servers, there's still sometimes connections errors 1045 
that shouldn't happen. I guess that's related to some encryption 
implementation difference between my client and v3 servers. Workaround 
for this, is catching error! values when connecting and retrying 
the connection if the user/pass is fixed and should be able to connect 
to the server (proper right set in 'mysql' tables).

hi guys, does the latest driver work with MySQL v5.0, or are there 
any known issues?

Apparently MySQL 5 has a datatype that isn't defined in the protocol, 
adding this line seems to work just fine:

defs/types 246 decimal

with 1.0.3 and MySQL 5.x.x

Remember that COPY actually calls the COPY function in the scheme 
handler. (probably, I haven't looked at MySQL scheme).
Therefore maybe what you want is actually:

	result: copy copy port


The theory being that the first COPY provides you with the result 
string from inside the scheme handler (but the scheme still hangs 
on to it), and the second COPY works as usual in rebol (actually 
makes a copy).

which mysql version? I've only seen it on 4.1 and up

I'm getting the Error 1043 Bad Handshake problem accessing My-SQL 
5.0.18 on Windows/XP using mysql-protocol.r via  localhost: that 
Alain Goyé reported on the mailing list:

>> db: open mysql://root:?@localhost/fundamentals
Password: *********
connecting to: localhost
>> insert db "SELECT * FROM books"
** User Error: ERROR 1043 : Bad handshake
** Near: insert db "SELECT * FROM books"

It works fine from another machine over the LAN.

I've just realised that I' have old_passwords set on and am using 
the latest version of Mysql-protocol.r :-(

I solved the problem by installing the 0.99 version of mysql-protocol.r. 
It was quicker than trying to get XAMPP to accept a MySQL revised 
configuration file.

addad "s" to below line in mysql-protochol.r otherwise "s" get set 
in global space

b0: b1: b2: b3: int: int24: long: string: field: len: byte: s: none

Doc, will there be a new version of the mysql driver somewhere punlicly 
available? I get a can't connect on softinnov.org?

This one should work : http://softinnov.net/tmp/mysql-protocol.r

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

I used mysql-admin to create the user. I don't know which method 
it uses

Thanks, I'll try with 5.0.21 + mysql-admin.

looks like mysql-admin doesn't use PASSWORD() to create passwords

dumb question.. is this a valid query?

select i1.id,i1.ip,i1.session from isa i1,isa i2 where i1.ip=i2.ip 
and i1.session<>i2.session order by i1.ip
about 70'000 records in isa
running this query seams to crash/block the mysql server..

mysql-admin is a GUI client.

Ok, so you mean MySQL Administrator (and not the mysqladmin.exe command 
line tool).

it's called "mysql-admin" under linux. looks like your name is correct.

Tried creating a user using that tool under WinXP and OSX, then I 
tried connecting with mysql-protocol.r => no problem...

I think the client is confused about that. The error log for it says 
that old passwords are not supported for 5.0. I initially tried to 
turn old passwords on, because I didn't think mysql-protocol.r supported 
newer ones.

that'll take a short while, the PC that the mysql server is on has 
been taken down. :-)

or looking at mysql logs, if there is not something suspicious ...

Good news : I've finally reached a very stable version of the mysql 
driver with v5+ servers ! The stability garanted only if using a 
database created with a v5+ server, copying old 3.x files in v5 server 
works but results in a big mess and unstable behaviour. Exporting/Importing 
v3.x data in v5 server using SQL flat files works very well (except 
for user with passwords, better recreate them in v5 directly).

copying old 3.x files in v5 server works but results in a big mess 
and unstable behaviour
 <--- is that because of MySQL or mysql-protocol.r?

MySQL, even MySQL Admin tool get lost when acting like that.

I've found numerous bugs in mysql-admin. I think this tool is not 
particularly trustworthy

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

there are times when the connection to the database won't wake up 
after hours of sleep and the application locks. this also happens 
with mysql-admin, but I wonder if it really is server or a client 
side problem.

I can see that mysql console has no problem in reconnecting where 
rebol and mysql-admin fail

1) Load the REBOL mysql console : do %mysql.r
2) Connect to the server
3) Command: show tables
4) result ok
5) Stop/Start the server
6) Command: show tables
7) result ok (the driver has reconnected transparently)

do %mysql-protocol.r
do %mysql-client.r
mysql

MySQL being a good example.

I would like you to suggest me some Linux distribution:

Current situation:


I run old Fedora Core 1 linux, so it lacks on security updates. The 
server is used for few domains, it runs apache, old mySQL 3.5.x version, 
glftpd, sendmail (I am used to that). Server has 2 hads. Content 
of server is packed each week via script and copied to other disk.

Objectives:


- need some easy distro, graphical mode installation, which even 
monkey can configure, forget somo guru stuff, target hardening, etc.
- need mysql 5.x family, Apache 2. family

- adding new users/developers by some tool, e.g. webmin - ftp, apache 
domain, webmail (squirrel)

- needs to run rebol in cgi mode, eventual sqlite library compatibility 
welcomed

- kind of easy recovery - install from CD in graphical mode, copy 
configs, reboot, or even better - instasll some kind of loader, map 
to second hd, unpack backup, reboot. Maybe this could be automated?


Of course I have some sympathies already - stay with Fedora? Try 
Ubuntu server edition? Any other suggestion?

Thanks.

The SME Server at contribs.org is based on Red Hat and heavily customized 
to run almost all functions you mention with particularly easy user 
management. Don't know how current is is on Apache and MySQL. As 
long as you don't need /View it's OK, because it comes without X11

I just wonder, if Command got some fixes. Some openssl vulnerabities 
appeared in last few years, yet Command showed components of 2002 
year. mySQL, from 4.x, requires new authentication method .... I 
don't trust SDK here ;-)

thanks, will try it. Debeasch, it is Ashley, right? If RebGUI will 
be of the same quality as RebDB, it is surelly gonna be cool. Ashley 
is very good and precise designer, his code is nice. Other such folk 
is DocKimbel, his mySQL code was pleasure to study :-)

( and, no, I don't know anything about mysql .. )

if anyone uses, mysql, they can rewrite the sql statements.

Doc - so - will you release mysql protocol as 1.0 stable?

moving to MySQL channel

Working on something else at the moment, but very interested in the 
Uniserv implementation of MySQL.

The MySQL async driver in this archive is an alpha version. It only 
works with v5+ servers and new passwords format (databases with old 
passwords for users won't work with this alpha driver).

Graham, btw., what would be needed for Rebol FireBird support? Does 
it use typical tcp scheme as mySQL e.g.? This week I met with two 
ppl using FireBird, and there seem to be no answer from Rebol part. 
Well, maybe ODBC, but that is not free ...

  MySQL 5.0 Adds Features for Enterprise Developers and DBAs
by Ken 
  North

Baseball legend Satchel Paige is famous for having said 

Don't look back, something might be gaining on you." Companies selling 
a commercial SQL database management system (DBMS) know its MySQL 
that's gaining on them. With an already large installed base, MySQL 
is set to attract new users because of the feature set of version 
5.0. It includes capabilities for which developers have often turned 
to commercial SQL products.


The purposes for which we use personal, mobile, workgroup, departmental, 
enterprise and web databases are diverse. Application requirements 
are a primary determinant of the capacity and features we need from 
an SQL DBMS. For example, a high-volume transaction processing web 
site places greater demands on a database than a contact list manager 
for laptops and small business servers.


A Web Techniques magazine article, "Web Databases: Fun with Guests 
or Risky Business?" discussed features that characterize an industrial-grade 
SQL DBMS. It explained SQL security and mission-critical databases, 
defined as


    "A database is mission critical if its lack of data integrity has 
    serious consequences, such as causing the loss of customers or even 
    lives."


Maintaining data integrity is implicit -- that's a prime directive 
for a DBMS. The article explained other features that enterprise 
developers look for in an SQL platform:


    ... mission-critical applications require features such as intrinsic 
    security, transaction journaling, concurrency controls and the ability 
    to enforce data integrity constraints. Without those features, you 
    do not have secure, robust databases. Connecting a database to a 
    Web server adds other requirements, such as a multithreaded architecture 
    and the ability to do database backups without taking the server 
    down.


    Freeware and PC DBMSs are suitable for certain classes of applications, 
    but not for high-volume Web sites and mission-critical databases. 
    In any case, don't bet your business, or lives, on such software 
    unless you have the source code and the expertise to understand and 
    repair it.


Since that article appeared in print, improvements to MySQL have 
removed the "not ready for prime time" label. Features described 
in that article are now available to MySQL users:

    * transactions
    * concurrency control, locking, SQL standard isolation levels
    * intrinsic security
    * integrity constraints
    * thread-based memory allocation.

  TII Computer Deals at Dell Home Systems 180x150
	


MySQL uses separate threads to handle TCP/IP and named pipes connections, 
authentication, signaling, alarms and replication. The combination 
of threaded architecture and MySQL clustering provides powerful parallel 
processing capabilities. MySQL can process transactions in parallel 
with separate connections on separate processors using separate threads.
MySQL Milestones


A decade of development has moved MySQL out of the bare-bones DBMS 
category, enlarged its user base, and turned MySQL AB into a profitable 
company. One of the important milestones was integration of the InnoDB 
engine with MySQL 4.0. That upgrade gave MySQL multiple tablespaces, 
tables greater than 4GB and support for transaction processing. Other 
enhancements included OpenGIS spatial data types and hot backups. 
The latter enables a DBA to perform a backup without taking the DBMS 
offline. Hot backup software is available as a commercial add-on 
for databases using the InnoDB storage engine. 


MySQL 5.0, the newest version, is a major milestone. There have been 
enhancements to the tool sets, storage engines, types and metadata. 
 MySQL 5.0 includes features enterprise developers have come to expect 
from commercial SQL products. 

    * capacity for very large databases
    * stored procedures
    * triggers
    * named-updateable views
    * server-side cursors
    * type enhancements
    * standards-compliant metadata (INFORMATION_SCHEMA)
    * XA-style distributed transactions
    * hot backups.


MySQL has a demonstrated capacity for managing very large databases. 
Mytrix, Inc. maintains an extensive collection of Internet statistics 
in a one terabyte (1 TB) data warehouse that contains 20 billion 
rows of data. Sabre Holdings runs the oldest and largest online travel 
reservation system. It replicates 10-60 gigabytes per day from its 
master database to a MySQL server farm. The MySQL databases are used 
to support a shopping application that can accommodate a million 
fare changes per day."

The main architect for Interbase/Firebird, was just lured to the 
mysql camp.

Well, a couple of months ago .. so expect to see Firebird like syntax 
appearing in future versions of mysql.

Jim Starkey Sells Netfrastructure to MySQL AB and Moves On

 Today Jim Starkey, who led the original Vulcan fork of Firebird, 
 announced that he has sold his Netfrastructure web software business 
 to MySQL AB and will be taking up a full-time job as a developer 
 for the MySQL company.

Jim won't be a regular code contributor around Firebird any more, 
but he has promised he'll still be around to post the occasional 
"wolf-gram" in Firebird-Architect.

We in the Firebird Project wish Jim all the best for what looks like 
an interesting turn in his career.

why have you decided to use FB for your EMR system? Wouldn't you 
be better serverd by mySQL e.g.?

mySQL does limit you license-wise? IIRC it is completly open. Just 
their embedded library is licensed differently IIRC ... but well, 
I am not good at all that licensing stuff myself .... also PostGress 
is reported being really profi and we have good driver for it too 
....

Just write the SQL statement as you would in MySQL, etc ... then 
we can see where the issues are.

IIRC MySQL docs join section describes very nicely the technique 
of optimisation. And after reading it some few monts ago I agree, 
that 'join is not trivial task at all, basically due to get it optimised. 
But you are right that most of the time those two mentioned cases 
are needed. The thing which complicates the optimisation part is 
- is your column a key? primary key? is it at least indexed? etc.

here's some interesting reading on mySQL optimisations - http://dev.mysql.com/doc/refman/5.0/en/optimization.html

I've only used MySQL a little, and quite a while ago, but I _think_ 
update (without where) affected the entire table there. Could be 
wrong though :)


Anyhow, I really love RebDB (And RebGUI also). It's really easy to 
use, and my program would probably be alot messier if I wrote my 
own data management functions :P

for larger projects, I would surely use mySQL, install server, and 
be done, but I have small one, but guys want to share the app on 
two or three machines ....

they base their work on mySQL-protocol.r, which is imo BSD, or not?

even mysql does so ...

as for visual tools - just suggest me one. I run thru installation 
of most of them. I did not find ANYTHING in a quality of mySQL Admin 
and mySQL Query. Thwo of them I liked are commercial, so imo sqlite, 
as a database of the year, is really badly supported here ....

0.1.5 available at: http://www.dobeash.com/SQLite/sqlite.r

Changes of note include:


 - New /format refinement of CONNECT that formats output like MySQL 
 (can be turned on/off via SQLIte/format?: true|false)
	- Widths block (SQLite/widths) added to supporrt above

 - DESCRIBE, TABLES and INDEXES functions rewritten (and simplified)
	- Added an EXPLAIN function

 - CONNECT rewritten (note that the attach refinement has been replaced 
 by the function accepting a block! of file names instead)
	- DATABASE function added

 - Experimental IMPORT function added (but not exported to global 
 context - see example for how it is used)

 - Error trapping / reporting more informative (especially if a library 
 call error occurs)
	- Example block updated (do example)

Documentation will be updated as time permits.

I wonder if /direct is usefull at all and if we should have two modes 
.... does mysql driver has two modes?

The best pattern for such huge tables?
MySQL

Could you use a cursor?

(That would work with many other SQLs....Not sure about MySQL's support 
for them)

there is one thing I really don't like about sqlite - it stores everything 
into one file. I want one file for table, one file for index, as 
with mysql, because for me it means simplicity - I can just look 
into file system and see how big some table is, or selectively backup 
some tables .... mySQL works that way IIRC

I have good link, although it relates to mySQL - it shows how to 
query hierarchical data - http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

one more link related to trees in mysql: http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

it does not sound appropriate, or why guys were asking mysql to add 
insert .... on duplicate key update facility, if update would be 
able to do it itself?

MySQL has a non-standard command for inserting if not there for update: 
REPLACE. SQLite has more detailed conflict resolution, but includes 
REPLACE as a MySQL-compatible shortcut for INSERT OR REPLACE (the 
OR REPLACE is itself a shortcut for ON CONFLICT REPLACE). I agree 
that UPDATE should have the option of acting like INSERT OR REPLACE.

single user apps. Or fileshared ones. Embedded space. Advantage against 
mySQL embedded - SQL interface, free. Not easily used for server 
solutions, although could be solved by some server dispatching requests. 
No installation, just one single dll.

I wanted separate connections, but maybe I understand it wrong as 
it is not same as with mysql, so probably have to go with attach, 
thanks for the suggestions.

thanks,  I have the mySQL driver working but I was just mewsing at 
not having to have a WAMP deployment.

Yes, I quite happy with the speed and stability of the new implementation, 
I have RSP pages with 3 SQL queries to a MySQL backend, input and 
output filters, session handling, tables constructed dynamically 
with data from DB, all this occuring in a few milliseconds...I still 
need to test how it scales.

Chris, sorry to not have chimed in before, I have a quite modified 
cheyenne that is in production for some time, only I have little 
time to help as I'm evely under pressur, somu guys came up with joomla 
in the company I work for, so it's either me coding from 0 or them 
assempling  jomla modules.. I could clean it up and send u a copy 
but this will be obsoleted by Dockimbel next version. it has rewrite 
rules and a slightly modified mysql protocol and a hihly modified 
mysql wrapper  to support stored procedures and getting data easly. 
like database: mysq://localhost/dbName db-open value: db-get 'table 
'column [{id=?} variable ] .

The bugtracker will be fully open sourced and will also serve as 
demo of a complete online application done in RSP with a DB backend 
(MySQL currently).

about R2, have an ajax app with db calls (ok mysql +query cache enabled) 
, 200 reqs/sec brings a dualG5 2GHz at 80% cpu pretty fast 8-)

Graham: yes, RSP and Session support is fully working, but you should 
consider it beta state.

  - that's no problem, the mysql driver was beta quality for years 
  ;-)

give me a few minutes, I've reproduce the error here using RT's mysql 
driver, tracking the cause...

WE also just hired another DB guy just to handle the MySQL, he has 
20+ years in DB design and implmentation.

CureCode also requires a DB backend (currently MySQL). It could be 
adapted to other DB engines, there's just one file to adapt (%private/db-abstract.r)

it's a standalone product that needs Cheyenne and MySQL to run. Maybe 
one of the future flagship product for spreading Cheyenne...;-)

For an SQL expert, it shouldn't take more than one hour, I guess. 
Maybe even less, I didn't counted how much MySQL specific syntax 
or functions are used.

Paul : if TRETBASE can support the same DB structure, support SQL 
JOINs (or similar feature), user and security management, not being 
more verbose than SQL, and being as fast as MySQL without using more 
memory, I'll drop MySQL at once and use TRETBASE as a backend in 
all my projects.

CureCode is a lightweight bugtracker inspired by Mantis (a popular 
tracker in php). It's implemented using RSP and use MySQL as a backend.

It's a planned feature. It just needs a SQLite REBOL driver with 
an API compatible with my MySQL driver or RT's DB drivers.

I guess that it would also needs a few changes in the SQL requests 
also, MySQL specific features like LAST_INSERT_ID( ) need to be ported. 
Anyway, it shouldn't take more than a couple of hour to make it work 
with SQLite (once you have the right driver).

Ah, looks like it's missing a user right access in MySQL.

If someone can do a step by step blow on installing curecode ( including 
what ever one has to do with mysql ) .. I will try and set it up 
as well .. I need a public bug tracking database too.