The Community Forums

Interact with an entire community of cPanel & WHM users!
  1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Mysql Upgrades

Discussion in 'Workarounds and Optimization' started by golden_eyes, Mar 4, 2011.

  1. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    Hello,

    I wanna upgrade my mysql is there any procedure I must done before doing the upgrades?

    mysql Ver 14.12 Distrib 5.0.91, for unknown-linux-gnu (x86_64) using readline 5.1

    [mysqld]
    set-variable = max_connections=500
    log-slow-queries
    safe-show-database

    when I click on mysql upgrade from the software area in whm it gave the following warning:
    MySQL has limited usernames to 16 characters for many years. In versions of MySQL prior to 5.1, when a login username longer than 16 characters was sent to the server it was silently truncated to the 16 characters limit. In MySQL 5.1, all characters submitted for the username are checked during authentication. This change means that any user submitting more than 16 characters as their login username will fail to authenticate once the server is upgraded. To correct this problem, only send the first 16 characters of the login username.

    Please help me with this before I make the upgrade or interrupt the databases.

    Sincerely,
     
  2. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Do you have any users with MySQL usernames longer than 16 characters at this time? You can run the following to see if you have any return:

    Code:
    mysql -e 'select User from mysql.user' > /root/userslist.mysql && awk '{ if ( length($0) > 16) { b=$0 }}END{ print b }' /root/userslist.mysql
    If you do, then check the file /root/userslist.mysql to see if there are others showing over 16 characters. What this basically does is take all of the User listed in mysql.user table and print them to /root/userslist.mysql file and then check that file for any users over 16 characters. It then prints the last user in the file over 16 characters long to the screen. If you have a zero return to the screen on running the command, then no users are over 16 characters and there would be no issue for the MySQL username lengths on your machine.

    If you do have any users over 16 characters, you'll need to check each of those accounts and add a new, shorter MySQL user in cPanel > MySQL Databases area, then revise the connection string. Alternatively, you could email those clients to ask they add the user and change the connection string.

    Before doing anything else, you would also want to take a full MySQL backup. I'm providing the steps below to make a full backup of MySQL and then restore that backup if needed:

    Code:
    mysqldump --all-databases | gzip > /home/alldatabases.sql.gz
    The command might take awhile to run. Of note, I've given the command with gzip so it will be compressed and have it going into /home so it will not take up space as /var normally doesn't have as much space.

    To restore from that backup, you'll need the MySQL root password:

    Code:
    cat /root/.my.cnf
    Once you have the password, then you can use this command to restore from that file:

    Code:
    gunzip < /home/alldatabases.sql.gz | mysql -u root -p
    You'll be asked for the password here, so simply enter the one noted in /root/.my.cnf location.
     
  3. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    thank you for your steps.

    I ran the first command that you gave, I got over 500 user have over 16 characters, so there is no script to change the password after the upgrades directly from the admin whm instead of the users?
     
  4. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    These users don't need a password change, they need a username change to be less than 16 characters.
     
  5. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    I checked the log file, it seems I have more than 500 user, but when I logged in into pypmyadmin all user are normal such as
    username_root
    username_vb
    username_sms
    etc....

    Any help?
     
  6. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Did you run the command I provided? The total command will not output anything to the shell if no users are over 16 characters. Did any username print? For example, if you change it to 15 rather than 16 in the prior command, you'll always get the last user returning to the shell.

    I believe you might have misunderstood what you would receive for the return. If no users are over 16 characters, nothing outputs to the screen when running the command. You do get content to /root/userslist.mysql at all points because it's writing all the users to that file, then it's counting the characters for each user and only printing to the screen (not the file but the screen) if any user is over 16 characters.
     
  7. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    ohhh sorry for this misunderstood :)

    below is the result of the command please confirm:
    root@web [~]# mysql -e 'select User from mysql.user' > /root/userslist.mysql && awk '{ if ( length($0) > 16) { b=$0 }}END{ print b }' /root/userslist.mysql

    root@web [~]#

    so now what is the next step to finish upgrading?

    Sincerely
     
  8. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Did you create the backup of all the databases using the other command provided? If so, then run the upgrade wizard in WHM for it.
     
  9. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    done :) every thing is working well :)

    thank you alot
     
  10. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Certainly, you are very welcome. Glad it all worked out!
     
  11. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    I surprised now that I'm receiving alot of emails saying that tailwatchd is not running,
    I ran /usr/local/cpanel/libexec/tailwatchd --status and here is the result

    any help please...
     
  12. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    The commands to run are provided in the message you've posted:

     
  13. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    I already did the above commands and still getting the same error log. Look I have 2 servers the first one getting as below:
    Code:
    root@web [~]# /usr/local/cpanel/libexec/tailwatchd --status
    tailwatchd is enabled
    Not running
      Driver (Active: 1) Cpanel::TailWatch::Eximstats
      Driver (Active: 1) Cpanel::TailWatch::cPBandwd
      Driver (Active: 1) Cpanel::TailWatch::Antirelayd
      Driver (Active: 1) Cpanel::TailWatch::ChkServd
    root@web [~]# /usr/local/cpanel/libexec/tailwatchd --start
    root@web [~]# [Fri Mar  4 22:36:54 2011] Starting /usr/local/cpanel/libexec/tailwatch/tailwatchd daemon
    Log is at /usr/local/cpanel/logs/tailwatchd_log
    
    root@web [~]# /usr/local/cpanel/libexec/tailwatchd --status
    tailwatchd is enabled
    Not running
      Driver (Active: 1) Cpanel::TailWatch::Eximstats
      Driver (Active: 1) Cpanel::TailWatch::cPBandwd
      Driver (Active: 1) Cpanel::TailWatch::Antirelayd
      Driver (Active: 1) Cpanel::TailWatch::ChkServd
    
    and the second is:
    Code:
    root@web2 [/backup/cpbackup]# /usr/local/cpanel/libexec/tailwatchd --status
    
    !!
    ATTENTION ATTENTION ATTENTION ATTENTION 
    
    Cpanel::TailWatch::Eximstats appears to have unprocessed SQL in /var/cpanel/sql/eximstats.sql.
    
    When mysql is unable to execute a query they are logged for processing later.
    
    Eventually these SQL files may be handled automatically and this message will not appear.
    
    In the meantime you can execute the queries as root with something like this:
    
      mv /var/cpanel/sql/eximstats.sql /var/cpanel/sql/eximstats.sql.tmp_working_copy
      /usr/local/cpanel/libexec/tailwatchd --restart
      mysql eximstats < /var/cpanel/sql/eximstats.sql.tmp_working_copy
    
    Once you are sure all is well you can remove /var/cpanel/sql/eximstats.sql.tmp_working_copy
    
    
    ATTENTION ATTENTION ATTENTION ATTENTION 
    !!
    
    tailwatchd is enabled
    Running, PID 15121
      Driver (Active: 1) Cpanel::TailWatch::Eximstats
      Driver (Active: 1) Cpanel::TailWatch::ChkServd
      Driver (Active: 1) Cpanel::TailWatch::cPBandwd
      Driver (Active: 1) Cpanel::TailWatch::Antirelayd
    root@web2 [/backup/cpbackup]# hostname
    web2.istdafh.net
    root@web2 [/backup/cpbackup]#
    Although I'm getting the notification mail from the first server only :( so in this case both server are not working but I'm getting notified for the first on only

    Any Idea?

    Sincerely,
     
  14. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    Please open up a ticket if you are still getting the messages and have already run the commands exactly as noted and then restarted cPanel after doing so. Please post the ticket ID number here upon opening the ticket.
     
  15. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    Thank you for your advice, in my first server I'm getting the same error message but with running status
    Code:
    root@web [~]# /usr/local/cpanel/libexec/tailwatchd --status
    
    !!
    ATTENTION ATTENTION ATTENTION ATTENTION 
    
    Cpanel::TailWatch::Eximstats appears to have unprocessed SQL in /var/cpanel/sql/eximstats.sql.
    
    When mysql is unable to execute a query they are logged for processing later.
    
    Eventually these SQL files may be handled automatically and this message will not appear.
    
    In the meantime you can execute the queries as root with something like this:
    
      mv /var/cpanel/sql/eximstats.sql /var/cpanel/sql/eximstats.sql.tmp_working_copy
      /usr/local/cpanel/libexec/tailwatchd --restart
      mysql eximstats < /var/cpanel/sql/eximstats.sql.tmp_working_copy
    
    Once you are sure all is well you can remove /var/cpanel/sql/eximstats.sql.tmp_working_copy
    
    
    ATTENTION ATTENTION ATTENTION ATTENTION 
    !!
    
    tailwatchd is enabled
    Running, PID 10455
      Driver (Active: 1) Cpanel::TailWatch::Eximstats
      Driver (Active: 1) Cpanel::TailWatch::ChkServd
      Driver (Active: 1) Cpanel::TailWatch::cPBandwd
      Driver (Active: 1) Cpanel::TailWatch::Antirelayd
    root@web [~]#
    And another message with not running and without attention in my second server as below:
    Code:
    root@web2 [~]# /usr/local/cpanel/libexec/tailwatchd --status
    tailwatchd is enabled
    Not running
      Driver (Active: 1) Cpanel::TailWatch::Eximstats
      Driver (Active: 1) Cpanel::TailWatch::cPBandwd
      Driver (Active: 1) Cpanel::TailWatch::Antirelayd
      Driver (Active: 1) Cpanel::TailWatch::ChkServd
    root@web2 [~]#
    in order to solv this problem I just deleted the file
    Code:
    rm -rf /var/cpanel/sql/eximstats.sql.tmp_working_copy
    and restarted the cpanel and now the status as below:

    Code:
    root@web [~]# /usr/local/cpanel/libexec/tailwatch/tailwatchd --status
    [Sat Mar  5 22:56:05 2011] Starting /usr/local/cpanel/libexec/tailwatch/tailwatchd daemon
    Log is at /usr/local/cpanel/logs/tailwatchd_log
    root@web [~]#
    and here is the status of the other server

    Code:
    root@web2 [~]# /usr/local/cpanel/libexec/tailwatch/tailwatchd --status
    tailwatchd is already running.
    root@web2 [~]
    now I can confirm that one of them are working well except the other I ran this command
    Code:
    pico /usr/local/cpanel/logs/tailwatchd_log
    and here is the last lines

    Code:
    [2011-03-05 22:57:52 -0600] [Cpanel::TailWatch] [INFO] Opened /usr/local/cpanel/logs/tailwatchd_log in append mode
    [2011-03-05 22:57:52 -0600] [main] [START] 30809 1299387472
    [2011-03-05 22:57:52 -0600] [Cpanel::TailWatch] [INFO] Restored /var/log/maillog (size:5093478) to 4385974 (requested 4385974)
    [2011-03-05 22:57:52 -0600] [Cpanel::TailWatch] [INFO] Restored /var/log/exim_mainlog (size:47063368) to 31556732 (requested 31556732)
    [2011-03-05 22:57:53 -0600] [Cpanel::TailWatch] [INFO] Caught up /var/log/maillog to 5093478
    [2011-03-05 22:57:53 -0600] [Cpanel::TailWatch] [INFO] Reading back thirty lines of /var/log/maillog starting at 5077094
    [2011-03-05 22:57:53 -0600] [Cpanel::TailWatch] [INFO] Restoring /var/log/maillog to catch up position 5093478
    [2011-03-05 22:57:53 -0600] [Cpanel::TailWatch] [INFO] Restored /var/log/maillog to position 5093478
    install_driver(mysql) failed: Can't load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib64/libmysqlclient.s$
     at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm line 14
    Compilation failed in require at (eval 11) line 3.
    Perhaps a required shared library or dll isn't installed where expected
     at /usr/local/cpanel/Cpanel/TailWatch/Eximstats.pm line 252
    Is I'm still facing any problem with the tailwatch?

    Sincerely,
     
  16. golden_eyes

    golden_eyes Well-Known Member

    Joined:
    Aug 9, 2010
    Messages:
    83
    Likes Received:
    0
    Trophy Points:
    6
    I created a ticket but no response from their side although the current status is still not running and the error as below:
    Code:
    [2011-03-07 01:40:01 -0600] [Cpanel::TailWatch] [INFO] Restored /var/log/exim_mainlog (size:16834385) to 361722 (requested 361722)
    [2011-03-07 01:40:01 -0600] [Cpanel::TailWatch] [INFO] Caught up /var/log/maillog to 470392
    [2011-03-07 01:40:01 -0600] [Cpanel::TailWatch] [INFO] Reading back thirty lines of /var/log/maillog starting at 454008
    [2011-03-07 01:40:01 -0600] [Cpanel::TailWatch] [INFO] Restoring /var/log/maillog to catch up position 470392
    [2011-03-07 01:40:01 -0600] [Cpanel::TailWatch] [INFO] Restored /var/log/maillog to position 470392
    install_driver(mysql) failed: Can't load '/usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so' for module DBD::mysql: /usr/lib64/libmysqlclient.so.15: version `libmysqlclient_15' not found (required by /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/auto/DBD/mysql/mysql.so) at /usr/lib64/perl5/5.8.8/x86_64-linux-thread-multi/XSLoader.pm line 70.
     at /usr/lib64/perl5/site_perl/5.8.8/x86_64-linux-thread-multi/DBD/mysql.pm line 14
    Compilation failed in require at (eval 11) line 3.
    Perhaps a required shared library or dll isn't installed where expected
     at /usr/local/cpanel/Cpanel/TailWatch/Eximstats.pm line 252
    Any help please :(
     
  17. cPanelTristan

    cPanelTristan Quality Assurance Analyst
    Staff Member

    Joined:
    Oct 2, 2010
    Messages:
    7,623
    Likes Received:
    21
    Trophy Points:
    38
    Location:
    somewhere over the rainbow
    cPanel Access Level:
    Root Administrator
    I'm sorry to hear that you weren't able to resolve it that way. If you are still having issues, please post the ticket number here so I can look up your ticket, since that information hasn't been provided.
     
Loading...

Share This Page