Skip to main content

Database Configuration

Configure database settings for SigmaOrders.

Database Types​

SigmaOrders supports two database types:

  • SQLite (default) - File-based, no setup required
  • MySQL - External database, better for large servers

SQLite Configuration​

SQLite is the default and requires no additional configuration.

database:
  type: SQLITE

SQLite Details​

  • File Location: plugins/SigmaOrders/database.db
  • Setup: Automatic, no configuration needed
  • Performance: Good for servers with < 200 players
  • Limitations: Single-server only, file-based

SQLite Advantages​

  • Zero configuration
  • No external dependencies
  • Fast for small-medium servers
  • Easy backups (just copy the file)

SQLite Disadvantages​

  • Slower with many concurrent operations
  • File-based (can't share across servers)
  • Limited scalability

MySQL Configuration​

For larger servers or multi-server setups, use MySQL.

database:
  type: MYSQL
  mysql:
    host: localhost
    port: 3306
    database: sigma_orders
    username: root
    password: your_password
    pool-size: 10

MySQL Settings​

SettingTypeDefaultDescription
hostStringlocalhostDatabase server hostname
portInteger3306Database server port
databaseStringsigma_ordersDatabase name
usernameStringrootDatabase username
passwordStringpasswordDatabase password
pool-sizeInteger10Connection pool size

MySQL Setup Steps​

  1. Create Database

    CREATE DATABASE sigma_orders CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;

  2. Create User (optional, recommended)

    CREATE USER 'sigmaorders'@'localhost' IDENTIFIED BY 'secure_password';
    GRANT ALL PRIVILEGES ON sigma_orders.* TO 'sigmaorders'@'localhost';
    FLUSH PRIVILEGES;

  3. Configure Plugin

    database:
      type: MYSQL
      mysql:
        host: localhost
        port: 3306
        database: sigma_orders
        username: sigmaorders
        password: secure_password
        pool-size: 10

  4. Restart Server

    • Plugin creates tables automatically
    • Check console for connection success

MySQL Advantages​

  • Better performance for large servers
  • Can share database across multiple servers
  • Better concurrent operation handling
  • Professional database management tools

MySQL Disadvantages​

  • Requires external database server
  • More complex setup
  • Additional resource usage

Connection Pooling​

MySQL uses connection pooling for performance:

  • pool-size: Number of concurrent connections (default: 10)
  • Adjustment: Increase for high-traffic servers (max: 50 recommended)
  • Monitoring: Check database connection count if issues occur

Database Migration​

SQLite to MySQL​

  1. Backup SQLite Database

    cp plugins/SigmaOrders/database.db database.db.backup

  2. Export Data (if needed)

    • Use SQLite tools to export data
    • Import to MySQL (manual process)

  3. Change Configuration

    database:
      type: MYSQL
      # ... MySQL settings

  4. Restart Server

    • Plugin creates MySQL tables
    • Old SQLite file can be kept as backup
warning

Data migration from SQLite to MySQL is not automatic. You may need to manually export/import data or start fresh.

Database Maintenance​

SQLite Maintenance​

  • Backup: Copy database.db file
  • Vacuum: SQLite auto-vacuums, but can be done manually if needed
  • Size: Monitor file size (grows with orders)

MySQL Maintenance​

  • Backups: Use standard MySQL backup tools
  • Optimization: Run OPTIMIZE TABLE periodically
  • Monitoring: Monitor connection pool usage

Troubleshooting​

Connection Errors​

Symptoms: "Failed to connect to database"

Solutions:

  1. Verify database server is running
  2. Check host/port settings
  3. Verify username/password
  4. Check firewall rules
  5. Test connection with MySQL client

Performance Issues​

Symptoms: Slow order operations, timeouts

Solutions:

  1. Increase pool-size for MySQL
  2. Optimize database (indexes are auto-created)
  3. Consider MySQL for large servers
  4. Check database server resources

Permission Errors​

Symptoms: "Access denied" errors

Solutions:

  1. Verify database user has proper permissions
  2. Check GRANT statements
  3. Verify database name is correct

Best Practices​

  1. Use MySQL for 200+ players: Better performance
  2. Regular Backups: Backup database regularly
  3. Secure Passwords: Use strong passwords for MySQL
  4. Monitor Pool Size: Adjust based on server load
  5. Test Changes: Test database changes on dev server first

Next Steps​