Installing FaucetInABox on a shared hosting CPanel: SQLSTATE[42000] and SQLSTATE[42S21] errors

I'm trying to install FaucetInABoxon a shared hosting CPanel . I was previously able to do it without errors, but now I'm encountering two specific errors on my site (bnb.faucet.lol).

The first error is:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS first_used timestamp NOT NULL DEFAULT current_timestamp()' at line 1 in /home/faucetl8/public_html/bnb/script/install.php:222 Stack trace: #0 /home/faucetl8/public_html/bnb/script/install.php(222): PDO->exec('ALTER TABLE `Fa...') #1 /home/faucetl8/public_html/bnb/script/common.php(152): include('/home/faucetl8/...') #2 /home/faucetl8/public_html/bnb/index.php(34): require_once('/home/faucetl8/...') #3 {main} thrown in /home/faucetl8/public_html/bnb/script/install.php on line 222

And the second error is:

Fatal error: Uncaught PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'users_returned' in /home/faucetl8/public_html/bnb/script/install.php:222 Stack trace: #0 /home/faucetl8/public_html/bnb/script/install.php(222): PDO->exec('ALTER TABLE `Fa...') #1 /home/faucetl8/public_html/bnb/script/common.php(152): include('/home/faucetl8/...') #2 /home/faucetl8/public_html/bnb/index.php(34): require_once('/home/faucetl8/...') #3 {main} thrown in /home/faucetl8/public_html/bnb/script/install.php on line 222

I tried changing the character set to utf-8 unicode, and I've also tried deleting and recreating the database, but these actions didn't help.

Does anyone have any suggestions on how to resolve these errors? I believe they are related to the SQL syntax and the existence of a column, but I'm not sure how to fix them.

Thanks in advance for your help!

I was trying to install FaucetInABox on a shared hosting CPanel by. Previously, I was able to do it without any issues. However, this time, I encountered two errors on my site, bnb.faucet.lol.

Solution

Yes, you are understanding your problem accurately. The first error tells you you have a syntax error:

Fatal error: Uncaught PDOException: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF NOT EXISTS first_used timestamp NOT NULL DEFAULT current_timestamp()' at line 1 in /home/faucetl8/public_html/bnb/script/install.php:222 Stack trace: #0 /home/faucetl8/public_html/bnb/script/install.php(222): PDO->exec('ALTER TABLE `Fa...') #1 /home/faucetl8/public_html/bnb/script/common.php(152): include('/home/faucetl8/...') #2 /home/faucetl8/public_html/bnb/index.php(34): require_once('/home/faucetl8/...') #3 {main} thrown in /home/faucetl8/public_html/bnb/script/install.php on line 222

This complains that you have an error around

IF NOT EXISTS first_used timestamp NOT NULL DEFAULT current_timestamp()

It seems that you attempt to create a column if it does not exist. But this is not the right syntax to do it, see https://dba.stackexchange.com/questions/169458/mysql-how-to-create-column-if-not-exists

You would need to look into your MySQL logs and see what was actually tried to be executed. Find this error and see what the full command was. You can check whether a column exists like this:

SELECT
    CASE
        WHEN column_name is null then 0
        ELSE 1
    END INTO @yourvariable
FROM (select 1 AS foo) t
LEFT JOIN information_schema.COLUMNS 
ON 
    TABLE_SCHEMA = 'db_name' 
AND TABLE_NAME = 'table_name' 
AND COLUMN_NAME = 'column_name'

and then you can use @yourvariable to check whether the column exists and if not then you can create it.

The second error complains about the failure to create a column that already exists:

Fatal error: Uncaught PDOException: SQLSTATE[42S21]: Column already exists: 1060 Duplicate column name 'users_returned' in /home/faucetl8/public_html/bnb/script/install.php:222 Stack trace: #0 /home/faucetl8/public_html/bnb/script/install.php(222): PDO->exec('ALTER TABLE `Fa...') #1 /home/faucetl8/public_html/bnb/script/common.php(152): include('/home/faucetl8/...') #2 /home/faucetl8/public_html/bnb/index.php(34): require_once('/home/faucetl8/...') #3 {main} thrown in /home/faucetl8/public_html/bnb/script/install.php on line 222

namely the users_returned column. If you need further information about the problem, then you will need to provide the script that is being executed along with pointers of exactly which commands fail and/or information about your script that is generating and running the script.