Lists and Clicks not showing in admin, link_cpt_id field to small

  • Resolved bowenac

    (@bowenac)


    1 year, 1 month ago

    Have a client who uses this plugin and said they’re not seeing links or clicks… I started debugging and noticed the queries to show links look for records in WP_PREFIX… prli_links where wp_posts id matches link_cpt_id.

    The clients website has a lot of records and very large IDs e.g. 9004111222131878 but the link_cpt_id maxes out at 2147483647 since it’s set as an int(11).

    As a test I created a link which still works and I can see all the existing links in the database. I then grabbed the ID that was created for that link in wp_posts and tried to update the record for it in wp_prli_links however the ID was to large and it would not let me update it via phpmyadmin. I then altered the wp_prli_links table and set the link_cpt_id as a bigint(20) to match that of the ID in wp_posts table, I was then able to paste the ID and then I see it in the links table in the plugin.

    I feel like the wp_prli_links id, and link_cpt_id really should be created as bigint(20) to match that of the wp_posts table.

    I noticed a lot of people have this same issue with links not showing up and I have a feeling they’re having the same issue with mismatched IDs due to the size you’re setting that field to when creating the database table. You even mention something about how it came behave weird on large databases. Should be an easy fix.

Viewing 2 replies - 1 through 2 (of 2 total)
  • Thread Starter bowenac

    (@bowenac)

    For anyone else that ever runs into this problem, here is how I fixed it for our scenario. As always make sure you have a backup of your database before doing this. This assumes your wpdb prefix is wp_ otherwise replace wp_ with whatever your db prefix is.

    Set wp_prli_links link_cpt_id to bigint(20) to match wp_posts ID field type
    Set wp_prli_clicks link_id to bigint(20) to match wp_prli_links id field type

    Then:

    Fix for Links Table

    UPDATE wp_prli_links
    JOIN wp_posts ON wp_prli_links.name = wp_posts.post_title
    SET wp_prli_links.link_cpt_id = wp_posts.ID

    Fix for Clicks Table

    SET link_id = (
    SELECT ID
    FROM wp_prli_links
    WHERE wp_prli_links.slug = SUBSTRING_INDEX(SUBSTRING(wp_prli_clicks.uri, 2), '?', 1)
    )
    WHERE EXISTS (
    SELECT 1
    FROM wp_prli_links
    WHERE wp_prli_links.slug = SUBSTRING_INDEX(SUBSTRING(wp_prli_clicks.uri, 2), '?', 1)
    );
    Tyler

    (@tylerthedude)

    Hi @bowenac,

    Thank you for reaching out and sharing your findings with us. I’ll get in touch with our development team and see if we may be able to change the data type on our ID columns, but unfortunately, the changes you made will revert back to the original int(11) data type the next time the plugin updates when our migration scripts run.

    The ID you’ve shared for the post is extremely large though, 9004111222131878 is in the quadrillions but this should almost never be reached. I’m wondering if the AUTO_INCREMENT value on the wp_posts table was changed at one point which is why that number is so high for newly created posts.

    You might try seeing if there’s a way to fix those post IDs so they map up to the number of posts you have published on your site. Once the IDs are back in order, you shouldn’t hit that limit when a new pretty link is being created.

    I hope this helps!

    Kind regards,

Viewing 2 replies - 1 through 2 (of 2 total)
  • The topic ‘Lists and Clicks not showing in admin, link_cpt_id field to small’ is closed to new replies.