mysql - transaction processing question
hi...
if i process multiple subsequent sql where the later statements might be
dependent on the completion of the former/earlier sql statement, will this
cause a problem...
ie:
$dbh-do(SET AUTOCOMMIT=0);
eval
{
$dbh-do(insert into foo(Name,Vorname,Konto) values
('Ehmann','Andres',710235434));
$dbh-do(insert into testofix (Name)
select cat foo.Name
from foo
where foo.Name='tom');
};
in this case, the 2nd statement might/would depend on the results of the 1st
statement... would this possibly cause an issue...??? i didn't get a clear
understanding from mysql/google as to whether this might cause an issue...
comments/criticisms/thoughts/etc...
thanks
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
I change my password,now I can't go to my site.
Does anyone know how I can return to my site. I changed the mysql password and it says cant log in because my password isn't "yes" I change it but now what do I do any help would be great. L.
Spatial not in current 4.1.3 build?
It seems the Spatial Extension is not in the 4.1.3 binary (for Win)...? This is frustating, as I am a GIS/Spatial database developer using until now succesfully the earlier alpha in my projects. I am not a programmer and certainly do not want to go into building my own binaries. Why this exclusion and why is it not mentioned clearly anywhere? I only found out when my SQL queries returend an error message that somewhere said 'not in current build' or something similar... It seems the support/interest for Spatial is low anyhow, which is a shame, because the implementation at the moment works fine. With this low interest, it's no wonder most GIS people are looking to PostGIS, and myabe so will I, which is a shame because in many ways MySQL suits my needs better... Barend Köbben International Institute for Geo-information Sciences and Earth Observation (ITC) PO Box 6, 7500AA Enschede (The Netherlands) ph: +31 (0)53 4874253; fax: +31 (0)53 4874335 _ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SV: ER Diagrams with mysql
I have been using dbdesigner from fabforce. An excellent tool for visual design. It also has a reverse engineer feature. http://www.fabforce.net/dbdesigner4/ regards /Jonas Hi, i created a database with around 20 tables. However i created the tables by script writting all create table statements, create constraints, etc... I need now a Entity Relationship diagram so i have a visual information about my database. I tried MySQL Comand Center but i doens't have such tool. Does any one know either a tool i can throw a database creation script and it gives me the relationship diagram or a mysql tool that can use my installed database and generate the relationship diagrams??? Thanks, = beginner ___ Yahoo! Mail agora com 100MB, anti-spam e antivírus grátis! http://br.info.mail.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Spatial not in current 4.1.3 build?
Hi! On Jul 06, Barend K?bben wrote: It seems the Spatial Extension is not in the 4.1.3 binary (for Win)...? You're right :( Sorry for this. We recently made GIS features optional, but ON by default. Unfortunately, this default is the value of ./configure switch and does not apply to Windows. You may consider it a bug, and we will correct it ASAP. The lack of the extensive test suite that works on Windows didn't let us to notice it before the release (we're porting our test suite to Windows, so hopefully slips like that will never happen again). This is frustating, as I am a GIS/Spatial database developer using until now succesfully the earlier alpha in my projects. I am not a programmer and certainly do not want to go into building my own binaries. Why this exclusion and why is it not mentioned clearly anywhere? It was not intentional. Regards, Sergei -- __ ___ ___ __ / |/ /_ __/ __/ __ \/ / Sergei Golubchik [EMAIL PROTECTED] / /|_/ / // /\ \/ /_/ / /__ MySQL AB, Senior Software Developer /_/ /_/\_, /___/\___\_\___/ Osnabrueck, Germany ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query from mulitple tables where data will only be in one table but that table unkown
Ok, So here is what I am trying to do, I have 2 tables one with DSL IP addresses and one with Dialup addresses. I need to be able to query both tables and extract the information by IP address but I will not know which table the data is in before I do the search. Basically I want to have a query that states look in both tables and if the ip is found in either then give me some fields from that table What I have tried already is things like this: Select DISTINCT DIALUP.Full_Name, DIALUP.Framed_IP_Address, DSL.Full_Name, DSL.Framed_IP_Address From DIALUP, DSL Where DSL.Framed_IP_Address = 65.xxx.196.175 or DIALUP.Framed_IP_Address = 65.xxx.196.175 LIMIT 500 However that doesn't work because it returns rows from the table that the IP address is NOT in as well, because of the self join that is being done. All help will be GREATLY appreciated. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message.
More semaphore mystery
Hi gang, Back in the throes of semaphores that refuse to play nice with all the other kids on the playground ;-) I'm getting the following in my error file: -- SEMAPHORES -- OS WAIT ARRAY INFO: reservation count 79461482, signal count 75199550 --Thread 23744585 has waited at btr0cur.c line 401 for 556.00 seconds the semaph ore: X-lock on RW-latch at 0x868f29b0 created in file buf0buf.c line 438 a writer (thread id 23744585) has reserved it in mode wait exclusive number of readers 1, waiters flag 1 Last time read locked in file btr0cur.c line 401 Last time write locked in file btr0cur.c line 401 ...So what's a guy to do? Is there a way I can determine just what exactly is causing this? -- Mitch -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unexpected insert/update behaviour makes backup import impossible.
Description: On a table with a auto_increment/primary constraint is it not possible to insert an id with value 0 but it is possible to insert it with another id and use update to set it to 0, thereby making the backup import later fail due to the use of insert statements. How-To-Repeat: mysql DROP TABLE test; Query OK, 0 rows affected (0.07 sec) mysql CREATE TABLE `test` ( - `id` int(10) unsigned NOT NULL auto_increment, - PRIMARY KEY (`id`) - ) TYPE=InnoDB PACK_KEYS=1 ; Query OK, 0 rows affected (0.00 sec) mysql insert into test values(0); Query OK, 1 row affected (0.00 sec) mysql select * from test; ++ | id | ++ | 1 | ++ 1 row in set (0.00 sec) mysql update test set id = 0; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql mysql select * from test; ++ | id | ++ | 0 | ++ 1 row in set (0.00 sec) Fix: Submitter-Id: submitter ID Originator: Organization: MySQL support: none Category: mysql Class: sw-bug Release: mysql-4.0.18-standard (Official MySQL RPM) C compiler:2.95.3 C++ compiler: 2.95.3 Environment: System: Linux 2.4.20-8bigmem #1 SMP Thu Mar 13 17:32:29 EST 2003 i686 i686 i386 GNU/Linux Architecture: i686 Some paths: /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.2/specs Configured with: ../configure --prefix=/usr --mandir=/usr/share/man --infodir=/usr/share/info --enable-shared --enable-threads=posix --disable-checking --with-system-zlib --enable-__cxa_atexit --host=i386-redhat-linux Thread model: posix gcc version 3.2.2 20030222 (Red Hat Linux 3.2.2-5) Compilation info: CC='gcc' CFLAGS='-O2 -mcpu=i486 -fno-strength-reduce' CXX='gcc' CXXFLAGS='-O2 -mcpu=i486 -fno-strength-reduce-felide-constructors -fno-exceptions -fno-rtti ' LDFLAGS='' ASFLAGS='' LIBC: lrwxrwxrwx1 root root 13 Oct 3 2003 /lib/libc.so.6 - libc-2.3.2.so -rwxr-xr-x1 root root 1549556 Mar 14 2003 /lib/libc-2.3.2.so -rw-r--r--1 root root 2321376 Mar 14 2003 /usr/lib/libc.a -rw-r--r--1 root root 204 Mar 13 2003 /usr/lib/libc.so lrwxrwxrwx1 root root 10 Oct 3 2003 /usr/lib/libc-client.a - c-client.a Configure command: ./configure '--disable-shared' '--with-mysqld-ldflags=-all-static' '--with-client-ldflags=-all-static' '--with-server-suffix=-standard' '--without-embedded-server' '--without-berkeley-db' '--with-innodb' '--without-vio' '--without-openssl' '--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' '--libdir=/usr/lib' '--sysconfdir=/etc' '--datadir=/usr/share' '--localstatedir=/var/lib/mysql' '--infodir=/usr/share/info' '--includedir=/usr/include' '--mandir=/usr/share/man' '--enable-thread-safe-client' '--with-comment=Official MySQL RPM' 'CC=gcc' 'CFLAGS=-O2 -mcpu=i486 -fno-strength-reduce' 'CXXFLAGS=-O2 -mcpu=i486 -fno-strength-reduce -felide-constructors -fno-exceptions -fno-rtti ' 'CXX=gcc' -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
InnoDB table data
Hi all We allocate a limited amount of disk space for user's home directory and the user is required to keep everything under his home directory including his databases. I moved the database created for the user under his home directory and made a link to it from /var/lib/mysql. We use InnoDB as well as MyISAM tables. I can see his MyISAM *.frm, *.MYD and *.MYI are kept under his database directory. But I can see only table.frm are in his database directory for InnoDB tables. It looks like InnoDB table data belongs to his database are not kept under his database directory. Am I right? If I'm wrong, how do I keep InnoDB table data also under his database directory? Many thanks in advance. Kind regards Sagara __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL 4.1.3 and time zone tables
Is there supposed to be data loaded into the mysql.time_zone* tables? There is none in the Solaris binaries nor the Win32 binaries. Is this a bug? Does anyone know? Thanks, Rick
RE: InnoDB table data
InnoDB data is kept in the ib* files. Did you explicitly create a directory for your innoDB files? -Original Message- From: Sagara Wijetunga To: [EMAIL PROTECTED] Sent: 7/6/04 8:16 AM Subject: InnoDB table data Hi all We allocate a limited amount of disk space for user's home directory and the user is required to keep everything under his home directory including his databases. I moved the database created for the user under his home directory and made a link to it from /var/lib/mysql. We use InnoDB as well as MyISAM tables. I can see his MyISAM *.frm, *.MYD and *.MYI are kept under his database directory. But I can see only table.frm are in his database directory for InnoDB tables. It looks like InnoDB table data belongs to his database are not kept under his database directory. Am I right? If I'm wrong, how do I keep InnoDB table data also under his database directory? Many thanks in advance. Kind regards Sagara __ Do you Yahoo!? Yahoo! Mail is new and improved - Check it out! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: authentication error
Chip Bell [EMAIL PROTECTED] wrote: The email users authenticate against a MySQL table... Then it's not about MySQL, it's about the imap/pop3 server. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select query that uses a temporary table
Lorderon [EMAIL PROTECTED] wrote: Mabye, is there a way to tell MySQL to limit the temporary table up to 500 rows? so, when a row is matching into the top 500 rows, the last row will be dropped out (in case the table is on limit), and the new matched row will be inserted into the right place in the temporary table... To find out top 500 of 10,000 rows ordered by some criteria you anyway need to sort these 10,000 rows. :) -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: bugzilla not working now after MySQL re-installed
Nguyen, Long P (Mission Systems) [EMAIL PROTECTED] wrote: Your thoughts on this would be appreciated. MySQL was re-installed and now Bugzilla is not working now I am getting this error when I bring up - http://localhost/bugzilla/ http://localhost/bugzilla/ The scripts were looking for MySQL socket located in /tmp. Now as you installed the RPM version, the socket is no longer available there. You can tweak my.cnf to tell client libraries where to find the socket. Add socket = /var/lib/mysql/mysql.sock to /etc/my.cnf to [client] section. For more info, see http://dev.mysql.com/doc/mysql/en/Option_files.html -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB table data
Sagara Wijetunga [EMAIL PROTECTED] wrote: I can see his MyISAM *.frm, *.MYD and *.MYI are kept under his database directory. But I can see only table.frm are in his database directory for InnoDB tables. It looks like InnoDB table data belongs to his database are not kept under his database directory. Am I right? Right. InnoDB uses big single (or multiple) tablespace for all data. But you can also use different tablespaces for different tables. See http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Bug in fulltext index creation on very huge sets of data?
Hi,
I got the following values:
key_buffer_size = 256M
myisam max extra sort file size = 8000M
myisam max sort file size = 8000M
myisam sort buffer size = 128M
But that big table (MYD = 2397 MB), rows = 5 355 866 still won't index
in full text.
Disk space is enough (25GB of free space), no error reported in the log.
Using MySQL 4.0.14, doesn't work better with 4.0.20 (running under Win32).
Where should I look at?
Vincent
Sergei Golubchik wrote:
Hi!
First - please use mailing lists for these questions, don't write to me
directly. You mail can be filtered in some obscure folder and I won't
see it for months. Also, there are many experienced users on the list,
so you can get a reply faster.
On Jun 20, Vincent Bouret wrote:
Hi,
I have been using the great fulltext capabilities of MySQL extensively
over the past year without having any problem. I just got a strange
problem, the fulltext index creation on very huge sets of data doesn't
seem to work.
I have switched from 4.0.14 to 4.0.20 and rebuilt the database from
scratch. I'm adding a lot of data on different tables (4 TEXT
fields/table). At the end, I issue a ALTER TABLE 'tablename' ADD
FULLTEXT ('field1'), ADD FULLTEXT ('field2'), ADD FULLTEXT ('field3'),
ADD FULLTEXT ('field4');, this SQL command worked just fine for MYD as
large as 495MB. Strangely, this same command doesn't work with table
with MYD size of 776MB (2.5 million rows), 2.4GB (5.3 million rows),
900MB (2.1 million rows). On the first 776MB table, I can issue a
ALTER TABLE 'table1' ADD FULLTEXT ('field1'); and one fulltext index
is created, but I cannot issue one more: ALTER TABLE 'table1' ADD
FULLTEXT ('field2'); on a different field. I see three temporary files
The way MySQL works, when you issue ALTER TABLE the table is rebuild
from scratch, so when you add *second* fulltext index with ALTER TABLE,
in fact, the first index is being rebuilt too. Thus you have no benefits
in adding indexes one by one - always do it in one ALTER TABLE.
being created, the MYI grows larger and larger and every temp files
disapear, and no fulltext index is created on field2. Even worse, on
the 900MB table, I cannot issue even a single ALTER TABLE 'table1' ADD
FULLTEXT ('field1');
Check myisam_... server variables (especially ...sort_file... related)
Check SHOW PROCESSLIST duing the ALTER process, error log, and available
disk space.
But please, direct your replies to the list!
Strangely, before rebuilding the database from scratch, I was doing
daily inserts on the content of these tables and the fulltext index
was updated sucessfully. The the MYD were the same size as they are
currently, but I think the index first got created with much smaller
tables.
When you create indexes on the existing data and when you add data to
the index MySQL uses different algoritms to create/modify indexes.
Thus is the first one - in ALTER TABLE - has a bug, you won't trigger it
with regular updates.
Regards,
Sergei
Re: slow response time
Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. Either upgrade to FreeBSD 5 or switch to Linux or Solaris. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64 Bit Support
David Griffiths [EMAIL PROTECTED] wrote: Sorry - didn't read your email closely enough. The Windows version is not native - runs under Cygwin. Is there a version of Cygwin for the Itanium 2? Wrong. It is a native application. :) No, there are no Itanium binaries for Windows. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DISTINCT issues with 3.23.54
My hosting company is running MySQL 3.23.54-log. I have a problem where I am requesting DISTINCT rows, but am getting repeats. Here is my query: select distinct member.ID as ID from member, address, joinstatuschange, specialtyheld, metroalias where address.memberID = member.ID and specialtyheld.memberID = member.ID and metroalias.memberID = member.ID and joinstatuschange.memberID = member.ID and joinstatuschange.Reference = 'current' and JoinStatusID in (4,7,11) and BlockFAP = 0 and (SpecialtyID = '9' or SpecialtyID = '' or SpecialtyID = '') and State = 'MI' order by rand() When I run this on MySQL 3.23.54-log I get 78 rows, with many dups. When I move the DB to MySQL 4.0.18-standard and run the query I get 21 rows, no dups. Is this a known bug? Is there any work around, besides upgrading (which is planned but not immediately). Thanks! ~ Mike - Mike Zornek Web Designer, Media Developer, Programmer and Geek Personal site: http://MikeZornek.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Concurrency Question
Javier Diaz [EMAIL PROTECTED] wrote: 3- Replication of these tables to another server ?? This is classical solution to the problem and probably the easiest to provide. Just do SELECTs on the slave server. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
first record is skipped in select and shows up last
What would be the cause of the following situation? I do a select * from a table and sort by the pk. The first line/record is notably empty/not there, and content starts w/2nd record, and what should be the 1st record appears at the end after the what should be the very last record. But doing a limit 0,1 will get the 1st rec btw. Is there more info I can give you? I'm I overlooking something? Thanks, Lee G. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Strange server crashes with large table and myisamchk
Hanno Fietz [EMAIL PROTECTED] wrote:
Jul 2 03:10:28 t56 kernel: hda: dma_intr: status=0x51 { DriveReadySeekComplete
Error }
Jul 2 03:10:28 t56 kernel: hda: dma_intr: error=0x40 { UncorrectableError },
LBAsect=429367, sector=316864
Jul 2 03:10:28 t56 kernel: end_request: I/O error, dev 03:02 (hda), sector 316864
Definitely, a hardware problem. Either your harddrive is dead (dying) or the
DMA. The first is more likely.
--
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
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTEL vs. SPARC
mac [EMAIL PROTECTED] wrote: does the speed of mysql depends more on things like the processor than other resources? Disk speed memory size are much more important then the CPU. Processor is used to calculate while MySQL is a database. And databases move data back and forth. if so: what kind of general suggestions can be made about using select-statements on huge tables to be fast over different platforms? Allocate as much memory as you can for key_buffer. Generally, 70% of RAM is a good starting point. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Create Temporary Table, incorrect rows
Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUI for mysql
gowthaman ramasamy [EMAIL PROTECTED] wrote: i use MySQL 4.0.20 on RH7.3 on a sun fire workstation. Can some one of you suggest a good GUI for mysql. I also use Mysql 4.0.13-pc-linux-i686 on P4. MySQL Administrator and MySQL Control Center are a good choice. See at http://www.mysql.com/products/ -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: INTEL vs. SPARC
mac [EMAIL PROTECTED] wrote: Are you running Linux or Solaris on these servers? sparc: solaris 9 intel: debian-linux (2.4.21-kernel) To achieve best performance, you better get the Official MySQL binaries from http://www.mysql.com rather then compiling by yourself. This is due to glibc patches applied to glibc for official binaries. -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Can't execute the given command because you have active locked tables or an active transaction
Hi all, i have a problem with a db innoDB 4.0.20 server. The error 'Can't execute the given command because you have active locked tables or an active transaction' happens on the second truncate (the fist table trucante correctly) of a db composed by three tables, without any foreign keys. The db is READ-COMMITTED mode, and no other strange things are around. I woldu like to truncate all the table, then insert new records. I found that a bug was discovered on 4.0.2, but i have 4.0.20... Thanks for any help. Michele -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Temporary Table update
I added a LIMIT clause to my create table command and now all data is present. Is the 1000 row limit a standard that must always be overridden? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL IPv6 enabled ready !!!
Rafael Martinez Torres [EMAIL PROTECTED] wrote: you will find a patch to make the last stable version on MySQL, (4.0.8) You are more than a year past the schedule: the last stable version of MySQL is 4.0.20. :-)) -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Thanks. That is a much better way to find dupes. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Peter Brawley [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:11 AM To: Paul McNeil; MySQL General Subject: Re: Create Temporary Table, incorrect rows A more direct way to find dupes ... SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt 1 PB - Original Message - From: Paul McNeil To: MySQL General Sent: Tuesday, July 06, 2004 9:30 AM Subject: Create Temporary Table, incorrect rows Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; When I run this it says that the temp table has only 1000 rows. Why? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
sock file
When trying to start mysql on a lx50 I get the following message: Error 2002 Cannot connect to local MySQL server through socket /var/lib/mysql/mysql.sock (111). What can I do to resolve this problem pls? Regards Michael Johnson
Re: InnoDB table data
--- Egor Egorov [EMAIL PROTECTED] wrote: Sagara Wijetunga [EMAIL PROTECTED] wrote: I can see his MyISAM *.frm, *.MYD and *.MYI are kept under his database directory. But I can see only table.frm are in his database directory for InnoDB tables. It looks like InnoDB table data belongs to his database are not kept under his database directory. Am I right? Right. InnoDB uses big single (or multiple) tablespace for all data. But you can also use different tablespaces for different tables. See http://dev.mysql.com/doc/mysql/en/Multiple_tablespaces.html Yep, this is the feature I was looking for. Thank you very much. Sagara __ Do you Yahoo!? Yahoo! Mail - 50x more storage than other providers! http://promotions.yahoo.com/new_mail -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tue, Jul 06, 2004 at 10:30:38AM -0400, Paul McNeil wrote: Good day to all. I have a table and want to find duplicate info in the table. I know duplicated info exists by running the following... Select count(*) from myTable - 141123 Select distinct(myData) from myTable - 1411000 I presume there's a typo here: count(*) should have returned a larger number than distinct(myData), not smaller. So I created a temporary table to store distinct records and want to do a join with original table to see what records are duplicates. Create Temporary Table A Select distinct(mydata),rowID from myTable; This is a different query than above. What cout do you get from this query: Select distinct(mydata),rowID from myTable; Yes, it will be huge, but you want that count of selected rows. God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. -- Brian Reichert [EMAIL PROTECTED] 37 Crystal Ave. #303Daytime number: (603) 434-6842 Derry NH 03038-1713 USA BSD admin/developer at large -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Query from mulitple tables where data will only be in one table but that table unkown
I don't think I know enough about the tables (is there a primary key) or what the end result should look like (one row per IP address, do you need to know what table matched) to give a very good answer, but a simple UNION will probably get you part of the way there: SELECT DISTINCT 'DIALUP', Full_Name, Framed_IP_Address FROM DIALUP WHERE Framed_IP_Address = 127.0.0.1 UNION SELECT DISTINCT 'DSL', Full_Name, Framed_IP_Address FROM DSL WHERE Framed_IP_Address = 127.0.0.1 mysql select * FROM DIALUP; +---+---+ | Full_Name | Framed_IP_Address | +---+---+ | Tom | 127.0.0.1 | | Dick | 127.0.0.2 | | Harry | 127.0.0.4 | +---+---+ 3 rows in set (0.00 sec) mysql select * FROM DSL; +---+---+ | Full_Name | Framed_IP_Address | +---+---+ | Jane | 127.0.0.1 | | Jill | 127.0.0.2 | | Janet | 127.0.0.3 | +---+---+ 3 rows in set (0.00 sec) mysql SELECT DISTINCT 'DIALUP', Full_Name, Framed_IP_Address - FROM DIALUP WHERE Framed_IP_Address = 127.0.0.1 - UNION - SELECT DISTINCT 'DSL', Full_Name, Framed_IP_Address - FROM DSL WHERE Framed_IP_Address = 127.0.0.1; ++---+---+ | DIALUP | Full_Name | Framed_IP_Address | ++---+---+ | DIALUP | Tom | 127.0.0.1 | | DSL| Jane | 127.0.0.1 | ++---+---+ 2 rows in set (0.00 sec) Eamon Daly - Original Message - From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 7:39 AM Subject: Query from mulitple tables where data will only be in one table but that table unkown Ok, So here is what I am trying to do, I have 2 tables one with DSL IP addresses and one with Dialup addresses. I need to be able to query both tables and extract the information by IP address but I will not know which table the data is in before I do the search. Basically I want to have a query that states look in both tables and if the ip is found in either then give me some fields from that table What I have tried already is things like this: Select DISTINCT DIALUP.Full_Name, DIALUP.Framed_IP_Address, DSL.Full_Name, DSL.Framed_IP_Address From DIALUP, DSL Where DSL.Framed_IP_Address = 65.xxx.196.175 or DIALUP.Framed_IP_Address = 65.xxx.196.175 LIMIT 500 However that doesn't work because it returns rows from the table that the IP address is NOT in as well, because of the self join that is being done. All help will be GREATLY appreciated. Chris Hood Investigator Verizon Global Security Operations Center Email: mailto:[EMAIL PROTECTED] [EMAIL PROTECTED] Desk: 972.399.5900 Verizon Proprietary NOTICE - This message and any attached files may contain information that is confidential and/or subject of legal privilege intended only for the use by the intended recipient. If you are not the intended recipient or the person responsible for delivering the message to the intended recipient, be advised that you have received this message in error and that any dissemination, copying or use of this message or attachment is strictly forbidden, as is the disclosure of the information therein. If you have received this message in error please notify the sender immediately and delete the message. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Create Temporary Table, incorrect rows
Yes I am using mysqlcc. Is that why the 1000 row limit? God Bless Paul C. McNeil Developer in Java, MS-SQL, MySQL, and web technologies. GOD BLESS AMERICA! To God Be The Glory! -Original Message- From: Duncan Hill [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 11:05 AM To: [EMAIL PROTECTED] Subject: Re: Create Temporary Table, incorrect rows On Tuesday 06 July 2004 15:30, Paul McNeil might have typed: When I run this it says that the temp table has only 1000 rows. Why? Are you using mysqlcc? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
User variables optimization of IF()
Hi everybody. I often try to parametrize out my queries as much as possible with user-variables. So, say you have a general query for all users: --- SELECT ... FROM users --- It's nice to do this: SET @USER_ID:= NULL; SELECT ... FROM users WHERE IF(@USER_ID IS NOT NULL,[EMAIL PROTECTED],1) --- This *works*, but when you set @USER_ID to a variable, the 4.1 optimizer doesn't optimize it very well: it does a table-scan, instead of using the index on user_id. Has anyone got a good alternative that will do less table-scans? Or, if a developer is listening, can you give us details on how the optimizer deals with IF()? Thanks. Tom. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
using fulltext search by using match()
i have a sql statement to execute in mysql for fulltext search like this.
select url from search where match(description) against ('oracle
application server') limit 20
but the record set is pretty big, so i want to return first 20 rows. but the
first 20 rows that are returned don't include this phrase in order oracle
application server.
I want to return first 20 rows that contain this phrase in order oracle
application server and narrow it down(maybe rows that contain one of these
three words). but this sql statement returns first rows that contain any
words.
i tried where description like '%oracle application server%'. it works ok,
but it's really slow, even though i created an index on description column.
I am using java to implement this like this.
stmt = conn.createStatement();
stmt.execute(selectQuery);
rs = stmt.getResultSet();
i searched the mailing list, but i can't find an answer that i want.
so if anybody knows how to do it, please let me know.. i'd appreciated
it...
_
FREE pop-up blocking with the new MSN Toolbar get it now!
http://toolbar.msn.click-url.com/go/onm00200415ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 05:07:24PM +0300, Egor Egorov wrote: Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. We're happily running MySQL on FreeBSD 4.x w/LinuxThreads at Yahoo. So I'm not sure how you back that claim. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, 6 Jul 2004, Egor Egorov wrote: Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. I thought that was pretty old news. I had also tried with Linux Threads and found similar poor performance. Either upgrade to FreeBSD 5 or switch to Linux or Solaris. Neither option is really feasible. vpopmail does have Postgres support, perhaps that's my answer. :) Thanks, Charles -- 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 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: first record is skipped in select and shows up last
At 10:22 -0400 7/6/04, leegold wrote: What would be the cause of the following situation? I do a select * from a table and sort by the pk. The first line/record is notably empty/not there, and content starts w/2nd record, and what should be the 1st record appears at the end after the what should be the very last record. But doing a limit 0,1 will get the 1st rec btw. Is there more info I can give you? I'm I overlooking something? The actual queries and some sample output to demonstrate the problem would be helpful. Otherwise, we're just guessing. -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: using fulltext search by using match()
i have a sql statement to execute in mysql for fulltext search like this.
select url from search where match(description) against ('oracle
application server') limit 20
but the record set is pretty big, so i want to return first 20 rows. but
the first 20 rows that are returned don't include this phrase in order
oracle application server.
I want to return first 20 rows that contain this phrase in order oracle
application server and narrow it down(maybe rows that contain one of these
three words). but this sql statement returns first rows that contain any
words.
i tried where description like '%oracle application server%'. it works ok,
but it's really slow, even though i created an index on description column.
I am using java to implement this like this.
stmt = conn.createStatement();
stmt.execute(selectQuery);
rs = stmt.getResultSet();
i searched the mailing list, but i can't find an answer that i want.
so if anybody knows how to do it, please let me know.. i'd appreciated
it...
_
Express yourself instantly with MSN Messenger! Download today - it's FREE!
http://messenger.msn.click-url.com/go/onm00200471ave/direct/01/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
update query question
hello, i've had to change some of the tables in my db to accomodate some greater flexibility in the application that uses it and because of this i need to go through and update all the records. i've done one table by hand and it had about 100 records and took about 20 minutes. but this next table has about 550 records and i really don't feel like doing this all by hand. i'm using MySQL Control Center to do this editing so i'd like to know if there's a single SQL statement i could use to update all the rows. here is a simple representation: products: (pay no attention to the poor choice in column names. this is a retrofitting and will be fixed in later versions.) +--+---+ | id | sequential_id | +--+---+ | PRDX-41 | 1 | | ABCX-01 | 2 | | FF00-11 | 3 | \/\/\/\/\/\/\/\/ | ETC0-99 | 500 | +--+---+ the 'prod_sequential_id' column was added later to the products_categories table. products_categories: +-+-+++ | id | prod_id | prod_sequential_id | cat_id | +-+-+++ | 1 | PRDX-41 | 0 | 41 | | 2 | PRDX-41 | 0 | 15 | | 3 | ABCX-01 | 0 | 13 | | 4 | FF00-11 | 0 | 89 | \/\/\/\/\/\/\/\/ | 610 | ETC0-99 | 0 | 41 | +-+-+++ so... as you can see, prod_sequential_id has all 0's in its column. it should contain the value of products.sequential_id WHERE products_categories.prod_id = products.id. the problem is that i'm not sure how to do this all in one statement (or if it's even possible): (i know the following does not work, but it's basically the logic i think i need.) UPDATE products_categories AS pc, products AS p SET pc.prod_sequential_id = p.id WHERE pc.prod_id = p.id; thanks for your help. chris. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Create Temporary Table, incorrect rows
On Tuesday 06 July 2004 17:20, Paul McNeil wrote: Yes I am using mysqlcc. Is that why the 1000 row limit? Yes. It's under the server options tag (don't have it in front of me to give exact details). If you right click the server connection, under I think properties, there's a query limit. Set to 0 and no limit. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 12:48:39PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Egor Egorov wrote: Charles Sprickman [EMAIL PROTECTED] wrote: So I haven't really done much to optimize things, as this seems like a fairly light load. I'm running 4.0.20 on FreeBSD 4.8 (port build w/Linux Threads). Reliability and performance is not what you should expect to find in FreeBSD 4.x. MySQL doesn't perform well on it because of threads problems in OS itself. I thought that was pretty old news. I had also tried with Linux Threads and found similar poor performance. Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Thanks, Charles Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 64 Bit Support
Yah - Jeremy Z. mentioned that a few days ago as well. Guess I have it confused with Posgres (we don't run MySQL on Windows, so I don't pay much attention to it except the performance issues most people seem to mention, which I guess made me assume Cygwin). David Egor Egorov wrote: David Griffiths [EMAIL PROTECTED] wrote: Sorry - didn't read your email closely enough. The Windows version is not native - runs under Cygwin. Is there a version of Cygwin for the Itanium 2? Wrong. It is a native application. :) No, there are no Itanium binaries for Windows. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysql...transaction question
hi..
i'm trying to understand if there's a difference/better reason for doing
transactions using either of the following psuedo approaches...
approach 1 does the commit inside the eval block, whereas approach 2 has the
commit outside the eval block... i've seen sample code with transactions
handled both ways...
approach 1:
eval
{
$dbh-do(do something);
# got this far means no errors
# commit
$dbh-commit();
};
# check errors/rollback
if ($@)
{
$dbh-rollback();
}
approach 2:
eval
{
$dbh-do(do something);
};
# check errors/rollback
if ($@)
{
$dbh-rollback();
}
else
{
# commit
$dbh-commit();
}
any comments/criticisms/thoughts/etc...
thanks...
-bruce
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow response time
Are you running spam assassin? I'm finding that my slow query log is showing the spam queries to really be dragging. -Original Message- From: Charles Sprickman [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 2:29 PM To: [EMAIL PROTECTED] Cc: Egor Egorov Subject: Re: slow response time On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Thanks, Charles Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql...transaction question
bruce wrote:
hi..
i'm trying to understand if there's a difference/better reason for doing
transactions using either of the following psuedo approaches...
approach 1 does the commit inside the eval block, whereas approach 2 has the
commit outside the eval block... i've seen sample code with transactions
handled both ways...
approach 1:
eval
{
$dbh-do(do something);
# got this far means no errors
# commit
$dbh-commit();
};
# check errors/rollback
if ($@)
{
$dbh-rollback();
}
approach 2:
eval
{
$dbh-do(do something);
};
# check errors/rollback
if ($@)
{
$dbh-rollback();
}
else
{
# commit
$dbh-commit();
}
any comments/criticisms/thoughts/etc...
thanks...
-bruce
I think the commit needs to be inside the eval, because
thats when problems might arise. Also, I assume that you
at some point set AutoCommit = 0. I usually do transctions
like this:
sub test {
eval { $dbh-begin_work };
if ($@) {
print begin transaction failed: $@;
return;
}
eval {
.. do some stuf ..
$dbh-commit;
};
if ($@) {
eval { $dbh-rollback };
print transaction failed: $@;
} else {
print data committed successfully.\n;
}
}
hope this helps,
Mayuran.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: slow response time
On Tue, 6 Jul 2004, Chip Bell wrote: Are you running spam assassin? I'm finding that my slow query log is showing the spam queries to really be dragging. I am, but that's on three other boxes. This one is just qmail/vpopmail (vpopmail hits mysql for user info). If I had to guess at when exactly this happens, I'd say it's when qmail starts running through it's queue of undeliverable junk. vmstat reports a ton of context switch hits when this happens. Plenty of memory though, and interactive performance of other services (http, smtpd, cli) is fine. I also see that vpopmail simply doesn't call mysql_close() in many places; the vpopmail folk claim that this is OK, but I wonder if it's upsetting mysql in some way. Thanks, Charles -Original Message- From: Charles Sprickman [mailto:[EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 2:29 PM To: [EMAIL PROTECTED] Cc: Egor Egorov Subject: Re: slow response time On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Thanks, Charles Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Interesting. Do you find the mysqld process using alot of CPU? Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Without looking at the box, I can hazard a few guesses. I suspect you're seeing one of two things (or both). I suspect that qmail, like some mail servers, makes heavy use of syncrous disk writes. And it's probably competing with MySQL for precious disk I/O resources. (Are they sharing a disk?) What's iostat look like? If you're not using LinuxThreads, you'll find that MySQL on FreeBSD behaves very poorly in high I/O situations. FreeBSD's userspace, self-scheduling threads just suck for database applications. There's no way around that. Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Concatenar Field's
Estoy teniendo un problema cuando ejecuto esta sentencia. SELECT Sum(tabla_manzana.TOTPOB) AS POBLACION, Sum(tabla_manzana.TOTVIVIEN) AS VIVIENDA, tabla_manzana.CODUBIGEO tabla_manzana.CODCCPP99 AS IDCCPP, tabla_manzana.NOMCCPP99 FROM tabla_manzana GROUP BY tabla_manzana.CODUBIGEO, tabla_manzana.CODCCPP99, tabla_manzana.NOMCCPP99; En el Access me funciona, pero en MySQL en el campos (tabla_manzana.CODUBIGEO tabla_manzana.CODCCPP99 AS IDCCPP) que trato de conactenar, dan como resultados numero cuando estos son textos. Gracias. Wilder O. Castelo Rojas Instituto Nacional de Cultura Lima-Perú
RE: Concatenar Field's
Ud necesista usar, CONCAT(tabla_manzan.CODUGIGEO, table_manaza.CODCCPP99) AS
IDCCPP
o si Ud quiere un espacio
CONCAT_WS(' ', tabla_manzan.CODUGIGEO, table_manaza.CODCCPP99) AS IDCCPP
-Original Message-
From: Wilder Castelo
To: [EMAIL PROTECTED]
Sent: 7/6/04 3:08 PM
Subject: Concatenar Field's
Estoy teniendo un problema cuando ejecuto esta sentencia.
SELECT Sum(tabla_manzana.TOTPOB) AS POBLACION,
Sum(tabla_manzana.TOTVIVIEN)
AS VIVIENDA, tabla_manzana.CODUBIGEO tabla_manzana.CODCCPP99 AS
IDCCPP,
tabla_manzana.NOMCCPP99
FROM tabla_manzana
GROUP BY tabla_manzana.CODUBIGEO, tabla_manzana.CODCCPP99,
tabla_manzana.NOMCCPP99;
En el Access me funciona, pero en MySQL en el campos
(tabla_manzana.CODUBIGEO tabla_manzana.CODCCPP99 AS IDCCPP) que trato
de
conactenar, dan como resultados numero cuando estos son textos.
Gracias.
Wilder O. Castelo Rojas
Instituto Nacional de Cultura
Lima-Perú
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, 6 Jul 2004, Jeremy Zawodny wrote: On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Interesting. Do you find the mysqld process using alot of CPU? No. There's no one process chugging CPU juice, but many many small processes (qmail-remote, qmail-local, maildrop, etc.). Looking at vmstat, it seems like just keeping track of all the processes and scheduling them is problematic (sorry about the wrapping): procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy id 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 2 3 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 2 9 90 0 13 0 780712 63220 1926 0 0 0 2652 0 50 0 577 7597 911 7 9 83 3 15 0 781320 62228 1320 0 0 0 952 0 90 0 515 5978 471 10 5 85 5 13 0 782204 62120 1834 0 0 0 1410 0 40 0 635 10150 812 17 16 68 0 14 0 783496 61168 2102 0 0 0 1550 0 109 0 643 17888 1217 28 15 57 From the vmstat manpage, it appears that the faults line, specifically cs represents context switches, which the best I can understand is that that indicates how much the cpus are thrashing from process to process in the run queue. At some point this number gets high enough that processes block even though there's no memory shortage, swapping, or disk i/o problems: faults Trap/interrupt rate averages per second over last 5 seconds. in device interrupts per interval (including clock interrupts) sy system calls per interval cs cpu context switch rate (switches/interval) This might be a red herring, as I have no similarly loaded boxes to compare these numbers to. Out of the blue mysql will start logging stuff like this in the slow query log: # administrator command: Ping; # [EMAIL PROTECTED]: squirrelmail[squirrelmail] @ localhost [] # Query_time: 47 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 # administrator command: Ping; # [EMAIL PROTECTED]: vpopmail[vpopmail] @ localhost [] # Query_time: 48 Lock_time: 0 Rows_sent: 0 Rows_examined: 0 Load is moderate, but not so bad that any other services on here are affected in any perceptible way. Without looking at the box, I can hazard a few guesses. I suspect you're seeing one of two things (or both). I suspect that qmail, like some mail servers, makes heavy use of syncrous disk writes. And it's probably competing with MySQL for precious disk I/O resources. (Are they sharing a disk?) Everything's on a 4 disk RAID 1+0 array. What's iostat look like? Not very heavy, it doesn't seem disk bound: tty da0 md0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id -0 -85 0.00 0 0.00 0.00 0 0.00 -90 -3-107 -5306 0 30 8.77 26 0.22 0.00 0 0.00 6 0 10 1 83 0 30 10.57 66 0.68 0.00 0 0.00 3 0 11 1 84 0 30 8.79 61 0.53 0.00 0 0.00 17 0 9 1 73 0 30 9.80 35 0.33 0.00 0 0.00 4 0 7 1 88 0 30 1.00 1 0.00 0.00 0 0.00 1 0 5 0 94 If you're not using LinuxThreads, you'll find that MySQL on FreeBSD behaves very poorly in high I/O situations. FreeBSD's userspace, self-scheduling threads just suck for database applications. There's no way around that. I was using LinuxThreads, but found that it made the situation worse; I think scheduling a few hundred procs was harder than dealing with the threads; just speculation on my part... I also couldn't find good docs explaining LT on BSD very well. I was worried that each thread (in reality, a process) in the LT model had it's own memory footprint. Of course I knew about LT from reading your site... I'll also reiterate a few datapoints about mysql for any latecomers: -Most queries are simple selects to grab user info (check password, check homedir). -The few updates or inserts are for a relay table for smtp use; it simply tracks where each pop/imap user connects from and smtp can refer to that table to see if someone should be able to relay mail. -There is also an insert/update on a last auth from... table. The hardware is a dual Athlon MP-1600 smp box with 1GB of RAM. Queries/second is about 15 tops, and there is a hard limit on the mail side; the box will only accept a finite amount of inbound/outbound smtp connections. Also, out of curiousity, the db servers that you've mentioned Yahoo is running are all likely dedicated mysql boxes,
Mysql 4.0.20, InnoDB my.cnf problems
Hello all, I would like to create InnoDB databases within MySQL. I have installed MySQL 4.0.20 and I have tried to uncomment the following on my /etc/my.cnf. MySQL is run on top of Fedora Core 2 Linux. # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /var/lib/mysql/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /var/lib/mysql/ #innodb_log_arch_dir = /var/lib/mysql/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 128M #innodb_additional_mem_pool_size = 20M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 64M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 After doing that, the service simply does not restart. There are no MySQL processes running. Then, by commenting back the server starts. Can anyone point me out what is going on wrong in this scenario? Thanks in Advance, C.F. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: MySQL and Point of Sale
Hey there! Does anyone know where I can find information regarding connecting MySQL and a Point of Sale device? Kind Regards Schalk Neethling Web Developer.Designer.Programmer.President Volume4.Development.Multimedia.Branding emotionalize.conceptualize.visualize.realize Tel: +27125468436 Fax: +27125468436 email:[EMAIL PROTECTED] web: www.volume4.co.za This message contains information that is considered to be sensitive or confidential and may not be forwarded or disclosed to any other party without the permission of the sender. If you received this message in error, please notify me immediately so that I can correct and delete the original email. Thank you.
Miicroweb
Hi, I have an application that run´s in a CD supported by Microweb, but i have a problem with de db included in the folder data of mysql, in the cd isn´t all the tables of the DB1, when i run microweb and then load the file, that is the backup, run well, but when i see the folder of data, isn t all the tables. Can help me? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL and Point of Sale
On Wed, Jul 07, 2004 at 12:05:01AM +0200, Schalk wrote: Hey there! Does anyone know where I can find information regarding connecting MySQL and a Point of Sale device? I suspect it'd depend on the device in question... -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
DBF to MySQL
I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: DBF to MySQL
Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
C API 3.23 to 4.1
I am going to upgrade my MySQL server from 3.23 to 4.1, but I have a C program that needs to continue to connect to the new server, and it can't be recompiled. Is the old API 100% backwards compatible with a 4.1 server? I assume the performance is the same? Ron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: C API 3.23 to 4.1
On Tue, Jul 06, 2004 at 03:40:02PM -0700, Ron Gilbert wrote: I am going to upgrade my MySQL server from 3.23 to 4.1, but I have a C program that needs to continue to connect to the new server, and it can't be recompiled. Is the old API 100% backwards compatible with a 4.1 server? I assume the performance is the same? You're confusing the API and the protocol. A 4.1 server can speak to a 3.23 client just fine if configured properly. See: http://dev.mysql.com/doc/mysql/en/Password_hashing.html Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 04:26:04PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: On Tue, Jul 06, 2004 at 02:29:16PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: Do you have a summary of the poor performance somewhere? Or at least a sense of where you think the bottleneck is? The best I can tell you is that mysql + moderate qmail load on the same box causes problems. I don't know if this is a scheduler issue with FreeBSD, or just qmail telling me that I should be using Postfix. Interesting. Do you find the mysqld process using alot of CPU? No. There's no one process chugging CPU juice, but many many small processes (qmail-remote, qmail-local, maildrop, etc.). Looking at vmstat, it seems like just keeping track of all the processes and scheduling them is problematic (sorry about the wrapping): procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90 [snip] Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( From the vmstat manpage, it appears that the faults line, specifically cs represents context switches, which the best I can understand is that that indicates how much the cpus are thrashing from process to process in the run queue. At some point this number gets high enough that processes block even though there's no memory shortage, swapping, or disk i/o problems: faults Trap/interrupt rate averages per second over last 5 seconds. in device interrupts per interval (including clock interrupts) sy system calls per interval cs cpu context switch rate (switches/interval) This might be a red herring, as I have no similarly loaded boxes to compare these numbers to. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) Without looking at the box, I can hazard a few guesses. I suspect you're seeing one of two things (or both). I suspect that qmail, like some mail servers, makes heavy use of syncrous disk writes. And it's probably competing with MySQL for precious disk I/O resources. (Are they sharing a disk?) Everything's on a 4 disk RAID 1+0 array. What's iostat look like? Not very heavy, it doesn't seem disk bound: tty da0 md0 cpu tin tout KB/t tps MB/s KB/t tps MB/s us ni sy in id -0 -85 0.00 0 0.00 0.00 0 0.00 -90 -3-107 -5306 0 30 8.77 26 0.22 0.00 0 0.00 6 0 10 1 83 0 30 10.57 66 0.68 0.00 0 0.00 3 0 11 1 84 0 30 8.79 61 0.53 0.00 0 0.00 17 0 9 1 73 0 30 9.80 35 0.33 0.00 0 0.00 4 0 7 1 88 0 30 1.00 1 0.00 0.00 0 0.00 1 0 5 0 94 Yeah, you're not doing much I/O at all. Hmm. If you're not using LinuxThreads, you'll find that MySQL on FreeBSD behaves very poorly in high I/O situations. FreeBSD's userspace, self-scheduling threads just suck for database applications. There's no way around that. I was using LinuxThreads, but found that it made the situation worse; I think scheduling a few hundred procs was harder than dealing with the threads; just speculation on my part... Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? I also couldn't find good docs explaining LT on BSD very well. I was worried that each thread (in reality, a process) in the LT model had it's own memory footprint. No, the memory is almost all shared, so memory overhead isn't an issue. Of course I knew about LT from reading your site... I'll also reiterate a few datapoints about mysql for any latecomers: -Most queries are simple selects to grab user info (check password, check homedir). Using the query cache at all? -The few updates or inserts are for a relay table for smtp use; it simply tracks where each pop/imap user connects from and smtp can refer to that table to see if someone should be able to relay mail. -There is also an insert/update on a last auth from... table. The hardware is a dual Athlon MP-1600 smp box with 1GB of RAM. Queries/second is about 15 tops, and there is a hard limit on the mail side; the box will only accept a finite amount of inbound/outbound smtp connections. Also, out of curiousity, the db servers that you've mentioned
Range of timestamp(14)
I can not seem to select a range of timestamp(14) data, something like Select * from foo where added is in the range of 01/01/2004 to 01/25/2004 -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connect to other database format
Hi, I'm completely new to MySQL and some of its concepts, therefore, these questions may be very basic. To connect to another database format I need the ODBC driver for that other format - right? I've built a repair job entry database on my Palm with HanDBase 3.0. I also got the HanDBase ODBC Driver v3.0. I tried to connect my desktop database program Paradox to it but there are some issues which seem to be unsolvable. I tried to connect to the .pdb (HanDBase) tables with Excel - that worked. Presumably, this indicates that the HanDBase ODBC driver is doing its job. What I would like to do is to copy the .pdb tables into MySQL tables and then work with these MySQL tables on the desktop. I searched hi and lo but all the connection related info seems to be about other database programs connecting to MySQL but I could not find any info about how MySQL connects to other database formats. Thanks for any help with this. Elmar -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, 6 Jul 2004, Jeremy Zawodny wrote: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90 Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( You read it right. Lots. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) Interesting. I'm not really more than about 70% sure of what a context switch is, my best read of it is that it's bad when those numbers go up because the scheduler is inefficiently juggling process around in the run queue... Yeah, you're not doing much I/O at all. Hmm. Yep, hmmm indeed. :) Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? I've worked with some much larger qmail installs, and the brick wall we hit in scaling it up is very similar; the box just seems to drown in syscalls. I think this is a feature of qmail; even if you're not very familiar with it, the basic gist is that a message goes from process to process rather than having a monolithic process like sendmail. At some point, I'm thinking this just doesn't scale well (we had trouble doing more than 2000 or so concurrent remote deliveries on a dual xeon box). No, the memory is almost all shared, so memory overhead isn't an issue. Excellent, that's very good to know. -Most queries are simple selects to grab user info (check password, check homedir). Using the query cache at all? Not sure... I'm using the values for caches and whatnot from the my-large.cnf in the distribution. Also, out of curiousity, the db servers that you've mentioned Yahoo is running are all likely dedicated mysql boxes, right?No dual-purpose stuff, correct? That's accurate for the majority of servers, yes. But not because apache and MySQL don't co-habitate well. It's because the raito of apache machines to mysql machines needed is rarely 1:1. Yeah, I was just hoping to find someone with a similar setup to see how their box is behaving. You'd think, yeah. I don't know squat about qmail, having moved from Sendmail to Exim a few years back. Maybe it really hammers systems? Apparently. I've started playing with Postfix a bit more and I find it to be much nicer than qmail. But for the foreseeable future I'm stuck with qmail. If I feel real brave I'll raise the syscall issue on the qmail list. Thanks again, Charles Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range of timestamp(14)
Try BETWEEN I hope this helps. Pat... Patrick Sherrill CocoNet Corporation SW Florida's 1st ISP 825 SE 47th Terrace Cape Coral, FL 33904 - Original Message - From: Scott Haneda [EMAIL PROTECTED] To: MySql [EMAIL PROTECTED] Sent: Tuesday, July 06, 2004 7:43 PM Subject: Range of timestamp(14) I can not seem to select a range of timestamp(14) data, something like Select * from foo where added is in the range of 01/01/2004 to 01/25/2004 -- - Scott HanedaTel: 415.898.2602 http://www.newgeo.com Fax: 313.557.5052 [EMAIL PROTECTED]Novato, CA U.S.A. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slow response time
On Tue, Jul 06, 2004 at 08:05:43PM -0400, Charles Sprickman wrote: On Tue, 6 Jul 2004, Jeremy Zawodny wrote: procs memory pagedisks faults cpu r b w avmfre flt re pi po fr sr da0 md0 in sy cs us sy 0 13 0 782108 61388 748 0 0 0 863 0 13 0 399 3756 276 23 95 2 13 0 788184 59172 2350 0 0 0 1394 0 73 0 424 7918 1142 29 90 Wow. If I've reassembled your vmstat output correctly, you're burning A LOT of system time. :-( You read it right. Lots. Well, I've seen machines witth cs numbers at lest 20 times that high and they were still getting some work done. (It was part of a MyQSL benchmark I ran, in fact.) Interesting. I'm not really more than about 70% sure of what a context switch is, my best read of it is that it's bad when those numbers go up because the scheduler is inefficiently juggling process around in the run queue... A context switch is anytime the CPU switches processes or goes from user space back to kernel space. So a lot of syscalls would certainly do this. Yeah, you're not doing much I/O at all. Hmm. Yep, hmmm indeed. :) Well, they're really apples and oranges. But I think you problem is *not* MySQL. It sounds as though you still have trouble with LinuxThreads, so I'd look at qmail. I'd try tracing (via truss) some of qmail's procs to see what they heck they're doing. Maybe they're needlessly making A LOT of syscalls? I've worked with some much larger qmail installs, and the brick wall we hit in scaling it up is very similar; the box just seems to drown in syscalls. I think this is a feature of qmail; even if you're not very familiar with it, the basic gist is that a message goes from process to process rather than having a monolithic process like sendmail. At some point, I'm thinking this just doesn't scale well (we had trouble doing more than 2000 or so concurrent remote deliveries on a dual xeon box). Yikes. And to think that I always stayed away for philosophical reasons alone. :-) No, the memory is almost all shared, so memory overhead isn't an issue. Excellent, that's very good to know. -Most queries are simple selects to grab user info (check password, check homedir). Using the query cache at all? Not sure... I'm using the values for caches and whatnot from the my-large.cnf in the distribution. The my-large.cnf I'm looking at has a 16M query cache, but doesn't explicitly turn it on. See what show variables like 'query_ca% says: mysql show variables like 'query_ca%'; +---+--+ | Variable_name | Value| +---+--+ | query_cache_limit | 1048576 | | query_cache_size | 33554432 | | query_cache_type | DEMAND | +---+--+ 3 rows in set (0.01 sec) You see demand there because we set query_cache_type = 2. But if you had = 1 you should see either ON or ENABLED, I don't remember which. If not, it's probably OFF or DISABLED. Also, out of curiousity, the db servers that you've mentioned Yahoo is running are all likely dedicated mysql boxes, right?No dual-purpose stuff, correct? That's accurate for the majority of servers, yes. But not because apache and MySQL don't co-habitate well. It's because the raito of apache machines to mysql machines needed is rarely 1:1. Yeah, I was just hoping to find someone with a similar setup to see how their box is behaving. Well, we've run MySQL on the same box as various things (Bugzilla, RT, etc) and never had problems like that. From what you've described about qmail, I can understand why. You'd think, yeah. I don't know squat about qmail, having moved from Sendmail to Exim a few years back. Maybe it really hammers systems? Apparently. I've started playing with Postfix a bit more and I find it to be much nicer than qmail. But for the foreseeable future I'm stuck with qmail. If I feel real brave I'll raise the syscall issue on the qmail list. Good luck with that. :-) Jeremy -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Range of timestamp(14)
At 16:43 -0700 7/6/04, Scott Haneda wrote: I can not seem to select a range of timestamp(14) data, something like Select * from foo where added is in the range of 01/01/2004 to 01/25/2004 Well, one problem might be that 01/01/2004 and 01/25/2004 are not dates... You should specify your values in year-month-day order. http://dev.mysql.com/doc/mysql/en/Date_and_time_types.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ER Diagrams with mysql
Hi, Have you tried DBDesigner4? Take a look, it's an OpenSource. http://www.fabforce.net/dbdesigner4/ Andre On Tue, 6 Jul 2004, Daniel Kasak wrote: Leandro Melo wrote: Hi, i created a database with around 20 tables. However i created the tables by script writting all create table statements, create constraints, etc... I need now a Entity Relationship diagram so i have a visual information about my database. I tried MySQL Comand Center but i doens't have such tool. Does any one know either a tool i can throw a database creation script and it gives me the relationship diagram or a mysql tool that can use my installed database and generate the relationship diagrams??? Thanks, Data Architect, by 'The Kompany' does ER diagrams. http://www.thekompany.com/products/dataarchitect/ It has a 'reverse engineer' feature that lets you suck a schema from a DB server. It also outputs a schema in SQL scripts. I'm not sure if you can feed it SQL scripts, but it seems like a logical function for it to have. I haven't used it for quite some time... It's not free. I don't know of any free ER tools. Or at least none I've found and investigated were good enough to remember. -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reset lost password on Server Logistics Mac OS X install
I'm attempting to follow instructions at the MySQL AB site: http://dev.mysql.com/doc/mysql/en/Resetting_permissions.html ...in order to reset a lost root password. I am using the Complete MySQL package from Server Logistics (www.serverlogistics.com) for Mac OS X Panther. I've read the installation manual from Server Logistics; I've read the instructions at MySQL AB; but each comes up with mysql: command not found, mysqld_safe: command not found, etc. Where can I find these tools in a default install of the Server Logistics Complete MySQL? Has anyone using the same configuration and OS done a successful reset of a root password and is willing to share their steps? Thanks, Eve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how to write query to return records has the closest date to end of month
Hi Lam,
Try and see whether following SQL work for you:
create temporary table tmp as
select product_id, min(abs(to_days('2004-6-30') - to_days(snapshot_date))
+ if(snapshot_date '2004-6-30',0,0.1)) as date_diff
from product_tbl group by product_id;
select product_tbl.*
from product_tbl, tmp
where product_tbl.project_id = tmp.project_id and
abs(to_days('2004-6-30') - to_days(snapshot_date)) + if(snapshot_date
'2004-6-30',0,0.1) = tmp.date_diff;
I have tested the about SQL on 4.0.20 and it is okay. If you are using
Version 4.1, you may try to use subquery.
Regards,
Michael
---
KMB E-mail Disclaimer
This e-mail may contain confidential, proprietary or legally privileged
information and is intended for the attention and use of the
addressee(s) only. If you are not the intended recipient of this
message, you must not copy, use or disclose any part of its
contents. Please notify the sender immediately and delete this
message from your system.
The KMB Group and each of its affiliates and the sender of this
message shall not be responsible or liable for any errors or omissions
in the contents of this message as secure or error free e-mail
transmission cannot be guaranteed. Information sent via e-mail
could arrive late or contain viruses or be intercepted, corrupted,
lost, destroyed, or incomplete. Unless otherwise stated, any
information given in this message is indicative only and is subject to
our formal written confirmation.
Re: Easy normalization how-to?
I was hoping someone could improve on my suggestion. But, not seeing any further replies, I guess my solution must be optimal (troll, troll :) --John On Saturday 03 July 2004 03:34 am, John Hicks wrote: On Friday 02 July 2004 11:40 pm, Your Name wrote: ... I now realize the importance of normalizing my data, ... what [is] the easy way... of getting it done now that I already have a database set up. Suppose I have a table books, consisting of id, title, and publisher; the publisher is redundant. I'd like to create a publisher table consisting of id and publisher, and then I want my original books table to use its publisher column to hold the value of publisher.id instead of the publisher's name. Here's a 3-a.m.-I-really-shouldn't-be-doing-this-now stab at it: Extract the publisher names from books into a new publisher table: create table publisher select distinct publisher from books; Add a primary key to the new publisher table: alter table publisher add column id int primary key auto_increment; Use a join to create a new table with both publisher and publisher id: create table newBooks select books.*, publisher.id as publisherId from books, publisher where books.publisher = publisher.publisher; Drop the redundant column publisher: alter newBooks drop column publisher; rename table books to oldBooks; rename table newBooks to books; There's bound to be better ways to do it. I'll check back tomorrow to see what I can learn from others' suggestions. Regards, John On Friday 02 July 2004 11:40 pm, Your Name wrote: I'm learning more about SQL all the time--I'm new to things now but I'm trying to read. I now realize the importance of normalizing my data, but what I can't figure out is what the easy way is of getting it done now that I already have a database set up. Suppose I have a table books, consisting of id, title, and publisher; the publisher is redundant. I'd like to create a publisher table consisting of id and publisher, and then I want my original books table to use its publisher column to hold the value of publisher.id instead of the publisher's name. Is there a way I can do this with an existing table full of data, other than going through it line-by-line with a scripting language? Thanks! Jen -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
select and where clause - help
I am trying to get a select statement with WHERE to work and I can't seem to do it. Below find some output. I try a select w/WHERE by book but it doesn't work. Originally I tried in PHP and didn't work, so now I just want the MYSQL to work first, I could be overlooking something simple since I'm new to MYSQL. Thanks, Lee G. mysql show columns from balloon.balloon_txt; +---+-+--+-+-+---+ | Field | Type| Null | Key | Default | Extra | +---+-+--+-+-+---+ | access_no | varchar(20) | | PRI | | | | recs_txt | text| YES | | NULL| | +---+-+--+-+-+---+ 2 rows in set (0.00 sec) mysql SELECT * FROM balloon_txt limit 0,1; +--+-- -- -- + | access_no| recs_txt | +--+-- -- -- + | BT-1034.02 | Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longe r duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL | +--+-- -- --
Increasing number of sockets
I am getting an error on my radius server asking me to increase the number of sockets on mysql. I'm looking the man pages for safe_mysqld and mysql but I don't see anything that offers to provision this. How does increase the number of sockets on the mysql? thanks in advance -- robert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: DBF to MySQL
For some reason, the imported information showed up as garbled nonsense. The file I was importing was an .xls file. Do you know if there is another TERMINATED BY I should be using? If not, I wonder how I can find out? The other question I have is: do I have to create a table within the MySQL database with exactly the right number of columns ahead of time for the import to work? - this is what I did. If so, is there a way to import info from a .dbf or .xls file without knowing the structure of the table ahead of time? Thanks, John on 7/6/04 3:59 PM, Chinchilla Zúñiga, Guillermo at [EMAIL PROTECTED] wrote: Try, for example: LOAD DATA INFILE 'myfile.cdf' INTO TABLE mytable FIELDS TERMINATED BY ',' ENCLOSED BY '' LINES TERMINATED BY '\n'; -Mensaje original- De: John Mistler [mailto:[EMAIL PROTECTED] Enviado el: Martes, 06 de Julio de 2004 04:51 p.m. Para: [EMAIL PROTECTED] Asunto: DBF to MySQL I am wanting to parse the info in a .dbf file (or .xls file for that matter) and place it in a table in a MySQL database. Is this something that I can do with the server side MySQL application, or do I need to figure out a way to do it on the client side? Any description of the method would be very welcome! For what it is worth, I am a Mac OSX.3 user. Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Increasing number of sockets
At 22:09 -0500 7/6/04, Robert Canary wrote: I am getting an error on my radius server asking me to increase the number of sockets on mysql. What kind of socket? TCP/IP socket? Unix domain socket file? Either way, that sounds like an odd message. The server listens to a single TCP/IP port and a single Unix socket file. There's no need for more. I'm looking the man pages for safe_mysqld and mysql but I don't see anything that offers to provision this. How does increase the number of sockets on the mysql? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select and where clause - help
On Tue, 06 Jul 2004 23:24:13 -0400, [EMAIL PROTECTED] said: from your output it's not really possible to tell, but i suspect that there are spaces following (and/or possibly in front of) the BT-1034.02 value, which would cause your select to fail to find anything. try: SELECT * FROM balloon_txt WHERE access_no like '%BT-1034.2%' if that works, remove the % at the front of the value. if that still works, check for tailing blanks. [if that doesn't still work, check for leading blanks.] Here's what works re. your suggestion- what do you think of this? I tried leading and trailing blanks but only the below is telling, of what I'm not sure...? If any % are removed it won't work. (?) mysql SELECT * FROM balloon_txt WHERE access_no like '%BT-1034.%2\r\n'; ++ -- -- -- ---+ | access_no | recs_txt | ++ -- -- -- ---+ | BT-1034.02 |Title_[ Balloon Capabilities and Futures] Author[ Thomas W. Kelly Resp_Org__[ Air Force Cambridge Research Labs. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 25 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 3-27.] Subj_Terms[ Balloon technology, tethered balloons, payload orientation, hot air balloons, balloon design, manned balloons, instrumentation] Content___[ The papers covered present load and altitude capabilities of many types of plastic balloons. Objectives of current research in balloon technology are described, including extension of present capabilities, increased reliability, and longe r duration.] ElecAccess[ http://library/Databases/Balloon/Data/BT1034.02.pdf Avail_From[ WFF/BTL | | BT-1034.12 |Title_[ High Altitude Survey Balloons] Author[ A.D. Struble Resp_Org__[ Sea-Space Systems, Inc. FundingOrg[ Date__[ Dec 1963 Report_No_[ Found in: AFCRL-TR-63-919, AFSG No. 154 Reposit_No[ Found in: AD-614 065 ContractNo[ Descript__[ Conference Paper, 13 p Notes_[ This symposium was held in Boston, MA on 25 to 27 September 1963. Twenty papers were presented, of which 16 were published. This paper is found on pp 169-181.] Subj_Terms[ Balloon design, balloon materials, polyethylene, balloon launches, flight tests] Content___[ A small balloon has been developed which can achieve altitudes up to 150,000 ft. A new balloon fabric - MERFAB - is used. Because of its very light weight and high strength, MERFAB allows the construction of balloons with extreme altitude
WinMySQLAdmin Invocation
Hi, I have installed MySQL 5.0.0-alpha. When I invoke WinMySQLAdmin, on Environment tab, I do not see any information for Server Info, Host Info, etc. I am also not able to create any new instance in MySQL control Center. I use Windows 2000 Server with SP4. What could be the reason? Can someone help? Thanks, Nawal Lodha.
