Databases

Right-click a database in the sidebar for its properties, to change its character set, or to rename it.

Database properties

Database Properties shows the database's name, default character set and collation. Each value has a Copy button, and Copy all puts all three on the clipboard at once:

Name: shop
Character set: utf8mb4
Collation: utf8mb4_unicode_ci

What the two values mean depends on the engine:

Engine	Character set	Collation
MySQL / MariaDB	Default character set of the database	Default collation
PostgreSQL	Encoding (e.g. UTF8)	datcollate, the locale strings sort by
SQL Server	Code page of the collation (e.g. CP1250, UTF-8 (CP65001))	Database collation
SQLite	PRAGMA encoding	BINARY

Changing the character set and collation

On MySQL and MariaDB the properties window has Change character set…. Pick the character set — the collation switches to that set's default, and you can choose another one.

With Also apply to all tables and fields in the database ticked (the default), the change does not stop at the database default. The window counts the tables and text fields and shows how many tables actually need converting; tables already on the chosen collation are skipped.

Apply asks for confirmation, then:

1. changes the database default (ALTER DATABASE … CHARACTER SET … COLLATE …), so tables created from now on get the new set;
2. converts the tables one at a time with ALTER TABLE … CONVERT TO CHARACTER SET …, which changes the table default and every CHAR, VARCHAR, TEXT, ENUM and SET column.

A progress bar shows which table is being converted. Stop ends the run after the current table. A table that fails does not stop the others; every error is listed at the end with the table's name.

Foreign keys on text columns do not get in the way: each conversion runs with foreign key checks off, so a child table can change before its parent. The keys stay in place and are enforced as before.

Each table is rebuilt

CONVERT TO rewrites the table and locks it for the duration, which takes a while on a large table. Going to a multi-byte set such as utf8mb4 can also turn a TEXT column into MEDIUMTEXT to keep its capacity. Take a backup first.

Views have no columns of their own and are not converted. Stored procedures and functions keep the collation they were created with; recreate them if they must use the new one.

PostgreSQL, SQL Server and SQLite show the values read-only: none of them can change this for an existing database safely.

Renaming a database

Rename Database… is on the database's right-click menu for MySQL, MariaDB, PostgreSQL and SQL Server. A SQLite database is a file — rename it by editing the connection.

PostgreSQL and SQL Server

Both rename with one statement (ALTER DATABASE … RENAME TO and ALTER DATABASE … MODIFY NAME). Tables, other objects and permissions stay where they are.

The server needs the database to itself for that. The window shows how many other sessions are using it, and Disconnect other sessions throws them out first — their open transactions are rolled back. NabuSQL closes its own connections to the database automatically.

On SQL Server the physical files (.mdf, .ldf) keep their names.

MySQL and MariaDB

MySQL and MariaDB have no statement for this, so NabuSQL rebuilds the database under the new name. The window explains the steps, lists what the database contains, and the Rename button stays disabled until you tick I understand the risk and have a backup of the database.

What happens:

1. Every definition that cannot be moved — triggers, views, procedures, functions, events, grants — is read first and saved to nabusql_rename_<database>_<time>.sql in your Downloads folder. If any of them cannot be read, nothing is changed.
2. The new database is created with the same character set and collation.
3. Triggers are dropped (MySQL will not move a table that has one), then all tables move in a single RENAME TABLE. That is fast — only metadata changes, no rows are copied.
4. Procedures and functions, views, triggers and events are recreated in the new database. References written as `old`.table are pointed at the new name. Triggers keep their firing order and the sql_mode they were created with.
5. User privileges on the database, its tables and routines are copied to the new name.
6. Only if every step succeeded are the old privileges revoked and the old, now empty, database dropped.

If step 3 fails, it is rolled back: the triggers are recreated and the new database removed. If something fails after the tables have moved, the old database is kept, holding whatever could not be recreated, and the result lists every step with its status.

Not atomic, and nothing outside this database changes

Views, routines and triggers in other databases, and applications connecting to the old name, keep using the old name. No one else should be working with the database while it is renamed.

When a definer account does not exist or you lack the privilege to use it, the object is created with you as its definer, and the result says so.

What NabuSQL updates for you

After any rename, NabuSQL points its own references at the new name: saved queries, open tabs, Task Scheduler jobs, the connection's default database and the saved-queries folder on disk.