Re: innodb disabled
Hi Nobert, I verified the syslog file and didn't found any error. Krishna Chandra Prajapati On Wed, May 7, 2008 at 8:20 AM, Norbert Tretkowski [EMAIL PROTECTED] wrote: 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] -- Krishna Chandra Prajapati MySQL DBA, Ed Ventures e-Learning Pvt.Ltd. 1-8-303/48/15, Sindhi Colony P.G.Road, Secunderabad. Pin Code: 53 Office Number: 040-66489771 Mob: 9912924044 URL: ed-ventures-online.com Email-id: [EMAIL PROTECTED]
Re: innodb disabled
Thanks a lot to all of you. On Wed, May 7, 2008 at 8:51 PM, Srini [EMAIL PROTECTED] wrote: 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 |
[ excel to mysql ]
Hi! How i do for transfer data from excel file to mysql? Some example? Thanks
RE: Order Problem
Perfect. It worked just how I wanted. Thanks for your help. Neil Date: Wed, 7 May 2008 19:54:39 +0200 To: [EMAIL PROTECTED] Subject: Re: Order Problem From: [EMAIL PROTECTED] Hi, You should look at the `FIND_IN_SET` function here: http://dev.mysql.com/doc/refman/5.0/en/string-functions.html#function_find-in-set Your query could look like: SELECT ProductID FROM Products WHERE Enabled= ' Yes' AND ProductID IN(varProductID) ORDER BY FIND_IN_SET(ProductID, varProductID); Haven't tested it, though... Take care, Aleksandar _ Discover and Win with Live Search http://clk.atdmt.com/UKM/go/msnnkmgl001007ukm/direct/01/
Re: [ excel to mysql ]
Save the excel file as a CSV text file, then use LOAD DATA INFILE command to import. See the mysql manual for usage and example. On May 8, 2008, at 4:49 AM, Lord Gustavo Miguel Angel wrote: Hi! How i do for transfer data from excel file to mysql? Some example? Thanks -- 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
Brent - thanks, now I understand. My aversion to subqueries is for performance against a very large table, which event_log promises to be. I hope to minimize this with some time boundaries on that table (where event_time between x and y). But thanks, I'll play with that. And thanks Martin for the start. andy Brent Baisley wrote: You can do it in a single UPDATE statement, but you do need a form of a subquery. Why the aversion of a subquery? The simplest approach is to first get what you want using a SELECT statement. Then change SELECT to UPDATE and add your SET statement. In your case there is a little twist because you have to use a group by to get the max. So first get the users and latest event time, as Martin stated. SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id That can be used to create a virtual table you can join against for your update statement. UPDATE user JOIN (SELECT user_id, MAX(event_time) maxtime FROM event_log GROUP by user_id) AS eMax ON user.user_id=eMax.user_id SET last_visit=maxtime WHERE user.user_id=eMax.user_id That should do it, although I don't think you need the WHERE clause. That will do a full table scan on the event_log table, which can be very bad if it is large. You can work around this by compiling groups of users at a time. This will require querying the users and for the group of users and joining on the event_log table. Brent Baisley Systems Architect On Thu, May 8, 2008 at 12:26 AM, Andy Wallace [EMAIL PROTECTED] wrote: 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.
LEFT JOIN without temporary table?
I want to get a list of all products that either exist or do not exist. In other words, if prod.prod_pub_prod_id exists then I want to report its prod_num; if it doesn't, then I want to report the product ID with a blank value for the prod_num. I'm currently using a temporary table to do this, but it feels inelegant to me. Is there any way to do this without a temporary table? Here's what I'm doing now: DROP TEMPORARY TABLE IF EXISTS prod_exists; CREATE TEMPORARY TABLE `giiexpr_db`.`prod_exists` ( `prod_pub_prod_id` VARCHAR( 255 ) NOT NULL , PRIMARY KEY ( `prod_pub_prod_id` ) ) ENGINE = MYISAM; INSERT INTO prod_exists VALUES (MCP-1018), (MCP-1024), ... (MCP-1031) ; SELECT prod_exists.prod_pub_prod_id, IF(prod.prod_num IS NOT NULL,prod.prod_num,) as GII_prod_ID FROM prod_exists LEFT JOIN prod ON prod_exists.prod_pub_prod_id = prod.prod_pub_prod_id WHERE (prod.prod_discont = 0 OR prod.prod_discont IS NULL) ORDER BY prod_exists.prod_pub_prod_id; This works fine, but is there a better way to do it? Regards, Jerry Schwartz The Infoshop by Global Information Incorporated 195 Farmington Ave. Farmington, CT 06032 860.674.8796 / FAX: 860.674.8341 www.the-infoshop.com www.giiexpress.com www.etudes-marche.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: innodb disabled
Is your problem fixed? what was the problem -srini Krishna Chandra Prajapati wrote: Thanks a lot to all of you. On Wed, May 7, 2008 at 8:51 PM, Srini [EMAIL PROTECTED] wrote: 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
Re: connectors: per session persistent connection (PHP)
On May 7, 2008, at 4:36 AM, Sebastian Mendel 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 How would a persistent connection save any of that? Suppose the script that previously used the connection reset any or all of those things? -- Paul DuBois, MySQL Documentation Team Madison, Wisconsin, USA www.mysql.com -- 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)
Paul DuBois schrieb: On May 7, 2008, at 4:36 AM, Sebastian Mendel 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 How would a persistent connection save any of that? Suppose the script that previously used the connection reset any or all of those things? i do not fully understand, why should the script reset these things? i do not talk of a global persistent connection, every session should have it's own persistent connection (if requested). -- 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
The problem happened on production server. So i will be doing all the testings later. Thanks a lot to all of you On Thu, May 8, 2008 at 2:23 PM, Srini [EMAIL PROTECTED] wrote: Is your problem fixed? what was the problem -srini Krishna Chandra Prajapati wrote: Thanks a lot to all of you. On Wed, May 7, 2008 at 8:51 PM, Srini [EMAIL PROTECTED] wrote: 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 | |
