integers
I created my first table with integer columns and am having trouble importing csv files with MySQL-Front. My decimal columns work fine, but not int. I had it set up like this: int 10 (length) NULL NULL The error message is kind of strange. It looks like every single value is incorrect, but it only lists the numerals following the comma. For example, it reports 625 as an error for 15,625, 252 as an error for 18,252. I don't understand signed, unsigned and binary, but one tutorial I checked out uses unsigned, so I tried it, without success. The numerals I'm working with range in value from 0 to perhaps 9 digits - 100,000,000. __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On Fri, May 14, 2004 at 01:40:37PM +1000, Chris Nolan wrote: Jeremy Zawodny 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. Didn't InnoDB gain PAE support on some platforms a little while ago? I think it may on Windows. That rings a very vague, distant bell. 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: MyISAM transactions
On Fri, May 14, 2004 at 02:16:05PM +1000, Chris Nolan wrote: 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. I agree. But don't be surprised when MyISAM picks up at least a few of InnoDB's features... 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]
Comments/questions on High Performance MySQL
Hi, I just finished reading High Performance MySQL. Congratulations to the authors, it's a great book and I enjoyed reading it. Would be great if someone now started to write MySQL Internals ;-) There are some items in the book I'd like to comment or ask questions about. I hope the list is a right place to do so. page 12/13: On p.12, the output of SHOW PROCESSLIST is shown. In the Command column, the value is Query. However, table 1-1 on p.13 indicates that the Command column would display Processlist when the client is running SHOW PROCESSLIST. With 4.0.17, I verified that the output is indeed Query. Is it a MySQL or a documentation bug? page 68: Quote: [...], a NULL value may appear only once as a primary key. What's the meaning of this - all columns of a PRIMARY KEY in MySQL are forced to be NOT NULL, aren't they? page 125: I'd like to know whether it's recommended to include myisam_sort_buffer in the computation of the minimum memory needed? In the my-medium.cnf configuration, for instance, myisam_sort_buffer is clearly the largest of all thread buffers (8M vs. 512K for the second-largest buffer). In my-(large|huge).cnf, it's even 64M. The MySQL manual on myisam_sort_buffer_size says: The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. To me, these seem rather rare operations that won't be performed by most of the clients. It makes a big difference, however, whether I use 9M or 1M per client in the computation of required memory (or even 64M per client with my-(large|huge).cnf). Do I miss something here? page 211: The username and hostname combination listing contains, in that order: @%.example.com [EMAIL PROTECTED] Since the second user entry is more specific (the MySQL manual says an empty user is least specific), shouldn't these entries be sorted in the reverse order? Also, the text following the listing explains: When jane connects from web.example.com, she'll receive the privileges granted to [EMAIL PROTECTED]. With a first-match rule, this makes sense only if [EMAIL PROTECTED] appears first. Thank you, Hans-Peter -- 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 14 May 2004, at 4:37 am, Roy Butler wrote: 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. Debian is reasonably current if you follow the testing tree, rather than its stable releases. Debian stable is on ReiserFS 3.6.25 for 2.4 kernels, so it's not too out of date. The testing tree has support for 2.6 kernels too. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
can't drop database: ERROR 1217
version: mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686) mysql DROP DATABASE xxx; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails what is this all about? how the heck can I drop the database? Dean Hoover -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: InnoDB filesystem
On 14 May 2004, at 1:14 am, Dathan Vance Pattishall wrote: -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. I have 168 32-bit machines (IBM HS20 blades) which can take 8GB RAM each, although we don't have them configured with that much. So there are plenty of these machines available. I tend to agree though, that for MySQL, if you want lots of memory its better to go for a 64-bit platform. We've been using Alphas and Tru64 for MySQL databases for years. Tim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MySql cluster
Hello, I am not a guru in mysql. Please somebody help me, Weather what I have done is correct or not. I created a replication cluster in mysql using two machines. machine A replicates to Machine B machine B replicates to Machine A. and heartbeat is used to give a service ipaddress. I tested it and its working. but it is now in a test platform and having nothing in it, no much tables or records. when A is master it is replicating to B and when A is down Harbert give the service IP to B and clients having database from B. and working with B . and when A is back B replicates to A. Presently it is working, whatever changes creating to A or B is replicating to the other. my question is weather it will work even if there is a loot of records and tables or in a heavy load situations ??? Thanks and Regards Ditto Kolankanny -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setup MySQL in RedHat ES 3
Dear Sir, As MySQL is included in this distribution. I install it together with RedHat. Do you know what do I need to do to make it start and working? I cannot find related document from www.mysql.com. Thanks, Joseph -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Comments/questions on High Performance MySQL
Hi. - Original Message - From: Hans-Peter Grimm [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 14, 2004 2:53 AM Subject: Comments/questions on High Performance MySQL Hi, I just finished reading High Performance MySQL. Congratulations to the authors, it's a great book and I enjoyed reading it. Would be great if someone now started to write MySQL Internals ;-) There are some items in the book I'd like to comment or ask questions about. I hope the list is a right place to do so. page 12/13: On p.12, the output of SHOW PROCESSLIST is shown. In the Command column, the value is Query. However, table 1-1 on p.13 indicates that the Command column would display Processlist when the client is running SHOW PROCESSLIST. With 4.0.17, I verified that the output is indeed Query. Is it a MySQL or a documentation bug? On my 4.0 and 4.1 installations, I get Query page 68: Quote: [...], a NULL value may appear only once as a primary key. What's the meaning of this - all columns of a PRIMARY KEY in MySQL are forced to be NOT NULL, aren't they? No, they can be NULL. This is a new concept to me, but I actually hit a case the other day where it seemed useful. I posted this query for another user who was trying to find all users without a computer: select * from users left join computers on (users.user_id = computers.user_id) where computers.user_id is NULL; user_id was his PK, but in order for the WHERE clause to work properly, it had to be null (http://dev.mysql.com/doc/mysql/en/LEFT_JOIN_optimisation.html). I suppose you could rebuild the table with a different (perhaps auto_increment) PK to avoid this. But to set up this test, I made it the PK and made it to allow NULLs, so MySQL does allow you to do it. page 125: I'd like to know whether it's recommended to include myisam_sort_buffer in the computation of the minimum memory needed? In the my-medium.cnf configuration, for instance, myisam_sort_buffer is clearly the largest of all thread buffers (8M vs. 512K for the second-largest buffer). In my-(large|huge).cnf, it's even 64M. The MySQL manual on myisam_sort_buffer_size says: The buffer that is allocated when sorting the index when doing a REPAIR or when creating indexes with CREATE INDEX or ALTER TABLE. To me, these seem rather rare operations that won't be performed by most of the clients. It makes a big difference, however, whether I use 9M or 1M per client in the computation of required memory (or even 64M per client with my-(large|huge).cnf). Do I miss something here? page 211: The username and hostname combination listing contains, in that order: @%.example.com [EMAIL PROTECTED] Since the second user entry is more specific (the MySQL manual says an empty user is least specific), shouldn't these entries be sorted in the reverse order? Also, the text following the listing explains: When jane connects from web.example.com, she'll receive the privileges granted to [EMAIL PROTECTED]. With a first-match rule, this makes sense only if [EMAIL PROTECTED] appears first. Thank you, Hans-Peter -- 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 web host
Hi, I know this may not strictly be within the remit of this mailing list but. I am having problems with my web host, I need to use Innodb tables, however my web host will not allow these table types (because of the obvious extra overhead), can anybody out there recommend/currently use a webhost who allow the Innodb table type? Many thanks Joe -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Exists
Hi, How can I write this query? Insert into table1 (field1) select (a.field1) from table2 a where a.field1 not exists (select c.field1 from table1 c) 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]
Re: Exists
What version of MySQL are you running? The answer to your question depends on the version. Rhino - Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 14, 2004 9:00 AM Subject: Exists Hi, How can I write this query? Insert into table1 (field1) select (a.field1) from table2 a where a.field1 not exists (select c.field1 from table1 c) 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]
Re: Exists
On Fri, 14 May 2004 14:00:46 +0100 (BST) A Z [EMAIL PROTECTED] wrote: Hi, How can I write this query? Insert into table1 (field1) select (a.field1) from table2 a where a.field1 not exists (select c.field1 from table1 c) http://dev.mysql.com/doc/mysql/en/Rewriting_subqueries.html Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Exists
MySQL 4.0.14 regards --- Rhino [EMAIL PROTECTED] wrote: What version of MySQL are you running? The answer to your question depends on the version. Rhino - Original Message - From: A Z [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Friday, May 14, 2004 9:00 AM Subject: Exists Hi, How can I write this query? Insert into table1 (field1) select (a.field1) from table2 a where a.field1 not exists (select c.field1 from table1 c) 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] 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]
RE: can't drop database: ERROR 1217
Dean, Seems your database has a table with foreign key constraints. You need to delete that table first and then you will be able to drop database successfully. Hope that helps!!! Raza -Original Message- From: Dean A. Hoover [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 4:13 AM To: [EMAIL PROTECTED] Subject: can't drop database: ERROR 1217 version: mysql Ver 12.22 Distrib 4.0.18, for pc-linux (i686) mysql DROP DATABASE xxx; ERROR 1217: Cannot delete or update a parent row: a foreign key constraint fails what is this all about? how the heck can I drop the database? Dean Hoover -- 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 remote to apache
Is there a good tutorial on installing Mysql on one machine and having Apache on another? I'm thinking on the server with Apache I guess you would just compile Apache with the mysql/php and in your php scripts just point to the hostname of the mysql server? Making sure you can talk to port 3306. Has anyone seen performance issues with large databases and the results going over tcp, instead of the local installs which i use sockets? Some of my query results return 150,000 records. Im just running out of ram on the machine with both Apache/mysql and I cant upgrade to anymore memory for my dell server, at 4gb ram. rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql remote to apache
Just a thought - but do your web pages actually display all 150K records at once? YOu may want to change how your page requests and do limits, and just do grabs at 100 at a time, or so. UNless of course you are processing all 150K in one big calculation of some sort - but then, I wouldnt do that webbased On Fri, 14 May 2004 06:52 , rmck [EMAIL PROTECTED] sent: Is there a good tutorial on installing Mysql on one machine and having Apache on another? I'm thinking on the server with Apache I guess you would just compile Apache with the mysql/php and in your php scripts just point to the hostname of the mysql server? Making sure you can talk to port 3306. Has anyone seen performance issues with large databases and the results going over tcp, instead of the local installs which i use sockets? Some of my query results return 150,000 records. Im just running out of ram on the machine with both Apache/mysql and I cant upgrade to anymore memory for my dell server, at 4gb ram. rob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] --- Chris McKeever If you want to reply directly to me, please use cgmckeever--at--prupref---dot---com http://www.prupref.com Prudential Preferred Properties Chicago and Illinois NorthShore Real Estate Experts Prudential Preferred Properties www.prupref.com Success Driven By Results Results Driven By Commitment Commitment Driven By Integrity We Are Prudential Preferred Properties -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: mysql remote to apache
I'm thinking on the server with Apache I guess you would just compile Apache with the mysql/php and in your php scripts just point to the hostname of the mysql server? Making sure you can talk to port 3306. It's not a very big problem (loads of people have done it before you). - Make sure php knows the correct host/port/user/password - Make sure MySQL grants access to the database for the right host/user/password Has anyone seen performance issues with large databases and the results going over tcp, instead of the local installs which i use sockets? Some of my query results return 150,000 records. Im just running out of ram on the machine with both Apache/mysql and I cant upgrade to anymore memory for my dell server, at 4gb ram. In this case I (and my sysadmin) think that you will gain speed by using a dedicated MySQL server (all the resources such as cached files, cpu, etc. are available to MySQL alone), which will compensate a bit for the speed penalty of tcp. A query result of 150,000 records is a big though IMHO. With such a large recordset I fear that php will be slower processing all the data than the slow tcp connection (local network I hope) can feed the data. Maybe you should consider different queries that do a lot of the processing? Regards, Jigal. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: avoiding Locked threads
Dathan Vance Pattishall wrote: log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? yes, the database is the only thing on the high speed RAID. everything else is on the other drive (also a RAID but only RAID0 with 2 drives). any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL i haven't tried this yet, but one of the other developers has objected that doing this will kill performance for people posting messages because their clients will hang waiting for the selects to finish. is this true? Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. h we've got approx 60G free on the RAID so this may not be such a big problem. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
table marked as crashed, then repaired by check?
Hi All, MySQL 4.0.18-standard on RHEL3, 2.4.21-15.ELsmp kernel. I have a table on one a moderate traffic site, and yesterday I had my client notify me that some of the pages were not working. Note that most of the page content is stored in a table called pageContent. Now, about half the pages worked, and about half of the pages displayed an error that was something like error opening file pageContent.MYI. All the other tables were working fine, and when I pulled up phpMyAdmin to look at the database, it listed the pageContent table as being in use, and wouldn't let me look at any of the data. Trying to view the structure gave me the same error about not being able to open the pageContent.MYI file. Hmm, strange... I tried a CHECK TABLE `pageContent`, and it returned Msg_type of warning and Msg_text of table is marked as crashed. Great. But, when I went back to the table list of phpMyAdmin, it no longer displayed the in use message for the pageContent table. Did running a CHECK TABLE repair the table somehow? I did not know it actually performed any repairs. But a subsequent CHECK TABLE return Msg_type status and Msg_text OK. Furthermore, I no longer got the message about being unable to open the pageContent.MYI file anymore. What happened? Did running CHECK TABLE `pageContent` repair my table? Is this expected behavior? Thanks! -Josh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Integers - How would you enter 112,249?
When I try to import my csv file in MySQL-Front, it appears to get hung up on every integer - or perhaps it's just every integer in the first integer field. When it cites an error, it only lists the numerals after the comma. For example, it says 249 for this sample from my csv file: 112,249,al,Calhoun,county, I don't see anything wrong with that, so I assume the problem lies with my table set up. I've tried various combinations without success. I inserted one value - 112,249 - direcly with phpMyAdmin, and it did just the opposite - it only displays 112. I though perhaps it would automatically insert the comma if I just inserted the six numerals, but it doesn't. Could someone tell me all the values you would use, assuming this field is not an index or key? I prsently have... int(10) | | Yes | NULL I deleted the length, but it inserted 11 by default. Any tips? __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integers - How would you enter 112,249?
In the last episode (May 14), David Blomstrom said: When I try to import my csv file in MySQL-Front, it appears to get hung up on every integer - or perhaps it's just every integer in the first integer field. When it cites an error, it only lists the numerals after the comma. For example, it says 249 for this sample from my csv file: 112,249,al,Calhoun,county, I don't see anything wrong with that, so I assume the problem lies with my table set up. I've tried various combinations without success. Commas are not part of the number. Just insert 112249. -- 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: Integers - How would you enter 112,249?
At 8:34 -0700 5/14/04, David Blomstrom wrote: When I try to import my csv file in MySQL-Front, it appears to get hung up on every integer - or perhaps it's just every integer in the first integer field. When it cites an error, it only lists the numerals after the comma. For example, it says 249 for this sample from my csv file: 112,249,al,Calhoun,county, I don't see anything wrong with that, so I assume the problem lies with my table set up. I've tried various combinations without success. Number syntax is documented in the manual: http://dev.mysql.com/doc/mysql/en/Number_syntax.html It doesn't say anything about commas being legal. I inserted one value - 112,249 - direcly with phpMyAdmin, and it did just the opposite - it only displays 112. I though perhaps it would automatically insert the comma if I just inserted the six numerals, but it doesn't. Could someone tell me all the values you would use, assuming this field is not an index or key? I prsently have... int(10) | | Yes | NULL I deleted the length, but it inserted 11 by default. Any tips? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
access problem
I have database Zope I run following command and get error myql grant all on Zope.* to [EMAIL PROTECTED]; ERROR 1044: Access denied for user: '[EMAIL PROTECTED]' to database 'Zope' mysql As a consequece I try to access Zope from a web, it also shows me the error. how can i fix this problem? Thanks -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Installation of mysql-3.23.58 on Redhat 9
All, I am looking for a stepwise installation of mysql-3.23.58 on Redhat 9. and have a hard time finding it on the MySql web site. Could someone e-mail me the steps, please. There should also be a section on installing databases, in order to make sure that MySql is running and is operational Thanks, P.S as a sample, i am providing the following, which is good, but still not complete. Get the sourceballs MySQL current version: 3.23.58 URL: http://www.mysql.com/downloads/ http://www.mysql.com/downloads/ ... Change into the MySQL source directory as follows; #cd mysql-4.0.16 Follow this command by typing; #./configure -prefix=/usr/local/mysql -localstatedir=/usr/local/mysql/data -disable-maintainer-mode -with-mysqld-user=mysql -enable-large-files-without-debug #make #make install MySQL is installed, #/usr/sbin/groupadd mysql #/usr/sbin/useradd -g mysql mysql #./scripts/mysql_install_db Then we make a couple minor ownership changes; # chown -R root:mysql /usr/local/mysql # chown -R mysql:mysql /usr/local/mysql/data we use vi to add a line the ld.so.conf file as follows; #vi /etc/ld.so.conf And we add the following line; /usr/local/mysql/lib/mysql #/usr/local/mysql/bin/mysqld_safe -user=mysql #/usr/local/mysql/bin/mysqladmin -u root password new_password Kamal Ahmed Sr. Test Engineer e-Security, Inc. Enterprise Security Management 1921 Gallows Road, Suite 700 Vienna, VA 22182 phone: 703-852-8055 fax: 703-852-8010
Re: Integers - How would you enter 112,249?
David Blomstrom [EMAIL PROTECTED] wrote in message news:[EMAIL PROTECTED] When I try to import my csv file in MySQL-Front, it appears to get hung up on every integer - or perhaps it's just every integer in the first integer field. When it cites an error, it only lists the numerals after the comma. For example, it says 249 for this sample from my csv file: 112,249,al,Calhoun,county, AFAIK 112,249 is not an integer. It is a string. If you need to have the commas, you need to store it as a string. -- 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
Sorry - haven't had a chance to respond till now. 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. I'm running ibbackup on an unlicenced machine. The manual states, ibbackup --apply-log /home/pekka/.backup-my.cnf Here's the output with --apply-log (some sensitive info stripped) --- mydb2:/data # ./ibbackup --apply-log ./my2.cnf InnoDB Hot Backup version 1.40; Copyright 2003 Innobase Oy License xxx is granted to (--restore works in any computer regardless of the hostname) Licensed for use in a computer whose hostname is 'hrdb1' Expires 2005-6-1 (year-month-day) at 00:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help --- Error: the hostname of this computer is 'mydb2'. Please contact [EMAIL PROTECTED] for a license renewal. If I run with --restore, however (same directory, same files, 30 seconds after the above command was run), mydb2:/data # ./ibbackup --restore ./my2.cnf InnoDB Hot Backup version 1.40; Copyright 2003 Innobase Oy License xxx is granted to (--restore works in any computer regardless of the hostname) Licensed for use in a computer whose hostname is 'hrdb1' Expires 2005-6-1 (year-month-day) at 00:00 See http://www.innodb.com for further information Type ibbackup --license for detailed license terms, --help for help Contents of ./my2.cnf: innodb_data_home_dir got value /data innodb_data_file_path got value ibdata1:2000M;ibdata2:2000M;ibdata3:2000M;ibdata 4:100M:autoextend innodb_log_group_home_dir got value /data innodb_log_files_in_group got value 2 innodb_log_file_size got value 104857600 ibbackup: We were able to parse ibbackup_logfile up to ibbackup: lsn 5 2076170601 040514 9:51:43 ibbackup: Full backup restoration successful! - Am I doing something wrong with regards to --apply-log? 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? That's a possibility - we were using a demo-ibbackup for a month or two, before ordering the full version - I guess the demo was beta, and the licenced version was 1.4. There is no version # in the name of the file, and I didn't think to check the version by running with the --help flag. I tried with a more recent backup, and it worked (as you can see above). 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. Ok - thx - that makes sense. David. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Books High Performance MySQL and Server Load Balancing
Hi All, I'm reading High Performance MySQL. In my humble opinion is an very good book. Congratulations to the authors. In the chapter Load Balancing and High Availability is cited the Tony Bourke's book Server Load Balancing. Someone read it? is one good reference font? I need study this subject. Any other recommendation? Thanks in advance. Renato Cramer. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Upgrading MySQL
Dear All, Hello is it advised not to do an upgrade on a Mysql 4.x to latest MySQL? What if the 4.x version is installed at installation time of say, RHL8, can an upgrade then be done afterwards to latest version? Just looking for any recommendations, etc Thanks in advance!~ Carlos - Do you Yahoo!? SBC Yahoo! - Internet access at a great low price.
Re: table marked as crashed, then repaired by check?
Joshua Beall wrote: Hi All, MySQL 4.0.18-standard on RHEL3, 2.4.21-15.ELsmp kernel. I have a table on one a moderate traffic site, and yesterday I had my client notify me that some of the pages were not working. Note that most of the page content is stored in a table called pageContent. Now, about half the pages worked, and about half of the pages displayed an error that was something like error opening file pageContent.MYI. All the other tables were working fine, and when I pulled up phpMyAdmin to look at the database, it listed the pageContent table as being in use, and wouldn't let me look at any of the data. Trying to view the structure gave me the same error about not being able to open the pageContent.MYI file. Hmm, strange... I tried a CHECK TABLE `pageContent`, and it returned Msg_type of warning and Msg_text of table is marked as crashed. Great. But, when I went back to the table list of phpMyAdmin, it no longer displayed the in use message for the pageContent table. Did running a CHECK TABLE repair the table somehow? I did not know it actually performed any repairs. But a subsequent CHECK TABLE return Msg_type status and Msg_text OK. Furthermore, I no longer got the message about being unable to open the pageContent.MYI file anymore. What happened? Did running CHECK TABLE `pageContent` repair my table? Is this expected behavior? Josh: I have several possibilities/explanations for the above behaviour: * you have myisam-recover enabled in my.cnf * MySQL has a bug that auto-enables myisam-recover in some cases, or thinks it is enabled * you have some secret script on your system than periodically repairs the table * The subsequent modifications (delete/insert/update) have fixed or obscured the corruption * CHECK TABLE has a bug that resets some flag in the table status area which obscures the corruption -- 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: Integers - How would you enter 112,249?
--- Joshua Beall [EMAIL PROTECTED] wrote: AFAIK 112,249 is not an integer. It is a string. If you need to have the commas, you need to store it as a string. I haven't got to strings yet. The table I'm making is for display purposes, so I want visitors to be able to see 10,212, rather than 10212. I'm also going to make JavaScript sortable columns. So do I need to store it as a string if I want to display columns? Or would I simply use some other function to insert commas for display purposes? __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
SELECT almost every column
Is there a SELECT statement, if I have a table with 50 columns, to select every column EXCEPT the last one? Can I do this without typing the name of all 49 columns? If so, then what if I want to exclude the last TWO columns? Thanks, John -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integers - How would you enter 112,249?
At 10:17 -0700 5/14/04, David Blomstrom wrote: --- Joshua Beall [EMAIL PROTECTED] wrote: AFAIK 112,249 is not an integer. It is a string. If you need to have the commas, you need to store it as a string. I haven't got to strings yet. The table I'm making is for display purposes, so I want visitors to be able to see 10,212, rather than 10212. I'm also going to make JavaScript sortable columns. So do I need to store it as a string if I want to display columns? Or would I simply use some other function to insert commas for display purposes? Insertion and display are two different issues. To insert a number, no commas are allowed. To display a number with commas, you can use the FORMAT() function: http://dev.mysql.com/doc/mysql/en/Miscellaneous_functions.html -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Integers - How would you enter 112,249?
David Blomstrom wrote --- Joshua Beall [EMAIL PROTECTED] wrote: AFAIK 112,249 is not an integer. It is a string. If you need to have the commas, you need to store it as a string. I haven't got to strings yet. The table I'm making is for display purposes, so I want visitors to be able to see 10,212, rather than 10212. I'm also going to make JavaScript sortable columns. So do I need to store it as a string if I want to display columns? Or would I simply use some other function to insert commas for display purposes? /David David, As several people have tried to point out, the data 112,249 represents a string and not a number. The comma is killing you. How you store a number INTERNALLY can be totally different than how you present it to the user. For instance you could HEX() the number and get back 1B679, it's the same number with a different look. You can format your numbers on output however you like (with or without commas, as a sequence of binary digits, as a date value, ) but for the import to work your data must not have commas in the number fields. Respectfully, Shawn Green Database Administrator Unimin Corporation - Spruce Pine -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sub-SELECT
Hi list, does MySQL accept a SELECT inside another SELECT, like this: SELECT t.Trial_ID_Code AS Trial, CONCAT(s.Site_Primary_Investigator_Title, , s.Site_Primary_Investigator_Last_Name) AS 'Investigator', CONCAT(sc.Site_ID_Number, -, s.Site_ID_Code, -, sc.Scanner_ID_Number) AS Site, sc.Scan_Subject_ID AS 'SubjectInitials', sc.Scan_Screening_ID AS 'ScreeningNumber', tp.TimePoint_Code AS 'TimePoint', sc.Scan_Acquired_On AS 'ScanAcquiredOn', IF(sc.QCResult_ID_Number = 2, 'Passed', 'Faild') AS 'QCResult', IF(sc.Scan_Screening_Result='n', 'Failed', 'Passed') AS 'GDScreeningResult' (SELECT Scan_Screening_Result FROM ScanQuantification WHERE sc.Trial_ID_Number = Trial_ID_Number AND sc.Site_ID_Number = Site_ID_Number AND sc.Scanner_ID_Number = Scanner_ID_Number AND sc.Scan_ID_Number = Scan_ID_Number AND sq.Quantification_ID_Number = 1 AND ProcessType_ID_Number = 5) AS 'GDScreeningValue' FROM Scan sc, Trial t, Site s, TimePoint tp WHERE sc.Trial_ID_Number = t.Trial_ID_Number AND sc.Site_ID_Number = s.Site_ID_Number AND sc.TimePoint_ID_Number = tp.TimePoint_ID_Number AND sc.TimePoint_ID_Number = 8 AND (sc.Scan_Receipt_On = '2004-05-10' AND sc.Scan_Receipt_On = '2004-05-14') AND sc.QCResult_ID_Number 1 ORDER BY sc.Site_ID_Number Thanks. Andre -- Andre Matos [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Installation of mysql-3.23.58 on Redhat 9
There's a quick install guide on the MySQL site, which has most of what you've listed: http://dev.mysql.com/doc/mysql/en/Quick_install.html The INSTALL-SOURCE doc which comes in the tarball covers the process in great detail. There may be others, those are two I've used. Kamal Ahmed wrote: All, I am looking for a stepwise installation of mysql-3.23.58 on Redhat 9. and have a hard time finding it on the MySql web site. Could someone e-mail me the steps, please. There should also be a section on installing databases, in order to make sure that MySql is running and is operational Thanks, P.S as a sample, i am providing the following, which is good, but still not complete. Get the sourceballs MySQL current version: 3.23.58 URL: http://www.mysql.com/downloads/ http://www.mysql.com/downloads/ ... Change into the MySQL source directory as follows; #cd mysql-4.0.16 Follow this command by typing; #./configure -prefix=/usr/local/mysql -localstatedir=/usr/local/mysql/data -disable-maintainer-mode -with-mysqld-user=mysql -enable-large-files-without-debug #make #make install MySQL is installed, #/usr/sbin/groupadd mysql #/usr/sbin/useradd -g mysql mysql #./scripts/mysql_install_db Then we make a couple minor ownership changes; # chown -R root:mysql /usr/local/mysql # chown -R mysql:mysql /usr/local/mysql/data we use vi to add a line the ld.so.conf file as follows; #vi /etc/ld.so.conf And we add the following line; /usr/local/mysql/lib/mysql #/usr/local/mysql/bin/mysqld_safe -user=mysql #/usr/local/mysql/bin/mysqladmin -u root password new_password Kamal Ahmed Sr. Test Engineer e-Security, Inc. Enterprise Security Management 1921 Gallows Road, Suite 700 Vienna, VA 22182 phone: 703-852-8055 fax: 703-852-8010 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Restoring a database
I am evaluating mysql for use at our organization. One thing I have not been able to determine from the documentation is -- once I've taken a backup via mysqlhotcopy, how do I restore the database from that backup when necessary? William K. Jones Chief, Database Systems Branch, DCSS, CIT [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 301-402-1241
Re: Restoring a database
You shutdown the server and copy the files back. Jones, William (NIH/CIT) wrote: I am evaluating mysql for use at our organization. One thing I have not been able to determine from the documentation is -- once I've taken a backup via mysqlhotcopy, how do I restore the database from that backup when necessary? William K. Jones Chief, Database Systems Branch, DCSS, CIT [EMAIL PROTECTED] mailto:[EMAIL PROTECTED] 301-402-1241 -- 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
--- PARTHA DUTTA, BLOOMBERG/ 499 PARK [EMAIL PROTECTED] 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. This will work, but keep in mind that it isn't 100% failsafe. If server1 crashes or fails hard, there is a possiblity that some records from server1 will not have replicated to server2. If you never bring server1 back up, or its database is damaged and you have to copy server2 over to server1, then there is a chance that there will be some lost data. One way around that is to use a two-phase commit, but MySQL doesn't support that yet (failsafe replication.) If your application absolutely can not tolerate any lost transactions then you will have to devise a logging scenerio that allows you to playback lost transactions. Also keep in mind that if you are using autoincrementing keys, that your replication could cause duplicates in a multi-master environment during a failover when you bring up the failed server. Plan for that accordingly. -- 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
--- Sasha Pachev [EMAIL PROTECTED] wrote: 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. There actually was a create index that was being executed on the web_master table. Still, that shouldn't result in a deadlock creating the other table should it? DDL against the web_master table of course should lock timeout because of the index creation, but you should be able to create another table based off that table with no problem, since you can obviously get a consistent read off the base table. Should I create a simple test scenario and post it to the bug list? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
OPTIMIZE TABLE and mySQL replication
Hi List, What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? Thanks, Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
In the last episode (May 14), Jim said: What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? Yes, unless you add the LOCAL flag to optimize. http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html -- 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: OPTIMIZE TABLE and mySQL replication
In the last episode (May 14), Dan Nelson said: In the last episode (May 14), Jim said: What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? Yes, unless you add the LOCAL flag to optimize. http://dev.mysql.com/doc/mysql/en/OPTIMIZE_TABLE.html I just noticed that this is 4.1.1 and above. Older versions do not replicate OPTIMIZE. -- 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: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote: Hi List, What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? It does not. The command doesn't replicate. -- 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]
--default_table_type
Is there a way to set this dynamically? Thanks, Lou
RE: OPTIMIZE TABLE and mySQL replication
Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Donny -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 2:46 PM To: Jim Cc: [EMAIL PROTECTED] Subject: Re: OPTIMIZE TABLE and mySQL replication On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote: Hi List, What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? It does not. The command doesn't replicate. -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
Jeremy Zawodny wrote: On Fri, May 14, 2004 at 03:10:05PM -0400, Jim wrote: Hi List, What are some issues relating to using OPTIMIZE TABLE and replication? Does running OPTIMIZE TABLE on a master DB cause the optimizations to be passed on to the slaves? It does not. The command doesn't replicate. Jeremy et al, thanks for the replies. So (and here I expose my newbness) can the OPTIMIZE TABLE be run on the slave database tables, which are read-only? This is on version 4.0.16 of the server. Jim -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: --default_table_type
At 15:57 -0400 5/14/04, Lou Olsten wrote: Is there a way to set this dynamically? The associated system variable is table_type, not default_table_type. SET table_type = xxx; SET SESSION table_type = xxx; SET GLOBAL table_type = xxx; Thanks, Lou -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA MySQL AB, www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... :-( -- 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: OPTIMIZE TABLE and mySQL replication
In the last episode (May 14), Jeremy Zawodny said: On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... I think not having the slaves optimize is more surprising. Slaves get the exact same insert/delete/update queries as the master, so why should only the master get its tables optimized? -- 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: OPTIMIZE TABLE and mySQL replication
It surprised me at first, but then I was actually happy about it. Donny -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 4:26 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Jim' Subject: Re: OPTIMIZE TABLE and mySQL replication On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... :-( -- Jeremy D. Zawodny | Perl, Web, MySQL, Linux Magazine, Yahoo! [EMAIL PROTECTED] | http://jeremy.zawodny.com/ [book] High Performance MySQL -- http://highperformancemysql.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: OPTIMIZE TABLE and mySQL replication
Yes. -Original Message- From: Jeremy Zawodny [mailto:[EMAIL PROTECTED] Sent: Friday, May 14, 2004 4:26 PM To: Donny Simonton Cc: [EMAIL PROTECTED]; 'Jim' Subject: Re: OPTIMIZE TABLE and mySQL replication On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... :-( -- 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: SELECT almost every column
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one? Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
There is no construct in SQL to select X number of
columns from a table.
The traditional answer to this question would normally
be use views, but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
select * from
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = desc $NAME_OF_TABLE
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt = 0)
{ error(to few columns);
return;
}
$sql = select
for ($i=0;$i $cnt-1;$i++)
{ $sql = $sql + $ary[$i][Field] + ,
}
$sql = $sql + $ary[$cnt][Field]
$sql = $sql + FROM $NAME_OF_TABLE_TO_SELECT_FROM
$sql = $sql + WHERE $WHERE_CLAUSE
$sql = $sql + HAVING $HAVING_CLAUSE
$sql = $sql + GROUP BY $GROUP_BY_CLAUSE
$sql = $sql + ORDER BY $ORDER_BY_CLAUSE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT almost every column
Hmmm. The reason I asked was that the last column in the table is TEXT, and
might contain up to 5000 text characters. I'm trying to make the query as
efficient as possible, and I don't know if that much data will make a
noticeable speed difference?
Thanks,
John
on 5/14/04 2:31 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one? Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
There is no construct in SQL to select X number of
columns from a table.
The traditional answer to this question would normally
be use views, but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
select * from
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = desc $NAME_OF_TABLE
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt = 0)
{ error(to few columns);
return;
}
$sql = select
for ($i=0;$i $cnt-1;$i++)
{ $sql = $sql + $ary[$i][Field] + ,
}
$sql = $sql + $ary[$cnt][Field]
$sql = $sql + FROM $NAME_OF_TABLE_TO_SELECT_FROM
$sql = $sql + WHERE $WHERE_CLAUSE
$sql = $sql + HAVING $HAVING_CLAUSE
$sql = $sql + GROUP BY $GROUP_BY_CLAUSE
$sql = $sql + ORDER BY $ORDER_BY_CLAUSE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: OPTIMIZE TABLE and mySQL replication
On Fri, May 14, 2004 at 04:29:29PM -0500, Dan Nelson wrote: In the last episode (May 14), Jeremy Zawodny said: On Fri, May 14, 2004 at 03:26:28PM -0500, Donny Simonton wrote: Actually, if you are using 4.1.1 optimize table does get passed to the slave. This is from the 4.1.1 change log. ANALYZE TABLE, OPTIMIZE TABLE, REPAIR TABLE, and FLUSH statements are now stored in the binary log and thus replicated to slaves. This logging does not occur if the optional NO_WRITE_TO_BINLOG keyword (or its alias LOCAL) is given. Exceptions are that FLUSH LOGS, FLUSH MASTER, FLUSH SLAVE, and FLUSH TABLES WITH READ LOCK are not logged in any case. For a syntax example, see section 14.5.4.2 FLUSH Syntax. Ugh. That's the *default*? Gee, that won't surprise anyone, I'm sure... I think not having the slaves optimize is more surprising. Slaves get the exact same insert/delete/update queries as the master, so why should only the master get its tables optimized? Easy. In the case of ANALYZE, OPTIMIZE, or REPAIR one would generally want to stagger their execution on slaves. Otherwise the exact same tables are unavailable at the same 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: fastest filesystem for MySQL
Reiser is good for lots of small files. ext3 would is better for large ones. At least that's what I get from the benchmark data that I've seen posted in various places. Curtis -- Curtis Maurand mailto:[EMAIL PROTECTED] http://www.maurand.com On Wed, 12 May 2004, Roy Butler wrote: Jacob, I'd go with Reiser on SuSE. Like Sasha mentioned though, the filesystem component may have little overall effect, depending on your set-up. I'd stay away from XFS when working with databases, as its performance gains are achieved via extended write delays while the queue sits in main memory: not the sort of thing you want after a crash... If you have the time/interest, why not try some benchmarks of your own? Roy -- Date: Wed, 12 May 2004 00:22:21 +0200 To: [EMAIL PROTECTED] From: JFL [EMAIL PROTECTED] Subject: fastest filesystem for MySQL Message-ID: [EMAIL PROTECTED] I've heard and read that the Reiser filesystem should be better for MySQL than Ext3. Is this still true? We will be running MySQL on either Red Hat ES 3, Suse or Debian. Thanks, Jacob -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: SELECT almost every column
John Mistler wrote:
Hmmm. The reason I asked was that the last column in the table is TEXT, and
might contain up to 5000 text characters. I'm trying to make the query as
efficient as possible, and I don't know if that much data will make a
noticeable speed difference?
Thanks,
John
on 5/14/04 2:31 PM, Justin Swanhart at [EMAIL PROTECTED] wrote:
John,
In that case even if you only grab one field in the select from the row
the entire Text and/or Blob will be loaded into memory as a side benefit
(/sarcasm).
Assuming a table like:
my_table(id, field1, field2, ..., field49, TEXTField)
You'll do much better by making two tables:
my_table(id_field, field1, field2, ..., field49)
my_table_text(id_field, TEXTField)
Then search on my_table and only pull my_table_text rows by exact match
on the id_field (making both id_fields primary keys in their
respective tables, of course).
HTH,
Robert J Taylor
[EMAIL PROTECTED]
--- John Mistler [EMAIL PROTECTED] wrote:
Is there a SELECT statement, if I have a table with
50 columns, to select
every column EXCEPT the last one? Can I do this
without typing the name of
all 49 columns?
If so, then what if I want to exclude the last TWO
columns?
Thanks,
John
There is no construct in SQL to select X number of
columns from a table.
The traditional answer to this question would normally
be use views, but since MySQL doesn't support them
that doesn't help you very much.
Unless the extra columns are long text columns or
contain BLOBS, then I see no harm in just selecting
them along with the rest of the other columns by using
select * from
If you are accessing the database from a programming
environment then you could do the following:
[pseudo code]
$sql = desc $NAME_OF_TABLE
$result = exec($sql)
$rows = fetch_result_into_array($result)
destroy($result)
$cnt = count($rows) - $NUMBER_OF_COLUMNS_TO_OMIT
if ($cnt = 0)
{ error(to few columns);
return;
}
$sql = select
for ($i=0;$i $cnt-1;$i++)
{ $sql = $sql + $ary[$i][Field] + ,
}
$sql = $sql + $ary[$cnt][Field]
$sql = $sql + FROM $NAME_OF_TABLE_TO_SELECT_FROM
$sql = $sql + WHERE $WHERE_CLAUSE
$sql = $sql + HAVING $HAVING_CLAUSE
$sql = $sql + GROUP BY $GROUP_BY_CLAUSE
$sql = $sql + ORDER BY $ORDER_BY_CLAUSE
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Foreign key question
--- Randy Clamons [EMAIL PROTECTED] wrote: Primary key names start with pk_, unique indexes start with uk_, other indexes start wiht ix_. That's a good tip. What if just named the primary key pk and the foreign key fk. Would you run into trouble if you're working with two or three tables, and each has a primary and foreign key named pk and fk? __ Do you Yahoo!? SBC Yahoo! - Internet access at a great low price. http://promo.yahoo.com/sbc/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: avoiding Locked threads
Jon, an update statement is supposed to return the # of rows updated, so the client must wait for a response, which means the client will wait if you low_priority your updates. But if they are insert delayed then the client gets a return immediately. This behavior limits the usefullness of low priority with updates. I did ask on the list if there were any plans for a delayed update, but I was told there aren't. Trevor Jon Drukman wrote: Dathan Vance Pattishall wrote: log-bin=/var/opt/mysql/db2-binlog skip-innodb log-error=/var/opt/mysql/db2-errlog This is on a separate drive? yes, the database is the only thing on the high speed RAID. everything else is on the other drive (also a RAID but only RAID0 with 2 drives). any ideas appreciated! Try setting low-priority-updates and delay-key-write=ALL i haven't tried this yet, but one of the other developers has objected that doing this will kill performance for people posting messages because their clients will hang waiting for the selects to finish. is this true? Your running into a concurrency issue, the only other quick fix is to use innodb, but your blobs will kill you in disk space. h we've got approx 60G free on the RAID so this may not be such a big problem. -jsd- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need Table Joins Example
I'm trying to learn how to join tables, but I'm doing
something wrong. I've found lots of examples that look
easy, but something isn't clicking.
I think part of the confusion stems from the dynamic
tables I was creating with Dreamweaver. I thought they
were a necessary part of the equation, when they may
in fact be optional.
At any rate, I wondered if anyone on this list would
be willing to translate the code I've appended into
two joins that I can use as working examples.
Let's say we're working with two tables, named
continents and nations, each with four columns, as
follows:
TABLE CONTINENTS
Name
Type
Group
CCode
TABLE NATIONS
Name
Type
NCode
CCode
And here are the two types of joined tables I'd like
to learn how to display:
1. A table displaying all four columns from Table
Nations, plus a 5th column - Table Continent's Group
column.
2. A table displaying both tables side by side - 8
columns, beginning with Table Continent's Name column
and ending with Table Nation's CCode column.
To illustrate my database connection, I copied the
code from my page, but stripped out almost everything,
including the dynamic table:
?php require_once('../../../Connections/World.php');
?
?php
mysql_select_db($database_World, $World);
$query_Nations = SELECT * FROM nations;
!DOCTYPE html PUBLIC -//W3C//DTD XHTML 1.0
Transitional//EN
http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd;
html xmlns=http://www.w3.org/1999/xhtml;
xml:lang=en lang=en
head
/head
body
/body
/html
If I can get SOME kind of join working on one of my
pages, then I ought to be able to figure out some
other varieties.
Thanks!
__
Do you Yahoo!?
SBC Yahoo! - Internet access at a great low price.
http://promo.yahoo.com/sbc/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
