Number pickup with parallel updating?
Hi! I have a small problem: a have a database containing three fields number, timestamp, used i allready inserted into the number fileds about 50.000 random and unique numbers - timestamp and used are empty With my perl script i have to do the following: look into the database for the first entry with having timestamp and used empty then updating the timestamp field with the actual date. My script then in addtion presents the number to the customer via webinterface. the next time i run the script the same procedure must be repeated, so finding the first empty entry (empty timestamp field) and then updating this filed with a timestamp. How can i realize this with using perl ? Any advice? How to connect to database using perl i allready know but iam trying to find out how manage this small problem the most easy way... Thanks in advance Sascha sql,query - 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: Number pickup with parallel updating? with script!
Howdy, how can I add an extra field to an already created table in sql ?
Andrew rich
-Original Message-
From: Sascha Kettner [mailto:[EMAIL PROTECTED]]
Sent: Sunday, 21 April 2002 9:29 PM
To: [EMAIL PROTECTED]
Subject: Number pickup with parallel updating? with script!
Hi!
I have a small problem:
a habe a database containing three fields
number, timestamp, used
i allready inserted into the number fileds about 50.000 random and
unique numbers - timestamp and used are empty
With my perl script i have to do the following:
look into the database for the first entry with having timestamp and
used empty
then updating the timestamp field with the actual date.
My script then in addtion presents the number to the customer via
webinterface.
the next time i run the script the same procedure must be repeated, so
finding the first empty entry (empty timestamp field) and then updating
this filed with a timestamp.
How can i realize this with using perl ? Any advice? How to connect to
database using perl i allready know but iam trying to find out how
manage this small problem the most easy way...
Thanks in advance
Sascha
--
ps: here is the script i´ve done so far, but it is not working!
Use DBI();
sub dojob {
# Now retrieve data from the table.
my $dbh = DBI-connect(DBI:mysql:database=dbname;host=hostname,
user, pass,
{'RaiseError' = 1});
my $sth = $dbh-prepare('select * from smartaktion where activated LIKE
0 Limit 1); #(schould select 1st entry in db with
activated=null#
$sth-execute(); #do my job#
while (my $ref = $sth-fetchrow_hashref()) {
}; #try to put the slected entry into an array#
$sth-finish();
my $sth = $dbh-prepare('update smartaktion SET activated=NOW() WHERE
serial=$ref-{'serial'}'); #(should select the picked serial from above
and update the timestamp-field activated with actual timestamp)#
$sth-execute(); #do my job#
$sth-finish();
# Disconnect from the database.
$dbh-disconnect(); #done#
};
(however, it is not working?!)
-
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
Poor Manual [Was: Why using filesort here?]
On Sat, Apr 20, 2002 at 05:16:46AM +0200, Benjamin Pflugmann wrote: Hi. [...] According to the MySQL doc (5.2.7, example 5) this should work using the index without any additional sorting. MySQL-3.23.49-max-log running on FreeBSD 4.4-RELEASE I assume you are referring to the online manual? It's documenting the most recent version. According to the change history (Appendix D), this optimization was introduced in version 4.0.2. I see, thanks for that one. This manual is f@#$% up, since it seems like there is no manual for stable versions online or at least some annotations that say in which version a feature was introduced (it's not nice to lookup every change in the ChangeLog). I suspected that the online version reflects the most recent stable not some development version. Regards Sven - 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: Poor Manual
On Sun, Apr 21, 2002 at 02:44:32PM +0200, Roger Baklund wrote: * Sven Huster This manual is f@#$% up, since it seems like there is no manual for stable versions online or at least some annotations that say in which version a feature was introduced (it's not nice to lookup every change in the ChangeLog). I suspected that the online version reflects the most recent stable not some development version. There is a full documentation in different formats, including HTML, following every distribution. The web based manual is a extra _free_ service provided by MySQL AB. The online docs needs to be of _some_ version, and it is obviously better to have the 'current' version than to have some random version used by some random user... agree? Disagree, if there is only one manual it *must* be, for my understanding, be of the current *stable* version. I do not suspect the random user to be a alpha release user. But one question here: Is it such big problem to put all versions on and create some hyperlinks to them? Seems like this, cause i thought it might be good practice to do so. I also thought it would have been nice to put on a release schedual on the web site. I suggested this to the MySQL AB representant for Germany (as i am located there) but never ever heard from him again. So any comment is better than no comment. Btw: The commuication was initiated by this guy. I agree that the annotations could have been better. But I disagree with the way you communicate this to MySQL AB the rest of the mysql community... :) I took this off-list, feel free to take it back to the list or to reply to me in private. So i say sorry to the community. That's the reason to put it on-list again. I think, I was driven by my bad expireance with the MySQL AB support of which I was a former paying customer. Regards Sven - 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: Number pickup with parallel updating? with script!
From: Andrew Rich [EMAIL PROTECTED] Howdy, how can I add an extra field to an already created table in sql ? ALTER TABLE tablename ADD columnname attributes See http://www.mysql.com/doc/A/L/ALTER_TABLE.html for more details. -- denonymous www.coldcircuit.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
problem with libmysqlclient_r.so
Hello, All! I have program which try to connect to different mysql servers twice at the same time. When I use libmysqlclient.so sometimes one or two mysql connection freeze in mysql_real_connect But when I try to use libmysqlclient_r.so program crash in my_fopen this is stack of program : (gdb) bt #0 0x180ef7f2 in my_fopen () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #1 0x180f0b20 in search_default_file () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #2 0x180f085f in load_defaults () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #3 0x180e776b in mysql_read_default_options () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #4 0x180e8054 in mysql_real_connect () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #5 0x1812f940 in MysqlConnection::real_connect (this=0x80c8c18, db=0x80fd8b0 kuku, host=0x80fa040 xxx.xxx.net, user=0x80fd910 test, passwd=0x80c1bca , port=3306, compress=1, connect_timeout=60, socket_name=0x80c1bde , client_flag=0) at connection.cc:52 #6 0x8082dc3 in QueueModule::Run (this=0x80c8c00) at QModule.cc:138 #7 0x8068a8f in start_Module (ptr=0x80c8c00) at Module.cc:23 #8 0x181fce73 in _thread_start () from /usr/lib/libc_r.so.4 #9 0x0 in ?? () (gdb) frame 5 #5 0x1812f940 in MysqlConnection::real_connect (this=0x80c8c18, db=0x80fd8b0 stat, host=0x80fa040 skynet.alkar.net, user=0x80fd910 stat, passwd=0x80c1bca , port=3306, compress=1, connect_timeout=60, socket_name=0x80c1bde , client_flag=0) at connection.cc:52 52 if (mysql_real_connect(mysql,host,user,passwd,db, port,socket_name,client_flag)) (gdb) p host $1 = (cchar *) 0x80fa040 xxx.xxx.net (gdb) p user $2 = (cchar *) 0x80fd910 test (gdb) p passwd $3 = (cchar *) 0x80c1bca (gdb) p db $4 = (cchar *) 0x80fd8b0 kuku (gdb) p port $5 = 3306 (gdb) p socket_name $6 = 0x80c1bde (gdb) p client_flag $7 = 0 (gdb) OS FreeBSD 4.4-RC mysql version 3.23.42, but I try to use 3.23.49 with the same effect Will anybody help me? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport ÔÅÌ. +380 562 34-00-44 - 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: Poor Manual [Was: Why using filesort here?]
Hello. On Sun, Apr 21, 2002 at 02:15:33PM +0200, [EMAIL PROTECTED] wrote: [...] I assume you are referring to the online manual? It's documenting the most recent version. According to the change history (Appendix D), this optimization was introduced in version 4.0.2. I see, thanks for that one. This manual is f@#$% up, since it seems like there is no manual for stable versions online A copy of the manual for your version of MySQL accompanied your copy of MySQL. At last should have. And if not, it's online in a downloadable format. Only - but it's not as if there was no copy available. I agree that a searchable version for the stable branch would be nice, anyhow. or at least some annotations that say in which version a feature was introduced (it's not nice to lookup every change in the ChangeLog). If you have read some more of the manual, you will notice that most features have such annotations, except if they are long established. I suspected that the online version reflects the most recent stable not some development version. You are right, that could be stated more obviously. Bye, Benjamin. -- [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: Table Locks...
Hi, You can use show full processlist to see what table is locked and unlock tables; Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Sukhdev Sethi [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, April 20, 2002 9:44 PM Subject: Table Locks... Hi, Lets say that I am the admin on mysql server, how can i know which tables currently are locked? And if they are locked by someone else, how to do I remove the locks. Thank you. Rajan. __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.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
Starting MySQL with Windows 2000
Can anybody explain how to start MySQL on Windows 2000? I've moved and renamed the my.ini file to C:\my.cnf - to no avail. I get the following messages displayed in the Err File tab of WinMySQLadmin: 020421 5:22:28 MySql: Got signal 11. Aborting! 020420 8:41:09 MySql: Shutdown Complete Any help is greatly appreciated. Joe - 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
mail2db
hey. I have a little question (hope somebody can help me out) I search for a script/tool/... to write mails into a mysql db. (I know there is a perl script mail2db.pl, but i cant find it) Or better is there a script for fetchmail to write the mails direct to the mysql db Big thx for your help Best regards, jens __ Gesendet von Yahoo! Mail - http://mail.yahoo.de Sie brauchen mehr Speicher für Ihre E-Mails? - http://premiummail.yahoo.de - 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: Re: problem with load data local infile
At 23:34 -0500 4/20/02, Darren Vollmer wrote: Both files are world readable. How do you know this? Because FTP tells me they are. Also, when you say client machine, what do you mean? In particular, are the web server and the MySQL servers running on the same machine? Client machine is the machine running the webserver and MySQL client. The host machine runs the actual MySQL server. Are they the same or different? Your answer doesn't actually specify. - 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: Got an error reading communication packets ...
Hi, I know , sound strange.I life the same experience and my problem was , the first network switch, near the server machine(big responding time on the network - over 5.000 msec).What is was really oddly, not all the time the network have the same behaviour. The network interfaces , mounted on the server machine , can be influence by this behaviour.So, the client doesn't matter where is runnig (on the localhost or another host). Good luck, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Do-Risika RAFIEFERANTSIARONJY [EMAIL PROTECTED] To: Gelu [EMAIL PROTECTED] Cc: [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 8:24 AM Subject: Re: Got an error reading communication packets ... Gelu wrote: Hi, Problems on the network.High risq for corrupting tables. What kind of network problem it should be because the clients are on the local machine. Regards, @+ -- DouRiX Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Do-Risika RAFIEFERANTSIARONJY [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Saturday, April 20, 2002 6:15 PM Subject: Got an error reading communication packets ... Hi all, Does somebody know what cause these errors ? (the option 'warnings' is turned on) It's debian 3.0, mysql 3.23.47-log, the application which use mysql are freeradius, postfix and courier (all in localhost). 020420 16:45:57 Aborted connection 172 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:45:57 Aborted connection 171 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:45:57 Aborted connection 170 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:45:57 Aborted connection 178 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:45:57 Aborted connection 177 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:45:57 Aborted connection 179 to db: 'radius' user: 'radiusd' host: `localhost' (Got an error reading communication packets) 020420 16:47:37 Aborted connection 165 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:47:37 Aborted connection 164 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:47:37 Aborted connection 166 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:47:38 Aborted connection 168 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:47:38 Aborted connection 167 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:50:23 Aborted connection 199 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) 020420 16:50:23 Aborted connection 198 to db: 'mail' user: 'smtpd' host: `simicro-193-251-140-214.simicro.net' (Got an error reading communication packets) Regards, -- Do-Risika RAFIEFERANTSIARONJY, SysAdmin mailto:[EMAIL PROTECTED] Simicro Internet, mailto:[EMAIL PROTECTED], http://internet.simicro.mg Tel : (+261) 20 22 648 83 (GMT +3), Fax : (+261) 20 22 661 83 - 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 -- Do-Risika RAFIEFERANTSIARONJY, SysAdmin mailto:[EMAIL PROTECTED] Simicro Internet, mailto:[EMAIL PROTECTED], http://internet.simicro.mg Tel : (+261) 20 22 648 83 (GMT +3), Fax : (+261) 20 22 661 83 - 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: Starting MySQL with Windows 2000
Joe: Here is what we do ... - Login as Administrator - Open DOS Prompt, switch to the drive with MySQL on it, and CD\MYSQL\BIN - Type MYSQLD-NT --INSTALL - Type NET START MYSQL If that doesn't do the trick, your C:\WINNT\MY.INI (or C:\MY.CNF) may not be correct. Most coomon mistake is to use the wrong slash in basedir or datadir directories. Use forward slash (/) instead of backslash (\). i.e.: basedir=d:/mysql datadir=d:/mysql/data Gerald Jensen - Original Message - From: Joe Meirow [EMAIL PROTECTED] To: mysql [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 10:05 AM Subject: Starting MySQL with Windows 2000 Can anybody explain how to start MySQL on Windows 2000? I've moved and renamed the my.ini file to C:\my.cnf - to no avail. I get the following messages displayed in the Err File tab of WinMySQLadmin: 020421 5:22:28 MySql: Got signal 11. Aborting! 020420 8:41:09 MySql: Shutdown Complete Any help is greatly appreciated. Joe - 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
Compare with 0 is not reliable
-Original Message-
From: zlab1 [mailto:[EMAIL PROTECTED]]
Sent: Sunday, April 21, 2002 13:50
To: MySQL Mailing List ([EMAIL PROTECTED])
Subject: Problem with HAVING
Hi,
I'm running Mysql 4.01 on mandrake 8.2 with all production data
using InnoDB type.
I've a field named dch_pri contain some account transaction data in
format of DECIMAL (14,2).
When running following query:
SELECT din_no, SUM(IF(tran_type = D, dch_pri, -dch_pri)) AS dch_sum
FROM pa_ch2
WHERE x_status IN (, C)
AND dch_acc IN ('231000', '31')
GROUP BY din_no
HAVING dch_sum # 0
It remove all record that contain absolute value between 0 and 0.99
Change query to:
SELECT din_no, SUM(IF(tran_type = D, dch_pri, -dch_pri)) AS dch_sum
FROM pa_ch2
WHERE x_status IN (, C)
AND dch_acc IN ('231000', '31')
GROUP BY din_no
HAVING dch_sum 0
It will retain all result record which dch_sum = 0
Change the HAVING dch_sum # 0 to HAVING dch_sum # 0.00
Or
Change the HAVING dch_sum 0 to HAVING dch_sum 0.00 Doesn't
improve. Any idea?
-
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 with libmysqlclient_r.so
Hi, You use and two different mysql_init ? Regards, Gelu _ G.NET SOFTWARE COMPANY Permanent e-mail address : [EMAIL PROTECTED] [EMAIL PROTECTED] - Original Message - From: Andrey Kotrekhov [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Sunday, April 21, 2002 4:55 PM Subject: problem with libmysqlclient_r.so Hello, All! I have program which try to connect to different mysql servers twice at the same time. When I use libmysqlclient.so sometimes one or two mysql connection freeze in mysql_real_connect But when I try to use libmysqlclient_r.so program crash in my_fopen this is stack of program : (gdb) bt #0 0x180ef7f2 in my_fopen () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #1 0x180f0b20 in search_default_file () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #2 0x180f085f in load_defaults () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #3 0x180e776b in mysql_read_default_options () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #4 0x180e8054 in mysql_real_connect () from /usr/local/mysql/lib/libmysqlclient_r.so.10 #5 0x1812f940 in MysqlConnection::real_connect (this=0x80c8c18, db=0x80fd8b0 kuku, host=0x80fa040 xxx.xxx.net, user=0x80fd910 test, passwd=0x80c1bca , port=3306, compress=1, connect_timeout=60, socket_name=0x80c1bde , client_flag=0) at connection.cc:52 #6 0x8082dc3 in QueueModule::Run (this=0x80c8c00) at QModule.cc:138 #7 0x8068a8f in start_Module (ptr=0x80c8c00) at Module.cc:23 #8 0x181fce73 in _thread_start () from /usr/lib/libc_r.so.4 #9 0x0 in ?? () (gdb) frame 5 #5 0x1812f940 in MysqlConnection::real_connect (this=0x80c8c18, db=0x80fd8b0 stat, host=0x80fa040 skynet.alkar.net, user=0x80fd910 stat, passwd=0x80c1bca , port=3306, compress=1, connect_timeout=60, socket_name=0x80c1bde , client_flag=0) at connection.cc:52 52 if (mysql_real_connect(mysql,host,user,passwd,db, port,socket_name,client_flag)) (gdb) p host $1 = (cchar *) 0x80fa040 xxx.xxx.net (gdb) p user $2 = (cchar *) 0x80fd910 test (gdb) p passwd $3 = (cchar *) 0x80c1bca (gdb) p db $4 = (cchar *) 0x80fd8b0 kuku (gdb) p port $5 = 3306 (gdb) p socket_name $6 = 0x80c1bde (gdb) p client_flag $7 = 0 (gdb) OS FreeBSD 4.4-RC mysql version 3.23.42, but I try to use 3.23.49 with the same effect Will anybody help me? Best regards. ___ Andrey Kotrekhov [EMAIL PROTECTED] ISP Alkar Teleport ÔÅÌ. +380 562 34-00-44 - 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
optimization
Hi, I have a question about how to optimize MySQL performance for a somewhat unusual (I think) usage pattern. In the first process, I'm summarizing a large amount of time series data (the main table will end up around 5GB, index around 1/3 of that) and storing the summaries to the db. During this period, no reads to the summary tables will occur. In another process, I'm reading the summaries and no writing occurs. I have a reasonable amount of RAM (1GB) and the system is dedicated to the db task. What is the best way to optimize these processes? Using HEAP tables seems to be out because they won't fit completely in memory. How best to take advantage of the no reading/no writing? Thanks, -David - 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
What ever happened to Name the Dolphin?
...winner will be announced January 31, or sometime in February. Did I miss something? What is the Dolphin's name? Steve the mysql data guy - 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: Poor Manual [Was: Why using filesort here?]
Hi benjamin/Sven, it's my understanding that the latest revision of the manual is (minus 4.x commits) always included in the distribution set. New features are always documented in the manual and are nearly always appended with a This was added in version insert version here string some place. Benjamin Pflugmann wrote: Hello. On Sun, Apr 21, 2002 at 02:15:33PM +0200, [EMAIL PROTECTED] wrote: [...] I assume you are referring to the online manual? It's documenting the most recent version. According to the change history (Appendix D), this optimization was introduced in version 4.0.2. I see, thanks for that one. This manual is f@#$% up, since it seems like there is no manual for stable versions online A copy of the manual for your version of MySQL accompanied your copy of MySQL. At last should have. And if not, it's online in a downloadable format. Only - but it's not as if there was no copy available. I agree that a searchable version for the stable branch would be nice, anyhow. or at least some annotations that say in which version a feature was introduced (it's not nice to lookup every change in the ChangeLog). If you have read some more of the manual, you will notice that most features have such annotations, except if they are long established. I suspected that the online version reflects the most recent stable not some development version. You are right, that could be stated more obviously. Bye, Benjamin. -- [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 -- Colin Faber (303) 736-5160 fpsn.net, Inc. - 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
Running Mysql On Host other than localHost
I have the standard tarbal install of mysql. I want to know how do I start the server so that I can access it from another machine. I am running on a linux 6.2 server where the local ip address of the box is 192.168.1.150 and the hostname is MysqlServer. I need to be able to change the host that ir start with from localhost to something else. I don't see anywhere in the docs that show how to do this. I can conect and do all db work on the linux console but cannot seem to connect from anyother box into the linux box to access the Mysql Server. PLEASE HELP ME !!! - 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
rows
Hey guys, I have a, for you simple, newbie question. my query is SELECT * from $TableName LIMIT $name now $name goes from 1 to 4, the 4 rows in my database, but i just want the query to give me one row, not say if $name is 2, rows 1 and 2, just row 2. How do i do this? thanks, Jule -- Jule Slootbeek [EMAIL PROTECTED] http://blindtheory.cjb.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: Running Mysql On Host other than localHost
From: Kevin Fialkowski [EMAIL PROTECTED] ...so that I can access it from another machine. Hi Kevin, This isn't a matter of how you start the service, but of your user privileges. Users are not only known by user name and password, but also by the host they are coming from. Start with this doc: http://www.mysql.com/doc/R/e/Request_access.html --- Rodney Broom 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
performance on unix vs windows
Hi, I am interested to know if there was any comparisions made between windows and unix mySQL ? What is the performance difference in comparable hardware and same version of mySQL running on unix vs windows ? If anyone can offer some help/insights/pointers I will appreciate it very much. I realize this is not really an apple to apple comprarions, but ballpark figures will help. Thanks! Mir PS: plz cc to my email as well as to the list - 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
InnoDB Error 150, MySQL 4.0.1 alpha 3.23.49
Hello,
When attempting to create some tables using the MySQL 4.0.1 alpha and
3.23.49, I'm getting a generic error message, General error: Can't
create table './industria/ORDEREDITEMS.frm' (errno: 150). The other 6
tables, all using similar features, are created without a problem.
perror just says, Error code 150: Unknown error 150 which isn't
terribly helpful.
Some assistance with this would be appreciated. :-)
-rh
CREATE TABLE VENDORS ( V_VENDORID BIGINT NOT NULL, V_VENDORNAME
VARCHAR(20) NOT NULL, V_DESCRIPTION VARCHAR(80) NOT NULL, V_LOCKCNT
BIGINT NOT NULL DEFAULT '0', V_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(V_VENDORID), UNIQUE KEY VENDORS_VENDORNAME_IDX (V_VENDORNAME) )
TYPE=InnoDB;
CREATE TABLE ITEMS ( I_ITEMID BIGINT NOT NULL, I_V_VENDORID BIGINT NOT
NULL, I_ITEMNAME VARCHAR(20) NOT NULL, I_DESCRIPTION VARCHAR(80) NOT
NULL, I_ACTIVE ENUM('T','F') NOT NULL, I_PRICE DECIMAL(10, 2) NOT NULL,
I_LOCKCNT BIGINT NOT NULL DEFAULT '0', I_TIMESTAMP TIMESTAMP NOT NULL,
PRIMARY KEY (I_ITEMID), UNIQUE KEY ITEMS_VENDORITEM_IDX (I_V_VENDORID,
I_ITEMNAME), FOREIGN KEY (I_V_VENDORID) REFERENCES VENDORS(V_VENDORID)
ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATALOGS ( C_CATALOGID BIGINT NOT NULL, C_CATALOGNAME
VARCHAR(20) NOT NULL, C_DESCRIPTION VARCHAR(80) NOT NULL, C_LOCKCNT
BIGINT NOT NULL DEFAULT '0', C_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(C_CATALOGID), UNIQUE KEY CATALOGS_CATALOGNAME_IDX (C_CATALOGNAME) )
TYPE=InnoDB;
CREATE TABLE CATEGORIES ( CG_CATEGORYID BIGINT NOT NULL, CG_C_CATALOGID
BIGINT NOT NULL, CG_CATEGORYNAME VARCHAR(20) NOT NULL, CG_CG_CATEGORYID
BIGINT NOT NULL, CG_DESCRIPTION VARCHAR(80) NOT NULL, CG_LOCKCNT BIGINT
NOT NULL DEFAULT '0', CG_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(CG_CATEGORYID), UNIQUE KEY CATEGORIES_CATALOGCATEGORY_IDX
(CG_C_CATALOGID, CG_CATEGORYNAME), KEY CATEGORIES_CATALOGID_IDX
(CG_C_CATALOGID), KEY CATEGORIES_PARENTCATEGORYID_IDX
(CG_CG_CATEGORYID), FOREIGN KEY (CG_C_CATALOGID) REFERENCES
CATALOGS(C_CATALOGID) ON DELETE CASCADE, FOREIGN KEY (CG_CG_CATEGORYID)
REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATAGORYITEMS ( CI_CG_CATEGORYID BIGINT NOT NULL,
CI_I_ITEMID BIGINT NOT NULL, CI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
CI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (CI_CG_CATEGORYID,
CI_I_ITEMID), KEY CATAGORYITEMS_ITEMID_IDX (CI_I_ITEMID), FOREIGN KEY
(CI_CG_CATEGORYID) REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE
CASCADE, FOREIGN KEY (CI_I_ITEMID) REFERENCES ITEMS(I_ITEMID) ON DELETE
CASCADE ) TYPE=InnoDB;
CREATE TABLE ORDERS ( O_ORDERID BIGINT NOT NULL, O_ORDEREDITEMSEQUENCE
INT NOT NULL, O_LOCKCNT BIGINT NOT NULL DEFAULT '0', O_TIMESTAMP
TIMESTAMP NOT NULL, PRIMARY KEY (O_ORDERID) ) TYPE=InnoDB;
CREATE TABLE ORDEREDITEMS ( OI_O_ORDERID BIGINT NOT NULL,
OI_ORDEREDITEMSEQUENCE INT NOT NULL, OI_I_ITEMID BIGINT NOT NULL,
OI_QUANTITY INT(4) NOT NULL, OI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
OI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (OI_O_ORDERID,
OI_ORDEREDITEMSEQUENCE), FOREIGN KEY (OI_O_ORDERID) REFERENCES
ORDERS(O_ORDERID) ON DELETE CASCADE, FOREIGN KEY (OI_I_ITEMID)
REFERENCES ITEMS(I_ITEMID) ON DELETE CASCADE ) TYPE=InnoDB;
java.sql.SQLException: General error: Can't create table
'./industria/ORDEREDITEMS.frm' (errno: 150)
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Statement.executeQuery(Unknown Source)
at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Unknown Source)
at
com.darkrealms.entities.DatabaseUtilities.createMissingTable(DatabaseUti
lities.java:432)
at
com.darkrealms.entities.DatabaseUtilities.checkTables(DatabaseUtilities.
java:464)
at
com.darkrealms.entities.DatabaseUtilities.openDatabaseConnection(Databas
eUtilities.java:38)
at
com.darkrealms.entities.DatabaseUtilitiesForMySQL.init(DatabaseUtiliti
esForMySQL.java:19)
at
com.darkrealms.entities.DatabaseFromXML.main(DatabaseFromXML.java:17)
Exception in thread main
-
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: rows
In your implementation, using limit, LIMIT startpoint,# to retrieve so you would want $name=2,1 -Brenden On Sun, 2002-04-21 at 17:05, Jule Slootbeek wrote: Hey guys, I have a, for you simple, newbie question. my query is SELECT * from $TableName LIMIT $name now $name goes from 1 to 4, the 4 rows in my database, but i just want the query to give me one row, not say if $name is 2, rows 1 and 2, just row 2. How do i do this? thanks, Jule -- Jule Slootbeek [EMAIL PROTECTED] http://blindtheory.cjb.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) 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
How to speed up mods to large table?
I haven't been able to dig up any specific information about how to get maximum performance when making changes to large tables. I have a table that's close to 4 GB, which I'm altering to come up with the best trade-off between performance and speed. Dropping a column or an index seems exceedingly slow and I'm wondering what parameters matter for this. I'm already using the highest-performance drive I can lay my hands on. What else matters? It certainly looks like it's I/O bound, so I'm wondering if forcing it to build its temp files on a separate disk subsystem would help. It would be a much lower-performance system, though (IDE v. SCSI-160). Thanks in advance... And now, to get past the (not-very-smart) spam filter... sql, query. Nick -- [EMAIL PROTECTED] (408) 904-7198 - 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: How to speed up mods to large table?
-Original Message- From: Nick Arnett [mailto:[EMAIL PROTECTED]] Sent: Sunday, April 21, 2002 3:39 PM To: [EMAIL PROTECTED] Subject: How to speed up mods to large table? I haven't been able to dig up any specific information about how to get maximum performance when making changes to large tables. I have a table that's close to 4 GB, which I'm altering to come up with the best trade-off between performance and speed. Dropping a column or an index seems exceedingly slow and I'm wondering what parameters matter for this. To answer my own question, at least in part... since I'm using MySQL 4.0.1, I'm trying the same operation again after doing alter table... disable keys. That appears to make it much, much faster. Strangely, though, it's not using the temporary directory that I gave it in my.ini with the tmpdir variable. I seem to recall having that problem before, but I don't know what solved it. Nick - 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 optimizations - tips/resources
Anyone have a good resource for mysql optimization tips? or just standard db optimization. Thanks, olinux __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.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 optimizations - tips/resources
yeah.. i've just been researching that subject myself.. it seems that db optimization is mainly about normalization.. check out this http://www.devshed.com/Server_Side/MySQL/Normal dan - Original Message - From: olinux [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, April 22, 2002 11:21 AM Subject: mysql optimizations - tips/resources Anyone have a good resource for mysql optimization tips? or just standard db optimization. Thanks, olinux __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.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
joins vs excess fields
Hello, I have a table with a large number of news articles. Articles of type 'feature' (identified in the article_type field) will have the path to an image associated with the article stored. The question is whether to 1) add a field to the database that most records will not use OR 2) add a table to hold image path names and require a JOIN in order to retrieve. I am thinking that the join query will be best. Thanks for any help. olinux Table setup is: CREATE TABLE articles ( id int(12) NOT NULL auto_increment, type varchar(15) NOT NULL default '', region smallint(1) NOT NULL default '0', date varchar(35) NOT NULL default '0', author varchar(30) NOT NULL default '', title varchar(90) NOT NULL default '', summary varchar(255) NOT NULL default '', content text NOT NULL, keywords varchar(150) NOT NULL default '', filename varchar(30) NOT NULL default '', PRIMARY KEY (id,id) ) TYPE=MyISAM; __ Do You Yahoo!? Yahoo! Games - play chess, backgammon, pool and more http://games.yahoo.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 optimizations - tips/resources
On Sun, Apr 21, 2002 at 06:21:33PM -0700, olinux wrote: Anyone have a good resource for mysql optimization tips? or just standard db optimization. Well, there are a few things I wrote up last year: http://www.linux-mag.com/2001-06/mysql_01.html http://www.linux-mag.com/2001-12/mysql_01.html Maybe they'll help get you started? 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.47-max: up 73 days, processed 1,940,871,186 queries (305/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 fails to correctly index decimal datatype columns
Description: Very similar to a problem I reported more than 2 years ago, which was then fixed in a subsequent release, it now appears that the index on a column that is declared as decimal(14,2) is not being built correctly. Dropping the index and re-creating it seems to fix the problem temporarily but when new inserts elsewhere in the table are done, it begins to fail again. How-To-Repeat: My table definition looks like this: +---+---+--+-+++ | Field | Type | Null | Key | Default| Extra | +---+---+--+-+++ | claimid | int(11) | | PRI | NULL | auto_increment | | client_id | varchar(10) | | MUL ||| | pctlnum | varchar(20) | | ||| | claimamt | decimal(14,2) | | | 0.00 || | dos | date | | | -00-00 || | provid| varchar(14) | | ||| | payerid | varchar(6)| | MUL ||| | memid | varchar(20) | | ||| | db_time | datetime | YES | | NULL || | db_oper | char(1) | | ||| | db_user | varchar(12) | | ||| | incatbox | char(1) | | | N || | loaded| date | | | -00-00 || +---+---+--+-+++ Note that a unique index exists on columns (client_id, pctlnum, claimamt, dos, provid). Insert data. Make sure to insert 5 or 10 rows which have identical keys in the multi-column index except that the dos column value is different. Insert many other rows. Try to select rows using partial key, e.g. Select pctlnum, claimamt, dos, provid, payerid, memid From claim Where client_id = 'ndabsolute' AND pctlnum = '00021169' and claimamt = 325.00; You will find some but not all of the rows which match. Fix: No work around or fix known. Submitter-Id: submitter ID Originator:Chris Johnson Organization: MySQL support: none Synopsis: Indexing of decimal columns fails to locate all rows. Severity: critical Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.47 (FreeBSD port: mysql-server-3.23.47) Environment: System: FreeBSD cheetah.claimlynx.com 4.5-RELEASE FreeBSD 4.5-RELEASE #0: Sat Feb 16 16:21:47 CST 2002 [EMAIL PROTECTED]:/usr/src/sys/compile/CHEETAH i386 Some paths: /usr/bin/perl /usr/bin/make /usr/local/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Using builtin specs. gcc version 2.95.3 20010315 (release) [FreeBSD] Compilation info: CC='cc' CFLAGS='-O -pipe ' CXX='cc' CXXFLAGS='-O -pipe -felide-constructors -fno-rtti -fno-exceptions' LDFLAGS='' LIBC: -r--r--r-- 1 root wheel 1208074 Jan 28 07:11 /usr/lib/libc.a lrwxrwxrwx 1 root wheel 9 Feb 16 09:58 /usr/lib/libc.so - libc.so.4 -r--r--r-- 1 root wheel 573760 Jan 28 07:11 /usr/lib/libc.so.4 Configure command: ./configure --localstatedir=/var/db/mysql --without-perl --without-debug --without-readline --without-bench --with-mit-threads=no --with-libwrap --with-low-memory '--with-comment=FreeBSD port: mysql-server-3.23.47' --enable-assembler --with-berkeley-db --with-innodb --prefix=/usr/local i386--freebsd4.5 Perl: This is perl, version 5.005_03 built for i386-freebsd - 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: joins vs excess fields
Hello olinux, If I were in your shoes, I would put not only paths to images into a separate table, but also a field called content and probably title, summary, author and linked all this stuff together via ID-like fields which are primary keys in those new tables. Then articles table can be searched through much faster, and title, summary, author data can be quickly retrieved using primmary keys ONLY when this data is needed. So you first search a very compact table where most of the fields are indexes or parts of indexes, and THEN, using primary keys, retrieve big pieces of data which you do not have to search through (and nothing, I guess, works faster in MySQL than retrieval of a single record by it's primary key). Run your queries with EXPLAIN, it will probably explain it less verbously than me :). Regards, Tom Monday, April 22, 2002, 6:29:52 AM, you wrote: o Hello, o I have a table with a large number of news articles. o Articles of type 'feature' (identified in the o article_type field) will have the path to an image o associated with the article stored. The question is o whether to 1) add a field to the database that most o records will not use OR 2) add a table to hold image o path names and require a JOIN in order to retrieve. o I am thinking that the join query will be best. Thanks o for any help. o olinux o Table setup is: o CREATE TABLE articles ( o id int(12) NOT NULL auto_increment, o type varchar(15) NOT NULL default '', o region smallint(1) NOT NULL default '0', o date varchar(35) NOT NULL default '0', o author varchar(30) NOT NULL default '', o title varchar(90) NOT NULL default '', o summary varchar(255) NOT NULL default '', o content text NOT NULL, o keywords varchar(150) NOT NULL default '', o filename varchar(30) NOT NULL default '', o PRIMARY KEY (id,id) o ) TYPE=MyISAM; - 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: InnoDB Error 150, MySQL 4.0.1 alpha 3.23.49
Richard,
- Original Message -
From: Richard Harms [EMAIL PROTECTED]
Newsgroups: mailing.database.mysql
Sent: Monday, April 22, 2002 2:32 AM
Subject: InnoDB Error 150, MySQL 4.0.1 alpha 3.23.49
Hello,
When attempting to create some tables using the MySQL 4.0.1 alpha and
3.23.49, I'm getting a generic error message, General error: Can't
create table './industria/ORDEREDITEMS.frm' (errno: 150). The other 6
tables, all using similar features, are created without a problem.
perror just says, Error code 150: Unknown error 150 which isn't
terribly helpful.
Some assistance with this would be appreciated. :-)
-rh
CREATE TABLE VENDORS ( V_VENDORID BIGINT NOT NULL, V_VENDORNAME
VARCHAR(20) NOT NULL, V_DESCRIPTION VARCHAR(80) NOT NULL, V_LOCKCNT
BIGINT NOT NULL DEFAULT '0', V_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(V_VENDORID), UNIQUE KEY VENDORS_VENDORNAME_IDX (V_VENDORNAME) )
TYPE=InnoDB;
CREATE TABLE ITEMS ( I_ITEMID BIGINT NOT NULL, I_V_VENDORID BIGINT NOT
NULL, I_ITEMNAME VARCHAR(20) NOT NULL, I_DESCRIPTION VARCHAR(80) NOT
NULL, I_ACTIVE ENUM('T','F') NOT NULL, I_PRICE DECIMAL(10, 2) NOT NULL,
I_LOCKCNT BIGINT NOT NULL DEFAULT '0', I_TIMESTAMP TIMESTAMP NOT NULL,
PRIMARY KEY (I_ITEMID), UNIQUE KEY ITEMS_VENDORITEM_IDX (I_V_VENDORID,
I_ITEMNAME), FOREIGN KEY (I_V_VENDORID) REFERENCES VENDORS(V_VENDORID)
ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATALOGS ( C_CATALOGID BIGINT NOT NULL, C_CATALOGNAME
VARCHAR(20) NOT NULL, C_DESCRIPTION VARCHAR(80) NOT NULL, C_LOCKCNT
BIGINT NOT NULL DEFAULT '0', C_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(C_CATALOGID), UNIQUE KEY CATALOGS_CATALOGNAME_IDX (C_CATALOGNAME) )
TYPE=InnoDB;
CREATE TABLE CATEGORIES ( CG_CATEGORYID BIGINT NOT NULL, CG_C_CATALOGID
BIGINT NOT NULL, CG_CATEGORYNAME VARCHAR(20) NOT NULL, CG_CG_CATEGORYID
BIGINT NOT NULL, CG_DESCRIPTION VARCHAR(80) NOT NULL, CG_LOCKCNT BIGINT
NOT NULL DEFAULT '0', CG_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY
(CG_CATEGORYID), UNIQUE KEY CATEGORIES_CATALOGCATEGORY_IDX
(CG_C_CATALOGID, CG_CATEGORYNAME), KEY CATEGORIES_CATALOGID_IDX
(CG_C_CATALOGID), KEY CATEGORIES_PARENTCATEGORYID_IDX
(CG_CG_CATEGORYID), FOREIGN KEY (CG_C_CATALOGID) REFERENCES
CATALOGS(C_CATALOGID) ON DELETE CASCADE, FOREIGN KEY (CG_CG_CATEGORYID)
REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE CASCADE ) TYPE=InnoDB;
CREATE TABLE CATAGORYITEMS ( CI_CG_CATEGORYID BIGINT NOT NULL,
CI_I_ITEMID BIGINT NOT NULL, CI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
CI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (CI_CG_CATEGORYID,
CI_I_ITEMID), KEY CATAGORYITEMS_ITEMID_IDX (CI_I_ITEMID), FOREIGN KEY
(CI_CG_CATEGORYID) REFERENCES CATEGORIES(CG_CATEGORYID) ON DELETE
CASCADE, FOREIGN KEY (CI_I_ITEMID) REFERENCES ITEMS(I_ITEMID) ON DELETE
CASCADE ) TYPE=InnoDB;
CREATE TABLE ORDERS ( O_ORDERID BIGINT NOT NULL, O_ORDEREDITEMSEQUENCE
INT NOT NULL, O_LOCKCNT BIGINT NOT NULL DEFAULT '0', O_TIMESTAMP
TIMESTAMP NOT NULL, PRIMARY KEY (O_ORDERID) ) TYPE=InnoDB;
CREATE TABLE ORDEREDITEMS ( OI_O_ORDERID BIGINT NOT NULL,
OI_ORDEREDITEMSEQUENCE INT NOT NULL, OI_I_ITEMID BIGINT NOT NULL,
OI_QUANTITY INT(4) NOT NULL, OI_LOCKCNT BIGINT NOT NULL DEFAULT '0',
OI_TIMESTAMP TIMESTAMP NOT NULL, PRIMARY KEY (OI_O_ORDERID,
OI_ORDEREDITEMSEQUENCE), FOREIGN KEY (OI_O_ORDERID) REFERENCES
ORDERS(O_ORDERID) ON DELETE CASCADE, FOREIGN KEY (OI_I_ITEMID)
REFERENCES ITEMS(I_ITEMID) ON DELETE CASCADE ) TYPE=InnoDB;
there is no index on OI_I_ITEMID. InnoDB requires that a suitable index is
explicitly defined for a foreign key. Also note that ON DELETE CASCADE only
works from 3.23.50 up. When you migrate to 3.23.50, you must recreate your
tables so that InnoDB is aware of ON DELETE CASCADE.
java.sql.SQLException: General error: Can't create table
'./industria/ORDEREDITEMS.frm' (errno: 150)
at org.gjt.mm.mysql.MysqlIO.sendCommand(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQueryDirect(Unknown Source)
at org.gjt.mm.mysql.MysqlIO.sqlQuery(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Connection.execSQL(Unknown Source)
at org.gjt.mm.mysql.Statement.executeQuery(Unknown Source)
at org.gjt.mm.mysql.jdbc2.Statement.executeQuery(Unknown Source)
at
com.darkrealms.entities.DatabaseUtilities.createMissingTable(DatabaseUti
lities.java:432)
at
com.darkrealms.entities.DatabaseUtilities.checkTables(DatabaseUtilities.
java:464)
at
com.darkrealms.entities.DatabaseUtilities.openDatabaseConnection(Databas
eUtilities.java:38)
at
com.darkrealms.entities.DatabaseUtilitiesForMySQL.init(DatabaseUtiliti
esForMySQL.java:19)
at
com.darkrealms.entities.DatabaseFromXML.main(DatabaseFromXML.java:17)
Exception in thread main
Best regards,
Heikki Tuuri
Innobase Oy
---
InnoDB - transactions, row level locking, and foreign key support for MySQL
See http://www.innodb.com, download MySQL-Max from
Re: Poor Manual [Was: Why using filesort here?]
On Sunday, 21. April 2002 20:54, Colin Faber wrote: it's my understanding that the latest revision of the manual is (minus 4.x commits) always included in the distribution set. New features are always documented in the manual and are nearly always appended with a This was added in version insert version here string some place. Thats right, but often you can't find this information with the first view. A little note under the function name, like in the PHP-Manual, would be easier to use. Just my 2 cents Georg Filter: mysql, query - 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: InnoDB transactions with Connection Pooling
Mark, if you do not explicitly do SET AUTOCOMMIT=0 then MySQL automatically calls COMMIT after every SQL statement. If you set AUTOCOMMIT=0, then you should yourself call COMMIT after each SELECT so that you do not leave a dangling transaction open in the database and that you get a fresh snapshot of the database in each consistent read. Best regards, Heikki Tuuri Innobase Oy --- InnoDB - transactions, row level locking, and foreign key support for MySQL See http://www.innodb.com, download MySQL-Max from http://www.mysql.com - Original Message - From: Mark Hazen [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Monday, April 22, 2002 3:35 AM Subject: InnoDB transactions with Connection Pooling Fellow MySQL gurus, I am using Apache::DBI to accomplish connection pooling. I am working with an InnoDB table that gets updated very frequently. My question is this: Since my connections are pooled and stay open for days at a time, am I essentially always going to read from that connection the same version of the database (even from request to request). My guess is yes and that I would need to do a COMMIT before every request (or after). Maybe someone can shed some light on this... Example: Table innodb_test has 2 rows. Connection ID 1, Apache Request 1 SELECT * FROM innodb_test; It spits back 2 rows. Then some other thread adds 3 rows to the table, and COMMITs them. Connection ID 1, Apache Request 2 (notice that it is the same connection, just a new web page request) SELECT * FROM innodb_test; My guess is that it would spit back the same 2 rows again and not 5. I would need to do a COMMIT either before or after each request. Is this right? Does anyone have an opinion on whether I should do it after or before. I would assume after because the request could then already be served to the user (I don't need up-to-the-picosecond results). Thanks! Mark - 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
