Re: Error in mysql replication with LOAD DATA INFILE
The application is designed to work such a way that it will process the csv files daily as part of the aggregate process to calculate some metrics. it runs fine on the master, when it come to slave through replicaiton it fails with the error. i even tried upgrading the slave to latest version mysql 5.1.53 after i see some post on the internet saying we have some issues in the older version , but it keeps giving the same error. thanks Anand On Mon, Dec 20, 2010 at 7:42 PM, who.cat win@gmail.com wrote: i wanna know you have done LOAD DATA INFILE in master ,why are you tring to do it in the slave ?The master didn't replication the data to the master ? All you best What we are struggling for ? The life or the life ? On Mon, Dec 20, 2010 at 3:32 PM, Anand Kumar sanan...@gmail.com wrote: On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
Re: Error in mysql replication with LOAD DATA INFILE
On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote: Hi guys, i am facing a serious issue with my replication , i tried so many things but no luck. my replication is running with mysql 5.0.51a in master and 5.0.90 in slave. we run LOAD DATA INFILE in master to process some csv files and load it into a table, it runs perfectly well in master but when it comes to slave it stops with SQL SYNTAX error i tried running the LOAD DATA INFILE manually on the slave , but it says different error as below mysql load data infile '/tmp/SQL_LOAD-4-3-161.data' into table t; ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database directory or be readable by all when i chcked the file persmission it is -rw-rw 1 mysql mysql 0 Dec 18 23:53 /tmp/SQL_LOAD-4-3-161 snippet from my error log 101219 0:06:32 [Note] Slave SQL thread initialized, starting replication in log '.000127' at position 923914670, relay log '/var/lib/mysql/slave-relay.02' position: 39311 101219 0:06:32 [Note] Slave I/O thread: connected to master 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at position 946657303 101219 0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1' on query. Default database: 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data' IGNORE INTO TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY '' ESCAPED BY '', Error_code: 1064 101219 0:06:33 [Warning] Slave: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ''' at line 1 Error_code: 1064 101219 0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix the problem, and restart the slave SQL thread with SLAVE START. We stopped at log '.000127' position 926912155 please help me fixing this .. thanks in advance.. thanks Anand
Re: Recommend A Backup User / Privileges?
Hi Carlos, I would say the below grants for a user to perform backup is the minimum grants which we can provide.. grant select , lock tables, file on *.* to backup_user'@'localhost' identified by 'somepassword'; Thanks Anand On Wed, Apr 28, 2010 at 10:28 PM, Carlos Mennens carlosw...@gmail.comwrote: I downloaded a MySQL backup script today since I have 3 or 4 relatively small databases. The script can be found here: http://sourceforge.net/projects/automysqlbackup/ Now I was wondering if I can create a local database user 'backup'@'localhost' grant him a level of permissions needed to perform a 'mysqldump' so he can backup the databases to an directory on the server. My Question is what level or permissions does a user need to perform a 'mysqldump' on a database since that particular user's password will be entered into the script noted above via plain text. I am scared the password will be compromised that would be bad if it's root or someone who has GRANT ALL PRIVILEGES ON *.*. Can you guys recommend something for me here? I don't really know enough about MySQL 'grant' permissions to determine which would work or if the 'backup' user would require high level privileges. -Carlos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@gmail.com
Re: installation
Hi James, Assuming your OS is linux ,you can add the mysql bin path in the environment .By adding the entry in .profile of your home directory. PATH=$PATH:/sbin:/usr/sbin:/usr/local/mysql/bin: export PATH Thanks Anand On Wed, Apr 28, 2010 at 11:44 AM, jamesadr...@globalfreeenterprise.comwrote: In the ReadMe.pdf that came with the mysql that I downloaded from mysql.com, there is this comment: -- You might want to add aliases to your shell's resource file to make it easier to access commonly used programs such as `mysql' and `mysqladmin' from the command line. The syntax for `bash' is this: alias mysql=/usr/local/mysql/bin/mysql alias mysqladmin=/usr/local/mysql/bin/mysqladmin Even better, add `/usr/local/mysql/bin' to your `PATH' environment variable. You can do this by modifying the appropriate startup file for your shell. For more information, see *Note invoking-programs. --- My questions are these: 1.What is the appropriate startup file for my shell (which is bash on OS X)? 2.Is this the best discussion group for the question above? -- The installation comment said For more information, see *Note invoking-programs but I don't find such a note in all of the downloaded material. Thank you for your help. Jim Adrian jamesadr...@globalfreeenterprise.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@gmail.com
Re: Analysis of a weeks worth of general log
Hi Imran, you can have a look at mysqldumpslow utility to analyze the data.. Thanks Anand On Tue, Apr 20, 2010 at 5:48 PM, Jim Lyons jlyons4...@gmail.com wrote: Has anyone tried using the log_output option in mysql 5.1 to have the general log put into a table and not a flat file? I used it for a while before having to downgrade back to 5.0 but thought it was a great idea. I'm curious to see if anyone feels it helps analysis. On Tue, Apr 20, 2010 at 6:02 AM, Imran Chaudhry ichaud...@gmail.com wrote: I have 7 days worth of general log data totalling 4.4GB. I want to analyze this data to get: a) queries per second, minute, hour and day b) a count of the number of selects versus write statements (delete, insert, replace and update) c) a variation of the above with select, replace, delete and insert versus update How can I do this? I've looked at mysqlsla which is complex, works well but does not quite get what I want. [1] I looked at MyProfi 0.18 which looks like it will get some of the answers but runs out of memory working on the smallest log file (mysql.log) even with memory_limit in php.ini set to 1024MB [2] -rw-r- 1 imran imran 268M 2010-04-19 13:03 mysql.log -rw-r- 1 imran imran 721M 2010-04-19 12:56 mysql.log.1 -rw-r- 1 imran imran 737M 2010-04-19 13:05 mysql.log.2 -rw-r- 1 imran imran 554M 2010-04-19 13:06 mysql.log.3 -rw-r- 1 imran imran 499M 2010-04-19 13:02 mysql.log.4 -rw-r- 1 imran imran 568M 2010-04-19 12:59 mysql.log.5 -rw-r- 1 imran imran 488M 2010-04-19 13:01 mysql.log.6 Any pointers please? If all else fails, I will prolly write a perl script to munge it. [1] http://hackmysql.com/mysqlsla [2] http://myprofi.sourceforge.net -- GPG Key fingerprint = B323 477E F6AB 4181 9C65 F637 BC5F 7FCC 9CC9 CC7F -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=jlyons4...@gmail.com -- Jim Lyons Web developer / Database administrator http://www.weblyons.com
Re: Disable innodb status info in err log
you are right sir , the variables innodb_status_file will write the show innodb status information on to the file innodb_status.PID ... however for me the innodb status is logging into the default error log file... i dont have any clue on how to stop it from writing... Any help would be appreciated Thanks Anand On Wed, Mar 17, 2010 at 3:24 PM, RaMeSh rames...@gmail.com wrote: Greetings. Try with the variable innodb_status_file. I wont get logged in mysqlerr.log file. On 17 March 2010 15:15, Shanmugam, Dhandapani dhandapani.shanmu...@eds.com wrote: anand Do you use innodb engine at all further -D -Original Message- From: sanan...@gmail.com [mailto:sanan...@gmail.com] On Behalf Of Anand Sent: Wednesday, March 17, 2010 3:00 PM To: mysql@lists.mysql.com Subject: Disable innodb status info in err log Hi All, Innodb status information is getting logged on to my mysql error log file for every 15 seconds, can someone help in disabling it ? Thanks Anand -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=rames...@gmail.com -- Ramesh
Re: Disable innodb status info in err log
Jesper, I dont have a table called innodb_monitor on any of my databases, i gone through the links which you mentioned here.. but no luck.. Thanks Anand S On Wed, Mar 17, 2010 at 5:06 PM, Jesper Wisborg Krogh jes...@noggin.com.auwrote: On 17/03/2010, at 9:10 PM, Anand kumar wrote: you are right sir , the variables innodb_status_file will write the show innodb status information on to the file innodb_status.PID ... however for me the innodb status is logging into the default error log file... i dont have any clue on how to stop it from writing... Any help would be appreciated Do you happen to have a table called innodb_monitor? If that is present, then that will cause the result of SHOW ENGINE INNODB STATUS to be outputted every 15 seconds. See also http://dev.mysql.com/doc/refman/5.0/en/innodb-monitors.html or http://dev.mysql.com/doc/refman/5.1/en/innodb-monitors.html Cheers, Jesper
Re: how to dump database or tables
it should be windows.. . On Fri, Jan 29, 2010 at 2:29 PM, Suresh Kuna sureshkumar...@gmail.comwrote: Which OS your are using ? Suresh Kuna MySQL DBA On Fri, Jan 29, 2010 at 2:25 PM, muralikrishna g muralikrishn...@gmail.comwrote: i am working on my pc with mysql-5.0.27-community-nt i have created several data bases and tables in that.. to take backup, we have to use dump., i dont know the correct syntax how to use dump to take backup to a specific location., after that how to resore. please help me regarding this. thanks in advance
Re: probably a permissions problem that I cannot figure out.
can you give us the error , it can be found on the file /usr/local/mysql/data/Power-Mac-G5.local.err Thanks Anand On Thu, Jan 14, 2010 at 9:49 AM, Chris Elhardt celha...@mac.com wrote: mySQL-5.1.34-osx10.4-powerpc-64bit Not sure how this happened, but mySql will not start anymore, either from the command line or the system pref control panel. here's a startup sample: [I have blocked out the password] Power-Mac-G5:/usr/local/mysql chriselhardt$ sudo /usr/local/mysql/bin/mysqld_safe --user=root --password= /usr/local/mysql/bin/mysqld_safe: line 209: /usr/bin/grep: cannot execute binary file 100113 16:21:07 mysqld_safe Logging to '/usr/local/mysql/data/Power-Mac-G5.local.err'. 100113 16:21:07 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 100113 16:21:08 mysqld_safe mysqld from pid file /usr/local/mysql/data/Power-Mac-G5.local.pid ended Power-Mac-G5:/usr/local/mysql chriselhardt$ pwd /usr/local/mysql and another one: Power-Mac-G5:/usr/local/mysql chriselhardt$ sudo /usr/local/mysql/support-files/mysql.server start Starting MySQL.. ERROR! Manager of pid-file quit without updating file. where are the config files supposed to be? ~Users/chriselhardt/.my.cnf has only a single line, [mySQL] and I'm pretty sure there used to be more in there than that. Thanks for any assistance. In the space of one hundred and seventy-six years the Mississippi has shortened itself two hundred and forty-two miles. Therefore ... in the Old Silurian Period the Mississippi River was upward of one million three hundred thousand miles long ... seven hundred and forty-two years from now the Mississippi will be only a mile and three-quarters long. ... There is something fascinating about science. One gets such wholesale returns of conjecture out of such a trifling investment of fact. M. Twain -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anand@gmail.com
Re: Before alter Field name in table, Things Need to check
you can take the help from information_schema . --Anand On Tue, Jan 5, 2010 at 4:01 PM, bharani kumar bharanikumariyer...@gmail.com wrote: Hi For example , Student_TBL Student_id Student_Name Student_regNo , Am going to Alter the StudentregNo To StudentRegister_No , Am not sure who are all used this field , that any of the views depend this StudentRegister_No , How to check it , Is there any function Do this, Thanks
Re: Global variable for only one database
Hello, you cannot change it in database level, it applies only to instance level. else if you dont want to use query cache for certain queries you can include SQL_NO_CACHE in your query .. ex .. SELECT SQL_NO_CACHE ID,NAME FROM EMPLOYEE; -- your query result will not be cached here .. SELECT SQL_CACHE ID,NAME FROM EMPLOYEE; --- your query result will be cached here if the query_cache variable is set to ON. Hope this will clear your doubt.. Regards Anand S On Thu, Dec 31, 2009 at 2:33 PM, Jeetendra Ranjan jeetendra.ran...@sampatti.com wrote: Hi, We have number of mysql database in one box. How can we set the variables that applies to only one database. Like when i set the query_cache_size it should be used by only those queries for which the parameter is set ? Thanks
Minimal grants for backup
Hi i am using mysql-4.1.3-beta version in mysq server my concern is that i want to create a user that should able to take backup using mysqldump ... can any one help me out .. i tried giving the following grants but it is not works grant file,SHOW DATABASES,select,lock tables on *.* to [EMAIL PROTECTED] by 'yyy'; regards Anand
Grants for mysqlbackup
Hi i am using mysql-4.1.3-beta version in mysq server my concern is that i want to create a user(minimal grant) that should able to take backup using mysqldump ... can any one help me out .. i tried giving the following grants but it is not works grant file,SHOW DATABASES,select,lock tables on *.* to [EMAIL PROTECTED] by 'yyy'; regards Anand
Re: Grants for mysqlbackup
Hi i am using mysql-4.1.3-beta version in mysq server my concern is that i want to create a user(minimal grant) that should able to take backup using mysqldump ...can any one help me out .. i tried giving the following grants but it is not works grant file,SHOW DATABASES,select,lock tables on *.* to [EMAIL PROTECTED] by 'pass'; regards Anand
Grants for mysqldump
Hi i am using mysql-4.1.3-beta version in mysq server my concern is that i want to create a user (minimal grant) that should able to take backup using mysqldump ...can any one help me out .. i tried giving the following grants but it is not works grant file,SHOW DATABASES,select,lock tables on *.* to [EMAIL PROTECTED] by 'pass'; regards Anand
