Category 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:

Upgrading Debian Lenny to Squeeze with MySQL for row-based-replication

October 28, 2011 5:46 pm /

I completed an upgrade of Debian Lenny to Squeeze on a production database server over the weekend. It went quite well and I had zero downtime thanks to my secondary database servers running in-place. One of the biggest benefits to running Squeeze is that MySQL runs at version 5.1.49. Lenny only supports up to MySQL 5.0.

Row-based-replication is safer to replicate data to other servers since all changes are replicated. Prior to MySQL 5.1.14, updates to the mySQL database were not replicated. They were updated via statements (e.g. GRANT, REVOKE). This can potentially cause data-consistency between the master and slave servers, if one runs a INSERT and SELECT (w/o ORDER BY) as the order of the results will be different. If you had a SQL statement on the MySQL database that ends up erroring out, your slave connection to the master server would stop.

Read More →

Posted in: debian, MySQL / 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: , ,

Disable autostart of MySQL in Debian and Ubuntu

July 7, 2010 12:39 pm /

The quickest way to disable autostart of services in Debian is to utilize a convenient curses interface to selectively choose which services to autostart in different run levels. Its so much easier to use than update-rc.d.  Its good for instances where you are migrating services out of an existing box, such as removing the mysql server without uninstalling.

The package is called:
sysv-rc-conf

(Install it using aptitude or apt-get install)

Posted in: debian, Linux, MySQL / Tagged:

Fix: MySQL Error Illegal mix of collations

April 12, 2010 3:00 pm /

The error message looked like:
“Illegal mix of collations (latin1_swedish_ci,COERCIBLE) and (utf8_general_ci,IMPLICIT) for operation ‘find_in_set'”

I ran into this problem today while importing text from a CSV file. Evidently, the text format of the document had UTF characters, which caused CodeIgniter to error out when it tried to insert it to the database. The line had French characters (The ç in Français) My database fields by default are set to “latin1_swedish_ci” character set.

The fix?
I decided the best way to fix this was to set the Collation option for that specific field in the MySQL database to  “utf8_general_ci” from “latin1_bin”. Re-import the data back into the database and it should preserve the character set. Its a good idea to set text fields to UTF-8, especially if you anticipate getting UTF characters.

Posted in: MySQL, PHP / Tagged:

Post Navigation

← Older Posts