Re: auto_increment without primary key in innodb?
yah, mysql only allows one auto increment field n that's used as the primary key in tables. I don't think it has to be the primary key as long as it is a unique key i think that's okay. so u should be able to do : create table (myid int unsigned not null auto_increment., unique key (myid)); but this is effectively a primary key if u want some auto incrementing behavior but have it do so only on certain scenarios and possibly hold null values, you can write an insert trigger that would update the field on every insert. Yong. On Mon, 2010-01-25 at 10:21 -0500, Yang Zhang wrote: In innodb, is it possible to have an auto_increment field without making it a (part of a) primary key? Why is this a requirement? I'm getting the following error. Thanks in advance. ERROR 1075 (42000): Incorrect table definition; there can be only one auto column and it must be defined as a key -- Yang Zhang http://www.mit.edu/~y_z/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
performance question
All, Just curious as to which query would be better in terms of performance: select * from (select * from a union select * from b) as c; versus select * from a union select * from b; or would these 2 queries be the same ? Thanks, Yong. Yong Lee Developer [EMAIL PROTECTED] http://www.eqo.com/ direct: +1.604.273.8173 x113 mobile:+1.604.418.4470 fax: +1.604.273.8172 web:www.EQO.com http://www.eqo.com/ EQO ID: yonglee
RE: Match/No Match query
chris, you're going to need a source for all the distinct codes that you may see in your product tableie: if you don't have a full list somewhere (like your temp table) you cannot do your query because you have nothing to compare against. assuming you had some table X which had a list of all the possible codes and you could generate the complete list by doing something like : select distinct code from X; you could use this in your sql statement like : select p.prod_num, p.code from products p left join (select distinct code from X) as codes on p.code = codes.code; Yong. -Original Message- From: Chris W [mailto:[EMAIL PROTECTED] Sent: May 21, 2008 10:14 AM To: Jerry Schwartz; MYSQL General List Subject: Re: Match/No Match query Jerry Schwartz wrote: From: Chris W [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 21, 2008 12:25 PM To: Jerry Schwartz Cc: mysql@lists.mysql.com Subject: Re: Match/No Match query Jerry Schwartz wrote: I have a list of codes. Some, but not all, of these codes will match the entries in a product table. Here's what the data would look like: List of Codes: The rows in the product table look like prod_num code 222 333 What I want to is get a list of ALL of the codes, with the associated prod_num if it exists or a flag if it does not: code prod_num 222 xxx 333 I need to preserve the empty rows in order to match the data against an Excel worksheet (which is where the list of codes came from). I have done this by putting all of the codes into a temporary table and doing a LEFT JOIN against the product table. Works fine, lasts a long time. However, it seems that I ought to be able to do this without the temporary table, by using derived tables. I just can't figure out how. This would be easier if you gave your table structure. But something like this would work SELECT c.code, p.prod_num FROM CodeTable c LEFT OUTER JOIN ProductNumTable p USING (code) [JS] That is what I am doing now. I was wondering if I could eliminate what you have designated as CodeTable, and do this all in a single (probably nested) query. In that case I must be missing something. What data do you have in the database that can be used to create the result. Some table structure would help and some more sample data that you want to use. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; Ham Radio Repeater Database. http://hrrdb.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: Replication for reporting
yes, one master to multiple slaves. I believe u can set up multiple mysql instances on a single machine with each mysql instance collecting from a different master. Yong. -Original Message- From: Andrey Dmitriev [mailto:[EMAIL PROTECTED] Sent: May 21, 2008 11:29 AM To: mysql@lists.mysql.com Subject: Replication for reporting Is it true that a single mysql server can be a slave to only one master? So if you need to replicate a dozen databases into a single reporting server, you need to have a chain of a dozen servers in between? Someone shared that to me, but it didn't make much sense. In oracle we have the following options to replicate: Snapshots (also known as materialized views) for individual tables Standby (similar to mysql, but replicates the entire db) Streams (pick whatever you need out of sql stream) So for reporting purposes from multiple db's, you'd mostly likely pick snapsohts or streams. Thanks, andrey -- 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]
List queries
Hi all, Is there a problem with the mysql web page (http://lists.mysql.com/) where you can query mailing list archives ? I can't seem to get more than 20 results to any query. Thanks, Yong. Yong Lee Developer [EMAIL PROTECTED] http://www.eqo.com/ direct: +1.604.273.8173 x113 mobile:+1.604.418.4470 fax: +1.604.273.8172 web:www.EQO.com http://www.eqo.com/ EQO ID: yonglee
log table advice
Hi all, Just wondering how people are dealing with tables that are used for logging, ie: insert only tables supporting occasional queries used for audit or event logs. These tables will keep growing and there is no need to keep them that large so what is the best strategy in managing the data in them. I was thinking of going with MyIsam tables because I don't need transactions n the table data is self contained and portable. I would change my application to insert into tables which are named with a datestamp component (ie: have the apps determine the name of the table based on current time before doing an insert) and then have a cron job to create new tables as needed and to also backup and remove older tables as they are no longer being used. Any thoughts on this ? Thanks, Yong. Yong Lee Developer [EMAIL PROTECTED] http://www.eqo.com/ direct: +1.604.273.8173 x113 mobile:+1.604.418.4470 fax: +1.604.273.8172 web:www.EQO.com http://www.eqo.com/ EQO ID: yonglee
rhel4 mysql max rpm question
Hi All, I noticed that there are RPM downloads for RHEL4 but that these do not contain the ndb storage engine (ie: these are standard builds). As such, I've resolved to just use the generic x86 rpm bundles that are offered but I'm curious if the ndb storage engine will ever get included into a RHEL4 RPM bundle? Thanks, Yong.
utf8 charset question
Hi all, I'm fairly new to character sets and I'm trying to get a better understanding of how mysql deals with them. I hope someone out there can shed some light on a behavior that I am seeing. We're using mysql 4.1.12 with clustered tables. I have a table with a varchar and a text field where the table has been defined to use : ENGINE=ndbcluster DEFAULT CHARSET=latin1 Now, through my php user interface, I find that I can update/insert Korean characters into the database table and successfully retrieve them from the database. I'm using microsoft's Korean input system (ms-ime2002) So, I'm wondering how can this be? If the charset is using latin1, then I would expect that I am using 1 byte for storage but the Korean character sets would need 2. - am I just getting lucky and not using the characters that require 2 bytes ? - is there something special about clustered tables in that it treats all char data as utf8 charsets ? - is my test invalid in that the Microsoft encoding for the Korean characters only needs 1 byte ? Thanks for any replies, Yong.
udf configuration
Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong.
udf configuration resources
Hi all, I hope I have hit the right group for this question. I would like to create a UDF that is configurable at run time. Similar to how mysql can use variables defined in the /etc/my.cnf file, I'd like my UDF to make use of configuration parameters that can be set at run time. I'm wondering if this is possible and what strategy to take to implement this, ie: a mechanism to have mysql or the UDF read something once and then have the UDF able to refer back to it everytime it is run. I know I could read a file in the _init function, but this seems very wasteful to read a file every time the function is being used. Any thoughts on this would be appreciated. thanks, Yong.
