# Fixing MySQL "Too Many Keys" Error

## Problem
MySQL has a limit of 64 indexes per table. When using `sync({ alter: true })`, Sequelize tries to modify existing tables which can hit this limit.

## Solution

### Option 1: Recreate Database (Recommended for Development)

1. **Set force sync flag in .env:**
   ```bash
   # Add this line to server/.env
   DB_FORCE_SYNC=true
   ```

2. **Restart the server:**
   ```bash
   npm run dev
   ```

3. **⚠️ WARNING:** This will **DELETE ALL DATA** in your database and recreate all tables.

4. **After first sync, remove the flag:**
   ```bash
   # Remove or set to false
   DB_FORCE_SYNC=false
   ```

### Option 2: Manually Drop and Recreate Database

```bash
# Connect to MySQL
mysql -u root -p

# Drop and recreate database
DROP DATABASE xyz_pos_db;
CREATE DATABASE xyz_pos_db;

# Exit MySQL
EXIT;

# Restart server (will create tables automatically)
npm run dev
```

### Option 3: Use Migrations (Production Approach)

For production, use Sequelize migrations instead of `sync()`:

1. Install Sequelize CLI:
   ```bash
   npm install --save-dev sequelize-cli
   ```

2. Initialize migrations:
   ```bash
   npx sequelize-cli init
   ```

3. Create migrations for each model change instead of using sync.

---

## Why This Happens

- MySQL limits each table to 64 indexes
- `sync({ alter: true })` tries to add/modify indexes on existing tables
- If the table already has many indexes, adding more hits the limit
- `sync({ force: true })` drops and recreates tables, avoiding this issue

## Prevention

- Use migrations in production
- Limit indexes to only what's necessary
- Combine related indexes where possible
- Consider composite indexes instead of multiple single-column indexes

