Watch Mouse: mysql

Showing posts with label mysql. Show all posts

show tables like in mysql

SHOW TABLES WHERE Tables_in_mydbname LIKE '%reward%'

suppose mydbname is 'blogposts'

SHOW TABLES WHERE Tables_in_blogposts LIKE '%reward%'

Get Column Name, Column Data Type and Column Length from MySQL Database

USE information_schema;

SELECT
COLUMN_NAME,
DATA_TYPE,
COLUMN_TYPE
FROM COLUMNS
WHERE TABLE_NAME = 'account_registration'

Take dump of mysql database and ignore some table with it

mysqldump -u username -p database --ignore-table=database.table1

--ignore-table=database.table2 > database.sql

Get global variable , time zone mysql

INSERT OR UPDATE + MYSQL

Insert if not there, update on duplicate.

Query: INSERT INTO TABLE_NAME (COLUMN_NAME1, COLUMN_NAME2) VALUES (?, ?) ON DUPLICATE KEY UPDATE COLUMN_NAME1=VALUES(COLUMN_NAME1), COLUMN_NAME2=VALUES(COLUMN_NAME1) + 2;

And the key means the, unique key. It is advised not to use the "on duplicate key update", when there are 2 or more number of unique keys (primary key is also a unique key) present in a table.

Get Column Meta Data like size, name type and all + sql + java

// Get column meta data from the database
    public static ResultSet getColumnData(final Connection conn, final String tableName, final String columnName) {
        ResultSet rs = null;
        try {
            if (conn != null && meta == null) {
                meta = conn.getMetaData();
            }
            rs = meta.getColumns(conn.getCatalog(), null, tableName, columnName);
        } catch (SQLException e) {
            logger.error("Exception occured while getting column meta data", e);
        }
        return rs;
    }

    // Get column size from the column meta data
    public static int getColumnSize(final Connection conn, final String tableName, final String columnName) {
        int columnSize = 0;
        try {
            ResultSet rs = getColumnData(conn, tableName, columnName);
            if (rs != null) {
                columnSize = rs.getInt("COLUMN_SIZE");
            }
        } catch (SQLException e) {
            logger.error("Exception occured while getting column size", e);
        }
        return columnSize;
    }

Java SimpleDateFormat always returning January for Month

Change the pattern string from "yyyy/MM/DD" to "yyyy/MM/dd"

SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd");

Eg:

String today = "2013/06/25";

SimpleDateFormat formatter = new SimpleDateFormat("yyyy/MM/dd");
try {           
     Date date = formatter.parse(today);
     long dateInLong = date.getTime();
           
     System.out.println(date);
     System.out.println(dateInLong);
     System.out.println(new Timestamp(dateInLong));
} catch (Exception e) {
     e.printStackTrace();
}

o/p:

Tue Jun 25 00:00:00 IST 2013
1372098600000
2013-06-25 00:00:00.0

To find the schema that conatins the given table name

1. select table_name, table_schema from information_schema.Tables order by table_name;

Find Table Structure using a Query in MySql

Display / Select all tables from all the data bases in MySql

Selects / Displays all the tables that are present in the data base from all the data bases;

SELECT *
FROM information_schema.Tables;

Use this following command for more precise data.

SELECT table_name, table_type, engine
    -> FROM information_schema.tables
    -> WHERE table_schema = 'db5'
    -> ORDER BY table_name DESC;

MySQL Subquery Returns more than one row

Sub Query returning mutliple values causes this problem.

To handle it properly use the GROUP BY feature for this.

Retrieving the data as List from HQL

Retrieving the data from the query as collection of pair.

Suppose the query is "Select username, count(id)..". If the query

returns a list String, Long pair then the query actually

returns it as list<Object[]>. So collect the result in a

variable List<Object[]>.

And then iterate over the object array. To get the values,

