Tag Archives: Mysql

Easiest way to filter out text in a MySQL field

August 29, 2017 6:53 pm /

Example:  Removing the “.0” from an Excel import into MySQL

  • MYTABLE – The MySQL Table Name
  • FIELDNAME – The field you wish to edit the data
  • Make sure you have a primary key defined, I usually set mine as “id” for the table. If you are using a different name for the primary key ID, replace the “id” with the different name.

UPDATE MYTABLE AS T1, MYTABLE as T2 SET T1.FIELDNAME = replace(T2.FIELDNAME,’.0′,”) WHERE T2.id = T1.id;

Before running this query, make sure you make a backup in case it doesn’t work correctly.

Posted in: MySQL / Tagged:

Fix: LoadError: no such file to load — mysql2/mysql2 with jRuby and Warbler

August 7, 2013 12:03 pm /

I recently transitioned a Ruby on Rails Passenger server to jRuby with Tomcat and encountered an error caused by the Gemfile.

“no such file to load — mysql2/mysql2
LoadError: no such file to load — mysql2/mysql2”

The fix? JRuby needs a MySQL adapter named “activerecord-jdbcmysql-adapter” in order to it to work properly.

# Original MySQL Adapter --
# gem 'mysql2', '~>0.3.13'
#
# New Adapter for jRuby
gem 'activerecord-jdbcmysql-adapter'

Adding the ‘activerecord-jdbcmysql-adapter’ gem and commenting out the original “mysql2” gem fixed the issue. I recommend using the “warble executable war” to troubleshoot these errors since you can quickly run a “java -jar myapp.war” to test it out.

Tested with jRuby 1.7.4, Rails 3.2.12

Posted in: Ruby on Rails / Tagged: , ,

MySQL Stats: Find number of rows in a database

January 10, 2011 7:48 pm /

If you ever have the need to find the answer to the following:

  • What are the number of tables in a MySQL database?
  • What are the number of rows in a MySQL database?
  • How much space is the MySQL database using up on the server?
  • How many tables there are in a MySQL server?
  • How many rows there are in a MySQL server?

One of the quickest ways to find out MySQL reporting stats is using PHPMyAdmin. The software is usually installed on a number of servers by default, so its just a matter of locating where in the program to find this info.

This is an example of what the stats look like in my test environment server.

Read More →

Posted in: MySQL, PHP / Tagged: , ,

MySQL Tutorial: Fetch last 10 rows without reversing order

January 5, 2010 4:57 pm /

Here’s the quickest way to sort data in MySQL after retrieving the last 10 entries with the LIMIT and ORDER BY DESC commands. If you simply do a ORDER BY DESC LIMIT 10, this will return the last 10 results in reverse order (newest first). Adding an inline SELECT command will sort the results after retrieving the last 10 items. (oldest first)

SQL Command

$sql = "SELECT * FROM (SELECT * FROM [TABLE] WHERE ORDER BY modified_date DESC LIMIT 10) AS tbl ORDER BY tbl.modified_date"

This will retrieve the last 10 rows while sorting by date chronologically. I used this SQL command for the graphing API I was using (LibChart). This allowed my graph to display the most current data on the right side as opposed to the left.

Posted in: MySQL / Tagged: , , ,

MySQL: Avoiding the Mysql:Too many connections error

July 17, 2009 2:37 pm /

 If your site experiences the infamous “Mysql : Too many connections” error, you will need to tune your MySQL server to handle the load of a heavily used website. 

 There are some configuration variables that you can adjust to make your site handle more connections.

  • An easy way to identify which variables need to be changed can be done through phpMyAdmin. Login to your phpMyAdmin control panel and search for the “Show MySQL runtime information” module. Scroll down the page and look for entries that are in red. These are suggested areas in your MySQL configuration that need to be looked at.

  • The wait_timeout default in MySQL is 28840 seconds (8 hours), which can seriously limit the number of open connections you have on your page. I adjusted mine to 60 seconds. This frees up the thread after idle timeout, which allows for more connections to connect. The 8 hour timeframe is fine if you only have a few connections to the server. For popular websites, its best to make this value as low as possible. Set this variable to the maximum time your page typically runs. For instance, if you have a page that needs to retrieve some data processed from another server (60-80 seconds) and you only use one mysql_connect, you may lose the mysql connection for processing the remaining parts of the page if your wait_timeout is too low. If the processing takes much longer, a workaround is to use multiple mysql_connects to prevent an idle connection from disconnecting.

  • DNS issues. If your DNS suddenly stops working, this will consume all your MySQL connections. MySQL tries to resolve the IP address for every connection it gets, so this will start creating many more threads than released if it DNS is down. In Linux, edit the /etc/hosts file and manually add in your web server IPs/names to this (if you have static IPs) This will speed up performance by resolving IPs locally as opposed to through DNS. You can identify DNS issues or connection attempts through “Failed attempts” section in phpMyAdmin runtime page. A significant number of failed attempts can mean either someone is trying to break into your MySQL box or your DNS isn’t working right.

  • Increase the key_buffer value (for MyISAM tables) This helps handle indexes for temporary tables. If you have lots of key misses, its important to increase the size of the key_buffer. You can do this by comparing the key_reads vs. key_read_requests value.

  • Increase the innodb_buffer_pool_size (for innodb tables)

  • Increase the table_cache value. If you look at your phpMyAdmin runtime “Opened_tables” field and see a large number (over 1,000) you will need to increase your table_cache. This will keep tables in memory to speed up peformance. Default is only 64. I’ve upped mine to over 2,000 since I have many databases and tables running on my server.

  • For more information, check out these resources:
    http://www.ibm.com/developerworks/linux/library/l-tune-lamp-3.html 
    http://dev.mysql.com/tech-resources/presentations/presentation-oscon2000-20000719/index.html

 

Posted in: MySQL / Tagged: , , ,

Post Navigation

← Older Posts