Jump to content
Larry Ullman's Book Forums


  • Posts

  • Joined

  • Last visited

Everything posted by codeLogic

  1. Hi Larry, Thank you for the response to my post. Below is a summary of my troubleshooting this problem. I hope you are having a great summer :-) Regards. A solution found: I have been working on this problem for almost a month. I had given up and made a stop gap by setting up two linux boxes with PHP5 MySQL and Apache and began developing on those instead. Then, after several weeks and alot of work a solution was found. Below is an outline of the troubleshooting steps I used to get MAC 10.5.8 working with a custom my.cnf file. I hope this helps someone avoid the stress I just went through to get this DB working again. ;-) When you want to use my.cnf to customize your MySQL options/configurations on OSX MAC you will notice several things: 1. There is no my.cnf file. 2. You have to create a my.cnf file. And, when you create it, it will make your MySQL inoperable. You cannot turn MySQL on after creating this file. So, if you desire to have a my.cnf file set up on your MAC, get ready to troubleshoot. The main file used for trouble shooting is your mysql error log. The error log is located in the file below: /usr/local/mysql/data/macbook-2.local.err Use the sudo option to acces it. sudo vi /usr/local/mysql/data/macbook-2.local.err ONE Create the my.cnf file. Sample my.cnf files are located in your MySQL install at: /usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/ . Just copy the file you want to your /etc directory. sudo cp /usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/my-medium.cnf /etc/my.cnf After I did this, MySQL could not be turned on. Before I changed the permissions on the my.cnf file I was getting the below error message. 110826 15:20:04 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended 110826 15:20:12 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data Warning: World-writable config file '/etc/my.cnf' is ignored 110826 15:20:12 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 110826 15:20:12 [Note] Plugin 'FEDERATED' is disabled. ^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 110826 15:20:12 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 110826 15:20:12 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. The main issue in this error message to be solved is: ""Warning: World-writable config file '/etc/my.cnf' is ignored"" This told me my.cnf that I created was not being used. Changed permission levels to 770: sudo chmod 770 my.cnf TWO Change settings in the my.cfn file. Make sure the socket is set correctly: socket = /tmp/mysql.sock ## Note: Originally, i tried the below path because unix command locate mysql.sock returns /var/mysql/mysql.sock socket = /var/mysql/mysql.sock But, this breaks systems preferences MySQL panel functionality and causes other problems. I fixed this at the end of the troubeshooting process. Possibly, having it set correctly here will avoid some of the below errors. Anyhow, for my situation, the next error was the main problem and a beast to fix. THREE Error message related to the ./mysql-bin.index was now displayed when I tried to turn on the database. Below is the error message: 110826 15:53:59 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 110826 15:54:00 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive ^G/usr/local/mysql/bin/mysqld: File './mysql-bin.index' not found (Errcode: 13) 110826 15:54:00 [ERROR] Aborting 110826 15:54:00 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 110826 15:54:00 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended The ./mysql-bin.index is not found. Based on the above error message, file "'./mysql-bin.index' not found (Errcode: 13)"" and the below web post Post. http://www.linuxquestions.org/questions/linux-software-2/mysqld-var-log-mysql-mysql-bin-index-not-found-480676/ I realized I needed to do two things: 1.) Change the log-bin settings in the my.cnf file. The my.cnf file has login-bin settings in two places. I changed both to the below value. I do not know if both have to be changed but that is what I did and it helped solve the issue. log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin 2.) Make the ./mysql-bin.index file. Based on the above web post, I created a script called ./mysql-bin.index I put the below values in it: ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006 ./mysql-bin.000008 ./mysql-bin.000001 ./mysql-bin.000002 ./mysql-bin.000003 ./mysql-bin.000004 ./mysql-bin.000005 ./mysql-bin.000006 ./mysql-bin.000007 ./mysql-bin.000008 ./mysql-bin.000009 ./mysql-bin.000016 Then, I saved the file and changed the permissions to it. Save at: /usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql-bin.index sudo chmod 770 ./mysql-bin.index Note: I think I created it using. touch ./mysql-bin.index. Then, I opened it in vi and put the above values in it. (ls -A to view all the hidden files in a folder) FOUR Change the ownership files of every file in the mysql directory. On MAC the MySQL directory is: /usr/local/mysql-5.1.36-osx10.5-x86_64/ The below is the UNIX command to change the ownership values: chown -R mysql:mysql /usr/local/mysql-5.1.36-osx10.5-x86_64/ Some posts said this command needed to be run in order to allow MySQL to create the files/directories it needed to make. I am not sure if this is true but I did it. FIVE After this I was getting the below types of error messages everytime I tried to turn on the database. 110826 16:56:22 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 110826 16:56:22 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive 110826 16:56:22 [Note] Plugin 'FEDERATED' is disabled. ^G/usr/local/mysql/bin/mysqld: Can't find file: './mysql/plugin.frm' (errno: 13) 110826 16:56:22 [ERROR] Can't open the mysql.plugin table. Please run mysql_upgrade to create it. 110826 16:56:22 InnoDB: Operating system error number 13 in a file operation. InnoDB: The error means mysqld does not have the access rights to InnoDB: the directory. InnoDB: File name ./ibdata1 InnoDB: File operation call: 'create'. InnoDB: Cannot continue operation. Based on the above error message I was looking for ./mysql/plugin.frm. I was using the unix command "locate" to find the plugin.frm but it could not find it. Many posts talked about the /data file permissions needing to be changed. However, using the unix command line sudo chmod 777 command on the /data directory was fruitless. I could not get in the /usr/local/mysql-5.1.36-osx10.5-x86_64/data directory. But, Mac's Finder did the trick. You use the Go -> Go to Folder command in Finder's menu bar. Put the path to MySQL in the Finder Go ->Go to Folder text box. For me this was: /usr/local/mysql-5.1.36-osx10.5-x86_64/ Then, you change the permissions on: /usr/local/mysql-5.1.36-osx10.5-x86_64/data To find hidden files in finder you use: /usr/local/mysql/data/. (note the "." at the end of the path) I changed the permissions on these folders so the root MAC user could have read access to them. Plus, mysql has ownership too. After changing this setting the above error message went away. SIX When trying to turn on MySQL through System preference I would get the error log message below: "mysqld_safe A mysqld process already exists message" This means MySQL is turned on. :-) Wow, we are making progress! Around this time I would also get a "cannot find /tmp/mysql.sock" message when I tried to login using /usr/local/mysql/bin/mysql -u root -p. So, I made a symbolic link from ln -s /var/mysql/mysql.sock /tmp/mysql.sock lrwxr-xr-x 1 root wheel 10 Aug 26 17:39 mysql.sock -> mysql.sock I thought the symbolic link in the /tmp file would allow MySQL to find the file in the directory it was looking in: /tmp/mysql.sock. But, this issue may have been caused by my not having the correct socket setting in my.cnf. If you did step TWO as above your should not have this problem. In short, I am not sure if you need to do this step. But, I did it. SEVEN I was still not able to log in and the "mysqld_safe A mysqld process already exists" message was still showing up in the error log. Then, I found this post which partially solved the final step in getting my DB working again: http://www.sitepoint.com/forums/mysql-182/mysqld-process-already-exists-very-green-newbie-292123.html In short, the database is already on. But, system preferences does not show you that it is on. However, I was able to access the database by using the below command: sudo /usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql -u root -p However, it was still a strange start up. Here is how I started it: 1.) Through system preferences turn it on. The result is an error message. And, system prefences shows the database is off. However, if you read the error message the mysqld is ready for connections. But, you cannot log in normally. A normal login would use: /usr/local/mysql/bin/mysql -u root =p This results in ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) But, if I use: sudo /usr/local/mysql-5.1.36-osx10.5-x86_64/bin/mysql -u root -p I get in. Strange. O, now found the solution for ERROR 2002 (HY000) :-) In my.cnf the socket settings were incorrect. SHOULD BE: socket = /tmp/mysql.sock NOT socket = /var/mysql/mysql.sock This solved the error. Now, the system preference panel works again. wheew, my MAC machine has been restored. Last step: Put in the minimum keywork length setting in [mysqld] section in the my.cnf file. Wanting to do this is what caused all this trouble. But, now it works fine :-) ft_min_word_len = 3 YEAAHHH!!!! I got my database back. And, I can change my database settings in the my.cnf file. :-) MySQL works fine now. SHORT SUMMARY: Why did creating a new my.cnf file cause so much havoc?? sigh..... It looks like the main reasons were incorrect permission settings on certain files and directories, incorrect settings in my.cnf script, and not having a ./mysql-bin.index. I am not a MySQL/Unix expert, so any advice to help better understand the above issues would be greatly appreciated. Cheers !! QUESTIONS: 1.) What is a mysql.sock file? What does it do? This thing is a mystery. Sometimes it was in the /private/tmp/mysql.sock and then other times it was in /private/var/mysql.sock. Mysterious..... 2.) Why does MySQL have this file in /private/var/mysql.sock and also in the /tmp file. Does it create a new .sock file in the ./tmp directory everytime you turn it on? 3.) Was not having a ./mysql-bin.index file a result of mysql.sock not working properly? 4.) Why did I have to change permissions of all MySQL files files using chown -R mysql:mysql /usr/local/mysql-5.1.36-osx10.5-x86_64/ ? Was this necessary? I read on some web posts that my.cnf has a "user= " setting. You can set it to user=mysql and then MySQL fuctions as the "mysql" etc. Is this important? BELOW IS THE MY.CNF FILE CONTENTS I AM CURRENTLY USING # Example MySQL config file for medium systems. # # This is for a system with little memory (32M - 64M) where MySQL plays # an important part, or systems up to 128M where MySQL is used together with # other programs (such as a web server) # # You can copy this file to # /etc/my.cnf to set global options, # mysql-data-dir/my.cnf to set server-specific options (in this # installation this directory is /usr/local/mysql/data) or # ~/.my.cnf to set user-specific options. # # In this file, you can use all long options that a program supports. # If you want to know which options a program supports, run the program # with the "--help" option. # The following options will be passed to all MySQL clients [client] #password = your_password port = 3306 #socket = /var/mysql/mysql.sock socket = /tmp/mysql.sock # Here follows entries for some specific programs # The MySQL server [mysqld] port = 3306 #socket = /var/mysql/mysql.sock socket = /tmp/mysql.sock skip-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M # min word length setting for searches. ft_min_word_len = 3 # Don't listen on a TCP/IP port at all. This can be a security enhancement, # if all processes that need to connect to mysqld run on the same host. # All interaction with mysqld must be made via Unix sockets or named pipes. # Note that using this option without enabling named pipes on Windows # (via the "enable-named-pipe" option) will render mysqld useless! # #skip-networking # Replication Master Server (default) # binary logging is required for replication # Originally was log-bin=mysql-bin log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin # binary logging format - mixed recommended binlog_format=mixed # required unique id between 1 and 2^32 - 1 # defaults to 1 if master-host is not set # but will not function as a master if omitted server-id = 1 # Replication Slave (comment out master section to use this) # # To configure this host as a replication slave, you can choose between # two methods : # # 1) Use the CHANGE MASTER TO command (fully described in our manual) - # the syntax is: # # CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>, # MASTER_USER=<user>, MASTER_PASSWORD=<password> ; # # where you replace <host>, <user>, <password> by quoted strings and # <port> by the master's port number (3306 by default). # # Example: # # CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306, # MASTER_USER='joe', MASTER_PASSWORD='secret'; # # OR # # 2) Set the variables below. However, in case you choose this method, then # start replication for the first time (even unsuccessfully, for example # if you mistyped the password in master-password and the slave fails to # connect), the slave will create a master.info file, and any later # change in this file to the variables' values below will be ignored and # overridden by the content of the master.info file, unless you shutdown # the slave server, delete master.info and restart the slaver server. # For that reason, you may want to leave the lines below untouched # (commented) and instead use CHANGE MASTER TO (see above) # # required unique id between 2 and 2^32 - 1 # (and different from the master) # defaults to 2 if master-host is set # but will not function as a slave if omitted #server-id = 2 # # The replication master for this slave - required #master-host = <hostname> # # The username the slave will use for authentication when connecting # to the master - required #master-user = <username> # # The password the slave will authenticate with when connecting to # the master - required #master-password = <password> # # The port the master is listening on. # optional - defaults to 3306 #master-port = <port> # # binary logging - not required for slaves, but recommended # Originally was log-bin=mysql-bin log-bin=/usr/local/mysql-5.1.36-osx10.5-x86_64/bin # Point the following paths to different dedicated disks #tmpdir = /tmp/ #log-update = /path-to-dedicated-directory/hostname # Uncomment the following if you are using InnoDB tables #innodb_data_home_dir = /usr/local/mysql/data/ #innodb_data_file_path = ibdata1:10M:autoextend #innodb_log_group_home_dir = /usr/local/mysql/data/ # You can set .._buffer_pool_size up to 50 - 80 % # of RAM but beware of setting memory usage too high #innodb_buffer_pool_size = 16M #innodb_additional_mem_pool_size = 2M # Set .._log_file_size to 25 % of buffer pool size #innodb_log_file_size = 5M #innodb_log_buffer_size = 8M #innodb_flush_log_at_trx_commit = 1 #innodb_lock_wait_timeout = 50 [mysqldump] quick max_allowed_packet = 16M [mysql] no-auto-rehash # Remove the next comment character if you are not familiar with SQL #safe-updates [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout
  2. Greetings Larry and forum users :-) Any help with the below problem would be greatly appreciated :-) Please see my signature at the bottom of this message for details on my OS, MySQL and PHP versions. -- A short summary of the problem -- I have been building a commerce site based on Larry's books Effortless-Ecommerce, PHP & MySQL (second edition), & PHP 5 advanced. All of them are great books! After re-reading Larry's PHP & MySQL chapter on Advanced SQL and MySQL for the 30th time, I got the idea to change MySQL's minimum keyword length from four characters to three characters. See page 201 for the reference to changing minimum keyword length. As a result, I did some research online and decided to modify the ft_min_word_len setting. This way I can build apps that FULL TEXT search for 3 character words instead of just words over 4 characters long. I tried to change ft_min_word_len setting from 4 to 3 by creating a my.cnf file and changed the ft_min_word_len setting to 3. I restarted my computer hoping the changes would take effect and boom my database stopped working. I cannot even turn it on. aiya! Please note my MySQL database server has been working fine for three years. -- Details on how I created the problem -- 1.) I chose a sample options file template that mysql includes in /usr/local/mysql/support-files and saved it in the /etc/ directory as my.cnf. I chose the my-large.cnf file as my template. Here are the permissions for the new my.cnf file -rw-r--r-- 1 root wheel 4775 Aug 1 15:17 /etc/my.cnf Below are mysql docs that led me to do this: http://dev.mysql.com/doc/refman/5.0/en/fulltext-fine-tuning.html http://dev.mysql.com/doc/refman/5.1/en/option-files.html http://dev.mysql.com/tech-resources/articles/mysql_intro.html 2.) I used vi to add the need setting that I desired and saved the file as: /etc/my.cnf I added the setting ft_min_word_len = 3 Now, the [mysqld] section of my.cnf file looks like this: [mysqld] port = 3306 socket = /tmp/mysql.sock skip-locking key_buffer_size = 256M max_allowed_packet = 1M table_open_cache = 256 sort_buffer_size = 1M read_buffer_size = 1M read_rnd_buffer_size = 4M myisam_sort_buffer_size = 64M thread_cache_size = 8 query_cache_size= 16M # Try number of CPU's*2 for thread_concurrency thread_concurrency = 8 ft_min_word_len = 3 3.) The problem begins. After making the above changes, I tried to restart MySQL and it would not restart. So, I restarted my computer and MySQL still would not restart. If I try: /usr/local/mysql/bin/mysql mysql -u root -p I get the below error message: ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2) MySQL documentation says if you get this error "Make sure that the server is running. If it is not, clients cannot connect to it. For example, if an attempt to connect to the server fails with a message such as one of those following, one cause might be that the server is not running:" http://dev.mysql.com/doc/refman/5.1/en/access-denied.html This is my problem. The server is not on and I cannot turn it on. Many MySQL posts regarding ERROR 2002 recommend checking the error log file for more details about the problem. This was not easy to figure out how to do so here is the command I used to get the error log. sudo vi /usr/local/mysql/data/macbook-2.local.err Hopefully, this will help someone. When I try to turn on MySQL from the System Preference panel in MAC, the error log message created is: 110801 18:09:42 mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data 110801 18:09:42 [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive ^G/usr/local/mysql/bin/mysqld: File './mysql-bin.index' not found (Errcode: 13) 110801 18:09:42 [ERROR] Aborting 110801 18:09:42 [Note] /usr/local/mysql/bin/mysqld: Shutdown complete 110801 18:09:42 mysqld_safe mysqld from pid file /usr/local/mysql/data/macbook-2.local.pid ended Interestingly, if I try to log in from the command line using: /usr/local/mysql/bin/mysql mysql -u root -p It does not create an error message in the error log. Only when I try to log in from System Preference is an error message created in the error log. After doing the above trouble shooting, it looks like I have the following problems: a.) According to ERROR 2002 there is a problem with /tmp/mysql.sock. However, if I open the /tmp/ directory there is not mysql.sock file. However, if I run the command: locate mysql.sock. It returns: /private/tmp/mysql.sock as the location of the mysql.sock file. So, it looks like mysql.sock exists but I cannot find it or modify it's permissions. Strange. Please advise how to find this file. Are the permissions of mysql.sock needing to be changed? b.) I have a [Warning] and (Errcode: 13): [Warning] Setting lower_case_table_names=2 because file system for /usr/local/mysql/data/ is case insensitive ^G/usr/local/mysql/bin/mysqld: File './mysql-bin.index' not found (Errcode: 13) From this message I assume the major problem is with the ./mysql-bin.index file. However, I cannot find this file either. Is there a permissions problem with this file? Please advise how to find this file or solve this problem. One other thing I tried to do was restart the DB server from the command line using: /usr/local/mysql-5.1.36-osx10.5-x86_64/support-files/mysql.server restart If I do this I get the following error: ERROR! MySQL manager or server PID file could not be found! Starting MySQL . ERROR! Manager of pid-file quit without updating file. My database was working great for 3 years. However, I tried to create one my.cnf file and I effectually broke MySQL. I cannot even turn it on or restart it. Any advice to solve this issue would be much appreciated :-) Regards.
  3. Hello, Sonal, Before I had UTF-8 problems using MySQL when storing Chinese characters. I was trying to store Chinese characters in the data base. However, after I stored characters in the MySQL database, MySQL would change the UTF-8 characters to nonsense. When I retrieved the data it was a mess. However, I found that the problem was related to MAMP. After setting up my system to not use MAMP but independently install PHP, MySQL and Apache it all worked perfect. Possibly, your problem is related to WAMP or MAMP or your IIS set up. That was my issue before. It seemed like the programmers that put MAMP together set it up in a UTF-8 unfriendly way. However, before I independently installed PHP, MySQL and Apache, I had a another work around in the MAMP system. I would convert the string into binary format, then store it. When retrieving the characters I would then reconvert the binary back to the orginal string format. This worked well. I believe it was the base64_encode() and base64_decode() functions that I used to encode and decode strings before and after inserting into the database. Sorry, I had this problem about 5 years ago, so I am a little vague on the function I used. But, I do remember encoding and decoding the string before and after DB storage did work. A search on php.net's site using the term encode should help you find the right function to use to encode the string. Note: Did you test your DB queries using other UTF-8 characters that you know should work? For example, test Chinese character storage. If Chinese characters do not work then you know it is the database. If Chinese characters work then you know it is a font issue. Anyhow, I hope this helps you a little :-) Regards.
  4. Hi Larry, This post is in regard to an idea regarding improving session security. I have read through your PHP & MySQL books (version 2 and 3). Thank you for writing such fine books. They are excellent :-) Here is my question: Would an IP fingerprint value test along with the HTTP_USER_AGENT test improve _SESSION security? Under your current heightened security method in PHP6&MySql (page 358) we rely on the below $_SESSION['HTTP_USER_AGENT'] to create a user agent value at login. Then, each time a page is accessed after login this value is tested. <?php // Create the agent session variable. $_SESSION['agent'] = md5($_SERVER['HTTP_USER_AGENT']); // On logged in pages / restricted access pages. // Run this test $user_print = md5($_SERVER['HTTP_USER_AGENT']); if ( (!isset($_SESSION['agent'])) || ( $user_print != ($_SESSION['agent']) )) { // Refuse Access if tests passed. } ?> Every time a restricted access page is accessed we test this _SESSION agent value against the current fingerprint value of the user. If they do not match then we know the session has been highjacked and we kick the user out. Thus, if someone wants to highjack a user's session they must 1.) mimic the real users operating system and browser settings etc. 2.) Highjack the current users session id. I am thinking about adding another test using $_SERVER['REMOTE_ADDR']. According to the PHP5 manual the $_SERVER['REMOTE_ADDR'] returns IP address from which the user is viewing the current page. Then, we would have a second test which would be something like: <?php // Set this session variable in the login page. $_SESSION['ipRecord'] = md5( $_SERVER['REMOTE_ADDR']); // Run this test on the Logged in pages. $userIp = md5($_SERVER['REMOTE_ADDR']); if ( (!isset($_SESSION['ipRecord'])) || ( ($userIp != ($_SESSION['ipRecord']) )) { // Refuse Access if tests passed. } ?> If we made this $_SESSION['ipRecord'] variable, then we would test the $_SESSION['agent'] value and the $_SESSION['ipRecord'] value everytime a restricted access page would be requested. I was thinking this would require a malicious user to mimic two user settings rather than one. They would be required to: 1.) Know what the user's OS and browser are and then have to make their machine imitate these values. 2.) They would also have to know the users IP address and fake the IP address on their machine too. Is it hard for a malicious user to fake an IP address? Would this improve the security of a site? Or, would it just increase the complexity of my code without any security benefit? Any advice would be much appreciated. :-) Regards.
  • Create New...