# The database server

> Source: https://www.allsweb.com/sixpanel/docs/database-server
> Markdown for agents: https://www.allsweb.com/sixpanel/docs/database-server.md
> Publisher: AllsWeb (www.allsweb.com)

Part of: SixPanel documentation

**What this page is for:** look after the one database server (MariaDB) every project
on this server shares — see whether it is healthy, restart it, choose the **time zone**
it runs on, see every database in it and whose each one is, open any of them in
**phpMyAdmin** signed in as its own user, and add MariaDB settings of your own.

Open **Server** and the **Database** tab.

**You need**

- The owner's login to change anything here. A temporary login can read the page; the
  read-only demo shows it but changes nothing.
- For a time-zone change: a quiet moment. Writes to every database pause while it runs
  (sites stay readable).

**The rule that does not move:** one MariaDB serves every project and website on this
server. A restart, a time-zone change or a setting here is felt by all of them — each
confirm on this page says so before you press it.

---

## At a glance

The banner at the top says whether the database server is running, with its version,
how long it has been up, how many connections are in use and how much data it holds.
**Live logs** follows MariaDB's own log; **Restart** restarts it — every project loses
its database for a few seconds, and the background workers (queue, scheduler) are
paused first and started again afterwards, so no job fails.

Below it: the version (from your distribution's own packages, kept up to date by its
security updates), uptime, connections now (hover for the peak), the number of
databases, data on disk, the **memory for data** (the InnoDB buffer pool SixPanel's
auto-tune sized for this server), how much of what is read comes straight from that
memory, free disk, and the server's character set.

If the server is **not answering**, the banner says so with systemd's own word for its
state. Press **Restart**, and open **Live logs** to see why it stopped.

## Time zone

**What it is:** the zone MariaDB shows dates in and stamps them with — `NOW()`,
`CURRENT_TIMESTAMP`, and every `TIMESTAMP` column as an app reads it. It is the
**database's**, not the panel's: the timezone you picked for the panel (first-run setup,
**Settings**) only changes what the panel shows, and never moves the database.

**The default is UTC**, and it is the safe one: the same data reads the same on any
server, and a change of the server clock re-labels nothing. Run the database on another
zone only if your apps read dates the database stamps itself and expect your local time.

The card shows the zone the database runs on now, its clock, your panel's timezone, and
the last time the zone was changed.

### Change it

Choose a zone under **Run the database on** — **UTC (recommended)** first, then your
panel's own zone, then every offset from UTC−12:00 to UTC+13:00 — and press **Change
time zone**. The panel first reads what the change involves and shows it: how many
tables hold dates, in which databases, about how many rows, and how long writes will
pause. Confirm, and it:

1. pauses writes to every database — sites stay readable, but anything that saves (an
   order, a sign-in, a form) waits until it finishes;
2. rewrites every stored `TIMESTAMP` so it reads **exactly** what it reads now, a chunk
   at a time, and checks each chunk before keeping it — `DATETIME` columns read the
   same under any zone and are not touched;
3. switches the database to the new zone and pins it there, so a restart cannot move it;
4. reconnects every app and turns writes back on.

If anything does not check out, every chunk is put back and the database stays on its
old zone, exactly as before. Afterwards only the dates the database stamps **from then
on** follow the new zone; nothing already stored reads differently.

That holds for an app that reads its dates through the database's zone, as most do —
neither 6amMart codebase does otherwise. An app that sets a time zone **of its own** on
its database connection reads its `TIMESTAMP` columns through that one, whatever the
database runs on: for such an app a change of the database's zone shifts those times by
the difference. If you run one, leave the database on the zone it is on.

**Why an offset, not a city:** a database zone here is a fixed offset from UTC
(UTC+05:30, UTC+03:00, UTC−05:00 …). It does not follow summer time — a city's name
would need MariaDB's own zone tables, which Ubuntu leaves empty, and a missing one stops
MariaDB from starting at all. If your panel's zone changes its clocks in summer, the page
says so: there, UTC is the better choice.

