Re: innodb disabled
r u not able to create tables of type=INNODB. On 5/7/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, What ever you have written i did the same thing, Yet innodb get disabled, On Tue, May 6, 2008 at 11:24 AM, Rolando Edwards [EMAIL PROTECTED] wrote: If you just created the /data/mysql folder and moved the ib* files to that folder from /var/lib/mysql, you may have to contend with the internal data dictionary with ibdata1. You do the following: 1) Put the mysql data back into /var/lib/mysql 2) Configure my.cnf to set datadir=/var/lib/mysql 3) Restart mysqld Once you put it back the way it was, then 4) mysqldump -h... -u... -p... --single-transaction --all-databases --routines --triggers AllData.sql 5) Make /data/mysql TOTALLY EMPTY. 6) Configure my.cnf to set datadir=/data/mysql 7) Restart mysqld At this point, mysqld will regenerate a new ibdata1 file with a clean internal data dictionary. 8) mysql -h... -u... -p... AllData.sql All data get put into this new data folder and will reload the internal data dictionary elements pertaining to its new location. Moving MyISAM is simple. Moving InnoDB is a little more work but doing it this way ensures ibdata1 is in a proper state. -Original Message- From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 06, 2008 9:12 AM To: Krishna Chandra Prajapati Cc: mysql Subject: Re: innodb disabled Hi, Can you send the error log file of mysql server in your machine?. Regards, Juan On 5/6/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi Juan, I have verified directory permissions, its shown below. linux76:~# ls -l /data/mysql/ total 535088 drwx-- 2 mysql mysql 20480 2008-05-04 23:44 dip -rw-rw 1 mysql mysql 10485760 2007-12-23 01:21 ibdata1 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile0 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile1 drwx-- 2 mysql root 4096 2008-03-28 02:58 mysql In error log file there is error. debian-sys-maint error The above error come while starting mysql for the first time. Then i have given the permissions for debian sys-maint from /etc/mysql/debian.cnf. After that restart mysql don't gives any error. still innodb is disabled. Thanks, Prajapati On Tue, May 6, 2008 at 7:03 AM, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Hi, The InnoDB engine was disable because some ib_log files or Ibdata files, InnoDB can´t read. In some cases, could be a directory permissions or some error in creation of ib_logfiles when database is started. If you want , try to see in the error log file generated in order to see more details. If you don´t understand nothing in the error log file, please send this errors to the list. Regards, Juan On Tue, May 6, 2008 at 5:27 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, I have change the data directory from /var/lib/mysql to /data/mysql directory and adding innodb_file_per_table. By doing this innodb storage engine get dissabled. my.cnf doesn't contains #skip-innodb. Is it's adding will enable or not HOW CAN I ENABLE INNODB mysql show engines; ++--++ | Engine | Support | Comment| ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine|
connectors: per session persistent connection (PHP)
Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb disabled
No, I am not able to create table of innodb type On Wed, May 7, 2008 at 2:48 AM, Ananda Kumar [EMAIL PROTECTED] wrote: r u not able to create tables of type=INNODB. On 5/7/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, What ever you have written i did the same thing, Yet innodb get disabled, On Tue, May 6, 2008 at 11:24 AM, Rolando Edwards [EMAIL PROTECTED] wrote: If you just created the /data/mysql folder and moved the ib* files to that folder from /var/lib/mysql, you may have to contend with the internal data dictionary with ibdata1. You do the following: 1) Put the mysql data back into /var/lib/mysql 2) Configure my.cnf to set datadir=/var/lib/mysql 3) Restart mysqld Once you put it back the way it was, then 4) mysqldump -h... -u... -p... --single-transaction --all-databases --routines --triggers AllData.sql 5) Make /data/mysql TOTALLY EMPTY. 6) Configure my.cnf to set datadir=/data/mysql 7) Restart mysqld At this point, mysqld will regenerate a new ibdata1 file with a clean internal data dictionary. 8) mysql -h... -u... -p... AllData.sql All data get put into this new data folder and will reload the internal data dictionary elements pertaining to its new location. Moving MyISAM is simple. Moving InnoDB is a little more work but doing it this way ensures ibdata1 is in a proper state. -Original Message- From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 06, 2008 9:12 AM To: Krishna Chandra Prajapati Cc: mysql Subject: Re: innodb disabled Hi, Can you send the error log file of mysql server in your machine?. Regards, Juan On 5/6/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi Juan, I have verified directory permissions, its shown below. linux76:~# ls -l /data/mysql/ total 535088 drwx-- 2 mysql mysql 20480 2008-05-04 23:44 dip -rw-rw 1 mysql mysql 10485760 2007-12-23 01:21 ibdata1 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile0 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile1 drwx-- 2 mysql root 4096 2008-03-28 02:58 mysql In error log file there is error. debian-sys-maint error The above error come while starting mysql for the first time. Then i have given the permissions for debian sys-maint from /etc/mysql/debian.cnf. After that restart mysql don't gives any error. still innodb is disabled. Thanks, Prajapati On Tue, May 6, 2008 at 7:03 AM, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Hi, The InnoDB engine was disable because some ib_log files or Ibdata files, InnoDB can´t read. In some cases, could be a directory permissions or some error in creation of ib_logfiles when database is started. If you want , try to see in the error log file generated in order to see more details. If you don´t understand nothing in the error log file, please send this errors to the list. Regards, Juan On Tue, May 6, 2008 at 5:27 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, I have change the data directory from /var/lib/mysql to /data/mysql directory and adding innodb_file_per_table. By doing this innodb storage engine get dissabled. my.cnf doesn't contains #skip-innodb. Is it's adding will enable or not HOW CAN I ENABLE INNODB mysql show engines; ++--++ | Engine | Support | Comment| ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL
Re: innodb disabled
when u try to create the table, what is the error ur getting. regards anandkl On 5/7/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: No, I am not able to create table of innodb type On Wed, May 7, 2008 at 2:48 AM, Ananda Kumar [EMAIL PROTECTED] wrote: r u not able to create tables of type=INNODB. On 5/7/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi, What ever you have written i did the same thing, Yet innodb get disabled, On Tue, May 6, 2008 at 11:24 AM, Rolando Edwards [EMAIL PROTECTED] wrote: If you just created the /data/mysql folder and moved the ib* files to that folder from /var/lib/mysql, you may have to contend with the internal data dictionary with ibdata1. You do the following: 1) Put the mysql data back into /var/lib/mysql 2) Configure my.cnf to set datadir=/var/lib/mysql 3) Restart mysqld Once you put it back the way it was, then 4) mysqldump -h... -u... -p... --single-transaction --all-databases --routines --triggers AllData.sql 5) Make /data/mysql TOTALLY EMPTY. 6) Configure my.cnf to set datadir=/data/mysql 7) Restart mysqld At this point, mysqld will regenerate a new ibdata1 file with a clean internal data dictionary. 8) mysql -h... -u... -p... AllData.sql All data get put into this new data folder and will reload the internal data dictionary elements pertaining to its new location. Moving MyISAM is simple. Moving InnoDB is a little more work but doing it this way ensures ibdata1 is in a proper state. -Original Message- From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 06, 2008 9:12 AM To: Krishna Chandra Prajapati Cc: mysql Subject: Re: innodb disabled Hi, Can you send the error log file of mysql server in your machine?. Regards, Juan On 5/6/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi Juan, I have verified directory permissions, its shown below. linux76:~# ls -l /data/mysql/ total 535088 drwx-- 2 mysql mysql 20480 2008-05-04 23:44 dip -rw-rw 1 mysql mysql 10485760 2007-12-23 01:21 ibdata1 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile0 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile1 drwx-- 2 mysql root 4096 2008-03-28 02:58 mysql In error log file there is error. debian-sys-maint error The above error come while starting mysql for the first time. Then i have given the permissions for debian sys-maint from /etc/mysql/debian.cnf. After that restart mysql don't gives any error. still innodb is disabled. Thanks, Prajapati On Tue, May 6, 2008 at 7:03 AM, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Hi, The InnoDB engine was disable because some ib_log files or Ibdata files, InnoDB can´t read. In some cases, could be a directory permissions or some error in creation of ib_logfiles when database is started. If you want , try to see in the error log file generated in order to see more details. If you don´t understand nothing in the error log file, please send this errors to the list. Regards, Juan On Tue, May 6, 2008 at 5:27 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, I have change the data directory from /var/lib/mysql to /data/mysql directory and adding innodb_file_per_table. By doing this innodb storage engine get dissabled. my.cnf doesn't contains #skip-innodb. Is it's adding will enable or not HOW CAN I ENABLE INNODB mysql show engines; ++--++ | Engine | Support | Comment| ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES |
Re: innodb disabled
Am Mittwoch, den 07.05.2008, 01:30 schrieb Krishna Chandra Prajapati: Currently error log file is empty. What else can be the reason for disable innodb. You mentioned that you're using Debian. The MySQL packages in Debian using syslog, check /var/log/syslog for errors. Norbert -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
This is a little off-topic for this list (recent PHP tutorials nowithstanding)... replying offline On Wed, May 7, 2008 at 5:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- Sebastian Mendel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
isn't this the general mysql list? isn't mysqlnd maintained by mysql? Michael Dykman schrieb: This is a little off-topic for this list (recent PHP tutorials nowithstanding)... replying offline On Wed, May 7, 2008 at 5:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: connectors: per session persistent connection (PHP)
Yes, it is the generic MySQL list. Your question, I am suggesting, goes much more towards PHP interfaces and programming techniques rather than MySQL itself. You will find excellent mailing lists for PHP programmers who have spent a lot of time thinking about exactly this sort of problem. The behviour of apache-modules compiled from zend-provided source is not really a MySQL issue. On Wed, May 7, 2008 at 9:38 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: isn't this the general mysql list? isn't mysqlnd maintained by mysql? Michael Dykman schrieb: This is a little off-topic for this list (recent PHP tutorials nowithstanding)... replying offline On Wed, May 7, 2008 at 5:36 AM, Sebastian Mendel [EMAIL PROTECTED] wrote: Hi, wouldn't it be very helpful if mysql connectors support some sort of per session persistent connection? this would save a lot of queries in many apps, for example SET NAMES, setting variables, creating temporary tables or are there any other methods i am not aware of to achieve this? -- - michael dykman - [EMAIL PROTECTED] - All models are wrong. Some models are useful. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Any better ways that LEFT JOIN?
Hi, My scenario is as described below, and i am in search of a better way to run a query for that scenario. Any bright ideas are more than welcome! I have a table keyword (which contains keyword_id, keyword_trackid, keyword_title) and a table track (which contains track_id, track_title). Obviously, i have a number of keywords describing each track. I need to search for a keyword, and then get all tracks which this keyword describes. However, i also need all other keywords which describes the found tracks. I currently solve this with the following query: SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword K1 LEFT JOIN track T1 ON T1.track_id = K1.keyword_trackid LEFT JOIN keyword K2 ON T1.track_id = K2.keyword_trackid WHERE (K1.keyword_keyword LIKE '%keyword%'); I am not really worried about full text search right now, as i am more worried about that the query itself most likely will take ages when the number of keywords gets bigger. I could probably also do this with programming, but then again i would have to get the list of all keywords from the database...which could be something like 5 million keyword - track relations. So, if you would happen to know how this query can be optimised, then i'd very much appreciate that! -- View this message in context: http://www.nabble.com/Any-better-ways-that-LEFT-JOIN--tp17106190p17106190.html 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]
design online lottery system
Hi, I am designing an online lottery system. There is two-lottery type: 1. The lottery number cannot be repeated (the order is not important), For example, pick three num in num 1 to 8. If I selected 235, 546 and 3458(which actually is combination), the table should like: - |id (int) |num (bit)| | 1 |01101000| | 2 |00011100| | 3 |00111001| OK, if I want to select a result for 345, I can use the query: select * from table where num = num 00111000; 2. The lottery number can repeat (which order is important). For example, pick there number to combine to a digit. e.g., 345, not 435 or 543. This time I really don't know how to design the database. I only to try is retrieving the data from database to php script, than analyze them. I'm not familiar with database design. Could you give me some advice? Thanks! -- Regards, Wang Yi -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
question about update/join query
Hey all - I have two tables - an event_log table, and a user table. There is a last_visit column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a last_visit column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Order Problem
Hi All, I've the following query :SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) This query works fine. However the query result is in a different order to what I passed in varProductID. How can I order the results based on my list like varProductID = 1000,2500,1500 At the moment the result is 1000 1500 2500 But I want 1000 2500 1500 Thanks, Neil _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/
Re: Any better ways that LEFT JOIN?
Hi, I would usually use something like : SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword K1, track T1, Keyword2 K2 where k1.keyword_trackid=t1.track_id and t1.track_id=k2.keyword_trackid where k1.keyword like '% keyword %' and use index on all 3 tables on the trackid fields and keyword field. I could get my result with such query in 2 secs for about 1.5million records. another way round is to create tmp tables where : table A will contain info relevant to condition1 table B will contain info relevant to condition2 table C will contain info from original table and matching results in table A B. This one may be slower. Regards, Velen - Original Message - From: sbrattla [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 6:41 PM Subject: Any better ways that LEFT JOIN? Hi, My scenario is as described below, and i am in search of a better way to run a query for that scenario. Any bright ideas are more than welcome! I have a table keyword (which contains keyword_id, keyword_trackid, keyword_title) and a table track (which contains track_id, track_title). Obviously, i have a number of keywords describing each track. I need to search for a keyword, and then get all tracks which this keyword describes. However, i also need all other keywords which describes the found tracks. I currently solve this with the following query: SELECT K1.keyword_id, K1.keyword_trackid, K2.keyword_keyword FROM keyword K1 LEFT JOIN track T1 ON T1.track_id = K1.keyword_trackid LEFT JOIN keyword K2 ON T1.track_id = K2.keyword_trackid WHERE (K1.keyword_keyword LIKE '%keyword%'); I am not really worried about full text search right now, as i am more worried about that the query itself most likely will take ages when the number of keywords gets bigger. I could probably also do this with programming, but then again i would have to get the list of all keywords from the database...which could be something like 5 million keyword - track relations. So, if you would happen to know how this query can be optimised, then i'd very much appreciate that! -- View this message in context: http://www.nabble.com/Any-better-ways-that-LEFT-JOIN--tp17106190p17106190.ht ml 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] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: Andy Wallace [EMAIL PROTECTED] To: mysql list mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a last_visit column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: question about update/join query
Ok, I think I need to try to restate my problem. I have an event_log table, which tracks events (!). Basic structure is: table: event_log event_time timestamp event_idint user_id int and my user table: table user user_id int namevarchar(50) last_visit datetime I want to run a query that updates the last_visit column of user with the MAX(event_time) row for which the user_id's match, but only if I find an event: update user U set U.last_visit = (select max(L.event_time) from event_log L where L.user_id = U.user_id I would like to do it without a subquery, I thought that the multiple table syntax for UPDATE would do it, but I can't wrap my head around it. UPDATE user U, event_log L SET U.last_visit = MAX(L.event_time) WHERE U.user_id = L.user_id GROUP BY L.event_time I guess the main question is - CAN I do this? Or will I have to resort to either a subquery, or external processing? thanks, andy Martin wrote: Hi Andy- the MAX function needs group by for the column for which it calculating max value as in this example (select MAX(EL.event_time) // from event_log EL // where EL.enduser_acnt = E.enduser_acnt //Inner join forces selection on columns which contain non null values as seen here from event_log AS EL INNER JOIN Event AS E ON EL.enduser_acnt = Event.enduser_anct group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message - From: Andy Wallace [EMAIL PROTECTED] To: Martin [EMAIL PROTECTED] Cc: mysql list mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: question about update/join query I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: Andy Wallace [EMAIL PROTECTED] To: mysql list mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a last_visit column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb disabled
It could be the problem with permissions or sizes of iblog or ibdata files Can you try this back up the iblog and ibdata files and move it to some other location from /data/mysql and restart mysql to see if it shows innodb up on show engines, by doing this atleast u can isolate the problem is not with iblog or ibdata files -srini Krishna Chandra Prajapati wrote: Hi, What ever you have written i did the same thing, Yet innodb get disabled, On Tue, May 6, 2008 at 11:24 AM, Rolando Edwards [EMAIL PROTECTED] wrote: If you just created the /data/mysql folder and moved the ib* files to that folder from /var/lib/mysql, you may have to contend with the internal data dictionary with ibdata1. You do the following: 1) Put the mysql data back into /var/lib/mysql 2) Configure my.cnf to set datadir=/var/lib/mysql 3) Restart mysqld Once you put it back the way it was, then 4) mysqldump -h... -u... -p... --single-transaction --all-databases --routines --triggers AllData.sql 5) Make /data/mysql TOTALLY EMPTY. 6) Configure my.cnf to set datadir=/data/mysql 7) Restart mysqld At this point, mysqld will regenerate a new ibdata1 file with a clean internal data dictionary. 8) mysql -h... -u... -p... AllData.sql All data get put into this new data folder and will reload the internal data dictionary elements pertaining to its new location. Moving MyISAM is simple. Moving InnoDB is a little more work but doing it this way ensures ibdata1 is in a proper state. -Original Message- From: Juan Eduardo Moreno [mailto:[EMAIL PROTECTED] Sent: Tuesday, May 06, 2008 9:12 AM To: Krishna Chandra Prajapati Cc: mysql Subject: Re: innodb disabled Hi, Can you send the error log file of mysql server in your machine?. Regards, Juan On 5/6/08, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi Juan, I have verified directory permissions, its shown below. linux76:~# ls -l /data/mysql/ total 535088 drwx-- 2 mysql mysql 20480 2008-05-04 23:44 dip -rw-rw 1 mysql mysql 10485760 2007-12-23 01:21 ibdata1 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile0 -rw-rw 1 mysql mysql 268435456 2008-01-13 01:17 ib_logfile1 drwx-- 2 mysql root 4096 2008-03-28 02:58 mysql In error log file there is error. debian-sys-maint error The above error come while starting mysql for the first time. Then i have given the permissions for debian sys-maint from /etc/mysql/debian.cnf. After that restart mysql don't gives any error. still innodb is disabled. Thanks, Prajapati On Tue, May 6, 2008 at 7:03 AM, Juan Eduardo Moreno [EMAIL PROTECTED] wrote: Hi, The InnoDB engine was disable because some ib_log files or Ibdata files, InnoDB can´t read. In some cases, could be a directory permissions or some error in creation of ib_logfiles when database is started. If you want , try to see in the error log file generated in order to see more details. If you don´t understand nothing in the error log file, please send this errors to the list. Regards, Juan On Tue, May 6, 2008 at 5:27 AM, Krishna Chandra Prajapati [EMAIL PROTECTED] wrote: Hi all, I have change the data directory from /var/lib/mysql to /data/mysql directory and adding innodb_file_per_table. By doing this innodb storage engine get dissabled. my.cnf doesn't contains #skip-innodb. Is it's adding will enable or not HOW CAN I ENABLE INNODB mysql show engines; ++--++ | Engine | Support | Comment| ++--++ | MyISAM | DEFAULT | Default engine as of MySQL 3.23 with great performance | | MEMORY | YES | Hash based, stored in memory, useful for temporary tables | | InnoDB | DISABLED | Supports transactions, row-level locking, and foreign keys | | BerkeleyDB | NO | Supports transactions and page-level locking | | BLACKHOLE | NO | /dev/null storage engine (anything you write to it disappears) | | EXAMPLE| NO | Example storage engine | | ARCHIVE| YES | Archive storage engine | | CSV| YES | CSV storage engine | | ndbcluster | DISABLED | Clustered, fault-tolerant, memory-based tables | | FEDERATED | YES | Federated MySQL storage engine | | MRG_MYISAM | YES | Collection of identical MyISAM tables | | ISAM | NO | Obsolete storage engine|
Re: question about update/join query
Ok, I think I need to try to restate my problem. I have an event_log table, which tracks events (!). Basic structure is: table: event_log event_time timestamp event_idint user_id int and my user table: table: user user_id int namevarchar(50) last_visit datetime I want to run a query that updates the last_visit column of user with the MAX(event_time) row for which the user_id's match, but only if I find an event: update user U set U.last_visit = (select max(L.event_time) from event_log L where L.user_id = U.user_id) I would like to do it without a subquery, I thought that the multiple table syntax for UPDATE would do it, but I can't wrap my head around it. UPDATE user U, event_log L SET U.last_visit = MAX(L.event_time) WHERE U.user_id = L.user_id GROUP BY L.event_time I guess the main question is - CAN I do this? Or will I have to resort to either a subquery, or external processing? thanks, andy Martin wrote: Hi Andy- the MAX function needs group by for the column for which it calculating max value as in this example (select MAX(EL.event_time) // from event_log EL // where EL.enduser_acnt = E.enduser_acnt //Inner join forces selection on columns which contain non null values as seen here from event_log AS EL INNER JOIN Event AS E ON EL.enduser_acnt = Event.enduser_anct group by EL.event_time); // group by EL.enduser_acnt); HTH Martin - Original Message - From: Andy Wallace [EMAIL PROTECTED] To: Martin [EMAIL PROTECTED] Cc: mysql list mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 6:21 PM Subject: Re: question about update/join query I want to put only the max date into the field... I was thinking that max was a group function, but now that I type that out loud, perhaps I'm not using all the neurons available... hmmm... thanks, andy Martin wrote: Hi Andy- Is there a reason why you are using Query group by clause in UPDATE statement? M - Original Message - From: Andy Wallace [EMAIL PROTECTED] To: mysql list mysql@lists.mysql.com Sent: Wednesday, May 07, 2008 1:07 PM Subject: Re: question about update/join query Clarification: I DON'T want to update the last_visit field if there is no matching event record... I managed to get this to sort of work: update enduser E set E.last_visit = (select MAX(EL.event_time) from event_log EL where EL.enduser_acnt = E.enduser_acnt group by EL.enduser_acnt); but it updated the last_visit field to the default value if it found no matching event_log row... which I don't want to happen. thanks, andy Andy Wallace wrote: Hey all - I have two tables - an event_log table, and a user table. There is a last_visit column in the user table, and I want to update it from the event_log with the most recent event timestamp. And I want to do it without a subquery, eventually, both these tables will be pretty large, especially the event_log. I tried this: update enduser E join event_log EL on EL.enduser_acnt = E.enduser_acnt set E.last_visit = MAX(EL.event_time) group by EL.enduser_acnt but I get an error on the group by. The pertinent tables sections are: table event_log event_time TIMESTAMP enduser_acnt int table enduser enduser_acnt int last_visit datetime Any help appreciated. Thanks... andy -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- Andy Wallace - CISData - IDX Slave AIM: acmwallace [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
error while creating a copy of a table
Hi All, I am getting below error. This is the only table of INNODB ENGINE, the default storage engine is MyISAM mysql create table tmp_dc as select gid,siteid,has_gy,starts from dc_tc; ERROR 1206 (HY000): The total number of locks exceeds the lock table size how do i fix this error.
