how to set SET SQL_BIG_SELECTS=1 ?
Dear all, After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing error: how to set SET SQL_BIG_SELECTS=1 ? Error is *Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename */ SELECT filename FROM system WHERE name = 'user' AND type = 'module' in */home/User/public_html/includes/database.mysql.inc* on line *136* *Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay query: drupal_lookup_path /* Guest : drupal_lookup_path */ SELECT src FROM url_alias WHERE dst = 'welcome' AND language IN('en', '') ORDER BY language DESC, pid DESC in */home/User/public_html/includes/database.mysql.inc* on line *136* *Fatal error*: Call to undefined function filter_xss_admin() in */home/User/public_html/includes/common.inc* on line *369*## Thank you
Re: how to set SET SQL_BIG_SELECTS=1 ?
Thank you , however i am newbie to mysql and not sure where to put that ( SET SQL_BIG_SELECTS=1;) : when i grep , i see following files for example that has query code: [root@server public_html]# grep -R SELECT filename . | grep -v error_lo ./sites/all/modules/views/includes/admin.inc:$filename = db_result(db_query(SELECT filename FROM {system} WHERE type = 'module' AND name = 'advanced_help')); ./sites/all/modules/ubercart/docs/hooks.php: $filename = db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $file_id)); ./sites/all/modules/ubercart/uc_file/uc_file.module: $files = db_query(SELECT filename FROM {uc_files} WHERE filename LIKE LOWER('%s'), '%'. strtolower($url[1]) .'%'); ./sites/all/modules/ubercart/uc_file/uc_file.module:$filename = db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid)); ./sites/all/ubercart/docs/hooks.php: $filename = db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $file_id)); ./sites/all/ubercart/uc_file/uc_file.module: $files = db_query(SELECT filename FROM {uc_files} WHERE filename LIKE LOWER('%s'), '%'. strtolower($url[1]) .'%'); ./sites/all/ubercart/uc_file/uc_file.module:$filename = db_result(db_query(SELECT filename FROM {uc_files} WHERE fid = %d, $fid)); ./modules/system/system.module: $result = db_query(SELECT filename, name, type, status, throttle, schema_version FROM {system} WHERE type = '%s', $type); ./includes/bootstrap.inc: elseif (db_is_active() (($file = db_result(db_query(SELECT filename FROM {system} WHERE name = '%s' AND type = '%s', $name, $type))) file_exists($file))) { [root@server public_html]# please advise. thanks On Fri, Feb 28, 2014 at 4:57 PM, Reindl Harald h.rei...@thelounge.netwrote: Am 28.02.2014 12:17, schrieb Madan Thapa: After upgrading from mysql 5.0 to mysql 5.1, i am getting the follwing error: how to set SET SQL_BIG_SELECTS=1? just type it before your query in question? as you can see below if you try such things and they are not supported or you have a typo you get a crear error, otherwise OK MariaDB [(none)] SET SQL_BIG_SELECTS=1; Query OK, 0 rows affected (0.00 sec) MariaDB [(none)] SET SQL_BIG_SELEtCTS=1; ERROR 1193 (HY000): Unknown system variable 'SQL_BIG_SELEtCTS' *Warning*: The SELECT would examine more than MAX_JOIN_SIZE rows; check your WHERE and use SET SQL_BIG_SELECTS=1 or SET MAX_JOIN_SIZE=# if the SELECT is okay query: drupal_get_filename /* Guest : drupal_get_filename */ SELECT filename FROM system WHERE name = 'user' AND type = 'module' in */home/User/public_html/includes/database.mysql.inc* on line *136*
mysql TIME_WAIT
Hi, When i do a netstat and grep port 3306 , i can see lots of TIME_WAIT.Can you please advise , what might be the issue for so many TIME_WAIT ? Here are the logs and related files: My.CNF on DB Server ## ## [root@sql mysql]# cat /etc/my.cnf [mysqld] safe-show-database skip-locking skip-bdb port= 3306 socket = /var/lib/mysql/mysql.sock skip-locking key_buffer = 384M max_allowed_packet = 16M table_cache = 512 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size = 32M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 4 max_connections=5000 long_query_time = 20 log-slow-queries=/var/log/mysqld-slow.log [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL safe-updates [isamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [myisamchk] key_buffer = 256M sort_buffer_size = 256M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout [root@sql mysql]# NETSTAT on Web Server # I have changed acutal IP to WEBSERVERIP ( for the webserver ) and DBSERVERIP ( for the db server ip ) [root@web temphotos]# netstat -an | grep DBSERVERIP tcp0 0 WEBSERVERIP:57666 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:54315 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:53293 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53295 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53305 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53304 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53307 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53306 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53309 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53308 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53311 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53310 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53297 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53296 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53299 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53298 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53301 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53300 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53303 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53302 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53257 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53256 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53259 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53258 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53462 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:56250 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:50288 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:52286 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:53116 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48573 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48574 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48575 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48568 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48569 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48570 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48571 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:48300 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:47460 DBSERVERIP:3306 TIME_WAIT tcp0 0 WEBSERVERIP:42983 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:44035 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:44974 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:43896 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:37081 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:40781 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:36109 DBSERVERIP:3306 ESTABLISHED tcp0 0 WEBSERVERIP:35119 DBSERVERIP:3306 ESTABLISHED [root@web temphotos]# Lots of TIME_WAIT , i have truncated the logs. Both servers have 1000M interface and db server has 3G ram 2cpu @ 2.27GHz (Intel(R) Xeon(R) CPU). Sql server is not loaded at all , infact load never goes beyond 1. Can you advise how can we get rid of
mysqld not writing to err-log
Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue.
Re: mysqld not writing to err-log
Hi Rick, It was't a permission issue though. I did compare with another server, that had the same thing working. [r...@server1 mysql]# ls -lh /var/log/mysqld.log -rw-r--r-- 1 mysql root 60M Jun 16 18:40 /var/log/mysqld.log [r...@server1 mysql]# grep err-log /etc/my.cnf err-log=/var/log/mysqld.log [r...@server1 mysql]# On this server now, I have commented err-log and use log_error , which happily writes to the log file r...@server [~]# grep err-log /etc/my.cnf #err-log=/var/log/mysqld.log r...@server [~]# r...@server [~]# ls -lh /var/log/mysqld.log -rw-r--r-- 1 mysql root 728 Jun 16 17:51 /var/log/mysqld.log r...@server [~]# err-log was under safe_mysqld, so neither any fault in its position in my.cnf ( http://mysqldatabaseadministration.blogspot.com/2006/07/err-log-or-log-error.html) [safe_mysqld] #err-log=/var/log/mysqld.log On Wed, Jun 17, 2009 at 12:21 AM, lists-mysql replies-lists-b3z1-my...@listmail.innovate.net wrote: This might be an issue of permissions (/var/log is controlled by root). So, you might try touch-ing /var/log/mysqld.log and setting ownership to mysql.mysql. Then [re-]start mysqld and see if the log gets written to. Related, the logging entry in my my.cnf is log-error= not your err-log=. I haven't looked in the documentation to see if your version should work, but mine does, so you might want to try it. - Rick Original Message Date: Tuesday, June 16, 2009 11:18:25 PM +0530 From: Madan Thapa madan.feedb...@gmail.com To: mysql@lists.mysql.com Subject: mysqld not writing to err-log Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue. End Original Message
Re: mysqld not writing to err-log
Thanks Eric, it worked. = On Tue, Jun 16, 2009 at 11:55 PM, Eric Bergen eric.ber...@gmail.com wrote: Madan, Try: [mysqld] log_error=/var/log/mysqld.log On Tue, Jun 16, 2009 at 10:48 AM, Madan Thapamadan.feedb...@gmail.com wrote: Hi, mysqld is not writing to err-log and i do not see any log generated upon mysql service restart. In my.cnf = [safe_mysqld] err-log=/var/log/mysqld.log pid-file=/var/lib/mysql/mysql.pid open_files_limit=16384 r...@server[~]# ll /var/log/mysqld.log -rw-r--r-- 1 mysql root 0 Nov 24 2008 /var/log/mysqld.log r...@server[~]# Please advise what i can look for to fix this issue.
full text search ft_min_word_len
Hi, When I use ft_min_word_len=3 under [mysqld] section, mysql fails to start. ft_min_word_len=3 parameter has been added under [myisamchk] only , which works. mysql Ver 14.12 Distrib 5.0.67, for redhat-linux-gnu (i686) using readline 5.1 Anyone know about this?
Re: Compiled configure string
On Fri, Dec 12, 2008 at 12:05 AM, voipcore voipcore voipc...@gmail.comwrote: Hi All, first post here :) I was wondering how can I view the ./configure ... ... string with which a mysql server installation was compiled with. Is that possible ? Thanks for supporting, have a nice day. Mike To make sure that you don't loose hope, I can say it is possible, but I don't remember the way to find it out. If I find it, before someone updates, I will let you know.
strange character while mirgation mysql db in arabic
Hi, I am having problem migrating a arabic vbulletin database from a remote server to my server, as it shows strange characters , or what we call charset or encoding issue and I have not been able to get past and fix it. On my server mysql show variables like 'char%' ; +--++ | Variable_name| Value | +--++ | character_set_client | latin1 | | character_set_connection | latin1 | | character_set_database | latin1 | | character_set_filesystem | binary | | character_set_results| latin1 | | character_set_server | latin1 | | character_set_system | utf8 | | character_sets_dir | /usr/share/mysql/charsets/ | +--++ 8 rows in set (0.00 sec) mysql mysql mysql show variables like 'colla%' ; +--+---+ | Variable_name| Value | +--+---+ | collation_connection | latin1_swedish_ci | | collation_database | latin1_swedish_ci | | collation_server | latin1_swedish_ci | +--+---+ 3 rows in set (0.00 sec) mysql mysql On remote sever, I have phpmyadmin acess to the database in arabic. When I take a back of the database using phpMyAdmin , I infact see strange characters in the backup sql file itself. Please advise Thanks
mysql master slave security considerations and planning
Hi, First of all I am newbie to MySQL administration, so if my questions seem weird, please forgive me. I am testing a mysql-slave setup on test nodes and was successful in setting it up. Here are the results. Please note this is a basic setup, I will explore more options that can be added to my.cnf , if required. mysql show slave status\G *** 1. row *** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.0.1 Master_User: slaveuser Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.02 Read_Master_Log_Pos: 98 Relay_Log_File: mysql-relay-bin.03 Relay_Log_Pos: 235 Relay_Master_Log_File: mysql-bin.02 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 98 Relay_Log_Space: 235 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: Seconds_Behind_Master: 0 1 row in set (0.00 sec) mysql 2) Can you advise, what security considerations are to be taken into account for a master - slave setup? 2) In order to distribute load, how can we make writes on master only and reads on both? It seems to be, it is something that has to be handled at script coding, instead of administration level, am I right? In that case, should applications be developed to write to master and read on both master and slaves, what considerations are to be taken into account, so that we can switch to slave quickest enough to avoid outage length, should the master node crash? Thank you.
Re: Bin Files.
On Sun, Oct 26, 2008 at 9:13 AM, Madan Thapa [EMAIL PROTECTED]wrote: On Sat, Oct 25, 2008 at 5:50 AM, Grant Peel [EMAIL PROTECTED] wrote: Hi all, I had a server pretty much locked up this morning due to the mysql bin logs filling up the /var filesystem. I had been investigating the my.cnf settings file a white back, got sidetracked, and never finished it. The bin logs are named: /var/db/mysql/myserver-bin.01 /var/db/mysql/myserver-bin.02 /var/db/mysql/myserver-bin.03 /var/db/mysql/myserver-bin.04 I assume a new one is created each time the mysql server is started. I had to shut down all services on the server, delete all these files and restart everything. Anywho, the two questions are: 1. Are these files used when 'fixing' tables, or, are they only used for the master/salve replication? 2. Is it safe to, and how do I, turn off the bin logs altogether (there are no slave servers), 3. What my.cnf settings would you all reccomend for: Mysql4, running on, FreeBSD 6, 1 GB Memory, var fs is 5 GB, 250 virtual domains on the server, of which 50 may be using mysql/php (for bulliten boards etc). (I am reading through the /usr/local/share/mysql *.cnf files ...) Hi, Goto mysql prompt as root and run mysql PURGE MASTER LOGS TO 'myserver-bin.04'; in your case. Here is a real world example with 3 binlogs: === mysql SHOW MASTER LOGS; +-+---+ | Log_name| File_size | +-+---+ | serverName-bin.01 | 499395745 | | serverName-bin.02 | 85511890 | | serverName-bin.03 |135043 | +-+---+ 3 rows in set (0.00 sec) mysql PURGE MASTER LOGS TO 'serverName-bin.04'; ERROR: Target log not found in binlog index mysql PURGE MASTER LOGS TO 'serverName-bin.03'; Query OK, 0 rows affected (1.27 sec) mysql quit Bye Add the line:- === expire_logs_days = 15 in my.cnf to rotate/purge binary log files automatically after 15 days. Then restart mysql service.
time zone
Hi, Can we make adjustments in mysql or php code to display time in php sites in EST , although server runs on CDT ? Thanks
long select query result as as query string on another select statment
Hi, - (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks
Re: long select query result as as query string on another select statment
-- --- (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name='abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar [EMAIL PROTECTED] wrote: what is the issue ur facing. Any syntax error or something else. trying usin IN instead of = ==/ The error is : The query could not be executed, I tried with IN instead of = too. My guess is , double brackets is causing some syntax error in : select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please note: SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' abc.com') it works and give the id number, it seems I am not using the correct syntax for a ))( double bracket ) in the above query. Please advise. Thanks
Re: long select query result as as query string on another select statment
Hi, ok i got it working. it was a typo(lol), i missed from in the initial select statment Wrong ### select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Correct ## select * from db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Thanks On Fri, Sep 26, 2008 at 5:18 PM, Madan Thapa [EMAIL PROTECTED]wrote: -- --- (SELECT id FROM domains where name='abc.com'); gives a result of 124 i am also able to use and get proper results for the following query: select * from domains where id=(SELECT id FROM domains where name=' abc.com '); - Now, select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please correct me the syntax for the above command. I am trying to use the result of one select query as a query string on another. Thanks On Fri, Sep 26, 2008 at 4:29 PM, Ananda Kumar [EMAIL PROTECTED] wrote: what is the issue ur facing. Any syntax error or something else. trying usin IN instead of = ==/ The error is : The query could not be executed, I tried with IN instead of = too. My guess is , double brackets is causing some syntax error in : select * db_users where db_id=(SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name='abc.com')); Please note: SELECT id FROM data_bases where dom_id=(SELECT id FROM domains where name=' abc.com') it works and give the id number, it seems I am not using the correct syntax for a ))( double bracket ) in the above query. Please advise. Thanks
Re: too many connections
On Fri, Sep 19, 2008 at 9:07 PM, Olexandr Melnyk [EMAIL PROTECTED] wrote: Restart MySQL server On 9/19/08, Kinney, Gail [EMAIL PROTECTED] wrote: Hello, We have MySQL 4.0.14 and have just gotten an error: too many connections. we can't connect to our site using MySQL admin. Please help. run the following from shell, mysqladmin flush-hosts and edit my.cnf and add or change the value ( max_connections by default is 100 ) max_connections=500 save my.cnf and restart mysql.
Re: MySQL not running on fresh LAMP install
On Thu, Sep 18, 2008 at 1:33 PM, Glyn Astill [EMAIL PROTECTED] wrote: Part of the problem is that I can't stop the server. Oh, so it does start? I thought it also wasn't starting, if it's not starting then stopping it will fail. Can you check the following : In my system, which is CentOS , I have [EMAIL PROTECTED] [~]# ls /usr/share/mysql/my-* /usr/share/mysql/my-huge.cnf* /usr/share/mysql/my-innodb-heavy-4G.cnf* /usr/share/mysql/my-large.cnf* /usr/share/mysql/my-medium.cnf* /usr/share/mysql/my-small.cnf* [EMAIL PROTECTED] [~]# your path may depend on installation. Copy any of the cnf file to /etc/my.cnf i.e. mv /etc/my.cnf /etc/my.cnf.bak cp /usr/share/mysql/my-medium.cnf /etc/my.cnf Then try to restart mysql /etc/init.d/mysql restart Also notice permissions/ownerships on the following folder [EMAIL PROTECTED] [~]# ls -lhd /var/lib/mysql/ drwxr-x--x 35 mysql mysql 4.0K Sep 18 04:11 /var/lib/mysql// [EMAIL PROTECTED] [~]# ls -lhd /var/lib/mysql/mysql drwx--x--x 2 mysql mysql 4.0K Sep 11 18:12 /var/lib/mysql/mysql/ [EMAIL PROTECTED] [~]# If problem persist, throw some light on how exactly you installed mysql (version as well as OS and path of mysql install )
Re: starting mysqld on Linux fedora
On Sun, Aug 31, 2008 at 4:36 AM, Alex Katebi [EMAIL PROTECTED] wrote: Hello, Is there any documentation on how to start the mysqld server. I have installed version 6.0 server and client. But I don't know how to start the server correctly. Refer to : http://dev.mysql.com/doc/refman/6.0/en/unix-post-installation.html what is the path you chose for mysql 6 install?
Re: MySQL Administrator Login Error
On Sat, Aug 9, 2008 at 1:08 AM, AndrewMcHorney [EMAIL PROTECTED]wrote: Hello I entered it incorrectly in my email. Andrew At 10:34 AM 8/8/2008, David Giragosian wrote: On 8/8/08, AndrewMcHorney [EMAIL PROTECTED] wrote: Hello I tried to login using localhost and root as user id with no password since I temporarily did not enter one when I installed the software. I am getting the following error message: MySQL Error Number 1045 Access denied for user '[EMAIL PROTECTED]' (using password:NO) The port select is 3036. What does this error mean? Andrew The default MySQL port is 3306, I believe. Do you have a typo above or have you set MySQL to use a different port? David. No virus found in this incoming message. Checked by AVG. Version: 7.5.524 / Virus Database: 270.6.0/1601 - Release Date: 8/8/2008 9:02 AM - It seems you are using some GUI software for mysql administration: This might be a solution for you: http://forums.mysql.com/read.php?35,9919,164372#msg-164372
Re: MySQL Administrator Login Error
On Fri, Aug 8, 2008 at 10:55 PM, AndrewMcHorney [EMAIL PROTECTED]wrote: Hello I tried to login using localhost and root as user id with no password since I temporarily did not enter one when I installed the software. I am getting the following error message: MySQL Error Number 1045 Access denied for user '[EMAIL PROTECTED]' (using password:NO) The port select is 3036. What does this error mean? Andrew [EMAIL PROTECTED] ~]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME mysqld 31792 mysql3u IPv4 434089 TCP *:mysql (LISTEN) [EMAIL PROTECTED] ~]# kill -9 31792 [EMAIL PROTECTED] ~]# /etc/init.d/mysql start Mysql Started [EMAIL PROTECTED] ~]# mysql Access denied for user 'root'@'localhost' (using password: NO) [EMAIL PROTECTED] ~]# /etc/init.d/mysql stop [EMAIL PROTECTED] ~]# mysqld_safe --skip-grant-tables [EMAIL PROTECTED] ~]# [EMAIL PROTECTED] ~]# mysql -u root mysql mysql use mysql; mysql update user set password=PASSWORD(NEW-ROOT-PASSWORD) where User='root'; mysql flush privileges; mysql quit NEW-ROOT-PASSWORD above is whatever password you want to set. [EMAIL PROTECTED] ~]# # /etc/init.d/mysql stop [EMAIL PROTECTED] ~]# lsof -i :3306 COMMAND PID USER FD TYPE DEVICE SIZE NODE NAME mysqld 31792 mysql3u IPv4 434089 TCP *:mysql (LISTEN) [EMAIL PROTECTED] ~]# kill -9 31792 [EMAIL PROTECTED] ~]# fuser -k /usr/bin/mysqld_safe( in case you see mysql process is still running ) [EMAIL PROTECTED] ~]# vi /root/.my.cnf [EMAIL PROTECTED] ~]# cat /root/.my.cnf [client] user=root pass=NEW-ROOT-PASSWORD [EMAIL PROTECTED] ~]# [EMAIL PROTECTED] ~]# service mysql restart [EMAIL PROTECTED] ~]# mysql Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 1 to server version: 4.1.21-standard-log Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql quit Bye [EMAIL PROTECTED] ~]# Ensure that you are using the default port 3306. In case not, then try connecting in the following way , assuming port is 3036. [EMAIL PROTECTED] ~]# mysql -p 3036
Re: multiple mysql servers
Resolved.
multiple mysql servers
Hello, I already have mysql 4 installed on datadir /var/lib/mysql and basedir /var/lib on RHEL5 running on port 3306. I want to run another instance in /home/mysql5 with datadir = /home/mysql5 and basedir=/home on port 3307. I installed as follows, but when I try to start mysql5, it fails to start or kills itself. Note I am trying to use mysql5 as user/group for mysql5 instance. Here are the details: [root@ server ~]# groupadd mysql5 [root@ server ~]# useradd mysql5 -g mysql5 [root@ server ~]# ./configure --prefix=/home/mysql5 --with-unix-socket-path=/home/mysql5/mysql5.sock --with-tcp-port=3307 --with-pic --with-mysqld-user=mysql5 [root@ server ~]# It installed successfully. [root@ server ~]# pwd /home/mysql5 [root@ server ~]# ls bin ibdata1 ib_logfile0 ib_logfile1 include info jobs1.localhelpwanted.net.err lib libexec man mysql mysql-test share sql-bench test [root@ server ~]# ls bin/ libexec/ bin/: comp_err myisampack mysqlbug mysqld_safe mysql_fix_privilege_tables mysqlshow mysql_upgraderesolveip innochecksum my_print_defaults mysqlcheck mysqldump mysqlhotcopymysql_tableinfo mysql_upgrade_shell resolve_stack_dump msql2mysql mysql mysql_client_test mysqldumpslow mysqlimport mysqltest mysql_waitpid myisamchk mysqlaccessmysql_config mysql_explain_log mysql_install_dbmysqltestmanagerc mysql_zap myisam_ftdump mysqladmin mysql_convert_table_format mysql_find_rows mysql_secure_installation mysqltestmanager-pwgen perror myisamlog mysqlbinlogmysqld_multi mysql_fix_extensions mysql_setpermission mysql_tzinfo_to_sql replace libexec/: mysqld mysqlmanager [root@ server ~]# Next I installed database directory [root@ server ~]# /home/mysql5/bin/mysql_install_db --defaults-file=/etc/my5.cnf --user=mysql5 Whenever I try to start mysql5 on port 3307, it fails ( there is no firewall issue in this case ) : [root@ server ~]# /home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec [1] 10676 [root@ server ~]# Starting mysqld daemon with databases from /home/mysql5 STOPPING server from pid file /home/mysql5/mysql5.pid 080715 07:39:40 mysqld ended [1]+ Done/home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec [root@ server ~]# /home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec --skip-grant-tables [1] 10723 [root@ server ~]# Starting mysqld daemon with databases from /home/mysql5 STOPPING server from pid file /home/mysql5/mysql5.pid 080715 07:40:03 mysqld ended [1]+ Done/home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec --skip-grant-tables [root@ server ~]# Please advise. Thanks
Re: multiple mysql servers
On Tue, Jul 15, 2008 at 7:05 PM, Madan Thapa [EMAIL PROTECTED] wrote: Hello, I already have mysql 4 installed on datadir /var/lib/mysql and basedir /var/lib on RHEL5 running on port 3306. I want to run another instance in /home/mysql5 with datadir = /home/mysql5 and basedir=/home on port 3307. I installed as follows, but when I try to start mysql5, it fails to start or kills itself. Note I am trying to use mysql5 as user/group for mysql5 instance. Here are the details: [root@ server ~]# groupadd mysql5 [root@ server ~]# useradd mysql5 -g mysql5 [root@ server ~]# ./configure --prefix=/home/mysql5 --with-unix-socket-path=/home/mysql5/mysql5.sock --with-tcp-port=3307 --with-pic --with-mysqld-user=mysql5 [root@ server ~]# It installed successfully. [root@ server ~]# pwd /home/mysql5 [root@ server ~]# ls bin ibdata1 ib_logfile0 ib_logfile1 include info jobs1.localhelpwanted.net.err lib libexec man mysql mysql-test share sql-bench test [root@ server ~]# ls bin/ libexec/ bin/: comp_err myisampack mysqlbug mysqld_safe mysql_fix_privilege_tables mysqlshow mysql_upgraderesolveip innochecksum my_print_defaults mysqlcheck mysqldump mysqlhotcopymysql_tableinfo mysql_upgrade_shell resolve_stack_dump msql2mysql mysql mysql_client_test mysqldumpslow mysqlimport mysqltest mysql_waitpid myisamchk mysqlaccessmysql_config mysql_explain_log mysql_install_dbmysqltestmanagerc mysql_zap myisam_ftdump mysqladmin mysql_convert_table_format mysql_find_rows mysql_secure_installation mysqltestmanager-pwgen perror myisamlog mysqlbinlogmysqld_multi mysql_fix_extensions mysql_setpermission mysql_tzinfo_to_sql replace libexec/: mysqld mysqlmanager [root@ server ~]# Next I installed database directory [root@ server ~]# /home/mysql5/bin/mysql_install_db --defaults-file=/etc/my5.cnf --user=mysql5 Whenever I try to start mysql5 on port 3307, it fails ( there is no firewall issue in this case ) : [root@ server ~]# /home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec [1] 10676 [root@ server ~]# Starting mysqld daemon with databases from /home/mysql5 STOPPING server from pid file /home/mysql5/mysql5.pid 080715 07:39:40 mysqld ended [1]+ Done/home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec [root@ server ~]# /home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec --skip-grant-tables [1] 10723 [root@ server ~]# Starting mysqld daemon with databases from /home/mysql5 STOPPING server from pid file /home/mysql5/mysql5.pid 080715 07:40:03 mysqld ended [1]+ Done/home/mysql5/bin/mysqld_safe --user=mysql5 --defaults-file=/etc/my5.cnf --datadir=/home/mysql5 --pid-file=/home/mysql5/mysql5.pid --port=3307 --ledir=/home/mysql5/libexec --skip-grant-tables [root@ server ~]# Please advise. Thanks It seems to me though, using mysql as the user instead of mysql5 would get it working. However any advise is appreciated. Thanks