**Moving projects to another server?** You do not have to match the two databases'
zones first. A move asks both servers how their database reads a stored time and, when
they differ, carries what every date **reads** here — an order of 21:28 reads 21:28
there, whatever zone that server's database runs on
([Move projects to another server](https://www.allsweb.com/sixpanel/docs/move-between-servers)). The move asks twice:
before anything is copied, and again at the moment the database is copied. A website
whose app sets a time zone of its own on its database connection is the exception —
[An app with a time zone of its own](https://www.allsweb.com/sixpanel/docs/move-between-servers#an-app-with-a-time-zone-of-its-own)
says what happens to it and what to do. One case is refused
before anything is copied: a receiving server whose database still follows a zone with
summer time. Change that database to UTC here, on that server, first.

**Not during a move.** A change of zone rewrites every stored date on the server, so it
is refused while a project or a website is being moved to another server, while one is
still arriving here, during a migration of the whole server and in the maintenance
window — the message says which. And a move is refused while a change of zone is under
way or was stopped part way: finish it, or put it back, first.

### A backup from before a change of time zone

A change of zone rewrites what is **in** the database at that moment. A backup taken
before it was not there to be rewritten: it still holds every `TIMESTAMP` the way the old
zone stored it. Put such a backup back after the change and it is whole — every row,
every table — but its `TIMESTAMP` values are read through the new zone, so each reads off
by the difference between the two zones (an order of 21:28 taken on UTC+05:30 reads 15:58
on UTC). `DATETIME` columns and everything else read as they did. The restore says so in
its log, in a line that begins *note: this backup is older than the last change…* — for a
project and for a website.

So, after a change of zone, **run a backup** (**Backups** → **Run backup now**): from
that one on, every backup matches the zone the database runs on.

**The same is true on another server.** A backup restored on a server whose database
runs on a different zone than the one it was taken on reads its times through that
server's zone. A server newly set up with SixPanel runs its database on UTC; one set up
before 1.5.2 ran it on the server's clock — the timezone chosen in its first-run setup —
unless that was changed (its **Health → Database time zone** names it). The restore
cannot tell there, and says nothing.

To put such a backup back with its times unchanged — an older backup on this server, or
any backup on a server whose database runs on another zone:

1. put the database on the zone that backup was taken on (**Change it**, above; on a
   server that holds no data yet this takes a moment);
2. restore the backup;
3. change the zone to the one you want — UTC, say. That rewrites the restored times
   together with all the others, so they keep reading the same.

This works when the zone the backup was taken on is UTC or a fixed offset — a zone that
never changes its clocks is its offset (India is UTC+05:30). A backup taken on a zone
that follows summer time cannot be put back exactly this way: the times of one half of
its year stay an hour off. [Moving a project](https://www.allsweb.com/sixpanel/docs/move-between-servers) from a server
that still runs has no such limit: a move carries what every time reads.

### If a change stopped part way

The card turns red and writes stay paused, so no date is stored two different ways.
**Try the move again** finishes it; **Put it back** undoes every chunk already rewritten
and turns writes back on, on the old zone. If the panel itself restarts during a change,
it finishes the change when it comes back. **Server → Health** shows the state too.

## Databases

Every database in this MariaDB, largest first, with its size, number of tables and
character set — and the **project or website it belongs to**, linked to that project's
own **Database** page (backups, slow queries). A database the panel did not make says
so; a deleted project's database is kept for a few days so it can be restored, and is
marked that way.

Every database the panel made has **Open in phpMyAdmin** beside it — see
[phpMyAdmin](#phpmyadmin) below.

### Remove a database

A database that **no project and no website on this server uses** has **Remove**
beside it. That is one the panel did not make — left by an earlier install, or made
by hand — and the two the panel made and no longer needs: what a deleted website left
behind when its database could not be removed at the time, and the scratch copy of a
backup check that was cut short.

- **A project's or a website's own database has no Remove here**, and neither has a
  deleted project's kept one. Delete the project or the website (or let the deleted
  project be purged) and its database goes with it — together with its account, its
  files and its record, which this button would leave behind.
- **The panel cannot know whether something outside it still uses the database.** Look
  at its size and its tables first; that judgement is yours.

Press **Remove**, type the database's name, and choose whether to **keep a copy on
this server first** (ticked unless you untick it). It runs as a job you can watch:

1. it checks again that nothing owns the database;
2. it writes the copy — a `.sql.gz` in `/opt/sixpanel/data/removed-databases/` that only
   root can read — and reads it back to make sure it is whole. If the copy cannot be
   made (a full disk, for one), the job stops there and **nothing is removed**;
3. it removes the database. No database account is touched — with one exception:
   a database a deleted website left behind goes together with the two accounts the
   panel had made for that website, as the website's delete meant.

The copy is kept for 7 days and then deleted by itself; the list shows each copy with
the date it goes. It holds the database's tables, views and triggers, and its stored
routines and events when it has any. (On a server where MariaDB's own record of
routines or events cannot be read, the copy is still made without them, and the job's
log says so in a line that begins *note:*.)

**To put a database back** from its copy, on the server over SSH — the job's log
prints this command with both names filled in:

```bash
sudo mariadb -e 'CREATE DATABASE `<name>`' && gunzip -c '/opt/sixpanel/data/removed-databases/<file>.sql.gz' | sudo mariadb --database='<name>'
```

The first half makes the database **new**, and refuses when a database of that name is
on this server; then nothing is written. That is deliberate: by the time you want a
copy back, the name may belong to something else — a project or a website made since,
or one a move brought here — and a copy read into a database that is in use replaces
its tables. If the name is taken and you still want the old data, put it back under
another name (change both `<name>`s) and look at it there. Without a copy, a removed
database cannot be brought back.

If the command stops part way — the disk fills up, say — the database it made is still
there, with what had been read in so far, and running the command again is refused for
that reason. **Remove** that database on this page first (it belongs to no project and
no website, so the page offers it), then run the command again.

This is also the way out when a **move to this server is refused** with *"a database
called … already exists and no project owns it"* (or *"… and no website there owns
it"*): remove the database here, then start the move again
([Move between servers](https://www.allsweb.com/sixpanel/docs/move-between-servers)).

## phpMyAdmin

**What it is:** the familiar database browser, for looking inside a database by hand —
its tables and rows, a query, an export of one table. One phpMyAdmin serves the whole
server, and it runs only while it is **On**: its own PHP workers and a port on the
server itself, reached only through the panel while you are signed in. **Off**, it has
no workers and no port, and costs nothing.

The card shows whether it is on, its version and, while it is on, how much memory its
workers use. **Turn on** and **Turn off** switch it. **Open phpMyAdmin** shows
phpMyAdmin's own sign-in, for a database user and password you type yourself.

### Open a database, signed in as its own user

**Open in phpMyAdmin** beside a database — and on that project's or website's own
**Database** page — opens phpMyAdmin in a new tab, already signed in as **that
database's own user**. It sees that database and nothing else, and there is no password
to look up or copy. If phpMyAdmin is off, it is turned on first.

Why no password leaves the server: the panel checks that the user and password it holds
for the database still open it, puts them in a single-use sign-in that lasts one minute
and that only phpMyAdmin can read, and opens phpMyAdmin with nothing but the database's
name in the address. phpMyAdmin reads the sign-in and deletes it. It works once, for the
browser that asked for it: the same address opened again, or in another browser, signs
nobody in. **Log out** in phpMyAdmin ends the sign-in, and turning phpMyAdmin off ends
every sign-in not yet used.

A database the panel did not make, or a deleted project's, has no button: use **Open
phpMyAdmin** and sign in with its user.

**While a project or a website is being moved to another server**, from the moment the
move pauses it until the move ends, **Open in phpMyAdmin** is refused for its database:
the database has been copied by then, and what you changed would be on this server only.
A project or a website that is still *arriving* from another server is refused too. A copy a move left
parked here opens as usual. **Close a phpMyAdmin tab that is signed in to a database
before you move its project** — the panel cannot end a sign-in that already exists
([Move projects to another server](https://www.allsweb.com/sixpanel/docs/move-between-servers) says what a move does
about that).

**Who can use it:** the owner. A temporary login sees whether phpMyAdmin is on but
cannot turn it on or off or open it, and neither can the read-only demo. Tia, the AI
assistant, may turn it on or off — asking you first, like any other change, unless you
set her to Auto — and can never open it or sign in to a database with it.

## Settings

MariaDB settings of your own for the whole server, one `name = value` per line — for
example `max_connections = 300` or `long_query_time = 1`. They win over SixPanel's tuned
values (shown folded above the box, by file), and a SixPanel update never undoes them.

Some of these are values **Settings → Performance** sizes for you — the database memory
(`innodb_buffer_pool_size`), temporary tables, the join and sort buffers, the
transaction log and the table cache. When you set one here, the Performance card shows
your value as what is running, marks the row *set by you on Server → Database*, and
**Apply tuning** no longer offers to change it. Take the line out here to hand that
value back to auto-tune.

**One project cannot use every connection.** Beside `max_connections`, SixPanel sets
`max_user_connections` — the most connections a single database account may hold at
once — to `max_connections` less a reserve of 30. A project with a connection leak then
runs into its own limit (*User … already has more than 'max_user_connections' active
connections*) instead of stopping every other project from connecting. When you set
`max_connections` here the limit moves with it at once; to choose it yourself, add a
`max_user_connections` line and yours wins.

Every save is **checked before it is kept**:

- the name must be a setting this MariaDB has;
- MariaDB must accept the whole configuration with your lines in it;
- a value it can take while running is **in effect at once** — nothing restarts;
- a value that only changes when the database starts (for example
  `performance_schema`) restarts it: the page asks first and says which settings need
  it. If MariaDB does not start with the new values, your **previous** settings are put
  back and it starts on them.

A few settings are never taken here because the panel depends on them — the data
directory, the socket, the port, the network address, the time zone (use the card
above), `read_only`, the general log, and a handful of others. The page shows the reason
when you try one. Two values are refused because they would stop MariaDB on this server:
a buffer pool larger than three quarters of its memory, and more than 10,000
connections.

Your lines are kept in `/etc/mysql/mariadb.conf.d/97-sixpanel-owner.cnf`. Change them
here, not by hand: a hand edit is not checked, and **Health** reports it.

## How to check it worked

- The banner says *The database server is running* and the time-zone card names the
  zone you chose, *kept there across restarts*.
- **Server → Health → Database time zone** is green and names the zone.
- After a settings save, the toast says *in effect now*, or the restart job finishes with
  *the database is running with the new settings*.
- **Open in phpMyAdmin** opens a tab on that database's tables, with its own user named at
  the top of phpMyAdmin — and no sign-in form on the way.
- After **Remove**, the job ends with *… is removed*, the database is gone from the list,
  and — when a copy was kept — *Copies kept of removed databases* under the list shows it.

## If it went wrong

- **"… belongs to the project …"**, **"… belongs to the website …"** or **"… is kept for
  the deleted project …"** when removing a database — it has an owner, so it is removed
  by deleting (or purging) that owner, not here.
- **"mariadb-dump failed"** or **"gzip failed … is the disk full?"** while removing a
  database — the copy could not be written, and **nothing was removed**. Free some disk,
  or untick *Keep a copy* if you are sure you do not need one, and press Remove again.
- **"MariaDB refused these settings, so nothing was changed"** — the message names the
  line. Correct or remove it and save again.
- **"MariaDB would not start with …, so the previous settings were put back"** — the
  database is running again on what you had before. The value was too large for this
  server, or not one MariaDB accepts at start.
- **"the database's time zone cannot be changed right now: …"** — a move to another
  server is running, something is still arriving on this one, or the maintenance window
  is open; the message names it. Try again when that has finished.
- **"The database cannot be moved yet"** — a table blocks the time-zone change (an
  `UPDATE` trigger, system versioning, a foreign key on a date column, a non-InnoDB table
  holding dates). Nothing was changed; the message names the table and what to do.
- **"the user SixPanel holds for … no longer opens it"** — the database's password was
  changed outside SixPanel. Give the database a new password from its project's or
  website's **Database** page, or use **Open phpMyAdmin** and sign in by hand.
- **"… is being moved to … right now and is paused for it: its database has been
  copied, and a change made in phpMyAdmin now would be on this server only"** (or
  **"… is still arriving from …"**) — a move has this project or website right now.
  Open it when the move has finished; **Migration** shows where it is.
- **"This sign-in has already been used"** or **"has expired"** — a sign-in works once,
  within a minute. Press **Open in phpMyAdmin** again.
- **"The database did not accept this sign-in"** — phpMyAdmin's own reason follows it.
  Usually the database's password was changed a moment ago; open it again.
- **The database server is not answering** — see
  [Troubleshooting](https://www.allsweb.com/sixpanel/docs/troubleshooting).