Friday, November 27, 2015

MySQL database naming conventions.

1. Overview


This document lists out the Database standards recommendations to be followed by developers for Database design in MYSQL Technologies.
            MySQL 5.5.27 is a new version of the 5.5 production release of the world's most popular open source database. MySQL 5.5.27 is recommended for use on production systems.

2. Database design


·      Database configuration to include :
DBMS                         : Mysql 5.5
Data base Engine    : InnoDB
Collation                    : utf8_general_ci
·      If a data item has more than 2 data values to be made available for selection by end users, Create a Parameter table to store the different data values for the said data item.
·      Data width for data items with numeric data type for storage of Amount data should be (12,2)
·      Data width for data items with Text data type should be 50 or 100 instead of big & small.
·      All Tables need to include a field with constraint defined as Primary key with Auto increment setup. This field will be referred as Record ID with data type int and data width of 8 digits. Whenever there is a relationship between two Tables, this field should be made used as Foreign key.
·      HTML file to be made used to store error/warning  messages along with unique message ID.
·         Data type for each primary key column should be “ int”.
·         Use same variable type for similar fields across tables.
·         Use VARCHAR instead of CHAR. Because more space and performance effective
e.g., 
Value
CHAR(4)
Storage Required
VARCHAR(4)
Storage Required
''
'    '
4 bytes
''
1 byte

·         For fixed length column as code use CHAR instead of VARCHAR
·         Specify data type and its size as per Functional Requirements.
·         All table should include the below 6 columns :

lld_rec_status
to identify active ( 1 ) & inactive ( 0 ) records
lld_createdby
To store login id of the user who has created the record.
lld_createddt
To store database server date on which the record was created.
lld_modifiedby
To store login id of the user who has modified the record.
lld_modifieddt
To store database server date on which the record was modified.
lld_ipaddress
To store ipaddress of the desktop which was made used by user.

3.  Naming Standards:


·      Database name should be same as Project name (for e.g., crm).
·      Schema name should be same as Database name (for e.g., crm)
·      All tables should be prefixed with Project Name and related module ( for e.g., roles to store roles details in crm Project ).
·      All master data table should suffix with table name _m.( for e.g., parameter_m)
·      Should not make use of spaces or any special characters in table name (Alpha number + Underscore are the only things allowed).
·      Column names should be prefixed by respective table name .
·      Column name should relate to the data being stored in the respective field .
·      Should not make use of spaces or any special characters to be used in column names (Alpha number + Underscore are the only things allowed).
·      Primary key column name should be “record_id” and prefixed with table name.( for e.g., ofo_rec_id).
·      Primary Key Field name to be prefixed with table name. (for e.g., ofo_rec_id ) with Primary key constraint.
·      Give names to all field level constraints.(for e.g., ofo_pk_rec_id, reg_fk_ofo_rec_id)

4. Normalization and Referential Integrity:


·      Use single column primary key in a table as much as possible.
·      Use unique constraints where ever required.
·      Use Referential integrity at Child Tables when ever required.
·      Avoid ON DELETE CASCADE
·      On-Line Transaction Processing (OLTP) should be at least 3NF.
(3 Normal Form: Every non-prime attribute is non-transitively dependent on every candidate key in the table. The attributes that do not contribute to the description of the primary key are removed from the table. In other words, no transitive dependency is allowed)
·      Evaluate every one-to-many relationship as a potential many-to-many relationship

5. General



  • We should include primary keys, foreign keys and simple indexes to improve performance.
  • Only use Unicode data types in very special circumstances.
  •  Identify the critical tables and ensure minimum number of required records are available at implementation.
  • Event, trigger, procedure should be prefixed with “project name_event_”, “projectname_trigger_”, project name_“proc_” followed by respective name

Thursday, November 26, 2015

Difference between MySQL GROUP_CONCAT() and CONCAT_WS()


SELECT GROUP_CONCAT(CONCAT_WS(' ', First_name,Last_name)) FROM users ORDER BY user_id ASC;

+------------------------------------------------+
| GROUP_CONCAT(CONCAT_WS(' ', fname,lname))      |
+------------------------------------------------+
| swarm robots,prabhakarq honni,Dillip Nayak,asdasdasd
+------------------------------------------------+
1 row in set (0.03 sec)




