Re: Deadlocks with High Concurrency SELECT FOR UPDATE
Hi William, William Newton wrote: Hello List, I have this table that has a single row in it: CREATE TABLE `quicktable` ( `x` int(11) NOT NULL auto_increment, `quick_id` int(11) NOT NULL default '0', PRIMARY KEY (`x`) ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=latin1 select * from quicktable; +---+--+ | x | quick_id | +---+--+ | 1 |0 | +---+--+ 1 row in set (0.00 sec) I have a large number of connections executing these queries: BEGIN: SELECT quick_id FROM quicktable FOR UPDATE; COMMIT; This works well until I hit a large number of concurrent connections (around 200), when I start getting deadlocks. Despite the fact, that I'm only selecting a single table. Here is the deadlock section from SHOW INNODB STATUS; LATEST DETECTED DEADLOCK 071015 20:22:35 *** (1) TRANSACTION: TRANSACTION 0 79790779, ACTIVE 2 sec, process no 7658, OS thread id 1185077584 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 368 MySQL thread id 3961, query id 2102790 10.1.10.122 bnewton statistics SELECT x, quick_id FROM quicktable WHERE x=1 FOR UPDATE *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790779 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) TRANSACTION: TRANSACTION 0 79790775, ACTIVE 2 sec, process no 7658, OS thread id 1191733584 2 lock struct(s), heap size 368 MySQL thread id 4094, query id 2102743 10.1.10.122 bnewton *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79790775 lock_mode X locks rec but not gap Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 540674 n bits 72 index `PRIMARY` of table `test/quicktable` trx id 0 79791014 lock_mode X locks rec but not gap waiting Record lock, heap no 2 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8001; asc ;; 1: len 6; hex 04c14f74; asc Ot;; 2: len 7; hex 098005054d; asc M;; 3: len 4; hex 8018f9fd; asc ;; TOO DEEP OR LONG SEARCH IN THE LOCK TABLE WAITS-FOR GRAPH *** WE ROLL BACK TRANSACTION (2) Can anyone explain whats going on? Is there a limit for the number of concurrent transactions, before looking at the lock graph becomes too expensive? Is that documented somewhere? It's not documented, but yes there's both a maximum number of steps to check for a cycle in the waits-for graph, and a maximum depth of checking it: http://dev.mysql.com/sources/doxygen/mysql-5.1/lock0lock_8c-source.html#l00052 00046 /* Restricts the length of search we will do in the waits-for 00047 graph of transactions */ 00048 #define LOCK_MAX_N_STEPS_IN_DEADLOCK_CHECK 100 00049 00050 /* Restricts the recursion depth of the search we will do in the waits-for 00051 graph of transactions */ 00052 #define LOCK_MAX_DEPTH_IN_DEADLOCK_CHECK 200 This is the price you pay for instant deadlock detection, as opposed to just lock wait timeout (the method a lot of other transactional systems take). You could recompile with a higher number if you want. It would be nice if this were configurable; you could submit a feature request for that. Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
The value of NULL in Uniqued Columns
This is more of a philosophical issue than anything, but it has jumped up to bite us so I thought I'd make others aware; Since NULL has no value they can be entered multiply times into unique columns. Some will say that NULL is a value and therefore should be unique in this case (only one NULL allowed) and others will say that since NULL has no intrinsic value it can be entered into a unique column as many times as you would like. We have found this behavior in multiple database types (MS-SQL, Oracle) so it is not unique to MySQL, it is just where we noticed it. It is not one of those things that we thought aboutuntil now! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
how ti put several records of one mysql table in one row of html table?
hi,
I have standard organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-++-++++
| organization_id | address_id | full_name | phone | fax|
website|
+-++-++++
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com |
+-++-++++
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*** 1. row ***
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*** 2. row ***
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*** 3. row ***
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual Member
*** 4. row ***
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-+--+--+
| organization_id | field_id | cust_field_value |
+-+--+--+
|8200 | 12 | 2005-04-01 |
|8200 | 14 | Small Member |
|8200 | 16 | 1-4 |
|8200 | 21 | Retail |
+-+--+--+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-++-+++++-+-+
| organization_id | address_id | full_name | phone | fax|
website| start date | cancel date | membership type |
+-++-+++++-+-+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 |Smal Member|
+-++-+++++-+-+
thanks for any help.
-afan
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
ORDER BY but disregard stop words
Is there any way to use ORDER BY in such a way as to have it ignore words such as the, a, an, and the like? brian -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: how ti put several records of one mysql table in one row of html table?
Hi,
I think GROUP_CONCAT will do it.
-Raj.
-Original Message-
From: afan pasalic [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 16, 2007 11:49 AM
To: mysql@lists.mysql.com
Subject: how ti put several records of one mysql table in one row of
html table?
hi,
I have standard organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-++-+++
+
| organization_id | address_id | full_name | phone | fax|
website|
+-++-+++
+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com |
+-++-+++
+
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*** 1. row ***
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*** 2. row ***
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*** 3. row ***
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual
Member
*** 4. row ***
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-+--+--+
| organization_id | field_id | cust_field_value |
+-+--+--+
|8200 | 12 | 2005-04-01 |
|8200 | 14 | Small Member |
|8200 | 16 | 1-4 |
|8200 | 21 | Retail |
+-+--+--+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-++-+++
++-+
-+
| organization_id | address_id | full_name | phone | fax|
website| start date | cancel date | membership type
|
+-++-+++
++-+
-+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 |Smal Member|
+-++-+++
++-+
-+
thanks for any help.
-afan
--
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: ORDER BY but disregard stop words
[snip] Is there any way to use ORDER BY in such a way as to have it ignore words such as the, a, an, and the like? [/snip] I haven't tested this but you might be able to do it with a little REGEX and a HAVING clause; SELECT REGEX(words) AS undesirable FROM table HAVING stuff undesirable -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: how ti put several records of one mysql table in one row of html table?
Yup! That's THE ONE!
Thanks Rajesh.
-afan
Rajesh Mehrotra wrote:
Hi,
I think GROUP_CONCAT will do it.
-Raj.
-Original Message-
From: afan pasalic [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 16, 2007 11:49 AM
To: mysql@lists.mysql.com
Subject: how ti put several records of one mysql table in one row of
html table?
hi,
I have standard organizations table with org_id, name, address,
city,... columns.
CREATE TABLE `organization` (
`organization_id` int(8) unsigned NOT NULL default '0',
`address_id` int(8) unsigned default NULL,
`full_name` varchar(255) default NULL,
`phone` varchar(255) NOT NULL default '',
`fax` varchar(10) default NULL,
`parent_org_id` int(8) default NULL,
`website` varchar(45) default NULL,
`country` varchar(45) default NULL,
PRIMARY KEY (`organization_id`)
) ENGINE=MyISAM
+-++-+++
+
| organization_id | address_id | full_name | phone | fax|
website|
+-++-+++
+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com |
+-++-+++
+
I have also custom_fields table
CREATE TABLE `custom_fields` (
`field_id` int(4) NOT NULL,
`field_display` varchar(100) character set latin1 NOT NULL,
`field_type` enum('text','date') character set latin1 NOT NULL default
'text',
`field_order` int(3) unsigned default NULL,
`choices` text character set latin1,
PRIMARY KEY (`field_id`)
) ENGINE=MyISAM
*** 1. row ***
field_id: 12
field_display: Start Date
field_type: date
field_order: 2
choices:
*** 2. row ***
field_id: 13
field_display: Cancel Date
field_type: date
field_order: 4
choices:
*** 3. row ***
field_id: 14
field_display: Membership Type
field_type: text
field_order: 6
choices: Large Member,Small Member,Associate Member,Individual
Member
*** 4. row ***
field_id: 15
field_display: Referred By
field_type: text
field_order: 8
choices:
and custom field values table
CREATE TABLE `custom_field_values` (
`organization_id` int(8) NOT NULL,
`field_id` int(4) NOT NULL,
`cust_field_value` varchar(255) default NULL,
PRIMARY KEY (`organization_id`,`field_id`)
) ENGINE=MyISAM
mysql select organization_id, field_id, cust_field_value from
dir_custom_field_values where instance_id=12 and organization_id=8200;
+-+--+--+
| organization_id | field_id | cust_field_value |
+-+--+--+
|8200 | 12 | 2005-04-01 |
|8200 | 14 | Small Member |
|8200 | 16 | 1-4 |
|8200 | 21 | Retail |
+-+--+--+
I have to make a list (on screen, as html table) of organizations with
custom fields as a part of the table, e.g.
Org. ID | Org. Name | Org. Address | ... | cust_field_1 | cust_field_2 |
cust_field_3 | ...
but I can't make a query to put several records from custom_field_values
for specific org_id in one row?
Example:
+-++-+++
++-+
-+
| organization_id | address_id | full_name | phone | fax|
website| start date | cancel date | membership type
|
+-++-+++
++-+
-+
|8200 | 1 | 1520 | 212245 | 2122457730 |
http://www.abcinc.com | 2005-04-01 | 2006-01-01 |Smal Member|
+-++-+++
++-+
-+
thanks for any help.
-afan
--
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]
Replication - urgent
Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks regards, Ratheesh
Re: Replication - urgent
Running this statement on the master should do it: DROP TABLE table1, table2...; That will remove the tables. Baron Ratheesh K J wrote: Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks regards, Ratheesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Join to find Duplicates
I need to find records in a table that may be duplicate records. The table stores basic information about the Users. I want to do a match on the FName and LName fields. The query I have looks like this SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS `Count` FROM user u1 JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName GROUP BY u1.UserID HAVING Count 1 ORDER BY u1.LName, u1.FName This works fine. However, I would like to help determine which of the duplicates should be removed buy getting data from another table. That is were I get lost. The second table has User Profile records for each user. It is a one to many relationship so each user can have 0 or more profile records. What I would like to add to this query is a count of how many profile records each UserID has in the User profile table. That way if there are two users with the same name and one has 5 profile records and the second has no profile records it is clear which to remove. What I have come up with is SELECT u1.UserID, u1.FName, u1.LName, u1.Email, COUNT(u1.Email) AS `Count`, COUNT(p.UserID) as ProfileCount FROM user u1 JOIN user u2 ON u1.FName = u2.FName AND u1.LName = u2.LName LEFT OUTER JOIN userprofile p ON u1.UserID = p.UserID GROUP BY u1.UserID HAVING `Count` 1 ORDER BY u1.LName, u1.FName However that returns rows that are not duplicate names and I'm not sure why. -- Chris W KE5GIX Protect your digital freedom and privacy, eliminate DRM, learn more at http://www.defectivebydesign.org/what_is_drm; -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