Cast the corresponding object.

 Query q1 = em.createQuery("SELECT e.name, e.salary

 FROM Employee e");
    List&lt;Object[]&gt; result1 = q1.getResultList();
    for (Object[] resultElement : result1) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];
        ...
    }

    Query q2 = em.createQuery("SELECT e.name, e.salary,

e.department FROM Employee e");
    List&lt;Object[]&gt; result2 = q2.getResultList();
    for (Object[] resultElement : result2) {
        String name = (String)resultElement[0];
        Double salary = (Double)resultElement[1];
        Department dept = (Department)resultElement[2];
        ...
    }

    Query q3 = em.createQuery("SELECT COUNT(e),

SUM(e.salary) FROM Employee e");
    Object[] result3 = (Object[])q3.getSingleResult();
    Long count = (Long)result3[0];
    Double sum = (Double)result3[1];

SQL Where IN + HQL

FROM
    Foo
WHERE
    Id = :id AND
    Bar IN (:barList)

To check in a set of results or values.

Use Query.setParameterList(), Javadoc here.

Retireve data from Data Base into a List of Map

final String SQL_QUERY = "select new map(l1.labelName as parentName,l2.labelName as childName,l1.jspPage as jspPage) from MenuBean as l1, MenuBean as l2 " +
                    "where l1.menuId=l2.parentMenuId and l2.typeId =:typeId ORDER BY l1.labelName,l2.labelName";

            final Query query = hibernateSession.createQuery(SQL_QUERY);
            query.setParameter("typeId", 1);

            List<Map> result = PersistenceServiceImpl.cast(query.list());

MySQL Error Number 1005 Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)

http://verysimple.com/2006/10/22/mysql-error-number-1005-cant-create-table-mydbsql-328_45frm-errno-150/

MySQL Error Number 1005
Can’t create table ‘.\mydb\#sql-328_45.frm’ (errno: 150)
If you get this error while trying to create a foreign key, it can be pretty frustrating. The error about not being able to create a .frm file seems like it would be some kind of OS file permission error or something but this is not the case. This error has been reported as a bug on the MySQL developer list for ages, but it is actually just a misleading error message.
In every case this is due to something about the relationship that MySQL doesn’t like. Unfortunately it doesn’t specify what the exact issue is.
First Steps:
If you have admin permission on the server, you may want to start by running the MySQL command “SHOW INNODB STATUS” (or MySQL 5.5 “SHOW ENGINE INNODB STATUS”) immediately after receiving the error. This command displays log info and error details. (Thanks Jonathan for the tip)
If your script runs fine on one server, but gives an error when you try to run it on a different server, then there is a good chance that #6 is the problem. Different versions of MySQL have different default charset setting and you may have unknowingly assigned different charsets on the different servers.

Known Causes:
Below is a running list of known causes that people have reported for the dreaded errno 150:

The two key fields type and/or size is not an exact match. For example, if one is INT(10) the key field needs to be INT(10) as well and not INT(11) or TINYINT. You may want to confirm the field size using SHOW CREATE TABLE because Query Browser will sometimes visually show just INTEGER for both INT(10) and INT(11). You should also check that one is not SIGNED and the other is UNSIGNED. They both need to be exactly the same. (More about signed vs unsigned here).
One of the key field that you are trying to reference does not have an index and/or is not a primary key. If one of the fields in the relationship is not a primary key, you must create an index for that field. (thanks to Venkatesh and Erichero and Terminally Incoherent for this tip)
The foreign key name is a duplicate of an already existing key. Check that the name of your foreign key is unique within your database. Just add a few random characters to the end of your key name to test for this. (Thanks to Niels for this tip)
One or both of your tables is a MyISAM table. In order to use foreign keys, the tables must both be InnoDB. (Actually, if both tables are MyISAM then you won’t get an error message – it just won’t create the key.) In Query Browser, you can specify the table type.
You have specified a cascade ON DELETE SET NULL, but the relevant key field is set to NOT NULL. You can fix this by either changing your cascade or setting the field to allow NULL values. (Thanks to Sammy and J Jammin)
Make sure that the Charset and Collate options are the same both at the table level as well as individual field level for the key columns. (Thanks to FRR for this tip)
You have a default value (ie default=0) on your foreign key column (Thanks to Omar for the tip)
One of the fields in the relationship is part of a combination (composite) key and does not have it’s own individual index. Even though the field has an index as part of the composite key, you must create a separate index for only that key field in order to use it in a constraint. (Thanks to Alex for this tip)
You have a syntax error in your ALTER statement or you have mistyped one of the field names in the relationship (Thanks to Christian & Mateo for the tip)
The name of your foreign key exceeds the max length of 64 chars. (Thanks to Nyleta for the tip)

mysql error 1005 errno 121 when trying to set a foreign key

There is a problem while creating a foreign key to a table with the mysql. So foreign key includes an extension like fk_menu_id_idbfk1 as the key name.

Create the foreign key like this whenever you faced this error.

DROP TABLE IF EXISTS `as_menu_action`;
CREATE TABLE IF NOT EXISTS `as_menu_action` (
`id` tinyint(2) NOT NULL auto_increment,
`menu_id` tinyint(2) NOT NULL,
`action_name` varchar(100) NOT NULL,
PRIMARY KEY (`id`),
KEY `menu_id` (`menu_id`),
CONSTRAINT FOREIGN KEY (`menu_id`) REFERENCES `as_menu` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

Recover Mysql Password

Step # 1 : Stop mysql service

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld.

Step # 2: Start to MySQL server w/o password:

# mysqld_safe --skip-grant-tables &
Output:

[1] 5988
Starting mysqld daemon with databases from /var/lib/mysql
mysqld_safe[6025]: started

Step # 3: Connect to mysql server using mysql client:

# mysql -u root
Output:

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 1 to server version: 4.1.15-Debian_1-log
Type 'help;' or '\h' for help. Type '\c' to clear the buffer.
mysql>

Step # 4: Setup new MySQL root user password

mysql> use mysql;

mysql> update user set password=PASSWORD("NEW-ROOT-PASSWORD") where User='root';

mysql> flush privileges;

mysql> quit

Step # 5: Stop MySQL Server:

# /etc/init.d/mysql stop
Output:

Stopping MySQL database server: mysqld
STOPPING server from pid file /var/run/mysqld/mysqld.pid
mysqld_safe[6186]: ended
[1]+  Done                    mysqld_safe --skip-grant-tables

Step # 6: Start MySQL server and test it

# /etc/init.d/mysql start

# mysql -u root -p

http://www.cyberciti.biz/tips/recover-mysql-root-password.html

Adding User Accounts to mysql

Social media for business

View more presentations from Vasanth Kumar

http://dev.mysql.com/doc/refman/5.1/en/adding-users.html

When we want to connect to a remote DB, we need to allow the present machine to connect to that remote DB.
For that we need add an user, with the host name like "192.168.0.120" with necessary permissions.
We can also set the option "Allow Remote machines to connect to this DB" while installing mysql.

mysql -u root -p
root
use mysql;
create user 'username'@'ip' identified by 'password';
grant all privileges on databaseSchema.* to 'username'@'192.168.0.120';
flush privileges;

mysql -u root -p
root
use mysql;
create user 'root'@'192.168.0.120' identified by 'root';
grant all privileges on dcon.* to 'root'@'192.168.0.120';
flush privileges;

(Have a DB Schema dcon: inside we have some tables: setting dcon.* means allow for all the tables inside the dcon schema)