Re: Update databases, the unanswered question
on 12/18/2002 11:39 PM, Dan at [EMAIL PROTECTED] wrote: http://worldcommunity.com/opensource/utilities/mysql_backup.html I've heard great things about this script and I'm just about to start using it myself. Am I wrong, but it seem this script dumps each table to a separate file, what is the use in this, you could need up with many many file, I think it would be rare you would need to restore a table, and not the entire database?? - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - 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
replication and auto_increment
hi, i would like to create a dual master/slave replication with mysql i think i wouldn't have any problem but i wonder how mysql manage auto_increment id ... how it works ? maxime - 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
Missing values
Hi, I've been trying to import a huge .txt file into a MySql table. In the .txt file, missing values are recorded as empty cells (it might be that there's some hidden character instead, but I wouldn't know how to figure that out). When I LOAD DATA INFILE, MySql writes the missing values as zeros, rather than nulls. I can't UPDATE these zeros as nulls after loading the data, because some of the data are actually zeros in the original .txt file. Do you have any suggestions? Gianluca - 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[2]: Can MySQL handle 120 million records?
Dobrý den, quarta-feira, 18 de dezembro de 2002, 13:10:07, napsal jste: MTB Qunfeng Dong wrote: Another thing, with some linux system, there is a size limit for file. MySQL seems to store each of its table as single file. You need to choose a file system without that limit. MTB Just use InnoDB tables for these files and you won't have a problem MTB AFAIK; you can have multiple 2G files that are used to create one big MTB table if you like (any InnoDB people want to comment on actual limits?) Use the InnoDB tables with the raw devices ( ex: allow innodb use a /dev/sdxx or /dev/hdxx to write tablespace ), the speed is better, MySQL don't loses time with the filesystem. In my production database , i have a tablespace with 130G ( with raw diveces on SCSI disks) and the performance is good :) ps: i'm using MySQL 4.0.5 sql,query - ++ Dyego Souza do Carmo ++ Dep. Desenvolvimento - E S C R I B A I N F O R M A T I C A - The only stupid question is the unasked one (somewhere in Linux's HowTo) Linux registred user : #230601 -- $ look into my eyes look: cannot open my eyes - Reply: [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: Accessing last_insert_id problem.
I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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: Accessing last_insert_id problem.
http://www.mysql.com/doc/en/SET_OPTION.html last option(s) maybe you can do somehting with that Gr At 10:54 19-12-02 +, Jeff Snoxell wrote: I'm working with MySQL 3.23.36 so, according to MySQL, Paul Dubois, New Riders running the query: DELETE FROM my_table_name should reset the auto-increment value... but it doesnt'. What SQL do I use to reset the val. You can't believe anything that book says. The author didn't even include anything about TRUNCATE TABLE my_table_name. Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table then: INSERT INTO my_table SET Name='Jeff' then: SELECT * FROM my_table and lo-and-behold, the 'Ref' primary key auto-update field is 37062, not 1 as I hoped. I am managing to reset it to '1' by using a windows mysql client program but it doesn't show me what SQL it's executing in order to obtain the desired result. Thanks, Jeff - 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: Can MySQL handle 120 million records?
MySQL may be new compared to Oracle, for example, but many other in-use DBs are in fact fairly new designs. They just happen to be written by* large companies you recognize every day. Any ideas about Postgresql vs. MySQL? I have always preferred MySQL because of the speed, but I have heard that Postgres also got improved over time. Anybody has some experience willing to share? How much slower is Postgres? - Cs. - 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: OT: Spam Filter (again)
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 18 Dec 2002, Jeremy Zawodny wrote: But, yeah, I agree. There are better ways to do this on the mail server side. Yes, and it's on our list-admin's TODO to move this list to another list server very soon. Please be patient for just a little longer - it's going to happen RSN and this sql,query thing will go away. Adding some more words to the filter was just a preliminary action until it's fixed for good. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+Aa2ZSVDhKrJykfIRAiFZAJ4mQZ+xnHIPDKdx51XDZgUDGjYZfQCeK+LU J3AHPFqN4x3rpLS/s+rNBs4= =MgJp -END PGP SIGNATURE- - 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: GUI for the server part of MySQL
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 19 Dec 2002, Stan Sebastian wrote: Is there any GUI for the server part of MySQL fro Windows. I mean not a client for MySQL, like SQLyog OR MySqlFront. Something better then the MySqlAdmin? Have you looked at MySQLCC yet? http://www.mysql.com/products/mysqlcc/ Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+Aa8FSVDhKrJykfIRAu8eAJ9buiPABSLPNuJMCrbEgCnGhWe9vwCaAx04 DoQbD0T9roDEQfN0oZXDZP8= =hLMq -END PGP SIGNATURE- - 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
/tmp datadir
Hello, Mysql server is working good for 5-10 minutes and then changes directory to /tmp - it starts telling ERROR 1049: Unknown database 'test'. Restarting the server helps for next 5-10 minutes. There are no errors in error-log, query log etc. SHOW VARIABLES still telling datadir from cnf file. Does anybody faced with this bug? System: FreeBSD 4.4, LinuxThreads, 4G RAM, InnoDB Best Regards, Dino Fabrello - 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: /tmp datadir
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 19 Dec 2002, Dino Fabrello wrote: Mysql server is working good for 5-10 minutes and then changes directory to /tmp - it starts telling ERROR 1049: Unknown database 'test'. Restarting the server helps for next 5-10 minutes. There are no errors in error-log, query log etc. SHOW VARIABLES still telling datadir from cnf file. Does anybody faced with this bug? System: FreeBSD 4.4, LinuxThreads, 4G RAM, InnoDB Recompile MySQL with -DHAVE_BROKEN_REALPATH and this problem should go away. The problem is that FreeBSDs realpath() syscall is not thread-safe. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+AbhsSVDhKrJykfIRAvCWAJ4uPyERuE7Ta4abLDGQlUqlgqVIuQCeK7dK x2yrQMpNs5+T7CZarxariKs= =Nqtt -END PGP SIGNATURE- - 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: /tmp datadir
Mysql server is working good for 5-10 minutes and then changes directory to /tmp - it starts telling ERROR 1049: Unknown database 'test'. Restarting the server helps for next 5-10 minutes. There are no errors in error-log, query log etc. SHOW VARIABLES still telling datadir from cnf file. Does anybody faced with this bug? System: FreeBSD 4.4, LinuxThreads, 4G RAM, InnoDB use the ports, Luke... Compile with default options: cd /usr/ports/databases/mysql323-server make all install LinuxThreads on FreeBSD is a poor choice. Use the native ones. - 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
mysqlhotcopy segfaults with perl 5.8
mysqlhotcopy always worked fine with perl 5.6.1 (debian woody), but i had
to upgrade to perl 5.8 and now it croaks and dies with a segfault.
the problem exists only with mysqlhotcopy; other db-connections with
perl 5.8 and DBI/DBD::mysql are no problem.
at http://volltext.net/mysql/strace.txt you find the full output
of strace (71kB)
snipped output of perl -d mysqlhotcopy -n fwwb./UserDepot/ /var/tmp/
---8-
[...]
921:my (%hash, $i, $h);
DBI::_new_handle(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-stdio/DBI.
:922):
922:$i = tie%hash, $class, $attr; # ref to inner hash (for driver
DBI::st::TIEHASH(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-stdio/DBI.
:932):
932:sub DBI::st::TIEHASH { bless $_[1] = $_[0] };
DBI::_new_handle(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-stdio/DBI.
:923):
923:$h = bless \%hash, $class; # ref to outer hash (for applic
ion)
DBI::_new_handle(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-stdio/DBI.
:926):
926:DBI::_setup_handle($h, $imp_class, $parent, $imp_data);
DBI::_new_handle(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-stdio/DBI.
:928):
928:return $h unless wantarray;
DBD::mysql::dr::connect(/usr/local/www/perl/lib/site_perl/5.8.0/i686-linux-std
/DBD/mysql.pm:128):
128:DBD::mysql::db::_login($this, $dsn, $username, $password)
129: or $this = undef;
[debugging output stops here]
---8-
How-To-Repeat:
replace /usr/bin/perl (version 5.6.1 as packaged with debian) with a
selfcompiled perl 5.8
Fix:
point shebang in mysqlhotcopy script back to perl version 5.6.1
Submitter-Id: submitter ID
Originator:root
Organization:
MySQL support: none
Synopsis: mysqlhotcopy segfaults with perl 5.8
Severity: serious
Priority: medium
Category: mysql
Class: sw-bug
Release: mysql-3.23.52 (Source distribution)
Server: /usr/local/mysql/bin/mysqladmin Ver 8.23 Distrib 3.23.52, for pc-linux-gnu
on i686
Copyright (C) 2000 MySQL AB MySQL Finland AB TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 3.23.52-log
Protocol version10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 1 day 15 hours 5 sec
Threads: 7 Questions: 2173091 Slow queries: 5 Opens: 24719 Flush tables: 1 Open
tables: 64 Queries per second avg: 15.477
Environment:
perl is 5.8 (compiled from source)
DBI : 1.30
DBD::mysql : 2.1020
machine is a hp netserver lp2000r (dual pIII 1133MHz)
opsys is debian woody
System: Linux kaelte.wirtschaftsblatt.at 2.4.19 #3 SMP Wed Oct 2 14:10:50 CEST 2002
i686 unknown
Architecture: i686
Some paths: /usr/local/bin/perl /usr/bin/make /usr/bin/gcc /usr/bin/cc
GCC: Reading specs from /usr/lib/gcc-lib/i386-linux/2.95.4/specs
gcc version 2.95.4 20011002 (Debian prerelease)
Compilation info: CC='gcc' CFLAGS='' CXX='g++' CXXFLAGS='' LDFLAGS=''
LIBC:
lrwxrwxrwx1 root root 13 6. Nov 12:52 /lib/libc.so.6 -
libc-2.2.5.so
-rwxr-xr-x1 root root 1145456 18. Sep 04:50 /lib/libc-2.2.5.so
-rw-r--r--1 root root 2376426 18. Sep 04:51 /usr/lib/libc.a
-rw-r--r--1 root root 178 18. Sep 04:51 /usr/lib/libc.so
Configure command: ./configure --prefix=/usr/local/mysql
--localstatedir=/usr/local/data/mysql --with-mysql-user=mysql --with-gnu-ld
--with-charset=german1 --enable-assembler
-
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: Password function not working with latest 4.1 tree
-BEGIN PGP SIGNED MESSAGE-
Hash: SHA1
On Thu, 19 Dec 2002 [EMAIL PROTECTED] wrote:
Description:
with the latest 4.1 tree (from today) the PASSWORD() function returns random
alpha-numeric text /[a-f0-9]/
45 characters in length (which is too long for a password string). The string
always starts with a * (asterisk).
example:*95144feaa0f433f3f62c29382697a1e631b283f860f0
How-To-Repeat:
Using latest BK 4.1 tree, SELECT PASSWORD('something');
Yes, that's intentional - we have changed this in 4.1, but it's not
documented in the manual yet. A quote from the developer working on that
code:
[SNIP]
I've send rather large piece of documentation about it to docs but I
belive they still did not get into the manual.
A lot of changes about MySQL authentication changes are need to be done so
I can understand why it is not that quick.
Also it is not really random, but has some randomity in it. It is whole
idea!
Now password(1) returns different strings all the time - so if you have
many users you can't search for matching hashes for most simple passwords
as you previously could.
password() function is designed especially to provide password hash to be
used MySQL and it still does so. Some people used it for password
encryption instead of MD5() or SHA1(). These people are wrong of course
:)
But not being so cruel we left OLD_PASSWORD() function for them which
generates old password hash.
Also --old-passwords startup option can help if you would like to run in
4.0 compatible password generation mode.
[SNIP]
Hope that helps!
Bye,
LenZ
- --
For technical support contracts, visit https://order.mysql.com/?ref=mlgr
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED]
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer
/_/ /_/\_, /___/\___\_\___/ Hamburg, Germany
___/ www.mysql.com
-BEGIN PGP SIGNATURE-
Version: GnuPG v1.2.0 (GNU/Linux)
Comment: For info see http://quantumlab.net/pine_privacy_guard/
iD8DBQE+Ab1fSVDhKrJykfIRAi+DAJ9CC9qQAGXS3L7QP5lVPcHwWUO9CgCeIdlX
pouFFLTHUvDidhcLYTpfDXk=
=W3v0
-END PGP SIGNATURE-
-
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 3.23.54-max GLIBC errors
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Wed, 18 Dec 2002, Nicholas Gaugler wrote: Thanks, I downloaded it and it all works fine now, the whole bin directory is statically linked. Thanks for the confirmation! Glad it works now. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+Ab22SVDhKrJykfIRAuPcAJ4uSFdx9lFfbcnKSvnJDIYtp6gQXACfU7lY c7c+e4czYhd4AcyCmGm1awc= =Lz9A -END PGP SIGNATURE- - 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: really slow query results --- SOLVED
Hi, The performance problem on his query was due to the missing index on join columns. However I was assuming using table1 INNER JOIN table2 ON condition would have helped the optimiser to choose the tables on which it had to perform the join. Regards, Jocelyn - Original Message - From: Harald Fuchs [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 12:40 PM Subject: Re: really slow query results --- SOLVED In article [EMAIL PROTECTED], Dan Nelson [EMAIL PROTECTED] writes: INNER JOIN and WHERE do the same thing: * `INNER JOIN' and `,' (comma) are semantically equivalent. Both do a full join between the tables used. Normally, you specify how the tables should be linked in the WHERE condition. That's what I always thought, but this must be wrong when Joseph noticed a difference in performance. Any experts out there with comments on that? [Filter fodder: 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 - 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 Security Flaw - Dropped Databases
Hi! Mark Hi, Gary I'd like to add to the security flaw thread with my own experience. Gary I have been hosting MySQL databases for over 2 years and on a few occasions Gary have had user databases disappear. Gary Last month one of my admin databases was dropped. The only user Gary who has Gary access to that database is root (me) and even after double checking all my Gary scripts/code and database/table permissions I was unable to determine how it Gary was done. I was able to track down the culprit and asked him how he did it. Gary He replied: GaryWhen use MySQL-Front(version 2.5) as client to connect to 4.x version Gary MySQL server,any users(even without any granted rights) can drop any Gary databases. I guess there is a horrible security hole exist in MySQL 4.x Gary version. Gary I don't really understand this client side exploit, nevertheless, the Gary database WAS dropped and that is how he told me he did it. Is this a red Gary herring (false lead)? If it is true, is this exploit being addressed? I checked this up and notice that your user is right. In MySQL we have two different ways to drop a database: With 'DROP DATABASE database_name'. Through the depricated client function 'mysql_drop_db()'. The first case works correct but in the second case the grant check is not done. I tracked this down to a merge I did between the 4.0 and 4.1 code in September last year :( The reson you could not repeat this is that MySQLFront() uses the old mysql_drop_db() call while you probably tested this with some client which uses the new way to drop a database. I will fix this at once for next 4.0 release. Thanks for reporting this! Regards, Monty -- MySQL 2003 Users Conference - http://www.mysql.com/events/uc2003/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Mr. Michael Widenius [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, CTO /_/ /_/\_, /___/\___\_\___/ Helsinki, Finland ___/ www.mysql.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
Storage Requirements Not Equal to Actual Disk Space Used...
I have a table with 17,168,035 records. I have the following column types and I read about the following storage requirements for each column: (1) INT Column - Should take up 4 bytes each (1) MEDIUMINT Column- Should take up 3 bytes each (1) DATE Column - Should take up 3 bytes each (1) VARCHAR Column - Should take up Length + 1 bytes each The INT Column should take up 4 * 17,168,035 = 68,672,140 bytes The MEDIUMINT Column should take up 3 * 17,168,035 = 51,504,105 bytes The DATE Column should take up 3 * 17,168,035 = 51,504,105 bytes I ran a query for the VARCHAR column and it has a total of 141,485,442 characters plus the additional 17,168,035 characters to store the string length. I see this column should be taking up 158,653,477 bytes. Total I would think my table should be somewhere around 330,333,827 byes, but rather it is 444,669,952 bytes. To be over 100 meg off, I must be doing something wrong. I did try repairing the table also to make sure I was reading the right file size, but it stays the same. Can anyone tell me what I am doing wrong? Thank you in advance, Andrew - 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: Storage Requirements Not Equal to Actual Disk Space Used...
Did you optimze the table first? warning optimize could take long (and i think the table is locked while doing it) Gr, Wico At 08:26 19-12-02 -0500, Andrew Kuebler wrote: I have a table with 17,168,035 records. I have the following column types and I read about the following storage requirements for each column: (1) INT Column - Should take up 4 bytes each (1) MEDIUMINT Column- Should take up 3 bytes each (1) DATE Column - Should take up 3 bytes each (1) VARCHAR Column - Should take up Length + 1 bytes each The INT Column should take up 4 * 17,168,035 = 68,672,140 bytes The MEDIUMINT Column should take up 3 * 17,168,035 = 51,504,105 bytes The DATE Column should take up 3 * 17,168,035 = 51,504,105 bytes I ran a query for the VARCHAR column and it has a total of 141,485,442 characters plus the additional 17,168,035 characters to store the string length. I see this column should be taking up 158,653,477 bytes. Total I would think my table should be somewhere around 330,333,827 byes, but rather it is 444,669,952 bytes. To be over 100 meg off, I must be doing something wrong. I did try repairing the table also to make sure I was reading the right file size, but it stays the same. Can anyone tell me what I am doing wrong? Thank you in advance, Andrew - 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: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)
Why isn't Seq_ID not an unsigned int? Primary key should always be something generated by the system that has no other significance than being a primary key. If there actually is a seq_id piece of data that has some other significance, I wouldn't use it to link all your data. Then you don't have to have a compound index (Seq_ID, Homolog_PID) in your NewSequence_Homolog table. I could understand why you may need to use text for the Comment, but would char(255) be good enough for the title? Then you could split Comment out to a separate table so you can get fixed length records. If comments should always be displayed with the data, then perhaps putting a flag field in the table to indicate there are comments. Then comments can be viewed individually. Your simple left join does seem to be taking quite a long time. I wouldn't consider myself an expert in MySQL, but I would think that your index key length of 50 has to be slowing things down. The left join example you have is also scanning an entire table of 2676711 records. I don't know how big the table is in disk space, but I would guess the entire table is not cached in RAM. A few weeks back I did read something about a company that was doing modeling on grain falling in a silo or something very complex like fluid dynamics. They were having severe performance issue where it would take 10 hours to model something. They used all the various Unix tools to determine where the bottleneck was (disk, memory, or CPU). It was RAM and disk I/O (due to low RAM) that was slowing things down. They started adding disks for scratch areas and virtual memory and made sure there were no hot disks. They knocked about two hours off of the time. They then added a whole bunch of RAM so the entire table could be loaded into RAM and got things down to something like 17 minutes. Regardless of whether you are using Oracle or MySQL, you are still limited by the hardware you are running it on. Finding what the bottleneck is (disk, memory, cpu, or network) is the key. On Wednesday, December 18, 2002, at 04:17 PM, Qunfeng Dong wrote: This NewSequence table is used to track some general info about sequence. Notice I have to use text datatype to describe Comment and Seq_Title fields; therefore I have to use varchar for other string fields. In addition, the Seq_ID is not numerical. BTW, I found indexing on Seq_Type. Organism which are very repeative still helps with accessing. This table has 2676711 rows. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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
Case sensitivity
Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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
username password issues with mysql
Hello ... my name is Brent ..and im using the latest version of mysql on a Freebsd box with apache php3 php4. My problem is that ive set a password for the root mysql user by doing: /usr/local/bin/mysqladmin -u root password 'x' then i installed phpMyAdmin ..and set the correct username password within phpMyAdmin's config file ..and it connects to mysql with no problems. However ..i wrote a form with php3 html ...to connect using the same username password and it will not connect. I know that the form is correct in its code...in that ...i moved the form to another server (running the same versions of mysql such) and it connected with no issues. Ive seen this problem on other servers that i have administerd in the past and have not been able to get it to work correctly without totally uninstalling and re-installing mysql. When i set a password in the form for root@localhost to connect to mysql i get the following error: Warning: MySQL Connection Failed: Access denied for user: 'root@localhost' (Using password: YES) in /usr/local/www/data/index.php3 on line 25 Warning: 0 is not a MySQL link index in /usr/local/www/data/index.php3 on line 26 to get around this issue temporarily i have set the root@localhost user with NO password ( i hate doing this as i know its not secure) Id rather not run mysql with skip grant tables ... whenever i add a user password to mysql and then set a form to use that UN PW ...it will not connect however if i take that user out and just use a root user with NO password ..it will work..can anyone give me a clue as to what i can do ..?? Any and ALL help is greatly appreciated.. thank you Brent - 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: Case sensitivity
You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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: Storage Requirements Not Equal to Actual Disk Space Used...
mysql needs a few more bytes per record for its internal use. Since you use a VARCHAR, you have dynamic length records; this is described in the manual: http://www.mysql.com/doc/en/Dynamic_format.html Regards, Joseph Bueno Andrew Kuebler wrote: I have a table with 17,168,035 records. I have the following column types and I read about the following storage requirements for each column: (1) INT Column- Should take up 4 bytes each (1) MEDIUMINT Column - Should take up 3 bytes each (1) DATE Column - Should take up 3 bytes each (1) VARCHAR Column- Should take up Length + 1 bytes each The INT Column should take up 4 * 17,168,035 = 68,672,140 bytes The MEDIUMINT Column should take up 3 * 17,168,035 = 51,504,105 bytes The DATE Column should take up 3 * 17,168,035 = 51,504,105 bytes I ran a query for the VARCHAR column and it has a total of 141,485,442 characters plus the additional 17,168,035 characters to store the string length. I see this column should be taking up 158,653,477 bytes. Total I would think my table should be somewhere around 330,333,827 byes, but rather it is 444,669,952 bytes. To be over 100 meg off, I must be doing something wrong. I did try repairing the table also to make sure I was reading the right file size, but it stays the same. Can anyone tell me what I am doing wrong? Thank you in advance, Andrew - 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: Case sensitivity
Thanks, Just worked it out with binary statement in my query. all fine. Would it be wise to use binary type table? didnt try that as i wanted a flexible table of type varchar to perserve space. what would be its impact? cheers. Adamji From: Mike Grabski [EMAIL PROTECTED] To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 2:29 PM Subject: RE: Case sensitivity You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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: Case sensitivity
See http://www.mysql.com/doc/en/CHAR.html or http://www.mysql.com/doc/en/BLOB.html . If your column types are CHAR or VARCHAR insert BINARY. If your column type is TEXT, change to BLOB. -- You said Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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: Case sensitivity
You can specify a varchar field as binary, for instance: field1 varchar(50) binary query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:50 AM To: Mike Grabski; [EMAIL PROTECTED] Subject: Re: Case sensitivity Thanks, Just worked it out with binary statement in my query. all fine. Would it be wise to use binary type table? didnt try that as i wanted a flexible table of type varchar to perserve space. what would be its impact? cheers. Adamji From: Mike Grabski [EMAIL PROTECTED] To: 'asp52' [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 2:29 PM Subject: RE: Case sensitivity You have two choices, you can use a binary datatype for the field, or force a binary comparison using the BINARY operator. http://www.mysql.com/doc/en/Case_Sensitivity_Operators.html http://www.mysql.com/doc/en/Column_types.html query, sql Mike -Original Message- From: asp52 [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 9:06 AM To: [EMAIL PROTECTED] Subject: Case sensitivity Hi, I have been searching tables based on search values which turn up tobe fine, however the results dont reflect case sensitivity. what i mean, if i run following query select field1 from table1 where name='text1'; this works fine but returns the same result set with text1 values of 'USER' or 'user'. i need to run the query which should run case sensitive. ie USER is different from user Thanks in advance who can throw some ligth Adamji - 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: Storage Requirements Not Equal to Actual Disk Space Used...
When you mentioned the number of bytes, is that just the data part of the table, or the index as well? You didn't mention if your schema was indexed one way or another. Cheers. -Dana -Original Message- From: Joseph Bueno [SMTP:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 8:44 AM To: Andrew Kuebler Cc: [EMAIL PROTECTED] Subject: Re: Storage Requirements Not Equal to Actual Disk Space Used... mysql needs a few more bytes per record for its internal use. Since you use a VARCHAR, you have dynamic length records; this is described in the manual: http://www.mysql.com/doc/en/Dynamic_format.html Regards, Joseph Bueno Andrew Kuebler wrote: I have a table with 17,168,035 records. I have the following column types and I read about the following storage requirements for each column: (1) INT Column - Should take up 4 bytes each (1) MEDIUMINT Column- Should take up 3 bytes each (1) DATE Column - Should take up 3 bytes each (1) VARCHAR Column - Should take up Length + 1 bytes each The INT Column should take up 4 * 17,168,035 = 68,672,140 bytes The MEDIUMINT Column should take up 3 * 17,168,035 = 51,504,105 bytes The DATE Column should take up 3 * 17,168,035 = 51,504,105 bytes I ran a query for the VARCHAR column and it has a total of 141,485,442 characters plus the additional 17,168,035 characters to store the string length. I see this column should be taking up 158,653,477 bytes. Total I would think my table should be somewhere around 330,333,827 byes, but rather it is 444,669,952 bytes. To be over 100 meg off, I must be doing something wrong. I did try repairing the table also to make sure I was reading the right file size, but it stays the same. Can anyone tell me what I am doing wrong? Thank you in advance, Andrew - 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 ** This email and any files transmitted with it are confidential and intended solely for the individual or entity to whom they are addressed. If you have received this email in error destroy it immediately. ** Wal-Mart Stores, Inc. Confidential ** - 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: Accessing last_insert_id problem.
At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Jeff MySQL, query, 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
Re: Missing values
replace missing value (NULL) as \N in your .txt file --- Gianluca Carnabuci [EMAIL PROTECTED] wrote: Hi, I've been trying to import a huge .txt file into a MySql table. In the .txt file, missing values are recorded as empty cells (it might be that there's some hidden character instead, but I wouldn't know how to figure that out). When I LOAD DATA INFILE, MySql writes the missing values as zeros, rather than nulls. I can't UPDATE these zeros as nulls after loading the data, because some of the data are actually zeros in the original .txt file. Do you have any suggestions? Gianluca - 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 you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
InnoDB tables and full text search
While creating InnoDB tables, I was not able to specify a fulltext column in the table creation sql. Is this a bug or a limitation with InnoDB ? There could be quite a few users who need FULLTEXT feature with InnoDB. - 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: Can MySQL handle 120 million records?
Hi Guys, I'm a lurker on this list but I have decided to come out of my shell for a moment. I a previous job I was the Oracle DBA for my development team. We had a persistent problem with Oracle corrupting the development database. Oracle had no idea with the problem was even after I sent them a copy of the data files. I eventually traced the problem to a flaky memory chip in the development database server. We replaced the chip, reinstalled Oracle and the problem went away. Now, the lessons learned from this story; No database server software can account for all possible conditions. Regardless of the database, you will have an event at some point in time that causes database corruption. It's like death and taxes. It's going to happen. Deal with it. Create a good backup strategy, a good disaster recovery plan and practice doing restores at least once a month. Expecting the software to save you from all situations is just a bad idea. John Griffin -Original Message- From: Michael She [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 3:36 PM To: Michael T. Babcock Cc: [EMAIL PROTECTED]; Muruganandam Subject: Re: Can MySQL handle 120 million records? I guess you can say I'm a follower. Other DB systems have been in use for years, so their reliability has been generally proven through use. It's good to know that a lot of people have had success with MySQL, but considering MySQL is the new comer, I'm still a little tepid! At 01:22 PM 12/18/2002 -0500, Michael T. Babcock wrote: Michael She wrote: X-MDRcpt-To: [EMAIL PROTECTED] X-Return-Path: [EMAIL PROTECTED] X-MDaemon-Deliver-To: [EMAIL PROTECTED] 2. Some of the comments in the mySQL manual... people losing data doing routine stuff like table optimizations, adding keys, etc. If a database is reliable, things like that shouldn't happen. Comments like those in the MySQL manual scared me. 1) Do you believe this doesn't ever happen with other 'enterprise' level DB systems? 2) What do you think Microsoft's pages would look like if they allowed arbitrary user comments (read their newsgroups ...) 3) Those reports should be filed as bugs to be fixed, not added as comments in the manual. -- Michael She : [EMAIL PROTECTED] Mobile : (519) 589-7309 WWW Homepage : http://www.binaryio.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
help me, please
Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
Programmatic MySQL backup in Perl
Hi, is there an easy way to programmatically backup MySQL database tables using Perl? My Perl code will be running on a machine remote to the MySQL server. Ta, Jeff - 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 Netware vs Linux
Hi, We are currently running MySQL on RedHat Linux with great success. However, we are upgrading our Netware servers to Netware 6. I know MySQL ports to Netware 6, but I am wondering how MySQL actually runs on Netware 6. Is there anyone out there running MySQL on Netware 6? If so, how's going? Is it stable? How does stack up against Linux? Would you recommend switching from Linux to Netware? Any Responses would be great. Thanks in advance, Neal Dawson [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: replication and auto_increment
hi, here is the problem ... think about this : serverR1 / serverR2 : replication servers serverWWW : webserver serverWWW reads/writes data from/to serverR1 serverR2 replicate serverR1 data serverR1 crashes serverWWW reads/writes data from/to serverR2 serverR1 (up) replicate serverR2 data the goal of this architecture is to prevent to lose data i don't know if it's possible with mysql (that depends of internal auto_increment id management) -Message d'origine- De : gerald_clark [mailto:[EMAIL PROTECTED]] Envoye : jeudi 19 decembre 2002 16:59 A : [EMAIL PROTECTED] Objet : Re: replication and auto_increment Do not allow inserts on both machines. Maxime LEMAIRE wrote: hi, i would like to create a dual master/slave replication with mysql i think i wouldn't have any problem but i wonder how mysql manage auto_increment id ... how it works ? maxime - 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
[Error] Mysql server has gone away 3.23.53a
Hi all, I've some problem with my Mysql servers since the beginning of this week. I've got plenty of error like this : Failed to connect to database: Error: Access denied for user: ´[EMAIL PROTECTED]´ to database ´DB1´ MySQL server has gone away Error Nø1, please contact webmaster ... In fact, clients are C cgi compiled with libmysql ( in this package MySQL-devel-3.23.49-1.i386.rpm ) The Cgi is ok and have worked well since several mounths. Error Nø1 : connection mysql ok, can't execute my query It seems that mysql disconnect me before i can execute my query. But query is just after the connection in the code ... :( Server : Linux Redhat 2 * PIII 1 Ghz 1Go ram Scsi raid 5 mysql-3.23.53a-pc-linux-gnu-i686.tar.gz ( binary ) As you can see, i connect to my server via a 100Mb LAN. The LAN is ok, all ethernet card are 100Mb Full Duplex without any collision or packet dropped. Exemple : RX packets:259950587 errors:0 dropped:0 overruns:0 frame:0 TX packets:1809122347 errors:0 dropped:0 overruns:0 carrier:0 collisions:0 lg file transmission:100 RX bytes:3130833186 (2985.7 Mb) TX bytes:1637489037 (1561.6 Mb) Here's the my.cnf : # The MySQL server [mysqld] port= 3306 socket = /tmp/mysql.sock skip-locking skip-name-resolve set-variable= key_buffer=256M set-variable= back_log=150 set-variable= record_buffer=1M set-variable= sort_buffer=1M set-variable= max_allowed_packet=1M set-variable= thread_stack=128K set-variable= max_connections=700 set-variable= max_connect_errors=100 set-variable= table_cache=512 set-variable= net_read_timeout=180 set-variable= net_write_timeout=180 set-variable= wait_timeout=3600 set-variable= thread_concurrency=4 I can't see anything in the error log. Mysqld is still alive and still responding 99% of the time. Status : Threads: 3 Questions: 3065350 Slow queries: 46 Opens: 91 Flush tables: 1 Open tables: 66 Queries per second avg: 9.052 Show Status : Aborted_clients 4401 Aborted_connects374 Bytes_received332129641 Bytes_sent 1691254415 [...] Created_tmp_disk_tables 0 Created_tmp_tables211 Created_tmp_files 1290 Handler_delete | 11135 | Handler_read_first | 226| Handler_read_key | 2005755| Handler_read_next| 1714626349 | Handler_read_prev| 0 | Handler_read_rnd | 2613047| Handler_read_rnd_next| 62634691 | Handler_update | 357300 | Handler_write| 304440 | Key_blocks_used | 62869 | Key_read_requests| 97082717 | Key_reads| 60114 | Key_write_requests | 487582 | Key_writes | 392106 | Max_used_connections | 23 | Not_flushed_key_blocks | 0 | Not_flushed_delayed_rows | 0 | Open_tables | 66 | Open_files | 102| Open_streams | 0 | Opened_tables| 91 Questions| 3065612| Select_full_join | 0 | Select_full_range_join | 0 | Select_range | 600| Select_range_check | 0 | Select_scan | 51701 | Slave_running| OFF| Slave_open_temp_tables | 0 | Slow_launch_threads | 0 | Slow_queries | 46 | Sort_merge_passes| 645| Sort_range | 99887 | Sort_rows| 2613047| Sort_scan| 20030 | Table_locks_immediate| 1841001| Table_locks_waited | 1977 | Threads_cached | 0 | Threads_created | 1226346| Threads_connected| 2 | Threads_running | 1 | Uptime | 338664 Extract of the mysql.db table : 192.168.10.4 | DB1 | JForvC15 | Y ( select priv ... ) Thanks to all. 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
Re: Re: Can MySQL handle 120 million records?
I'm a lurker on this list but I have decided to come out of my shell for a moment. I a previous job I was the Oracle DBA for my development team. We had a persistent problem with Oracle corrupting the development database. Oracle had no idea with the problem was even after I sent them a copy of the data files. I eventually traced the problem to a flaky memory chip in the development database server. We replaced the chip, reinstalled Oracle and the problem went away. I propose an addition to the MySQL manual in order to promote general good sys adminlyness. A good way to weed out these problems early and with minimal grief is by stress testing a server before it is ever purposed. Most people try to develop their own stress test schemes. Don't bother, there's a very good one out there called CTCS. I believe VA Linux developed it in-house to stress test servers that they had just built for customers. After running CTCS on 7 x86 servers we recently received (NOT from VA Linux), we discovered 2 of them had bad RAM, and one of them had a faulty RAID controller(!). The disturbing part is these machines appeared perfectly functional, even held up under high load for weeks sometimes before crashing. If I hadn't found CTCS when I did our agony probably would have been far more prolonged. We learned our lesson. Now it's policy that machines must stand 48 hours of CTCS before being put into any role at all. Presumbably your vendors do this too, but its a good idea to do it after UPS hands you the box too, for reasons that should be obvious. -- Michael Bacarella | Netgraft Corp | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | http://netgraft.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
Change log
Hello there, I would like to know where I can find the change log for the new versions of MySQL. For example, just recently .54 was released, and then .54a. There is a ChangeLog file in the distribution, but at least to me, it is TOTALLY incomprehensible. Isn't there a simple file that says something like: Verion 3.23.54a: - fixed this, - improved that, etc. Thanks, Maximo. - 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: help me, please
select * from your_table order by no desc limit 0,1 -Original Message- From: mustakim abas [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 12:07 PM To: [EMAIL PROTECTED] Subject: help me, please Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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
Re: InnoDB tables and full text search
On Thu, 19 Dec 2002, Deepa wrote: While creating InnoDB tables, I was not able to specify a fulltext column in the table creation sql. Is this a bug or a limitation with InnoDB ? There could be quite a few users who need FULLTEXT feature with InnoDB. It's a feature :-( FULLTEXT has not yet been implemented for InnoDB tables. Thomas -- 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: help me, please
On Thu, 19 Dec 2002, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Provided I understand your question, then I would suggest: SELECT * FROM mytable WHERE No somevalue LIMIT 1; Then 'somevalue' would be the value of 'No' from your last query. Thomas -- 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: Programmatic MySQL backup in Perl
Jeff,
Here is a piece of my backup script. Its been modified from Unix world to
windoze world ( because at this point I
am working in that environment - seeking to return to Unix if anyone has a
gig )
=pod
=head1 Script: mysql_backup.pl
=head2
=over 4
=item 1
This script builds a db dump file to s:\mysql_dump
The dump file is named for the date
In addition this script backs up all script files in cgi-bin to s drive for
nightly backup
=back
=cut
use HTTP::Date;
my ($time, $date_string, $cmd, $s);
$date_string = time2str($time);# Format as GMT ASCII time
$date_string =~ s/\s/\_/g;
$date_string =~ s/[^a-zA-Z0-9]//g;
$cmd = build_cmd('my_mysqldb');
do_cmd($cmd);
exit(0);
sub build_cmd{
my ($name) = @_;
my $s = $date_string . $name . '.dmp';
my $cmd = qq!mysqldump -c --add-drop-table -uroot -psirron $name
s:\\mysql_backup\\!;
$cmd .= $s;
return($cmd);
}
sub do_cmd{
my ($cmd) = @_;
if (!(system ($cmd) == 0)){
warn could not do system $cmd - $!;
}
}
-Original Message-
From: Jeff Snoxell [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 8:16 AM
To: [EMAIL PROTECTED]
Subject: Programmatic MySQL backup in Perl
Hi,
is there an easy way to programmatically backup MySQL database tables using
Perl? My Perl code will be running on a machine remote to the MySQL server.
Ta,
Jeff
-
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
INSERT... SELECT Statement fails when...
When I run an INSERT. SELECT query and I am inserting the records into a new table that has a UNIQUE key, the statement will fail as soon as it hits a Duplicate entry error. No further entries will be inserted. The only way I know to get around this is to pull the entire column(s) into memory and insert them back in one by one with a repeating INSERT statement. Can anyone tell me the easy way to do this? (I assume there must be a better way) Thanks! Andrew - 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: Programmatic MySQL backup in Perl
The way I do it is to call mysqldump.exe from perl, granted it's not the most elegant
way but it's certainly the fastest (to code). Just do:
---
# name or IP address of dbmachine
my $DB_server = localhost.localdomain.tld
# db login authorised to do db dump
my $DB_User = sa
# location and name of dumpfile
my $DB_dumpfile = /tmp/db_dumps/my_dump.sql
# define command with parameters to dump the database
my $cmd = mysqldump --add-locks --add-drop-tables --all-databases --lock-tables
--host=$DB_Server --user=$DB_User $DB_dumpfile;
# run command to dump the database
my $retval = system($cmd);
# check the return value of the dump command
if ($retval != 0)
{
# there was a problem do something about it...
}
---
-Pete
P.S.: This is not production grade code, just something I threw together for you while
eating a sandwich :)
sql
---
-Original Message-
From: Jeff Snoxell [mailto:[EMAIL PROTECTED]]
Sent: Thursday, December 19, 2002 11:16
To: [EMAIL PROTECTED]
Subject: Programmatic MySQL backup in Perl
Hi,
is there an easy way to programmatically backup MySQL database tables using
Perl? My Perl code will be running on a machine remote to the MySQL server.
Ta,
Jeff
-
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: Accessing last_insert_id problem.
At 15:13 + 12/19/02, Jeff Snoxell wrote: At 09:46 19/12/02 -0500, you wrote: Jeff Snoxell wrote: Nope. That doesn't do it either! I go: TRUNCATE TABLE my_table Are you using InnoDB tables? You'll have to do something akin to ALTER TABLE my_table AUTO_INCREMENT=1 ... at least according to Paul ... :) No, I'm using MyISAM I believe. Can you check for sure. With MyISAM, TRUNCATE TABLE should always reset the counter, I believe. Here's an example: mysql CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY); Query OK, 0 rows affected (0.00 sec) mysql SHOW CREATE TABLE t; +---+--+ | Table | Create Table | +---+--+ | t | CREATE TABLE `t` ( `i` int(11) NOT NULL auto_increment, PRIMARY KEY (`i`) ) TYPE=MyISAM | +---+--+ 1 row in set (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.01 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 0 rows affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) With InnoDB, what I observe is that you have to issue an ALTER TABLE after truncating the table to force the counter back to 1: mysql CREATE TABLE t (i INT NOT NULL AUTO_INCREMENT PRIMARY KEY) TYPE = INNODB; Query OK, 0 rows affected (0.02 sec) mysql SHOW CREATE TABLE t; +---+--+ | Table | Create Table | +---+--+ | t | CREATE TABLE `t` ( `i` int(11) NOT NULL auto_increment, PRIMARY KEY (`i`) ) TYPE=InnoDB | +---+--+ 1 row in set (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | | 2 | +---+ 2 rows in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 2 rows affected (0.00 sec) mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 3 | +---+ 1 row in set (0.00 sec) mysql TRUNCATE TABLE t; Query OK, 1 row affected (0.00 sec) mysql ALTER TABLE t AUTO_INCREMENT = 1; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql INSERT INTO t SET i = NULL; Query OK, 1 row affected (0.00 sec) mysql SELECT * FROM t; +---+ | i | +---+ | 1 | +---+ 1 row in set (0.00 sec) Jeff MySQL, query, 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
Re: Re[2]: Can MySQL handle 120 million records?
At 13:08 +0100 12/19/02, Harald Fuchs wrote: In article [EMAIL PROTECTED], Dyego Souza do Carmo [EMAIL PROTECTED] writes: Dobrý den, quarta-feira, 18 de dezembro de 2002, 13:10:07, napsal jste: MTB Qunfeng Dong wrote: Another thing, with some linux system, there is a size limit for file. MySQL seems to store each of its table as single file. You need to choose a file system without that limit. MTB Just use InnoDB tables for these files and you won't have a problem MTB AFAIK; you can have multiple 2G files that are used to create one big MTB table if you like (any InnoDB people want to comment on actual limits?) Use the InnoDB tables with the raw devices ( ex: allow innodb use a /dev/sdxx or /dev/hdxx to write tablespace ), the speed is better, MySQL don't loses time with the filesystem. In my production database , i have a tablespace with 130G ( with raw diveces on SCSI disks) and the performance is good :) /dev/sdxx or /dev/hdxx are _not_ raw devices; they are disk partitions without a file system, but still subject to the Linux buffer cache. man 8 raw says how to bind a disk partition to a true raw device (/dev/raw/rawX). And yes, those beasts work fine with InnoDB. The InnoDB documentation refers to partitions as raw devices, so that's how we talk about them, 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: RE: NOW()-TIMESTAMP does not return accurate results
At 23:39 -0800 12/16/02, Troy Kruthoff wrote: Description: Invalid reporting of date calc How-To-Repeat: note: SESSIONTS is TIMESTAMP type SELECT (NOW()-SESSIONTS) FROM WEBSESSIONS; What leads you to expect that this should yield any useful result? According to the documentation of the NOW() function: Returns the current date and time as a value in '-MM-DD HH:MM:SS' or MMDDHHMMSS format, depending on whether the function is used in a string or numeric context Assuming SESSIONTS is of type TIMESTAMP, SELECT (NOW()-SESSIONTS) should return the differences in seconds But it does not, in fact I can not determine what MySQL is returning, it does not appear to be any accurate measurement of time. Troy --- Outgoing mail is certified Virus Free. Checked by AVG anti-virus system (http://www.grisoft.com). Version: 6.0.431 / Virus Database: 242 - Release Date: 12/17/2002 - 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 tables and full text search
At 10:35 -0500 12/19/02, Deepa wrote: While creating InnoDB tables, I was not able to specify a fulltext column in the table creation sql. Is this a bug or a limitation with InnoDB ? There could be quite a few users who need FULLTEXT feature with InnoDB. It's like the manual says: FULLTEXT is only for MyISAM tables. http://www.mysql.com/doc/en/Fulltext_Search.html 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: re: Replication of only a couple of databases
On Wednesday 18 December 2002 17:18, you wrote: At 16:57 18-12-02 +0200, you wrote: On Wednesday 18 December 2002 11:36, Wico de Leeuw wrote: I've setup replication with a master-slave. But i only want to replicate some databases so i'd put in my.cnf on the slave (it's being used, checked that) replicate-wild-do-table=adb.% (it has to catch cross updates to) I also tried to replicate-do-database=adb (in different combinations) What i have found: (replicate-wild-do-table=adb.%) create/drop/insert/delete with current db adb works, replace isn't working Replace works fine for me with 4.0.6 master and 4.0.5 slave. Could you provide a repeatable test case? use adb; update atable set name = 'test'; the query showsup in the logs but values aren't changed It is already fixed in 4.0.6 -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: Recover deleted records
On Wednesday 18 December 2002 16:49, Alexis Antonakis wrote: I posted this a few days ago, but haven't seen a reply as yet, and I'm getting quite desperate now!! I've managed to delete all the records in a table, well haven't we all done it sometime!, and was wondering whether it's possible to recover them. I notice that the .MYD and .MYI files still appear to be showing the original data. Nope. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Egor Egorov / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.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: help me, please
At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? - 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
pull all plugs out for mysql performance?
Hello. I am setting up a quad xeon server that is dedicated for mysql. All access will be via jdbc. Are there any gotchas or performance bottlenecks in the configuration that I can change to insure that the full capabilities of this hardware are available for mysql? Thanks! Phillip - 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 Netware vs Linux
Neal, - Original Message - From: Neal Dawson [EMAIL PROTECTED] Newsgroups: mailing.database.mysql Sent: Thursday, December 19, 2002 7:04 PM Subject: MySQL Netware vs Linux Hi, We are currently running MySQL on RedHat Linux with great success. However, we are upgrading our Netware servers to Netware 6. I know MySQL ports to Netware 6, but I am wondering how MySQL actually runs on Netware 6. Is there anyone out there running MySQL on Netware 6? If so, how's going? Is it stable? How does stack up against Linux? Would you recommend switching from Linux to Netware? Any Responses would be great. the newsgroup news://developer-forums.novell.com/novell.devsup.mysql is probably the best source of information. Thanks in advance, Neal Dawson [EMAIL PROTECTED] 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 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
Making up for lack of subqueries (-or- what the heck am I doing?)
Couldn't figure out what the option FOR UPDATE does in a SELECT statement unless it's just to lock the table. Spent the last few hours trying to get subqueries to work only to find that the reason they're not working is not just due to my abominable syntax. What I'm doing is getting messages from an NNTP server. First, I download all article numbers into a text file and the LOAD DATA FROM INFILE IGNORE into the database which already has article numbers plus headers and bodies. I have found this to be _infinitely_ faster than taking each article number, doing a SELECT to see if it is in the main table, and an INSERT if it is not (we're talkin' light years faster). I thought that if I then create a foreign_key table with LOAD DATA FROM INFILE and just suck in those article numbers, I could just update the main table column (is_expired BOOL) by doing an update with an inner join between the main_table and the foreign_kay table. (Actually setting 'is_expired' to true for all articles and then setting it to false for any article numbers in the foreign_key table. Whew! Sorry that was so long-winded. Anybody figure out what the heck I'm trying to say? And if I may go so far, how I should do this? Thank you, - Zeno - 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()-TIMESTAMP does not return accurate results
At 9:36 -0800 12/19/02, Troy Kruthoff wrote: At 23:39 -0800 12/16/02, Troy Kruthoff wrote: Description: Invalid reporting of date calc How-To-Repeat: note: SESSIONTS is TIMESTAMP type SELECT (NOW()-SESSIONTS) FROM WEBSESSIONS; What leads you to expect that this should yield any useful result? According to the documentation of the NOW() function: Returns the current date and time as a value in '-MM-DD HH:MM:SS' or MMDDHHMMSS format, depending on whether the function is used in a string or numeric context MMDDHHMMSS is not a value in seconds. It's a date and time value in numeric form. Assuming SESSIONTS is of type TIMESTAMP, SELECT (NOW()-SESSIONTS) should return the differences in seconds But it does not, in fact I can not determine what it us returning, it does not appear to be any accurate measurement of time. Troy To do what you want, it depends on whether or not any of your values are earlier than 1970. If not, then you can use UNIX_TIMETAMP(NOW()) - UNIX_TIMESTAMP(SESSIONTS) Otherwise you'll need to bust up the values into date parts and time parts and the expression is more complex: ((TO_DAYS(NOW()) - TO_DAYS(SESSIONTS)) * 24*60*60) + TIME_TO_SEC(NOW()) - TIME_TO_SEC(SESSIONTS) Solutions above adapted from MySQL Cookbook, pp 256-257, I hope I didn't goof them up. :-) - 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 Netware vs Linux
In the last episode (Dec 19), Neal Dawson said: We are currently running MySQL on RedHat Linux with great success. However, we are upgrading our Netware servers to Netware 6. I know MySQL ports to Netware 6, but I am wondering how MySQL actually runs on Netware 6. Is there anyone out there running MySQL on Netware 6? If so, how's going? Is it stable? How does stack up against Linux? Would you recommend switching from Linux to Netware? Any Responses would be great. MySQL on Netware is still only available as a Leading Edge (alpha) download, so there are probably very few people running it yet. So far I haven't heard too many problems, but you can check the novell.devsup.mysql newsgroup on developer-forums.novell.com. If they end up bundling MySQL with Netware 6.1, you can bet it's going to be stable :) LE#182 at http://developer.novell.com/ndk/leadedge.htm is a combo pack of MySQL, Apache, and PHP, but I'm sure you can just install the MySQL bit. -- Dan Nelson [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: Change log
At 15:00 -0300 12/19/02, Maximo Migliari wrote: Hello there, I would like to know where I can find the change log for the new versions of MySQL. For example, just recently .54 was released, and then .54a. There is a ChangeLog file in the distribution, but at least to me, it is TOTALLY incomprehensible. Isn't there a simple file that says something like: Verion 3.23.54a: - fixed this, - improved that, etc. Thanks, Maximo. Check the News section in the online manual: http://www.mysql.com/doc/en/News.html 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: Missing values
At 11:01 +0100 12/19/02, Gianluca Carnabuci wrote: Hi, I've been trying to import a huge .txt file into a MySql table. In the .txt file, missing values are recorded as empty cells (it might be that there's some hidden character instead, but I wouldn't know how to figure that out). When I LOAD DATA INFILE, MySql writes the missing values as zeros, rather than nulls. I can't UPDATE these zeros as nulls after loading the data, because some of the data are actually zeros in the original .txt file. Do you have any suggestions? Gianluca Hmm, that's kind of difficult. You say there might be some hidden characters, but you don't know for sure. How could anyone else know, then? :-) If you can determine either that there is some hidden character, or that the fields are in fact empty, you might try preprocessing your file to replace them with \N (backslash capital-N, no quotes), which LOAD DATA will interpret as a NULL values. - 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: SELECT and UPDATE at the same time?
Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? Hello again, I'm selecting a group of records from my database. I then loop through the selected records and do some work based on what I find. But what I also want to do as I interrogate each record is update some of its fields with new values... but won't that screw up the outer loop? I mean if I try to execute a query whilst looping around the result set of a former query will I not screw up my result set that I'm looping through? Also, is it possible to update specific fields of certain records within a SELECT query? ie can I do something like this: SELECT * FROM my_table WHERE Age 50 AND UPDATE Status = OLD Ta, Jeff - 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 To unsubscribe, e-mail 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
Database type question
I am looking at an application that will be could be potentially writing hundreds of records a second (syslog) to a MySQL database. I expect to reach millions of records (if not billions) in time. I will be doing a variety of types of queries on the data which will be anything from summarization of certain types of data to full pattern matching. So, my question is which of the database formats would be best suited for this type of environment? The MyISAM seem to work well on receiving the data, but the InnoDB database style seems better suited for the queries. Any ideas anyone? Richard - 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[2]: Can MySQL handle 120 million records?
At 13:08 +0100 12/19/02, Harald Fuchs wrote: In article [EMAIL PROTECTED], Dyego Souza do Carmo [EMAIL PROTECTED] writes: Dobrý den, quarta-feira, 18 de dezembro de 2002, 13:10:07, napsal jste: MTB Qunfeng Dong wrote: Another thing, with some linux system, there is a size limit for file. MySQL seems to store each of its table as single file. You need to choose a file system without that limit. MTB Just use InnoDB tables for these files and you won't have a problem MTB AFAIK; you can have multiple 2G files that are used to create one big MTB table if you like (any InnoDB people want to comment on actual limits?) Use the InnoDB tables with the raw devices ( ex: allow innodb use a /dev/sdxx or /dev/hdxx to write tablespace ), the speed is better, MySQL don't loses time with the filesystem. In my production database , i have a tablespace with 130G ( with raw diveces on SCSI disks) and the performance is good :) /dev/sdxx or /dev/hdxx are _not_ raw devices; they are disk partitions without a file system, but still subject to the Linux buffer cache. man 8 raw says how to bind a disk partition to a true raw device (/dev/raw/rawX). And yes, those beasts work fine with InnoDB. I asked Heikki about this. His reply: Paul, you can use a disk partition which Linux buffers in its file cache, and you can use also a 'raw device disk partition' which Linux probably does not buffer. Google the mailing list. In summer a Swiss user was able to get a raw device working as a data file. I have no measurements of performance raw device / buffered disk partition. In theory, a raw device should be faster. fsync was extremely slow in Linux-2.2, and is still a bit slow in 2.4. Regards, Heikki - 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: SELECT and UPDATE at the same time?
At 13:23 -0500 12/19/02, [EMAIL PROTECTED] wrote: Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. Okay. If that's what you observe, I won't dispute it. But then the behavior is probably something specific to the Delphi interface. The underlying behavior of the client/server protocol is somewhat different than what you observe, so people using other APIs will likely see something different happening. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? Hello again, I'm selecting a group of records from my database. I then loop through the selected records and do some work based on what I find. But what I also want to do as I interrogate each record is update some of its fields with new values... but won't that screw up the outer loop? I mean if I try to execute a query whilst looping around the result set of a former query will I not screw up my result set that I'm looping through? Also, is it possible to update specific fields of certain records within a SELECT query? ie can I do something like this: SELECT * FROM my_table WHERE Age 50 AND UPDATE Status = OLD Ta, Jeff - 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: INSERT... SELECT Statement fails when...
At 12:19 -0500 12/19/02, Andrew Kuebler wrote: When I run an INSERT. SELECT query and I am inserting the records into a new table that has a UNIQUE key, the statement will fail as soon as it hits a Duplicate entry error. No further entries will be inserted. The only way I know to get around this is to pull the entire column(s) into memory and insert them back in one by one with a repeating INSERT statement. Can anyone tell me the easy way to do this? (I assume there must be a better way) Thanks! Either use INSERT IGNORE or REPLACE, depending on whether you want to ignore subsequent duplicates or use them to replace earlier duplicates. Andrew - 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 3.23.x preformance on MacOS 10.2
At 8:12 -1000 12/17/02, [EMAIL PROTECTED] wrote: Hello all, I have checked the mailing list and the documentation but have been unable to find any information regarding the level of support for MySQL on the MacOS 10.2 platform. After moving a production database from a linux to MacOS 10.2 for development I have noticed that on my MacOS machine there is a significant performance hit on the MySQL server. Perhaps this is just hardware related but it seems as though the performance should be at least comparable on the following machines. Intel Pentium III 933 MHz Mandrake Linux v8.2 No windowing system running 512 MB RAM Query takes about 12 seconds. Apple G4 1GHz MacOS X 10.2 No windowing system running 512 MB RAM Query Takes about 25 seconds. I have noticed that on linux the mysqld runs as many processes and on MacOS 10.2 it runs as a single process. Is this an architectural decision? or have i configured the server incorrectly? Thank you, Christophe Banal No, actually, it always runs as a single process. What you're seeing is that ps reports separate threads as processes on Linux. - 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: JOIN + GROUP BY question
At 20:21 +0100 12/17/02, Csongor Fagyal wrote: Hi, I have two tables: one holding bids for an auction (table bids) and one holding user data who placed the bids (users). I would like to get the highest bid, the user who placed the bid and the number of bids placed, so I use the following query: SELECT MAX(bids.amount) AS amount, COUNT(bids.itemid) AS bidcount, bids.bidderid, users.username FROM bids LEFT JOIN users ON bids.bidderid=users.userid WHERE bids.itemid = 71580 AND users.userid IS NOT NULL GROUP BY bids.itemid; What I get is not totally OK: amount is OK, COUNT is also OK, but the user (users.username) I get is not the one who placed the highest bid, but someone in the middle. How come? Am I misunderstanding something about GROUP BY on a joined table? No, you're misunderstanding something about GROUP BY. When you group on a column or set of columns, you can select for output those columns, and aggregate (summary) function values on other columns, but you cannot select other columns. You're grouping by itemid, but selecting bidderid and username for output. So you get indeterminate results. THX, - Csongor - 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
/etc/my.cnf does not contain innodb_log_files_in_group.
Description: After ugrading from MySQL-Max-3.23.53 to 3.23.54, mysqld failed to start. Error message was:/usr/sbin/mysqld-max: unrecognized option `--innodb_log_files_in_group=3'. This option required for Innodb Hot backup! How-To-Repeat: Just upgrade MySQL-Max and try to run /usr/bin/ibbackup Fix: Comment out innodb_log_files_in_group=3 in my.cnf (still breaks ibbackup). Submitter-Id: Kipland S. Iles Originator:hostmaster Organization: Transcor, Inc MySQL support: none Synopsis: innodb_log_files_in_group=3 no longer valid Severity: critical: No database backups now Priority: high Category: mysql Class: sw-bug Release: mysql-3.23.54 (Official MySQL RPM) Environment: System: Linux dbs01 2.4.18-14smp #1 SMP Wed Sep 4 12:34:47 EDT 2002 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /usr/bin/perl /usr/bin/make /usr/bin/gmake /usr/bin/gcc /usr/bin/cc GCC: Reading specs from /usr/lib/gcc-lib/i386-redhat-linux/3.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --host=i386-redhat-linux --with-system-zlib --enable-__cxa_atexit Thread model: posix gcc version 3.2 20020903 (Red Hat Linux 8.0 3.2-7) Compilation info: CC='gcc' CFLAGS='-O6 -fno-omit-frame-pointer -mpentium' CXX='gcc' CXXFLAGS='-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' LDFLAGS='' LIBC: lrwxrwxrwx1 root root 14 Aug 19 15:38 /lib/libc.so.6 - libc-2.2.93.so -rwxr-xr-x1 root root 1235468 Sep 5 19:12 /lib/libc-2.2.93.so -rw-r--r--1 root root 2233342 Sep 5 18:59 /usr/lib/libc.a -rw-r--r--1 root root 178 Sep 5 18:50 /usr/lib/libc.so Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--without-berkeley-db' '--without-innodb' '--enable-assembler' '--enable-local-infile' '--with-mysqld-user=mysql' '--with-unix-socket-path=/var/lib/mysql/mysql.sock' '--prefix=/' '--with-extra-charsets=complex' '--exec-prefix=/usr' '--libexecdir=/usr/sbin' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O6 -fno-omit-frame-pointer -mpentium' 'CXXFLAGS=-O6 -fno-omit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti -mpentium' 'CXX=gcc' MY BACKUP SCRIPT... #!/bin/sh # KSI 20021126 # Database HotBackup Script # INITIALIZATION STAMP=date +%Y%m%d~%H:%M:%S TIMESTAMP=date +%Y%m%d%H%M%S echo echo = echo `$STAMP`: $0 STARTED BIN=/root/bin ROTATEBU=$BIN/rotatebu DBHOME=/database/mysql GTS=$DBHOME/gts MYSQL=$DBHOME/mysql HBHOME=/database/hotbackup/etranscor HBDIR=$HBHOME/`$TIMESTAMP` IDBDATA=$HBDIR/innodb/idbdatab IDBLOGS=$HBDIR/innodb/idblogsb INNODB_CNF=$HBDIR/backup.my.cnf MDUMPBEFORE=$HBDIR/before-ibbackup.gz MDUMPAFTER=$HBDIR/after-ibbackup.gz LDUMPFILE=$HBDIR/etranscor.ldif.gz MYSQL_CNF=/etc/my.cnf # LETS BE SPECIFIC SO WE DONT GET SPOOFED alias mdump='/usr/bin/mysqldump -prodeswll' alias idump='/usr/bin/ibbackup' alias ldump='/root/ldifs/dump' alias gzip='/usr/bin/gzip' alias tar='/bin/tar' alias rm='/bin/rm' # CREATE BACKUP HOME IF IT DOESNT EXIST if [ ! -d $HBHOME ] then echo `$STAMP`: Creating $HBHOME mkdir -p $HBHOME fi # CREATE BACKUP DIRS IF THEY DONT EXIST # OR WIPE IT OUT if [ -d $HBDIR ] then echo `$STAMP`: Removing $HBDIR rm -r $HBDIR fi echo `$STAMP`: creating $HBDIR mkdir -p $HBDIR echo `$STAMP`: creating $IDBDATA mkdir -p $IDBDATA echo `$STAMP`: creating $IDBLOGS mkdir -p $IDBLOGS # CREATE THE BACKUP INNODB CONF FILE DYNAMICALLY echo `$STAMP`: creating $INNODB_CNF cat EOF$INNODB_CNF # For Innodb Backups # innodb_data_home_dir = $HBDIR/innodb/idbdatab innodb_data_file_path = ibdata1:1000M:autoextend innodb_log_group_home_dir = $HBDIR/innodb/idblogsb innodb_log_arch_dir = $HBDIR/idblogsb innodb_log_files_in_group = 3 set-variable = innodb_buffer_pool_size=500M set-variable = innodb_additional_mem_pool_size=20M set-variable = innodb_log_file_size=125M set-variable = innodb_log_buffer_size=25M innodb_flush_log_at_trx_commit=1 set-variable = innodb_lock_wait_timeout=50 EOF # DO A MYSQL DATABASE DUMP echo `$STAMP`: Dumping MySQL to $MDUMPBEFORE mdump --all-databases | gzip $MDUMPBEFORE echo `$STAMP`: Dumping INNODB
Re: INSERT... SELECT Statement fails when...
On 19 Dec 2002, at 12:19, Andrew Kuebler wrote: When I run an INSERT. SELECT query and I am inserting the records into a new table that has a UNIQUE key, the statement will fail as soon as it hits a Duplicate entry error. You probably want to use INSERT IGNORE: http://www.mysql.com/doc/en/INSERT.html For some reason IGNORE isn't explained on the INSERT ... SELECT syntax page, though it is mentioned. [Filter fodder: SQL] -- Keith C. Ivey [EMAIL PROTECTED] Tobacco Documents Online http://tobaccodocuments.org Phone 202-667-6653 - 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: GUI for the server part of MySQL
Greetings What do you mean server part of MySQL. SQLyog allows you to perform almost all operation which MySQLAdmin does except starting and shutting down a MySQL server, which probably SQLyog will have in one of its future releases. Insane - Original Message - From: Stan Sebastian [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 12:47 PM Subject: GUI for the server part of MySQL Is there any GUI for the server part of MySQL fro Windows. I mean not a client for MySQL, like SQLyog OR MySqlFront. Something better then the MySqlAdmin? -- Sebastian Stan [EMAIL PROTECTED] ### Disclaimer on Exchange This message has been scanned by F-Secure Anti-Virus for Microsoft Exchange. For more information, connect to http://www.F-Secure.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 mysql-unsubscribe-##L=##[EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/doc/ (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
Weird errors appearing in log..
I upgraded a very high load site from 3.23.51 to 3.23.54a. The upgrade seems to have gone fine, but some really odd messages appeared in the error log: InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/EmailCache has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/RecentMatches has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/EmailCache has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/RecentMatches has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! They stopped after the first minute or two. Is this cause for concern? How can I make them go away? :) -- Michael Bacarella | Netgraft Corp | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | http://netgraft.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
MySQL/InnoDB-4.0.6 is released
Hi! InnoDB is a MySQL table type which provides to MySQL transactions, row level locking, foreign key constraints, and a non-free hot backup tool. InnoDB is included in all MySQL-4.0 downloads and MySQL-Max-3.23 downloads. You can download them from http://www.mysql.com. MySQL AB decided to release 4.0.6 quickly to fix recently discovered security issues in MySQL-4.0.5. For InnoDB, 4.0.6 is a bugfix release which corrects version 4.0.5 major bugs in AUTO_INCREMENT and REPLACE, INSERT INTO ... SELECT ..., and CREATE TABLE ... SELECT ... . Full changelog: * Since innodb_log_arch_dir has no relevance under MySQL, there is no need to specify it any more in my.cnf. * LOAD DATA INFILE in AUTOCOMMIT=1 mode no longer does implicit commits for each 1 MB of written binlog. * Fixed a bug introduced in 4.0.4: LOCK TABLES ... READ LOCAL should not set row locks on the rows read. This caused deadlocks and lock wait timeouts in mysqldump. * Fixed two bugs introduced in 4.0.4: in AUTO_INCREMENT, REPLACE could cause the counter to be left 1 too low. A deadlock or a lock wait timeout could cause the same problem. * Fixed a bug: TRUNCATE on a TEMPORARY table crashed InnoDB. * Fixed a bug introduced in 4.0.5: if binlogging was not switched on, SQL commands INSERT INTO ... SELECT ... or CREATE TABLE ... SELECT ... could cause InnoDB to hang on a semaphore created in btr0sea.c, line 128. * Fixed a bug: in replication issuing SLAVE STOP in the middle of a multi-statement transaction could cause that SLAVE START would only perform part of the transaction. A similar error could occur if the slave crashed and was restarted. Best regards, Heikki Tuuri Innobase Oy http://www.innodb.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
mysqldump user prive
I want to run mysqldump through a batch process, in order to do that what prive do i need to give to the user. It will be running as a cron job, i don't want to give any creation or deletion authorization. Your help will be greatly appreciated. Regards Arun __ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.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: help me, please
Select max(No) from table Marcos Henke - Original Message - From: Paul DuBois [EMAIL PROTECTED] To: mustakim abas [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, December 19, 2002 1:51 PM Subject: Re: help me, please At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? - 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: Weird errors appearing in log..
Michael, InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! these messages below derive from this bug fix: MySQL/InnoDB-3.23.54, December 12, 2002 ... Fixed a bug: ORDER BY could fail if you had not created a primary key to a table, but had defined several indexes of which at least one was a UNIQUE index with all its columns declared as NOT NULL. ... If you have not defined a PRIMARY KEY, MySQL internally treats the first UNIQUE KEY with all non-NULL columns as the primary key. That can cause confusion between InnoDB and MySQL. I am not sure if it can cause any serious errors. Fix: drop and recreate the tables below. In 3.23.54 MySQL and InnoDB are harmonized. Hmm... I have to add this advice to the error printout. 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 sql query . Subject: Weird errors appearing in log.. From: Michael Bacarella Date: Thu, 19 Dec 2002 14:17:19 -0500 I upgraded a very high load site from 3.23.51 to 3.23.54a. The upgrade seems to have gone fine, but some really odd messages appeared in the error log: InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/Pilot has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/EmailCache has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/RecentMatches has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/EmailCache has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! InnoDB: Error: table ./m4m4sex/RecentMatches has no primary key in InnoDB InnoDB: data dictionary, but has one in MySQL! They stopped after the first minute or two. Is this cause for concern? How can I make them go away? :) -- Michael Bacarella | Netgraft Corp | 545 Eighth Ave #401 Systems Analysis | New York, NY 10018 Technical Support | 212 946-1038 | 917 670-6982 Managed Services | http://netgraft.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: Change log
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, On Thu, 19 Dec 2002, Maximo Migliari wrote: So if I just untar the new .54a release, and just copy the safe_mysqld script to my current .54 release, it will work fine? Yes, that is sufficient. Or do I have to re-install everything? No, that is all you need to do, in case you have problems with starting up MySQL 3.23.54 using safe_mysqld. If you have been able to use the initial release without problems, you can simply stick with what you have. Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+AiSXSVDhKrJykfIRAnjoAJ95OrBY+neF1RVb8A3XZL2PiT4xBwCfcC5l D//KnA1t/kzDUa0fAB60aY8= =jbUy -END PGP SIGNATURE- - 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: Password function not working with latest 4.1 tree
Yes, that's intentional - we have changed this in 4.1, but it's not documented in the manual yet. A quote from the developer working on that code: So, the PASSWORD() function is now not to be used for passwords? The problem is that I have built at least a few applications that use PASSWORD() as the authentication mechanism as it produces a one-way hash that is the same every time - the same as what MD5() does. No one was told that this wasn't the desired usage for PASSWORD(). Now the problem that I face is that I can't convert my old passwords to MD5 hashes because the original PASSWORD() function was irreversible. Therefore I am left with no choice but to use a deprecated function... which I really don't want to do... What do you suggest? Regards, Matt Parlane Modus Consulting 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: OS X 10.2.2 Can not access mysql with the terminal
On Sunday, December 15, 2002, at 03:43 PM, Helmuth Lutz wrote: First, from where did you install MySQL? Are you using OS X Server? If so, MySQL came installed with your system and you shouldn't have a problem with it. So I'm assuming you're using the client. Does anybody have an idea: - WHY does my currently installed MySQL 3.23.46 start at System-Start? It's supposed to by default. Check /System/Library/StartupItems - WHY do the currently installed db's work within Internet-Explorer? huh? I'm assuming you're using Perl/PHP/Cold Fusion/ASP (although, probably not ASP, maybe not CF, on OS X)/something else to generate a web site. Check your HTML, as it's probably just a syntax issue. If you're using php, install phpmyadmin and try it from different browsers. - WHY does the terminal not know the commands: mysql or mysqladmin? Try prefixing the mysql or mysqladmin with: /Library/MySQL/bin/ so your command looks like #/Library/MySQL/bin/mysqladmin -u root -p version To avoid having to do that (if that works), add /Library/MySQL/bin to your $PATH. There are plenty of tutorials that can be found by using Google. Search for [your shell here] PATH modify, so, for example, bash PATH modify Not as user neither as root The terminal response: [hlutz:/usr/local/mysql] hlutz# mysqladmin -u root user -p version mysqladmin: Command not found. Definately a PATH issue. -- Chris Garaffa [EMAIL PROTECTED] -BEGIN GEEK CODE BLOCK- Version: 3.1 GCS/M d-(--) s+:+ a--- C++(+++) U*++@ P+ L+ E- W+++ N++ o? K- w--- O-- M++$ ?V PS++(+++) PE-(--) Y+ PGP t- 5 X R !tv b+++ DI++ D+ G e h r++ y+ --END GEEK CODE BLOCK-- - 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: Making up for lack of subqueries (-or- what the heck am I doing?)
Have you tried looking into the REPLACE command? It basically does an update if the record is present or an insert if it's not, but all in one command. Since MySQL won't update a record if you are trying to update it with data that is unchanged it should be pretty fast. On Thursday, December 19, 2002, at 12:43 PM, Zeno wrote: I have found this to be _infinitely_ faster than taking each article number, doing a SELECT to see if it is in the main table, and an INSERT if it is not (we're talkin' light years faster). -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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: Database type question
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi! I guess you're not a subscriber of the MySQL Newsletter. Subscribe to it - it's very useful! Your question was answered a couple of days ago in ta newsletter article: http://www.mysql.com/newsletter/2002-12/a91.html Regards, Iikka On Thu, 19 Dec 2002, Richard E. Perlotto II wrote: I am looking at an application that will be could be potentially writing hundreds of records a second (syslog) to a MySQL database. I expect to reach millions of records (if not billions) in time. I will be doing a variety of types of queries on the data which will be anything from summarization of certain types of data to full pattern matching. So, my question is which of the database formats would be best suited for this type of environment? The MyISAM seem to work well on receiving the data, but the InnoDB database style seems better suited for the queries. Any ideas anyone? Richard - 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 Iikka Meriläinen Vaala, Finland E-mail: [EMAIL PROTECTED] -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) iD8DBQE+AjNPCVGYZ+r4ZncRAvVOAJ4pswyFWizkCnSbHOv47HQmSip7hACbBiWT qzLWClrkqxVCroVNrp3Ob1o= =eIxR -END PGP SIGNATURE- - 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
Loading Queries.
Hi: I know how to save a query using mysqlgui. How do I load a query? Thanks. EV - 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
Excluding records?
If I've got the following two tables:
CREATE TABLE password_log (
time_stamp int(11) unsigned NOT NULL default '0',
remote_host char(15) NOT NULL default '',
remote_user char(50) NOT NULL default '',
status smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (remote_host,remote_user,status),
KEY time_stamp (time_stamp),
KEY remote_user (remote_user),
KEY status (status)
) TYPE=MyISAM;
CREATE TABLE exclude_log (
ip_block char(15) NOT NULL default '',
PRIMARY KEY (ip_block)
) TYPE=MyISAM;
#
# Dumping data for table 'exclude_log'
#
INSERT INTO exclude_log VALUES ('152.163.188');
INSERT INTO exclude_log VALUES ('152.163.189');
INSERT INTO exclude_log VALUES ('152.163.206');
INSERT INTO exclude_log VALUES ('152.163.207');
INSERT INTO exclude_log VALUES ('195.93.64');
INSERT INTO exclude_log VALUES ('195.93.65');
INSERT INTO exclude_log VALUES ('195.93.66');
INSERT INTO exclude_log VALUES ('195.93.72');
INSERT INTO exclude_log VALUES ('195.93.73');
INSERT INTO exclude_log VALUES ('195.93.74');
INSERT INTO exclude_log VALUES ('205.188.208');
INSERT INTO exclude_log VALUES ('205.188.209');
And I want a list of everything in the 'password_log' table that doesn't
match up with any entry in the 'exclude_log' table, something along the
lines of:
select remote_user, substring_index(remote_host,'.',3) As ip_subnet from
password_log, exclude_log where remote_user != '-' and status=200 and
substring_index(remote_host,'.',3) != exclude_log.ip_block group by
ip_subnet order by remote_user, ip_subnet\g
+---+-+
| remote_user | ip_subnet |
+---+-+
| adamvernau| 207.79.8|
| amos | 24.53.232 |
| badmilk | 62.57.227 |
| [EMAIL PROTECTED] | 80.103.137 |
| beerbomb60| 12.80.11|
| BogusBob | 65.58.37|
| brendenm123 | 172.190.203 |
| brize | 217.39.73 |
| bruneau | 195.242.80 |
| chicken | 24.101.127 |
| cracking | 213.122.143 |
| DanielNoble | 172.151.183 |
| DESIO | 204.213.78 |
| diamond | 4.60.97 |
| dlgeo | 68.42.127 |
| ewing | 195.29.35 |
| fnadeau | 64.228.196 |
| frogman | 67.234.8|
| fujerome | 156.143.132 |
| geno6969 | 65.58.94|
| gravy01 | 81.86.119 |
| iftkharmaan | 62.255.64 |
| jamesz| 204.186.14 |
| jaysap| 12.235.160 |
| karak | 80.63.120 |
| kevin | 152.163.188 |
| kevin | 152.163.189 |
| kevin | 152.163.201 |
| kjelljanssonx | 213.66.154 |
| ksm70512 | 172.195.152 |
| leinad| 210.120.128 |
| leinad| 66.68.138 |
| lemurs| 24.60.185 |
| leolebr | 81.48.138 |
| Malakon | 24.186.21 |
| martisr | 217.39.29 |
| matglew | 81.98.84|
| mikeestela| 129.106.169 |
| Mirhos| 80.11.19|
| newyork | 62.134.74 |
| ordinary3 | 12.37.234 |
| pcomdh| 212.185.249 |
| pp-hosereed | 24.61.65|
| pp-lobeneath | 67.82.86|
| prodrifter72 | 66.75.124 |
| RbrtMackay| 81.77.108 |
| revrendpoe| 65.43.0 |
| rockey| 62.64.135 |
| rockey| 80.225.68 |
| stwgolfer | 64.221.53 |
| thebear | 205.188.208 |
| thebear | 205.188.209 |
| tooms | 63.225.249 |
| ulyanov | 12.5.196|
| WHATSUP | 172.173.81 |
| [EMAIL PROTECTED] | 80.143.42 |
| xmartyx | 68.5.149|
| xym180| 216.41.132 |
| zook10| 213.89.57 |
+---+-+
59 rows in set (0.03 sec)
As you can see, I still end up with records from the 'exclude_log'
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: help me, please
At 8:06 -0800 12/19/02, mustakim abas wrote: Hello, i am new in mysql. I got a problem. I try write my C program with mysql API.I have 5 field in table.No,Name,Phone,Date,Time. How can i take one row where the No is bigger? Thanks for help. Bigger than what? Maybe SELECT No, Name, Phone, Date, Time FROM tablename ORDER BY No DESC LIMIT 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
Re: Making up for lack of subqueries (-or- what the heck am I doing?)
On Thu, 19 Dec 2002 15:50:18 -0500, Brent Baisley wrote: Thanks, Brent. I did read about the REPLACE command, but I only want to change one field (a simple flag) in the main_table. With REPLACE, I cannot even copy the rest of the fields into the new record because it does not offer any access to the existing fields. Thanks again, - Zeno Have you tried looking into the REPLACE command? It basically does an update if the record is present or an insert if it's not, but all in one command. Since MySQL won't update a record if you are trying to update it with data that is unchanged it should be pretty fast. - 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: Cluster/replication
Does any one have any advise about clustering and replication with of course mysql. Are there any third party products? Sam - 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
sql help
hi, wonder if the sql gurus can help with this one. i have two tables (simplified), tblAccounts and tblAccountsServices. tblAccounts has an ID (PK) and an accountNumber, and tblAccountsServices has an ID (PK) and accountID (FK to tblAccounts.ID). i want to run a query that gives me a row for each record in tblAccountsServices that looks like - tblAccountsServices.ID, tblAccounts.accountNumber, COUNT of tblAccountsServices.ID for this tblAccountsServices.accountID i'm thinking something like - select acctSrv.ID, a.accountNumber, select COUNT( ID) from tblAccountsServices where accountID = ?? as theCount from tblAccountsServices acctSrv, tblAccounts a where a.ID = acctSrv.accountID it's the part in quotes i'm having trouble with, if it's even possible. i feel like i've seen something similar posted before. of course, if i have to, i can use the brute force method of looping through the rowset of tblAccountsServices, and if it's a new accountID from the previous record, then run another query to get the count then... 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: MySQL 3.23.x preformance on MacOS 10.2
I'm not surprised OSX is slower than Linux. Apple still has a way to go toward fully optimizing the Unix underpinnings for their hardware. They are also a few versions behind in the BSD they are using. I'm presuming you have similar settings for each setup. As for the processes you see on Linux, in a nutshell Linux used threads and other Unix uses processes. That's one of the fundamental differences between Linux and other Unix flavors. Your actually seeing threads. Threads don't have the launch overhead that a process does so you should get better performance with threads. On Thursday, December 19, 2002, at 01:43 PM, Paul DuBois wrote: Apple G4 1GHz MacOS X 10.2 No windowing system running 512 MB RAM Query Takes about 25 seconds. I have noticed that on linux the mysqld runs as many processes and on MacOS 10.2 it runs as a single process. Is this an architectural decision? or have i configured the server incorrectly? Thank you, Christophe Banal No, actually, it always runs as a single process. What you're seeing is that ps reports separate threads as processes on Linux. -- Brent Baisley Systems Architect Landover Associates, Inc. Search Advisory Services for Advanced Technology Environments p: 212.759.6400/800.759.0577 - 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
sql help
btw, regarding my last post, i'm running mysql 3.23.53-max-nt... - 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: saving a file into a field of a table
Hello, my name is Fabiana Garfunkel, and I´m new in MySQL developing. I have a punctual question: I need to save a zipped file into a field of a table. The question is: 1- is it possible to save a file into a field of a a table ? 2- if the answer is YES, which is the proper type of field for this ? 3- how can I save a file into a field ? 4- how can I read or retrieve the file saved in a field of a table ? Thanks a lot and best regards !!! Fabiana - 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
Thanks! Re: Can MySQL handle 120 million records? - Ok, If you guys really can handle tens of millions records, you have to help me to enjoy MySQL too :-)
Well, thanks to all of your great help! I am able to
speed up the query {select count(*) from NEW_Sequence
s left join NEW_Sequence_Homolog h on s.Seq_ID =
h.Seq_ID;} from 1 min 52.61 sec down to 20.62 sec now.
The only thing I changed so far was the Seq_ID from
type varchar to bigint. The Seq_ID was not all
numerical for different type of Sequences; but I
managed to assign numerical code to those
non-numerical ones now.
Qunfeng
CREATE TABLE NewSequence
(
Seq_ID varchar(50) NOT NULL,
GenBank_Acc varchar(10),
Organismvarchar(50) NOT NULL,
Seq_Type enum(EST,GSS,EST
Contig,EST
Singlet,GSS Contig,GSS Singlet,GSS Plasmid
Contig,Protein) NOT NULL,
Seq_Length int NOT NULL,
Seq_Title textNOT NULL,
Comment text,
Entry_Date dateNOT NULL,
PRIMARY KEY (Seq_ID),
UNIQUE (GenBank_Acc),
INDEX (Seq_Type),
INDEX (Organism)
);
This NewSequence table is used to track some
general
info about sequence. Notice I have to use text
datatype to describe Comment and Seq_Title
fields;
therefore I have to use varchar for other string
fields. In addition, the Seq_ID is not numerical.
BTW, I found indexing on Seq_Type. Organism which
are
very repeative still helps with accessing. This
table
has 2676711 rows.
CREATE TABLE NewSequence_Homolog
(
Seq_ID varchar(50) NOT NULL,
Homolog_PID int NOT NULL,
Homolog_Descvarchar(50) NOT NULL,
Homolog_Species varchar(50),
PRIMARY KEY (Seq_ID, Homolog_PID)
);
This NewSequence_Homolog table is to track which
protein sequences (homolog) are similar to the
sequence I store in the NewSequence table. This
table
has 997654 rows.
mysql select count(*) from NewSequence s left
join
NewSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+--+
| count(*) |
+--+
| 3292029 |
+--+
1 row in set (1 min 30.50 sec)
So a simple left join took about 1 min and half.
First, is this slow or I am too picky?
This is the Explain.
mysql explain select count(*) from NewSequence s
left
join NewSequence_Homolog h on s.Seq_ID = h.Seq_ID;
+---+---+---+-+-+--+-+--
---+
| table | type | possible_keys | key |
key_len |
ref | rows| Extra |
+---+---+---+-+-+--+-+--
---+
| s | index | NULL | PRIMARY |
50 |
NULL | 2676711 | Using index |
| h | ref | PRIMARY | PRIMARY |
50 |
s.Seq_ID |9976 | Using index |
+---+---+---+-+-+--+-+--
---+
I am running MySQL 3.23.49 on RedHat linux 7.3 on
a
dedicated server with 4 GB memory. The only
setting I
changed is to copy the my-huge.cnf into
/etc/my.cnf.
Qunfeng
--- Michael T. Babcock [EMAIL PROTECTED]
wrote:
Qunfeng Dong wrote:
not-so-good performance (join on tables much
smaller
than yours takes minutes even using index) and
I
seem
to read all the docs I could find on the web
about
how
to optimize but they are not working for me (I
am
Have you stored a slow query log to run them
through
'explain' and see
why they're slow? Do you want to post some of
them
here so we can
suggest what might be done to make them faster?
=== message truncated ===
__
Do you Yahoo!?
Yahoo! Mail Plus - Powerful. Affordable. Sign up now.
http://mailplus.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
Update databases, the unanswered question (resend)
I lister was kind enough to show me echo show databases | mysql -N But there are still some remaining questions I have been searching for answers to for a long time. Any help appreciated, Hello all, I have dug through the archives to no avail, the archives point to many ways to back up your databases, I have found a way that works for me, but it is no where near as automated as what I want it to be, so I thought I would ask a few people here what the best approach would be. To start with, my first question, Mysqldump has a option to dump all databases to a file, this logically seems the simplest way, and is close to what I want to do, however, it dumps all databases to one file. My question is, in the event I had to restore a database from this type of dump, would I also be restoring ALL databases? If this is the case, this is not the option I am looking for, I anticipate a client calling and telling me they accidentally deleted a whole bunch of records, and want a restore from a few days ago. If I have to restore ALL databases, I would be messing up a whole lot of clients databases. When you restore, does mysql ask you which database you want to restore even if the dump file has tons of other information in it? If not The approach I have been using now is this... I have a small shell script that cron calls, in it is mysqldump -u backup my_database | gzip /path_to_storage_$newtime.sql.gz I have many entries like this where I change my_database with the correct database name. You will also notice I do not have a password in there, is that safe? I have created a user with select only privileges just for backup, what is the best way to pass a username and password for these types of purposes? And finally, the main question, mysql show databases; +---+ | Database | +---+ | dsadsadsad| | ewrererrewrerr| | 324effdfdfd | | liffdfdsfewfdsake | | mysql | +---+ As you can see, it is rather simple to get a real up to date list of all databases, but it is bordered by bars, +'s and -'s, is there any way to issue a show databases with a flag that tells it to return the database names in a list, perhaps comma separated or better yet \r separated. This way, I can create a shell script that will loop through all the databases, and when I add new databases I need not ever worry about messing with my backup script. Any suggestions of pre-made scripts that would work under OS X would be much appreciated. - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. - 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/InnoDB-4.0.6 is released
There is not link to download the Windows version. Any date for the Windows version to be published? 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: Change log
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On Thu, 19 Dec 2002, Maximo Migliari wrote: I would like to know where I can find the change log for the new versions of MySQL. For example, just recently .54 was released, and then .54a. There is a ChangeLog file in the distribution, but at least to me, it is TOTALLY incomprehensible. Isn't there a simple file that says something like: Verion 3.23.54a: - fixed this, - improved that, etc. Yes, you are right - I should have added a note to the ChangeLog. The only difference between 3.23.54 and 3.23.54a is in the safe_mysqld script: [SNIP] - --- bin/safe_mysqld.org Mon Nov 25 11:49:31 2002 +++ bin/safe_mysqld Fri Dec 13 13:09:16 2002 @@ -159,9 +159,9 @@ NOHUP_NICENESS=`nohup nice 21` if test $? -eq 0 test x$NOHUP_NICENESS != x0 nice --1 echo foo /dev /null 21 then - -if $NOHUP_NICENESS -gt 0 +if test $NOHUP_NICENESS -gt 0 then - - $NOHUP_NICENESS=nice --$NOHUP_NICENESS nohup + NOHUP_NICENESS=nice --$NOHUP_NICENESS nohup else NOHUP_NICENESS=nice -$NOHUP_NICENESS nohup fi [SNIP] I wrote an addendum to the initial relase announcement about that as well and it also came through this list: http://lists.mysql.com/cgi-ez/ezmlm-cgi?2:mss:145:200212:emmpdkidnedchpgmjele Bye, LenZ - -- For technical support contracts, visit https://order.mysql.com/?ref=mlgr __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Mr. Lenz Grimmer [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Production Engineer /_/ /_/\_, /___/\___\_\___/ Hamburg, Germany ___/ www.mysql.com -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.0 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQE+Ah0oSVDhKrJykfIRAsrGAJ0YxJspbqXUNREPBLsJucHYxckuFwCggTdN VIr4dnTGY2HEvTrBrxV7QrI= =uREJ -END PGP SIGNATURE- - 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
Help w/ Join Query
Hello, I working on a small my project to learn more about joins. I have three tables here is what they look like CREATE TABLE `album` ( `album_id` smallint(3) NOT NULL auto_increment, `name` text NOT NULL, `description` text NOT NULL, `img_id` smallint(5) NOT NULL default '0', PRIMARY KEY (`photo_album_id`) ) TYPE=MyISAM; CREATE TABLE `images` ( `img_id` smallint(3) unsigned NOT NULL auto_increment, `img_path` varchar(60) default NULL, `img_caption_id` smallint(5) NOT NULL default '0', PRIMARY KEY (`img_id`) ) TYPE=MyISAM; CREATE TABLE `img_caption` ( `caption` text NOT NULL, `img_caption_id` smallint(5) NOT NULL auto_increment, PRIMARY KEY (`img_caption_id`) ) TYPE=MyISAM; Is it possible write a join query that would join the album table and the images table and then join the images table with the img_caption table?. I would like to select name and description from album , img_path from images, and caption from img_caption for any paticluar album row. Thanks in advance for the help, Mike - 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
To join or not to join
I am looking for an expert opinion on the speed difference between fetching related data from 2 tables with a join and fetching them in to single selects. The scenario is kind of the following: SELECT a , b, c FROM table1 WHERE a='x'; # gets b='y' SELECT b, d , e, f FROM table2 WHERE b='y'; instead SELECT a , b, c, d , e, f FROM table1, table2 WHERE a='x' AND table1.b = table2.b; Background: I wrote a little Perl module that automatically instantiates a object for each table in the database connected to and each table object allows you to access any record in that table or create a new one. So the above SQL looks like: my $DB = DB-new($config); my $record_a_b_c = $DB-table1-new(primary_key_value); my $field_b_value = $record_a_b_c-fieldname; my $record_b_d_e_f = $DB-table2-new($field_b_value); In this scenario it very easy to retrieve related from several tables without doing a join, but I am not sure how hard the performance hit actually is, since MySQL would have to look up the first select before it can do the join on the second table. Thanks for your input. /h mysql, 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
Error 2013: Lost connection to MySQL server
mysql client connectiosn work fine on localhost, but connections from other hosts on local area network fail with the following error: ERROR 2013: Lost connection to MySQL server during query This is an existing installation. I had mysql-3.23.33 working and was trying to upgrade to a more current version. I tried mysql-3.23.53 and a few days later mysql-3.23.54a (this try). I was able to go back to mysql-3.23.33, which still works. I just did a make install in the old build directory that I still had online. I may try recompiling 3.23.33 to try to verify that its not a problem related to compile time changes. I don't think it is, but its possible due to the time span between the working and non-working builds. I noticed that the configure line listed below doesn't match the actual configure line I used. This may be normal, but it caught my eye. I used: ./configure --prefix=/local --with-libwrap=/local \ --with-charset=usa7 --with-mysqld-user=mysql \ --localstatedir=/local/dbdata Submitter-Id: submitter ID Originator: Aaron Martin Organization: Institute for Crustal Studies, UCSB --- Aaron J. Martin(805) 893-8415 voice and message Institute for Crustal Studies (805) 893-8649 FAX Girvetz 1140E (805) 448-4120 SCEC Cellular UC Santa Barbara [EMAIL PROTECTED] Santa Barbara, CA 93106http://www.crustal.ucsb.edu/~aaron PBIC Lab1252 Arts (805) 893-3758 voice --- MySQL support: none Synopsis: Error 2013: Lost connection to MySQL server Severity: serious Priority: medium Category: mysql Class: sw-bug Release: mysql-3.23.54 (Source distribution) Environment: System: SunOS fablio 5.8 Generic_108528-17 sun4u sparc SUNW,Ultra-5_10 Architecture: sun4 Some paths: /bin/perl /usr/ccs/bin/make /net/quake/opt/bin/gmake /local/bin/gcc /local/apps/SUNWspro/bin/cc GCC: Reading specs from /local/lib/gcc-lib/sparc-sun-solaris2.8/3.0.4/specs Configured with: /local/apps/gcc-3.0.4/configure --prefix=/local Thread model: posix gcc version 3.0.4 Compilation info: CC='gcc' CFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' CXX='gcc' CXXFLAGS='-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' LDFLAGS='' LIBC: -rw-r--r-- 1 root bin 1788196 Dec 4 15:03 /lib/libc.a lrwxrwxrwx 1 root root 11 Oct 24 2000 /lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157872 Dec 4 15:03 /lib/libc.so.1 -rw-r--r-- 1 root bin 1788196 Dec 4 15:03 /usr/lib/libc.a lrwxrwxrwx 1 root root 11 Oct 24 2000 /usr/lib/libc.so - ./libc.so.1 -rwxr-xr-x 1 root bin 1157872 Dec 4 15:03 /usr/lib/libc.so.1 Configure command: ./configure '--prefix=/usr/local/mysql' '--enable-assembler' '--with-extra-charsets=complex' '--enable-thread-safe-client' '--with-innodb' '--with-berkeley-db' 'CFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Wunused -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXXFLAGS=-Wimplicit -Wreturn-type -Wswitch -Wtrigraphs -Wcomment -W -Wchar-subscripts -Wformat -Wparentheses -Wsign-compare -Wwrite-strings -Woverloaded-virtual -Wsign-promo -Wreorder -Wctor-dtor-privacy -Wnon-virtual-dtor -felide-constructors -fno-exceptions -fno-rtti -mcpu=pentiumpro -O3 -fno-omit-frame-pointer' 'CXX=gcc' - 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
problems with query speed when joining several tables
platform: windows 2000 pro, mysql default table type myIsam, non-binary distribution (install version). I am still getting very slow query results when I join multiple tables together. I have been trying to figure this out for days and am at a loss. I added an index to my cross reference tables as suggested and things worked very well for the first query but not the second one. Someone thought it might be a bug in the query optimizer. My tables contain only two records and are structured like this: pk_organism_id---pk_media_idfk_media_id,fk_author_id---pk_authors_id fk_organism_id fk_technique_id fk_admin_id pk_technique_id pk_admin_id . . . . . CREATE TABLE Medias ( pk_media_id bigint unsigned not null auto_increment, fk_organism_id int unsigned not null, fk_technique_id int unsigned not null, fk_admin_id int unsigned not null, file_name_in_db char(50), file_name char(50), file_name_altered char(50), file_data longblob, creation_date date, upload_date date, download_date date, modified_date date, creation_time time, upload_time time, update_timestamp timestamp(14), download_time time, modified_time time, pixel_size int unsigned, file_size int unsigned, format_in_db char(15), format char(15), magnification int unsigned, label bool, release bool, comment varchar(250), keyword varchar(150), field_status char(50), PRIMARY KEY (pk_media_id), FOREIGN KEY(fk_organism_id) REFERENCES Organisms(pk_organism_id), FOREIGN KEY(fk_technique_id) REFERENCES Techniques(pk_technique_id), FOREIGN KEY(fk_admin_id) REFERENCES Admin(pk_admin_id)); CREATE TABLE MediaAuthors ( fk_media_id bigint unsigned not null, fk_author_id int unsigned not null, UNIQUE (fk_media_id, fk_author_id), KEY (fk_author_id), FOREIGN KEY(fk_media_id) REFERENCES Medias(pk_media_id), FOREIGN KEY(fk_author_id) REFERENCES Authors(pk_author_id)); CREATE TABLE Authors ( pk_author_id int unsigned not null auto_increment, admin_username char(50), last_name char(50), first_name char(50), organization varchar(100), title varchar(100), position varchar(100), email char(50), email_1 char(50), email_2 char(50), phone_number char(50), phone_number_1 char(50), phone_number_2 char(50), address varchar(100), city varchar(100), province varchar(100), country varchar(100), postal_code char(50), field_status char(50), PRIMARY KEY(pk_author_id)); mysql select * from organisms \G; *** 1. row *** pk_organism_id: 1 genus: test genus species: test species common_name: test common name empire: test empire kingdom: test kingdom complexity: test nosebleed comment: test comment keyword: test keyword field_status: active *** 2. row *** pk_organism_id: 42 genus: test genus 2 species: test species 2 common_name: test common name 2 empire: Eukaryotic kingdom: Plant complexity: Multicellular comment: test comment 2 keyword: test keyword 2 field_status: active 2 rows in set (0.00 sec) . . . . . // this works well // EXPLAIN SELECT DISTINCT Medias.pk_media_id, Organisms.common_name, Tissues.type As tiss_type, Cells.type As cell_type, Organelles.type As org_type, Macromolecules.type As macro_type, Authors.last_name, Authors.organization, Medias.file_name, Medias.format, Medias.label FROM Macromolecules INNER JOIN MediaMacromolecules ON fk_macromolecule_id = pk_macromolecule_id INNER JOIN Medias ON pk_media_id = MediaMacromolecules.fk_media_id INNER JOIN MediaAuthors ON pk_media_id = MediaAuthors.fk_media_id INNER JOIN Authors ON fk_author_id = pk_author_id INNER JOIN MediaTissues ON pk_media_id = MediaTissues.fk_media_id INNER JOIN Tissues ON fk_tissue_id = pk_tissue_id INNER JOIN MediaCells ON pk_media_id = MediaCells.fk_media_id INNER JOIN Cells ON fk_cell_id = pk_cell_id INNER JOIN MediaOrganelles ON pk_media_id = MediaOrganelles.fk_media_id INNER JOIN Organelles ON fk_organelle_id = pk_organelle_id INNER JOIN Organisms ON fk_organism_id = pk_organism_id INNER JOIN Techniques ON fk_technique_id = pk_technique_id INNER JOIN Admin ON Medias.fk_admin_id = pk_admin_id WHERE pk_macromolecule_id = 1; *** 1. row *** table: Macromolecules type: const possible_keys: PRIMARY,pk_macromolecule_id key: PRIMARY key_len: 8 ref: const rows: 1 Extra: Using temporary *** 2. row *** table: MediaMacromolecules type: ref possible_keys: fk_media_id,fk_macromolecule_id key: fk_macromolecule_id key_len: 8 ref: const
RE: SELECT and UPDATE at the same time?
Actually, I think the behavior comes from the components used to connect to MySQL. Delphi doesn't really care one way or the other. Dan Cumpian -Original Message- From: Paul DuBois [mailto:[EMAIL PROTECTED]] Sent: Thursday, December 19, 2002 1:41 PM To: [EMAIL PROTECTED] Cc: Dan Cumpian; 'Jeff Snoxell'; [EMAIL PROTECTED] Subject: RE: SELECT and UPDATE at the same time? At 13:23 -0500 12/19/02, [EMAIL PROTECTED] wrote: Well, the only basis I have is personal experience from connecting to a local MySQL database. The components I use for connecting to MySQL with Delphi does cache the records as the server returns them. However, not all the records are returned at once. As I said, it depends on how one connects to the server. Okay. If that's what you observe, I won't dispute it. But then the behavior is probably something specific to the Delphi interface. The underlying behavior of the client/server protocol is somewhat different than what you observe, so people using other APIs will likely see something different happening. If I'm wrong, then I apologize for any incorrect information that I may have given. Just trying to help. Dan Cumpian On Thu, 19 Dec 2002 00:18:48 -0600 Paul DuBois [EMAIL PROTECTED] wrote: At 23:43 -0500 12/18/02, Dan Cumpian wrote: Jeff, Not if your outer loop is in a separate query. In that case, your query is essentially a cursor and is static once OPENed. As you move from record to record, what you are seeing is the records at the time the query was opened. Now, if you were to update records that you haven't processed yet, then they may show up as modified in your outer query Your basis for saying that? because (depending on how you are connecting to the database) the server only returns several hundred rows at a time and caches them as you go Your basis for saying that? through the record set. But that doesn't sound like it will impact you. HTH, Dan Cumpian -Original Message- From: Jeff Snoxell [mailto:[EMAIL PROTECTED]] Sent: Wednesday, December 18, 2002 12:56 PM To: [EMAIL PROTECTED] Subject: SELECT and UPDATE at the same time? Hello again, I'm selecting a group of records from my database. I then loop through the selected records and do some work based on what I find. But what I also want to do as I interrogate each record is update some of its fields with new values... but won't that screw up the outer loop? I mean if I try to execute a query whilst looping around the result set of a former query will I not screw up my result set that I'm looping through? Also, is it possible to update specific fields of certain records within a SELECT query? ie can I do something like this: SELECT * FROM my_table WHERE Age 50 AND UPDATE Status = OLD Ta, Jeff - 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: Excluding records?
I guess you need something like
SELECT * FROM password_log t1 LEFT OUTER JOIN exclude_log t2 ON
t1.remote_host=t2.ip_block WHERE t2.ip_block IS NULL;
I am just guessing that both tables are related through remote_host and
ip_block,
Adolfo
On Thu, 2002-12-19 at 17:31, Eric Anderson wrote:
If I've got the following two tables:
CREATE TABLE password_log (
time_stamp int(11) unsigned NOT NULL default '0',
remote_host char(15) NOT NULL default '',
remote_user char(50) NOT NULL default '',
status smallint(5) unsigned NOT NULL default '0',
PRIMARY KEY (remote_host,remote_user,status),
KEY time_stamp (time_stamp),
KEY remote_user (remote_user),
KEY status (status)
) TYPE=MyISAM;
CREATE TABLE exclude_log (
ip_block char(15) NOT NULL default '',
PRIMARY KEY (ip_block)
) TYPE=MyISAM;
#
# Dumping data for table 'exclude_log'
#
INSERT INTO exclude_log VALUES ('152.163.188');
INSERT INTO exclude_log VALUES ('152.163.189');
INSERT INTO exclude_log VALUES ('152.163.206');
INSERT INTO exclude_log VALUES ('152.163.207');
INSERT INTO exclude_log VALUES ('195.93.64');
INSERT INTO exclude_log VALUES ('195.93.65');
INSERT INTO exclude_log VALUES ('195.93.66');
INSERT INTO exclude_log VALUES ('195.93.72');
INSERT INTO exclude_log VALUES ('195.93.73');
INSERT INTO exclude_log VALUES ('195.93.74');
INSERT INTO exclude_log VALUES ('205.188.208');
INSERT INTO exclude_log VALUES ('205.188.209');
And I want a list of everything in the 'password_log' table that doesn't
match up with any entry in the 'exclude_log' table, something along the
lines of:
select remote_user, substring_index(remote_host,'.',3) As ip_subnet from
password_log, exclude_log where remote_user != '-' and status=200 and
substring_index(remote_host,'.',3) != exclude_log.ip_block group by
ip_subnet order by remote_user, ip_subnet\g
+---+-+
| remote_user | ip_subnet |
+---+-+
| adamvernau| 207.79.8|
| amos | 24.53.232 |
| badmilk | 62.57.227 |
| [EMAIL PROTECTED] | 80.103.137 |
| beerbomb60| 12.80.11|
| BogusBob | 65.58.37|
| brendenm123 | 172.190.203 |
| brize | 217.39.73 |
| bruneau | 195.242.80 |
| chicken | 24.101.127 |
| cracking | 213.122.143 |
| DanielNoble | 172.151.183 |
| DESIO | 204.213.78 |
| diamond | 4.60.97 |
| dlgeo | 68.42.127 |
| ewing | 195.29.35 |
| fnadeau | 64.228.196 |
| frogman | 67.234.8|
| fujerome | 156.143.132 |
| geno6969 | 65.58.94|
| gravy01 | 81.86.119 |
| iftkharmaan | 62.255.64 |
| jamesz| 204.186.14 |
| jaysap| 12.235.160 |
| karak | 80.63.120 |
| kevin | 152.163.188 |
| kevin | 152.163.189 |
| kevin | 152.163.201 |
| kjelljanssonx | 213.66.154 |
| ksm70512 | 172.195.152 |
| leinad| 210.120.128 |
| leinad| 66.68.138 |
| lemurs| 24.60.185 |
| leolebr | 81.48.138 |
| Malakon | 24.186.21 |
| martisr | 217.39.29 |
| matglew | 81.98.84|
| mikeestela| 129.106.169 |
| Mirhos| 80.11.19|
| newyork | 62.134.74 |
| ordinary3 | 12.37.234 |
| pcomdh| 212.185.249 |
| pp-hosereed | 24.61.65|
| pp-lobeneath | 67.82.86|
| prodrifter72 | 66.75.124 |
| RbrtMackay| 81.77.108 |
| revrendpoe| 65.43.0 |
| rockey| 62.64.135 |
| rockey| 80.225.68 |
| stwgolfer | 64.221.53 |
| thebear | 205.188.208 |
| thebear | 205.188.209 |
| tooms | 63.225.249 |
| ulyanov | 12.5.196|
| WHATSUP | 172.173.81 |
| [EMAIL PROTECTED] | 80.143.42 |
| xmartyx | 68.5.149|
| xym180| 216.41.132 |
| zook10| 213.89.57 |
+---+-+
59 rows in set (0.03 sec)
As you can see, I still end up with records from the 'exclude_log'
table.. ?
-
Before posting, please check:
http://www.mysql.com/manual.php (the manual)
http://lists.mysql.com/
Tables read only
i cant create SQl tables andoso cant del the tables.. it always shows table is read only.. anyway to solve? - 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
Table \'User\' is read only
anyway to solve this SQL problems? - 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
