Re: InnoDB filesystem
On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. I believe most OSes support direct file access which either bypasses or minimizes cache effects, and InnoDB will enable it if possible. Solaris direct file I/O performance on UFS is within a couple percent of raw partitions, for example. This is all very well, but on some systems, surely the OS filesystem cache is a bonus, not a hindrance. Take for example a four-way X86 system with 16 GB RAM. I've seen people talk about such machines on this list. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
php mysql problem
I have redhat 9.0 and Server version: Apache/2.0.40. i have installed rpms php-4.2.2-17.2.i386.rpm php-mysql-4.2.2-17.2.i386.rpm After i create a database called mydb and serveral tables in mysql, I tried to run following testdb.php script -- html body ?php $db = mysql_connect(localhost, root,xx); mysql_select_db(mydb,$db); $result = mysql_query(SELECT * FROM employees,$db); printf(First Name: %sbr\n, mysql_result($result,0,first)); printf(Last Name: %sbr\n, mysql_result($result,0,last)); printf(Address: %sbr\n, mysql_result($result,0,address)); printf(Position: %sbr\n, mysql_result($result,0,position)); ? /body /html --- but i got error message with: http://coopunit.forestry.uga.edu:8080/testdb.php the error is: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/testdb.php on line 13 How can Fix this problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On 13 May 2004, at 4:02 pm, Jacob Friis Larsen wrote: I'd go with Reiser on SuSE. What about Reiser on Debian? It shouldn't matter too much. This functionality is in the kernel, so if the kernel version on SuSE and Debian is the same, the filesystem code will be the same, with the possible caveat that SuSE may have applied some other patches. The same isn't so true of Red Hat, who patch their kernels up to the eyeballs with whatever they feel like, until it bears scant resemblance to the version it actually says it is. Debian kernels are pretty much vanilla kernel.org kernels. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
Does the filesystem matter as much as disk throughput? I'd imagine that is where the bottleneck would be, at least as I've seen... Tim Cutts [EMAIL PROTECTED] 05/13/2004 11:13 AM To: Jacob Friis Larsen [EMAIL PROTECTED] cc: [EMAIL PROTECTED] Subject:Re: fastest filesystem for MySQL On 13 May 2004, at 4:02 pm, Jacob Friis Larsen wrote: I'd go with Reiser on SuSE. What about Reiser on Debian? It shouldn't matter too much. This functionality is in the kernel, so if the kernel version on SuSE and Debian is the same, the filesystem code will be the same, with the possible caveat that SuSE may have applied some other patches. The same isn't so true of Red Hat, who patch their kernels up to the eyeballs with whatever they feel like, until it bears scant resemblance to the version it actually says it is. Debian kernels are pretty much vanilla kernel.org kernels. Tim -- 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: php mysql problem
From: Jianping Zhu [mailto:[EMAIL PROTECTED] but i got error message with: http://coopunit.forestry.uga.edu:8080/testdb.php the error is: Fatal error: Call to undefined function: mysql_connect() in /var/www/html/testdb.php on line 13 How can Fix this problem? Thanks Sounds as though PHP was installed without the MySQL library. I haven't done it myself in a while, but I believe it requires the --with-mysql flag. HTH -- Mike Johnson Web Developer Smarter Living, Inc. phone (617) 886-5539 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ORDER BY Question
Hi Dirk, from the excellent on-line manual: http://dev.mysql.com/doc/mysql/en/SELECT.html Columns selected for output can be referred to in ORDER BY and GROUP BY clauses using column names, column aliases, or column positions. Column positions are integers and begin with 1: mysql SELECT college, region, seed FROM tournament - ORDER BY region, seed; mysql SELECT college, region AS r, seed AS s FROM tournament - ORDER BY r, s; mysql SELECT college, region, seed FROM tournament - ORDER BY 2, 3; /Johan Dirk Bremer (NISC) wrote: (The count(*) expression is the second column of the result set so you replace it with a 2). This saves you from having to use an 'As' expression for 'count(*)' although it makes the query less clear too. (It won't be apparent to some people what the effect of the '2' in the 'order by' is.) Rhino Thanks for all of the suggestions, the 'order by count' worked like a charm. Concerning Rhino's suggestion quoted above, it this method of using numbers to represent the columns documented anywhere? -- Johan Höök, Pythagoras Engineering Group - MailTo:[EMAIL PROTECTED] - http://www.pythagoras.se Pythagoras AB, Stormbyv. 2-4, SE-163 55 SPÅNGA, Sweden Phone: +46 8 760 00 10 Fax: +46 8 761 22 77 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: GUID storage
Hi! On May 12, Larry Lowry wrote: A uniqueidentifier in MS SQL is basically a guid. I am generating them via System.Guid.NewGuid().ToString(N) in the Dot Net framework which now returns me a string of 32 characters (hex). Internally I understand it is a 128-bit integer. As an option I could store that in MySql. What data type would that be? How would one convert to binary char(16)? In what documentation would I find this? I do not know enough yet to write a UDF. In 4.1.2 you can use function UNHEX(): UNHEX(REPLACE(uuid, '-', '')) will convert uuid to 16-byte string There's no easy way to do a reverse conversion yet. HEX() will do, but you'll lose dashes. 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]
Re:Re: Problems compiling NDB-Cluster mysql-4.1.2
Hello,
thanks for your response.
The zlib-Library was already installed on the machine.
The command find / | grep zlib showed my the file zlib.h at
/usr/include/linux/zlib.h
I've update from zlib 1.1.4-105 to 1.1.4-232 and installed the
zlib-devel package too.
I've tried to compile with different flags (CXX=gcc and/or CC=gcc) and
configured with the flag: --include-dir=/usr/include/linux
Nothing worked, the error is still the same.
Steffen Moelter
-Ursprüngliche Nachricht-
Von: Anders Karlsson [mailto:[EMAIL PROTECTED]
Gesendet: Montag, 10. Mai 2004 11:57
An: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Betreff: Re: Problems compiling NDB-Cluster mysql-4.1.2
You have to have zlib installed. This seems to be a bug, which I have
just reported. The fix is
simple: install zlib.
You can check with a simple C-program like this:
#include zlib.h
#include stdio.h
int main(int argc, char *argv[])
{
printf(Hello, World\n);
return 0;
}
If this refuses to compile (missing includefile) you are
missing zlib includes at least. You might want to link with -lz to check
that the library is there too, or even call one one the functions in
zlib (compress
for example).
Install zlib and make sure this little program compiles, if it does, you
should be OK.
/Karlsson
[EMAIL PROTECTED] wrote:
Hello all,
i want to compile the development tree source from
bk://mysql.bkbits.net/mysql-4.1 on SuSE 8.2 Linux. The compiling of the
mysql-4.1 without the ndb-cluster works fine.
When set the configuration-option --with-ndbcluster, the compilation
fails with the error:
ha_ndbcluster.o(.text+0x3b5c): In function `packfrm(void const*,
unsigned, void const**, unsigned*)':
: undefined reference to `my_compress'
ha_ndbcluster.o(.text+0x3c34): In function `unpackfrm(void const**,
unsigned*, void const*)':
: undefined reference to `my_uncompress'
collect2: ld returned 1 exit status
make[4]: *** [mysqld] Error 1
make[4]: Leaving directory `/home/Steffen/mysql-4.1/sql'
I've tried to set the CXX and/or CC environment-variable to gcc, but
the
compilation fails with the same error.
Installed Software Versions:
Kernel: 2.4.22-33
gcc/cpp : 3.3-23
bison: 1.875
autoconf: 2.53
automake: 1.5
libtool: 1.5
Thanks
Steffen
--
__ ___ ___ __
/ |/ /_ __/ __/ __ \/ / Anders Karlsson ([EMAIL PROTECTED])
/ /|_/ / // /\ \/ /_/ / /__ MySQL AB, Sales Engineer
/_/ /_/\_, /___/\___\_\___/ Stockholm
___/ www.mysql.com Cellphone: +46 708 608121
--
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: unexpected create table as lock issue
Hi! - Original Message - From: Sasha Pachev [EMAIL PROTECTED] To: [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; [EMAIL PROTECTED] Sent: Thursday, May 13, 2004 3:01 AM Subject: Re: unexpected create table as lock issue [EMAIL PROTECTED] wrote: I have a table called web_master which has as a primary key column called web_id. When I try to create another table as a select, I get the following error message [note, that group_id is indexed]: mysql create table sort_test as select * from web_master where group_id = 69; ERROR 1205: Lock wait timeout exceeded; Try restarting transaction There aren't any other transactions running in the database, so I do not understand why there is any lock wait.. Very possibly a bug. INNODB deadlocks itself during the operation, then detects the deadlock and aborts the transaction. If this is indeed a bug, I am sure Heikki would like to fix it ASAP. please run SHOW INNODB STATUS to determine what lock it is waiting for and if there really are no other active transactions. Maybe you have forgotten a transaction open? -- Sasha Pachev Create online surveys at http://www.surveyz.com/ Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnodB Hot Backup Questions
David, - Original Message - From: David Griffiths [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Thursday, May 13, 2004 7:50 AM Subject: InnodB Hot Backup Questions I'm hoping someone on the list has some experience with the tool (specifically, restoring a backup), as I'm stumped. First, --apply-log seems to only work on the host that ibbackup is lode-locked to. This doesn't make sense. Running the tool dumpes out some text, and part of that text is, (--restore works in any computer regardless of the hostname) The manual says, Therefore, the option --apply-log was added as a synonym for --restore in the version 1.40 of ibbackup, and the option name --restore will become deprecated. So restore == apply-log, but one works on any computer, and the other only works on the computer that it's node locked to. --apply-log works also in any computer regardless of the hostname or the license expiration date. Am I missing something obvious? And what happens after version 1.4? No one is able to apply the binary log file if they need to test the backup on a different server? Second, the manual has a bunch of inconsistencies w/regards to the my.cnf file you are supposed to use to apply the log. Section 3 says, We run ibbackup to roll forward the data files so that they correspond to the same log sequence number: $ ibbackup --apply-log /home/pekka/.backup-my.cnf Note the usage of the backup-my.cnf file; this is the file that ibbackup uses to figure out how to actually perform the backup, as opposed to the my.cnf file that is used to actually configure the MySQL server. but Section 5.3 says, innobackup --apply-log /home/pekka/.my.cnf /home/pekka/script/backups/2004-02-03_13-27-09 Notice that the my.cnf file appears to be the normal config file for mysqld (farther down the page, you find out that the file is infact a copy of the my.cnf file). There is the backup-my.cnf file in the directory with the data files, and it's not used. The above line uses the PERL interface, and thus the ibbackup-innobackup. Section 5.3 is about a the innobackup Perl script. It has different usage than the ibbackup binary. Which .cnf file is supposed to be used? The Server config file (my.cnf) or In fact, the my.cnf file parameter in the innobackup Perl script is not used at all! I have asked Pekka to remove the extra parameter, because it confuses users. the ibbackup config file (you can name it whatever you want)? Note that both seem to do exactly the same thing; I guess the issue here is more clarity than anything else. Finally, When I run --restore (apply-log doesn't work, remember?) I get, 040512 21:38:12 ibbackup: ibbackup_logfile's creation parameters: ibbackup: start lsn 5 1804179456, end lsn 5 1804179476, ibbackup: start checkpoint 5 1804179476 ibbackup: Error: backed up log file segment has a wrong magic n:o 542632761. ibbackup: Run 'ibbackup --help' for help. #define BACK_UP_LOG_END_MAGIC_N 98435789 /* The log end is new format if we store info of possible --include regexp option when the backup was taken */ #define BACK_UP_LOG_END_NEW_FORMAT_MAGIC_N 542632761 Looks like you have taken the backup with ibbackup-2.0, but are trying to run --apply-log with ibbackup-1.40. You should use the same or later ibbackup version to run --apply-log. Maybe you have forgotten to replace your old ibbackup-1.40 binary with the new ibbackup-2.0 binary, and the innobackup script is using the old binary? There is no troubleshooting section in the manual to explain the error, and ibbackup --help - it's focus is on the backup, not the restore. Googling on the message returns no pages. Unfort, support for the tool costs much more than the tool itself, and is basically 10 emails a year. Support costs 590 euros per year, the tool 390 euros per year. Can anyone provide some insight? The whole apply-log thing is confusing - should it be done immediately after a backup? That is safer. It can reveal corruption. Or is it only done if you need to use the backup? --apply-log is needed to use the backup. But I recommend doing it immediately after you have taken the backup. In short, the logic is this: 1. Take a backup. 2. Run --apply-log to make the backup a 'consistent snapshot'. 3. Use the backup. Thx, David Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb - next key locking
Hi! - Original Message - From: mayuran [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, May 12, 2004 7:56 PM Subject: Innodb - next key locking I have a perl script which fork()'s many children and each child is updating a table, and each child is inserting/updating DIFFERENT rows - I split up the work so that no two children try to update the same row so that no child has to wait for any if you use a UNIQUE index to access the rows, and the searched row ALWAYS EXISTS for each query, then no gaps are locked in recent InnoDB versions. No lock waits should happen. Can you make a simple test case that exhibits the problem? locks to be released. The problem is, I am getting lock wait timeout's still, its not consistant - sometimes it happens sometimes it does not. It might be due to next key locking. Anyhow, has anyone tried to do something similar before ? How can I have all the children update/insert without running into lock problems. I am using the latest production version of MySQL and all the tables are InnoDB. Any input is welcome. thank you. Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign Key Renaming Problem
Deepak, I tested this, and MySQL refused to rename the column in either the parent table or the child table. I do not understand how you have been able to rename the column x1 to to y1 in the parent table. Can you make a simple, repeatable test case where the renaming in the parent table succeeds? Best regards, Heikki Tuuri Innobase Oy Foreign keys, transactions, and row level locking for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php Order MySQL technical support from https://order.mysql.com/ - Original Message - From: Victoria Reznichenko [EMAIL PROTECTED] Newsgroups: mailing.database.myodbc Sent: Wednesday, May 12, 2004 4:30 PM Subject: Re: Foreign Key Renaming Problem Deepak Vishwanathan [EMAIL PROTECTED] wrote: I have a table named t1 with a column x1 which is the primary key for that table. There are some other tables that reference this table t1 on x1 with foreign key constraints. I wanted to rename the column x1 for some reason, so, I went ahead and renamed the column x1 to y1. I was able to change it successfully. Then, I wanted to rename the foreign key's column name from x1 to y1 in all referencing tables and I got the error ERROR 1025: Error on rename of './Test/t1' to './Test/#sql2-3c90-4bd' (errno: 121). So, then, I tried dropping the foreign key constraints on the referencing tables with alter table command with the foreign key value that I got from show create table command for those referencing tables...But, the system won't allow me to do that. I understand this problem is because of the foreign key constraintsDoes that mean, I should have dropped the foreign key constraint in referencing tables before I renamed the column x1 to y1 in the base table t1??? Yes, you should drop foreign key constraint first. -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.net http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Victoria Reznichenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.net ___/ www.mysql.com -- 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: innodb log
Hi!
- Original Message -
From: mayuran [EMAIL PROTECTED]
Newsgroups: mailing.database.myodbc
Sent: Monday, May 10, 2004 11:11 PM
Subject: innodb log
When I do a SHOW INNODB STATUS i see a query which is
waiting for a lock to be released, but innodb status
doesnt show the whole query, the end of it got truncated.
it looks something like:
INSERT INTO test(col1, .., col10) VALUES ('9',
and just stops.
is it possible to see the entire query, im having some
lock wait timeout issues and it would help alot if I can
see the entire query.
Marko has fixed this to the upcoming 4.0.19.
thanks
Best regards,
Heikki Tuuri
Innobase Oy
Foreign keys, transactions, and row level locking for MySQL
InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM
tables
http://www.innodb.com/order.php
Order MySQL technical support from https://order.mysql.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
mysqld is not deamoning and hangs console under Linux
Hi! I haven't installed MySQL to LInux for ages (mostly I use FreeBSD) and now I get the problem I remember of in past but I can't recall its cure. :0) When I start mysqld it works fine but doesn't leave console and block it. As far as I put mysqld launch into /etc/rc.d/rc.local script I get the whole system but as the main and single console is blocked cannot login to machine - only by remote. That's a problem. Linux RedHat 9.0 with standard kernel 2.4.18 Single Processor with Hyperthreading - hence using smp-kernel. P4-3200 2048 MB RAM Alex -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Max
Thank you for correction. You are absolutly right!
Best regards,
Mikhail.
- Original Message -
From: Brian Mansell [EMAIL PROTECTED]
To: Mikhail Entaltsev [EMAIL PROTECTED]
Cc: A Z [EMAIL PROTECTED]; [EMAIL PROTECTED]
Sent: Wednesday, May 12, 2004 10:11 AM
Subject: Re: Max
Just a minor correction...
select YourField from YourTable order by YourField DESC limit 1;
('DESC' in order to return the greatest value first)
On Tue, 11 May 2004 17:28:05 +0200, Mikhail Entaltsev
[EMAIL PROTECTED] wrote:
Hi,
select YourField from YourTable order by YourField limit 1;
Best regards,
Mikhail.
- Original Message -
From: A Z [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Tuesday, May 11, 2004 3:01 PM
Subject: Max
Hi,
A field of type VarChar() with following syntax:
ABA1.
How to get the Max value. Bearing in mind that value
ABA10 comes before ABA2.
regards
Yahoo! Messenger - Communicate instantly...Ping
your friends today! Download Messenger Now
http://uk.messenger.yahoo.com/download/index.html
--
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]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
The InnoDB storage engine can use raw disks without a filesystem. Would that be the fastest possible setup? Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On Thu, May 13, 2004 at 10:21:15AM +0200, JFL wrote: The InnoDB storage engine can use raw disks without a filesystem. Would that be the fastest possible setup? Probably, yes. 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: Restore 20 gigabytes of binary logfiles
I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. Would that be faster than having converted the binary logs to sql commands in one file and then use that? Thanks for your help, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySQL documentation
Hi, I have found that messages a.. Non-standard behavior of UNION statements has changed to the standard ones. So far, a table name in the ORDER BY clause was tolerated. From now on a proper error message is issued (Bug #3064). a.. Added max_insert_delayed_threads system variable as a synonym for max_delayed_threads. a.. Added query_cache_wlock_invalidate system variable. It allow emulation of MyISAM table write-locking behavior, even for queries in the query cache. (Bug #2693) are doubled in the documentation (http://dev.mysql.com/doc/mysql/en/News-4.0.19.html) Best regards, Mikhail. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:28:26AM +0200, JFL wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? I am considering this: mysqlbinlog --database=mydb logfiles | mysql -f There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. Would that be faster than having converted the binary logs to sql commands in one file and then use that? It would be, yes. It's hard to say how much faster though. -- 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]
InnoDB filesystem
I've been told that InnoDB on a raw partition is the fastest setup. To setup my system for this, could I create a partition called /innodb and adjust the my.cnf like this? innodb_data_home_dir = /innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ I suppose that innodb_log_group_home_dir and innodb_log_arch_dir can not be on a raw disk? Any other tips, pros and cons? Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On Thu, May 13, 2004 at 11:00:17AM +0200, JFL wrote: I've been told that InnoDB on a raw partition is the fastest setup. Actually, you've been told that it's probably the fastest. To setup my system for this, could I create a partition called /innodb and adjust the my.cnf like this? innodb_data_home_dir = /innodb innodb_log_group_home_dir = /var/lib/mysql/ innodb_log_arch_dir = /var/lib/mysql/ Nope. I suppose that innodb_log_group_home_dir and innodb_log_arch_dir can not be on a raw disk? Check the InnoDB docs. They explain how to setup raw disk partitions. You'll be using device names, not mount points. Any other tips, pros and cons? Pros: performance and bypassing the filesystem cache. Cons: loss of transparency and limited backup options. 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: InnoDB filesystem
I've been told that InnoDB on a raw partition is the fastest setup. Actually, you've been told that it's probably the fastest. Correct. Sorry :) Check the InnoDB docs. They explain how to setup raw disk partitions. You'll be using device names, not mount points. Thanks. I forgot to check the manual. Any other tips, pros and cons? Pros: performance and bypassing the filesystem cache. Cons: loss of transparency and limited backup options. What do you mean by transparency? Thanks for your help, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Using a hardware load balancer in from of MySQL
Hello all, I would like to find out if anyone has implemented an architecture where a hardware load balancer is placed in front of some MySQL servers in a Multi-master replication scheme. I want to use the load balancer more for high availability, than for load balancing. All connections to the database server would go the mysql server 1. If server1 fails, the load balancer should send all connections to server 2, etc. Thanks for any insight on any implementation gotchas. Partha Dutta Bloomberg, L.P. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to dump data in db.table syntax?
Alle Wednesday 12 May 2004 21:51, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 14:12, hai scritto: Hi! On May 12, Nico Sabbi wrote: Alle Wednesday 12 May 2004 11:19, Sergei Golubchik ha scritto: Hi! sorry for replying to myself, but I verified that adding replicate-do-db=db to my.cnf doesn't work as I expected What do you mean - it doesn't work ? Or, rather, how do you expect it to work ? :) I expect the slave servers to execute insert statements related to the database db that they have in their binlog (that I can see), both when they are in the form insert into db.table values() and when they show as: use db; insert into table values() but this doesn't happen: the directive replicate-wild-do-table=db.% seems to control the behaviour of the slaves, so they only executes statements like insert into db.table values() This behavior breaks replication when I use mysqldump -h local -a -B db | mysql -h master Yes, but the second syntax (with use db) should replicate if you use replicate-do-db=db. I understood that you tried it, and it didn't work ? Exactly. In my.cnf I have both: replicate-do-db=db replicate-wild-do-table=db.% but only insert into db.table values() First - sorry for confusion, according to http://dev.mysql.com/doc/mysql/en/Replication_Options.html replicate-do-db is not expected to do anything if you have replicate-wild-do-table. So you are right - it does not work as you expected. But replicate-wild-do-table should work, no matter whether you use insert into db.table values() or use db; insert into table values() Could you provide a repeatable test case to show that replicate-wild-do-table does not work ? If yes - please submit it at http://bugs.mysql.com/ Regards, Sergei I can't reproduce it anymore, I must have made something wrong that messed up the replication sooner. Sorry and thanks for your help, Nico -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Connections repeatedly dropped
Dear MySQL experts! I'm at something of a loss here. I'm testing MySQL on a new hardware platform. Previously, we had it running on Tru64 Alpha boxes. We're now moving it onto Itanium2 boxes running Debian Linux. Each machine has 4 CPUs and 16 GB RAM. Kernel version is 2.6.5. We couldn't get the MySQL binary distribution to run at all; it dumped core immediately with SEGV. We compiled it ourselves using the Intel compiler, and got the same result. I then compiled it with gcc, and we have a binary that does at least run without crashing instantly, and appears to work correctly. The instance is replicated from one Itanium2 machine to a second identical machine. The clients to these databases are computational jobs running on a cluster of approximately 1000 X86 Linux boxes. The jobs query the database for the data on which they are to work, and upload results to it once they are finished. They also update a status table in the database as they work so that a master control script can periodically poll the database and resubmit jobs which fail and so on. This setup works fine with MySQL 4.0.18 running on AlphaServer ES45 machines. But on the Itanium2 Linux machines, the vast majority of clients are seeing aborted connections: ia64c show status like 'Aborted_%'; +--+---+ | Variable_name| Value | +--+---+ | Aborted_clients | 2177 | | Aborted_connects | 0 | +--+---+ 2 rows in set (0.00 sec) Looking at: http://dev.mysql.com/doc/mysql/en/Communication_errors.html for possible reasons, I see the usual suspects of timeout variables, but those are fine on this instance: ia64c show global variables like '%_timeout'; +--+-+ | Variable_name| Value | +--+-+ | connect_timeout | 5 | | delayed_insert_timeout | 300 | | innodb_lock_wait_timeout | 50 | | interactive_timeout | 2678200 | | net_read_timeout | 30 | | net_write_timeout| 60 | | slave_net_timeout| 3600| | wait_timeout | 2678200 | +--+-+ 8 rows in set (0.00 sec) These are the same settings we use on the Alphas, where they work fine. The other possibility is max_allowed_packet, but we've got that set quite large (certainly large enough for these queries): ia64c show global variables like '%_packet'; ++--+ | Variable_name | Value| ++--+ | max_allowed_packet | 16776192 | ++--+ 1 row in set (0.00 sec) So I don't think it's any of these settings. As to the Linux problems which are mentioned: 1) We don't think it's ethernet duplex - these are gigabit ethernet. 2) TCP/IP seems to be correctly configured in all other respects. 3) The switches are all fine, as far as we know The only base I can't cover is the statement: Some problem with the thread library that causes interrupts on reads. Are there known problems of this sort on certain Linux versions? Is there any code around to test whether this machine has this problem? Many thanks for any help you gurus can offer... Regards, Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Changing Table Row Order
David Blomstrom [EMAIL PROTECTED] wrote: I just switched my primary key from a numerical column to abbreviations, primarily because the latter are more distinctive and easier for me to work with. The flip side is that my row order has been turned upside down. I made my database in a spreadsheet, with North America (#1) at the top and Antarctica at the bottom, and that's how my tables display on my web pages. But switching to an alphabetical key puts Antarctica at the top and North America near the bottom. What's the best solution? Can I retain my alphabetical primary key but also retain a numerical index, used to maintain table order? Yes, you can have additional column for sort order. -- 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: Using a hardware load balancer in from of MySQL
On Thu, May 13, 2004 at 06:49:01AM -0400, PARTHA DUTTA, BLOOMBERG/ 499 PARK wrote: Hello all, I would like to find out if anyone has implemented an architecture where a hardware load balancer is placed in front of some MySQL servers in a Multi-master replication scheme. I want to use the load balancer more for high availability, than for load balancing. All connections to the database server would go the mysql server 1. If server1 fails, the load balancer should send all connections to server 2, etc. Thanks for any insight on any implementation gotchas. Have you considered the Linux High Availability project: http://linux-ha.org/ We use it for failover at work. It does a good job. Luis -- GnuPG Key fingerprint = 113F B290 C6D2 0251 4D84 A34A 6ADD 4937 E20A 525E -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
In the last episode (May 13), JFL said: I've been told that InnoDB on a raw partition is the fastest setup. Actually, you've been told that it's probably the fastest. Correct. Sorry :) Check the InnoDB docs. They explain how to setup raw disk partitions. You'll be using device names, not mount points. Thanks. I forgot to check the manual. Any other tips, pros and cons? Pros: performance and bypassing the filesystem cache. I believe most OSes support direct file access which either bypasses or minimizes cache effects, and InnoDB will enable it if possible. Solaris direct file I/O performance on UFS is within a couple percent of raw partitions, for example. Cons: loss of transparency and limited backup options. What do you mean by transparency? If you decide you need more space, you won't be able to simply extend the tablespace file; you'll need to either add another disk or resize your existing filesystems to free up space for another raw partition. And for backup, you'll either have to manually dd the data off the partition, or use mysqldump (or InnoDB hot backup) , since backup software will simply back up the device node without trying to read its contents. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Using a hardware load balancer in from of MySQL
Sounds like you might be interested in Emic Networks' Application Cluster 2.0 for MySQL. We've begun taking a look at it ourselves -- without arriving at any conclusions at this point. (However, it is not strictly a hardware solution.) General Info: http://www.emicnetworks.com/products/mysql.html Features: http://www.emicnetworks.com/products/mysql_features.html --Fail-over Clustering --Multi-Master Architecture Robert J Taylor [EMAIL PROTECTED] PARTHA DUTTA, BLOOMBERG/ 499 PARK wrote: Hello all, I would like to find out if anyone has implemented an architecture where a hardware load balancer is placed in front of some MySQL servers in a Multi-master replication scheme. I want to use the load balancer more for high availability, than for load balancing. All connections to the database server would go the mysql server 1. If server1 fails, the load balancer should send all connections to server 2, etc. Thanks for any insight on any implementation gotchas. Partha Dutta Bloomberg, L.P. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: replication of temporary tables
Tom Cunningham [EMAIL PROTECTED] wrote: In short: what is the standard MySQL handling of temporary tables, *especially* when you've got a slave filter on to only replicate certain tables? These options apply to the temporary tables too. My replication stopped over the weekend, because a query was executed which created temporary tables, then used that table to update one of the non-temporary tables which I was replicating. Do I need to add all possible temporary tables to my slave-side replication filter? (i.e., replicate-do-table=temp1, temp2, ...) (even when I did so, it didn't seem to work, but I'll have another go). For replicate-do-table option you should also specify database name. Look at replicate-wild-do-table option where you can use wildcard characters in the database and table names. For example: replicate-wild-do-table=db%.temp% -- 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]
InnoDB Questions
Need someone with some insight or experience with InnoDB (Heikki?? :-) 1) According to a book I'm reading (High Performance MySQL) InnoDB uses MVCC, effectively allowing readers to not block writers. In Oracle (with which I am more familiar) this is accomplished via rollback segments, and now, undo tablespaces. Occasionally, if the rollback data overwrites itself, you can get a Snapshot too old message. But you can control the size and number of the rollback segments to fit your system. So, my InnoDB questions are: a) Where does InnoDB store all of this information (such as the deleted rows)? In the InnoDB tablespace? b) How long is it stored? c) Can we (users) control any of this? 2) Another question I have is surrounding fragmentation. Is there a way I can monitor my InnoDB tables to see the level of fragmentation on the table and/or indexes? Thanks very much for any help that you can provide. BTW, though I'm not finished, I'm very much enjoying the book and would recommend it for someone like me who is new to MySQL but experienced in other RDBMS's. Lou
Re: fastest filesystem for MySQL
I'd go with Reiser on SuSE. What about Reiser on Debian? Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MyISAM vs InnoDB + Foreign Keys
I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Thanks. __ Do you Yahoo!? Yahoo! Movies - Buy advance tickets for 'Shrek 2' http://movies.yahoo.com/showtimes/movie?mid=1808405861 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sorting Varchar
OK, I'm sorry. The solution I gave doesn't work.
You need to have some sort of conditional in the sort expression so that
numbers are sorted numerically and other things are sorted alphanumerically.
I'm not aware of a test for numeric vaues in MySql, so you need to use some
trick to differentiate between the two.
This seems to work, provided there are no negative numbers and the text
things start with letters
ORDER BY IF (cost ':', LPAD(cost,10,'0'), CONCAT('1',cost))
In this,
cost ':' tests if the string starts with a digit (':' is the character
after '9')
LPAD(cost,10,'0') pads the integer on the left with zeros -- replace the
10
with a number at least one more than the maximum number of digits
CONCAT('1',cost) causes the text items to sort to the end (the numbers
now start with '0')
Pasha's solution is probably cleaner, but you have to change the
table definition. For Pasha's solution to work, you would need to
have the text_val column be null (or '') when the value is numeric.
Subject: Re: Sorting Varchar
From: [EMAIL PROTECTED]
Date: Wed, 12 May 2004 18:13:17 -0400
That didn't do it. I got 0,0,1050,1168,1195, 1975, 150,155,16500,170,178.
. . The non-numerics came out last (which I want). There are two zeros
but no negative numbers. Any alternatives?
Ken
**
On Wed, 12 May 2004 07:47:11 -0400, Bill Easton [EMAIL PROTECTED]
wrote:
You could also use order by cost+0,cost. This puts the non-numerics
first, then the numerics in numerical order. (You'd need to get fancier
if there are non-numerics starting with a digit or numerics = 0.)
From: Sasha Pachev [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
How do I set up a column (cost) that contains numbers and text so
that
the numbers will sort the numbers accurately? Using varchar results
in
a sort based on the first digit, so that I get e.g. 1, 10, 100, 3. .
.
when the command is order by cost. Almost all of the text is By
County.
Ken:
Consider having two columns - num_val, and text_val, and then order by
num_val,
text_val
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB + Foreign Keys
I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. The ability to join a bunch of tables in a query is different from foreign keys. A foreign key is a relationhip between two tables. It says that if you wish to put a value into a column with a foreign key, then that value has to exist in the other table. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. InnoDB is a storage engine, and so is MyISAM. When you create a table, you specify one of the types. When you add data and indexes to that table, the type of table determins the storage engine used. InnoDB has row level locking. This means that when you are updating a row, only that one row gets locked (which means that another connection to the database cannot modify that row). MyISAM locks the entire table. Only one connection/session at a time can update/insert/delete. InnoDB uses the concept of a tablespace; MyISAM doesn't. A tablespace is where you store your data, and is made up of datafiles. You don't know where your data is stored in those data files. When you create a table in MyISAM, it creates a file of the same name as your table. InnoDB has foreign keys. What you wrote above is correct - you're defining a relationship between tables that the database will enforce. To backup InnoDB, you either have to shut down the database, or buy a hot-backup tool ($500 US, 390 Euros, I think). There is lots more, but those are the basics. MyISAM is easier, InnoDB has more enterprise features. For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. Yes, the keys have to be an exact match. You could also allow a country to be in two continents with a one-to-many relationship. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. It's not that tough. ALTER TABLE your_table_here ADD CONSTRAINT some_foreign_key_name FOREIGN KEY (column from table) REFERENCES other_table (other column)l ALTER TABLE your_table_here DROP FOREIGN KEY some_foreign_key_name; Note that it depends on the version of MySQL you are using. Some versions of MySQL would not allow you to create foreign keys outside of CREATE-TABLE statements. Others wouldn't allow you to give your foreign key a name. Checkout http://www.innodb.com/ibman.php - it's the InnoDB manual. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Explained it above. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Yes, foreign keys would help catch bugs. If there is alot of updating/inserting/deleting, InnoDB can be very helpful as well. Davis -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM vs InnoDB + Foreign Keys
On Thu, 13 May 2004 10:34:37 -0700 (PDT) David Blomstrom [EMAIL PROTECTED] wrote: I thought that only InnoDB tables could be joined - and only if they had foreign keys. But it sounds like any kind of table can be joined, and it doesn't need a foreign key. Exactly, you can do a join with any two or more tables (even the same table joined to itself actually) and on any columns. It's just a matter of whether or not the join makes any sense, for example: SELECT * FROM t1, t2 WHERE t1.city = t2.golfer_id Doesn't make much sense to do this, but the SQL will parse and attempt to execute anyway. Can someone explain InnoDB, MyISAM and foreign keys in plain English? If I understand correctly, foreign keys simply help ensure integrity. To put it another way, they help weed out errors. InnoDB and MyISAM are table types, or table engines is now the preferred terminology. Different storage implementations basically. MyISAM is so much faster because it doesn't have to deal with the overhead of transactions and foreign keys. An important aspect of foreign keys is the referential action (ON DELETE CASCADE, ON UPDATE SET NULL, etc) which allow you to have the database take care of cascading actions when a parent row is deleted without having to worry about having an application programmer do it and make a coding error or forget to and leave orphaned rows. So if you have a student parent table and an enrollment child table, you can set it to delete rows in the enrollment table for student 123 if student 123 is deleted form the student table (no orphaned rows in enrollment when a student is deleted). You can also set up referential actions to prevent deleting rows from a parent if there are rows in a child table (ON DELETE RESTRICT), it all depends on your situation. Foreign Keys also requires that any rows inserted into the the child table MUST have a value that matches in the parent table. So for the student/enrollment table, if you attempt to insert a row into enrollment for studentId 342, the only way that query will work is if there is indeed a student with studentId 342 in the student table. This is the referential integrity part of Foreign Keys. Foreign keys provide a real link between tables to implement an actual relationship between two tables, or even a table to itself. (remember this is a relational database afterall). Without that foreign key, the relationship is only implied and might not be known to anyone unless there is an entity-relationship diagram for the project. This comes in handy when trying to reverse engineer a data model from an existing db application. Data models should always come first though, but we're only human! For example, when I import data, I often get errors - something about violations of foreign key restraints. When I examine my original tables, I often find discrepancies - like eu* rather than eu in a row for Turkey, which lies partly in Europe AND Asia. I've considered the possibility of creating foreign keys for quality control, then deleting them after I'm finished so I can tweak my table - like adding * to certain elements. However, it sounds like it's very difficult to delete foreign keys. I tried it in MySQL-Front or SQLyog and was presented with an 11-step process, or something like that. ALTER TABLE mytable DROP FOREIGN KEY fk_symbol; (SHOW CREATE TABLE will reveal the fk_symbol) But why even put the foreign key in there in the first place if you're just going to violate it later? If you do that, you should have a eu* in the parent table. My understanding is that MyISAM tables are faster than InnoDB tables, but the latter offer row locking, though I don't really understand what that is. Yes. In addition to foreign keys, InnoDB offers transaction support, which is absolutely critical when dealing with larger OLTP applications. Speed does suffer though because all this Foreign Key / Transaction stuff takes lots of overhead. Putting it all together, what combination would you recommend for my project - a series of tables with data on the world's nations, states, counties and natural regions? My biggest table at present (Nations) has about 250 rows and half a dozen columns, but I'll be creating perhaps a dozen accessory tables, with data on area, population, economics, government, etc. With rows in the hundreds, InnoDB vs. MyISAM speed should be negligable. Make sure you create Indexes on your foreign key columns and you should be ok. (Indexes will speed up your queries, foreign key columns are almost always the columns used in a join condition, hence the need for an index) I'm also planning a series of tables focusing on animals - common and scientific names, diet, habitat, etc. For both of these projects, I think foreign keys would be a good choice for quality control, which would, in turn, require the use of InnoDB tables. Am I right? Yes, once you understand how they
upgrading a server to latest MySQL
I have MySQL 3.23.41 running on an Apache server with Red hat. it's hosted at EV1 (formerly Rackshack) but they do not provide any support for the server. I'd like to upgrade to the latest MySQL. Does anyone know of a bulletproof, step-by-step, online guide to doing this? Perhaps a reasonable priced resource that will do it? Thank you -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Sorting Varchar
OK, I'm sorry. The solution I gave doesn't work.
You need to have some sort of conditional in the sort expression so
that
numbers are sorted numerically and other things are sorted
alphanumerically.
I'm not aware of a test for numeric vaues in MySql, so you need to use
some
trick to differentiate between the two.
This seems to work, provided there are no negative numbers and the
text
things start with letters
ORDER BY IF (cost ':', LPAD(cost,10,'0'), CONCAT('1',cost))
In this,
cost ':' tests if the string starts with a digit (':' is the
character
after '9')
LPAD(cost,10,'0') pads the integer on the left with zeros -- replace
the
10
with a number at least one more than the maximum number of digits
CONCAT('1',cost) causes the text items to sort to the end (the
numbers
now start with '0')
Pasha's solution is probably cleaner, but you have to change the
table definition. For Pasha's solution to work, you would need to
have the text_val column be null (or '') when the value is numeric.
You could try something like this alphanumeric sort:
order by
case
when substring(cost,1,1) between '0' and '9'
then ''
when substring(cost,2,1) between '0' and '9'
then left(cost,1)
when substring(cost,3,1) between '0' and '9'
then left(cost,2)
...
else 'Z'
end
, cast(
case
when substring(cost,1,1) between '0' and '9'
then substring(cost,1)
when substring(cost,2,1) between '0' and '9'
then concat('0',substring(cost,2))
when substring(cost,3,1) between '0' and '9'
then concat('00',substring(cost,3))
...
else 9
end
as integer)
-Erich-
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
On Thu, May 13, 2004 at 11:16:18AM -0400, Peter J Milanese wrote: Does the filesystem matter as much as disk throughput? I'd imagine that is where the bottleneck would be, at least as I've seen... Throughput or seek time? 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: Sorting Varchar
I've just tried the three column solution (designating each column as
null), but the empty cells produce a 0 just as the two cells with an
actual 0. How do I avoid that, preferably with a blank in that place?
*
On Thu, 13 May 2004 13:53:37 -0400, Bill Easton [EMAIL PROTECTED]
wrote:
OK, I'm sorry. The solution I gave doesn't work.
You need to have some sort of conditional in the sort expression so that
numbers are sorted numerically and other things are sorted
alphanumerically.
I'm not aware of a test for numeric vaues in MySql, so you need to use
some
trick to differentiate between the two.
This seems to work, provided there are no negative numbers and the text
things start with letters
ORDER BY IF (cost ':', LPAD(cost,10,'0'), CONCAT('1',cost))
In this,
cost ':' tests if the string starts with a digit (':' is the
character
after '9')
LPAD(cost,10,'0') pads the integer on the left with zeros -- replace
the
10
with a number at least one more than the maximum number of digits
CONCAT('1',cost) causes the text items to sort to the end (the numbers
now start with '0')
Pasha's solution is probably cleaner, but you have to change the
table definition. For Pasha's solution to work, you would need to
have the text_val column be null (or '') when the value is numeric.
Subject: Re: Sorting Varchar
From: [EMAIL PROTECTED]
Date: Wed, 12 May 2004 18:13:17 -0400
That didn't do it. I got 0,0,1050,1168,1195, 1975,
150,155,16500,170,178.
. . The non-numerics came out last (which I want). There are two zeros
but no negative numbers. Any alternatives?
Ken
**
On Wed, 12 May 2004 07:47:11 -0400, Bill Easton [EMAIL PROTECTED]
wrote:
You could also use order by cost+0,cost. This puts the non-numerics
first, then the numerics in numerical order. (You'd need to get
fancier
if there are non-numerics starting with a digit or numerics = 0.)
From: Sasha Pachev [EMAIL PROTECTED]
[EMAIL PROTECTED] wrote:
How do I set up a column (cost) that contains numbers and text so
that
the numbers will sort the numbers accurately? Using varchar
results
in
a sort based on the first digit, so that I get e.g. 1, 10, 100, 3.
.
.
when the command is order by cost. Almost all of the text is By
County.
Ken:
Consider having two columns - num_val, and text_val, and then order
by
num_val,
text_val
--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: fastest filesystem for MySQL
I would think that seek time may be interdependent on disk speed and Filesystem type... I can see why it would matter sort of... Jeremy Zawodny [EMAIL PROTECTED] 05/13/2004 02:45 PM Please respond to mysql To: Peter J Milanese [EMAIL PROTECTED] cc: Tim Cutts [EMAIL PROTECTED], Jacob Friis Larsen [EMAIL PROTECTED], [EMAIL PROTECTED] Subject:Re: fastest filesystem for MySQL On Thu, May 13, 2004 at 11:16:18AM -0400, Peter J Milanese wrote: Does the filesystem matter as much as disk throughput? I'd imagine that is where the bottleneck would be, at least as I've seen... Throughput or seek time? 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]
bug?
In the mysql documentation it stands: max_user_connections The maximum number of simultaneous connections allowed to any given MySQL account. A value of 0 means ``no limit.'' This variable was added in MySQL 3.23.34. In my configuration file I wrote: max_user_connections = 0 That did not work, mysql automatically changed the value to 1, and that created some trouble fore me. Why is that? Is this a bug or Is it something I did wrong? - anders
Query join issues
Hello, I am trying to figure out a problem I am having with a query involving multiple joins but am not having much luck. I currently have this query working: db1 LEFT JOIN db2 ON db1.files_groupId = db2.group_id LEFT JOIN db3 ON db1.files_userId = db3.user_id Basically the above is taking the group id and user id from db1 and getting their names from the appropriate db. What I would like to be able to do though is join db3 (user) on to db1 again but using a different ON value (a different user id in the same record). Is this possible? I have tried aliasing the db3: db1 LEFT JOIN db2 ON db1.files_groupId = db2.group_id LEFT JOIN db3 AS user1 ON db1.files_userId = user1.user_id LEFT JOIN db3 AS user2 ON db1.files_otherUserId = user2.user_id WHERE ... The above query does not cause a MySQL error, however there are no values passed for the requested labels of db3 e.g user2.user_name. I have tried printing out mysql_fetch_assoc($result) and from what I can see the user db doesn't get joined on again but wipes all results of the other user join. Thanks for any help offered. Will
Re: upgrading a server to latest MySQL
Piece of cake :) http://dev.mysql.com/doc/mysql/en/Upgrading-from-3.23.html I've recently upgraded our servers from 3.23.57 to 4.0.18. No problems at all. Luck! - Original Message - From: Joe Harkins [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 13, 2004 2:23 PM Subject: upgrading a server to latest MySQL I have MySQL 3.23.41 running on an Apache server with Red hat. it's hosted at EV1 (formerly Rackshack) but they do not provide any support for the server. I'd like to upgrade to the latest MySQL. Does anyone know of a bulletproof, step-by-step, online guide to doing this? Perhaps a reasonable priced resource that will do it? Thank you -- 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: Restore 20 gigabytes of binary logfiles
I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. How should relay-log.info look like? Like this: linuxweb1-bin.001 linuxweb1-bin.002 linuxweb1-bin.003 ... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Potential crashing bug in MySQL 4.0.19
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 Hi, Some of you may have noted that we have uploaded the MySQL 4.0.19 binaries to our mirrors, but have not sent out an announcement or updated the web pages yet. The MySQL 4.0.19 binaries were uploaded to the download mirrors on May, 10th. However, a potential crashing bug in MySQL 4.1 was found and confirmed to affect the 4.0.19 release as well. This happened just shortly before the release was publicly announced and published on the 4.0 download pages at http://dev.mysql.com/. See Bug #3596 for details (it was initially reported against MySQL-4.1, but was confirmed to exits 4.0.19 as well). A fix for this bug was pushed into the MySQL source tree quickly after it could be reproduced and will be included in the upcoming MySQL 4.0.20, to be released shortly. We recommend our users to stick to MySQL 4.0.18 for the time being, until MySQL 4.0.20 has been released (this specific bug was introduced after MySQL 4.0.18 was released, so older versions were not affected by it). We apologize for the inconvenience! Bye, LenZ - -- Lenz Grimmer [EMAIL PROTECTED] Senior Production Engineer MySQL GmbH, http://www.mysql.de/ Hamburg, Germany -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.3 (GNU/Linux) Comment: For info see http://quantumlab.net/pine_privacy_guard/ iD8DBQFAo+IqSVDhKrJykfIRAhIZAJ0cqsKxuxYvfwy63E8lOxS+2xYbZgCfaFQw BTnndPF57A9ZFROMSCCnKkM= =bszb -END PGP SIGNATURE- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: select Distinct question
Hello List, Please forgive this rather lengthy post. I thought I had something worked out to answer Rob's question but when I put it to the test I found what MAY be a bug in 4.1.1a-alpha-nt-log. Here is what I did. I created two tables, tablea and tableb. Here are their defs: mysql show create table tablea \G *** 1. row *** Table: tablea Create Table: CREATE TABLE `tablea` ( `IP` varchar(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) mysql show create table tableb \G *** 1. row *** Table: tableb Create Table: CREATE TABLE `tableb` ( `IP` varchar(16) default NULL ) ENGINE=MyISAM DEFAULT CHARSET=latin1 1 row in set (0.00 sec) I then populated them with sample data. I used odd IP addresses for tablea and even IP addresses for tableb except for .3 which exists in both. I created multiple rows for each IP to simulate a set of (extremely simplified) web server logs. Here are the table dumps: mysql select * from tablea; +-+ | IP | +-+ | 192.168.1.1 | | 192.168.1.1 | | 192.168.1.1 | | 192.168.1.3 | | 192.168.1.3 | | 192.168.1.3 | | 192.168.1.5 | | 192.168.1.5 | | 192.168.1.5 | | 192.168.1.3 | +-+ 10 rows in set (0.00 sec) mysql select * from tableb; +-+ | IP | +-+ | 192.168.1.2 | | 192.168.1.2 | | 192.168.1.2 | | 192.168.1.4 | | 192.168.1.4 | | 192.168.1.4 | | 192.168.1.6 | | 192.168.1.6 | | 192.168.1.6 | | 192.168.1.8 | | 192.168.1.8 | | 192.168.1.8 | | 192.168.1.3 | | 192.168.1.3 | +-+ 14 rows in set (0.00 sec) What I was attempting to do was to try to work around the lack of FULL OUTER JOIN capability in order to help Rob find out how many unique IP addresses exist between the two tables. I crafted the following statement: SELECT COUNT(DISTINCT IFNULL(a.ip, c.ip)) as IP_Count FROM tablea a LEFT JOIN tableb b ON b.ip = a.ip RIGHT JOIN tableb c ON c.ip = a.ip; The answer I got back was 3.. h. To try to see what was being counted distinctly I rewrote the query to read: SELECT a.ip as a, b.ip as b, c.ip as b FROM tablea a LEFT JOIN tableb b ON b.ip = a.ip RIGHT JOIN tableb c ON c.ip = a.ip; and got 148 rows. What had actually happened was the query engine created a cartesian product of the two tables. Here is a partial dump of the output: +-+-+-+ | a | b | c | +-+-+-+ | 192.168.1.1 | NULL| 192.168.1.2 | - clipped for space - | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.5 | NULL| 192.168.1.8 | | 192.168.1.3 | NULL| 192.168.1.8 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.1 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.5 | NULL| 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | | 192.168.1.3 | 192.168.1.3 | 192.168.1.3 | +-+-+-+ 148 rows in set (0.00 sec) All of the rows that were clipped were combinations of tablea and tableb values with the b column always coming back as null. Column a and column c were never NULL. Here is why I think it's a bug. If we break down the FROM clause we get two situations. I will refer to the tables by their aliases so that the physical names don't get confusing: FROM tablea A LEFT JOIN tableb B ON B.ip = A.ip and FROM tablea A RIGHT JOIN tableb C ON C.ip = B.ip; If A left joins to B first we get a set that consists of all of A's rows with some colmns of B data populated in where they match (8 rows of B data thanks to the duplicate matches on 192.168.1.3). If we then call that result set T and right join C to T on a.ip (of T) matching c.ip we will get all of C's rows with a few of the columns from T populated where they match. Shouldn't I have
Re: Restore 20 gigabytes of binary logfiles
On Thu, May 13, 2004 at 10:16:52PM +0200, Jacob Friis Larsen wrote: I need to restore 20 gigabytes of binary logfiles. What should I do in order to get the job done as quickly as possible? There is a faster way, but it is rather tricky. 4.0 slave can be tricked into thinking that those binary logs are in fact the relay logs that it gathered from the master. So we trick the server into thinking it is a slave of the server that produced them. You just need to pick a server-id that is not the same as the one that produced them. Then if you list the logs in in the relay log info file in the correct order, hand-craft relay-log.info file to point at the first one), then start the slave with skip-slave-start, and then just start the SQL thread manually (SLAVE START SQL_THREAD), it will process them until it is done. Periodically run SHOW SLAVE STATUS to see if it got to the end of the last log. How should relay-log.info look like? Like this: linuxweb1-bin.001 linuxweb1-bin.002 linuxweb1-bin.003 ... That looks right. 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]
Inserting in a auto-inremental column
Hi, Can I insert specifying the data into a auto-incremental column? Explain better: We have a database (MySQL-4.0.17) that I need to syncronize with a off-line software. If a client was inserted in the off-line software, I´ll need to import the client datas to the central database. The client ID is an auto-incremental column, but if I leave MySQL set the client ID, it´s supposed to be different from the off-line software. Thanks, Ronan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Help about a db
Hi all, I have the following data Users : user(varchar), entreprise_1(int), entreprise_2(int) Entreprise: idx(int, prim, auto_incr), code(varchar) some users have one entreprise, and some two. if I decide that 'no entreprise x' is coded with entreprise_x=0, i have obviously a problem with SELECT Users.* FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 WHERE entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx AND (Entreprise1.code='CODEX' OR Entreprise2.code='CODEX'); since SELECT Users.* FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 WHERE entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx returns 0 tuples when entreprise_2=0 In fact, i'm looking for the smartest way to code 'nothing'. For the moment, i've created the Enterprise 'dummy' (idx=0, code='VOID'), but i absolutely not satisfied with this solution. Any idea ? Thank you all by advance -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Inserting in a auto-inremental column
Ronan Lucio wrote: Hi, Can I insert specifying the data into a auto-incremental column? Explain better: We have a database (MySQL-4.0.17) that I need to syncronize with a off-line software. If a client was inserted in the off-line software, Ill need to import the client datas to the central database. The client ID is an auto-incremental column, but if I leave MySQL set the client ID, its supposed to be different from the off-line software. MySQL will let you specify a value for an auto_increment column, or update the value later. I'm not sure if I agree with this feature or not. It is handy in some cases, but those cases are where you are doing something that you shouldn't be doing. You will NOT be able to update the values if it conflicts with an existing auto_increment value. For this reason, you are better off letting MySQL choose the best value, and then using last_insert_id() ( see the docs ) to retreive the value you've been given. Then use this value to update all related tables ( including the offline database ). As a design rule, you shouldn't be letting the offline database dictate the auto_increment values in the main server - it should be the other way other. Either way, it's a little dodgy, but the above way is better. Dan -- signature Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
avoiding Locked threads
I've got a very high traffic discussion forum database that is constantly running into a problem with lots and lots of threads in the Locked state. i was under the impression that MySQL could update/insert and select from the same table at the same time, but it doesn't seem to be the case. all tables are MyISAM. the machine is a dual xeon 3.2G with 2G of RAM now but i am upgrading it to 4G. the database is on a 15K RPM SCSI RAID0+1. the cpu load and io load all looks pretty good. here's my my.cnf: [mysqld] set-variable= query_cache_size=384M set-variable= key_buffer=384M set-variable= max_allowed_packet=4M set-variable= table_cache=64 set-variable= sort_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=8 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=120 set-variable= max_binlog_size=5 set-variable= long_query_time=5 server-id= 1 log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog here's a sample of some of the locked threads at the moment: INSERT INTO MSGPOST VALUES (NULL, '931046', 'leon is...br/b(SPOILERS)/bbr/-br/-br/-br/ | INSERT INTO MSGPOST VALUES (NULL, '943738', 'All I got is from the British. It should be the same th | INSERT INTO MSGPOST VALUES (NULL, '951538', '(i)Don\'t forgot that Florina can BARELY hold a slim la | INSERT INTO MSGPOST VALUES (NULL, '955788', 'it stands for cyberathletic amateur leaguebr/---br/ | INSERT INTO MSGPOST VALUES (NULL, '971210', 'I\'m getting a job at a place that sells DnD books and | SELECT * FROM MSGPOST WHERE MsgPostId = 4466518 SELECT * FROM MSGPOST WHERE MsgPostId = 4466519 SELECT * FROM MSGPOST WHERE MsgPostId = 4466523 SELECT * FROM MSGPOST WHERE MsgPostId = 4466524 SELECT * FROM MSGPOST WHERE MsgPostId = 4466526 SELECT * FROM MSGPOST WHERE MsgPostId = 4466527 SELECT * FROM MSGPOST WHERE MsgPostId = 4466532 SELECT * FROM MSGPOST WHERE MsgPostId = 4466534 we've tried using MERGE tables on some of the real busy ones to break up the updates but we're still seeing locks doing SELECT on the merged table. any ideas appreciated! -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: avoiding Locked threads
-Original Message- From: news [mailto:[EMAIL PROTECTED] On Behalf Of Jon Drukman Sent: Thursday, May 13, 2004 3:21 PM To: [EMAIL PROTECTED] Subject: avoiding Locked threads I've got a very high traffic discussion forum database that is constantly running into a problem with lots and lots of threads in the Locked state. i was under the impression that MySQL could update/insert and select from the same table at the same time, but it doesn't seem to be the case. all tables are MyISAM. the machine is a dual xeon 3.2G with 2G of RAM now but i am upgrading it to 4G. NOTE: 4GB only helps because of system cache, mySQL in 32 bit cannot go beyond 2GB with out some experimental tweak that are separate from mySQL. the database is on a 15K RPM SCSI RAID0+1. the cpu load and io load all looks pretty good. here's my my.cnf: [mysqld] set-variable= query_cache_size=384M set-variable= key_buffer=384M set-variable= max_allowed_packet=4M set-variable= table_cache=64 set-variable= sort_buffer=8M set-variable= record_buffer=8M set-variable= thread_cache=8 set-variable= tmp_table_size=128M set-variable= thread_concurrency=4 the above is for solaris only set-variable= myisam_sort_buffer_size=128M set-variable= max_connections=1800 set-variable= max_connect_errors=10 set-variable= wait_timeout=120 set-variable= max_binlog_size=5 set-variable= long_query_time=5 server-id= 1 log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? here's a sample of some of the locked threads at the moment: INSERT INTO MSGPOST VALUES (NULL, '931046', 'leon is...br/b(SPOILERS)/bbr/-br/-br/-br/ | INSERT INTO MSGPOST VALUES (NULL, '943738', 'All I got is from the British. It should be the same th | INSERT INTO MSGPOST VALUES (NULL, '951538', '(i)Don\'t forgot that Florina can BARELY hold a slim la | INSERT INTO MSGPOST VALUES (NULL, '955788', 'it stands for cyberathletic amateur leaguebr/---br/ | INSERT INTO MSGPOST VALUES (NULL, '971210', 'I\'m getting a job at a place that sells DnD books and | SELECT * FROM MSGPOST WHERE MsgPostId = 4466518 SELECT * FROM MSGPOST WHERE MsgPostId = 4466519 SELECT * FROM MSGPOST WHERE MsgPostId = 4466523 SELECT * FROM MSGPOST WHERE MsgPostId = 4466524 SELECT * FROM MSGPOST WHERE MsgPostId = 4466526 SELECT * FROM MSGPOST WHERE MsgPostId = 4466527 SELECT * FROM MSGPOST WHERE MsgPostId = 4466532 SELECT * FROM MSGPOST WHERE MsgPostId = 4466534 we've tried using MERGE tables on some of the real busy ones to break up the updates but we're still seeing locks doing SELECT on the merged table. I assume that your msgPostId is a primary key? any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. -jsd- -- 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]
ifnull and quote
Hi everyone. I seem to be getting some funny results when I combine the IFNULL and QUOTE functions. Maybe I have overlooked something I can't see what. Anyway here's the situation. SELECT version(); 4.0.18-max-debug-log The query I'm having problems with is as follows: SELECT attachment, QUOTE(attachment), IFNULL( QUOTE(attachment), 'NULLVALUE' ), IF( ISNULL( QUOTE(attachment) ), 'NULLVALUE', QUOTE(attachment) ), IF( ISNULL( attachment ), 'NULLVALUE', QUOTE(attachment) ) FROM notes WHERE parent=2408 I get two rows with the following values. (NULL),(NULL),NULLVALUE,NULLVALUE,NULLVALUE 32645.html,'32645.html',NULLVALUE,NULLVALUE,'32645.html' The first row of results is fine. The question I have is about the third and fourth value in the second row. Surely these values should not be NULLVALUE because there is no way that QUOTE(attachment) is null for this row, as illustrated by the second value in that row. Sure I can use the result from the fifth column, but I don't understand why the the third or fourth columns don't have the values that I would expect. Thanks in advance. Toro -- Equillia Ltd. Outreach - Contact Management Software PO Box 5735, Dunedin, New Zealand Phone: +64 3 471-6751 Fax: +64 3 477-8020 http://equillia.com - http://equillia.net # This e-mail message has been scanned for Viruses and Content and cleared by MailMarshal For more information please visit www.marshalsoftware.com # -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB Questions
-Original Message- From: Lou Olsten [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 6:45 AM To: [EMAIL PROTECTED] Subject: InnoDB Questions Need someone with some insight or experience with InnoDB (Heikki?? :-) a) Where does InnoDB store all of this information (such as the deleted rows)? In the InnoDB tablespace? In the innodb transaction logs, usually 2 of them are created with an archive log b) How long is it stored? It's flushed on commit and/or every few seconds c) Can we (users) control any of this? Sort of but not as fine grain as it seem your indicating. 2) Another question I have is surrounding fragmentation. Is there a way I can monitor my InnoDB tables to see the level of fragmentation on the table and/or indexes? No, other then looking at show table status and looking at pages free. Look up innodb start up options on mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: InnoDB filesystem
-Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 7:11 AM To: MySQL List Subject: Re: InnoDB filesystem On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? It depends, if your datafile is less then 16 GB then the system cache can help, but fill up the innodb_buffer_pool you'll get better performance. Think of innodb as being its own virtual filesystem. If you have 16GB it's probably a 64 bit OS, and mysql is available in 64 bit. Tim -- 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: InnoDB filesystem
On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 7:11 AM To: MySQL List Subject: Re: InnoDB filesystem On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? It depends, if your datafile is less then 16 GB then the system cache can help, but fill up the innodb_buffer_pool you'll get better performance. Think of innodb as being its own virtual filesystem. If you have 16GB it's probably a 64 bit OS, and mysql is available in 64 bit. I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. 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: InnoDB filesystem
-Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 4:03 PM To: Dathan Vance Pattishall Cc: 'Tim Cutts'; 'MySQL List' Subject: Re: InnoDB filesystem On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Intel box with 4GB? It is possible with a patch like hugemem in Linux but 4GB should only be used 2^32 = 4GB. - The hugemem patch for instance allows you to use all 16 GB but at a performance penalty. 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]
MyISAM transactions
Will MyISAM support transactions in the future versions? Is it possible? -thaks, Lorderon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM transactions
On Fri, 14 May 2004 04:38:56 +0200 Lorderon [EMAIL PROTECTED] wrote: Will MyISAM support transactions in the future versions? Is it possible? Not at the moment, I think 5.1 will support foreign keys in MyISAM, but not sure about transactions... Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MyISAM transactions
On Fri, May 14, 2004 at 04:38:56AM +0200, Lorderon wrote: Will MyISAM support transactions in the future versions? Is it possible? It's possible, but I don't see it happening for quite a while... -- 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: fastest filesystem for MySQL
Jacob, I'd go with Reiser on SuSE. What about Reiser on Debian? I'd choose SuSE since Reiser is their default filesystem and they have been an early implementor of Reiser-related patches. If you use Linux kernel 2.4.24 (or later) and the latest 3.6 series of ReiserFS+tools, the Linux distribution you choose shouldn't technically matter. I'm under the impression that Debian isn't bleeding-edge in many respects, perhaps due to its support of so many architectures, so you might have to build all of this yourself (or find someone who has) if you go that route. In regard to using raw disks under the InnoDB storage engine, if it's I/O code is of the same quality as the filesystems we're discussing, it'll almost necessarily be faster. Again, you'll probably be your own best judge and panel. If you do perform benchmarks, you'll probably want to perform reboots (at a minimum) between tests to bypass caching effects - unless that's what you're trying to test. :) I'd be interested in hearing what you discover. Roy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
Jeremy Zawodny wrote: On Thu, May 13, 2004 at 04:51:27PM -0700, Dathan Vance Pattishall wrote: -Original Message- From: Tim Cutts [mailto:[EMAIL PROTECTED] Sent: Thursday, May 13, 2004 7:11 AM To: MySQL List Subject: Re: InnoDB filesystem On 13 May 2004, at 3:34 pm, Dan Nelson wrote: Pros: performance and bypassing the filesystem cache. MySQL can't use all that memory itself, so it makes sense to allow the OS to cache as much disk space as possible in the memory that MySQL can't use directly? It depends, if your datafile is less then 16 GB then the system cache can help, but fill up the innodb_buffer_pool you'll get better performance. Think of innodb as being its own virtual filesystem. If you have 16GB it's probably a 64 bit OS, and mysql is available in 64 bit. I think that the problem is that it's *not* a 64 bit OS. It's just an Intel 32bit box with 4GB of memory. And sine MySQL doesn't do PAE, it'll never see that extra memory. Didn't InnoDB gain PAE support on some platforms a little while ago? Best regards, Chris Jeremy -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Help about a db
In my view, when you have a one-to-many relationship like users to enterprises, you should always design the database to handle *any* number of occurrences (enterprises in this case). The standard way of doing that would be to normalize to Third Normal Form. Your current design isn't even in First Normal Form since it includes a repeating group (Enterprise repeats twice). What will you do when one of your users suddenly has 4 enterprises? Or 10? Maybe it won't happen right away but it almost certainly *will* happen eventually. That is not to say that repeating groups are always wrong. There are cases where denormalization to something like what you describe is acceptable. However, the normal advice given on database design courses is to normalize everything to Third Normal Form (or even Fourth Normal Form), then denormalize selected cases ONLY WHEN YOU CAN MAKE A STRONG SPECIFIC PERFORMANCE ARGUMENT IN FAVOUR OF THE DENORMALIZATION AND ARE PREPARED TO PAY FOR THE EXTRA OVERHEAD INVOLVED. The resulting design would look like this: Users -- user varchar enterprise int primary key(user, enterprise) foreign key(enterprise) references Enterprise(code) Enterprise --- code int autoincrement description varchar primary key (code) The data would look like this: Users -- George 1 George 2 Rene 3 Simone 4 Enterprises - 1 Hotel Paris 2 Camion de Location Marseilles 3 Brasserie Levesque 4 Boutique Simard To get all the enterprises owned by a given user: select user, enterprise from users u inner join enterprises e on u.enterprises = e.code This query will return one row for each enterprise owned by the user, no matter how many enterprises he or she owns. This design also eliminates the question of what data to store for the second enterprise when a user has only one enterprise; that will never happen. Rhino - Original Message - From: Pierre-Etienne Mélet [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Thursday, May 13, 2004 6:42 PM Subject: Help about a db Hi all, I have the following data Users : user(varchar), entreprise_1(int), entreprise_2(int) Entreprise: idx(int, prim, auto_incr), code(varchar) some users have one entreprise, and some two. if I decide that 'no entreprise x' is coded with entreprise_x=0, i have obviously a problem with SELECT Users.* FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 WHERE entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx AND (Entreprise1.code='CODEX' OR Entreprise2.code='CODEX'); since SELECT Users.* FROM Users, Entreprise Entreprise1, Entreprise Entreprise2 WHERE entreprise_1=Entreprise1.idx AND entreprise_2=Entreprise2.idx returns 0 tuples when entreprise_2=0 In fact, i'm looking for the smartest way to code 'nothing'. For the moment, i've created the Enterprise 'dummy' (idx=0, code='VOID'), but i absolutely not satisfied with this solution. Any idea ? Thank you all by advance -- 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: MyISAM transactions
Jeremy Zawodny wrote: On Fri, May 14, 2004 at 04:38:56AM +0200, Lorderon wrote: Will MyISAM support transactions in the future versions? Is it possible? It's possible, but I don't see it happening for quite a while... Additionally, is there much of a point considering the fact that MyISAM's strengths are simplicty and a lack of transaction related overhead? Couple this with InnoDB's excellent performance and it looks very unlikely to happen IMHO. Best regards, Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: ifnull and quote
I get the same behavior in my copy of 4.0.17. This looks like a bug to me, so I've submitted it as one http://bugs.mysql.com/bug.php?id=3756. Michael Toro Hill wrote: Hi everyone. I seem to be getting some funny results when I combine the IFNULL and QUOTE functions. Maybe I have overlooked something I can't see what. Anyway here's the situation. SELECT version(); 4.0.18-max-debug-log The query I'm having problems with is as follows: SELECT attachment, QUOTE(attachment), IFNULL( QUOTE(attachment), 'NULLVALUE' ), IF( ISNULL( QUOTE(attachment) ), 'NULLVALUE', QUOTE(attachment) ), IF( ISNULL( attachment ), 'NULLVALUE', QUOTE(attachment) ) FROM notes WHERE parent=2408 I get two rows with the following values. (NULL),(NULL),NULLVALUE,NULLVALUE,NULLVALUE 32645.html,'32645.html',NULLVALUE,NULLVALUE,'32645.html' The first row of results is fine. The question I have is about the third and fourth value in the second row. Surely these values should not be NULLVALUE because there is no way that QUOTE(attachment) is null for this row, as illustrated by the second value in that row. Sure I can use the result from the fifth column, but I don't understand why the the third or fourth columns don't have the values that I would expect. Thanks in advance. Toro -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
