Implementing HA w/o clustering
hi, how can i spread reads on a non-cluster config? what i have in mind is a master (with a passive box beside) and 2 replicated servers.. unfortunately i can only spread reads and not writes... thanks, DISCLAIMER: This Message may contain confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you received this message in error please notify your Mail Administrator and delete this message immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of GMA New Media, Inc. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How to delete all rows....
Hi, Its delete * from table will only do if you go for a truncate it will recreate the table structure ? It's better to use delete. Thanks Regards Dilipkumar - Original Message - From: Peter Lauri [EMAIL PROTECTED] To: [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Wednesday, September 20, 2006 1:25 PM Subject: RE: How to delete all rows DELETE FROM table -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, September 20, 2006 10:35 AM To: mysql@lists.mysql.com Subject: How to delete all rows Hi All, How do I delete all the rows of all the tables(but not table) in the database at one shot. Regards, Ravi K The information contained in this electronic message and any attachments to this message are intended for the exclusive use of the addressee(s) and may contain proprietary, confidential or privileged information. If you are not the intended recipient, you should not disseminate, distribute or copy this e-mail. Please notify the sender immediately and destroy all copies of this message and any attachments. WARNING: Computer viruses can be transmitted via email. The recipient should check this email and any attachments for the presence of viruses. The company accepts no liability for any damage caused by any virus transmitted by this email. www.wipro.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] ** DISCLAIMER ** Information contained and transmitted by this E-MAIL is proprietary to Sify Limited and is intended for use only by the individual or entity to which it is addressed, and may contain information that is privileged, confidential or exempt from disclosure under applicable law. If this is a forwarded message, the content of this E-MAIL may not have been sent with the authority of the Company. If you are not the intended recipient, an agent of the intended recipient or a person responsible for delivering the information to the named recipient, you are notified that any use, distribution, transmission, printing, copying or dissemination of this information in any way or in any manner is strictly prohibited. If you have received this communication in error, please delete this mail notify us immediately at [EMAIL PROTECTED] Watch the latest updates on Mumbai, with video coverage of news, events, Bollywood, live darshan from Siddhivinayak temple and more, only on www.mumbailive.in Watch the hottest videos from Bollywood, Fashion, News and more only on www.sifymax.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: What mysql 5.0 binary relase use for CentOS 4.4 Pentium?
Finally I have an erroneous /etc/hosts file. But aside from that what of the realeses is best suited for a CentOS 4.4 Double Pentium III 1.0 Ghz? * Linux (x86, glibc-2.2, standard is static, gcc): mysql-standard-5.0.24a-linux-i686.tar.gz * Linux (x86): mysql-standard-5.0.24a-linux-i686-glibc23.tar.gz The first is compiled statically with glibc-2.2 and the second is compiled dinamically against glibc-2.3. CentOS 4.4 uses glibc-2.3. Then which is the best suited? For update is more easier using rpm relases but If I want to downgrade to the previous version I only have to change the symbolic link mysql in /usr/local/mysql to the directory of the previous version if I use binary instead of rpm releases. That works because I use another director for data thant /usr/local/mysql/data. Could I do these with rpm? -Mensaje original- De: Chris [mailto:[EMAIL PROTECTED] Enviado el: viernes, 22 de septiembre de 2006 2:19 Para: [EMAIL PROTECTED] CC: MySql Mail List Asunto: Re: What mysql 5.0 binary relase use for CentOS 4.4 Pentium? [EMAIL PROTECTED] wrote: Hi. What binary relase use for CentOS 4.4: * Linux (x86, glibc-2.2, standard is static, gcc): mysql-standard-5.0.24a-linux-i686.tar.gz * Linux (x86): mysql-standard-5.0.24a-linux-i686-glibc23.tar.gz In http://dev.mysql.com/doc/refman/5.0/en/binary-notes-linux.html says that it is possible that mysql couldn't resolves ips for hostnames. First I install mysql-standard-5.0.24a-linux-i686-glibc23.tar.gz and executing mysql_install_db it says: Sorry, the host '' could not be looked up If you read that page: You can deal with this by executing mysql_install_db --force, which does not execute the resolveip test in mysql_install_db. The downside is that you cannot use hostnames in the grant tables: except for localhost, you must use IP numbers instead. If you are using an old version of MySQL that does not support --force, you must manually remove the resolveip test in mysql_install using a text editor. Basically your DNS is stuffed and mysql can't look up hostnames. Why aren't you using the rpms? They are much easier to deal with when it comes time to upgrade. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Implementing HA w/o clustering
On Friday 22 September 2006 18:36, JM wrote: sorry.. what i have in mind is the writes.. master | |_ _ _ _ replicated 1 (read) | |_ _ _ _ replicated 2 (read) thanks, hi, how can i spread reads on a non-cluster config? what i have in mind is a master (with a passive box beside) and 2 replicated servers.. unfortunately i can only spread reads and not writes... thanks, Mailing-List -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Implementing HA w/o clustering
I have done this, but only with some external programming. I was using Informix, not MySQL, but the principle would be the same. Essentially, I put a layer in between the client and the data base, and it wrote transactions into a FIFO for a slave on each server. The slaves, in turn, took care of all writes to the data bases. The reads were distributed simply by having the clients open different copies of the data base. Ugly, but it works. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: JM [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 6:37 AM To: mysql@lists.mysql.com Subject: Implementing HA w/o clustering hi, how can i spread reads on a non-cluster config? what i have in mind is a master (with a passive box beside) and 2 replicated servers.. unfortunately i can only spread reads and not writes... thanks, DISCLAIMER: This Message may contain confidential information intended only for the use of the addressee named above. If you are not the intended recipient of this message you are hereby notified that any use, dissemination, distribution or reproduction of this message is prohibited. If you received this message in error please notify your Mail Administrator and delete this message immediately. Any views expressed in this message are those of the individual sender and may not necessarily reflect the views of GMA New Media, Inc. -- 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]
Changed headers?
Dear MySQL-ers, After upgrading to MySQL 5.0.24a (from 4.0.26), I've been raking my brain over installing DBD-mysql-3.0007. Installed DBI-1.52, then DBD::mysql, all by the book. Troublesome installation. But that's not what I want to ask about. So, after installing DBI-1.52 (built against libmysqlclient.so.15), I tried to see if I could connect with Use DBI; anyway, with the old DBD drivers. And I can. So, my question is, how is this possible?? I thought headers had changed (and should have, significantly, between 4.0.26 and 5.0.24a). And that the BDB::mysql drivers abolutely need upgrading too. I ran pretty complex database stuff on it, and all seems to work just fine. Did I misunderstand the upgrade process? I mean, I don't mind that it works fine; I just don't understand why. :) Thanks, - Mark -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Option/Variable Reference for the MySQL Server
The MySQL Reference Manual has become huge (about 2000 pages), and this means it's becoming increasingly hard to find things. And often it's hard for the MySQL documentation team to decide where to put things, because no matter where we put them some people expect to find them in another place. We've discussed solutions to this dilemma, and decided that overview tables would address it best. We've made a start with an overview table that lists all available options, system variables, and server variables of the MySQL server v5: * http://dev.mysql.com/doc/refman/5.0/en/mysqld-option-tables.html * http://dev.mysql.com/doc/refman/5.1/en/mysqld-option-tables.html We'll expand that table in many ways: We'll add more columns (such as version information), we'll make parts of that table appear in other sections of the Manual (for example, there would be a table that contains replication-related options and variables in the Replication chapter), and last but not least we'll add detailed summary tables that provide more detailed information for each individual option or variable. All this is made possible because we have created a new set of reference material that provides extensive metadata about the different options, variables and relationships. Currently, we are only generating summary information from this data. If you're interested, take a look at the mysqldoc repository to see how this is done: * http://svn.mysql.com/svnpublic/mysqldoc/ The XML file that contains all metadata is mysqld-optvars/options.xml. We welcome updates, corrections and improvements ([EMAIL PROTECTED])! Regards, Stefan -- Stefan Hinz [EMAIL PROTECTED] MySQL AB Documentation Team Lead. Berlin, Germany (UTC +2:00) Skype:stefanhinz Cell:+491777841069 Desk:+493082702940 Fax:+493082702941 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: tree query
Did anyone have any thoughts? On Mon, 2006-09-18 at 00:02 -0400, THO wrote: In need of suggestions to get correct part multiplier factor. (apologies up front for message length) I have an parts/assemblies table. mysql describe assycard; +--+-+--+-+-+---+ | Field| Type| Null | Key | Default | Extra | +--+-+--+-+-+---+ | part_id | int(7) unsigned | NO | PRI | 0 | | | assy | int(7) | NO | PRI | 0 | | | qty_assy | int(7) unsigned | NO | | 0 | | | unit | char(10)| NO | | EA | | +--+-+--+-+-+---+ This table forms an assembly tree and parts list resource. qty_assy is the qty of each part (part_id) that belongs to an assembly (assy). Assembly number assy can also be a part_id and in that case is called a sub-assembly and has a qty_assy value for how many sub-assemblies belong to its parent assembly. I have a procedure that eventually, properly generates the tree... Thanks to info from http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html What I have been unable to get right is the qty_assy multipliers up the tree. truncated example; +-++-+ | part_id | assy | qty_assy| +-++-+ | 8100028 | 8100030| 8 | | 8100029 | 8100028| 6 | | 1000100 | 8100029| 1 | | 2100050 | 8100029| 4 | +-++-+ parts 1000100 and 2100050 belong to assembly 8100029 which is a child of 8100028, 8100028 is a child of the top assembly 810030. In this example there are 6 8100029's in 8100028 and 8 8100028's in 8100030 therefore every part in 8100029 should be multiplied by 48 to account for all parts from 8100029 included in 1 8100030 (48 1000100's and 192 2100050's). Parts in 8100028 should only be multiplied by 8 to account for parts in 8100028 included in 8100030. The actual depth of the tree is in principal unknown so I need to walk the tree and correctly collect the multipliers for each assembly. The heart of the assembly tree procedure that works, with some cleaning up of the result, is below but carrying the qty factors along the way did not work... DROP PROCEDURE IF EXISTS ListDescendants; DELIMITER | CREATE PROCEDURE scanner.ListDescendants( IN ancestor int(7) ) BEGIN DECLARE rows INT DEFAULT 0; -- CREATE NEEDED TABLES DROP TABLE IF EXISTS descendants; CREATE TABLE IF NOT EXISTS descendants ( d_part_id int(7), d_assy int(7), d_qty int(5) ) ENGINE=MEMORY; DROP TABLE IF EXISTS nextparents; CREATE TABLE IF NOT EXISTS nextparents ( assy int(7), np_qty_assy int(5) ) ENGINE=MEMORY; DROP TABLE IF EXISTS prevparents; CREATE TABLE prevparents LIKE nextparents; -- SEED NEXTPARENTS INSERT INTO nextparents SELECT assy, qty_assy FROM assycard WHERE assy=ancestor and qty_assy!=0; SET rows = ROW_COUNT(); WHILE rows 0 DO -- ADD CHILDREN OF NEXTPARENTS INSERT INTO descendants SELECT t.part_id, t.assy, t.qty_assy FROM assycard AS t INNER JOIN nextparents USING(assy) where t.part_id!=0; SET rows = ROW_COUNT(); -- SAVE COPY OF NEXTPARENTS TRUNCATE prevparents; INSERT INTO prevparents SELECT * FROM nextparents; -- NEXT PARENTS ARE CHILDREN OF THESE PARENTS: TRUNCATE nextparents; INSERT INTO nextparents SELECT part_id, qty_assy FROM assycard INNER JOIN prevparents USING (assy) where assycard.part_id!=0; SET rows = rows + ROW_COUNT(); END WHILE; ... cleanup of resulting descendants table, joining with part and cost information from other tables... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
MIT Kerberos integration with MySQL
Does anyone know if MySQL currently integrates with MIT Kerberos authentication (http://web.mit.edu/Kerberos/) http://web.mit.edu/Kerberos/ . And if not, are they working on this for a future release? MIT Kerberos has broad use in government, academic and research institutions as well as some corporate environments. In addition, Microsoft has implemented it's own version of Kerberos. And Oracle integrates with it, which enables us to have single-sign on for our Oracle Database applications once we have logged into our network, but we'd like to have this same function available for our MySQL databases as well.
Count Fields of a Table
Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count Fields of a Table
Hi David If you are using mysql 5.0 and up, you can select from the INFORMATION_SCHEMA database to get this information and much more. Following is an example using a database called test and a table called t To get the column names, use SELECT column_name FROM information_schema.columns WHERE table_schema='test' AND table_name='t'; mysql use test; Database changed mysql describe t; +-+--+--+-+-+---+ | Field | Type | Null | Key | Default | Extra | +-+--+--+-+-+---+ | TransactionDate | datetime | YES | | | | | amount | float| YES | | | | +-+--+--+-+-+---+ 2 rows in set (0.07 sec) mysql select * from information_schema.columns where table_schema='test' and table_name='t'; +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | TABLE_CATALOG | TABLE_SCHEMA | TABLE_NAME | COLUMN_NAME | ORDINAL_POSITION | COLUMN_DEFAULT | IS_NULLABLE | DATA_TYPE | CHARACTER_MAXIMUM_LENGTH | CHARACTER_OCTET_LENGTH | NUMERIC_PRECISION | NUMERIC_SCALE | CHARACTER_SET_NAME | COLLATION_NAME | COLUMN_TYPE | COLUMN_KEY | EXTRA | PRIVILEGES | COLUMN_COMMENT | +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ | | test | t | TransactionDate | 1|| YES | datetime | NULL | NULL | NULL | NULL ||| datetime || | select,insert,update,references || | | test | t | amount | 2|| YES | float | NULL | NULL | 12 | NULL ||| float || | select,insert,update,references || +---+--++- +--++-+--- +--+ +---+---+ ++-++--- +-++ 2 rows in set (0.01 sec) mysql select count(*) from information_schema.columns where table_schema='test' and table_name='t'; +--+ | count(*) | +--+ | 2| +--+ 1 row in set (0.32 sec) mysql select column_name from information_schema.columns where table_schema='test' and table_name='t'; +-+ | column_name | +-+ | TransactionDate | | amount | +-+ 2 rows in set (0.08 sec) Douglas Sims [EMAIL PROTECTED] On Sep 22, 2006, at 9:54 AM, davidvaz wrote: Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- 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: Count Fields of a Table
David, For the count of columns in a table: SELECT count(information_schema.columns.column_name) FROM information_schema.columns WHERE information_schema.columns.table_schema = 'database_name' ANDinformation_schema.columns.table_name = 'table_name' For the names of the columns in a table: SELECT information_schema.columns.column_name FROM information_schema.columns WHERE information_schema.columns.table_schema = 'database_name' ANDinformation_schema.columns.table_name = 'table_name' Hope this helps. Randall Price Microsoft Implementation Group Secure Enterprise Computing Initiatives Virginia Tech Information Technology 1700 Pratt Drive Blacksburg, VA 24060 Email: [EMAIL PROTECTED] Phone: (540) 231-4396 -Original Message- From: davidvaz [mailto:[EMAIL PROTECTED] Sent: Friday, September 22, 2006 10:54 AM To: mysql@lists.mysql.com Subject: Count Fields of a Table Hello, Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? Thanks David -- 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: Count Fields of a Table
[...] Is there any way to find out, using only plain SQL, the number of fields of a given table. describe gives me the number of fields as result, but I need to get only that. Is it possible? Is it also possible to get only the fields name? AFIK there's no easy way to accomplish this using just plain SQL. Every time you use DESCRIBE tbl_name you get the number of columns counted as rows. You can also use SHOW COLUMNS FROM tbl_name SHOW FIELDS FROM tbl_name And from the shell you can also type mysqlshow -u myuser -p db_name tbl_name -- Iván Alemán ~ [[ Debian (Sid) ]] ~ -BEGIN GEEK CODE BLOCK- Version: 3.12 G!GCM d+ s: a? C+++ UL++ P L$ E--- W+++ N* o--- K- w O- M+ V-- PS++ PE-- Y PGP+++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z**$ --END GEEK CODE BLOCK-- bonovoxmofo.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Count Fields of a Table
Hello, @Mr. Price and Mr. Sims Thank you for show me the way, your tips where very educational. Thanks again. -- Iván Alemán ~ [[ Debian (Sid) ]] ~ -BEGIN GEEK CODE BLOCK- Version: 3.12 G!GCM d+ s: a? C+++ UL++ P L$ E--- W+++ N* o--- K- w O- M+ V-- PS++ PE-- Y PGP+++ t-- 5 X R+ !tv b++ DI-- D+++ G+ e++ h* r+ z**$ --END GEEK CODE BLOCK-- bonovoxmofo.blogspot.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[OFF] PHP/MySQL contractor needed
We need a guy for some hourly PHP/MySQL work. Large project to start with, plenty of incremental stuff down the road. Looking for someone expert, with a flexible schedule who can make hours available when we need them. Your regular hourly rate. Also - only interested in someone local to south Orange County, CA. No outsourcers or non-locals can be considered, sorry. Please email me back-channel for details if interested. Thanks! - Brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MIT Kerberos integration with MySQL
In the last episode (Sep 22), Whisler, David said: Does anyone know if MySQL currently integrates with MIT Kerberos authentication (http://web.mit.edu/Kerberos/) http://web.mit.edu/Kerberos/ . And if not, are they working on this for a future release? MIT Kerberos has broad use in government, academic and research institutions as well as some corporate environments. In addition, Microsoft has implemented it's own version of Kerberos. And Oracle integrates with it, which enables us to have single-sign on for our Oracle Database applications once we have logged into our network, but we'd like to have this same function available for our MySQL databases as well. There's already a Permit authentification outside of mysql bug report filed: http://bugs.mysql.com/bug.php?id=4703 Must be pretty far down on the todo list, though.. -- 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: MIT Kerberos integration with MySQL
Hi! Please see the Forge wiki and provide comments at the end of the technical specification for pluggable authentication and authorization. Thanks! Jay http://forge.mysql.com/wiki/PluggableAuthenticationSupport http://forge.mysql.com/wiki/PluggableAuthorizationSupport On Friday 22 September 2006 12:45, Dan Nelson wrote: In the last episode (Sep 22), Whisler, David said: Does anyone know if MySQL currently integrates with MIT Kerberos authentication (http://web.mit.edu/Kerberos/) http://web.mit.edu/Kerberos/ . And if not, are they working on this for a future release? MIT Kerberos has broad use in government, academic and research institutions as well as some corporate environments. In addition, Microsoft has implemented it's own version of Kerberos. And Oracle integrates with it, which enables us to have single-sign on for our Oracle Database applications once we have logged into our network, but we'd like to have this same function available for our MySQL databases as well. There's already a Permit authentification outside of mysql bug report filed: http://bugs.mysql.com/bug.php?id=4703 Must be pretty far down on the todo list, though.. -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
auto_increment field start value
I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- 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: auto_increment field start value
Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
OK. If you assign to auto_increment any number higher than what currently exists in the column, it changes the value and the incremented sequence from that point. But apparently you can't assign the value zero to the column, even if the table is empty. Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1),but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Re: auto_increment field start value
I just tried it in 5.0.21, and found that it fails silently with zero (0). Works with 100. I did specify int, not unsigned int, in my test table. See http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html for some discussion about how you could get a zero in there; look for NO_AUTO_VALUE_ON_ZERO Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: Thanks, Dan, but I can't get it to work. Defining a column like this: a int not null auto_increment=0 primary key throws an error, and while the alter table statement seems to work ok, whether the table is empty or not, it has no effect on subsequent inserts. I'm wondering if 4.0.16 has not implemented this feature. David - there's some info in the online docs here: http://dev.mysql.com/doc/refman/5.0/en/example-auto-increment.html Specifically: To start with an AUTO_INCREMENT value other than 1, you can set that value with CREATE TABLE or ALTER TABLE, like this: mysql ALTER TABLE tbl AUTO_INCREMENT = 100; HTH, Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. I'm using 4.0.16 and table type=myisam. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
At 14:16 -0500 9/22/06, [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. Don't store 0 in an AUTO_INCREMENT column. -- 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]
Views with functions and performance issues
Hi, I've got a view of a base table that is 100% identical to that base table except for one column, which is a projection of the base table after its MD5 hashed. The table is largish (~700,000 rows) and is growing quickly. Queries on the base table are nice and fast, but on the hashed view are painfully slow - in particular when restricting the queries on the view based on the hashed colum. I assume this is because there is no index for the hashed colum. Is there a good way I can deal with this, without changing the base table? TIA, running MySQL 5 on windows, can change to a later build of MySQL (as long as I can replicate from 5) if there is some funky stuff in there that will do what I need... Chris -- Christopher A. Brooks Research Officer, ARIES Group University of Saskatchewan Email: [EMAIL PROTECTED] Mail: Christopher Brooks Department of Computer Science University of Saskatchewan 57 Campus Drive Saskatoon, Saskatchewan, S7N 5A9 Canada -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
finding row with latest date in a range from joined tables in v4.0.26
my ISP is using mysql v4.0.26 I am trying to do the following SQL and cannot get it to parse without an #1064 error. select r.prog_id, r.prog_name, p.show_id, p.show_title, p.show_desc, p.show_speaker, p.show_date_recorded, s.spk_name from programmes p, series r, speaker s where r.prog_id = p.show_prog_id and p.show_speaker = s.spk_id and r.prog_id 5 and p.show_date_recorded between '2006-08-25' and '2006-09-22' and p.show_date_recorded = (select max(show_date_recorded) from programmes q where q.show_id = p.show_id) Tables are joined as shown by first two where statements; r has 6 unique series, p has multiple programmes and s has multiple speakers I'm trying to pick out the latest programme in each series in a date range and include the series full name (prog_name) and speaker full name (spk_name) from the other tables. I've played around with joins but cannot seem to get round it, Any suggestions apart from ISP using different mySQL greatly appreciated! Rob - Email sent from www.ntlworld.com Virus-checked using McAfee(R) Software Visit www.ntlworld.com/security for more information -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: auto_increment field start value
At 14:16 -0500 9/22/06, [EMAIL PROTECTED] wrote: I seem to recall that when creating a table, you could designate an auto_increment field to begin counting at zero(0) instead of one (1), but I can't find an example in the documents. Don't store 0 in an AUTO_INCREMENT column. Thanks Dan and Paul, Since I can't store 0 in the column, I won't ;) For clarification, I'm writing a DICOM application and will store a complicated query result in table before further processing of the data. I guess I'll deal with the starts at 1, not 0 issue in the application. David -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Views with functions and performance issues
If I'm understanding right - the view contains an additional column that is an MD5 hash of some or all of the data in the base table, right? Yes, I would expect that to be very very slow. When selecting, your database engine has tro calculate 700K MD5 hashes. Slow. When selecting a subset it has to do that plus what amounts to a table scan of those 700K MD5 hashes. Very slow. Wonder if you could do this: set up second table, with two columns. One is id number to correspond to id number in original table, second is to hold Md5 hash. Createn index on the MD5 column for sure, possibly a two column index. I'm not sure whether it would be better to create it as (id, md5_col) or (md5_col, id); I'd test it each way I guess. Set up insert/update/delete triggers on the first table to add/update/delete records in the second table with ID and the MD5 hash. Then re-create your view to show you all the columns from the first table plus the MD5 column from the second table, joining on the id column. I think this should end up being pretty fast since the hashes will only be calculated when the data changes, not all at once for every select, and an index will speed things up tremendously when selecting a subset. HTH, Dan On 9/22/06, Christopher Brooks [EMAIL PROTECTED] wrote: Hi, I've got a view of a base table that is 100% identical to that base table except for one column, which is a projection of the base table after its MD5 hashed. The table is largish (~700,000 rows) and is growing quickly. Queries on the base table are nice and fast, but on the hashed view are painfully slow - in particular when restricting the queries on the view based on the hashed colum. I assume this is because there is no index for the hashed colum. Is there a good way I can deal with this, without changing the base table? TIA, running MySQL 5 on windows, can change to a later build of MySQL (as long as I can replicate from 5) if there is some funky stuff in there that will do what I need... Chris -- Christopher A. Brooks Research Officer, ARIES Group University of Saskatchewan Email: [EMAIL PROTECTED] Mail: Christopher Brooks Department of Computer Science University of Saskatchewan 57 Campus Drive Saskatoon, Saskatchewan, S7N 5A9 Canada -- 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: finding row with latest date in a range from joined tables in v4.0.26
Rob - sub-selects aren't supported prior to version 4.1. This page may be of some assistance: http://dev.mysql.com/doc/refman/4.1/en/rewriting-subqueries.html Have you tried using a MAX(p.show_date_recorded) and appropriate GROUP BY ? Dan On 9/22/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: my ISP is using mysql v4.0.26 I am trying to do the following SQL and cannot get it to parse without an #1064 error. select r.prog_id, r.prog_name, p.show_id, p.show_title, p.show_desc, p.show_speaker, p.show_date_recorded, s.spk_name from programmes p, series r, speaker s where r.prog_id = p.show_prog_id and p.show_speaker = s.spk_id and r.prog_id 5 and p.show_date_recorded between '2006-08-25' and '2006-09-22' and p.show_date_recorded = (select max(show_date_recorded) from programmes q where q.show_id = p.show_id) Tables are joined as shown by first two where statements; r has 6 unique series, p has multiple programmes and s has multiple speakers I'm trying to pick out the latest programme in each series in a date range and include the series full name (prog_name) and speaker full name (spk_name) from the other tables. I've played around with joins but cannot seem to get round it, Any suggestions apart from ISP using different mySQL greatly appreciated! Rob - Email sent from www.ntlworld.com Virus-checked using McAfee(R) Software Visit www.ntlworld.com/security for more information -- 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: [others] Re: Views with functions and performance issues
Hi, thanks for the comments, If I'm understanding right - the view contains an additional column that is an MD5 hash of some or all of the data in the base table, right? Close. It's got all of the data in the base table except for the colum that's being hashed - we show the hashed version of that instead. Like a password table, where the base table has the plain text passwords and usernames, and the view only shows the hashed passwords and usernames (though it's not for passwords). set up second table, with two columns. One is id number to correspond to id number in original table, second is to hold Md5 hash. Createn index on the MD5 column for sure, possibly a two column index. I'm not sure whether it would be better to create it as (id, md5_col) or (md5_col, id); I'd test it each way I guess. Set up insert/update/delete triggers on the first table to add/update/delete records in the second table with ID and the MD5 hash. Then re-create your view to show you all the columns from the first table plus the MD5 column from the second table, joining on the id column. This sounds good, and this way I don't have to change the first table much at all (as it's a replicated table and my luck with replication only lately seems to have gotten good). What kind of performance hit will I be taking because of the join in the view (e.g. every query to the view is going to have to have to do the join, yes?). Or is this neglegable as long as the hash and pointer of the second table are both indexed... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: RE: [others] Re: Views with functions and performance issues
The hit with a join on indexed columns is negligible. Relational databases live for joins - they eat them for breakfast! Seriously, as long as it's indexed in both tables, it'll be super-speedy. Dan On 9/22/06, Christopher Brooks [EMAIL PROTECTED] wrote: Hi, thanks for the comments, If I'm understanding right - the view contains an additional column that is an MD5 hash of some or all of the data in the base table, right? Close. It's got all of the data in the base table except for the colum that's being hashed - we show the hashed version of that instead. Like a password table, where the base table has the plain text passwords and usernames, and the view only shows the hashed passwords and usernames (though it's not for passwords). set up second table, with two columns. One is id number to correspond to id number in original table, second is to hold Md5 hash. Createn index on the MD5 column for sure, possibly a two column index. I'm not sure whether it would be better to create it as (id, md5_col) or (md5_col, id); I'd test it each way I guess. Set up insert/update/delete triggers on the first table to add/update/delete records in the second table with ID and the MD5 hash. Then re-create your view to show you all the columns from the first table plus the MD5 column from the second table, joining on the id column. This sounds good, and this way I don't have to change the first table much at all (as it's a replicated table and my luck with replication only lately seems to have gotten good). What kind of performance hit will I be taking because of the join in the view (e.g. every query to the view is going to have to have to do the join, yes?). Or is this neglegable as long as the hash and pointer of the second table are both indexed... Chris -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
