Kurinchi Blogger Scribbles …


Archive for the ‘MySql’ Category

Aug 09
2014

MySQL Error: Got a packet bigger than ‘max_allowed_packet’ bytes

Last updated: September 9th, 2014

When trying to import large SQL dump, you often get the above message. Remedy for this issue is to increase the packet size of MySQL’s daemon by setting a packet size of max_allowed_packet. Sudo to your account and set the following

root $ mysql -u admin -p

mysql> set global net_buffer_length=1000000;

mysql> set global max_allowed_packet=1000000000;

In the command line, issue the command

$ mysql –max_allowed_packet=128M -uUSER -pPASSWORD database < database.sql.dump

Apr 04
2014

Mysql: Another MySQL daemon already running with the same unix socket.

Last updated: April 4th, 2014

After VPS reboot, you may encounter this issue and the fix is simple. Follow these stops to prevent this error.

$ service mysqld stop

$ mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

$ service mysqld start

Jan 09
2014

How to find the collation and character set of MySQL tables?

Last updated: January 9th, 2014

To find the collation of tables within MySQL database, you can use one of the commands listed below

mysql> show table status from exampledb;

mysql> SELECT TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA=’exampledb';

 

To find the character set used in a MySQL table

mysql> SELECT tbl.table_name, CCSA.character_set_name FROM information_schema.`TABLES` tbl, information_schema.COLLATION_CHARACTER_SET_APPLICABILITY CCSA WHERE CCSA.collation_name = tbl.table_collation AND tbl.table_schema = “schemaname”;

Jan 08
2014

How to find the MySQL collation and character set?

Last updated: January 9th, 2014

To find the collation of a database

mysql> SELECT DEFAULT_COLLATION_NAME FROM information_schema.SCHEMATA WHERE SCHEMA_NAME = ‘exampledb’ LIMIT 1;

+————————+
| DEFAULT_COLLATION_NAME |
+————————+
| utf8_general_ci |
+————————+

 

To find the characterset of a MySQL database

mysql> SELECT default_character_set_name FROM information_schema.SCHEMATA S WHERE schema_name = “exampledb”;

Jul 09
2013

How to setup, install Apache, PHP and MySQL on Mac OS?

Last updated: July 9th, 2013

Apache server is installed by default on Mac. Access the configuration settings for Apache by going to

$ cd /etc/apache2/
$ vi httpd.conf

To enable php, edit httpd.conf and uncomment the line
#LoadModule php5_module libexec/apache2/libphp5.so

Uncomment other lines that you may want to load in your system. As you are enabling PHP in your system, you may want to change the DirectoryIndex to
DirectoryIndex index.php index.html

Default group/user settings inside Apache on Mac is _www:_www
(more…)

Jun 16
2013

MySQL Error #1449 – The user specified as a definer does not exist in database

Last updated: June 16th, 2013

The reason why this message is shown is because the user for the view does not exist in the database.

There are few ways by which we can remove this error

i) Alter statement

ii) Create the specified user in the database

iii) Modify the .frm files related to the view and set the definer

We will show the Alter statement method to achieve the desired results

# To fix it:

mysql> ALTER DEFINER = ‘root’@’localhost’ VIEW `mytableview` AS select * from cities;

mysql> SELECT * FROM mytableview;

Sometimes it so happens that the view statement will be a big selection of fields with joins in it. Export the table first to get the required SELECT statement for the view and then change the DEFINER

 

Mar 17
2013

How to fix Hibernate MySql connection timeout issue (solved)?

Last updated: June 14th, 2013

When we began creating an application using Struts2 framework with Hibernate and MySQL, we ran into a problem which was related to MySQL timing out its connection after a span of 8 hours when left unused. It conceived a lot of time at our end but we were able to nail down the issue at the end.

Steps that we had taken to correct the issue related to Hibernate MySql connection timeout problem are listed below

– Download Hibernate C3P0 and copy .jar files
– Set c3p0.properties
– Make changes to hibernate.cfg.xml
– Test MySQL connection timeout
– Hibernate, MySQL connection timeout related error messages
(more…)

Nov 24
2012

How to find the port used by mysql?

Last updated: November 24th, 2012

List the listening ports and check if mysql is listed on it.

$ netstat -tln | grep mysql

If you cannot find the port number for mysql view my.cnf file to see if you can find the port number. If you see a line “skip-networking” try commenting out the line and then restart mysql daemon.

$ vi /etc/my.cnf

[mysqld]
set-variable=local-infile=0
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
skip-networking

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

$ service mysqld restart

Sep 09
2012

SQL Injection: Whitelist validation vs. blacklist validation

Last updated: September 8th, 2012

Whitelist validation refers to data validation techniques such as checking the data type, data length, input range, nature of data by its format (for e.g. phone number will be ten digits separated by hyphens satisfying the format nnn-nnn-nnnn). Regular expressions may be used for format validation in inputs.

Blacklist validation refers to rejecting data based on a know bad list filter. This is not a powerful solution as the list of possible exclusions will be many and it is difficult to implement all possible scenarios. Blacklist validation should be used in conjunction with whitelist validation but in cases where whitelist validation cannot be applied at least blacklist validation should be implemented.

Sep 08
2012

SQL Injection: Database Code Security in Programming

Last updated: September 8th, 2012

Dynamic SQL (concatenated sql string) is a coding practice where by the queries are built in the program and sent to SQL Server for execution. This can allow the code to be injected into the dynamic queries causing a damage to the database.

A good alternative is to use parameterised queries where placeholders are set for the variables. The possibility of queries getting infected with injected code is completely removed with parameterized queries.

In addition to parameterized queries, it is alway a best practice to sanitize the input parameters before using them in queries.

Also the data input should be encoded appropriately especially in case of dynamic sql usage and to apply appropriate encoding when extracted from the database to avoid cross-site script execution.


Valid HTML 4.01 Strict  Valid HTML 4.01 Strict