• PHP
  • Ruby on Rails
  • MySQL
  • Linux
    • SELINUX
    • Fedora
    • debian
  • Apache
  • nginx
  • AJAX
Albertech.net

Category Archives: Mysql

Easiest way to filter out text in a MySQL field

August 29, 2017 6:53 pm / Albertech.net

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.

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL / Tagged: MySQL

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

October 28, 2011 5:46 pm / Albertech.net

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 →

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: debian, MySQL / Tagged: debian, mysql 5.1, row replication, squeeze, upgrade

MySQL Stats: Find number of rows in a database

January 10, 2011 7:48 pm / Albertech.net

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 →

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL, PHP / Tagged: MySQL, reports, stats

Disable autostart of MySQL in Debian and Ubuntu

July 7, 2010 12:39 pm / Albertech.net

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)

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: debian, Linux, MySQL / Tagged: MySQL debian autostart

Fix: MySQL Error Illegal mix of collations

April 12, 2010 3:00 pm / Albertech.net

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.

Share this:

  • Facebook
  • Google
  • Twitter
  • Print
  • Email
Posted in: MySQL, PHP / Tagged: mysql character set

Post Navigation

← Older Posts
 

Categories

  • AJAX
  • Android
  • Apache
  • Canon Cameras
  • Cloud
  • CMS
  • Computer Mods
  • Conferences
  • Deals
  • debian
  • Fedora
  • Flash
  • Frameworks
  • git
  • Hardware
  • HTML
  • IDE
  • iPhone
  • iPhone App Review
  • jQuery
  • Linux
  • Mac OS X
  • MySQL
  • nginx
  • PHP
  • portfolio
  • Puppet
  • Ruby on Rails
  • Script Reviews
  • SELINUX
  • Software
  • Software Review
  • SQL Server
  • statistics
  • Tech
  • Tomcat
  • Uncategorized
  • VMWARE
  • VPS
  • Windows
  • wordpress
  • Zend Framework

Blogroll

  • DragonAl Flickr
  • Dropbox – Free 2GB Account
  • James' Blog
  • Javascript Compressor
  • PHP Builder Community
  • PHP-Princess.net
  • Rubular – Regular Expression Validator
  • The Scale-Out Blog
  • Tiny MCE

Tags

activation AJAX android antec Apache AWS awstats canon coda codeigniter debian enclosure external free G1 install vmware tools Internet Explorer iphone 5 jquery Linux mx-1 MySQL office 2007 OSX photoshop PHP plugin plugins portfolio redesigned website review rewrite script security SELinux ssh tinymce tutorial upgrade VMWARE vmware server wordpress wordpress mu XSS zend framework
© Copyright 2013 Albertech.net
Infinity Theme by DesignCoral / WordPress
loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.