Re: Error:1067 could not start mysql server
Hi Venu, If you are having problem in running mysqldump, you can copy the data directory and place it in another mysql servers data directory. The data dir of the particular database is stored in the same name of the database. After moving the data dir, restart the mysql dameon. Now you can see the copied database in the new mysql server. If you are using Unix , by default, data dir will be in /usr/local/ mysql_ - - -/ data If it is linux , it will be in /var/lib Thanks, ViSolve DB Team - Original Message - From: Visolve DB Team [EMAIL PROTECTED] To: VenuGopal Papasani [EMAIL PROTECTED]; mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:32 AM Subject: Re: Error:1067 could not start mysql server Hi, On which platform? Have you checked with *.err file? Thanks ViSolve DB Team. - Original Message - From: VenuGopal Papasani [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Saturday, November 11, 2006 7:36 PM Subject: Error:1067 could not start mysql server Dear all, I am getting the following error when i m trying to start mysql server Error:1067:process terminated unexpectedly. I have tried stopping many services and start mysql but not successful.But i need to get my data backup.Is there any alternative to mysqldump command where i can get the backup.Please give me the solution asap thanks and regards, venu -- 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.409 / Virus Database: 268.14.3/531 - Release Date: 11/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Besoin d'aide urgent
Em Sat, 11 Nov 2006 16:03:41 +0100, Yannick Landry ANTONIO escreveu: Je viens au pres de vous solliciter une aide en ce qui concerne le demarrage du serveur mysql. Eſt-ce qu’il n’y a pas de liſte francophone ? J'ai installe sur ma machine la version mysql suivant: mysql-4.0.20a-win Cette verſion eſt trop vielle, ſurtout ſur MS Windows il faudrait attendre des problèmes. Quelle verſion de MS Windows utilisez vous ? Il le faudrai au moins MS WNT, pas 3.11, 9[58] ou ME. Je constate qu'il n'y a pas un racourcie pour la source graphique. J'ai tente de demarrer le server en ligne de commande en me mettant sur: C:\mysql\bin puis en entrant la commande suivante: mysqld-nt.exe mais celui ci ne se lance pas. Existe t'il une autre maniere d'aborder la situation??? Il doive être un ſervice, pas lancé à la ligne de commande. Je vous remercie d'avance pour avoir une solution a ce probleme et j'aimerai savoir s'il existe un mode graphique pour cette version de mysql???. Pluſieurs, mais ça n’est pas ton problème maintenant. Ah, une note: eßayez PostgreSQL, c’eſt un ſyſtème plus ſolide. -- Leandro Guimarães Faria Corcete DUTRA +55 (11) 9406 7191 (cel) Administrador de (Bases de) Dados +55 (11) 2122 0302 (com) http://br.geocities.com./lgcdutra/+55 (11) 5685 2219 (res) BRASILmailto:[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
How do I do this query efficiently?
Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: www.innodb.com
Curtis, the reason why innodb.com was unreachable for some time on Friday was that Oracle web administrators moved the DNS records to an Oracle domain server. The registrar of innodb.com is Tucows, and I believe the admins made some error which caused Tucows to set renewyourname.net as the domain server, which made innodb.com to show as a Google search page. I am sorry for the inconvenience this caused for InnoDB users. Concerning the MySQL AB - Innobase Oy OEM contract, it was renewed in spring 2006. Best regards, Heikki Oracle Corp./Innobase Oy InnoDB - transactions, row level locking, and foreign keys for MySQL InnoDB Hot Backup - a hot backup tool for InnoDB which also backs up MyISAM tables http://www.innodb.com/order.php . My guess is that its intentional. Oracle is who they are and MySQL is eating their lunch. I look for them to kill the product to try to drive MySQL out of business or make life difficult for them; hence the reason they're working on a new storage engine of their own. They tried to buy MySQL, but when they couldn't they bought up the two pieces of software that gave MySQL ACID transactions (innodb and Berkely (sp?)). They will renegotiate the contract, but make it very expensive for MySQL to license. You don't honestly think Oracle is going to be honest about this do you? They have absolutely no interest in helping MySQL survive. Sounds pretty fishy to me no matter what they're saying publicly. Curtis Bill MacAllister wrote: --On Friday, November 10, 2006 08:46:50 AM -0500 Curtis Maurand [EMAIL PROTECTED] wrote: http://www.oracle.com/innodb/index.html Please quit telling us that Oracle purchased Innodb. That is ancient news. The innodb.com web site had innodb content on it in the past, Heikki signature makes me think that it still should, and it doesn't now. That is the issue that I raised. I am guessing, given Reimer's insight, that the domain name registration has expired and someone needs to renew it. I hope they plan to do that because I found the site useful. Bill Riemer Palstra wrote: On Thu, Nov 09, 2006 at 09:26:52AM -0800, Bill MacAllister wrote: What happened to the Innodb web pages? What comes up for be is a search page with a bunch of related links on it. I wanted to pull down a copy of ibbackup documentation and it isn't there anymore. Strange indeed, I get the search pages that Tucows/OpenSRS put up when they park a domain as soon as a customer lets their domain name expire... -- Riemer Palstra Amsterdam, The Netherlands [EMAIL PROTECTED] http://www.palstra.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=1 +--- | Bill MacAllister, Senior Programmer | 10030 Foothills Blvd | Roseville, CA 95747 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=1 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I do this query efficiently?
for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/530 - Release Date: 11/11/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I do this query efficiently?
To get the value for each id's max version take the query Peter just suggested and make it a subselect and join it back to data like this: select data.* from (SELECT id,MAX(version) maxversion FROM data GROUP BY id) a,data b where a.id=b.id and a.version=b.maxversion; - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/530 - Release Date: 11/11/2006 -- 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]
Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0 0: len 4; hex 2434; asc $4;; 1: len 4; hex 029c;
asc ;; 2: len 4; hex 005d; asc];; 3: len 6; hex
002e0aa8; asc. ;; 4: len 7; hex 00800f27f5; asc ' ;;
5: len 4; hex 160d; asc ;; 6: len 4; hex 295cdd41; asc )\ A;;
7: len 8; hex 8000123ed6cf3331; asc 31;;
--
echo Hello World :)
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I do this query efficiently?
Hi, Thanks for your quick answer, but unfortunately this query does not return the value column of the row; and that is the column I am ultimately interested in (in combination with the id). Regards, Sebastiaan Peter Brawley wrote: for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
You should play it safe and add $dblink-commit(); right after
$dblink-exec($sql);
The reason for this is from Page 419 of the
MySQL 5.0 Certification Study Guide bullet point #3:
During the course of a transaction, InnoDB may acquire row locks
AS IT DISCOVERS THEM TO BE NECESSARY.
I don't like the sound of that statament, because it is assumed
that PDO will attempt an autocommit with each SQL statment.
That is not so with a transaction in PDO. Note the explanation
for PDO::commit (http://us3.php.net/manual/en/function.pdo-commit.php):
bool PDO::commit ( void )
Commits a transaction, returning the database connection to autocommit mode
until the next call to PDO::beginTransaction() starts a new transaction.
That means that autocommit is disabled when you issue
$dblink-beginTransaction();
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 13, 2006 9:34:37 AM GMT-0500 US/Eastern
Subject: Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0 0: len 4; hex 2434; asc $4;; 1: len 4; hex 029c;
asc ;; 2: len 4; hex 005d; asc];; 3: len 6; hex
002e0aa8; asc. ;; 4: len 7; hex 00800f27f5; asc ' ;;
5: len 4; hex 160d; asc ;; 6: len 4; hex 295cdd41; asc )\ A;;
7: len 8; hex 8000123ed6cf3331; asc 31;;
--
echo Hello World :)
--
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: How do I do this query efficiently?
Try this !!! - Original Message - From: Rolando Edwards [EMAIL PROTECTED] To: peter brawley [EMAIL PROTECTED] Cc: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:28:46 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? To get the value for each id's max version take the query Peter just suggested and make it a subselect and join it back to data like this: select data.* from (SELECT id,MAX(version) maxversion FROM data GROUP BY id) a,data b where a.id=b.id and a.version=b.maxversion; - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/530 - Release Date: 11/11/2006 -- 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 General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: How do I do this query efficiently?
Hi, Thanks for the response! I thought I had tried this, but maybe my index was wrong or my query was just different because when I tried it it was really slow (also around 5 seconds). However this does the trick and it is very fast (0.02 seconds). Thanks again! Regards, Sebastiaan Rolando Edwards wrote: To get the value for each id's max version take the query Peter just suggested and make it a subselect and join it back to data like this: select data.* from (SELECT id,MAX(version) maxversion FROM data GROUP BY id) a,data b where a.id=b.id and a.version=b.maxversion; - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED], mysql@lists.mysql.com Sent: Monday, November 13, 2006 9:18:49 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
Sorry
I have $dblink-commit(); right after $dblink-exec($sql); but I
forgot to write it here (my mistake, sorry )
also I want to mention that I have 3 primary key in my table:
ShopID
CustomerID
OrderID
Could this be the problem ?
On 11/13/06, Rolando Edwards [EMAIL PROTECTED] wrote:
You should play it safe and add $dblink-commit(); right after
$dblink-exec($sql);
The reason for this is from Page 419 of the
MySQL 5.0 Certification Study Guide bullet point #3:
During the course of a transaction, InnoDB may acquire row locks
AS IT DISCOVERS THEM TO BE NECESSARY.
I don't like the sound of that statament, because it is assumed
that PDO will attempt an autocommit with each SQL statment.
That is not so with a transaction in PDO. Note the explanation
for PDO::commit (http://us3.php.net/manual/en/function.pdo-commit.php):
bool PDO::commit ( void )
Commits a transaction, returning the database connection to autocommit mode
until the next call to PDO::beginTransaction() starts a new transaction.
That means that autocommit is disabled when you issue
$dblink-beginTransaction();
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 13, 2006 9:34:37 AM GMT-0500 US/Eastern
Subject: Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 13428 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X waiting
Record lock, heap no 59 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0 0: len 4; hex 2434; asc $4;; 1: len 4; hex 029c;
asc ;; 2: len 4; hex 005d; asc];; 3: len 6; hex
002e0aa8; asc. ;; 4: len 7; hex 00800f27f5; asc ' ;;
5: len 4; hex 160d; asc ;; 6: len 4; hex 295cdd41; asc )\ A;;
7: len 8; hex 8000123ed6cf3331; asc 31;;
--
echo Hello World :)
--
MySQL General Mailing List
For list archives:
Re: How do I do this query efficiently?
Right, if you want the value column you need too, you need a different query ... SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; PB Sebastiaan van Erk wrote: Hi, Thanks for your quick answer, but unfortunately this query does not return the value column of the row; and that is the column I am ultimately interested in (in combination with the id). Regards, Sebastiaan Peter Brawley wrote: for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/531 - Release Date: 11/12/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
quote for job
I have a client that is looking for a setup and was hoping that somebody here could give me a quote for the job. I can't give ALL of the details, but I am hoping that this will be enough to get a quote. I am not looking for a hardware quote. Just one for setting this up software wise. 20 MySQL database servers. 10 at primary site and 10 at secondary site. Sites have not been choosen yet and can change in the future and might have more sites used for secondary etc. sites. The 10 at the other site need to mirror the 10 from the primary site. They are on a 1 to 1 ratio for the mirroring. So if one of the servers goes down, the one at the secondary site will take over. If that happens, then the one at the secondary site will be written to by the frontend program and when the primary sites server comes back up, it will need to take back over with any changes that where made to the secondary server. I understand that Microsoft has a setup where the frontend servers talk to a middleman server and the middleman server than directs the frontend server to the backend database server. Something like that anyway. There will be a LOT of frontend servers, but that is probably not going to be an issue at this point. We are mainly concerned with the backend database servers and connectivity to them. IF things work out, there could be as many as 100+ database servers and 100+ frontend servers within a couple of years. The kind of quotes that I need would be: 1. quote to set it all up when we buy the hardware and choose the data centers. 2. quote to have you and/or your company on call to maintain the servers. If you have any specific questions, you can either email me the questions or if you wish, you can email me your phone numbers and I will call you. If it goes the way they want, we will have to have all servers, frontend, middleman and backend setup within about 6 weeks if possible. Thanks Steve -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Deadlock
It should not be a problem if you are updating only one row at a time.
If an UPDATE query is updating more tahn one row, then MySQL may try
to acquire all the row-level locks first.
For example, in the query you gave
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
How many rows does this update ???
If Customer 1697 has OrderID=105 from shop 210,
does this occur in shop 210 only or multiple shops
in the case of parts coming from other shops
should shop 210 not have every part needed for order 105 ???
This is only a hypothetical question.
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: Rolando Edwards [EMAIL PROTECTED]
Cc: MySQL List mysql@lists.mysql.com
Sent: Monday, November 13, 2006 10:39:02 AM GMT-0500 US/Eastern
Subject: Re: Deadlock
Sorry
I have $dblink-commit(); right after $dblink-exec($sql); but I
forgot to write it here (my mistake, sorry )
also I want to mention that I have 3 primary key in my table:
ShopID
CustomerID
OrderID
Could this be the problem ?
On 11/13/06, Rolando Edwards [EMAIL PROTECTED] wrote:
You should play it safe and add $dblink-commit(); right after
$dblink-exec($sql);
The reason for this is from Page 419 of the
MySQL 5.0 Certification Study Guide bullet point #3:
During the course of a transaction, InnoDB may acquire row locks
AS IT DISCOVERS THEM TO BE NECESSARY.
I don't like the sound of that statament, because it is assumed
that PDO will attempt an autocommit with each SQL statment.
That is not so with a transaction in PDO. Note the explanation
for PDO::commit (http://us3.php.net/manual/en/function.pdo-commit.php):
bool PDO::commit ( void )
Commits a transaction, returning the database connection to autocommit mode
until the next call to PDO::beginTransaction() starts a new transaction.
That means that autocommit is disabled when you issue
$dblink-beginTransaction();
- Original Message -
From: Ahmad Al-Twaijiry [EMAIL PROTECTED]
To: MySQL List mysql@lists.mysql.com
Sent: Monday, November 13, 2006 9:34:37 AM GMT-0500 US/Eastern
Subject: Deadlock
Hi everyone,
Everyday I got around 10 Deadlock errors in my database :
SQLSTATE[40001]: Serialization failure: 1213 Deadlock found when
trying to get lock; try restarting transaction
SQL=UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
I'm using Innodb engine type for my tables and I already checked
http://dev.mysql.com/doc/refman/5.0/en/innodb-deadlocks.html
I know this is not dangerous but is there anyway to prevent it ? I
recheck my script and I couldn't find any problem on it
my question is this deadlock because there is another transaction that
lock the row or the table ? I want to know if this error because row
locking or table locking
my code is something like this (PHP5) :
?php
try{
$dblink-beginTransaction();
$sql=UPDATE Shop SET Total=Total-$q WHERE
CustomerID= . $CustomerID. AND OrderID= . $OrderID;
$dblink-exec($sql);
}catch (PDOException $e) {
// deadlock , report it
$dblink-rollBack();
}
?
I also have this code in another script (with another mysql username)
that will go over all record in Shop table and do some update in the
records.
Here is what I got when I execute SHOW ENGINE INNODB STATUS
061104 13:53:02
*** (1) TRANSACTION:
TRANSACTION 0 3020198, ACTIVE 0 sec, process no 12031, OS thread id
2584136624 fetching rows
mysql tables in use 1, locked 1
LOCK WAIT 42 lock struct(s), heap size 5504
MySQL thread id 110727, query id 3714030 localhost user2 Updating
UPDATE Shop SET Total=Total-125 WHERE CustomerID=1697 AND OrderID=105
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020198 lock_mode X waiting
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2: len 4; hex 0060; asc`;; 3: len 6; hex 002e1597; asc
. ;; 4: len 7; hex 00801026f1; asc ;; 5: len 4; hex
0c61; asca;; 6: len 4; hex d723a142; asc # B;; 7: len 8; hex
8000123ed6edf88a; asc;;
*** (2) TRANSACTION:
TRANSACTION 0 3020183, ACTIVE 1 sec, process no 12031, OS thread id
2588175280 starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
30 lock struct(s), heap size 2496, undo log entries 26
MySQL thread id 110721, query id 3714111 localhost user2 Updating
UPDATE Shop SET Total=Total+1,Price=27.67 WHERE ShopID=9268
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 0 page no 35335 n bits 400 index `PRIMARY` of
table `dbname/Shop` trx id 0 3020183 lock_mode X
Record lock, heap no 292 PHYSICAL RECORD: n_fields 8; compact format;
info bits 0
0: len 4; hex 3471; asc 4q;; 1: len 4; hex 06cc; asc
;; 2:
Re: How do I do this query efficiently?
Pretty slick. - Original Message - From: Peter Brawley [EMAIL PROTECTED] To: Sebastiaan van Erk [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, November 13, 2006 10:43:26 AM GMT-0500 US/Eastern Subject: Re: How do I do this query efficiently? Right, if you want the value column you need too, you need a different query ... SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; PB Sebastiaan van Erk wrote: Hi, Thanks for your quick answer, but unfortunately this query does not return the value column of the row; and that is the column I am ultimately interested in (in combination with the id). Regards, Sebastiaan Peter Brawley wrote: for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.14.3/531 - Release Date: 11/12/2006 -- 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: How do I do this query efficiently?
Wow, neat. I didn't think you could do that without a subquery somewhere. Learned a cool new trick today. Thanks! Regards, Sebastiaan Peter Brawley wrote: Right, if you want the value column you need too, you need a different query ... SELECT t1.id, t1.version, t1.value FROM data t1 LEFT JOIN data t2 ON t1.id=t2.id AND t1.version t2.version WHERE t2.id IS NULL; PB Sebastiaan van Erk wrote: Hi, Thanks for your quick answer, but unfortunately this query does not return the value column of the row; and that is the column I am ultimately interested in (in combination with the id). Regards, Sebastiaan Peter Brawley wrote: for every id I want exactly one row, namely the row with the maximum value of version. SELECT id,MAX(version) FROM data GROUP BY id; PB - Sebastiaan van Erk wrote: Hi all, I have the following simple table: CREATE TABLE data ( id int NOT NULL, version int NOT NULL, value int NOT NULL, PRIMARY KEY (id, version) ) ENGINE=InnoDB DEFAULT CHARSET=utf8; What I would like to do is to find all the values for the latest versions, that is, for every id I want exactly one row, namely the row with the maximum value of version. Without using SQL I can think of a very efficient way for the query optimizer to accomplish this for me, namely by using the primary key to iterate through each value for id and then looking for the maximum version, which is essentialy O(1) because it's the last entry in the index for that specific id. However, I don't know how to do this query in SQL, the closest I got was: select id, version, value from data d1 where version = (select max(d2.version) from data d2 where d2.id = d1.id) However this is extremely inefficient and takes much to long (in the order of 5 seconds on the data I have). Does anybody know an efficient way to do this? Thanks in advance, Sebastiaan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Senior DBA Opening at Pythian in Ottawa - Will Sponsor
Hello everyone, I have an opening for a senior DBA here in Ottawa, Ontario, Canada. I measure senior not by years of experience but rather by overall abilities, so please feel free to apply even if you don't have years years of experience. We will happily sponsor a work visa for an exceptional candidate. Please forward this message to anyone you feel might have an interest, please feel free to re-post this message anywhere you feel is appropriate. - If one crosses my desk, I will consider either an elite MySQL DBA with little to no Oracle or SQL Server experience for this opening, or an elite Oracle DBA. Please note that this description is written assuming Oracle skills - if you're a MySQL DBA please 's/Oracle/MySQL/g' for me and spare me the trouble would ya? Top criteria: - Outstanding Oracle DBA on UNIX skills, especially SQL, PL/SQL, tuning, modeling, config, etc. - Exceptional troubleshooting, problem-solving and learning skills - Superior productivity per hour and overall getting-the-job-done-right abilities - Fluent communication skills in English, both written and oral - Oracle DBA on platforms other than UNIX a plus, so long as the UNIX experience is there too - UNIX sysadmin experience a plus, RAC implementation experience a plus - Publications and presentations experience and interest a plus - RAC, Oracle Applications, SAP and Peoplesoft experience a plus - DBA experience on non-Oracle platforms a plus, expecially MySQL and SQL Server Job highlights: - Work in an elite team of DBAs for an elite group and growing of customers - you'll learn more here in a year than in any in-house DBA job no matter how long you stay; I personally guarantee it. - Work and gain valuable experience on every mainstream platform, including AIX, HP/UX, Solaris, Linux, Tru64, Windows, etc. - Support every mainstream database technology and feature, including Oracle RAC, advanced queuing, advanced replication, every flavour of dataguard, RMAN, streams, etc. etc. - Work across multiple industries including health care, manufacturing, media, dot-com, education, retail, services, and many more. - Work in a company that values hard work, not long work. - Work in a company that will allow you to research and write articles, presentations and blog posts on company time, and pay for you to present your research at any user conference in North America where it gets accepted. Learn more about Pythian and see our customer list at http://www.pythian.com . To apply: Send an email with a one-paragraph introduction of who you are and why you are exceptional to me at [EMAIL PROTECTED] In case you're wondering, I use gmail for our HR inbox because of its superior labeling, sorting and searching features. Please attach your resume in Word or PDF format. More information about Ottawa: Ottawa is a city of around 1,000,000 and is a great place to live. Encyclopedia: a href=http://en.wikipedia.org/wiki/Ottawa http://en.wikipedia.org/wiki/Ottawa/a Cost of living: a href=http://www.finfacts.com/costofliving.htm http://www.finfacts.com/costofliving.htm /a Quality of life: a href= http://www.finfacts.com/irelandbusinessnews/publish/article_1000846.shtml http://www.finfacts.com/irelandbusinessnews/publish/article_1000846.shtml/a a href= http://www.canadaka.net/modules.php?name=Forumsfile=viewtopict=3052 http://www.canadaka.net/modules.php?name=Forumsfile=viewtopict=3052/a (Globe Mail article reprint) -- Paul Vallee President, The Pythian Group, Inc. http://www.pythian.com I blog at http://www.pythian.com/blogs/
Backing up large dbs with tar
Greetings: I have a 600M data file that never gets backed up. The following error occurs in the cron job: tar: /data/mysql/my_db_name/my_large_table_name.MYI: file changed as we read it Is there a way I can set this one table to read-only prior to the backup without affecting other db writes during this operation? Thanks, Van -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up large dbs with tar
Van wrote: Greetings: I have a 600M data file that never gets backed up. The following error occurs in the cron job: tar: /data/mysql/my_db_name/my_large_table_name.MYI: file changed as we read it Is there a way I can set this one table to read-only prior to the backup without affecting other db writes during this operation? Thanks, Van Look at mysqlhotcopy. -- Gerald L. Clark Supplier Systems Corporation -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Backing up large dbs with tar
Van, I'll second what Gerald said about mysqlhotcopy. When we first began using MySQL at my last job, we had terrible problems with MySQL crashing. Turned out to be due to a 3rd party backup process attempting to lock and read the database files while MySQL was attempting to use them. Using mysqlhotcopy to copy the files elsewhere, and excluding the data directory from the backup software, gave us a stable solution. mysqldump might also work well for you, as it can lock tables/databases and give you a consistent snapshot. Potentially takes longer to restore from a mysqldump file though. HTH, Dan On 11/13/06, Van [EMAIL PROTECTED] wrote: Greetings: I have a 600M data file that never gets backed up. The following error occurs in the cron job: tar: /data/mysql/my_db_name/my_large_table_name.MYI: file changed as we read it Is there a way I can set this one table to read-only prior to the backup without affecting other db writes during this operation? Thanks, Van -- 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]
Find next available number for gidnumbers and uidnumbers
I need some advice. We currently are in the process of starting to use LDAP for our accounts to authenticate. Now when I create a new LDAP account I need to assign a free gidnumber and uidnumber which can be to 1 to 999. My plan is to load all gidnumbers and uidnumbers that are being used into two separate mysql tables. What command could I use to find the first number that is not being used or not in the database?. Now sometimes it could be 12 or the very highest number, because when accounts are deleted these numbers will be removed from the tables, so I need to find the next available number.
RE: Find next available number for gidnumbers and uidnumbers
Would it be practical to leave the record in the table and mark it as unused? That would certainly simplify the whole business. Regards, Jerry Schwartz Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 -Original Message- From: Kory Wheatley [mailto:[EMAIL PROTECTED] Sent: Monday, November 13, 2006 12:51 PM To: mysql@lists.mysql.com Subject: Find next available number for gidnumbers and uidnumbers I need some advice. We currently are in the process of starting to use LDAP for our accounts to authenticate. Now when I create a new LDAP account I need to assign a free gidnumber and uidnumber which can be to 1 to 999. My plan is to load all gidnumbers and uidnumbers that are being used into two separate mysql tables. What command could I use to find the first number that is not being used or not in the database?. Now sometimes it could be 12 or the very highest number, because when accounts are deleted these numbers will be removed from the tables, so I need to find the next available number. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Find next available number for gidnumbers and uidnumbers
Kory - It's always a little more challenging to find something that's NOT in the data! I'd suggest two approaches: 1 - create a reference table containing all the possible values, 1 - 999. Then do a SELECT MIN and a join to find the lowest number not in your data table, something like this: SELECT MIN(r.uid) FROM reference_table r LEFT JOIN data_table d ON r.uid = d.uid WHERE d.uid IS NULL 2 - create a stored procedure that starts at 1 and checks for the presence of each number, adds one, until not present in the table. This is pretty inefficient though. HTH, Dan On 11/13/06, Kory Wheatley [EMAIL PROTECTED] wrote: I need some advice. We currently are in the process of starting to use LDAP for our accounts to authenticate. Now when I create a new LDAP account I need to assign a free gidnumber and uidnumber which can be to 1 to 999. My plan is to load all gidnumbers and uidnumbers that are being used into two separate mysql tables. What command could I use to find the first number that is not being used or not in the database?. Now sometimes it could be 12 or the very highest number, because when accounts are deleted these numbers will be removed from the tables, so I need to find the next available number. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Find next available number for gidnumbers and uidnumbers
I need some advice. We currently are in the process of starting to use LDAP for our accounts to authenticate. Now when I create a new LDAP account I need to assign a free gidnumber and uidnumber which can be to 1 to 999. My plan is to load all gidnumbers and uidnumbers that are being used into two separate mysql tables. What command could I use to find the first number that is not being used or not in the database?. Now sometimes it could be 12 or the very highest number, because when accounts are deleted these numbers will be removed from the tables, so I need to find the next available number.
replication
Hi, I enabled binlog for replication yesterday and i'm getting some problems: -Yesterday, and all last days binlog was off, and i had an only one mysqld process at top processlist. -Today I'm getting several mysql processes, instead of a single one. -I've got three hard disks, one drive for system and innodb, another for all other data, and thirid for binaly replication logs. Yesteday I had no more than 200 httpd processes peak, and today i have more than 400. Server load seems to be fine ( similar to last days ), so i can't understand what has changed, and what's causing this situation. Every mysqld and httpd server are on sepparated 4processors XEON dedicated servers. Main website has 1.000.000 impressions, and 60.000 unique hits per day. Thanks for amy help. Regads. Ivan Lopez. Logosur.
Re: access full-text index
I think the full-text index is an inverted index structure. So, it has all the words from the fields it indexes. For each word it has a list of record ID which have that word. What I am interested to get is this inverted index structure. I imagine it can be represented as 1-2 table(s). Can I do that? Is the structure accessible as a table? (Of course, I don't want to change it.) Thanks a lot, Ray Leandro Guimarães Faria Corcete DUTRA wrote: On Wed, 08 Nov 2006 18:51:20 -0800, Rares Vernica wrote: Is it possible to access the Full-Text Index structures from SQL? What do you mean exactly? SQL is not intended for physical structures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
