CODEWITHSUNDEEP
javamysqldatabase
phil294
phil294

Reputation: 10892

Get MYSQL database size in Java

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?

Upvotes: 7

Views: 2407

Answers (2)

O. Jones
O. Jones

Reputation: 108836

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.

Upvotes: 1

Kostas Kryptos
Kostas Kryptos

Reputation: 4111

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' :) :)

Upvotes: 4

Related Questions