Jump to content

Recommended Posts

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.

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
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 🙂

Annotation 2020-07-30 155748.png

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites
Posted (edited)
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 by Mohsin Amer
Input direct link to DB by mistake

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


  • Similar Content

    • By dewabizmedia
      Hi,
      How add sub menu affiliate whmcs?
      https://prnt.sc/tqwhut
      Thanks
    • By HarryAdney
      Hi guys,
      Quick question, when testing out an affiliate link the page redirects straight to my home page, i.e. clicking on https://domain.com/*?aff=1 redirects straight to https://domain.com. Is this correct?
      Cheers,
       
      Martin
    • By Syed Ans Shah
      hi,
      i want to know if there is any process or any way to create a Promotion and assign it to specific Affiliate
      i have generated a promo promocode=TestPromo
      and i have an affiliate as ?aff=10
      Now i want to assign all the user/clients who uses that promo to be assigned to the Affiliate and gets the specified commision.
      help required immediately please.
      thanks
    • By Starflix
      Hello, guys,
      I am trying to find a solution which helps me to setup the refferal program in the way that for example for a first period refferal gets x% commisions and for the following y%? Are you aware of how i could make this happen? Maybe there is some kind of WHMCS extension?
      Thank you a lot.
    • By ModulesGarden
      Are you looking for an Affiliate Program with clear-cut conditions and reasonable commission rates?
      You will fall in love then with our newly started promotion directed at all active Affiliates and those, who have every aspiration of yet becoming our partners!

      How would you like the idea of receiving a double commission for each and every sale made throughout the entire April and May?
      If you are an ambitious, resourceful person who can easily muster 10 and more orders with no sweat whatsoever, consider yourself a perfect candidate for our Partnership Program!
       
      Get familiar with the specifics of our kickass promotion!
  • Recently Browsing   0 members

    No registered users viewing this page.

×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use & Guidelines and understand your posts will initially be pre-moderated