Sequence ID generation transaction-safe?
Hi, I am using a sequence table as suggested in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#id2887015 in order to generate new, unique IDs for a table where I have a primary key spanning multiple columns and still need a unique numeric part without being able to use an auto-increment table. I am using UPDATE translations_seq SET id=LAST_INSERT_ID(id+1) and then I fetch my newest ID with select id from translations_seq. While this method is described in the manual as multi-user safe I was wondering if this was also transaction safe? When two users start a transaction at the same time (and don't commit it yet) will they get different IDs? Because the UPDATE statement will not take place yet until COMMITing it, I am unsure if it will actually hand out different IDs for both of the transactions. Can anyone enlighten me? Thanks Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
database design help
Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: phpmysql don't answer
Curiously, now, I can access to phpmysql, but I don't know why... So, I have an error 1064 when I try install a base with a python script. This is the error message : ERROR 1064 (42000) at line 772: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'return char(4) default NULL, renewals tinyint(4) default NULL, timestamp tim' at line 9 Start tag expected, '' not found at /usr/lib/perl5/XML/LibXML/SAX.pm line 64 at /usr/share/perl5/XML/Simple.pm line 287 BEGIN failed--compilation aborted at scripts/updater/updatedatabase line 21. Problem updating database... Thanks, Thibaud. João Cândido de Souza Neto a écrit : Is your apache looking for index.php file as well or just for index.html file? Give a trial in this. Thibaud Hulin [EMAIL PROTECTED] escreveu na mensagem news:[EMAIL PROTECTED] Thanks for your answer. This is the error message : Not Found The requested URL /phpmysql was not found on this server. Apache/2.2.3 (Debian) PHP/5.2.0-7 Server at localhost Port 80 For the logs, I don't know where looking. I suppose the base is corrupt after a hard reboot ? Barry a écrit : Thibaud Hulin schrieb: Hi! I'm a beginner with phpmysql, and after an installation, I can't access to my page http://localhost/phpmysql on Debian Etch. Is a problem of restarting mysql ? Thanks for help, Thibaud. Errors, warnings, logs? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Innodb log sequence error - urgent
Ratheesh, if the database otherwise looks ok (no crashes, no corrupt tables), then the easiest way to fix the wrong log sequence number (lsn) is to artificially inflate the log sequence number. If your log sequence number is 4 GB too small, then inserting and deleting 4 GB worth of rows will lift it high enough so that the complaints about a too small lsn end. The risk in having inconsistent lsn's stamped into data pages is that if there is a database crash, then the log will not be applied to those pages. 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 . From: Ratheesh K J Date: December 11 2006 10:23am Subject: Re: Innodb log sequence error - urgent Get Plain Text Thanks, I have the previous ib_log* files on the app server. And every thing on the cnf file was perfect. Only the ib_log file's size was a mismatch. Whats the best work around? Can I copy the log files of the App server to the DB server and change the innodb_log_file_size to 256M and then restart the MySQL server. If I do so will I lose the updates to the database that happened today? So my actual problem is this: I have two sets of ib_logfile* files. To be particular there are a.. ib_logfile0, ib_logfile1, ib_logfile2 on the App server- each 257M (when i did a du -sh). In the my.cnf file of the App server innodb_log_file_size is set to 256M b.. ib_logfile0, ib_logfile1, ib_logfile2 on the DB server - each 5M. These log files were created freshly by the MySQL server as the log files from the App server was not copied to the DB server. In the my.cnf file of the DB server innodb_log_file_size is set to 5M by mistake. All the other settings were same as on the app server. The ibdata1 file is that of the App server. And I get the log sequence errors as shown in my previous post. But everything seems to be working fine. There have been no problems accessing the data. What I can I possibly do to get everything right. How can I correct the log sequence error? Should the log files of App server be in the DB server? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sequence ID generation transaction-safe?
Hi Frederic, Update then select on a single row table is transaction safe. If two users start a transaction and issue update queries the first query to execute will set a lock on that row. The second query will then block on the update waiting to obtain the same lock. In innodb row locks are not released until a transaction commits. This means that the transaction with the successful update can then issue another select to fetch the new row id while the first transaction is still waiting on the row lock. When the first transaction commits the row lock will be freed allowing the section transaction to obtain the lock, update, and select the next number in the sequence. I'm curious why you are using a sequence table to generate unique ids for another table. Why not just change the other table to have an auto_increment primary key and a secondary unique key to replace the current primary key? Innodb uses a special mechanism to allocate auto_increment ids that is much faster than a sequence table... -Eric On 12/16/06, Frederic Wenzel [EMAIL PROTECTED] wrote: Hi, I am using a sequence table as suggested in http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#id2887015 in order to generate new, unique IDs for a table where I have a primary key spanning multiple columns and still need a unique numeric part without being able to use an auto-increment table. I am using UPDATE translations_seq SET id=LAST_INSERT_ID(id+1) and then I fetch my newest ID with select id from translations_seq. While this method is described in the manual as multi-user safe I was wondering if this was also transaction safe? When two users start a transaction at the same time (and don't commit it yet) will they get different IDs? Because the UPDATE statement will not take place yet until COMMITing it, I am unsure if it will actually hand out different IDs for both of the transactions. Can anyone enlighten me? Thanks Fred -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: database design help
At 12:54 PM 12/16/2006, you wrote: Hiya, Real easy quick question. I need to design a database which holds users with email, name and some other details. I also want each user to be able to create one or more groups of users, owned by themselves. What would be the best design approach? So far i have a table for the users which stores their personal details, but i dont know where to go from here to create the groups? Create a new table for every group? The group would just contain a list of the users emails in that group. Or would i create a new table for the groups and attach a password field on it so only the user that created it could access it? A very newbie question i know, but i am one, i'll admit it. Any help would be apprectiated. Thanks John -- View this message in context: http://www.nabble.com/database-design-help-tf2832533.html#a7908028 Sent from the MySQL - General mailing list archive at Nabble.com. Presumably users is something like this: Members table ID - numeric, auto-generated BelongsTo - group id Firstname Surname email phone etc. But that's not right - A User can belong to only one group, most probably want to belong to many - so remove the BelongsTo field and let a refernce to the ID field of the membertable do the work GroupMembers table ID - numeric, autogenerated Group_ID - numeric, foreign key Member_ID - numeric, foreign key - refers to ID field in Members table and of course a Groups table, ID - numeric, autogenerated Managed_By - foreign key, refers to ID field in Members table Name other pertinent stuff There you go - three tables able to hold unlimited combinations of groups and members and you will never have a many to many problem. Cheers - Miles other info . -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.409 / Virus Database: 268.15.21/589 - Release Date: 12/15/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Sort Select by List
Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith
Re: Sort Select by List
Hi Hal, I appreciate your help, but the order can not be alphabetical. It must be explicitly Exec, VP, Dir... Keith - Original Message - From: Hal Wigoda [EMAIL PROTECTED] To: Keith Spiller [EMAIL PROTECTED] Sent: Saturday, December 16, 2006 2:28 PM Subject: Re: Sort Select by List order by office On Dec 16, 2006, at 3:25 PM, Keith Spiller wrote: Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Keith Spiller írta: Hello, RE: Sort Select by List I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, Keith order by right(office,1) or make an extra column for ordering t -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC,office='Dir' DESC; Not many people know that you can order by literals as well ;) Cheers, Mark -- Mark Leith, Support Engineer MySQL AB, Worcester, England, www.mysql.com Are you MySQL certified? www.mysql.com/certification -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Why innodb can give the same X gap lock to two transactions?
Which version of mysql is this? In 5.1.12 when I run your test the section transaction blocks waiting for the lock (as it should). My show innodb status output is: TRANSACTIONS Trx id counter 0 1300 Purge done for trx's n:o 0 1288 undo n:o 0 0 History list length 1 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 1284, not started, process no 23890, OS thread id 1116363696 MySQL thread id 2, query id 25 localhost root ---TRANSACTION 0 1299, ACTIVE 21 sec, process no 23890, OS thread id 1116765104 starting index read mysql tables in use 1, locked 1 LOCK WAIT 2 lock struct(s), heap size 3201 row lock(s) MySQL thread id 5, query id 58 localhost root statistics select * from test where id=6 for update Trx has approximately 1 row locks --- TRX HAS BEEN WAITING 21 SEC FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 0 page no 52 n bits 80 index `PRIMARY` of table `test`.`test` trx id 0 1299 lock_mode X locks rec but not gap waiting Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0 0: len 4; hex 8006; asc ;; 1: len 6; hex 0510; asc ;; 2: len 7; hex 80002d0110; asc - ;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; On 12/11/06, leo huang [EMAIL PROTECTED] wrote: Hi, all, We have an innodb table named test. It has some rows as follow: mysql show create table test; +---+-+ | Table | Create Table | +---+-+ | test | CREATE TABLE `test` ( `id` int(11) NOT NULL default '0', `name` char(20) default NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 | +---+-+ 1 row in set (1.75 sec) mysql select * from test; ++-+ | id | name| ++-+ | 1 | huangjy | | 2 | huangjy | | 3 | huangjy | | 4 | huangjy | | 5 | huangjy | | 7 | huangjy | | 8 | huangjy | | 9 | huangjy | ++-+ 8 rows in set (1.98 sec) When I start two transactions as follow: Transaction 1: mysql begin; Query OK, 0 rows affected (2.51 sec) mysql select * from test where id=6 for update; Empty set (2.17 sec) Transaction 2: mysql begin; Query OK, 0 rows affected (1.56 sec) mysql select * from test where id=6 for update; Empty set (2.27 sec) Now, I use show engine innodb status to see the innodb lock status. The output as follow: TRANSACTIONS Trx id counter 0 5168907 Purge done for trx's n:o 0 5168898 undo n:o 0 0 History list length 2 Total number of lock structs in row lock hash table 2 LIST OF TRANSACTIONS FOR EACH SESSION: ---TRANSACTION 0 0, not started, process no 30668, OS thread id 2484620208 MySQL thread id 2, query id 46 localhost root show engine innodb status ---TRANSACTION 0 5168906, ACTIVE 83 sec, process no 30668, OS thread id 2484820912 2 lock struct(s), heap size 320 MySQL thread id 1, query id 45 localhost root TABLE LOCK table `test/test` trx id 0 5168906 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168906 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ;; ---TRANSACTION 0 5168905, ACTIVE 120 sec, process no 30668, OS thread id 2484419504 2 lock struct(s), heap size 320 MySQL thread id 3, query id 43 localhost root TABLE LOCK table `test/test` trx id 0 5168905 lock mode IX RECORD LOCKS space id 0 page no 54 n bits 80 index `PRIMARY` of table `test/test` trx id 0 5168905 lock_mode X locks gap before rec Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; 1-byte offs TRUE; info bits 0 0: len 4; hex 8007; asc ;; 1: len 6; hex 004eb50c; asc N ;; 2: len 7; hex 008013285c; asc (\;; 3: len 20; hex 6875616e676a7920202020202020202020202020; asc huangjy ... As you can see, TRANSACTION 0 5168906 and TRANSACTION 0 5168905 both get the X gap locks on the same record. The MySQL Manual said that X lock is an exclusive lock. Why two transactions can get the same X lock? Any comment will be welcomed? Best regards, Leo Huang -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com --
Re: Sort Select by List
In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Sort Select by List
In the last episode (Dec 16), Dan Nelson said: In the last episode (Dec 16), Mark Leith said: Pintér Tibor wrote: Keith Spiller írta: I'm wondering how I would turn three different queries: SELECT * FROM team WHERE office = 'Exec' SELECT * FROM team WHERE office = 'VP' SELECT * FROM team WHERE office = 'Dir' Into one query with the sort order of office = 'Exec', 'VP', 'Dir'... Thanks, order by right(office,1) or make an extra column for ordering Or be really smart :) SELECT * FROM team ORDER BY office='Exec' DESC,office='VP' DESC, office='Dir' DESC; More efficient would be to use the FIELD function: SELECT * FROM team ORDER BY FIELD(office,Exec,VP,Dir); Oops. I only read the replies and not the original post. Assuming there are many other values for the office field, you might want SELECT * FROM team WHERE office = 'Exec' UNION SELECT * FROM team WHERE office = 'VP' UNION SELECT * FROM team WHERE office = 'Dir'; -- Dan Nelson [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
