Affiliates program not working

[Mohsin Amer]

Hello,

We are facing a problem which is we do not have affiliate program enabled, previously we did and it worked perfectly fine. Here is a list of my versions:

• PHP: 7.0
• WHMCS: 7.4.2
• All required extensions for making WHMCS work are installed.
• CloudFlare CDN
• MySql

Now we want to start our affiliate program once again, we went to General Settings -> Affiliates and enabled it there, no issues it said successful. When we tried to signup a test client having ID 1,  it said account successfully created, but in the client profile it still said Activate as affiliate. Even after multiple attempts it did not work, but in client dashboard it did show the bonus balance. Tried enabling from Admin/Client Area but no luck. We tried to check our logs and the Database and found out that the table tblaffiliates is having multiple entries of Client ID 1, which was obvious because we tried it like 2-3 times to enable and it created multiple entries in tblaffiliates, which we know should not happen. Then we checked the other tables related to affiliates and they all were empty.

This is what we gathered from our Activity Log

Quote

Activated Affiliate Account - Affiliate ID: 0 - User ID: 1

So basically WHMCS is trying to to create an affiliate account but is assigning the ID 0 to it which makes sure there is some error in the system.

Fixes we tried:

• Tried switching PHP versions.
• Tried enabling/disabling CloudFlare CDN.
• Tried removing duplicate values from tblaffiliates. (Restored them later)  
• Tried enabling/disabling Affiliates module multiple times.

Need help.

[WHMCS Alex]

Hello @Mohsin Amer,

The issue you detail indicates to me that the tblaffiliates.id column is not set to auto increment (which is it when WHMCS creates it).

You can resolve that by executing this SQL query on your WHMCS database:

ALTER TABLE `tblaffiliates` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT; 

Once executed, you need to ensure the AUTO_INCREMENT value is set correctly, for example, if you have three affiliates within that table, with the tblaffiliates.id values of 1,2 and 3 respectively, you will want to execute this SQL query on your WHMCS database to ensure the next tblaffiliates.id value to be set/used is "4":

ALTER TABLE `tblaffiliates` AUTO_INCREMENT=4; 

As always, backup the database before taking any action.

I hope this helps.

[Mohsin Amer]

57 minutes ago, WHMCS Alex said:

Hello @Mohsin Amer,

The issue you detail indicates to me that the tblaffiliates.id column is not set to auto increment (which is it when WHMCS creates it).

You can resolve that by executing this SQL query on your WHMCS database:

ALTER TABLE `tblaffiliates` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT; 

Once executed, you need to ensure the AUTO_INCREMENT value is set correctly, for example, if you have three affiliates within that table, with the tblaffiliates.id values of 1,2 and 3 respectively, you will want to execute this SQL query on your WHMCS database to ensure the next tblaffiliates.id value to be set/used is "4":

ALTER TABLE `tblaffiliates` AUTO_INCREMENT=4; 

As always, backup the database before taking any action.

I hope this helps.

Hello there Alex,

 

Thanks for your reply, I have tried your solution but unfortunately it is not working. Here is what I get when I run the first query:

Quote

MySQL said: Documentation #1075 - Incorrect table definition; there can be only one auto column and it must be defined as a key

I have attached a screenshot of my 'tblaffiliates' structure for your knowledge. Waiting for help 🙂

[WHMCS Alex]

Hello @Mohsin Amer,

Your tblaffiliates table is missing the primary key, which is why the auto_increment attribute cannot be set.

I have amended the SQL query I previously gave to include setting the primary key:

ALTER TABLE `tblaffiliates` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`); 

That should resolve the matter for you.

I would also advise you open a ticket with us as seeing this is indicative of other database tables being affected, so may cause further issues with the operation of WHMCS in the future. Within a ticket, we can review the schema of your entire database and advise on any structural issues/irregularities.

I hope this helps.

[Mohsin Amer]

19 minutes ago, WHMCS Alex said:

Hello @Mohsin Amer,

Your tblaffiliates table is missing the primary key, which is why the auto_increment attribute cannot be set.

I have amended the SQL query I previously gave to include setting the primary key:

ALTER TABLE `tblaffiliates` CHANGE `id` `id` INT(10) NOT NULL AUTO_INCREMENT, ADD PRIMARY KEY (`id`); 

That should resolve the matter for you.

I would also advise you open a ticket with us as seeing this is indicative of other database tables being affected, so may cause further issues with the operation of WHMCS in the future. Within a ticket, we can review the schema of your entire database and advise on any structural issues/irregularities.

I hope this helps.

Thankyou Alex,

This post resolved my issue and Affiliates system is now working, I created a test affiliates account and saw the AUTO_INCREMENT feature is working fine auto assigning the ID for each affiliate. can you tell me are these tables meant to be blank even after creating an affiliate? Because I'm not having any issue with affiliates system from the admin panel but in DB I see these tables are empty.

tblaffiliatesaccounts

tblaffiliateshistory

tblaffiliatespending

tblaffiliateswithdrawals

Edited July 30, 2020 by Mohsin Amer
Input direct link to DB by mistake