Select Primary Key
Hi folks. How can I select primary key from a table? I can't find any syntax out of show fields from tablename, and the 'key' field = PRI. Just seems to be a bit much. Any takers? Cheers Rich in Toronto (cold) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Update failing with error 1062
Good morning, I issue an update statement containing a where clause on the primary index, so as to update a single record. Howerver this is failing with Duplicate entry '6' for key 1 - update clients.calendarentry set Subject = 'presentation' where idCalendarEntry = 6; In the table, the field 'idCalendarEntry' is declared as : `idCalendarEntry` int(10) unsigned NOT NULL auto_increment The server version of MySql I am using is 5.0.24 The client version of MySql I am using is 5.0.11 Is anyone able to help? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Advantages in combining DB's ?
Hello All, I am running 4 different databases each has different functionality in Linux box. If i combine all four databases into one (moving the tables into anyone of the database), will it increase the performance? will it be give any advantages?. MySQL version i am using is 4.1.1 standard. Each database has the size of around 100M. Thanks, Prem
Fw: Update failing with error 1062
- Original Message - From: Devi [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Monday, February 12, 2007 5:34 PM Subject: Re: Update failing with error 1062 Hi MySQLeers, How can I setup multiple daemons, One daemon for one database? So that they can act independenly. What might be the pitfalls over here? In what situation one can opt for multiple daemons? What about maximum_connections. Is it for all the server instances? Thanks DEVI. G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update failing with error 1062
Hi MySQLeers, How can I setup multiple daemons, One daemon for one database? So that they can act independenly. What might be the pitfalls over here? In what situation one can opt for multiple daemons? What about maximum_connections. Is it for all the server instances? Thanks DEVI. G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a help in Query
Thank you very much Jorn. Your suggestion helped me.
need a select query
Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M.
NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this by using subquery or trigger,
but it's a little bit painful.
The simpler the better :)
Thanks.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a select query
select * from table1 order by field1 limit 10,1 Dusan balaraju mandala napsal(a): Hi All, I need a select query, with which i can reach to a particular row directly. I mean if a table have 100 rows inserted, we can use select * from table1 limit 10; with this query i will have 10 rows, but my requirement is only 10th row only should come as a result. If u have any solution for this please share with me. Thank you. regards, Bala Raju M. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EMPTY, like NOT NULL
Use the IF function or the the IFNULL function like this:
SELECT IF(fieldname='','Empty Value of Your Choice',fieldname) FROM tbl-name;
SELECT IFNULL(fieldname,'Default Null Value of Your Choice') FROM tbl-name;
If the fieldname column contains either Strings, Empty Strings or Nulls do this
SELECT IF(IFNULL(fieldname,'BLAHBLAHBLAH')='BLAHBLAHBLAH','Null Value of Your
Choice',
IF(fieldname='','Empty Value of Your Choice',fieldname)) FROM tbl-name
Give it a try, it's Worth a Shot !!!
- Original Message -
From: js [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, February 12, 2007 9:48:28 AM (GMT-0500) US/Eastern
Subject: NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this by using subquery or trigger,
but it's a little bit painful.
The simpler the better :)
Thanks.
--
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: NOT EMPTY, like NOT NULL
Have a look at the HAVING clause ...
- Original Message -
From: js [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Monday, February 12, 2007 2:48 PM
Subject: NOT EMPTY, like NOT NULL
Hi list,
A silly question.
Is it possible to prevent empty value('') from appearing in a field?
I can solve this by using subquery or trigger,
but it's a little bit painful.
The simpler the better :)
Thanks.
--
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]
Help with mysql triggers
Question. I have two tables donation_test, and max_donation. The max donation table is a summary table, used to keep the max donation for each contact_id, by listing its donation_id. We have decided to use database level triggers to keep the summary table (max_donation) table up to date. I have been looking at the looping of a cursor syntax but am a little lost. Would someone mind taking the time to show me how exactly the syntax would look. For example if i run an update on donation_id 126 and change the amount equal to then 2000.00 then update after trigger should search though all the donations made by NEW.contact_id, figure the highest amount, then insert that into the max_donation table, also, and here is the tricky part, if there are two or more donations with the same amount, they all must be inserted into the max_donation table. mysql select * from donation_test; +-+++ | donation_id | contact_id | donation_date|total_amount | +-++-+--+ | 114 | 1 | 2007-02-05 00:00:00 | 2000.00 | | 115 | 2 | 2007-02-05 00:00:00 | 2100.00 | | 118 | 3 | 2007-02-05 00:00:00 | 1052.00 | | 126 | 2 | 2007-02-07 00:00:00 | 2.00 | +-++-+--+ mysql select * from max_donations; ++-+ | contact_id | donation_id | ++-+ | 1 | 114 | | 3 | 118 | | 2 | 126 | ++-+ Thanks very much in advance Winn Johnston The fish are biting. Get more visitors on your site using Yahoo! Search Marketing. http://searchmarketing.yahoo.com/arp/sponsoredsearch_v2.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: need a select query
Thank you Guys, your answer helpful to me.
Re: NOT EMPTY, like NOT NULL
Thanks for reply and sorry for lack of information.
What I want to know is not how to query,
but how to prevent emtpy data from being inserted in tables.
Let's say I'd like to have a table that doesn't contains any NULL value.
I'd create the table like below.
##
mysql create table t (a char(10) not null);
create table t (a char(10) not null);
Query OK, 0 rows affected (0.07 sec)
mysql insert into t values(NULL);
insert into t values(NULL);
ERROR 1048 (23000): Column 'a' cannot be null
##
well, looks good,
but NOT NULL only prevent NULL, as the name implied.
##
mysql insert into t values('');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
select * from t;
+---+
| a |
+---+
| |
+---+
1 row in set (0.01 sec)
###
Is there any easy way to implement 'NOT EMPTY' constraint?
Thank you in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Anywhere to FTP latest mod_auth_mysql?
File downloads are enabled in IE but refuse to work. Does anyone know where I can FTP the latest version of mod_auth_mysql? Thanks, Jon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Anywhere to FTP latest mod_auth_mysql?
Jonathan Mangin wrote: File downloads are enabled in IE but refuse to work. Does anyone know where I can FTP the latest version of mod_auth_mysql While this particular apache module does deal with mysql, the question you are asking is more oriented to the module working with apache than help with understanding/adjusting the sql schema behind it. That said, your question would probably be best answered in the mailing list of that particular module, or an apache related mailing list. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: NOT EMPTY, like NOT NULL
Try this:
mysql insert into t (a) values (if(char_length(sbd), sbd, NULL));
Query OK, 1 row affected (0.47 sec)
mysql insert into t (a) values (if(char_length(), sbd, NULL));
ERROR 1048 (23000): Column 'a' cannot be null
Regards,
Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032
860.674.8796 / FAX: 860.674.8341
-Original Message-
From: js [mailto:[EMAIL PROTECTED]
Sent: Monday, February 12, 2007 11:09 AM
To: Rolando Edwards
Cc: mysql@lists.mysql.com
Subject: Re: NOT EMPTY, like NOT NULL
Thanks for reply and sorry for lack of information.
What I want to know is not how to query,
but how to prevent emtpy data from being inserted in tables.
Let's say I'd like to have a table that doesn't contains any
NULL value.
I'd create the table like below.
##
mysql create table t (a char(10) not null);
create table t (a char(10) not null);
Query OK, 0 rows affected (0.07 sec)
mysql insert into t values(NULL);
insert into t values(NULL);
ERROR 1048 (23000): Column 'a' cannot be null
##
well, looks good,
but NOT NULL only prevent NULL, as the name implied.
##
mysql insert into t values('');
Query OK, 1 row affected (0.00 sec)
mysql select * from t;
select * from t;
+---+
| a |
+---+
| |
+---+
1 row in set (0.01 sec)
###
Is there any easy way to implement 'NOT EMPTY' constraint?
Thank you in advance.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:
http://lists.mysql.com/[EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: NOT EMPTY, like NOT NULL
Hi Js, js wrote: Is there any easy way to implement 'NOT EMPTY' constraint? There currently is no support for CHECK Constraints in MySQL, at least to my knowing. So you'd have to go with a trigger. regards Nils -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Select Primary Key
Rich, How can I select primary key from a table? To retrieve PKs for db.tbl in MySQL 5 ... SELECT k.column_name FROM information_schema.table_constraints t JOIN information_schema.key_column_usage k USING(constraint_name,table_schema,table_name) WHERE t.constraint_type='PRIMARY KEY' AND t.table_schema='db' AND t.table_name='tbl'; Pre-5, parse the result of DESC tbl or SHOW KEYS FROM tbl. PB Rich wrote: Hi folks. How can I select primary key from a table? I can't find any syntax out of show fields from tablename, and the 'key' field = PRI. Just seems to be a bit much. Any takers? Cheers Rich in Toronto (cold) --MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] --No virus found in this incoming message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.37/682 - Release Date: 2/12/2007 -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.411 / Virus Database: 268.17.37/682 - Release Date: 2/12/2007 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Update failing with error 1062
Simon, send in the schema for the table in question, that should show something. The only condition I can think of off the top of my head which might do that is if you have another unique key in your structure and that is the one this error is complaining about. On 2/12/07, Simon Giddings [EMAIL PROTECTED] wrote: Good morning, I issue an update statement containing a where clause on the primary index, so as to update a single record. Howerver this is failing with Duplicate entry '6' for key 1 - update clients.calendarentry set Subject = 'presentation' where idCalendarEntry = 6; In the table, the field 'idCalendarEntry' is declared as : `idCalendarEntry` int(10) unsigned NOT NULL auto_increment The server version of MySql I am using is 5.0.24 The client version of MySql I am using is 5.0.11 Is anyone able to help? Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Merge tables and ON DUPLICATE KEY UPDATE
I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? Kevin -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: 2 ways replication
2 way replication, also referred to as dual master replication, has been available for quite some time. However implementation can be tricky. Look for the Dual master section in chapter 7 of the High Performance MySQL book: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html If you need more than 2 masters, then all bets are off.. Atle On Mon, 12 Feb 2007, Rilawich Ango wrote: Hi all, I know it is an old question and I have read from the mysql website about the topic. Until now, mysql still doesn't support 2 ways replication, quoted from mysql website. As I have multiple location and each location will have a DB. Most of all need to read and write to the database. In my case, 2 ways replication is the most direct way to do it. Any other solution is suitable for me to implement if 2 ways replication is not a good way? Anyone has successfully implement 2 ways replication? Any suggestion? 1PC-read/update-DB(a) --- 2 ways replication -- DB(b)-read/update-PC2 ango -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables and ON DUPLICATE KEY UPDATE
Kevin Burton wrote: I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? What about using partitioning in MySQl 5.1+? Would this work? http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Cheers, Jay -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Merge tables and ON DUPLICATE KEY UPDATE
I thought about it I was actually going to use merge tables AND partitions to split the underlying MyISAM tables across two disks and then partition on top. It's POSSIBLE to use partitioning the way I want it but I'm going to have to grok it for a bit more. Thanks though. Kevin On 2/12/07, Jay Pipes [EMAIL PROTECTED] wrote: Kevin Burton wrote: I want to use a merge table so that I can direct all new INSERTs to a new merge table and migrate old data off the system by having a continually sliding window of underlying MyISAM tables. The problem is that of I do INSERT ... ON DUPLCATE KEY UPDATE and that value isn't in the leading table where all INSERTs go a *new* row will be created. Is there any way around this problem? What about using partitioning in MySQl 5.1+? Would this work? http://dev.mysql.com/doc/refman/5.1/en/partitioning.html Cheers, Jay -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
BUG? SHOW SLAVE STATUS blocks on disk full?
Hm. Running on 4.1.21 seems to have a 'feature' where SHOW SLAVE STATUS blocks when the disk is full. Thoughts? Kevin -- Founder/CEO Tailrank.com Location: San Francisco, CA AIM/YIM: sfburtonator Skype: burtonator Blog: feedblog.org Cell: 415-637-8078 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Setting up multiple daemons
Hi MySQLeers, How can I setup multiple daemons, One daemon for one database? So that they can act independenly. What might be the pitfalls over here? In what situation one can opt for multiple daemons? What about maximum_connections. Is it for all the server instances? Thanks DEVI. G -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Binlogfile change its name
Hi, I have also found that the master bin log file changing its name from myhostname-bin. to mysql.XXX and to other different names. I am running mysql Ver 11.12 Distrib 3.23.33, for hp-hpux11.00 (hppa2.0w). When I did sar -v 5 I found that inod-sz is on maximum: 14:39:05 text-sz ov proc-sz ov inod-sz ov file-sz ov 14:39:10 N/A N/A 185/2048 0 2248/2248 0 7789/10010 0 Is there any relation between inod size and the changing of the master bin log file? Any help would be appreciated. Regards, Tedy Aulia -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
