Changing column datatype in mysql

From time to time I have been changing the column datatype for different reasons. Majorly being for performance and efficiency and some other time based on how the mysql engines would be strong on some types vs the other
here is the simple command i use for changing the column datatype

alter table table_name modify column column_name datatypegoeshere
eg.
ALTER TABLE members MODIFY COLUMN date_created TIMESTAMP;

Allowing others to use mysql from user machine on ubuntu- simple one!

It is customary sometimes to share the part of the database from sandbox to a fellow developer or to access it from the other machine – umm.. is it not how the servers are doing it?? what are you talking about 😉

Anyway here is a simple note to make it happen.1. We need to bind the machine name for the server. to do that
open your my.cnf – inside the /etc/mysql/my.cnf for debianish machines and search for the
[mysqld] and add the following
bind-address = your ip address goes here
you can find your ip address from

ifconfig

then save the file and restart your mysql

sudo /etc/init.d/mysql restart

There are ways to assign for a specified user from the specified ip addresses as well.
Like if you have database db1 and user1 from ip address 168.192.3.4 then

grant select, insert, update on db1.* to 'user1'@'168.192.3.4'

Newer version of mysql exists error while installing on Mac OS

I was reinstalling my mac machine and wanted to install my goodies [PHP, Apache, MySQL] on it and I come up with the newer version error when i try to install mysql.
here is what I gather from different sites and blogs:
Go to the terminal and issue these commands
sudo rm /usr/local/mysql*
sudo rm -rf /usr/local/mysql*
sudo rm -rf /Library/StartupItems/MySQLCOM
sudo rm -rf /Library/PreferencePanes/My*
sudo rm -rf /Library/Receipts/mysql*
sudo rm -rf /Library/Receipts/MySQL*
sudo rm /etc/my.cnf
sudo rm -rf /var/db/receipts/com.mysql.*

This has taken care of the error for me

Number of rows that would have been returned in case of Limit in mysql

The thing is, you would have sql that have LIMIT appended at the end like

select * from sometable limit 25

but what if you wanted to know how many rows would have been returned if it was not limited?
the trivial approach is

select count(*) from sometable

and then issuing query with limit on it.
This is slower solution.
here is the way to do it in a better way

select sql_calc_found_rows * from sometable limit 25;
select found_rows();

Right after execution the select with sql_calc_found_rows, we would get the ‘would have been’ value on the second query.

dumping mysql database ignoring some tables

Mysql comes with a handy tool for dumping database, like from remote server to local machine or vice versa.
Sometimes you might want to dump some tables but not the other here you go!

mysqldump -h[HOST] -u[USERNAME] --port=[PORT] -p --ignore [ignoretablename] databasename > databasename.sql

The database.sql would contain all the schema + data.
Then import it as

mysql -h[host] -u[username] -p databasename < databasename.sql

You can provide -p[password] or if you leave it as -p you would be prompted for password later which is a cool way to proceed.
ENJomYsql!

Adding Unicode character to mysql from Java-hibernate

How to enable/add unicode character in hibernate with mysql

Unicode is ruling, it would come handy when working with non ASCII characters like when dealing with Chinese or Ethiopian alphabet amharic characters.

With internalization being the main concern in softwares, bit it website or mobile application, there is a chance you will need unicode in your application.

Hibernate is an ORM, object relational mapper, that is being used with Java and .net. It will allow to abstract all the database related stuffs with simple interface. In this unicode hibernate tutorial, I will show how you can insert, select update and delete records with unicode.

Why do I get No provider for Entity manager Error and how should I fix it

I will show the fix from the hibernate side for MySQL. But keep in mind that unicode characters have to be enabled on the database side as well with proper encoding.

See how you can avoid could not open hibernate session error

The solution for having unicode characters to be recognized as they are passing though the hibernate world would be done on the config file.

Enabling unicode character in hibernate

Here are the steps to follow:

1. go to your hibernate.cfg.xml file

2. make your the connection url something like: jdbc:mysql://localhost:3306/database?useUnicode=true&characterEncoding=UTF-8 – make sure the the ampersand is the html-encoded ampersand.

Do you know you can have boilerplate java app with maven?

Find k complementary numbers – algorithm in Java

Adding NULL as value to mySql database

Have you wondered how to update/insert NULL as value to the existing row in mysql? Too simple:

UPDATE table_name SET field_name = NULL WHERE some_criteria 

Watch! there is no any quote on the NULL part, if you do so, it would be inserted/updated as string with value “NULL”

Using If else conditional statement on where condition mysql

Using if / else on the mysql would be much easier to express with simple query. Her we go:

Assume you have the following table:

+--------------+--------------+------+-----+---------+----------------+
| Field        | Type         | Null | Key | Default | Extra          |
+--------------+--------------+------+-----+---------+----------------+
| file_id      | int(11)      | NO   | PRI | NULL    | auto_increment |
| file_path    | text         | NO   |     | NULL    |                |
| file_name    | varchar(120) | NO   | MUL | NULL    |                |
| file_size    | bigint(20)   | YES  |     | NULL    |                |
| file_content | text         | NO   | MUL | NULL    |                |
+--------------+--------------+------+-----+---------+----------------+

Then you want to collect records either by file_id or by file_size based on the file_size, if the file size above some value, check the name of the file to contain xls, otherwise, consider only files that have file id more than y

select * from file where if (file_size > x, file_name like '%.xls', file_id > y)

YUP!

PHP is displaying questionmark (???) for unicode

One of the fun things in php-mysql is working with Unicode characters. It is so cooked that, we should almost eat those with a little bit adjustment.
I am not going to tutor on how to use php-mysql unicode stuff – there are a bunch of ’em out there.
Rather I would discuss an error that would be common while doing your stuff..
Once you configure everything and put your unicode to mysql, you might see the output of it being questionmark on the browser – but you see the correct unicode in the database through the phpmyadmin.. it is this single line of code you would ever need…
mysql_query(“SET NAMES ‘utf8′”, conn); where the conn is the result of your connection – mysql_connect();
You can modify it accordingly, for example I am using an object oriented one so I can issue the query from my object – you would need to do this only once.

Happy unicoding..

Cannot find .frm file – mysql error

Especially when you move mysql from Window [see related] to Linux using the data file copy, you might face such and error telling you one of your table’s .frm file is not found.
In this case either of the two can solve the problem.
1. be aware that the .frm, for that matter both MDI and MYI files are case sensitive to linux – as it is for all files. So, if on windows you have test.frm and your application access them as TesT.frm, you may not have problem, but it is a major one on Linux.
2. The other is group and ownership issue, make sure the files are under mysql on both ownership and group – unless you have other configuration for that.

TO change ownership use
sudo chmod -R mysql /path/to/data/files
and for group

sudo chgrp -R mysql /path/to/data/files

Hope it would help somebody.