SELECT CONCAT_WS(' ', First_name,Last_name) FROM crm_users ORDER BY user_id ASC;



+------------------------------------------------+
| CONCAT_WS(' ', fname,lname)     |
+------------------------------------------------+
|
swarm robots
prabhakarq honni
Dillip Nayak
asdasdasd asdddasd
love love
+------------------------------------------------+


Wednesday, October 7, 2015

ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction (MYSQL)


mysql> delete from users;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction


You should consider increasing the lock wait timeout value for InnoDB by setting the innodb_lock_wait_timeout, default is 50 sec

mysql> show variables like 'innodb_lock_wait_timeout';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| innodb_lock_wait_timeout | 50    |
+--------------------------+-------+
1 row in set (0.01 sec)


[mysqld]
innodb_lock_wait_timeout=120
and restart mysql. If you cannot restart mysql at this time, run this:

SET GLOBAL innodb_lock_wait_timeout = 120;
You could also just set it for the duration of your session

SET innodb_lock_wait_timeout = 120;

OR


You can see list of locked tables by-

 show open tables where in_use>0;
Now see the thread which is using this table

  show full processlist;
now you can kill that thread or wait to complete it.

  show full processlist;



+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+
| Id   | User       | Host                                        | db         | Command | Time | State | Info                  |
+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+
| 8970 | idba | localhost                               | honnikery| Sleep   |  107 |       | NULL                  |
| 8971 | idba | localhost                              | honnikery| Sleep   |  195 |       | NULL                  |
| 9052 | idba | localhost                                   | honnikery| Sleep   | 4027 |       | NULL                  |
| 9054 | idba |localhost                                  | honnikery| Sleep   | 2134 |       | NULL                  |
| 9055 | idba | localhost                                 | honnikery| Sleep   | 2165 |       | NULL                  |
| 9059 | idba | localhost                                   | honnikery| Query   |    0 | init  | show full processlist |
+------+------------+---------------------------------------------+------------+---------+------+-------+-----------------------+


now you can kill that thread or wait to complete it.

mysql> kill 9052;
Query OK, 0 rows affected (0.00 sec)

Monday, September 28, 2015

How to view a list of MySQL users and their privileges




mysql> select user,host from mysql.user;

+------------------+--------------+
| user             | host                 |
+------------------+--------------+
| root             | %                    |
| root             | 127.0.0.1         |
| honnikery   | localhost     |
| dba             | %                     |
| test              | localhost           |
+------------------+--------------+


5 rows in set (0.01 sec)


mysql> show grants for 'root'@'%';

+------------------------------------------------+
| Grants for root@%                              |
+------------------------------------------------+
| GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' .... |
+------------------------------------------------+
1 row in set (0.00 sec)

Tuesday, September 15, 2015

How to change the foreign key referential action in MYSQL

first step, drop old constraint: (reference)

alter table `honnikery`
drop foreign key `fk_name`;

notice only constraint is deleted

second step, add new constraint:

alter table `honnikery`
add constraint `fk_name`
    foreign key (`bidar`) references `honnikery1` (`bidar`) on delete cascade;

Wednesday, September 2, 2015

how to connect to mysql database from command line in windows

 I am using XAMPP software for programming and MySQL as a Database.

Go command line

C:\>d:

d:\>cd xampp\mysql\bin

d:\xampp\mysql\bin> mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 47
Server version: 5.6.24 MySQL Community Server (GPL)

Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| 007                |
| 02                 |
| assignment         |
| bidar              |
| cdcol              |
| cr_debug           |
| crm                |
| demo               |
 || mydb               |
| mysql              |
| om                 |
| orthos             |
| performance_schema |
| phpmyadmin         |
| prabha             |
| signup             |
| test               |
| webauth            |
+--------------------+
23 rows in set (0.00 sec)

Friday, August 28, 2015

MYSQL commands in linux

First to connect to MySQL command line.

# mysql -u root -p
Password:


view all available databases.

mysql> SHOW DATABASES;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| test    
  honnikery          |                    |
+--------------------+

Select a Database in MySQL

user database-name;


Show Tabels in a Database

show tables;

Show MySQL Database Fields & Field Formats
describe table-name;


Convert MyISAM to INNODB
ALTER TABLE ENGINE=INNODB;

Repair Broken Table(s) in MySQL
repair table broke_table_name;