Bug #20796 MySQL doesn't support more than 32000 databases

• Submitted: 30 Jun 2006 17:24
• Modified: 3 Jul 2006 17:21
• Reporter: Boris Burtin
• Status: Verified
• Category: MySQL Server: DDL
• Severity: S4 (Feature request)
• Version: 4.1
• OS: Linux (Linux)
• CPU Architecture: Any

[30 Jun 2006 17:24] Boris Burtin

Description:
MySQL creates a subdirectory for every database.  As a result, the number of databases is limited by the number of subdirectories that the operating system will support.  We've hit the limit of 32000 subdirectories with ext3 and Veritas on Linux.  To maximize concurrency, our server creates a separate database for each user. We need to be able to scale to tens-to-hundreds of thousands of users on a single server.

How to repeat:
Create 32001 databases on Linux/ext3.

Suggested fix:
One way to solve this problem is to update the filesystem mapping algorithm to create two levels of directories, so that a limit of N databases would be stored in each upper-level directory.

[30 Jun 2006 19:50] James Day

I've escalated your feature request to MySQL's Director of Architecture for his comments.

[1 Jul 2006 20:03] MySQL Verification Team

Thank you for the bug report. I should consider this bug report as
a feature request as James commented. The C client program at the bottom success on Suse Linux 10 with ReiserFS and fails with ext3:

Creating Database
Error (query): Can't create database 'db31997' (errno: 31)

miguel@hegel:~/dbs/4.1$ bin/perror 31
OS error code  31:  Too many links

miguel@hegel:~/dbs/4.1/var$ ls -d -la
drwx------  32000 miguel miguel 512000 2006-07-01 15:48 .
miguel@hegel:~/dbs/4.1/var$

#include <my_global.h>
#include <m_string.h>
#include "mysql.h"

#define DB_HOST			"192.168.0.118"
#define DB_USER			"miguel"
#define DB_PASSW		"miguel"
#define DB_NAME			"test"
#define DB_PORT			0
#define DB_UNIX_SOCKET	"/tmp/mysql.sock"

void main( void )
{
  MYSQL mysql;
  int x;
	char my_sub[255], my_query[255];
 
  mysql_init(&mysql);
  
   
  if (!mysql_real_connect(&mysql,DB_HOST,DB_USER,DB_PASSW,DB_NAME,
						   DB_PORT,DB_UNIX_SOCKET,0))
  {
    printf("Error: %s\n",mysql_error(&mysql));
    return;
  }
  else
	printf("Connected to the server: %s\n",mysql_get_server_info(&mysql));

  printf("Creating Database\n");
  
	for (x = 1; x <= 100000; x++)
	{
	  int10_to_str((int) x,strmov(my_sub,
          "Create Database db"), 10);

	  strxnmov(my_query,sizeof(my_query)-1,my_sub,NullS);
	 
   	  if (mysql_query( &mysql, my_query) )
		{
          printf("Error (query): %s\n", mysql_error( &mysql ));
          mysql_close( &mysql );
          return;
		}
	}
  
  mysql_close(&mysql);
}