Now I found the reason how 'mysql_affected_rows' cause the httpd halt (with drop-in replacement library) Compare the following two statement: 1. $res = mysql_query("delete from data where nbr < 10"); echo mysql_affected_rows() . " rows have been deleted"; 2. mysql_query("delete from data where nbr < 10"); echo mysql_affected_rows() . " rows have been deleted"; The first one using a variable $res, it works fine, but the second with an error. -------------------------------------------------------------------------------- mysql output binds: >So you'll be needing to make several calls to mysql_next_result() to get the OUT/INOUT values. This is for a general purpose MySQL driver called from our products. I did it like this: 1. Create a "set @vn=?[,@v(n-1)=?[,...]]" string based on the number n of incoming parameters (IN, INOUT, OUT). 2. mysql_stmt_prepare(), mysql_stmt_bind_param(), mysql_stmt_execute() 3. Create a "CALL ([@vn | literal_value[,@v(n-1) | literal value[,...]]" string. 4. mysql_stmt_prepare(), mysql_stmt_execute() 5. Create a "select @vm[,@v(m-1)[,...]]" string based on the number m of INOUT and OUT parameters. 6. mysql_stmt_prepare(),mysql_stmt_result_metadata(),mysql_stmt_execute() 7. Loop through mysql_fetch_field_direct and build a MYSQL_BIND structure, mysql_stmt_bind_result(). 8. mysql_stmt_fetch() 9. Grab the results 10. mysql_stmt_fetch() until rc == MYSQL_NO_DATA Step 10 was required otherwise I got "Commands out of sync; you can't run this command now" if I tried to run another stored procedure on a different statement handle. All the above steps are run on the same statement. Initially I thought that I could simply mysql_stmt_bind_param the IN and INOUT parms and mysql_stmt_bind_result the INOUT and OUT parameters; clearly this was not the case. Thanks for your help Hasani, I never would have figured this out. Is this documented somewhere? All I found concerning SPs was chapter 17 of the UG. -------------------------------------------------------------------------------- sqlr-listeners pile up upon shutdown if clients are still active support oracle OS authentication Sybase IQ support ruby install fails on solaris 10 query router: "do something"'s support accessing different db's by specifying a different user/password support accessing different db's by specifying which db to use semop's can return ENOSPC if the undo list is full which is unhandled and causes deadlocks in the sqlr-listeners. If we get an ENOSPC, then we need to send the client an error and the sqlr-listener needs to exit. if a client connects and the forked listener ends up waiting for a connection and the client disconnects, the forked listener has no way of knowing that the client has disconnected and can't bail need to test-link something for each detected api/db to make sure it's x86_64 OCI8 drop-in library hack on postgresql's ecpg to emit SQL Relay code rather than Postgresql code perl apis -> CPAN get sid working, document how to: create db create user mysql -usiddb -psiddb siddb < siddb set up sqlrelay instance to talk to it set up another instance to use siddb instance for sid populate siddb sidparameters: sparam=INGRESS - perform ingress filtering EGRESS - perform egress filtering LISTEN - ??? VERIFICATION - don't send queries to db PREVENTION - send queries to db if ok svalue=1/0 sidingressblist - ingress blacklist sql_state - queries to reject sidingresswlist - ingress whitelist sql_state - queries to accept, overrides blacklist sidingressldb - ingress learning database parsed_sql - ??? sidegressblist - egress blacklist sql_state - "parsed results" to reject sidegresswlist - egress whitelist sql_state - "parsed results" to accept overrides blacklist sidegressldb - egress learning database parsed_sql - ??? what are "parsed results" - something to do with column names... sidlog - every query is logged here with a message about it solaris 8: libgcc_s not found solaris 9: tries to look in /opt/sfw/lib/mysql/.libs for libmysqlclient (libtool looks in .libs if -Lpath doesn't end in lib) but it works on other platforms...? links fine, works after manually modifying config.mk to link directly against the .so file openbsd-3.8 handoff="pass" doesn't work cygwin handoff="pass" doesn't work php debug doesn't print out numbers because zend_printf doesn't support %ld's interbase fails float output bind test, prec/scale issue? Tru64 - unix sockets don't work, looks like a byte-order translation issue can't start more than 1 cursor, looks like out-of-memory setting backlog: linux: looks like max is 128 on linux 2.0 2.2/2.4 - /proc/sys/net/ipv4/tcp_max_syn_backlog Default is 1024 for systems with more than 128Mb of memory, and 128 for low memory machines. If you make it greater than 1024, change TCP_SYNQ_HSIZE in include/net/tcp.h to keep TCP_SYNQ_HSIZE*16<=tcp_max_syn_backlog and recompile kernel. syn cookies - no max backlog solaris: /usr/sbin/ndd -set /dev/tcp tcp_conn_req_max 32 Tests: stored procedures for mysql-5.0 adodb using peardb driver db2 temp tables odbc temp tables zope odbc tests mysql_stmt calls in drop-in library better pythondb/rubydbi tests test init scripts test send query w/length test upper/lower/mixed column case methods need database reconnect tests column flags getFieldAsXXX getOutputBindAsXXX Java "getXXXAsByteArray" methods check blob/lobs with binary data in them perl dbi driver execute_array bind_param_array selectall_arrayref with attr/binds selectall_hashref with attr/binds selectcol_arrayref with attr/binds selectrow_array with attr/binds selectrow_arrayref with attr/binds selectrow_hashref with attr/binds prepare_cached fetchall_arrayref with slice quote err errstr state begin_work available_drivers data_sources oldmysql test files for non-C++ api's cygwin: how to use with non-cygwin dll's? how to install as a service? languages: VB wrapper how to build a php extension? 1) need php source active state python http://sebsauvage.net/python/mingw.html non-cygwin ruby creates funny CFLAGS active state perl PERL5LIB env var points to Oracle's perl installation perl -V:prefix return C:\Perl active state tcl no doubt will have similar problems... -I/cygdrive/Tcl/include -L/cygdrive/c/Tcl/lib -ltcl84 -ltclstub84 zope /cygdrive/c/Program Files/Zope-2.7.1/lib/python/Products databases: sqlite .dll and .def file wherever msql won't build, need older bison doc installing packages optional cygwin components running sqlrelay clients add path to mysql dll to PATH Compatibility: * daemontools * rename interbase stuff firebird * figure out how to get the drop-in client compiled into the mysql PHP module like the mysql native client is Documentation: * snippets from tnsnames.ora can be used as ORACLE_SID * how to use new features of Tony/Erik's perl dbi patch * issue with sqlrelay.conf permissions and runasuser="nobody" * getting started with odbc * how to use pear db driver with adodb * "how sqlrelay works" with diagrams and animations * example sqlrelay.conf's for different configurations * performance benchmark charts * HOWTO for linuxdoc.org * man pages for everything * document tds version for various versions of MSSQL/Sybase Performance/Code Enhancements: * NLS_LANG-like issues with other db's? * client connect timeout * store passwords in encrypted form in the config file * call endSession() from perl DBD finish method * implement column flags as a bitmap * persistent cursors - parse query, attach an id to it, make it available to other sessions * connection daemons could put buffers in shared memory and clients could send queries, access buffers using shared memory and semaphores instead of unix sockets * api methods to use preallocated buffers for result sets * api methods for configuring optimistic buffer sizes * result set compression during transmission for distant relays * server side result set caching * support batch DML in oracle8 * for databases that can return non-strings in their result sets, send non strings to client * seperate modules into standalone builds * should be a single sqlr-connection daemon that dlopens the appropriate database module * login errors should be printed on the screen by sqlrconnection class, not by individual connections * api for running queries in the background (asynchronous queries w/timeouts) * use async queries by default, even if the client runs a synchronous query, so the query can be cancelled if the client disconnects * support freetds/sybase ct_con_prop/encryption * use bandwidth delay product to set tcp send/receive buffer sizes Security: * SSL encryption Features: * 2-phase commit, use it in query router * use PAM if possible as alternative to defining users in sqlrelay.conf * support stored procedures that return result sets in sybase, interbase, db2 * could debug logs be logrotate compatible? * postgresql drop-in api lacks many features * postgresql drop-in needs to parse out ; delimited queries for db's that don't support that, execute them seperately * mysql drop-in api lacks a few features, stmt functions need to be updated * sybase/freetds (possibly others) need options for setting chained mode and isolation mode when client connects datatype support: handle binary data correctly in all databases identical long/lob datatype support in all databases more advanced long/lob datatype support support for array datatypes (oracle collections) handle blobs, quads in interbase handle timestamps handle enum types in mysql front end replication quotas logging handle commit/rollback for named transactions implement API's that handle errors with exceptions better transaction handling for postgresql odbc connection should check the odbc definition for connect string arguments - SQLBrowseConnect solaris init script api needs to expose integer column types call dontGetColumnInfo() when doing fetchrow_array calls in DBD driver Some databases (like postgresql) are in auto-commit mode unless you manually begin a transaction. SQL Relay calls commit/rollback at the end of a client session, even if the database is in auto-commit mode. For postgresql at least, this is expensive. It should only call commit/rollback if the database isn't in auto-commit mode. date object binds Availability: connections should ping database server every so often to make sure it's up, if it's not, relogin failover - master, fallback databases, where connection only goes to the fallback if the master is overloaded or down if a connection dies mysteriously, a new one should fire up backends: pam_sqlrelay mod_auth_sqlrelay for apache samba auth module bind 9.1 backend dhcp backend qmail backend dbmail backend qpopper port bugzilla port litesql API's: JDBC ODBC QT3 C# - have a driver, just need to incorporate it look at npgsql for .net data provider example ADA Eiffel gnome-db D dbstep ADODB driver PHP PDO driver http://wiki.cc/php/PDO PHP5 Creole driver Drop-in replacement libraries: Oracle OCI7/8 Sybase-ct Interbase Native DB Support: Informix SAP DB LocalSQL kekedb ThinkSQL picosql shsql does clip provide an odbc driver for accessing x-base files? daffodildb/one$db LK bin DB - http://www.lksoft.cjb.net/en/ Cache - http://intersystems.com Frontbase J-stels - stelsdbf, stelscsv (http://www.csv-jdbc.com/) Config Tools: gtk2 QT curses need keyboard shortcuts size should be less than 640x480 connectstring editor wizard