Get MYSQL database size in Java

Question

My MYSQL-Table has got 3 tables.

I can't get the given examples working.

In Java, using java.sql.*, I would like to know how much space is left in my entire database, for the space is very limited.

Here is my approach:

// this.conn = DriverManager.getConnection("jdbc:mysql://......");

public long remainingSpace() {
    String sql = "SELECT table_schema AS \"Database\", "
        + "ROUND(SUM(data_length + index_length) / 1024, 2) AS \"Size (KB)\" "
        + "FROM information_schema.TABLES GROUP BY table_schema;";
    ResultSet rs;
    PreparedStatement prpSttm = this.conn.prepareStatement(sql);
    rs = prpSttm.executeQuery();
    rs.next();
    long remainingSpace = rs.getInt("Size (KB)");
    return remainingSpace;
}

This returns 9 (KB).

phpMyAdmin instead tells me there is 64 KiB of data in one of my tables: Table size according to phpMyAdmin

How can I get the correct size in Java?

score 4 · Accepted Answer · answered Dec 27 '14 at 14:37

You are just showing the size of the first database the ResultSet returns, you probably have more databases in your DBMS (there are some default system DBs). You should use a while loop.

while (rs.next()) {
    System.out.println(rs.getString("Database") " | " + rs.getString("Size (KB)"));
}

*From my experience, ~9KB is usually the size of the default information_schema database.

**As this query returns databases in alphabetical order, I guess the name of your database starts with a letter >=i; thus, surprisingly your code will work if you rename your database with a name starting eg from 'a' :) :)

this works! the given code snipped prints out the following for me: `information_schema | 9.00 sql560710 | 96.00 ` thanks! — phil294, Dec 27 '14 at 15:21

score 1 · Answer 2 · answered Dec 27 '14 at 14:37

You are only reading one row from your ResultSet in your Java program. But your query generates one row for each distinct database (schema) on your server. You may be reporting information for some database other than yours. You'd best read all the rows of that ResultSet.

Beware: Disk space exhaustion for MySQL can be catastrophic. You really don't want to completely run out of disk space. If you're wise you'll leave a margin of at least a few GiB.

Get MYSQL database size in Java

2 Answers2