1. Kayako Download customers: we will continue to develop and support Kayako Download beyond July 2017, alongside the new Kayako for existing customers.

    Find out more.

  2. The forum you are viewing relates to Kayako Classic. If you signed up or upgraded to the new Kayako (after the 4th July 2016), the information in this thread may not apply to you. You can visit the forums for the new Kayako here.

Over 21000 records in table "swnewssubscribers" (cleaning non-isvalidated)

Discussion in 'Using Kayako Classic' started by MvdL79, Feb 12, 2019.

  1. MvdL79

    MvdL79 Member

    Hi all,

    Decided to investigate why our MySQL database is so big and trying to clean it up. Now I noticed one table which has a ton of email addresses: swnewssubscribers.

    This table has over 21000 lines/records! If I investigate this, I noticed most of them 80-90% are spam email addresses, for example: jtaylorbarnett@gmail.com, chismiiim@yahoo.com, soraya.taleb01@gmail.com, talk2blee711@gmail.com, etc.

    Obviously I want to clean this up, to decrease the database site quite a bit.

    I also noticed that there is a row called and I quote: "isvalidated". Apparently this is for real users (who confirmed their email address. I think it's safe to state that we can delete all users (in table swnewssubscribers) who have isvalidated set on "0" (as in not validated).

    Doing this clean-up manually is quite some work. Is there a easy / safe command which makes this possible?
    Please advice. Thank you kindly!

    Regards
     
  2. MvdL79

    MvdL79 Member

    I also noticed that the table "swnewssubscriberhash" has a similar amount of records.
    Is it possible to clean this as well?

    Thanks in advance.
     
  3. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there!

    To clean up both these tables, you can run the following SQL commands:

    1. DELETE from swnewssubscriberhash where newssubscriberid in (select newssubscriberid from swnewssubscribers where isvalidated=0);

    2. DELETE from swnewssubscribers where where isvalidated=0;

    NOTE: Do take your database back up before you execute these queries.
     
  4. MvdL79

    MvdL79 Member

    Hi Gurpreet!

    Thank you kindly for answering! Highly appreciated.
    Obviously I will take a backup.

    Removing those will increase MySQL performance I guess? At least it will be way smaller. Correct?
     
  5. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there!

    You are welcome.

    I can't be certain that it will improve the MySQL performance as I do not know the version of Kayako you are running as well as the version of MySQL and size of other DB tables. The performance depends upon other factors too.

    If you can create a support ticket with us, we can take a look into your existing set up and advise you accordingly.
     
  6. MvdL79

    MvdL79 Member

    Hi Gurpreet!

    Once again thank you for your answers. :)

    I have applied the the 2 SQL commands (though you made a small typo with 2x "where" in your 2nd SQL command).
    And it removed a ton of records. Now it has 2 and 302 rows left! So it really cleaned up. I couldn't find any issues after this.

    While cleaning I noticed another few tables which are really big, but as far as I can tell, we do not use this?
    In the Admin section, under GeoIP (where the below is related to) it says: "City (Location) database couldn't be found. Kayako is unable to locate a valid GeoIP database for City (Locations)."
    So I think we do not use this, can it safely be deleted or emptied? If so, how?

    Here is an overview:

    - swgeoipcities
    - swgeoipcityblocks1 to swgeoipcityblocks10
    - swgeoipisp1 to swgeoipisp10
    - swgeoipnetspeed1 to swgeoiporganization10

    I think this totals to 12 million records (if I guessed it right) and approx 1100 MiB size.

    Thank you once again! Really appreciate the help and advice here!

    Regards
     
  7. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there!

    Good catch on that typo; you have an eagle eye :)

    If you are not using the GeoIP service, you can truncate the data in those tables.

    You can use the commands:

    1. Truncate table swgeoipcities;
    2. Truncate table swgeoipcityblocks10;
    3. Truncate table swgeoipisp10;
    4. Truncate table swgeoipnetspeed1;
    5. Truncate table swgeoiporganization10;

    These table generally store the visitors geo locations. You can safely truncate them if required.

    NOTE: Back up is a must before every deletion :)
     
  8. MvdL79

    MvdL79 Member

    Hi Gurpreet!

    Haha. Nah, I don't have an eagle eye, but rather enough coffee. ;-)

    Anyways, thank you for those SQL commands! I applied them for alles tables from 1 till 10 (for example swgeoipcityblocks1 to swgeoipcityblocks10).
    And it really saved a ton of records and space in total!

    This is what was saved:
    - about 12.400.000 rows
    - almost 1000 MiB

    I think we never used GeoIP server ever to be honest (or maybe at the very beginning, who knows).
    So this saved us a lot of space. I think MySQL memory usage will be lower as well after this, now I am going to optimize all the tables.

    Thank you once again Gurpreet for your help! :)

    Regards
     
  9. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there!

    That is an awesome news! Glad to know that I was able to help.

    Have a fantastic weekend ahead :)
     
  10. MvdL79

    MvdL79 Member

    Thank you. Same to you Gurpreet!
     
  11. MvdL79

    MvdL79 Member

    Hi Gurpreet!

    Sorry for bothering you once again. I don't know what I did exactly, however the "News Items RSS"-feed which we use on all our sites, suddenly are not being displayed fully anymore (truncated).
    Now only a portion of the news item is being shown, while we want to show the complete / full news item on our RSS feeds.

    I am guessing it's a template issue, but rather have your input on this. Maybe I was to "active" with cleaning stuff.
    Or where can I change the limit of RSS-feeds messages size in general?

    We had this working before though, so I am guessing I did something wrong here... :-(

    Thanks in advance.

    Regards
     
  12. MvdL79

    MvdL79 Member

    Okay small update. I think the issue is related to something else.
    If I open our news page (on Kayako) I get the first part of the news (as passed trough onto our RSS feed).

    But after that I get this:

    Notice
    Undefined index: nwreadmore (./__swift/cache/feed0254c4541898d4bfba6bb88d6024.php:39)

    I checked the /cache/ folder ofcourse and the file is in there: feed0254c4541898d4bfba6bb88d6024.php
    And the file also has the correct permissions: 777

    Code:
    <?php
    ob_start(); /* template body */ ?><div class="boxcontainer">
           <div class="boxcontainerlabel"><?php if ((isset($this->scope["_settings"]["nw_enablerss"]) ? $this->scope["_settings"]["nw_enablerss"]:null) == '1') {
    ?><div style="float: right;"><a href="<?php echo $this->scope["_swiftPath"];?>rss/index.php?/News/Feed/Index/<?php echo $this->scope["_newsCategoryID"];?>" title="<?php echo $this->scope["_language"]["rssfeed"];?>" target="_blank"><img src="<?php echo $this->scope["_themePath"];?>images/icon_rss.png" align="absmiddle" alt="<?php echo $this->scope["_language"]["rssfeed"];?>" border="0" /></a></div><?php
    }
    echo $this->scope["_language"]["news"];?></div>
    
           <div class="boxcontainercontent">
             <table cellpadding="0" cellspacing="0" border="0" class = "containercontenttable">
             <?php
    $_fh0_data = (isset($this->scope["_newsContainer"]) ? $this->scope["_newsContainer"] : null);
    if ($this->isArray($_fh0_data) === true)
    {
       foreach ($_fh0_data as $this->scope['newsitemid']=>$this->scope['_newsItem'])
       {
    /* -- foreach start output */
    ?>
               <tr>
                 <td class="newstitlecontainer" width="60" align="left" valign="top">
                   <div class="datecontainerparent">
                   <div class="monthholder"><div class="monthsub"><?php echo $this->scope["_newsItem"]["parsedmonth"];?></div></div>
                   <div class="dateholder"><div class="datecontainer"><?php echo $this->scope["_newsItem"]["parseddate"];?></div></div>
                   </div>
                 </td>
    
                 <td width="100%" valign="top">
                   <div class="newsavatar"><img src="<?php echo $this->scope["_baseName"];
    echo $this->scope["_templateGroupPrefix"];?>/Base/StaffProfile/DisplayAvatar/<?php echo $this->scope["_newsItem"]["staffid"];?>/<?php echo $this->scope["_newsItem"]["emailhash"];?>/60" align="absmiddle" border="0" /></div>
                   <div class="newstitle"><a class="newstitlelink" href="<?php echo $this->scope["_baseName"];
    echo $this->scope["_templateGroupPrefix"];?>/News/NewsItem/View/<?php echo $this->scope["_newsItem"]["newsitemid"];?>/<?php echo $this->scope["_newsItem"]["urlextension"];?>" title="<?php echo $this->scope["_newsItem"]["subject"];?>"><?php echo $this->scope["_newsItem"]["subject"];?></a>
                   <div class="newsinfo"><?php echo $this->scope["_language"]["postedby"];?> <?php echo $this->scope["_newsItem"]["author"];?> <?php echo $this->scope["_language"]["on"];?> <?php echo $this->scope["_newsItem"]["date"];?></div></div>
                 </td>
               </tr>
               <tr><td colspan="2" class="newscontents">
                 <?php echo $this->scope["_newsItem"]["contents"];?>
    
                 <br />
                 <a class="newsreadmorelink" href="<?php echo $this->scope["_baseName"];
    echo $this->scope["_templateGroupPrefix"];?>/News/NewsItem/View/<?php echo $this->scope["_newsItem"]["newsitemid"];?>/<?php echo $this->scope["_newsItem"]["urlextension"];?>" title="<?php echo $this->scope["_newsItem"]["subject"];?>"><?php echo $this->scope["_language"]["nwreadmore"];?></a>
               </td></tr>
               <tr>
               <td colspan="2"><hr class="newshr" /><br /><br /></td>
               </tr>
             <?php
    /* -- foreach end output */
       }
    }?>
    
             </table>
             <?php if ((isset($this->scope["_newsCount"]) ? $this->scope["_newsCount"] : null) > 0) {
    ?>
             <br />
             <div class="newsfooter">
             <?php if ((isset($this->scope["_showOlderPosts"]) ? $this->scope["_showOlderPosts"] : null) == true) {
    ?><a class="newsreadmorelink" href="<?php echo $this->scope["_baseName"];
    echo $this->scope["_templateGroupPrefix"];?>/News/List/Index/<?php echo $this->scope["_newsCategoryID"];?>/<?php echo $this->scope["_olderOffset"];?>"><?php echo $this->scope["_language"]["olderposts"];?></a><?php
    }?>
    
             <?php if ((isset($this->scope["_showNewerPosts"]) ? $this->scope["_showNewerPosts"] : null) == true) {
    ?>&nbsp;&nbsp;&nbsp;<a class="newsreadmorelink" href="<?php echo $this->scope["_baseName"];
    echo $this->scope["_templateGroupPrefix"];?>/News/List/Index/<?php echo $this->scope["_newsCategoryID"];?>/<?php echo $this->scope["_newerOffset"];?>"><?php echo $this->scope["_language"]["newerposts"];?></a><?php
    }?>
    
             </div>
             <?php
    }?>
    
             <?php if ((isset($this->scope["_newsCount"]) ? $this->scope["_newsCount"] : null) == 0) {
    ?>
             <div class="infotextcontainer">
             <?php echo $this->scope["_language"]["noinfoinview"];?>
    
             </div>
             <?php
    }?>
    
           </div>
           </div><?php  /* end template body */
    return $this->buffer . ob_get_clean();
    ?>
    
    The above is the contents of that particular file.

    And the line which is causing "problems" is:
    Code:
    echo $this->scope["_templateGroupPrefix"];?>/News/NewsItem/View/<?php echo $this->scope["_newsItem"]["newsitemid"];?>/<?php echo $this->scope["_newsItem"]["urlextension"];?>" title="<?php echo $this->scope["_newsItem"]["subject"];?>"><?php echo $this->scope["_language"]["nwreadmore"];?></a>
    
    What can this be? Did I mess up something? As it was working before... :-(

    Regards
     
  13. bear

    bear Kayako Guru

    That is a very dangerous permission setting. Seriously.
     
  14. MvdL79

    MvdL79 Member

    I know, but for testing this issue...
    Anyways, put it back to 775, dunno what else to use. Got this from the documentation obviously.
     
  15. MvdL79

    MvdL79 Member

    Okay made some progress; I am not receiving the error message anymore: Undefined index: nwreadmore (./__swift/cache/feed0254c4541898d4bfba6bb88d6024.php:39)
    I changed in the template "newsitem" (under "News") "nwreadmore" to "readmore".

    This fixed the error on the frontend news page of Kayako...

    ...however for some reason my RSS feeds are still truncated (shortened).

    How do I get the full news message on my RSS feed?


    I really don't understand why it is not working anymore. :-(
     
  16. MvdL79

    MvdL79 Member

    Hi Gurpreet,

    I am getting crazy. No matter what I edit and or change I cannot get the full newsitems to be displayed in the RSS feeds.
    Maybe I need to edit something else? Please advice.

    Now on our websites, which use the RSS news feed from Kayako, are only being displayed half and not in full. :-(
    I am 100% sure this was being displayed fully before.
     
  17. MvdL79

    MvdL79 Member

    Nevermind Gurpreet, I finally found the issue and fixed it! TY.
     
  18. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there! I am so sorry that I was away from my machine yesterday. The error Notice: Undefined index: nwreadmore, it seems to be related to missing language phrase called nwreadmore. Make sure that this phrase exists in the locale which you are loading in your Support Center.

    And glad to know that you were managed to fix the issue :)
     
  19. MvdL79

    MvdL79 Member

    Hi Gurpreet!

    No worries! Really. ;-)
    In the end I managed to fix it anyways. But thank you for responding.

    I was checking and cleaning and fixing several older errors in our current Kayako. When using the "Help tools for webdevelopers" in Chrome I noticed two minor errors:

    Error 1:
    Was an error related to "X-Frame-Options", which can easily be fixed by removing that line from the header template: <meta http-equiv="X-Frame-Options" content="SAMEORIGIN" />
    (so this error was fixed quite easily)

    Error 2 (which appears almost every where on Kayako):
    Uncaught TypeError: $(...).bindLate is not a function
    at HTMLDocument.<anonymous> (index.php?/Knowledgebase/List:761)
    at o (index.php?/Core/Default/Compressor/js:6)
    at Object.fireWith (index.php?/Core/Default/Compressor/js:6)
    at Function.ready (index.php?/Core/Default/Compressor/js:6)
    at HTMLDocument.B (index.php?/Core/Default/Compressor/js:6)


    index.php?/Tickets/Submit/RenderForm:219 Uncaught TypeError: $(...).bindLate is not a function
    at HTMLDocument.<anonymous> (index.php?/Tickets/Submit/RenderForm:219)
    at o (index.php?/Core/Default/Compressor/js:6)
    at Object.fireWith (index.php?/Core/Default/Compressor/js:6)
    at Function.ready (index.php?/Core/Default/Compressor/js:6)
    at HTMLDocument.B (index.php?/Core/Default/Compressor/js:6)

    I Googled this, however couldn't find a solution for it.
    I even tried adding the following to the header template:

    <script type="text/javascript">
    function bindLate(funcName, fixThis) { // instead of bind
    return function () {
    return fixThis[funcName].apply(fixThis, arguments)
    }
    }
    </script>

    (maybe a stupid idea, but I don't have JS knowledge though)
    In the hope of creating this function. However it didn't help or fix things.

    I doubt it's a serious issue, but I rather have an error free Kayako as you might understand.
    Maybe you have an idea what is causing this or how it can be fixed perhaps?

    Thanks in advance.

    Regards
     
  20. Gurpreet Singh

    Gurpreet Singh Staff Member

    Hello there!

    It seems you are using quite an older build of Kayako where this latebinding error appears in the Support Center. I believe this was due to duplicate declaration if the same method, however, I do not remember it on the top of head. This is already addressed in the latest releases.

    You are requested to upgrade your Kayako to the latest build as it has new UI, couple of new features, number of bug fixes and PHP 7 compatibility.
     

Share This Page