Re: Let's approach stored procedures
On Tue, 11 Sep 2001, Arnulf Kristiansen wrote: | I have just started to look into the stored procedure issue. We will come up with a | definite plan within a short period of time. But does this mean that you won't welcome any other initiative or ideas from other programmers? | | It is true that there are solutions that would make transitions from another DBMS to |MySQL | easier however, I am leaning towards the ANSI standard rather than PL/SQL or |Transact-SQL. | If we still need to provide other languages or subsets of languages for |compatibility and | easy transition, we might provide this later. Again, if two persons (groups) were working on this at the same time, then you could take ideas from both, ditch one of the implementations and use the other one's ideas in the remaining. Much faster development, and will problaby be better. | Widening the existing SQL functionality is most likely going to be the | first step. Why is this a first step? As I see it, SQL (as in the query language) is a rather different thing than an scripting language or similar (read stored procedures). -- Mvh, Endre. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Let's approach stored procedures
newbie DB developer question Are stored procedures scripts or compiled code ? James - Original Message - From: Endre Stølsvik [EMAIL PROTECTED] To: Arnulf Kristiansen [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 5:09 PM Subject: Re: Let's approach stored procedures On Tue, 11 Sep 2001, Arnulf Kristiansen wrote: | I have just started to look into the stored procedure issue. We will come up with a | definite plan within a short period of time. But does this mean that you won't welcome any other initiative or ideas from other programmers? | | It is true that there are solutions that would make transitions from another DBMS to MySQL | easier however, I am leaning towards the ANSI standard rather than PL/SQL or Transact-SQL. | If we still need to provide other languages or subsets of languages for compatibility and | easy transition, we might provide this later. Again, if two persons (groups) were working on this at the same time, then you could take ideas from both, ditch one of the implementations and use the other one's ideas in the remaining. Much faster development, and will problaby be better. | Widening the existing SQL functionality is most likely going to be the | first step. Why is this a first step? As I see it, SQL (as in the query language) is a rather different thing than an scripting language or similar (read stored procedures). -- Mvh, Endre. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Let's approach stored procedures
Endre Stølsvik wrote: On Tue, 11 Sep 2001, Arnulf Kristiansen wrote: | I have just started to look into the stored procedure issue. We will come up with a | definite plan within a short period of time. But does this mean that you won't welcome any other initiative or ideas from other programmers? Absolutely not. All ideas and initiatives are welcome. I will however try to coordinate efforts as best i can. | | It is true that there are solutions that would make transitions from another DBMS to MySQL | easier however, I am leaning towards the ANSI standard rather than PL/SQL or Transact-SQL. | If we still need to provide other languages or subsets of languages for compatibility and | easy transition, we might provide this later. Again, if two persons (groups) were working on this at the same time, then you could take ideas from both, ditch one of the implementations and use the other one's ideas in the remaining. Much faster development, and will problaby be better. I don't disagree. Which ever way we go I think we will end up with quite a few additions to make it easier on people with one preference or another and to facilitate transitions to MySQL. A lot of fundamental stuff is going to be common to the different variants and I like to concentrate on something rather small for starters, keeping the larger objective in mind. | Widening the existing SQL functionality is most likely going to be the | first step. Why is this a first step? As I see it, SQL (as in the query language) is a rather different thing than an scripting language or similar (read stored procedures). This can be done relatively fast, but I agree it will is not the same thing. /Arnulf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Table Mysqld host doesnt exist
Hi, Looks like you didn't run mysql_install_db. NOTE: You'll have to change ownership of the database to mysql:mysql. Regards, Ian. I installed MYSQL on a HPUX 11 machine and I ran the mysql-test-run script and everything passed, but when I start mysql up using bin/safe_mysqld or bin/safe_mysql --user=mysql I receive this error in the logfile Table 'mysqld.host' doesn't exist Any solutions -- # Kory Wheatley Academic Computing Analyst Sr. Phone 282-3874 # Everything must point to him. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Let's approach stored procedures
James Clifford wrote: newbie DB developer question Are stored procedures scripts or compiled code ? James It can be either. Compiled code generally offers greater performance while something that has to be accepted by an interpreter will be more robust, provided the interpreter is solid. When it comes to compiled code you can either have a built in compiler or you can compile you code yourself and present the executable to the server one way or another (like UDF's). /Arnulf - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Rolling over on sequence number?
I have a requirement to use a sequence number table that I can reserve a block of sequences, (so auto increment is not suitable). This is my proposed table structure create table SEQNO ( SEQNO INT UNSIGNED NOT NULL DEFAULT 0, SESSION VARCHAR(10), LOCK_TIMEOUT TIMESTAMP); my blocks will go up in 100's, using SQL like update SEQNO set SEQNO = SEQNO + 100; My question is, is there any way to get the database to roll over the int value when it gets to 4,294,967,295? In my tests it goes upto the max value and stays there. I could do it programmatically, but it would be so much nicer if the database rolled over the value. for example 4,294,967,200 + 100 - 4 Any ideas? I have read manual, and MySQL book regarding sequences, but could not find anything relevant. Thanks Robin - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Group By clause is behaving two different way?
Hi I am using windows2000 server and MYSQL ver3.23.41-win and Active Server Pages to retreive MYSQL result set to my webpage, It is retreiving well if there is only one row in the table( It is not reaching EOF immediately), but if there is more then one row (in group by result) then immediately reaching EOF. How can i move to the BOF. Please guide me in this issue. My query is : Select agent_code,count(agent_code) AS Expr1 from eworkeronline_workers where (sno0) and (selected=0) and (subskillID='101')and ( age = 18 or age=19 )group by agent_code Thanks selva - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql 3.23.42 errors during compile on Solaris
Hi, when I compilling mysql that occur something error below on solaris. Soft Spc. Solaris 8 Mysql 3.23.42 gcc 3.0.1 GNU Make 3.79.1 My steps: gunzip -d -c mysql-3.23.4.tar.gz | tar xvf - cd mysql-3.23.42 CC=gcc CFLAGS=-O3 CXXLD=g++ \ CXX=gcc CXXFLAGS=-O3 -felide-constructors -fno-exceptions -fno-rtti \ ./configure \ --prefix=/usr/local/mysql \ --with-low-memory \ --with-extra-charsets=complex \ --enable-assembler make Error: creating mysqltest gcc -DUNDEF_THREADS_HACK -I./../include -I../include -I./.. -I.. -I..-O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/mysql-3.23.42/include -DHAVE_RWLOCK_T -c mysqlbinlog.cc /bin/sh ../libtool --mode=link gcc -O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/mysql-3.23.42/include -DHAVE_RWLOCK_T -o mysqlbinlog mysqlbinlog.o ../libmysql/libmysqlclient.la -lz -lcrypt -lgen -lsocket -lnsl -lm gcc -O3 -DDBUG_OFF -O3 -felide-constructors -fno-exceptions -fno-rtti -fno-implicit-templates -fno-exceptions -fno-rtti -DHAVE_CURSES_H -I/usr/local/mysql-3.23.42/include -DHAVE_RWLOCK_T -o .libs/mysqlbinlog mysqlbinlog.o ../libmysql/.libs/libmysqlclient.so -lz -lcrypt -lgen -lsocket -lnsl -lm -lz -lcrypt -lgen -lsocket -lnsl -lm -Wl,--rpath -Wl,/usr/local/mysql/lib/mysql mysqlbinlog.o(.gnu.linkonce.d._ZTV9Log_event+0xc): undefined reference to `__cxa_pure_virtual' mysqlbinlog.o(.gnu.linkonce.d._ZTV9Log_event+0x1c): undefined reference to `__cxa_pure_virtual' collect2: ld returned 1 exit status make[2]: *** [mysqlbinlog] Error 1 make[2]: Leaving directory `/usr/local/mysql-3.23.42/client' make[1]: *** [all-recursive] Error 1 make[1]: Leaving directory `/usr/local/mysql-3.23.42' make: *** [all-recursive-am] Error 2 Anybody help-me? Thanks! --- José Raimundo S. Barbosamailto:[EMAIL PROTECTED] Setor de Informação DDR 621-0350/0324 --- Embrapa Amazônia Ocidental http://www.cpaa.embrapa.br Rod. AM-010 KM 29 CEP: 69011-970 Cx. Postal 319Manaus, AM - Brasil Fone:(092)621-0300 FAX:(092)622-1100/232-8101 Embrapa-Sat: 81 Fax: --- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
UPDATE with automatic INSERT if not found..
Hi! I have a counter that is increased each time an event occur. The number of events should be reported as events / month, so along with the counter i have a date-field. The table structure looks like this: table stats count int unsigned not null default 0 datetimestamp (using only year/month part) I update this table with: UPDATE stats SET count=count+1 WHERE date=XXX BUT, if the date is not there, no counter will be updated so I must first check this and insert a record if date is not found. Since there is more than one thread that can write to this table I must use a lock: LOCK TABLES stats WRITE SELECT COUNT(*) FROM stats WHERE date=XXX if count == 0 INSERT INTO stats ... else UPDATE stats ... UNLOCK TABLES Can this be specified as one statement, so that I dont have to use table locks?? Thanks! /torgil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
using LEFT JOINS
Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE with automatic INSERT if not found..
check the replace syntax: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#REPLACE regards rene On Thu, 13 Sep 2001 13:40:44 +0200 Torgil Zechel [EMAIL PROTECTED] wrote: Hi! I have a counter that is increased each time an event occur. The number of events should be reported as events / month, so along with the counter i have a date-field. The table structure looks like this: table stats count int unsigned not null default 0 datetimestamp (using only year/month part) I update this table with: UPDATE stats SET count=count+1 WHERE date=XXX BUT, if the date is not there, no counter will be updated so I must first check this and insert a record if date is not found. Since there is more than one thread that can write to this table I must use a lock: LOCK TABLES stats WRITE SELECT COUNT(*) FROM stats WHERE date=XXX if count == 0 INSERT INTO stats ... else UPDATE stats ... UNLOCK TABLES Can this be specified as one statement, so that I dont have to use table locks?? Thanks! /torgil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE with automatic INSERT if not found..
Torgil Zechel writes: table stats count int unsigned not null default 0 datetimestamp (using only year/month part) I update this table with: UPDATE stats SET count=count+1 WHERE date=XXX BUT, if the date is not there, no counter will be updated so I must first check this and insert a record if date is not found. Since there is more than one thread that can write to this table I must use a lock: Can this be specified as one statement, so that I dont have to use table locks?? You could to the UPDATE first, and if it updates 0 rows do an INSERT IGNORE, and if that too changes 0 rows another thread has inserted a row with that date in the meantime and you just have to do the UPDATE again. UPDATE ... if(changed 0 rows) { INSERT IGNORE ... if(changed 0 rows) UPDATE ... } Of course you could skip the first UPDATE and try the INSERT immediately, but this will probably be less efficient since most of the time the first UPDATE would be all that is needed. //C -- Carl Troein - Círdan / Istari-PixelMagic - UIN 16353280 [EMAIL PROTECTED] | http://pixelmagic.dyndns.org/~cirdan/ Amiga user since '89, and damned proud of it too. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Fulltextsearch
Hi! On Sep 07, Doug Poland wrote: On Fri, Sep 07, 2001 at 10:41:33AM -0500, Ed Carp wrote: Lorang Jacques ([EMAIL PROTECTED]) writes: Now I was wondering if it would get faster by using FULLTEXTSEARCH on my tables. As it is quiet some work, I first want to ask you guys if this is really going to make my queries faster (much faster ?) Yup. I've got the entire set of laws for the State of Texas online. My query time went down from 120 seconds to less than a second. So, yes, it's really worth it. Do the current limitations of 500 characters per index and limited (?) number of columns per index pose problems? Limited number of columns restriction applies to FULLTEXT indexes as well (I think). 500 char/index limitation does not . I want to use fulltext on a database with 7 varchar(255) columns and several smaller varchar columns but can't build a fulltext index. What was your problem, exactly ? Regards, Sergei -- MySQL Development Team __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, http://www.mysql.com/ /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
ANN: EMS MySQL Manager 0.91 released
Dear Sirs and Madams, EMS HiTech company is announcing the next version (0.91) of MySQL Manager -- A Powerful MySQL Administration and Development Tool for Windows95/98/ME/NT/2000/XP. You can download the latest version from http://www.mysqlmanager.com/download.phtml First, thanks to all our users who already registered! We are very glad that you're using our product. Also, we are a very much thankful to all user who sent us their suggestion about MySQL Manager. These suggestions will make our product much more better than it is. What's new in version 0.91? 1. New menu Services was added. Now new features available in this menu: a) Backup tables. You can backup tables from any database to the directory on your hard disk. b) Restore tables. You can restore previously backuped tables from your hard disk to registered/unregistered or new database. c) Flush hosts/logs/privileges/tables/tables with read lock/status. You can use this feature to clear some of the internal caches of MySQL server. The Analyze table, Check table, Repair tables and some more features of this kind will be available soon. 2. Fixed bug with help system. Now trying to show a help file doesn't cause an error. 3. Fixed bug with adding of fulltext indices. Fulltext indices is now supporting properly. 4. Some minor bugs were fixed. What is the EMS MySQL Manager? EMS MySQL Manager provides you powerful and effective tools for MySQL Server administration and objects management. Its Graphical User Interface (GUI) allows you to create/edit of all MySQL database objects most easy and simple way, run SQL scripts, manage users and administrate users' privileges, visually build SQL queries, extract or print metadata, export/import data, view/edit BLOBs and many more services that will make you work with MySQL server as easy as you want... Best regards, EMS HiTech development team. http://www.ems-hitech.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Question about LIKE statements and escaped characters
Hi, I posted this query to the list via the newsgroup mailing.database.mysql a few days ago but didn't receive any replies. If someone could respond and let me know whether or not I'm asking something that should be obvious or if this is just something that's not that common so know one knows the answer, I would really appreciate it. :-) Anyway, on with my question: I have MySQL v3.22.32 running on Linux (RH 7.0). I'm noticing what seems to me to be strange behavior of LIKE statements when strings containing escape characters are involved. In the odd rare case that I have a value in a field that actually contains the backslash character in it, it seems that the behavior of LIKE changes, and to find the field I have to add an additional set of backslashes. If I am searching for an exact match the number of backslashes changes. I'm using a PHP script to store and retrieve some data and unless I can figure out why this is happening I will have to deal with it in my program. Anyway, this is best illustrated with an example. Lets say I have a table with one field, and I have three rows, like so: mysql select message from im; +-+ | message | +-+ | Mike| | Mike's | | Mike\'s | +-+ 3 rows in set (0.00 sec) Now, let's say I want to find the third row listed using an exact match: mysql select message from im where (message = 'Mike\\\'s'); +-+ | message | +-+ | Mike\'s | +-+ 1 row in set (0.00 sec) This behaves as I would expect (and according to the documentation I've read). Now, my understanding is that I SHOULD be able to retrieve the same result with a LIKE statement, but look: mysql select message from im where (message LIKE 'Mike\\\'s'); +-+ | message | +-+ | Mike's | +-+ 1 row in set (0.00 sec) That's not the result I expected at all...it looks like it ignored two of my backslashes (as if I had typed ...message LIKE 'Mike\'s'); To find the one I want with a LIKE statement, I have to add two MORE backslashes, like so: mysql select message from im where (message LIKE 'Mike\'s'); +-+ | message | +-+ | Mike\'s | +-+ 1 row in set (0.00 sec) If ANYONE could explain this behavior or at the very least point me in the direction of some resource that explains it I would be extremely grateful. I apologize if this is a basic or obvious question, but I have looked through the documentation on www.mysql.com and haven't found anything explaining this. Thanks in advance to anyone who can help... - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Size of DB
On Thursday 13 September 2001 03:12, Antoine E. Hall wrote: Hello, I'm new to MySQL and I was wondering if there was a command that you can issue to see the current size of a mysql database (in kb or MB)? Not in MySQL as such (unless I've missed something in the meantime). In Unix/Linux du -k /var/mysql/database_name (or wherever your database is located (*)) will get you the total size of all files for that database in kilobytes. (In Linux du -sh will produce an easier-to-read megabyte value if the total is = 1 MB). If MySQL is properly installed you will need root permissions to read the data directory. In Windows you can probably do something involving the mouse and some icon-thingies, or maybe a simple DIR at the command line. (*) If you are not sure, use the command SHOW VARIABLES or at the command line mysqladmin variables and look for 'datadir'. Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Size of DB
Ian Barwick wrote: In Windows you can probably do something involving the mouse and some icon-thingies, or maybe a simple DIR at the command line. Right-click on the MySQL database folder, selecting Properties. Under DOS, substitute your Unix command with DIR and look at the total at the end of the list, and use backslashes instead of forward slashes. Of course, you could do the icon-thingy with KDE, Gnome, etc. Frank. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
errors 2006 and 2000
hi i'm totally new to this so you'll have to forgive these questions if they are really basic. the situation with my server is that i have a user name that is associated with a mysql database i logged in (after telnetting in) as the user using: mysql -u username -p i then entered my password i got Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 930616 to server version: 3.22.25 Type 'help' for help. mysql as a reply which i took as a good sign. i then entered: create table saveMovie ( - id int not null auto_increment, - Name varchar(30) not null unique, - Object1 varchar(60), - Object2 varchar(60), - Object3 varchar(60), - Comment text, - primary key (id)); as a reply i got ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:930844 Current database: databasename ERROR 2000: You have an error in your SQL syntax near 'unique, Object1 varchar(60), Object2 varchar(60), Object3 varchar(60), Comment t' at line 3 as error messages i am not sure why i am getting the error 2000 as i have got the code from a mysql tutorial but the error 2006 worries me as it looks as though i am not connected to any database also i am not sure if the error 2006 is causing the error 2000. can you help? am i doing something wrong or is there an error with my mysql database? thanks toby - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: Size of DB
I'm new to MySQL and I was wondering if there was a command that you can issue to see the current size of a mysql database (in kb or MB)? If your intent is to get the size 'on the fly' from within a web page, you can use the filesystem object. I use ChiliSoft ASP but the solution is the approximately the same for IIS ASP: Syntax for a function to return filesize (in JScript/ASP) is: function ShowFileSize(filespec) { var fs, f, s; fs = new ActiveXObject(Scripting.FileSystemObject); f = fs.GetFile(filespec); s = f.Name + uses + f.size + bytes.; Response.Write(s); } Jay Fesco Magical Mystery Words: database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: errors 2006 and 2000
create table saveMovie ( - id int not null auto_increment, You attempted to create a table without telling MySQL which database to use (or without creating a database in the first place). Either: \u MyDatabaseName -OR- create database MyDatabaseName; \u MyDatabaseName; before trying to create a table. Jay Fesco Magical Mystery Words: database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE with automatic INSERT if not found..
REPLACE is different than UPDATE - replace deletes the row and then inserts a new one. Update updates the values in the existing row. Big difference if you aren't updating all columns... - Original Message - From: Rene Tegel [EMAIL PROTECTED] To: Torgil Zechel [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 8:03 AM Subject: Re: UPDATE with automatic INSERT if not found.. check the replace syntax: http://www.mysql.com/documentation/mysql/bychapter/manual_Reference.html#REP LACE regards rene On Thu, 13 Sep 2001 13:40:44 +0200 Torgil Zechel [EMAIL PROTECTED] wrote: Hi! I have a counter that is increased each time an event occur. The number of events should be reported as events / month, so along with the counter i have a date-field. The table structure looks like this: table stats count int unsigned not null default 0 date timestamp (using only year/month part) I update this table with: UPDATE stats SET count=count+1 WHERE date=XXX BUT, if the date is not there, no counter will be updated so I must first check this and insert a record if date is not found. Since there is more than one thread that can write to this table I must use a lock: LOCK TABLES stats WRITE SELECT COUNT(*) FROM stats WHERE date=XXX if count == 0 INSERT INTO stats ... else UPDATE stats ... UNLOCK TABLES Can this be specified as one statement, so that I dont have to use table locks?? Thanks! /torgil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL ODBC on Unix
Hi, The m/c on which I have my database is also Unix, and the machine from which I want to connect is also Unix. Instead of directly connecting to database, I want to use ODBC in between, so that, if tomorrow, my database changes, I should not change my program. Any idea about how to go about it?? (What I read from MyOODBC docs is that it is just a driver and not a program manager..I really don't know how to use ODBC on UNIX).. TIA.. Regards, Seenu. -- - If not here, you can reach me at [EMAIL PROTECTED] C-DOT, 71/1, Sneha Complex, Miller Road, Bangalore - 560 052 Ph: 226 3399 Ext: 268/Direct: 238 3951 (Off) 363 1707 (Resi) http://www.seenutn.org - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
i am begginer ...
hi all good morning. i ahve a question: i use delphi and want work with mysql too, need create indexes in the tables of mysql for more speend in the querys? how can create the indexes? thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: i am begginer ...
alter table add index command Please look at the online manual it is very good Simon -Original Message- From: Miguel Enriquez Alvarado [mailto:[EMAIL PROTECTED]] Sent: 13 September 2001 15:05 To: [EMAIL PROTECTED] Subject: i am begginer ... hi all good morning. i ahve a question: i use delphi and want work with mysql too, need create indexes in the tables of mysql for more speend in the querys? how can create the indexes? thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: i am begginer ...
Read the MySQL manual, it is very well written and will tell you exactly how to create any index you like. Try here: http://www.mysql.com/doc/O/p/Optimizing_Database_Structure.html # Nathan - Original Message - From: Miguel Enriquez Alvarado [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 8:05 AM Subject: i am begginer ... hi all good morning. i ahve a question: i use delphi and want work with mysql too, need create indexes in the tables of mysql for more speend in the querys? how can create the indexes? thanks - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: UPDATE with automatic INSERT if not found..
You COULD do what we do: select GET_LOCK(date) select (update | insert) select RELEASE_LOCK(date) [I don't recall the exact syntax on the get lock) Anyway, all of our software uses this to emulate row-level locking. It is not as elegant as a 2 line SQL statement,but it is effective and readable. =) On Thursday 13 September 2001 05:40 am, you wrote: Hi! I have a counter that is increased each time an event occur. The number of events should be reported as events / month, so along with the counter i have a date-field. The table structure looks like this: table stats count int unsigned not null default 0 datetimestamp (using only year/month part) I update this table with: UPDATE stats SET count=count+1 WHERE date=XXX BUT, if the date is not there, no counter will be updated so I must first check this and insert a record if date is not found. Since there is more than one thread that can write to this table I must use a lock: LOCK TABLES stats WRITE SELECT COUNT(*) FROM stats WHERE date=XXX if count == 0 INSERT INTO stats ... else UPDATE stats ... UNLOCK TABLES Can this be specified as one statement, so that I dont have to use table locks?? Thanks! /torgil - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
DB Files in an alternate dir
Hi! Can I put the DB files in another dir instead of default dir? Let me explain: In my actual system, the files are in /var/lib/mysql (for all tables). I want to put the tables (files) of the user Jonh in the Jonh\'s user directory. The same for many others users. How can I do that? Thanks, Wendell. ___ FreeMail SunNet - É muito bom ter você por perto! (82)357-3150 http://www.sunnet.com.br - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using LEFT JOINS
Show us your table definitions, what you tried, and what you think didn't work. Deryck Henson wrote: Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
slave_net_timeout?
The manual says this is a valid my.cnf option for replication: slave_read_timeout=# Number of seconds to wait for more data from the master before aborting the read. http://www.mysql.com/doc/R/e/Replication_Options.html My server (mysql-3.23.41-sun-solaris2.8-sparc) doesn't recognize that. There is, however, a variable named 'slave_net_timeout.' I'm guessing that's the same thing? Looks like that manual ought to say ... set-variable = slave_net_timeout=# ... instead. Please CC me on any replies as I'm not subscribed to this list. - Matt - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql Fulltextsearch
On Thu, Sep 13, 2001 at 02:17:51PM +0200, Sergei Golubchik wrote: On Sep 07, Doug Poland wrote: On Fri, Sep 07, 2001 at 10:41:33AM -0500, Ed Carp wrote: Lorang Jacques ([EMAIL PROTECTED]) writes: Now I was wondering if it would get faster by using FULLTEXTSEARCH on my tables. As it is quiet some work, I first want to ask you guys if this is really going to make my queries faster (much faster ?) Yup. I've got the entire set of laws for the State of Texas online. My query time went down from 120 seconds to less than a second. So, yes, it's really worth it. Do the current limitations of 500 characters per index and limited (?) number of columns per index pose problems? Limited number of columns restriction applies to FULLTEXT indexes as well (I think). 500 char/index limitation does not . I want to use fulltext on a database with 7 varchar(255) columns and several smaller varchar columns but can't build a fulltext index. What was your problem, exactly ? Problems is knowning how many columns can be indexed in a table. Is that number including non-FULLTEXT indexes as well? Is the 500 char/index limitation a compile-time option? I've got some more questions about the behavoir of FULLTEXT but am leaving for the weekend and will ask on Monday. Thanks for your help so far! -- Regards, Doug - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: errors 2006 and 2000
thanks for that i tried doing that (\u mydatabasename;) but same results: ERROR 2006: MySQL server has gone away No connection. Trying to reconnect... Connection id:937749 Current database: databasename ERROR 2000: You have an error in your SQL syntax near 'unique, Object1 varchar(60), Object2 varchar(60), Object3 varchar(60), Comment t' at line 3 is it me? or is there a problem with the mysql server on my server? thanks very much toby -Original Message- From: Jay Fesco [mailto:[EMAIL PROTECTED]] Sent: 13 September 2001 15:06 To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Subject: RE: errors 2006 and 2000 create table saveMovie ( - id int not null auto_increment, You attempted to create a table without telling MySQL which database to use (or without creating a database in the first place). Either: \u MyDatabaseName -OR- create database MyDatabaseName; \u MyDatabaseName; before trying to create a table. Jay Fesco Magical Mystery Words: database,sql,query,table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
INNOBASE, autocommit = 0, and C API ( mysql_real_query )
Hello, I am using Innobase with MySql version 3.23.39 under Linux and want to run WITHOUT autocommit mode (= autocommit=0 for using commit/roolback instructions) I have made a test whith 2 INTERACTIVE MySql sessions and this works fine. BUT, when I use the C API (mysql_real_query), it DOESN't work. I mean the autocommit=0 is NOT taken in account //-- My C code looks like : // set autocommit = 0 mysql_real_query(connection, set autocommit = 0, the_length ); // begin mysql_real_query(connection, begin, the_length ); // insert mysql_real_query(connection, insert into , the_length ); // NO commit is DONE, for test purpose //- END of My C code When I check the table, in an interaction session, my insertion is available! I mean a request like 'select... ' show the NEW insertion ! Why ? NO commit has been DONE! I have made several other try, like reading a config file (and a 'set-variable = autocommit=0' in it) But the probleme doesn't change Furthermore,the server has a global transaction-isolation option set to serializable. We tried to force the autocommit=0 through an init-file. The problem is still there ! The autocommit option seems to be an session command. Is there any way to set it for the whole server ? Thank you for your help Sincerely. -- Bernard CHAMBON IN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Storing DB on another server
Okay this may seem odd, but here goes. I have a client, they have a nice big Netware server, with lots of extra disk space. I know that Linux can access NetWare volumes, so is it possible to set up a small (read cheap) Linux box for the database server, and store the actual database files on the Netware server, I know this will result in a performance hit, but it saves them needing to install RAID5 SCSI on the Linux box, and they can use otherwise wasted space. The question is: will this work? Thanks Paul Paul Schmidt, Tricat Technologies Email: [EMAIL PROTECTED] Website: www.tricattechnologies.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL ODBC on Unix
Srinivasa T.N. wrote: Hi, The m/c on which I have my database is also Unix, and the machine from which I want to connect is also Unix. Instead of directly connecting to database, I want to use ODBC in between, so that, if tomorrow, my database changes, I should not change my program. Any idea about how to go about it?? (What I read from MyOODBC docs is that it is just a driver and not a program manager..I really don't know how to use ODBC on UNIX).. TIA.. There are two free Driver Managers; - unixODBC - iODBC I use unixODBC with great success. You can check it out at... http://www.unixodbc.org There is a mailing list there as well so you can get great support if you need it. Peter -- +--- | Data Architect | your data; how you want it | http://www.codebydesign.com +--- table - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Storing DB on another server
On Thu, Sep 13, 2001 at 12:56:10PM -0400, Paul Schmidt wrote: Okay this may seem odd, but here goes. I have a client, they have a nice big Netware server, with lots of extra disk space. I know that Linux can access NetWare volumes, so is it possible to set up a small (read cheap) Linux box for the database server, and store the actual database files on the Netware server, I know this will result in a performance hit, but it saves them needing to install RAID5 SCSI on the Linux box, and they can use otherwise wasted space. The question is: will this work? As long as the netware filesystem access in Linux is good (and I've heard it is), yes. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 161,891,983 queries (248/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql to oracle
Hi. Is anyone aware of any tool to convert a mysql database to an oracle database? thanks __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Size of DB
On Thursday 13 September 2001 15:36, Frank Fisher wrote: Ian Barwick wrote: In Windows you can probably do something involving the mouse and some icon-thingies, or maybe a simple DIR at the command line. Right-click on the MySQL database folder, selecting Properties. Under DOS, substitute your Unix command with DIR and look at the total at the end of the list, and use backslashes instead of forward slashes. And leaving out the command line switches. AFAIK the DOS DIR only shows totals in bytes (at least in NT4). Of course, you could do the icon-thingy with KDE, Gnome, etc. Yup, assuming the KDE, Gnome etc. user can read the database directory. Which means either the user is running an X-session as root (generally Not A Good Thing) or the directory rights have been changed to something other than a healthily paranoid 700. Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql to oracle
On Thu, Sep 13, 2001 at 10:16:35AM -0700, g g wrote: Hi. Is anyone aware of any tool to convert a mysql database to an oracle database? Oracle published one several months back. -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 MySQL 3.23.41-max: up 7 days, processed 162,007,342 queries (248/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql to oracle
On Thursday 13 September 2001 19:16, g g wrote: Hi. Is anyone aware of any tool to convert a mysql database to an oracle database? see: http://www.mysql.com/news/article-57.html Ian Barwick -- Ian Barwick - Developer - [EMAIL PROTECTED] akademie.de asp GmbH - http://www.akademie.de To query tables in a MySQL database is more fun than eating spam - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql to oracle
It's amazing what a search on google will turn up. First result when searching on convert mysql to oracle came up with: http://freshmeat.net/projects/m2o.pl/?highlight=m2o Nick - Original Message - From: g g [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 1:16 PM Subject: mysql to oracle Hi. Is anyone aware of any tool to convert a mysql database to an oracle database? thanks __ Terrorist Attacks on U.S. - How can you help? Donate cash, emergency relief information http://dailynews.yahoo.com/fc/US/Emergency_Information/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: MySQL getting backlogged
Have you also increased your master maxfiles as well? If not, you're not getting the number of file handles you expect you are. I'd also consider upgrading to 4.3, as it's much faster than 3.x. Try this -- sysctl -w kern.maxfiles=16424 Chris Bolt [EMAIL PROTECTED] wrote: Right now MySQL is getting really backlogged but I have no idea why. There are 169 concurrently running queries, and 153 of them are in show the status as Opening tables. A processlist looks something like this (formatted by Status, Seconds query has been running, and query): Opening tables 15 SELECT users.username, user... Opening tables 15 SELECT users.username, user... [...] What I don't get is why all those queries are at Opening tables. My table_cache is currently set to 2048. It's a FreeBSD 3.4-RELEASE box. $ sysctl kern.maxfilesperproc kern.maxfilesperproc: 16424 I just reloaded the processlist and now half of the queries are in closing tables state (with running times from 16 to 48 seconds) and the other half are in Opening tables state, with running times all at 1 second. Why are threads closing tables if the table cache isn't even full? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Installation problsm - Can't locate DBI.pm
I get the following error message when trying to run-all-test. Any help would be greatly appreciated. /usr/local/mysql/sql-bench # ./run-all-tests Can't locate DBI.pm in @INC (@INC contains: /opt/perl5/lib/5.00503/PA-RISC1.1 /opt/perl5/lib/5.00503 /opt/perl5/lib/site_perl/ 5.005/PA-RISC1.1 /opt/perl5/lib/site_perl/5.005 .) at ./run-all-tests line 36. BEGIN failed--compilation aborted at ./run-all-tests line 36. Thanks, Eric White - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Installation problsm - Can't locate DBI.pm
You need to install Perl DBI, Mysql and Data-Dumper modules On Thu 13 Sep 2001 21:12, OPEN.org System Administrator wrote: I get the following error message when trying to run-all-test. Any help would be greatly appreciated. /usr/local/mysql/sql-bench # ./run-all-tests Can't locate DBI.pm in @INC (@INC contains: /opt/perl5/lib/5.00503/PA-RISC1.1 /opt/perl5/lib/5.00503 /opt/perl5/lib/site_perl/ 5.005/PA-RISC1.1 /opt/perl5/lib/site_perl/5.005 .) at ./run-all-tests line 36. BEGIN failed--compilation aborted at ./run-all-tests line 36. Thanks, Eric White - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Roman Festchook Network Engineer ISP ORTA Polesye http://www.polesye.net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql-max 3.23.41 beta
Hello all, I had installed mysql-max 3.23.41 beta some time back. Now I was trying to get the binary , but cannot find it anywhere! Also, if I use the stable 3.23.41 do I make some installation etc. changes? Any advice will be greatly appreciated. Thanks, Sheena. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mysql on HPUX10.20
Hi... I downloaded the mysql v3.23.42 and I processed as the instruction in INTALL-BINARY file, but when I run mysqld, I got the following: # bin/mysqld 010913 15:49:43 Warning: setrlimit couldn't increase number of open files to more than 60 010913 15:49:43 Warning: Changed limits: max_connections: 50 table_cache: 64 bin/mysqld: Can't change dir to '/usr/local/src/mysql-3.23.42-source-bin/var/' (Errcode: 2) 010913 15:49:43 Aborting 010913 15:49:43 bin/mysqld: Shutdown Complete Does someone have some clue to solve this problem? Leandro. - Mensagem enviada pelo servidor de Webmail do CEFET-AL - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re:INNOBASE, autocommit = 0, and C API ( mysql_real_query )
Bernard, you could look at the source code of the mysql client. I think it is written in C or C++, and should show how you get the C API working. Unfortunately there is no global command to set all sessions to autocommit = 0. There has been discussion of adding such an option. Regards, Heikki http://www.innodb.com Hello, I am using Innobase with MySql version 3.23.39 under Linux and want to run WITHOUT autocommit mode (= autocommit=0 for using commit/roolback instructions) I have made a test whith 2 INTERACTIVE MySql sessions and this works fine. BUT, when I use the C API (mysql_real_query), it DOESN't work. I mean the autocommit=0 is NOT taken in account//-- My C code looks like : // set autocommit = 0 mysql_real_query(connection, set autocommit = 0, the_length );// begin mysql_real_query(connection, begin, the_length );// insert mysql_real_query(connection, insert into , the_length ); // NO commit is DONE, for test purpose//- END of My C code When I check the table, in an interaction session, my insertion is available! I mean a request like 'select... ' show the NEW insertion ! Why ? NO commit has been DONE! I have made several other try, like reading a config file (and a 'set-variable = autocommit=0' in it)But the probleme doesn't change Furthermore,the server has a global transaction-isolation option set to serializable. We tried to force the autocommit=0 through an init-file. The problem is still there ! The autocommit option seems to be an session command. Is there any way to set it for the whole server ?Thank you for your help Sincerely.-- Bernard CHAMBONIN2P3 / CNRS (Centre de Calcul de LYON) Tél : 04 72 69 42 18 http://www.in2p3.fr/CC - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mysql.sock error
I'm trying to start my first ever installation of mysql, so I've installed the tar.gz (3.23.42 on Linux) and I've run ./configure from /mysqlhome when I try to star the server I get the following message Can't connect ot local MySQL server through socket '/tmp/mysql.sock' When I do a find I can't find the mysql.sock file anywhere on my server. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: ANN: EMS MySQL Manager 0.91 released
database,sql,query,table Igor Brynskich wrote: Dear Sirs and Madams, Perhaps you should consider creating your own mail list for minor release announcements? We do this for Data Architect. I am sure that 90% of the people on this list received your last announcement so this one just seems redundant. Peter -- +--- | Data Architect | your data; how you want it | http://www.codebydesign.com +--- - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql.sock error
Just had this same error and fixed it...with help. You need to set a symlink to the /tmp ln -s /tmp/mysql var/lib/mysql/mysql.sock I may have this backwards..I'll send it from home when I get there. M;) -Original Message- FROM: Nick Torenvliet TO: Mysql (E-mail) DATE: Thu 9/13/01 13:58 SUBJECT: mysql.sock error I'm trying to start my first ever installation of mysql, so I've installed the tar.gz (3.23.42 on Linux) and I've run ./configure from /mysqlhome when I try to star the server I get the following message Can't connect ot local MySQL server through socket '/tmp/mysql.sock' When I do a find I can't find the mysql.sock file anywhere on my server. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
mySQL and LDAP
Is anyone using mySQL as an LDAP database backend? I'm looking into using LDAP as a means to keep a user directory (employee id, name, phone #, email, etc...). Unfortunately it seems that most of what I've found recommend using Berkeley DB or Gnu's GDBM. I don't want yet another database to learn and care for. Any suggestions and links would be greatly appreciated. Thanks regards, Ron - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Problem installing mySql on Mac OS X
I am having a few problems with the install. First off Mac OS X does not support the groupadd or useradd commands. Next the scripts/mysql_install_db fails and tells me to look for more i\ nformation in ./data logs, however in the data directory there are no log files\ . How-To-Repeat: Follow the instructions in the INSTALL-BINARY file. Fix: ??? Submitter-Id: submitter ID Originator:System Administrator Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: Installing on Mac OS X Severity: critical Priority: medium Category: mysql Class: support Release: mysql-3.23.42-apple-rhapso (Official MySQL binary) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: MySQL getting backlogged
Have you also increased your master maxfiles as well? If not, you're not getting the number of file handles you expect you are. I'd also consider upgrading to 4.3, as it's much faster than 3.x. Try this -- sysctl -w kern.maxfiles=16424 kern.maxfiles is already the same as .maxfilesperproc sql,query,database,table,select,stupid,spam,filter - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySQL/MyODBC and IMail
Has anyone had any experience using Mysql via MyODBC with Ipswitch's IMail mail server? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
uploading 200,000 x 5 rows
Hi all, mysql-team i have a very very complicated problem , hope to be clear. i have a file report (.txt) with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) without tabs or separation, i built a parser to split each field and works fine. After each sequential parsing (one book) i upload that info to the database but in 5 tables (verifying duplication, ids, etc): book author subject book-author (relationship) book-subject (relationship) When i reach the book number 11500 (aprox) mysql shows a deepth fall in performance , each insertion takes 30 segs or more, even with/without indexes. First i though that parsing takes longer, so i parsed the entire file and generated a java serialized file, then just inserted to db but was unsuccessfully , still the same performance. I have calculated the total number of rows for each table and is about 200,000 for books, 150,000 for authors , 130,000 for subjects and relations can have 250,000 each one. Any have an idea of how can i upload this information quickly ??? I'll really appreciate any advise. Carlos _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Rolling over on sequence number?
At 10:06 AM +0100 9/13/01, Robin Keech wrote: I have a requirement to use a sequence number table that I can reserve a block of sequences, (so auto increment is not suitable). This is my proposed table structure create table SEQNO ( SEQNO INT UNSIGNED NOT NULL DEFAULT 0, SESSION VARCHAR(10), LOCK_TIMEOUT TIMESTAMP); my blocks will go up in 100's, using SQL like update SEQNO set SEQNO = SEQNO + 100; My question is, is there any way to get the database to roll over the int value when it gets to 4,294,967,295? In my tests it goes upto the max value and stays there. I could do it programmatically, but it would be so much nicer if the database rolled over the value. for example 4,294,967,200 + 100 - 4 Why 4? You want the value mod 4,294,967,296? Write your update like this: update SEQNO set SEQNO = MOD(SEQNO + 100, 4294967296) Any ideas? I have read manual, and MySQL book regarding sequences, but could not find anything relevant. Thanks Robin -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
select from a join (a better/Quicker way??)
Huston we have a problem: table1: purchase_order (primary key) client_number buyer_name table2: client_number buyer_number buyer_name (all three combine for the primary key for table2) Query: select table1.purchase_order, table1.buyer_name, table2.buyer_number from table1 inner join table1 on (table1.buyer_name = table2.buyer_name); This works, it takes 4 seconds with a limit 50... but there is over 100,000 records in table1 and over 33,000 records in table2, so the inner join has to create a temp table with 100,000 X 33,000 records(??? is this right) to do it. = 133mins. Any info, pointers would be apreciated. Thanks Simon Buchanan Technical Director - New Media Communicationshttp://newmedia.co.nz Contact: 07 928-3701 mailto:[EMAIL PROTECTED] - Endless Loop: n., see Loop, Endless - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Query Even Possible in MySQL?
Hello, I have a query as follows: SELECT products.*, avg(ratings.rating) AS rating FROM products, ratings WHERE products.ref = ratings.product_ref GROUP BY products.ref ORDER BY rating In one sense this query works fine - it adds a new column called 'rating' (which is a dynamically calculated average of ratings given to a certain product) to my product results. My problem is that it only works if a rating has been given for a product. When a new product is added, it will have no ratings, and so it will not be returned in any of my queries. Is there any way to assign a default value to the 'rating' column if actual row(s) do not exist in the ratings table? I dont really want to have to make a dummy rating row just to trick it into working. Am i making sense? I hope so ;o) Jamie Burns. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Problem installing mySql on Mac OS X
This site has precompiled MySQL binaries for Mac OS X: http://www.entropy.ch/software/macosx/mysql/ It also has easy to follow installation instructions. You need to use your Users utility to add the MySQL user instead of the useradd and groupadd commands. Complete instructions are at the site. On Thursday, September 13, 2001, at 05:39 PM, [EMAIL PROTECTED] wrote: I am having a few problems with the install. First off Mac OS X does not support the groupadd or useradd commands. Next the scripts/mysql_install_db fails and tells me to look for more i\ nformation in ./data logs, however in the data directory there are no log files\ . How-To-Repeat: Follow the instructions in the INSTALL-BINARY file. Fix: ??? Submitter-Id: submitter ID Originator:System Administrator Organization: organization of PR author (multiple lines) MySQL support: none Synopsis: Installing on Mac OS X Severity: critical Priority: medium Category: mysql Class: support Release: mysql-3.23.42-apple-rhapso (Official MySQL binary) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail mysql-unsubscribe- [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Query Even Possible in MySQL?
Hi. Just use a LEFT JOIN and you will get a default of NULL whenever there is no entry in the rating table for a product_ref. Bye, Benjamin. On Fri, Sep 14, 2001 at 01:46:01AM +0100, [EMAIL PROTECTED] wrote: Hello, I have a query as follows: SELECT products.*, avg(ratings.rating) AS rating FROM products, ratings WHERE products.ref = ratings.product_ref GROUP BY products.ref ORDER BY rating In one sense this query works fine - it adds a new column called 'rating' (which is a dynamically calculated average of ratings given to a certain product) to my product results. My problem is that it only works if a rating has been given for a product. When a new product is added, it will have no ratings, and so it will not be returned in any of my queries. Is there any way to assign a default value to the 'rating' column if actual row(s) do not exist in the ratings table? I dont really want to have to make a dummy rating row just to trick it into working. Am i making sense? I hope so ;o) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using LEFT JOINS
OK, first of all, brilliant and well thought out. One problem, every one of my tables are EXACTLY the same. All I want to do is take the records from all 15 of the tables where the username(column) in them is what I say. When I try to do this, it gives me an error that says it's ambiguous. So I need to merge all the tables (and I dont mean the MERGE statement) together to create a sort of one big master table but it still knows what table each record is from. Hopefully that clears things up a bit, and I do appreciate that long yet excellent explanation on LEFT JOINS. Thank you and please, if you have an idea of what I mean, please tell me an answer. - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Loyd Goodbar [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:13 PM Subject: Re: using LEFT JOINS On Thu, 13 Sep 2001 07:03:03 -0500, Deryck Henson [EMAIL PROTECTED] wrote: Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com Say you want to see records from a header table that has 1 row per unique value (say, purchase orders), and a detail table that has multiple records per unique value in the header table (purchase order line items)... HEADER table (po_num is unique key): po_num,po_date,vendor 1001,'2001-01-01','ABC Corp' 1002,'2001-01-01','DEF Corp' 1003,'2001-01-01','GHI Corp' DETAIL table (po_num is foreign key to HEADER) po_num,po_line,qty,price_per 1001,10,1,1.00 1001,20,1,0.50 1002,10,3,4.50 1002,20,6,0.25 1002,30,1,5.00 Before we start: I work on at least 3 database systems on a daily basis: MySQL, Microsoft SQL server, and IBM's UDB. I primarily work with UDB and SQL server, so some items herein may or may not work correctly with MySQL. However, they ARE valid ANSI SQL statements. Now you want to know how much the total PO is. You can't just read the header file, it doesn't have prices. You must join the header and detail files. The sum query is shown far below, but please read through the join information first. In normal (ANSI) type joins, the first table you use in the FROM clause is the left or leftmost table, and other tables are to the right of it. Imagine your select statement on one long line, and you'll see the relationship between left and right tables. If you run this statement: select * from header join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50 1002,'2001-01-01','DEF Corp',1002,10,3,4.50 1002,'2001-01-01','DEF Corp',1002,20,6,0.25 1002,'2001-01-01','DEF Corp',1002,30,1,5.00 In this sample, you get as many header hits as there are detail hits. This is because each row in the detail is matched to a row in the header. There are 2 PO 1001s in the detail table, so the query engine must return those. It matches these to PO 1001 in the header table. Now, what happened to PO number 1003? A regular join only gives you results if all tables in the select statement return rows. The detail table for PO 1003 returned NULL rows, so it was discarded. If you want to see all the PO header records, regardless of whether detail records exist, you use a left join. The left join says, return data from the left table even if there is nothing to return in the right table. Given the above modified query: select * from header left join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50 1002,'2001-01-01','DEF Corp',1002,10,3,4.50 1002,'2001-01-01','DEF Corp',1002,20,6,0.25 1002,'2001-01-01','DEF Corp',1002,30,1,5.00 1003,'2001-01-01','GHI Corp',-,-,-,- (The dashes represent null values.) For practical purposes, LEFT and LEFT OUTER joins are the same. Now, you see there's a problem with PO 1003, it doesn't have any line items. You want a query to identify POs that don't have line items. You can use this query: select a.po_num,a.po_date,a.vendor from header a exception join detail b on (a.po_num=b.po_num) you will get: po_num,po_date,vendor 1003,'2001-01-01','GHI Corp' You could also use these alternate queries: select a.po_num,a.po_date,a.vendor from header a where a.po_num not in (select b.po_num from detail b where a.po_num=b.po_num) select a.po_num,a.po_date,a.vendor from header a, detail b where a.po_num=b.po_num and b.po_num is null Whenever you want columns from a specific table (not using *), you must specify which table to select from, if column names are identical in both tables. I could have used vendor
Re: using LEFT JOINS
At 7:55 PM -0500 9/13/01, Deryck Henson wrote: OK, first of all, brilliant and well thought out. One problem, every one of my tables are EXACTLY the same. All I want to do is take the records from all 15 of the tables where the username(column) in them is what I say. When I try to do this, it gives me an error that says it's ambiguous. So I need to merge all the tables (and I dont mean the MERGE statement) together to Actually, you probably do want a MERGE statement. Or rather, a MERGE table. Your reason for not using one is that it doesn't support INSERT. I asked what that had to do with anything, because when you want to update one of these fifteen tables, you'll clearly want to put the row back in the table from which it originally came, and therefore must know which table that is. (If you don't know, that means you can't distinguish them and then there's no reason to have 15 tables in the first place.) I'm still unclear why a MERGE table won't work. LEFT JOIN isn't what you want. create a sort of one big master table but it still knows what table each record is from. Hopefully that clears things up a bit, and I do appreciate that long yet excellent explanation on LEFT JOINS. Thank you and please, if you have an idea of what I mean, please tell me an answer. - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Loyd Goodbar [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:13 PM Subject: Re: using LEFT JOINS On Thu, 13 Sep 2001 07:03:03 -0500, Deryck Henson [EMAIL PROTECTED] wrote: Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com Say you want to see records from a header table that has 1 row per unique value (say, purchase orders), and a detail table that has multiple records per unique value in the header table (purchase order line items)... HEADER table (po_num is unique key): po_num,po_date,vendor 1001,'2001-01-01','ABC Corp' 1002,'2001-01-01','DEF Corp' 1003,'2001-01-01','GHI Corp' DETAIL table (po_num is foreign key to HEADER) po_num,po_line,qty,price_per 1001,10,1,1.00 1001,20,1,0.50 1002,10,3,4.50 1002,20,6,0.25 1002,30,1,5.00 Before we start: I work on at least 3 database systems on a daily basis: MySQL, Microsoft SQL server, and IBM's UDB. I primarily work with UDB and SQL server, so some items herein may or may not work correctly with MySQL. However, they ARE valid ANSI SQL statements. Now you want to know how much the total PO is. You can't just read the header file, it doesn't have prices. You must join the header and detail files. The sum query is shown far below, but please read through the join information first. In normal (ANSI) type joins, the first table you use in the FROM clause is the left or leftmost table, and other tables are to the right of it. Imagine your select statement on one long line, and you'll see the relationship between left and right tables. If you run this statement: select * from header join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50 1002,'2001-01-01','DEF Corp',1002,10,3,4.50 1002,'2001-01-01','DEF Corp',1002,20,6,0.25 1002,'2001-01-01','DEF Corp',1002,30,1,5.00 In this sample, you get as many header hits as there are detail hits. This is because each row in the detail is matched to a row in the header. There are 2 PO 1001s in the detail table, so the query engine must return those. It matches these to PO 1001 in the header table. Now, what happened to PO number 1003? A regular join only gives you results if all tables in the select statement return rows. The detail table for PO 1003 returned NULL rows, so it was discarded. If you want to see all the PO header records, regardless of whether detail records exist, you use a left join. The left join says, return data from the left table even if there is nothing to return in the right table. Given the above modified query: select * from header left join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50 1002,'2001-01-01','DEF Corp',1002,10,3,4.50 1002,'2001-01-01','DEF Corp',1002,20,6,0.25 1002,'2001-01-01','DEF Corp',1002,30,1,5.00 1003,'2001-01-01','GHI Corp',-,-,-,- (The dashes represent null values.) For practical purposes, LEFT and LEFT OUTER joins are the same. Now, you see there's a problem with PO 1003, it doesn't have any line items. You want a query to identify POs that don't have line items. You can use
Re: using LEFT JOINS
Dude, I AM going to be inserting data! - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 8:04 PM Subject: Re: using LEFT JOINS At 7:55 PM -0500 9/13/01, Deryck Henson wrote: OK, first of all, brilliant and well thought out. One problem, every one of my tables are EXACTLY the same. All I want to do is take the records from all 15 of the tables where the username(column) in them is what I say. When I try to do this, it gives me an error that says it's ambiguous. So I need to merge all the tables (and I dont mean the MERGE statement) together to Actually, you probably do want a MERGE statement. Or rather, a MERGE table. Your reason for not using one is that it doesn't support INSERT. I asked what that had to do with anything, because when you want to update one of these fifteen tables, you'll clearly want to put the row back in the table from which it originally came, and therefore must know which table that is. (If you don't know, that means you can't distinguish them and then there's no reason to have 15 tables in the first place.) I'm still unclear why a MERGE table won't work. LEFT JOIN isn't what you want. create a sort of one big master table but it still knows what table each record is from. Hopefully that clears things up a bit, and I do appreciate that long yet excellent explanation on LEFT JOINS. Thank you and please, if you have an idea of what I mean, please tell me an answer. - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Loyd Goodbar [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:13 PM Subject: Re: using LEFT JOINS On Thu, 13 Sep 2001 07:03:03 -0500, Deryck Henson [EMAIL PROTECTED] wrote: Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com Say you want to see records from a header table that has 1 row per unique value (say, purchase orders), and a detail table that has multiple records per unique value in the header table (purchase order line items)... HEADER table (po_num is unique key): po_num,po_date,vendor 1001,'2001-01-01','ABC Corp' 1002,'2001-01-01','DEF Corp' 1003,'2001-01-01','GHI Corp' DETAIL table (po_num is foreign key to HEADER) po_num,po_line,qty,price_per 1001,10,1,1.00 1001,20,1,0.50 1002,10,3,4.50 1002,20,6,0.25 1002,30,1,5.00 Before we start: I work on at least 3 database systems on a daily basis: MySQL, Microsoft SQL server, and IBM's UDB. I primarily work with UDB and SQL server, so some items herein may or may not work correctly with MySQL. However, they ARE valid ANSI SQL statements. Now you want to know how much the total PO is. You can't just read the header file, it doesn't have prices. You must join the header and detail files. The sum query is shown far below, but please read through the join information first. In normal (ANSI) type joins, the first table you use in the FROM clause is the left or leftmost table, and other tables are to the right of it. Imagine your select statement on one long line, and you'll see the relationship between left and right tables. If you run this statement: select * from header join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50 1002,'2001-01-01','DEF Corp',1002,10,3,4.50 1002,'2001-01-01','DEF Corp',1002,20,6,0.25 1002,'2001-01-01','DEF Corp',1002,30,1,5.00 In this sample, you get as many header hits as there are detail hits. This is because each row in the detail is matched to a row in the header. There are 2 PO 1001s in the detail table, so the query engine must return those. It matches these to PO 1001 in the header table. Now, what happened to PO number 1003? A regular join only gives you results if all tables in the select statement return rows. The detail table for PO 1003 returned NULL rows, so it was discarded. If you want to see all the PO header records, regardless of whether detail records exist, you use a left join. The left join says, return data from the left table even if there is nothing to return in the right table. Given the above modified query: select * from header left join detail on (header.po_num=detail.po_num) you will get po_num,po_date,vendor,po_num,po_line,qty,price_per 1001,'2001-01-01','ABC Corp',1001,10,1,1.00 1001,'2001-01-01','ABC Corp',1001,20,1,0.50
Re: mysql.sock error
the sock file is in the /usr/lib/mysql dir i created a symlink of the file from there to /tmp On Thu, 2001-09-13 at 14:32, Nick Torenvliet wrote: I'm trying to start my first ever installation of mysql, so I've installed the tar.gz (3.23.42 on Linux) and I've run ./configure from /mysqlhome when I try to star the server I get the following message Can't connect ot local MySQL server through socket '/tmp/mysql.sock' When I do a find I can't find the mysql.sock file anywhere on my server. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using LEFT JOINS
Dude, I AM going to be inserting data! Yeah, I know. And I said below, in effect, so what? I'm still waiting for an answer what that has to do with anything. When you insert, you insert back into the original table, not the merge table. So what's the problem? - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 8:04 PM Subject: Re: using LEFT JOINS At 7:55 PM -0500 9/13/01, Deryck Henson wrote: OK, first of all, brilliant and well thought out. One problem, every one of my tables are EXACTLY the same. All I want to do is take the records from all 15 of the tables where the username(column) in them is what I say. When I try to do this, it gives me an error that says it's ambiguous. So I need to merge all the tables (and I dont mean the MERGE statement) together to Actually, you probably do want a MERGE statement. Or rather, a MERGE table. Your reason for not using one is that it doesn't support INSERT. I asked what that had to do with anything, because when you want to update one of these fifteen tables, you'll clearly want to put the row back in the table from which it originally came, and therefore must know which table that is. (If you don't know, that means you can't distinguish them and then there's no reason to have 15 tables in the first place.) I'm still unclear why a MERGE table won't work. LEFT JOIN isn't what you want. create a sort of one big master table but it still knows what table each record is from. Hopefully that clears things up a bit, and I do appreciate that long yet excellent explanation on LEFT JOINS. Thank you and please, if you have an idea of what I mean, please tell me an answer. - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Loyd Goodbar [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 6:13 PM Subject: Re: using LEFT JOINS -- Paul DuBois, [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? --- Rodney Broom Programmer: Desert.Net - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql.sock error
Nick Torenvliet wrote: ...I've run ./configure Did you run 'make'? --- Rodney Broom Programmer: Desert.Net Keywords: sql, spam-off - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using LEFT JOINS
Sure... SQL Query: SELECT * FROM asp, c, cpp, java, javascript, coldfusion, xml, pascal, vb, perl, cgi, php, assembly, python where user = 'user' (gets an ambiguous error and I know why) Database structure (all tables the same) Tables- ASP, C, CPP, Java, Javascript, Coldfusion, XML, VB, Pascal, Perl, CGI, PHP, Assembly, Python, dHTML Columns- user text, email text, homepage text, code longtext, tutorial longtext, codetype enum ('file', 'file-sourcecode', 'tutorial', 'file-tutorial', 'sourcecode'), file_path text, title text, description text, codeid text, rating int, votes int, views bigint, date_created datetime, level enum ('all', 'novice', 'intermediate', 'advanced') - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Gerald Clark [EMAIL PROTECTED] To: Deryck Henson [EMAIL PROTECTED] Cc: MySQL [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 10:00 AM Subject: Re: using LEFT JOINS Show us your table definitions, what you tried, and what you think didn't work. Deryck Henson wrote: Well, before I asked which joins to use and now I want to know how to use the LEFT JOINS. They would be quite useful on my site right now. Any help is appreaciated and dont say to read the manual cause Ive tried that and it doesnt give a good enough explanation. This will be very helpful when displaying my database tables. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php -- Gerald L. Clark [EMAIL PROTECTED] - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: mysql.sock error
Well lets get it runningG Basic install nothing fancy ./configure make make install to fix your error create a symlink su to root ln -s var/lib/mysql/mysql.sock /tmp/mysql.sock Cheers M;) -Original Message- From: Nick Torenvliet [mailto:[EMAIL PROTECTED]] Sent: Thursday, September 13, 2001 1:32 PM To: Mysql (E-mail) Subject: mysql.sock error I'm trying to start my first ever installation of mysql, so I've installed the tar.gz (3.23.42 on Linux) and I've run ./configure from /mysqlhome when I try to star the server I get the following message Can't connect ot local MySQL server through socket '/tmp/mysql.sock' When I do a find I can't find the mysql.sock file anywhere on my server. Any suggestions? - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Mysql on HPUX10.20
The directory /usr/local/src/mysql-3.23.42-source-bin/var/ does not exist on your system. You probably do not really want your database files stored in such a cryptic directory name. You probably need to use the --basedir or --datadir parameters to mysqld to tell it where your databases really are. It would also be a good idea for whoever built the binary package to use more intelligent default path names for those values. - Original Message - From: [EMAIL PROTECTED] Hi... I downloaded the mysql v3.23.42 and I processed as the instruction in INTALL-BINARY file, but when I run mysqld, I got the following: # bin/mysqld 010913 15:49:43 Warning: setrlimit couldn't increase number of open files to more than 60 010913 15:49:43 Warning: Changed limits: max_connections: 50 table_cache: 64 bin/mysqld: Can't change dir to '/usr/local/src/mysql-3.23.42-source-bin/var/' (Errcode: 2) 010913 15:49:43 Aborting 010913 15:49:43 bin/mysqld: Shutdown Complete Does someone have some clue to solve this problem? Leandro. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql install failed on FreeBSD!!
What version of FreeBSD do you have installed? Was it upgraded from an earlier version? If so, was the ports collection upgraded as well? This sounds like you have incompatible versions of the system and the ports collection installed. - Original Message - From: Siva Namburi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Wednesday, September 12, 2001 3:48 PM Subject: mysql install failed on FreeBSD!! Hi, I am pretty new to mysql and started installing it on freeBSD from usr/ports/. I got the following error. Can somebody help me why it is failing. thanks, siva === Installing for mysql-server-3.23.36 === mysql-server-3.23.36 depends on executable: mysql - not found ===Verifying install for mysql in /usr/ports/databases/mysql323-client === Building for mysql-client-3.23.36 make all-recursive Making all in include Making all in libmysql /bin/sh ../libtool --mode=link cc -DDBUG_OFF -O -pipe -o libmysqlclient.la -rp ath .iso.org.dod.internet.private.enterprises.clickarray/lib/mysql -version-info 10:0:0 libmysql.lo net.lo violite.lo password.lo get_password.lo errmsg.lo my_ init.lo my_static.lo my_malloc.lo my_realloc.lo my_creat e.lo my_delete.lo mf_tempfile.lo my_open.lo mf_casecnv.lo my _read.lo my_write.lo errors.lo my_error.lo my_getwd.lo my_div.l o mf_pack.lo my_messnc.lo mf_dirname.lo mf_fn_ext.lo mf_wcomp.lo typelib.lo safemalloc.lo my_alloc.lo mf_format.lo mf_path.lo mf_unixpath.lo my_fopen.lo my_fstream.lo mf_loadpath.lo my_pthread.lo my_thr_init.lo thr_mutex.lo mulalloc.lo string.lo default.lo my_compress.lo array.lo my_once.lo list.lo my_net.lo cha rset.lo hash.lo getopt.lo getopt1.lo getvar.lo my_lib.lo strmov.lo strxmov.lo st rnmov.lo strmake.lo strend.lo strnlen.lo strfill.lo is_prefix. lo int2str.lo str2int.lo strinstr.lo strcont.lo strcend.lo bchange.lo bmove.lo bmove_upp.lo longlon g2str.lostrtoull.lo strtoll.lo llstr.lo ctype.lo dbug.lo -lz -lcrypt -lm libtool: link: only absolute run-paths are allowed *** Error code 1 Stop in /usr/ports/databases/mysql323-client/work/mysql-3.23.36/libmysql. *** Error code 1 Stop in /usr/ports/databases/mysql323-client/work/mysql-3.23.36. *** Error code 1 Stop in /usr/ports/databases/mysql323-client. *** Error code 1 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: mysql install failed on FreeBSD!!
Hi Siva, I know I'm about to get in trouble with the FreeBSD people, but here goes: I'm not a real big fan of FreeBSD's /usr/ports/ thing. My suggestion would be to download the source in a TAR ball, unpack it, run: % configure % make % make install If that doesn't succeed, hollar back with the errors. I expect that this will be much easier, and far less prone to problems. --- Rodney Broom Programmer: Desert.Net SpamKey: sql - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Pro-Active Response to a National Tragedy
Dear Educator, Columbine (Littleton, CO)Jonesboro, ARWest Paducah, KYPearl, MS... Immediately, you know why these places are linked together. The names of these schools are forever burned into our memories and the national conscience... Are you concerned about the rising number of incidents of violence in our schools? Would you like to lower the probabilty of such an incident from occuring at your school? Would you like to help our children better cope with the pressures of modern life - helping to prevent issues such as substance abuse, teen pregnancy and suicide? If you answered yes to any of these questions - then read on. The Resilient Kids CD ROM Program is used in schools, churches, and homes to teach optimism, coping skills, and personal mastery and helps to prevent depression in young people. This program, launched in April 2000, is now in over 1,000 primary and secondary schools across Australia. With its light-hearted, interactive approach, the Resilient Kids CD ROM Program strives to achieve the following objectives: - Reducing the incidence of suicide in the young by teaching primary prevention strategies. - Teaching children how to evaluate their automatic thoughts and to re-look at failures or set-backs as challenges to be reacted to with activity and hope. - Teaching parents how to help their children achieve self-esteem through personal mastery. - Creating a sense of belonging through parents and school working together for the childs well being. - Equipping older teenagers with ideas and skills for managing the transition from adolescence to adulthood and handling typical stressful situations. - Taking a serious problem and making it easy to communicate and fun to learn through interactivity. The program's success is due to: - A clear format for instant implementation - Colorful, fun-filled interactive material suitable for all reading ages and learning abilities - Practicality. It will not burden crowded curriculums or overload teachers - A built-in, prepared parent education component Resilience Training - the most effective way for both primary and secondary schools to address the serious social problems of depression, suicide and substance abuse. Take advantage of this unique program and teach your students how to bounce back positively. Resilient Kids is a positive, effective and enjoyable prevention program for your school community. Please visit us at http://www.resilientkids.net or call for more information about ordering online, our parent/teacher resiliency seminars, or our newly released parent CD package. To order, simply go to http://www.resilientkids.net and order online. You can email [EMAIL PROTECTED] if you have any questions or to request an order form. To reach us by phone, call (501) 267-3710 or fax (501) 267-1283. We accept all major credit cards. Products may be sold within a school (e.g. Parents) at cost, but not outside the school. All Programs may be ordered on approval at no charge, to be paid for or returned (customer to pay return freight) within 2 school weeks in new condition. If not returned, full purchase price will be due and payable. ** Under Bill s.1618 TITLE III passed by the 105th US Congress this letter cannot be considered spam as long as the sender includes contact information and a method of removal. You have received this email as you are believed to be in the educational field. If you feel you received this email in error, please visit http://www.resilientkids.net/remove to have your email address permanently removed from our database. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Question about LIKE statements and escaped characters
At 07:55 AM 9/13/2001 -0500, Michael Sims wrote: Hi, I posted this query to the list via the newsgroup mailing.database.mysql a few days ago but didn't receive any replies. My apologies to the list...someone in alt.php was kind enough to point out that my question is answered in section 6.3.2.1 of the MySQL manual. Sorry for wasting time.. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: using LEFT JOINS
Actually, version 4.0 will. I'll have to wait til then but for now, I'll use you other advice. Thanx - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Loyd Goodbar [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 10:22 PM Subject: Re: using LEFT JOINS Duh on me... what about this (may not work with MySQL, does it support unions yet?) insert into mastertable (user,name,info) (select * from one where user='loyd' union select * from two where user='loyd' union ...) repeat to table 15. Since 15 tables have the same structure, this will work. Loyd On Thu, 13 Sep 2001 20:19:14 -0500, Loyd Goodbar [EMAIL PROTECTED] wrote: On Thu, 13 Sep 2001 19:55:11 -0500, Deryck Henson [EMAIL PROTECTED] wrote: OK, first of all, brilliant and well thought out. One problem, every one of my tables are EXACTLY the same. All I want to do is take the records from all 15 of the tables where the username(column) in them is what I say. When I try to do this, it gives me an error that says it's ambiguous. So I need to merge all the tables (and I dont mean the MERGE statement) together to create a sort of one big master table but it still knows what table each record is from. Hopefully that clears things up a bit, and I do appreciate that long yet excellent explanation on LEFT JOINS. Thank you and please, if you have an idea of what I mean, please tell me an answer. - Deryck H - http://www.comp-u-exchange.com Ouch. I don't know off the top to achieve that easily, and I'll explain why... SQL does not stack results, which is what I believe you're asking for. Let me see... I have table ONE: user,name,info loyd,Loyd,hello joe,Joe,hi and TWO: user,name,info loyd,Loyd G,hello2 joe,Joe P,hi2 So what I gather you want is this result (query on loyd): user,name,info loyd,Loyd,hello loyd,Loyd G,hello2 The problem is SQL won't stack the results like the above, but will do something like this: user,name,info,user,name,info loyd,Loyd,hello,loyd,Loyd G,hello2 But what you're running into is a query like this select * from one left join two on (one.user=two.user) left join three on (one.user=three.user) ... where one.user='loyd' That would give the above result, except the field user, name, and info are defined more than once. SQL doesn't know WHICH user, name, and info you want since they're defined multiple times. This leads to the ambiguous column error. You could do something like select one.user as user1, one.name as name1, one.info as info1, two.user as user2 ... but that won't stack the results like you expect. The only clean way I know is to do this programmatically with multiple statements... insert into temptable (user,name,info) (select user,name,info from one where user='loyd') insert into temptable (user,name,info) (select user,name,info from two where user='loyd') and so on, then select * from temptable That will give you stacked results, but is not a single SQL statement. I truly believe that is a very complex selection (especially over 15 tables!), and is one I haven't needed to do yet. One good thing about the above is you can insert arbitrary data, such as this... insert into temptable (user,name,info,comment) (select user,name,info,'from table one' from one where user='loyd') Devshed (www.devshed.com) I think had a MySQL article recently on creating SQL crosstabs. However, crosstabs give summary information, not detail. How often would the master table need to be (re)created? Are you wanting a realtime table, or an occasional reporting table? If this is only occasionally, it would be a good candidate for stored procedures, if/when MySQL supported them. If this is a realtime table, your only real hope (currently unsupported with MySQL) is a trigger. (A program that acts on data as it is inserted, updated, or deleted from a table. It is called by the database engine without requiring user intervention. In this case, an after-insert trigger could copy the data from the one...fifteen table to the master table automatically.) I hope I understood your request, and maybe the above will lead you in the right direction. Loyd -- How much would you pay for your life? More than I would take to give it up. [EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/ -- How much would you pay for your life? More than I would take to give it up. [EMAIL PROTECTED] ICQ#504581 http://lgoodbar2.pointclark.net/ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual)
Symlinks
Setup mysql a few months back as a backend to radius. Welp, one thing that caught my attention last week was disk space. I have mysql going to /usr/local/var (default for mysql I believe) At any rate when this machine was setup it was given a 15GB /var partition and just over 1GB for /usr and now well its running low on /usr. What I'd like to do is move mysql radius db to say /var/mysql and symlink /usr/local/var to /var/mysql. Will a symlink cause mysql grief? I'd like to move the current database and continue to log to it if that is possible as well. This is under BSD/OS 4.1 and mysql 3.23.38 Thanks for any info. Keith - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Fulltext indexing libraries (perl/C/C++)
Hello [ It seems the post didn't make it through the first time ] While programming a journal in perl/axkit I realize that the problems of both creating useful indexes for searching content efficiently and parse user input and create the right sql queries from it are sooo common that there *must* be some good library already. :-) So I headed over to CPAN, but didn't really find what I was looking for. It should create indexes that are efficiently searchable in mysql, i.e. only select ... where .. like abcd% queries, not %abc%. Allow to search for word parts (i.e. find fulltext when entering text). Allow for multiple form fields (i.e. one field for title words, one for author names, etc.) at once. Preferably allow for some sort of query rules (AND/NOT/OR or something). Preferably do some relevance sorting. Preferably allow to hook some numbers (link or access counts etc) into the relevance sorting. I think there are 3 tough parts which are needed: 1. creation of sophisticated index structures (inverted indexes) 2. somehow recognize sub-word boundaries to split words on. Maybe use some form of thesaurus? Or syllables? (I suspect it should be the same rules as for splitting words on line boundaries) 3. user input parser / query creator Why not: - use mysql's fulltext indexes? Because I think that currently they are too limited (i.e. see user comments about them www.mysql.com/doc/) (should be better in mysql-4, I read, but we need it in a few weeks already...). And they are also not supported in Innodb which we want to use. - use indexing robots? Because we work with XML documents, and would like to both keep the index up to date immediately, as well as split the XML contents into several parts (i.e. there's a title, byline, etcetc, which should be searchable or weigted differently). We want a *library*, not a finished product. There's Lucene (www.lucene.com) in Java that I think does exactly what I want. Anyone who helps me port that to perl or C(++)/perl-bindings (-; ? (It should be ready in a few weeks, and it's about 500k source code :-(). (Something in C/C++ that would be loaded as UDF or so would be nice too, but as I understand (from recent discussion about stored procedures) it's not possible since these UDF's would have to start other queries (i.e. to insert each word fragment into an index table).) Like Daniel Gardner has pointed out to me, one could maybe use Search::InvertedIndex as a basis and complement it with Lingua::Stem (only english) or Text::German (german) (both seem to be quite imperfect tough) or with some word list processing. (I don't understand Search::InvertedIndex enough yet.) I think it would still be much work. Has someone finished something like this? More info about mysql4? Thx Christian. - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: high speed insertion required
Hi Mark: insert delayed helps little. I think there are two reasons. 1. One reason insert delayed speeding up insertion is that the client will return immediately after it issues a insert delayed query. I think all those inserts must be stored in a delayed queue and will be executed whenever it is possible or necessary. But since the delayed queue can't hold too many inserts and my program inserts data at a steady and high speed and will run for a long period of time (couple of days or maybe weeks), the queue will be full inevitably. And after the queue is full, I think no inserts can be delayed any longer. At that time, the insertion speed of my program will low down to the normal speed as insertion without delayed option chosen. 2. Another reason insert delayed speeding up insertion is that inserts from many clients are bundled together and written in one block. But on my server there are no other clients. So the insertion of my program can't be accelerated in this way neither. Disable or remove indexes. This sometimes speeds it up tremendously depending on your indexes. Well, index... I do have some indexes in the tables, but I can't give them up because those tables will be accessed at any time during the insertion. Is the hash table in the same server/database? The hash tables are in the same database. Well, I think maybe I should face the reality, maybe my program has already tried its best, and so have we(us? hehe, poor english :). Any way, thanks for your help. From: Mark Rissmann [EMAIL PROTECTED] To: Z Julian [EMAIL PROTECTED] Subject: Re: high speed insertion required Date: Thu, 13 Sep 2001 11:14:26 -0700 I use Delphi and am having a similar problem. But here are some things that can be done (that I haven't tried). INSERT DELAY -- this is supposed to help because my SQL will recognize a group of inserts into a table and queue them. This should be faster. You'll have to experiment on how long you allow the queue to build. Disable or remove indexes. This sometimes speeds it up tremendously depending on your indexes. Is the hash table in the same server/database? Please post your findings to the listserver. Let me know if this works or if you have any more questions. Mark Rissmann -Drifting Sands LLC - Original Message - From: Z Julian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, September 13, 2001 1:24 AM Subject: Re: high speed insertion required Hi: Thanks for your reply. :) I write that program in C. I get data from a mysql heap table which is always full of records. That's why I use queries like insert into table1 select from table2 to insert data. Obviously table2 is the heap table I mentioned above. I use that program to do some log jobs. Someone tells me to use insert HIGH_PRIORIRY to speed up the insertion, but I think that helps little since there are no other database clients running on my machine except the one which fills the heap table for my program. Do you have any suggestion? Please mail. Thank you and have a nice day! :) From: Mark Rissmann [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: high speed insertion required Date: Thu, 13 Sep 2001 01:02:31 -0700 Hello, What development tool are you programming in? Where are you getting these records from? Have you received any good tips? Mark Rissmann -Drifting Sands LLC I do not fear computers. I fear the lack of them. - Isaac Asimov _ Äú¿ÉÒÔÔÚ MSN Hotmail Õ¾µã http://www.hotmail.com/cn Ãâ·ÑÊÕ·¢µç×ÓÓʼþ - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Failure in downloading MySQLGUI
I am not able to download WIN 32 static binary of MySQLGUI 1.7.5-2 from http://www.mysql.com/downloads/gui-mysqlgui.html What is actually happening is that on downloading, the zipped file is not opening through Winzip and a message is being shown that the downloaded ziiped file is not a valid archive . Please help me as soon as possible regarding this. Reply to: [EMAIL PROTECTED] . - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
MySql connection problem
I am a new one in database area. Hopefully someone can help me or give me clue to solve the problem. I have already run mySql in c:\winnt\System32\cmd.exe, and create a database (table test1) in c:\mysql\data\test directory. The classpath I setup in control system environment, C:\jdk1.3.0_02; c:\jdbc\mm.mysql.jdbc-1.2c\mysql_comp.jar; c:\jdbc\mm.mysql.jdbc-1.2c\mysql_uncomp.jar; I try to test a simple java code with JBuilder, it doesn't work. The code: import java.sql.*; public class TestQuery { public TestQuery() { } public static void main(String args[]) { String url=(jdbc:mysql://localhost/test?user=pannyxpassword=654321); Connection con; String query = (select * from test1;); Statement stmt; try { Class.forName(org.gjt.mm.mysql.Driver).newInstance(); System.out.println(ok); } catch(java.lang.ClassNotFoundException e) { System.err.println(classnot fund exception: +e.getMessage()); } catch(Exception ex) { System.err.println(other exception: +ex.getMessage()); } try { System.out.println(Try to connect...); con = DriverManager.getConnection(url, pennyx, 654321); System.out.println(Connect!); stmt = con.createStatement(); ResultSet result = stmt.executeQuery(query); while (result.next()) { String name=result.getString(1)+ + result.getString(2); System.out.println(name); } stmt.close(); con.close(); } catch(SQLException ex) { System.out.println(SQLEXCEPTION: + ex.getMessage()); System.out.println(SQLState: + ex.getSQLState()); System.out.println(VendorError: + ex.getErrorCode()); } } } I got the following message: C:\Program Files\jdk1.3\bin\javaw -classpath C:\Documents and Settings\pennyx.ALNMEL\jbproject\untitled7\classes;C:\Program Files\jdk1.3\demo\jfc\Java2D\Java2Demo.jar;C:\Program Files\jdk1.3\jre\lib\i18n.jar;C:\Program Files\jdk1.3\jre\lib\jaws.jar;C:\Program Files\jdk1.3\jre\lib\rt.jar;C:\Program Files\jdk1.3\jre\lib\sunrsasign.jar;C:\Program Files\jdk1.3\lib\dt.jar;C:\Program Files\jdk1.3\lib\tools.jar -Xdebug -Xnoagent -Djava.compiler=NONE -Xrunjdwp:transport=dt_shmem,address=javadebug,suspend=y TestQuery Java HotSpot(TM) Client VM warning: Setting of property java.compiler is ignored ok Try to connect... java.lang.InstantiationError: org/gjt/mm/mysql/Connection at org.gjt.mm.mysql.Driver.connect(Driver.java:126) at java.sql.DriverManager.getConnection(DriverManager.java:517) at java.sql.DriverManager.getConnection(DriverManager.java:177) at TestQuery.main(TestQuery.java:32) I try to many times. Would you like to tell me how to solve the problem? Thanks. penny - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Original Message Follows From: Rodney Broom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: uploading 200,000 x 5 rows Date: Thu, 13 Sep 2001 18:42:22 -0700 Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. thanx a lot ;) ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? Yeah indeed, imagine an old bibliographical card, well i have a file with 200,000 consecutive cards. After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? I have a file something similar to: --- Classification: QAT67.0 Title: History of America System key: tyup Authors: Broom, Rodney. Proal, Carlos Subjects: History, Social Sciences ...other fields Classification: QE56.0 Title: Mysql System key: iuds Authors: Broom, Rodney. Dubois, Paul Subjects: Computer Science, History ...other fields ...200,000 records like these After each parsing (just reading one record) i have a java object containing: Book information: title, classif, systemkey, etc Book's authors: array of [author1,author2] Book's subjects: array of [subject1,subject2] i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? when i begin the whole process i open a connection to the DB by java using localhost (i work on the same machine). ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? the first one, i need to split into five tables. (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? yep let me explain, i have a kind of do-while statement like these: open connection to db while (still records in the file) { parse one record insert book information into table book (classif, title, year, etc) for(each author (current book)) { if author currently in db (through a select) get authorID else authorID= insert author into table author (id authorname) insert into table book_author (authorID, classif) - relationship } ...subjects with similar for that authors } close connection to db thanx again _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Trouble with SELECT * FROM...
OK, well, as you all know, I am using mysql only for ASP Connectivity. Sooo, this is my question: Every time I try to use this statement to open a table, it gives me an error:: statement used- rs.open SELECT username, codeid FROM user_votes where username = 'user', dbconn if rs.fields(codeid) = codeid then response.redirect show.asp?response=Youve%20already%20votedcodeid=codeidcategory=cate gory else error returned- error '80020009' Exception occurred. any help? The fields 'CodeID' has a Text DATATYPE. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: uploading 200,000 x 5 rows
Original Message Follows From: Rodney Broom [EMAIL PROTECTED] To: [EMAIL PROTECTED] Subject: Re: uploading 200,000 x 5 rows Date: Thu, 13 Sep 2001 18:42:22 -0700 Hi Carlos, I'm glad to help, but I'd like to clarify several things, first. thanx a lot ;) ...with about 200,000 data about books (title, clasif, year, publisher, authors, subjects, etc etc) So you have a single file with data for multiple tables? Yeah indeed, imagine an old bibliographical card, well i have a file with 200,000 consecutive cards. After each sequential parsing (one book) What defines one book? One file, several records from one or more files, or one record from a single file? I have a file something similar to: --- Classification: QAT67.0 Title: History of America System key: tyup Authors: Broom, Rodney. Proal, Carlos Subjects: History, Social Sciences ...other fields Classification: QE56.0 Title: Mysql System key: iuds Authors: Broom, Rodney. Dubois, Paul Subjects: Computer Science, History ...other fields ...200,000 records like these After each parsing (just reading one record) i have a java object containing: Book information: title, classif, systemkey, etc Book's authors: array of [author1,author2] Book's subjects: array of [subject1,subject2] i upload that info to the database You upload? How is this done, exactly? Do you mean that you run mysql from one machine with the host set for another machine? when i begin the whole process i open a connection to the DB by java using localhost (i work on the same machine). ...but in 5 tables... So you've split your data into sets that need to get loaded into five destination tables, or you upload five sets of data? the first one, i need to split into five tables. (verifying duplication, ids, etc): Does this mean that you are checking for duplicates before loading into MySQL? yep let me explain, i have a kind of do-while statement like these: open connection to db while (still records in the file) { parse one record insert book information into table book (classif, title, year, etc) for(each author (current book)) { if author currently in db (through a select) get authorID else authorID= insert author into table author (id authorname) insert into table book_author (authorID, classif) - relationship } ...subjects with similar for that authors } close connection to db thanx again _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php _ Get your FREE download of MSN Explorer at http://explorer.msn.com/intl.asp - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Now...I asked LEFT JOIN with WHERE, now without
This relates to the earlier posts I made about left joins. I was shown how to do it with a where clause, but now how do I do it WITHOUT one? Like SELECT * FROM basetable left join 2ndtable on (basetable.*=2ndtable.*) Something like that... I need help with this as you can see. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Now...I asked LEFT JOIN with WHERE, now without
Just to join ALL the records from ALL 15 tables together in one big blob of data and have there be a something that tells me which table each record comes from. - Deryck H - http://www.comp-u-exchange.com - Original Message - From: Deryck Henson [EMAIL PROTECTED] To: MySQL [EMAIL PROTECTED] Sent: Friday, September 14, 2001 1:20 AM Subject: Now...I asked LEFT JOIN with WHERE, now without This relates to the earlier posts I made about left joins. I was shown how to do it with a where clause, but now how do I do it WITHOUT one? Like SELECT * FROM basetable left join 2ndtable on (basetable.*=2ndtable.*) Something like that... I need help with this as you can see. - Deryck H - http://www.comp-u-exchange.com - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php