# 🔧 Collation Fix for Live Server

## Problem
Getting error: `Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation '='`

## Root Cause
This error occurs in the Special Access Links feature when the query tries to JOIN the `special_access_links` table with the `registrations` table:

```sql
LEFT JOIN registrations r ON r.referral_code = sal.link_code
```

If these tables have different collations, MySQL cannot perform the string comparison, causing the error.

## Affected Tables
- `special_access_links` (created by migration)
- `registrations` (existing table)
- `migrations` (created by migration system)

## Solutions (Choose One)

### Option 1: PHP Script (Recommended)
Run this on your live server via SSH/command line:

```bash
php fix_sal_collation.php
```

This script will:
- Detect your database's collation settings
- Fix Special Access Links tables (special_access_links, migrations, registrations)
- Test that JOIN queries work (the main source of collation errors)
- Adapt to your server's specific collation

### Option 2: Full Database Fix
If multiple tables have collation issues:

```bash
php fix_live_collation.php
```

This fixes ALL tables in your database to use consistent collation.

### Option 3: Manual SQL Commands
If you can't run PHP scripts, execute these SQL commands in phpMyAdmin or MySQL console:

```sql
-- First check your database collation:
SELECT @@collation_database;

-- Then run appropriate ALTER commands based on the result:
ALTER TABLE special_access_links CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE migrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
ALTER TABLE registrations CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
```

## After Running Fix

1. **Clear any cached queries** in your application
2. **Restart your web server** if possible
3. **Test the Special Access Links page**
4. **Verify the feature works without errors**

## Still Having Issues?

If you still get the error after running the fix, test the specific query:

```bash
php test_query.php
```

This will show you exactly which tables still have collation mismatches.

## Files Included

- `fix_sal_collation.php` - Fixes only Special Access Links tables
- `fix_live_collation.php` - Fixes all tables in database
- `sql/fix_collation.sql` - Manual SQL commands

## Prevention

Future deployments will automatically use the correct collation that matches your database settings, preventing this issue from recurring.
