URGENT: Change Default Location of where Database Files get written?
I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
RE: URGENT: Change Default Location of where Database Files get written?
Add: datadir=/path/to/datadir/mysql to your my.cnf file and restart mysql. -Original Message- From: Tina Matter [mailto:ti...@umich.edu] Sent: Friday, May 13, 2011 8:22 AM To: mysql@lists.mysql.com Subject: URGENT: Change Default Location of where Database Files get written? I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=dch...@invenda.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
On Fri, May 13, 2011 08:21, Tina Matter wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 Since your are on a linux box, the simplest method is to create the database, but no tables and then replace the directory with a symbolic link to the desired location. Make sure the permissions at the new directory match that of the other directories. If you are using innodb you will need to set it to use separate files for each table. Hope this helps. -- William R. Mussatto Systems Engineer http://www.csz.com 909-920-9154 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
On Fri, May 13, 2011 at 9:21 AM, Tina Matter ti...@umich.edu wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location: /opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename http://lmgtfy.com/?q=mysql+location+of+database+files -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: URGENT: Change Default Location of where Database Files get written?
There absolutely is; there is a configuration file belonging to MySQL named `my.cnf`. It can exist in many places and there's a hierarchal order of precedense. The most common of which is /etc/my.cnf. Within this file you may specify the `datadir` option to identify location you wish your data to reside. This is a static variable that can't be altered whilst the server is in motion and there are things you may need to do before considering changing this value. There is a wealth of documentation on this configuration file that can be found at... http://dev.mysql.com/doc/refman/5.5/en/mysqld-option-tables.html HTH Andy On Fri, May 13, 2011 at 4:21 PM, Tina Matter ti...@umich.edu wrote: I have a MySQL question that I'm hoping someone can help answer. We have a linux machine which has MySQL 5.5.8 installed. It is currently installed in this location:/opt/mysql When creating a new database, a folder (with the name of the databas) gets created in this location: /opt/mysql/data Is there any way to change the location of where data is stored? The database that I need to create is going to have over a billion records in it, so it needs to be in a specific place. I want the database folder to get created here: /science/databases/databasename Thanks for any help. Tina -- Tina Matter Web Applications Developer University of Michigan Department of Epidemiology 1415 Washington Heights, Suite 4605 Ann Arbor, MI 48109 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=eroomy...@gmail.com
Hello-NEED ASSISITANCE URGENT!!!!
I wondered if you can get me started on selling MANY mailing list to potential clients who are in need of millions of e-mail names and addresses. This has collected for some time. I am needing to start a business doing this Here in the United States and out of country yet I am not sure how to go about it. I know you need a large server to do this. I just don't know of a good one or how to move forward from there. Can you assist me? I will pay you for your time. A partner would be nice too.Thank you Mary Lisa Gara P. S. I am very serious about doing this! Again thank you much:)
cannot alter table - rather urgent
I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. -- Hervé Kempf: Pour sauver la planète, sortez du capitalisme. - Phil Jourdan --- p...@ptahhotep.com http://www.ptahhotep.com http://www.chiccantine.com/andypantry.php -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
On Thu, May 21, 2009 at 11:06 PM, PJ af.gour...@videotron.ca wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. We will need a little more information. The table looks sound but is clearly designed to link 2 other tables. If you are failing to insert or update, it seems likely that it is because the data is absent in the foreign tables. Can you confirm? Because without that forgeign data, these rows are pretty meaningless. What is it you are trying to do? -- - michael dykman - mdyk...@gmail.com - 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/mysql?unsub=arch...@jab.org
Re: cannot alter table - rather urgent
I cannot insert values into the tables What is the error message? Has the smallint key run out of values? and I cannot alter or delete the primary key (which should not exist) Eh? Without a PK, it ain't a table. or delete the foreign keys nor remove the constraint. G search doesn't help. If the pk referenced by the fk is full, I think you need to drop the fk, then drop the pk in the table referenced by the fk, then recreate that pk as an int, then recreate the fk. PB - PJ wrote: I have a seemingly impossible situation. I cannot insert values into the tables and I cannot alter or delete the primary key (which should not exist) or delete the foreign keys nor remove the constraint. G search doesn't help. CREATE TABLE `book_categories` ( `bookID` smallint(6) unsigned NOT NULL, `categories_id` int(2) unsigned NOT NULL, PRIMARY KEY (`bookID`,`categories_id`), KEY `fk_book_categories_books` (`bookID`), KEY `fk_book_categories_categories` (`categories_id`), CONSTRAINT `book_categories_ibfk_1` FOREIGN KEY (`book_id`) REFERENCES `book` (`id`) ON DELETE CASCADE ON UPDATE CASCADE ) ENGINE=InnoDB DEFAULT CHARSET=latin1 Anybody out there still up? I'm rather desperate to fix this this evening... Thanks in advance. No virus found in this incoming message. Checked by AVG - www.avg.com Version: 8.5.339 / Virus Database: 270.12.36/2126 - Release Date: 05/21/09 06:22:00
URGENT! up2date -u deleted mysql...safest way to recover on production server
Decided to run up2date -u I noticed that our website was tossing off mysql errors. Quickly realized that mysql was down. Went to restart but it couldn't find mysqld_safe, mysqld, mysqladmin, etc. I used locate and it couldn't find the binaries anywhere...it appears that up2date -u had somehow uninstalled MySQL 4.0.27. I have the rpms... The /var/lib/mysql/mysite database directory is fine as is the /var/lib/mysql/mysql database directory with all the permissions (columns_priv.MYD columns_priv.MYI columns_priv.frm db.MYD db.MYI db.frm func.MYD func.MYI func.frm host.MYD host.MYI host.frm tables_priv.MYD tables_priv.MYI tables_priv.frm user.MYD user.MYI user.frm) What's the safest way to reinstall the rpms and have it use/find/whatever the permissions database without it creating a new one? Thanks...guess I'm drinking coffee until I get this done! -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT! up2date -u deleted mysql...safest way to recover on production server
Glyn Astill wrote: I'd back up the data directories then try and then re-install mysql (sorry, I know little about red hat and it's package management). It shouldn't overwrite your data if it's already present anyway. Great. Thought that would be the case, but without sleep, I wasn't sure. :-) Worked fine. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - urgent
Hello All,
I set up replication between 2 servers recently. I just need one db to be
replicated and the SHOW SLAVE STATUS shows this:
Relay_Master_Log_File: gyana01-bin.02
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB: tallydb,tallydb
Now the problem is that there is another db named tallydbopextblob and the
tables from this db are also getting replicated as shown in the below line.
Last_Error: Error 'Table 'tallydbopextblob.TBL_EVAL_PITEM_OP_EXT_DETAILS'
doesn't exist' on query. Default database: 'tallydb'. Query: 'INSERT INTO
tallydbopextblob.TBL_EVAL_PITEM_OP_EXT_DETAILS(
FLD_OP_INPUT_DATA,FLD_OP_INDEX_DATA1,FLD_OP_INSTANCE_ID)
VALUES('type=FPCONNECTsource=TALLYrequest_type=ACTIVATIONoffline_flag=0serial=372123675major_version=7minor_version=2major_release=3minor_release=14build_num=rel7.2_3.14_2007-05-21_19.33platform=WINos=WINuser_name=serverhost_name=SERVERlic_ver=2fingerprint63=1mQ0nE8HkaBlNRg==aa,3GAwmgzLMbCGNJyMyaa,3GAwmgzLMbCGNJyMyaa,2NDkxODk0MjUyAA==finger_print=5qZEKE5HgchxNpnOZ3GyZaaserver_id=0activation_code
...
Why is this happening?
Thanks regards,
Ratheesh
Replication - urgent
Hello All, I need to add a couple of more tables to the list of tables to be replicated from a particular db in the salve my.cnf. Can this be done without restarting the mysql server on the slave? Thanks regards, Ratheesh
Replication - urgent
Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks regards, Ratheesh
Re: Replication - urgent
Running this statement on the master should do it: DROP TABLE table1, table2...; That will remove the tables. Baron Ratheesh K J wrote: Hello all, I cannot afford to stop my slave server. I have list of tables of the master that are being replicated on the slave. Now I want to remove a couple of tables from this list without affecting the master and slave. How is this possible? Thanks regards, Ratheesh -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: [Replication] - urgent
Thanks, It helped me a lot. I wanted to know 1.. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? 2.. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? 3.. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? 4.. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? Thanks regards, Ratheesh - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Tuesday, October 02, 2007 4:16 PM Subject: Re: [Replication] - urgent Ratheesh K J schrieb: Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks regards, Ratheesh You have 2 options: 1. on the slave, enter SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; and then SLAVE START; on the slave. This skips the upcoming entry in the binlog which is the create table command that causes your problem. 2. if you don't have any data in the table on the slave, just drop the table and do a slave start;, it will then create the table again as this is the next command in the binlog. Remember: never write on the slave without knowing what you do and you'll be happy with your replication ;) Jan
Re: [Replication] - urgent
Ratheesh K J schrieb: Thanks, It helped me a lot. I wanted to know 1. what are the various scenarios where my replication setup can fail? (considering even issues like network failure and server reboot etc). What is the normal procedure to correct the failure when something unpredicted happens? You should first read the right parts of the manual at https//dev.mysql.com/doc before asking such questions. Basically: -Use good hardware with ECC-RAM and RAID-Controllers in order to minimize trouble with faulty hardware. -Never write on the slaves without knowing what this could do to your replication setup -Watch the diskspace and make sure it's always enough space for the binlogs. Otherwise you might end up with half-written binlogs on either the slave or master because of a full disk which can cause trouble and some work to get it up and running again. When a master goes down or network connection is lost, the slave automatically tries to reconnect once a minute or so. Restarting the master or exchanging some network equipment is no problem. When the slave reboots, it tries to reconnect on startup, too. This is out-of-the-box-behaviour. You can modify it in the my.cnf (i.e. use the skip-slave-start option etc) 1. What are the scenarios where the SQL THREAD stops running and what are the scenarios where the IO THREAD stops running? SQL thread stops when it can't run a SQL-Query from the binlogs for any reason, as you have experiences when the table already existed. The IO-Thread only stops when it has an error reading a binlog from the master. When its only a lost connection, it automatically reconnects. Other problems (i.e. unable to read a binlog) should never happen as long a you don't delete binlogs on the master that have not yet been copied over to the slave by the io-thread (show master status and show slave status commands and their output) or you have faulty hardware (io_errors on the harddisk or such things) 1. Does SQL_SLAVE_SKIP_COUNTER skip the statement of the master binlog from being replicated to the slave relay log OR Has the statement already been copied into the slave relay log and has been skipped from the relay log? it skips the entry on the local copy of the binlog. The IO-Thread replicates the whole binlog and the sql-thread skips an entry in it when you use sql_slave_skip_counter 1. How do I know immediately that replication has failed? ( have heard that the enterprise edition has some technique for this )? watch the logfile, it is written there. Or run a cronjob once a minute with something like mysql -e 'show slave status\G' |grep '_Running:' /dev/null || bash my_alarm_script_that_sends_mail_or_whatever.sh regards Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
[Replication] - urgent
Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks regards, Ratheesh
Re: [Replication] - urgent
Ratheesh K J schrieb: Hello all, I issued a create table statement on the master for a table which was not present on the master but present on the slave. I did this purposely to see the error on slave. I am a newbie to replication. Now when i see SLave status on the slave machine it shows that the SQL Thread has stopped. When I start the SQL thread it does not start and gives the error message that the table exists. How do i correct this and how do I calculate the next position that the slave must start executing from the relay log. Is there any article on MySQL replication that tells me how to deal when errors occur. Thanks regards, Ratheesh You have 2 options: 1. on the slave, enter SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; and then SLAVE START; on the slave. This skips the upcoming entry in the binlog which is the create table command that causes your problem. 2. if you don't have any data in the table on the slave, just drop the table and do a slave start;, it will then create the table again as this is the next command in the binlog. Remember: never write on the slave without knowing what you do and you'll be happy with your replication ;) Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: mysql_history on windows ?
Hi, I need to track the mysql commands executed on mine system , I am running windows with mysql 5.x I know there is a file .mysql_history and hopes there must be a similar file on windows too. Pl. help me urgently, Thanks, -- Regards, Abhishek Jain
need help urgent
How to retrieve data from three consecutive tuples until th end of the database.Such that suppose the field name is ID and I want to retrieve data from another field LAT such that at a time LAT values for ID's 1,2,3 are taken then for 3,4,5 then for 4,5,6 so on till table ends. -- View this message in context: http://www.nabble.com/need-help-urgent-tf3491135.html#a9749875 Sent from the MySQL - General mailing list archive at Nabble.com.
Re: need help urgent
Hi Rakaha, You have a cursor , select id from table_name and then have a loop where in you select values of LTA for each id got from the above cursor, close the loop once all the ID have been processed. regards anandk On 3/30/07, raksha [EMAIL PROTECTED] wrote: How to retrieve data from three consecutive tuples until th end of the database.Such that suppose the field name is ID and I want to retrieve data from another field LAT such that at a time LAT values for ID's 1,2,3 are taken then for 3,4,5 then for 4,5,6 so on till table ends. -- View this message in context: http://www.nabble.com/need-help-urgent-tf3491135.html#a9749875 Sent from the MySQL - General mailing list archive at Nabble.com.
Re: need help urgent
Or you could also do this. SELECT LTA FROM TABLE A WHERE ID IN (SELECT B.ID FROM TABLE B); i hope this what your looking at, please let us know. regards anandkl On 3/30/07, Ananda Kumar [EMAIL PROTECTED] wrote: Hi Rakaha, You have a cursor , select id from table_name and then have a loop where in you select values of LTA for each id got from the above cursor, close the loop once all the ID have been processed. regards anandk On 3/30/07, raksha [EMAIL PROTECTED] wrote: How to retrieve data from three consecutive tuples until th end of the database.Such that suppose the field name is ID and I want to retrieve data from another field LAT such that at a time LAT values for ID's 1,2,3 are taken then for 3,4,5 then for 4,5,6 so on till table ends. -- View this message in context: http://www.nabble.com/need-help-urgent-tf3491135.html#a9749875 Sent from the MySQL - General mailing list archive at Nabble.comhttp://nabble.com/ .
[urgent] - Problem with index_merge
Hello all, I have struck with a big problem with MySQL 5.0.22 server on RHEL 3. After an upgradation from MySQL 4.1.11 to MySQL 5.0.22 almost all the queries are struggling to execute and the DB server is clogged. Below is an example of what is happening. This query usd to execute very fast on MySQL 4.1.11 (in about less than 10 seconds). But on MySQL 5.0.22 it is taking an eternity. Running an EXPLAIN shows that an index_merge is being used as shown below in the EXPLAIN result. On MySQL 4.1.11 the the PRIMARY key was being used as the index as shown in the second query(The same query with FORCE index on PRIMARY). I am really confused as to why it is taking such a long time to execute when an index_merge is being used. As we can see that the number of row scans using index_merge is way too less when compared to the second query. Why is the first query so slow when compared to the second one even if the number of rows to be examined is too less in the former? Is this a bug in index_merge? And we have atleast 15 such queries always running on the system. The server is clogged !! Query with index_merge # Execution time : 53 seconds EXPLAIN SELECT SUM(1) AS ELE13, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14, SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) AS ELE15, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17, SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18, SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19, SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE21, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE22 FROM TBL_FORUMS_MSG_MAIN TFMM WHERE TFMM.FLD_ACC_ID in (6) AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_MSG_ID 0 AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ADD_DATE_TIME = '2007-01-23 00:00:00' AND TFMM.FLD_ADD_DATE_TIME ='2007-01-23 23:59:00' ORDER BY TFMM.FLD_ADD_DATE_TIME ; - *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_PARENT_ID,FLD_ADD_DATE_TIME,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG key: FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG key_len: 4,2,2,2,2,2 ref: NULL rows: 10170 Extra: Using intersect(FLD_PARENT_ID,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_ESCALATED_FLAG,FLD_BOUNCED_MAIL_FLAG); Using where - Query with FORCE INDEX(PRIMARY) # Execution time : 13 seconds EXPLAIN SELECT SUM(1) AS ELE13, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 1, 1, 0 ) ) AS ELE14, SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) , 1, 0 ) ) AS ELE15, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4, 1, 0 ) ) AS ELE16, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3, 1, 0 ) ) AS ELE17, SUM( IF( TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE18, SUM( IF( TFMM.FLD_ISSUE_CLOSED != 3 AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 0, 1, 0 ) ) AS ELE19, SUM( IF( ( TFMM.FLD_ISSUE_CLOSED = 2 OR TFMM.FLD_ISSUE_CLOSED = 5 ) AND TFMM.FLD_ASSIGNED_TO = 134 AND TFMM.FLD_USR_SEEN_FLAG = 1, 1, 0 ) ) AS ELE20, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 4 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE21, SUM( IF( TFMM.FLD_ISSUE_CLOSED = 3 AND TFMM.FLD_ASSIGNED_TO = 134, 1, 0 ) ) AS ELE22 FROM TBL_FORUMS_MSG_MAIN TFMM FORCE INDEX(PRIMARY) WHERE TFMM.FLD_ACC_ID in (6) AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_MSG_ID 0 AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ADD_DATE_TIME = '2007-01-23 00:00:00' AND TFMM.FLD_ADD_DATE_TIME ='2007-01-23 23:59:00' ORDER BY TFMM.FLD_ADD_DATE_TIME ;
Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type: eq_ref possible_keys: PRIMARY key: PRIMARY key_len: 4 ref: tallydb.TFMM.FLD_ASSIGNED_TO rows: 1 Extra: NULL *** row 3 *** table: TFMOT type: ref possible_keys: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - From: Alex Arul To: Ratheesh K J Sent: Tuesday, January 23, 2007 11:57 AM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
: FLD_MSG_ID,FLD_OP_ID,FLD_OP_DATE_TIME,FLD_LAST_FLAG key: FLD_MSG_ID key_len: 4 ref: tallydb.TFMM.FLD_MSG_ID rows: 1 Extra: Using where - Original Message - *From:* Alex Arul [EMAIL PROTECTED] *To:* Ratheesh K J [EMAIL PROTECTED] *Sent:* Tuesday, January 23, 2007 11:57 AM *Subject:* Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently you threads connected is 38 but your thread cache has only 8. So please do bump it up. Also generate explain plan on both versions of mysql and provide create table statement of the tables and the query. FYI, index_merge_optimization is used when more than one index can be used for execution. Thanks Alex On 1/23/07, Ratheesh K J [EMAIL PROTECTED] wrote: Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
J Cc: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 12:20 PM Subject: Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently the monitor the threads-connected variable over a period of time and calcuate the value. you can even look at max used connections status variable and allot accordingly. BTW, is you application using demand based connections or connection pooling ? If it is using connection pooling then bumping thread-cache might not help. Please provide create table statement of the tables in question also. Thanks Alex On 1/23/07, Ratheesh K J [EMAIL PROTECTED] wrote: Thanks, By how much should I be increasing the thread_cache? currently it is 8... Currently I can provide the EXPLAIN result of a query using index_merge on MySQL 5.0.22. EXPLAIN SELECT IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) AS ELE1 , TFMM.FLD_ASSIGNED_TO AS KEY_ID, SUM(1) AS ELE2, SUM( IF(TFMM.FLD_ESCALATED_FLAG= 0 , 1 , 0) ) AS ELE3, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE4, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 0 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE5, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) ) AS ELE6,SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND ( TFMM.FLD_ISSUE_CLOSED IN ( 2, 4,5,1 ) ) , 1, 0) ) AS ELE7, SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 AND TFMM.FLD_ISSUE_CLOSED = 3, 1, 0) ) AS ELE8, ROUND(( SUM( IF(TFMM.FLD_ESCALATED_FLAG = 1 , 1, 0) )/SUM(1)) * 100 ,2 ) AS ELE9 FROM TBL_FORUMS_MSG_MAIN TFMM LEFT JOIN TBL_ADMIN_EMP_MASTER TAEM ON TAEM.FLD_EMP_ID = TFMM.FLD_ASSIGNED_TO INNER JOIN TBL_FORUMS_MSG_OP_TRACK TFMOT ON ( TFMM.FLD_MSG_ID=TFMOT.FLD_MSG_ID AND TFMOT.FLD_OP_ID=15 AND TFMOT.FLD_LAST_FLAG=1 ) WHERE TFMM.FLD_ACC_ID IN ( 6, 375 ) AND TFMM.FLD_MARK_AS_DELETED = 0 AND TFMM.FLD_BOUNCED_MAIL_FLAG = 0 AND TFMM.FLD_BLOCK_STATE = 0 AND TFMM.FLD_PARENT_ID = 0 AND TFMM.FLD_ASSIGNED_TO IN ( 935,805,563,543,1352,670,571,530,655,577,355,885,392,155,1579,693,1577,509,199,770,1535,78,54,993,594,557,132,859,99,1557,645,527,79,181,1520,200,1350,1534,1591,545,70,191,1550,189,726,40,228,97,196,860,303,1321,394,363,1412,597,1013,1377,1250,1299,3,301,756,170,1553,1578,1343,953,593,250,600,1552,494,311,146,664,589,631,495,4,1254,678,511,931,1020,410,592,822,933,1531,1507,858,453,1257,555,897,352,188,546,1544,291,1529,370,765,963,356,1303,1328,354,414,1581,1030,382,1356,1521,227,396,1333,591,1249,760,1334,1034,51,80,1276,794,145,295,934,544,165,1594,886,929,558,685,880,831,1592,882,320,566,174,796,1593,5,1361,1522,435,388,951,1362,369,806,20,1336,330,77,907,754,507,1330,1364,1,202,1501,289,1296,1378,1061,1500,952,1439,1369,1358,373,1548,294,338,30,1351,1575,728,207,1558,406,837,210,970,620,387,450,1586,38,1227,460,455,1347,841,386,318,130,492,961,590,229,463,284,1380,1580,422,362,1337,581,1490,568,950,1083,960,1329,825,532,404,936,1251,552,1089,1585,1225,708,1564,817,260,372,965,305,456,847,192,1465,962,1523,1590,745,180,1540,753,585,890,1537,1099,225,750,230,7,413,1554,578,572,820,1549,883,810,1105,1403,423,1524,969,542,286,797,1582,1301,384,930,308,854,742,1107,1108,1555,1338,1562,947,673,1506,417,236,798,1576,63,27,1210,371,1485,82,272,1274,529,1277,381,1342,689,185,1118,235,1120,37,598,724,205,946,203,608,405,610,19,958,126,307,967,1360,218,954,1525,891,116,135,75,715,1547,431,1138,879,1498,211,1140,1463,6,1528,344,956,595,91,826,1145,1584,1545,1258,443,643,632,1526,267,1530,58,945,314,1470,763,1491,1595,968,385,955,282,684,179,178,666,409,663,390,1447,1341,1546,1587,125,358,173,279,957,50,182,840,107,580,807,133,1248,892,690,513,898,365,821,325,669,121,62,827,106,219,1253,633,41,562,1489,162,101,861,561,839,153,1565,1583,395,447,217,1551,888,1574,1176,743,446,556,787,263,949,1178,1331,209,134,1505,1354,55,306,31,964,1348,850,1252,862,966,100,521,175,709,1542,942,1335,297,445,296,411,525,420,266,102,1559,418,438,109,661,804,662,1543,984,1556,1409,522,195,1471,439,341,1209,878,838,1464,881,271,36,83,1379,857,944,656,959,538,2,764,1588,672,520,503,531,462,729,528,204,201,93,677,564,426,606,855,234,676,889) AND TFMOT.FLD_OP_DATE_TIME BETWEEN '2007-01-19 00:00:00' AND '2007-01-23 23:59:59' AND TFMM.FLD_MEDIUM IN ( 1 ) AND TFMM.FLD_MSG_ID 0 GROUP BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO) ORDER BY IFNULL(TAEM.FLD_FULL_NAME, TFMM.FLD_ASSIGNED_TO): *** row 1 *** table: TFMM type: index_merge possible_keys: PRIMARY,FLD_MEDIUM,FLD_PARENT_ID,FLD_ASSIGNED_TO,FLD_MARK_AS_DELETED,FLD_ACC_ID,FLD_BLOCK_STATE,FLD_BOUNCED_MAIL_FLAG key: FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID key_len: 2,1,2,2,4 ref: NULL rows: 34468 Extra: Using intersect(FLD_BLOCK_STATE,FLD_MEDIUM,FLD_MARK_AS_DELETED,FLD_BOUNCED_MAIL_FLAG,FLD_PARENT_ID); Using where; Using temporary; Using filesort *** row 2 *** table: TAEM type
Re: Urgent - MySQL 5 - mysqld using a lot of memory consistently
Hi, Here, threads_connected is considerable and below the preset value. The threads_connected and threads_running are the good indicators to see how loaded the server is. In your case it is good numbers. So use 'iostat'/relavant utility to monitor the DB activity. Also threads_created is more, which should be low. so to average it increase the thread_cache size to some 64 or more. Ref: http://www.mysql.com/news-and-events/newsletter/2004-01/a000301.html Thanks ViSolve DB Team - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Tuesday, January 23, 2007 11:59 AM Subject: Urgent - MySQL 5 - mysqld using a lot of memory consistently Hello all, Recently we upgraded from MySQL 4.1.11 to MySQL 5.0.22. The queries are taking a lot of time to execute in the newer version. The queries which were executing within 10 secs are now taking more than 100 secs. Running an expalin on the queries showed that an index_merge optimization is being used which is a new concept in MySQL 5. My initial doubt was on this but now when I checked top it shows that mysqld is consistently using 59% of Memory and 25% of cpu even when there is no load. the SHOW STATUS command in mysql shows: Threads_created21863 Threads_cached1 Threads_connected38 Connections5784350 Running a SHOW VARIABLES shows: thread_cache_size8 It is evident that mysqld is creating a lots of threads... Could this be the problem? Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
URGENT: Question on table storage
Hello, I have the following table in mysql 5.0.20... employ == empid date_time site I am using this table to store all the websites accessed by each employees. There is no primary key on that table since the same employee will access more than 100 sites per day. If there is 100 employees then the rows inserted per day will be ~ 100*100= 1. If it grows till 2 months or 3 months then the table size will become larger. As we all know the default table size in MySQl is 4 GB. Can anyone explain will it produce issues in the future? How to overcome this? How to design a table for this scenario? Thanks, Prem
Re: URGENT: Question on table storage
On 2006-12-19 Prem wrote: I am using this table to store all the websites accessed by each employees. I hope you're aware that your country might have data privacy laws, too.. There is no primary key on that table since the same employee will access more than 100 sites per day. If there is 100 employees then the rows inserted per day will be ~ 100*100= 1. If it grows till 2 months or 3 months then the table size will become larger. As we all know the default table size in MySQl is 4 GB. Default table size maybe but a MyISAM table can grow beyond 4GB without problems. (just try it by inserting lines with a script) The general advice for handling such big tables is to either build a map table that maps long hostnames to numbers (4 = www.google.com) to safe space or to use one table per day and use a Merge-Table to access them all in one SELECT. bye, -christian- -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: URGENT: Question on table storage
Hi, I have the following table in mysql 5.0.20... employ == empid date_time site I am using this table to store all the websites accessed by each employees. There is no primary key on that table since the same employee will access more than 100 sites per day. If there is 100 employees then the rows inserted per day will be ~ 100*100= 1. If it grows till 2 months or 3 months then the table size will become larger. As we all know the default table size in MySQl is 4 GB. Can anyone explain will it produce issues in the future? How to overcome this? How to design a table for this scenario? maybe this is still correct: http://jeremy.zawodny.com/blog/archives/000796.html But perhaps you don't need it at all: 1 records * e.g. 300 byte = 300 byte/day 4GB = 4294967296 byte 4294967296 byte / 300 byte/day = 1431 days No issues for about 3 years. And after that time you could simply create a new table. hth, Tom Horstmann -- 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: Innodb log sequence error - urgent
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 - Original Message - From: Jan Kirchhoff [EMAIL PROTECTED] To: Ratheesh K J [EMAIL PROTECTED] Cc: mysql@lists.mysql.com Sent: Monday, December 11, 2006 1:25 PM Subject: Re: Innodb log sequence error - urgent Ratheesh K J schrieb: Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. You cannot just copy innodb-databases to other servers without adjusting your my.cnf: Once you created an innodb-database, you cannot change parameters like innodb_log_file_size any more. (this is explained in the manual, you should read the chapter about backing up and restoring innodb-databases) So when you copy the database to the new server, be sure to copy the settings from the my.cnf, too! Jan
Innodb log sequence error - urgent
Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 90766 log sequence number 87 2007657570 InnoDB: is in the future! Current system log sequence number 86 4025048133. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 101643 log sequence number 87 1555755135 InnoDB: is in the future! Current system log sequence number 86 4025048213. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 126123 log sequence number 87 2434816015 InnoDB: is in the future! Current system log sequence number 86 4025048253. InnoDB: Your database may be corrupt. 061211 11:41:48 InnoDB: Error: page 91391 log sequence number 87 3435504059 InnoDB: is in the future! Current system log sequence number 86 4025048310. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 7520 log sequence number 87 558983226 InnoDB: is in the future! Current system log sequence number 86 4025049185. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 70232 log sequence number 87 3176686221 InnoDB: is in the future! Current system log sequence number 86 4025049185. InnoDB: Your database may be corrupt. 061211 11:41:49 InnoDB: Error: page 9339 log sequence number 87 3426386305 InnoDB: is in the future! Current system log sequence number 86 4025051173. InnoDB: Your database may be corrupt. Its not affecting the database as such till now. 1. What is the actual problem? 2. What is the possible work around?
Re: Innodb log sequence error - urgent
Ratheesh K J schrieb: Hello all, yesterday we seperated our app server and db server. We moved our 70GB of data from our app server to a new DB server. We installed MySQL 4.1.11 on the DB server. Now the following happened. On the DB server the ibdata1 and all the databases are the old ones (which were copied from the app server). But when Mysql was installed the ib_logfile0 ib_logfile1 and ib_logfile2 were created freshly on the DB serever. Each of these log files were created with 5M size. on the app server these files were 256M in size (innodb_log_file_size = 256M). On the DB server it is (innodb_log_file_size = 5M). Today morning when I checked the error log, there seems to be a lot of error msg flowing in. 061211 11:41:47 InnoDB: Error: page 203046 log sequence number 87 3002891543 InnoDB: is in the future! Current system log sequence number 86 4025048037. InnoDB: Your database may be corrupt. You cannot just copy innodb-databases to other servers without adjusting your my.cnf: Once you created an innodb-database, you cannot change parameters like innodb_log_file_size any more. (this is explained in the manual, you should read the chapter about backing up and restoring innodb-databases) So when you copy the database to the new server, be sure to copy the settings from the my.cnf, too! Jan -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent please..
All, Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 I want to use PHP as sever side scripting. Cheers, Renish. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent please..
On Wednesday 15 November 2006 18:14, Renish wrote: Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 Please read the documentation before asking questions such as these. Thanks. http://www.php.net/manual/en/install.windows.php j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Urgent please..
Try http://www.php.net/manual/en/install.windows.php --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Renish [mailto:[EMAIL PROTECTED] Sent: Thursday, 16 November 2006 1:44 PM To: mysql@lists.mysql.com Subject: Urgent please.. All, Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 I want to use PHP as sever side scripting. Cheers, Renish. -- 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: Urgent please..
Ocourse i read the manual . I was unable to do so..ie the reason I asked...I also knew this location mysql@lists.mysql.com - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Renish [EMAIL PROTECTED] Sent: Thursday, November 16, 2006 11:20 AM Subject: Re: Urgent please.. On Wednesday 15 November 2006 18:14, Renish wrote: Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 Please read the documentation before asking questions such as these. Thanks. http://www.php.net/manual/en/install.windows.php j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Urgent please..
Hi Renish, Perhaps these might be a more appropriate forum and also get you the result you need. This being a database list (I'm sure there are php experts on here) is probably not quite the right place to be asking. One of the php lists may produce a more relevant response. http://www.php.net/mailing-lists.php Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Renish [mailto:[EMAIL PROTECTED] Sent: Thursday, 16 November 2006 2:38 PM To: Joshua J. Kugler Cc: mysql@lists.mysql.com Subject: Re: Urgent please.. Ocourse i read the manual . I was unable to do so..ie the reason I asked...I also knew this location mysql@lists.mysql.com - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Renish [EMAIL PROTECTED] Sent: Thursday, November 16, 2006 11:20 AM Subject: Re: Urgent please.. On Wednesday 15 November 2006 18:14, Renish wrote: Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 Please read the documentation before asking questions such as these. Thanks. http://www.php.net/manual/en/install.windows.php j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- 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: Urgent please..
Thank you very much..excellent support - Original Message - From: Logan, David (SST - Adelaide) [EMAIL PROTECTED] To: Renish [EMAIL PROTECTED] Cc: mysql@lists.mysql.com; Joshua J. Kugler [EMAIL PROTECTED] Sent: Thursday, November 16, 2006 12:16 PM Subject: RE: Urgent please.. Hi Renish, Perhaps these might be a more appropriate forum and also get you the result you need. This being a database list (I'm sure there are php experts on here) is probably not quite the right place to be asking. One of the php lists may produce a more relevant response. http://www.php.net/mailing-lists.php Regards --- ** _/ ** David Logan *** _/ *** ITO Delivery Specialist - Database *_/* Hewlett-Packard Australia Ltd _/_/_/ _/_/_/ E-Mail: [EMAIL PROTECTED] _/ _/ _/ _/ Desk: +61 8 8408 4273 _/ _/ _/_/_/ Mobile: +61 417 268 665 *_/ ** ** _/ Postal: 148 Frome Street, _/ ** Adelaide SA 5001 Australia invent --- -Original Message- From: Renish [mailto:[EMAIL PROTECTED] Sent: Thursday, 16 November 2006 2:38 PM To: Joshua J. Kugler Cc: mysql@lists.mysql.com Subject: Re: Urgent please.. Ocourse i read the manual . I was unable to do so..ie the reason I asked...I also knew this location mysql@lists.mysql.com - Original Message - From: Joshua J. Kugler [EMAIL PROTECTED] To: mysql@lists.mysql.com Cc: Renish [EMAIL PROTECTED] Sent: Thursday, November 16, 2006 11:20 AM Subject: Re: Urgent please.. On Wednesday 15 November 2006 18:14, Renish wrote: Can anyone tell me..how to install PHP php-5.2.0-Win32 . i click on php.exe and nothing seems to happen. I have intalled 1) webserver-Apache2 2) MySql-41.1 Please read the documentation before asking questions such as these. Thanks. http://www.php.net/manual/en/install.windows.php j -- Joshua Kugler Lead System Admin -- Senior Programmer http://www.eeinternet.com PGP Key: http://pgp.mit.edu/ ID 0xDB26D7CE PO Box 80086 -- Fairbanks, AK 99708 -- Ph: 907-456-5581 Fax: 907-456-3111 -- 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: 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]
Besoin d'aide urgent
Je viens au pres de vous solliciter une aide en ce qui concerne le demarrage du serveur mysql. J'ai installe sur ma machine la version mysql suivant: mysql-4.0.20a-win 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??? 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???. ___ Découvrez une nouvelle façon d'obtenir des réponses à toutes vos questions ! Profitez des connaissances, des opinions et des expériences des internautes sur Yahoo! Questions/Réponses http://fr.answers.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent: How to decode base64 via mysql V 5.0.x
Hi, Initially i thought it solved the problem but then i realized that the encoding done by PERL and this mysql function is different.I compated and found that the difference is in a new line , in this function the encoded output is all in one line and the same done via PERL via MIME::Base64 module gives in a different line after some same no of characters. Pl. someone give me a sol. to it. I coould have attached the files but the mailing list wont support that. Also pl. forgive mine top posting. -- Regards, Abhishek jain On 10/17/06, Ady Wicaksono [EMAIL PROTECTED] wrote: http://firestuff.org/wordpress/wp-content/uploads/2006/03/base64.sql On 10/17/06, abhishek jain [EMAIL PROTECTED] wrote: Hi, I want to decode base 64 string via mysql . Also i am using aspx .net . Pl. help me. Urgent reply will be appreciated -- Regards, Abhishek jain
Urgent: How to decode base64 via mysql V 5.0.x
Hi, I want to decode base 64 string via mysql . Also i am using aspx .net . Pl. help me. Urgent reply will be appreciated -- Regards, Abhishek jain
Re: Urgent: How to decode base64 via mysql V 5.0.x
Hi, Yes that solved the problem and was fast. I would like to know now that is there anyother way for the same in earlier versions of mysql. Thanks again, -- Regards, Abhishek jain On 10/17/06, Ady Wicaksono [EMAIL PROTECTED] wrote: http://firestuff.org/wordpress/wp-content/uploads/2006/03/base64.sql On 10/17/06, abhishek jain [EMAIL PROTECTED] wrote: Hi, I want to decode base 64 string via mysql . Also i am using aspx .net . Pl. help me. Urgent reply will be appreciated -- Regards, Abhishek jain
Urgent plsss
Can anyone tell me how can I import the *.gra (oracle db file) files to Navicat or Acess. Pls let me know in steps as I am v new to this field. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query. Urgent!
Hi all, I have installed MySql 4.1 in my system. when I tried to run Mysql , I always gets this error. Could not start MySql service on a local computer Error 1067: the process terminated unexpectedly. Any help is highly appreciated. Regards, Renish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Query...Urgent
Hi all, I have installed MySql 4.1 in my system. when I tried to run Mysql , I always gets this error. Could not start MySql service on a local computer Error 1067: the process terminated unexpectedly. Any help is highly appreciated. Regards, Renish -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: parameter of my_hash_sort_simple
Hello, Could anyone explain me the parameter of the function my_hash_sort_simple in MySQL 5.0.20a. Which is called under strings/ctype-simple.c. Thanks, Prem
InnoDB Crash RECOVERY HELP (Urgent)
Dear all, our DB server crashed and when I try to start Mysql /etc/init.d/mysql/start I get these lins in my error log 060921 13:00:14 mysqld started 060921 13:00:14 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060921 13:00:14 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 3546172175. InnoDB: Error: tried to read 65536 bytes at offset 0 2173440. InnoDB: Was only able to read 54784. InnoDB: Fatal error: cannot read from file. OS error number 17. 060921 13:01:24InnoDB: Assertion failure in thread 3086943936 in file os0file.c line 2107 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=536866816 max_used_connections=0 max_connections=550 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2094947 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffe3d4c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814cbfb 0x8d28b8 0x1 0x8355aed 0x835c659 0x835ce73 0x829ba01 0x81d3af3 0x81c5cb2 0x815028a 0x773e33 0x80e0c71 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 060921 13:01:24 mysqld ended when I add skip-innodb in my.cnf, it startsup but my innodb tables could not be accessed. How can I start MySQL server again? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get Firefox! /a
Re: InnoDB Crash RECOVERY HELP (Urgent)
The error message says to go to http://dev.mysql.com/doc/refman/5.0/en/forcing-recovery.html to learn how to set the different recovery options for innodb. On 9/21/06, Sayed Hadi Rastgou Haghi [EMAIL PROTECTED] wrote: Dear all, our DB server crashed and when I try to start Mysql /etc/init.d/mysql/start I get these lins in my error log 060921 13:00:14 mysqld started 060921 13:00:14 InnoDB: Database was not shut down normally! InnoDB: Starting crash recovery. InnoDB: Reading tablespace information from the .ibd files... InnoDB: Restoring possible half-written data pages from the doublewrite InnoDB: buffer... 060921 13:00:14 InnoDB: Starting log scan based on checkpoint at InnoDB: log sequence number 3 3546172175. InnoDB: Error: tried to read 65536 bytes at offset 0 2173440. InnoDB: Was only able to read 54784. InnoDB: Fatal error: cannot read from file. OS error number 17. 060921 13:01:24InnoDB: Assertion failure in thread 3086943936 in file os0file.c line 2107 InnoDB: We intentionally generate a memory trap. InnoDB: Submit a detailed bug report to http://bugs.mysql.com. InnoDB: If you get repeated assertion failures or crashes, even InnoDB: immediately after the mysqld startup, there may be InnoDB: corruption in the InnoDB tablespace. Please refer to InnoDB: http://dev.mysql.com/doc/mysql/en/Forcing_recovery.html InnoDB: about forcing recovery. mysqld got signal 11; This could be because you hit a bug. It is also possible that this binary or one of the libraries it was linked against is corrupt, improperly built, or misconfigured. This error can also be caused by malfunctioning hardware. We will try our best to scrape up some info that will hopefully help diagnose the problem, but since we have already crashed, something is definitely wrong and this may fail. key_buffer_size=0 read_buffer_size=536866816 max_used_connections=0 max_connections=550 threads_connected=0 It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 2094947 K bytes of memory Hope that's ok; if not, decrease some variables in the equation. thd=(nil) Attempting backtrace. You can use the following information to find out where mysqld died. If you see no messages after this, something went terribly wrong... Cannot determine thread, fp=0xbffe3d4c, backtrace may not be correct. Stack range sanity check OK, backtrace follows: 0x814cbfb 0x8d28b8 0x1 0x8355aed 0x835c659 0x835ce73 0x829ba01 0x81d3af3 0x81c5cb2 0x815028a 0x773e33 0x80e0c71 New value of fp=(nil) failed sanity check, terminating stack trace! Please read http://dev.mysql.com/doc/mysql/en/Using_stack_trace.html and follow instructions on how to resolve the stack trace. Resolved stack trace is much more helpful in diagnosing the problem, so please do resolve it The manual page at http://www.mysql.com/doc/en/Crashing.html contains information that should help you find out what is causing the crash. 060921 13:01:24 mysqld ended when I add skip-innodb in my.cnf, it startsup but my innodb tables could not be accessed. How can I start MySQL server again? -- Sincerely, Hadi Rastgou A Google Account is the key that unlocks the world of Google. a href= http://www.spreadfirefox.com/?q=affiliatesamp;id=0amp;t=1; Get Firefox! /a -- 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]
MySQL performing too badly under heavy load - urgent hlp needed
Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J
Re: MySQL performing too badly under heavy load - urgent hlp needed
I doubt apache is to blame. 5 seconds for a query on a website is extremely slow, so if that is your normal results, then you have a problem there already. I've been building database driven websites for around 11 years and i don't think i can remember a single time i went into production with a single query that was slower than 0.1 second, unless it was something very rarely used for administration purposes. From the top of my head i'd say your problem is either a) poor datamodel design which forces slow queries b) poor usage of indexes in the database (use explain to check) c) non-optimized configuration of the server (have you tuned the server parameters to the way you use the server?) d) insufficient hardware for your needs e) any combination of the above Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... 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: MySQL performing too badly under heavy load - urgent hlp needed
At 09:38 AM 7/27/2006, Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J So, what have you tried? Give us some information -- knowing which version you are running would be a good start. Are the queries slow if run from the command line? Are there enough threads in Apache? Have you rebuilt your indexes? Dropped them and replaced. Run optimize database? Done a dump and restore? Miles Thompson -- No virus found in this outgoing message. Checked by AVG Anti-Virus. Version: 7.1.394 / Virus Database: 268.10.4/401 - Release Date: 7/26/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: MySQL performing too badly under heavy load - urgent hlp needed
On Thursday 27 July 2006 17:00, Martin Jespersen wrote: I doubt apache is to blame. 5 seconds for a query on a website is extremely slow, so if that is your normal results, then you have a problem there already. I've been building database driven websites for around 11 years and i don't think i can remember a single time i went into production with a single query that was slower than 0.1 second, unless it was something very rarely used for administration purposes. From the top of my head i'd say your problem is either a) poor datamodel design which forces slow queries b) poor usage of indexes in the database (use explain to check) c) non-optimized configuration of the server (have you tuned the server parameters to the way you use the server?) d) insufficient hardware for your needs e) any combination of the above f) not enough memory, that forces major swapping activity -Stathis Ratheesh K J wrote: Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... 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: MySQL performing too badly under heavy load - urgent hlp needed
Do a show status and check on what mysql is doing. I would start by looking at: threads_created - if this is high, increase your thread_cache_size. This means MySQL is busy creating and destroying threads instead of reusing them. This can take a toll on the OS. Opened_tables - if this number is high/climbing, MySQL is buys opening and closing tables, which means your table_cache is probably too low. Compare open_tables to table_cache, open_tables should be lower. Show variables will help you see your current settings. Some things can be changed on the fly, like the thread cache, so can can do some things without taking MySQL down. - Original Message - From: Ratheesh K J [EMAIL PROTECTED] To: mysql@lists.mysql.com Sent: Thursday, July 27, 2006 8:38 AM Subject: MySQL performing too badly under heavy load - urgent hlp needed Hello all, Stuck up with a major problem. Urgent hlp required MySQL seems to be performing too bad during heavy load on the server. Queries which normally take around 5 secs to complete are taking more than 1000 secs to complete during load. What could be the reason. Show processlist shows many process in sending data state. All tables are of INNODB type. But we are not running any transactions as yet. The server is clogged due to many httpd requests (150 Max). All the httpd requests are in W state ( means sending response ). What could be causing this. Is it MySQL or is it Apache... Any suggestions would help... Thanks, Ratheesh K J -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent: Please Confirm Interest in China Business Opportunity
This is a text part of the message. It is shown for the users of old-style e-mail clients
[Solved] Urgent problem
It was just to copy the files from the DATA folder in the installation directory. That was easier then I thought. -Original Message- From: Peter Lauri [mailto:[EMAIL PROTECTED] Sent: Wednesday, May 24, 2006 6:52 AM To: mysql@lists.mysql.com Subject: Urgent problem Best group member, My computer stopped working. And I was able to install Windows on another partition and now I face one problem. I was running Windows with MySQL 4.1. I have all C: working, and can access all files. The first question: Can I recover that data from MySQL? The second question: How can I do that? (All attempts to repair Windows has failed, so that option is gone) I have gone thru the manual but all information there seems to be related to if the database crashes. And some options are there when you do make backups, but I do not have the backups. A desperate soul... -- 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: Urgent problem
Hi! Peter == Peter Lauri [EMAIL PROTECTED] writes: Peter Best group member, Peter My computer stopped working. And I was able to install Windows on another Peter partition and now I face one problem. I was running Windows with MySQL 4.1. Peter I have all C: working, and can access all files. Peter The first question: Can I recover that data from MySQL? Peter The second question: How can I do that? (All attempts to repair Windows has Peter failed, so that option is gone) Peter I have gone thru the manual but all information there seems to be related to Peter if the database crashes. And some options are there when you do make Peter backups, but I do not have the backups. Peter A desperate soul... In principle you can just copy the tables from one partition/computer to another and start using them. (The MySQL version doesn't have to exactly the same, as long as the new one is newer than the old one). To fix this: - Install MySQL on your new partition (computer) - Copy all files in the MySQL data directory (normally C:\mysql\data) to your new partition. If the path is different, you may have to create a my.cnf file that includes the --basedir and --datadir options to your new path. - Restart MySQL. - Check/repair your tables by using from the command line: C:\mysql\bin\mysqlcheck --all-databases --auto-repair Regards, Monty CTO of MySQL AB -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
If u need this in urgent, i think my suggestion may help you. try to do replication in this way Branch-A will be update in Branch-B and Branch-B will update in Branch-C . Branch-E(which got total data of A,B,C,D) will update in Main Office.
Urgent problem
Best group member, My computer stopped working. And I was able to install Windows on another partition and now I face one problem. I was running Windows with MySQL 4.1. I have all C: working, and can access all files. The first question: Can I recover that data from MySQL? The second question: How can I do that? (All attempts to repair Windows has failed, so that option is gone) I have gone thru the manual but all information there seems to be related to if the database crashes. And some options are there when you do make backups, but I do not have the backups. A desperate soul... -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
Only with MySQL 5.0 is multi-master replication possible. Guiseppe Maxia has a wonderful article about it at: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html -Sheeri On 5/22/06, Winanjaya - CBN [EMAIL PROTECTED] wrote: Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need to replicate my DB on 5 branches into 1 DB at HO - Urgent
On 5/22/06, Winanjaya - CBN [EMAIL PROTECTED] wrote: Dear Expert, I am very new to MySQL replication, I need to replicate my DB on 5 branches into 1 DB at Head Office. eg. MyDB at Branch A need to be replicated to MyDB at Head Office MyDB at Branch B need to be replicated to MyDB at Head Office MyDB at Branch C need to be replicated to MyDB at Head Office MyDB at Branch D need to be replicated to MyDB at Head Office MyDB at Branch E need to be replicated to MyDB at Head Office FYI, every table has Branch Id, so it won't be duplicate record! I really need advise .. how to do such replication on MySQL 5..what should I do in my my.ini? Any prompt reply would be appreciated ! .. Thanks a lot in advance Regards Winanjaya On 5/22/06, sheeri kritzer [EMAIL PROTECTED] wrote: Only with MySQL 5.0 is multi-master replication possible. Guiseppe Maxia has a wonderful article about it at: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html -Sheeri The solution to his setup is not Multi-Master replication (even in Multi-Master replication any single instance of MySQL can have atmost one master), but a Slave with Multiple Masters, which offcourse is not possible unless he plans to run five different mysqld instances (diff ports and diff data dirs...) on the same box, Kishore Jalleda -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Urgent Fetch and updation in single step/query
Hi all, Can we run a query like UPDATE table_name set abc='1'; and still fetch the value from a column from table . I mean Can we run select And update query in one statement. If yes how. Pl. tell me for versions 4.x and 5.x Pl. do help me. -- Regards Abhishek Jain On 4/20/06, abhishek jain [EMAIL PROTECTED] wrote: No i do not want an autoincrement key , as i want to be only one row in the table and it value gets incremented / updated and i to know whats its current value. -- Regards Abhishek Jain On 4/19/06, Michael Kruckenberg [EMAIL PROTECTED] wrote: Seems like what you need is an auto-increment key. Is that out of the question? On Apr 18, 2006, at 1:34 AM, abhishek jain wrote: Dear Friends, I run several processes and they need to query the mysql 5.0.8 database simultaneously .I have a config table which have the record id. I need to fetch that and increment that .What I feel that the same record id is fetched by different simultaneosly before i update .Can anyone help me in either: 1)telling me a single query which will fetch and incr. in the same query. so the problem of simultaneously queries are solved. 2)A system by which delaying the other queries are done, I use PHP . Expecting a quick reply. Thanks, Abhishek Jain
Re: Urgent Fetch and updation in single step/query
WEBBEE . BIZ wrote: Hi all, Can we run a query like UPDATE table_name set abc='1'; and still fetch the value from a column from table . I mean Can we run select And update query in one statement. If yes how. Pl. tell me for versions 4.x and 5.x Pl. do help me. Hmm should work with PROCEDURE in both versions. -- Smileys rule (cX.x)C --o(^_^o) Dance for me! ^(^_^)o (o^_^)o o(^_^)^ o(^_^o) -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
C K wrote: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK I first encountered this issue yesterday, when mucking around with Access 2003. When I dropped the timestamp(14) field from my table, everything worked ( no #DELETED# after updating records ). I tested with about 3 records. No guarantees it work work the 4th time ... -- Daniel Kasak IT Developer NUS Consulting Group Level 5, 77 Pacific Highway North Sydney, NSW, Australia 2060 T: (+61) 2 9922-7676 / F: (+61) 2 9922 7989 email: [EMAIL PROTECTED] website: http://www.nusconsulting.com.au -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Reserevd Error -7776 -- Urgent
Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. Sorry I can't be more helpful but it's a busy day here Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Reserevd Error -7776 -- Urgent
On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection. When I update an y data from MySQL Administrator or SQLYog it gets updated. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Reserevd Error -7776 -- Urgent
C K [EMAIL PROTECTED] wrote on 04/07/2006 01:59:39 PM: On 4/7/06, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: C K [EMAIL PROTECTED] wrote on 04/07/2006 01:35:22 PM: Dear Friends, I have linked tables from MySQL through ODBC DSN in Access 2003. when I go for updating any record in any linked table then it gives me error as follows: Reserved Error (-7776); there is no message for this error. What will be the problem? Please help. Is this error from MS ACCESS or for MySQL. Thanks Regards, CPK Have you read through this: http://dev.mysql.com/doc/refman/4.1/en/msaccess.html or this: http://dev.mysql.com/doc/refman/4.1/en/myodbc-with-vb.html or checked here: http://msdn.microsoft.com/archive/default.asp?url=/archive/en- us/dnaraccessdev/html/ODC_JetDatabaseEngineVersion30ODBCConnectivity.asp (quoted) -7776 SQLGetData(SQL_C_TIMESTAMP) Illegal date/time value returned. My quick suggestion: Make sure that all of your timestamp and datetime fields are within their expected ranges. I am using MySQL server 5.0.17 and ODBC 3.51.12 for ODBC connection. When I update an y data from MySQL Administrator or SQLYog it gets updated. I see your point? That must be why I *didn't* give you links that document working with MySQL with MySQL Administor OR SQLYog. I gave you links to using MS Access and ODBC with MySQL. Those pages (and the ones they link to) contain valuable information to help you learn how limited those products are compared with MySQL and how you need to adjust your data so that it fits within the boundaries that MS sets for *their* data manipulation tools. The short explanation is that MySQL has more range and capacity than ODBC and Access can handle and you have to work within the MS limits if you want to use with those tools. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: Reserevd Error -7776 -- Urgent
I also tried folloing and got results as below: 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as mentioned in MySQL 5.1 manual. But this not worked. 2) I dropped timestamp fiield from that table and refreshed link, then I can ork properly. Now I can insert and update reocrds without any problem. But this against the comment and responce from MySQL community that it is necessary to have a timestamp field to properly view and edit data in linked MysQL table. Else it will give he error as #deleted, But isn't it strange that now it is not giving me any such error. ? Why? Thanks. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Reserevd Error -7776 -- Urgent
I have been linking MySQL tables into access for versions 3.23 thorough 5.17. The only way I have been able to deal with insert/update tables with a timestamp field is to build a query which includes all of the fields except the timestamp field. You can then do your insert or update against the query. ACCESS/ODBC does not know about the timestamp field so they are happy. If you specify CURRENT TIMESTAMP on the timestamp field in the MySQL definition, MySQL will populate the field every time the row is changed or a new row added. -Original Message- From: C K [mailto:[EMAIL PROTECTED] Sent: Friday, April 07, 2006 1:50 PM To: [EMAIL PROTECTED]; [EMAIL PROTECTED] Cc: [EMAIL PROTECTED]; mysql@lists.mysql.com; [EMAIL PROTECTED] Subject: Re: Reserevd Error -7776 -- Urgent I also tried folloing and got results as below: 1) I updated the default for timestamp filed as CURRENT TIMESTAMP as mentioned in MySQL 5.1 manual. But this not worked. 2) I dropped timestamp fiield from that table and refreshed link, then I can ork properly. Now I can insert and update reocrds without any problem. But this against the comment and responce from MySQL community that it is necessary to have a timestamp field to properly view and edit data in linked MysQL table. Else it will give he error as #deleted, But isn't it strange that now it is not giving me any such error. ? Why? Thanks. -- 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]
Need Update Query Help (Urgent)
Hi i ahve one table table_1 and columns like col_1,col_2,col_3 col_1 col_2 col_3 1 aa aaa 2 bb Now i want to update my table table_1 SET col_3 as bbb where max of col_1 I wrote this below Query but it shows error how to write UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = (SELECT max(col_1) FROM table_1) -- Thanks Regards, veerabhadrarao narra, +91-988-556-5556 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Update Query Help (Urgent)
On Monday 20 February 2006 03:27, Veerabhadrarao Narra wrote: Hi i ahve one table table_1 and columns like col_1,col_2,col_3 col_1 col_2 col_3 1 aa aaa 2 bb Now i want to update my table table_1 SET col_3 as bbb where max of col_1 I wrote this below Query but it shows error how to write UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = (SELECT max(col_1) FROM table_1) It appears that you can't do what you want. This is at the bottom of the UPDATE syntax page: Currently, you cannot update a table and select from the same table in a subquery. http://dev.mysql.com/doc/refman/5.0/en/update.html However, you can to something like: select @maximum_column :=max(col_1) from table_1; UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = @maximum_column; Perhaps someone else has a better solution. -- Jeff Shapiro listserv only address -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Need Update Query Help (Urgent)
I wrote this below Query but it shows error how to write UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = (SELECT max(col_1) FROM table_1) See the docs for Update at http://dev.mysql.com/doc/refman/5.0/en/update.html. You cannot refer to the update table in a subquery. PB - Veerabhadrarao Narra wrote: Hi i ahve one table table_1 and columns like col_1,col_2,col_3 col_1 col_2 col_3 1 aa aaa 2 bb Now i want to update my table table_1 SET col_3 as bbb where max of col_1 I wrote this below Query but it shows error how to write UPDATE table_1 SET col_3 = 'bbb' WHERE col_1 = (SELECT max(col_1) FROM table_1) -- No virus found in this outgoing message. Checked by AVG Free Edition. Version: 7.1.375 / Virus Database: 267.15.11/264 - Release Date: 2/17/2006 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
A difficult query- urgent for me
Dear Friends,
I have a problm, try to solve that.
Actually there is a table with columns a and b .
So i want if a contains a particular word than a's value should return else
'b' value should return. And there must be one and only one column
returning. I have mysql 4.x and i think the logic will be like. I know i am
wrong by syntax but ...
SELECT IF(a REGEXP CONCAT('word' , '$'),a,b) from table_name where a =
'anything';
I think i am clear if not do please ask me questions.
I shall be very grateful if any one of you can give me a solutions.
--
Regards
Abhishek jain.
mail2web - Check your email from the web at
http://mail2web.com/ .
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: A difficult query- urgent for me
On 1/15/06, [EMAIL PROTECTED] wrote: Actually there is a table with columns a and b . So i want if a contains a particular word than a's value should return else 'b' value should return. SELECT CASE WHEN a = 'Good' THEN a ELSE b END FROM table Jochem
Re: A difficult query- urgent for me
An example of the _data_ would be very helpful here.
For example, let's say this is your data:
Col_ACol_B
-----
aceexpert
doghound
hungryravenous
If you are searching for the word ace, you should find it. You then want
the SQL to return ace, right?
If you are searching for the word puppy, you won't find it in the first
column. Which word from the second column do you want to return??? There is
no obvious reason to prefer any of the different values in the second column
when the search word does not appear in the first column.
Or do you only have a single row in this table? If so, I'm not sure why you
want to create a table just to contain these two values; it might be
justified, depending on what you are doing, but it seems unlikely.
Can you clarify what you are trying to accomplish? Otherwise, it's going to
be hard to help you.
Also, which version of MySQL are you on, 4.0.x or 4.1.x? It might make a big
difference to the answer I would give since 4.0.x does not support
subqueries while 4.1.x does.
Rhino
- Original Message -
From: [EMAIL PROTECTED]
To: mysql@lists.mysql.com
Sent: Sunday, January 15, 2006 10:21 AM
Subject: A difficult query- urgent for me
Dear Friends,
I have a problm, try to solve that.
Actually there is a table with columns a and b .
So i want if a contains a particular word than a's value should return else
'b' value should return. And there must be one and only one column
returning. I have mysql 4.x and i think the logic will be like. I know i am
wrong by syntax but ...
SELECT IF(a REGEXP CONCAT('word' , '$'),a,b) from table_name where a =
'anything';
I think i am clear if not do please ask me questions.
I shall be very grateful if any one of you can give me a solutions.
--
Regards
Abhishek jain.
mail2web - Check your email from the web at
http://mail2web.com/ .
--
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.371 / Virus Database: 267.14.18/230 - Release Date: 14/01/2006
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.371 / Virus Database: 267.14.18/230 - Release Date: 14/01/2006
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Urgent help using logon to mySQL
Can some one please tell me what I am doing wrong here I have installed and configured users in the database. I am using ODBC driver to logon. When I use the password, it does not work but surprisingly the logon is allowed without a password. I have tried to use MySQL Administrator to logon remotely but same thing. I reset user's password and Flushed but still could not get the logon with blank password go away. Any help is appreciated.
Re: Urgent help using logon to mySQL
Aftab Khan [EMAIL PROTECTED] wrote on 12/28/2005 02:15:33 PM: Can some one please tell me what I am doing wrong here I have installed and configured users in the database. I am using ODBC driver to logon. When I use the password, it does not work but surprisingly the logon is allowed without a password. I have tried to use MySQL Administrator to logon remotely but same thing. I reset user's password and Flushed but still could not get the logon with blank password go away. Any help is appreciated. Depending on how you installed MySQL, you may or may not have performed this step of the installation process: http://dev.mysql.com/doc/refman/4.1/en/default-privileges.html It sounds to me that you have the anonymous account still in your mysql.user table. Try this: SELECT id, user, host, password from mysql.user; You should be able to identify the odd account pretty easily. Just DELETE the account and FLUSH PRIVILEGES again and you should no longer be able to login without both a user name and a password. Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Hi, Thanks Again. You are really helping me Well it get a lot of query ok, 0 rows affected 0.00 sec And one like this : query ok, 2 rows affected 0.01 sec Records: 2 Duplicates: 0 Warnings: 0 But it look like everything is in the Database now ? What is this Source Command ? Another thing I noticed is that My Original databes my Sp's Got Delimiter $$ But on the backup file it replaced with ;; Barak _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 7:21 AM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore I saw both views and stored procedures in the dump file. What error do you get if you process the script with the source command within the MySQL CLI? mysql -uroot -pmypass (login welcome) mysql CREATE DATABASE IF NOT EXISTS bcm; mysql USE bcm; mysql source myback1.sql There is another option to drop/create the database. Your dump didn't have that so you need to do it manually in order to restore it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM: Wel I read the manual but didn't find something that I really need to add I suspect it is something with the script itself. Now I'm using mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql but after i run mysqldump -uroot -pmypass --routines bcmmyback1.sql it now restore the tables but not the views or sp's here is the backup file thanks Barak snipped out backup file From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute agoI did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed
urgent : PLEASE HELP - problems with back up and restore
Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak
Re: urgent : PLEASE HELP - problems with back up and restore
Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Thanks again, I will try this and read the manual again. _ From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn't help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute ago I did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
RE: urgent : PLEASE HELP - problems with back up and restore
Thanks Marko :) -Original Message- From: Marko Knezevic [mailto:[EMAIL PROTECTED] Sent: Thursday, November 24, 2005 12:29 AM To: MySQL list Subject: Re: urgent : PLEASE HELP - problems with back up and restore Dear Barak, Save yourself year or two of your life and try using MySQL Front not that command line tools. Its really nice tool for handling MySQL databases Regards Marko -- 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]
LEFT JOIN not working on 5.0.16 - urgent help
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
Terence Le Grange
Senior IS Executive - ITS
Sunway University College
Email: [EMAIL PROTECTED]
Phone: (+603) 7491 8623 ext. 8078
Website: http://www.sunway.edu.my
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN not working on 5.0.16 - urgent help
On 24/11/2005 2:22 p.m., Terence wrote: SELECT um.username,rm.role_name,dm.department_name FROM user_master um, role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id WHERE um.role_id = rm.role_id; 1054 - Unknown column 'um.department_id' in 'on clause' Query: SELECT um.username,rm.role_name,dm.department_name FROM user_master um, role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id WHERE um.role_id = rm.role_id = Has the left join syntax changed? Yeah this caught me out too. The precedence of JOINs has changed to more strictly follow the SQL standard. In 5.0, MySQL is now interpreting your query as: SELECT ... FROM user_master um, (role_master rm LEFT JOIN department_master dm ON um.department_id = dm.department_id) ... And the table um doesn't exist in the join between rm and dm. The solution is to put the FROM tables in parentheses, like: SELECT ... FROM (user_master um, role_master rm) LEFT JOIN department_master dm ON um.department_id = dm.department_id) ... You can read more about this here: http://dev.mysql.com/doc/refman/5.0/en/join.html -Simon -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: LEFT JOIN not working on 5.0.16 - urgent help
Terence,
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
J
Your query joins rm to dm on a um column = a dm column. Recent 5.0 releases
rightly object to syntactical weirdness like that. You need something
like...
... FROM role_master rm
INNER JOIN user_master um USING (role_id)
LEFT JOIN department_master USING (department_id) ...
PB
-
Terence wrote:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
No virus found in this outgoing message.
Checked by AVG Free Edition.
Version: 7.1.362 / Virus Database: 267.13.6/179 - Release Date: 11/23/2005
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: urgent : PLEASE HELP - problems with back up and restore
I saw both views and stored procedures in the dump file. What error do you get if you process the script with the source command within the MySQL CLI? mysql -uroot -pmypass (login welcome) mysql CREATE DATABASE IF NOT EXISTS bcm; mysql USE bcm; mysql source myback1.sql There is another option to drop/create the database. Your dump didn't have that so you need to do it manually in order to restore it. Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 07:18:02 PM: Wel I read the manual but didn’t find something that I really need to add I suspect it is something with the script itself. Now I’m using mysqldump -uroot -pmypass --routines -q -x bcmmyback1.sql but after i run mysqldump -uroot -pmypass --routines bcmmyback1.sql it now restore the tables but not the views or sp’s here is the backup file thanks Barak snipped out backup file From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:30 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: RE: urgent : PLEASE HELP - problems with back up and restore There's a new option added in 5.0.13: -R (--routines) It's designed designed specifically to dump SPROCs and FUNCTIONs. I thought it would have been turned on by default but I guess it wasn't. Add that to the list of options and check your dump results again. I refer you again to the manual for additional warnings and cautions. http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html Shawn Green Database Administrator Unimin Corporation - Spruce Pine Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 02:23:42 PM: Well Shawn, First of thanks for the quick result. I tried your suggestion but it didn’t help no sp and no restore. It only shrinked the backup file (by deleting the cr). Is there any log file where I can trace that kind of erros ? Barak From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] Sent: Wednesday, November 23, 2005 9:07 PM To: Barak Mery Cc: mysql@lists.mysql.com Subject: Re: urgent : PLEASE HELP - problems with back up and restore Barak Mery [EMAIL PROTECTED] wrote on 11/23/2005 01:51:37 PM: Hi, I'm really desperate on this on. After struggling with some really weird bugs I finally finished my project. Buut I can't perform a good backup and restore. I'm using : MySql 5.0.16-nt (essentials version) Windows xp The DB contains tables, vw's and sp's. It is a very small one and at the moment I don't have any data inside. The whole backup file size is 100Kb. 1. I backed up my db using mysql administrator. It created a back up file with all tables, views and sp's, but when I tried a restore it got errors like. Could not handle this statement etc. 2. I tried : mysqldump -uroot -pmypassword dbname backup.sql And then mysqldump -uroot -pmypassword newDbName backup.sql I didn't get any error. The shell printed to the screen the backup file completely only without the table script part. It Created a backup file only for the tables (why ? a minute agoI did the backup with the same tool). But after restoring, the new db was still empty. 3. I tried to restore with mysql -uroot -pmypassword newDbName backup.sql But I got the same results. It should be a very simple and basic issue. Why everything is so hard with mysql ? Is there any really good and quick forum for mySql ? I posted some new thread in mysql.com at the past few days but never got answered. Is it just me or that's the life on the mysql planet ? I now try this mailing-list. I hope you can help me. Thanks in advance Barak You are in luck as the mailing list is quite active. Look at your actual dump file. It is simply a SQL script that will create all of the elements of the database and populate them with data (if you had any). My suspicion is that you have something that isn't quoted that needed to be. mysqldump has several options. You can see them with the command: mysqldump --help -or- refer to the manual http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html try dumping your database again, this time use the -r= and -Q options. That does two things: a) it avoids adding CR characters at the end of every line b) it puts backticks around EVERYTHING that needs them (table names, column names, etc) mysqldump -uroot -pmypassword -r backup.sql -Q dbname Shawn Green Database Administrator Unimin Corporation - Spruce Pine
Re: LEFT JOIN not working on 5.0.16 - urgent help
Terence [EMAIL PROTECTED] wrote on 11/23/2005 08:22:30 PM:
Hi All,
We have upgraded from 4.1.0 to 5.0.16 and notice all left joins not
working. It works fine on 4.1 but 5.0.16 gives us an error:
How to reproduce:
CREATE TABLE `user_master` (
`user_id` int(5) unsigned NOT NULL auto_increment,
`department_id` int(5) default NULL,
`role_id` int(5) unsigned default NULL,
`username` varchar(50) NOT NULL,
PRIMARY KEY (`user_id`),
UNIQUE KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
);
CREATE TABLE `role_master` (
`role_id` int(5) unsigned NOT NULL auto_increment,
`role_name` varchar(50) NOT NULL,
PRIMARY KEY (`role_id`),
UNIQUE KEY `role_id` (`role_id`),
KEY `role_id_2` (`role_id`)
);
CREATE TABLE `department_master` (
`department_id` int(5) unsigned NOT NULL auto_increment,
`department_name` varchar(50) NOT NULL,
PRIMARY KEY (`department_id`),
UNIQUE KEY `department_id` (`department_id`),
KEY `department_id_2` (`department_id`)
);
INSERT INTO role_master(role_name) VALUES('Administrator');
INSERT INTO department_master(department_name) VALUES('ITS');
INSERT INTO user_master(department_id,role_id,username)
VALUES('1','1','Joey');
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id;
1054 - Unknown column 'um.department_id' in 'on clause'
Query:
SELECT um.username,rm.role_name,dm.department_name
FROM user_master um, role_master rm
LEFT JOIN department_master dm ON um.department_id = dm.department_id
WHERE um.role_id = rm.role_id
=
Has the left join syntax changed?
--
Terence Le Grange
Senior IS Executive - ITS
Sunway University College
Email: [EMAIL PROTECTED]
Phone: (+603) 7491 8623 ext. 8078
Website: http://www.sunway.edu.my
The SQL didn't change, the query engine was debugged. That is the error
you should have been getting all along but weren't. Please check the
manual for a full explanation but the short version is that crappy
comma-delimited method of making a CROSS JOIN has been demoted in
evaluation priority (where it should be). Three options:
a) swap the order you list the tables so that user_master appears next to
the LEFT JOIN
b) use parentheses to reprioritize the joins so that user_master CROSS
JOINs to role_master BEFORE you LEFT JOIN to department_master
c) quit using the comma. Use CROSS JOIN instead.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
RE: Killing a thread -- Urgent
The statement remained in the killed state for 2 hrs and then got out of the processlist. Great sigh of relief then :). Sorry for the late reply. But one thing I still couldn't understand is why it took so long to move out of the processlist from the killed state. sujay -Original Message- From: Gleb Paharenko [mailto:[EMAIL PROTECTED] Sent: Sunday, October 16, 2005 12:53 AM To: mysql@lists.mysql.com Subject: Re: Killing a thread -- Urgent Hello. Do you have other queries which are running at this moment on the server? The show-processlist.html says that thread could be in Killed state if it is locked by another thread. What version of MySQL do you use? What storage engine does your table have? Sujay Koduri [EMAIL PROTECTED] wrot hi all.. I started a select count(1) command on a table in mysql, assuming it will end soon within the off peak time.. But now the peak load time is about to start, so i tried to kill that query.. logged into mysql as root. show processlist for getting the thread id. kill 85300 (thrad_id) After doing this the state of the query has changed to killed, but still it is showing like this 85300 | root | localhost | db_name | Killed | 23055 | Sending data | select count(1) from I have issued the kill command almost 1hour back..but still it is showing up this query..And the iowait on the DB server is touching 60%..generally in off peak times it should be 35%. (i seriously suspect this query for the high iowaits) Any help is greatly appreciated. Thank you sujay -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.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]
Killing a thread -- Urgent
hi all.. I started a select count(1) command on a table in mysql, assuming it will end soon within the off peak time.. But now the peak load time is about to start, so i tried to kill that query.. logged into mysql as root. show processlist for getting the thread id. kill 85300 (thrad_id) After doing this the state of the query has changed to killed, but still it is showing like this 85300 | root | localhost | db_name | Killed | 23055 | Sending data | select count(1) from I have issued the kill command almost 1hour back..but still it is showing up this query..And the iowait on the DB server is touching 60%..generally in off peak times it should be 35%. (i seriously suspect this query for the high iowaits) Any help is greatly appreciated. Thank you sujay
Re: Killing a thread -- Urgent
Hello. Do you have other queries which are running at this moment on the server? The show-processlist.html says that thread could be in Killed state if it is locked by another thread. What version of MySQL do you use? What storage engine does your table have? Sujay Koduri [EMAIL PROTECTED] wrot hi all.. I started a select count(1) command on a table in mysql, assuming it will end soon within the off peak time.. But now the peak load time is about to start, so i tried to kill that query.. logged into mysql as root. show processlist for getting the thread id. kill 85300 (thrad_id) After doing this the state of the query has changed to killed, but still it is showing like this 85300 | root | localhost | db_name | Killed | 23055 | Sending data | select count(1) from I have issued the kill command almost 1hour back..but still it is showing up this query..And the iowait on the DB server is touching 60%..generally in off peak times it should be 35%. (i seriously suspect this query for the high iowaits) Any help is greatly appreciated. Thank you sujay -- For technical support contracts, goto https://order.mysql.com/?ref=ensita This email is sponsored by Ensita.NET http://www.ensita.net/ __ ___ ___ __ / |/ /_ __/ __/ __ \/ /Gleb Paharenko / /|_/ / // /\ \/ /_/ / /__ [EMAIL PROTECTED] /_/ /_/\_, /___/\___\_\___/ MySQL AB / Ensita.NET ___/ www.mysql.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: URGENT --- OSX 10.4 ODBC driver error
Andrew stolarz wrote: Hello All, I am downloading and installing the Installer package (Mac OS X v10.3) from the mysql site. I am installing on a OSX 10.4 machine, at the end of the install I get an error message saying The application MYODBCConfig quit unexpedically after it was reopened, max OSX and other applications are not effected etc. etc. its version : 3.51.11-1 so this ODBC driver will not work on the 10.4 machine? as right next to the download it states OSX v10.3 (installing on 10.3 works no problem) Sorry, I can't help here, but noted this was an urgent request. If you need urgent help, please contact MySQL Support at: https://support.mysql.com Regards, Josh -- Josh Chamas Director, Professional Services MySQL Inc., www.mysql.com Get More with MySQL! www.mysql.com/consulting -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
please help .........very urgent
hi ,
I wanted to check the following condition and if it is
success i wanted to display a massage.I tried as
follows. but no luck. nothing displayed.
$dbQuery = results;
$result = mysql_query($dbQuery) or die(Couldn't get
file list);
if (!isset($result))
{echo NULL;}
//if (mysql_result($result == 0))(
//echo sorry;
?
please , tell me how can i do this ?
Thanx
Kane.
__
Click here to donate to the Hurricane Katrina relief effort.
http://store.yahoo.com/redcross-donate3/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
