Category: Database-MYSQL

Command to find MYSQL DB size and table size

Using the command line

You can use the mysql command-line program to determine the sizes of MySQL databases and tables. To do this, follow these steps:

  1. Log in to your account using SSH command
  2. At the command line, type the following command, replacing username with your valid DB login name
    mysql -u username -p
  3. At the Enter Password prompt, type your password. When you type the correct password, the mysql> prompt appears.
  4. To determine the sizes of all of your databases, at the mysql> prompt type the following command:
    SELECT table_schema AS "Database", 
    ROUND(SUM(data_length + index_length) / 1024 / 1024, 2) AS "Size (MB)" 
    FROM information_schema.TABLES 
    GROUP BY table_schema;
    Depending on how many databases you have and how large they are, this command may take a minute or two to complete. After the command finishes, it displays a list of all of your databases and their corresponding size (in megabytes).
  5. To determine the sizes of all of the tables in a specific database, at the mysql> prompt, type the following command. Replace database_name with the name of the database that you want to check:
    SELECT table_name AS "Table",
    ROUND(((data_length + index_length) / 1024 / 1024), 2) AS "Size (MB)"
    FROM information_schema.TABLES
    WHERE table_schema = "database_name"
    ORDER BY (data_length + index_length) DESC;
    After the command finishes, it displays a list of all of the tables and their corresponding size (in megabytes), with the largest table at the top and smallest table at the bottom.

Invalid date in MYSQL causing problem in java application

Exception:
Caused by: java.sql.SQLException: Cannot convert value ‘0000-00-00 00:00:00’ from column 42 to TIMESTAMP.
Solution:
In hibernate data source mapping property please add the highlighted property
jdbc:mysql://yourserver:3306/yourdatabase?zeroDateTimeBehavior=convertToNull
 By default these invalid date will be converted into null values when it comes to Java object.
 Alternate options:

  • exception (the default), which throws an SQLException with an SQLState of S1009.
  • convertToNull, which returns NULL instead of the date.
  • round, which rounds the date to the nearest closest value which is 0001-01-01.

Insert only One Record in Table

ometimes you have table in your system that has only one record like Configuration, Setup, Settings tables.
You want to prevent users from inserting more than one record in this table, To do this you can use the below INDEX .

CREATE UNIQUE INDEX ONE_ROW_INDEX
ON TABLE_NAME (1);

If you try to insert more than one record in previous table you will get below exception

ORA-00001: unique constraint (SCHEMA.ONE_ROW_ALLOWED) violated

http://mahmoudoracle.blogspot.in/2012/06/insert-only-one-record-in-table.html#.T9zPpEcUDIU

Checking size on multiple mysql tables and schemas

You could use the SHOW TABLE STATUS command in MySQL. If you go to your MySQL prompt and select a database, then use the SHOW TABLE STATUS command, like this:

show table status like ‘%’;

you’ll find a column called Data_length which tells you “the length of the data file” for each table. If you simply add the size of each table in the database together, you should have the size of the whole database.

If you run the query which is given below in MySQL Query Browser then you will get the two columns first will display the Data Base Name and the second will display the Data Base Size in MB.

SELECT table_schema “Data Base Name”, sum( data_length + index_length ) / 1024 / 1024 “Data Base Size in MB”
FROM information_schema.TABLES GROUP BY table_schema ;

http://stackoverflow.com/questions/4162010/checking-size-on-multiple-mysql-tables-and-schemas

Import CSV file directly into MySQL

Import CSV file directly into MySQL

Posted by katy8439 in MySQL
Instead of writing a script to pull in information from a CSV file, you can link MYSQL directly to it and upload the information using the following SQL syntax.

To import an Excel file into MySQL, first export it as a CSV file. Remove the CSV headers from the generated CSV file along with empty data that Excel may have put at the end of the CSV file.

You can then import it into a MySQL table by running:

load data local infile ‘uniq.csv’ into table tblUniq fields terminated by ‘,’
enclosed by ‘”‘
lines terminated by ‘\n’
(uniqName, uniqCity, uniqComments)

The fields here are the actual tblUniq table fields that the data needs to sit in. The enclosed by and lines terminated by are optional and can help if you have columns enclosed with double-quotes such as Excel exports, etc.

http://www.tech-recipes.com/rx/2345/import_csv_file_directly_into_mysql/