28 Replies Latest reply on Oct 30, 2018 3:09 PM by jcutrone

    12.6 MySQL Upgrade Fails

    skreycik

      Hey everyone,

       

      I have attempted to upgrade WHD 12.5.2 to 12.6 a few times now and every time I run into the same issue. The application updates without issue, but the database upgrade fails. We are running MySQL 5.7. The error in the whd-spring.log file states that it is attempting to run "UPDATE client SET email = NULL WHERE email LIKE '%<%>%'" but it errors out and says the table doesn't exist. Thus, it never starts Spring and the upgrade fails forcing us to revert to the last snapshot. Obviously, the client table exists or the product would not run, but in our database the table names are all CAPS. I have to wonder if the scripts are case sensitive. I have just opened a ticket with Solarwinds to inquire, but thought I would reach out to the community and see if anyone else has run into this. I'm attaching a text file I compiled of the pertinent errors in both whd-spring.log and helpdesk.log from the attempt I made last night.

        • Re: 12.6 MySQL Upgrade Fails
          skreycik

          I should also mention, if not evident from the log files, that we are running both the front end and the database on Redhat 7.

          • Re: 12.6 MySQL Upgrade Fails
            skreycik

            I figured it out. It was related to case sensitivity. I have successfully upgraded to 12.6! Here’s what I had to do:

             

            1. Back up the database using mysqldump
            2. Drop the database
            3. Add lower_case_table_names=1 to the [mysqld] section of /etc/my.cnf
            4. Restart mysqld
            5. Import the backup (this took a long time) using mysql –u [user] –p < [backup_file]
              1. This changes all tables to lowercase, and allows for non-case-sensitive queries
            6. Run the webhelpdesk rpm upgrade

             

            Performing all of these steps worked for me. If you use MySQL on Linux and your table names are all CAPS, this should work for you as well.

            2 of 2 people found this helpful
              • Re: 12.6 MySQL Upgrade Fails
                123kateb

                Is this different from the last upgrade you did to WHD - is this new to 12.6 upgrade with mysql?

                 

                We are preparing to do this same upgrade, from 12.5.2, .and haven't ever had this case sensitive issue along the way.

                  • Re: 12.6 MySQL Upgrade Fails
                    skreycik

                    This was new to the upgrade from 12.5.2 to 12.6. Never had the problem before then. Easiest way to check would be to look at the tables in the database. If they are all CAPS and the database is on Linux, you will run into this issue. If they are all lowercase or your database server is windows, you should be fine. As always, take backups and/or snapshots before trying. They saved me this go around.

                    2 of 2 people found this helpful
                • Re: 12.6 MySQL Upgrade Fails
                  123kateb

                  milan.hulik  fluffy midnight  Will this be fixed in a later release of this 12.6 upgrade distribution?  Was there

                  a reason that Solarwinds is changing the case sensitivity of this release and will the release notes

                  indicate this?

                   

                  We didn't find anything in the release notes that mentioned this issue, or the SW recommended fix.

                  • Re: 12.6 MySQL Upgrade Fails
                    jcutrone

                    We are having the same problem. We have been upgrading our WHD install for over 10 years without issues until now. Our MySQL server is running on RHEL 7 also which results in case-sensitive database services by default. Since WHD isn't the only database running on our MySQL server, the global "lower_case_table_names" setting isn't an option since that may affect other databases and apps we have. I have a ticket in with WHD support also and they sent me a script that renames all the database table names to lowercase. Basically it was a just a bunch of "ALTER TABLE X RENAME TO x" SQL statements. Just renaming the database tables didn't work for me since it appears when you first start up WHD it does a check to see if an existing WHD database is present. It appears to do this check using a SQL statement that references all uppercase table names. Since this check fails after renaming all the database tables to lowercase it presents me with the new installation setup wizard when I start up WHD after the table rename. So after renaming the table names I don't even get the option to upgrade the database since WHD now thinks no existing database is present and it wants to create a new one. I did a clean install of WHD 12.6.0 to an empty MySQL database on our server and that resulted in table names that were in all uppercase. So it is looking like the 12.6.0 code is not consistent with using either all upper or all lower case tables names in SQL queries. If that is the case then the only fix is looking like either all the WHD 12.6.0 code needs to be fixed to be consistent with the table name casing in SQL queries or people with case-sensitive database servers have to make them case-insensitive like what the op did. Hopefully I am wrong and there is a simpler fix WHD support will surprise me with.

                      • Re: 12.6 MySQL Upgrade Fails
                        jcutrone

                        After some digging I was able to get the upgrade to complete on a test installation of WHD 12.6.0 that I created to point at a copy of our production WHD 12.5.1 database. From what I can tell there is a programming error in a couple of SQL statements that are part of the database upgrade process. There are 2 SQL statements that reference the table "client" in all lowercase when it should be all uppercase. The 2 lines are at the top of the file "upgrade-mysql-12-6-0.sql" which is contained inside the /usr/local/webhelpdesk/bin/webapps/helpdesk/WEB-INF/lib/whd-persistence.jar file. After I corrected the SQL statements the upgrade completed without errors. However I don't trust any of the new 12.6.0 code after seeing this so I don't want to upgrade our production WHD instance. I requested WHD support escalate my issue to one of their app engineers to confirm there are no other case-sensitivity issues with any of the new 12.6.0 code.

                         

                        As an aside during my digging I did notice there is a single table in our database with an all lowercase name - "swip_product_info". Inside that whd-persistence.jar file is another file used for the upgrade to WHD 12.5.1. Inside that file is the SQL code that creates that table with an all lowercase name. When you look at the rest of the WHD programming it appears obvious that the coding standard was to use all upper case table names for the database so I am wondering if this other table with an all lowercase name is going to cause problems down the road.

                          • Re: 12.6 MySQL Upgrade Fails
                            123kateb

                            Should everyone with MySQL db's open a case or are you getting traction with SW?  fluffy midnight  milan.hulik  @newprojectmanagerforWHD

                              • Re: 12.6 MySQL Upgrade Fails
                                jcutrone

                                I was just emailed by one of their Application Engineers. They said my case was being forwarded to the development team to discuss how to handle it. Based on my experience with other products, the more people that point out a problem the better. So I think everyone with the issue opening up a ticket would be a good idea just to give SW an idea of how big of an impact this is. Maybe in the ticket reference my case # 00188031 and this forum thread so they know it's the same issue.

                                1 of 1 people found this helpful
                                  • Re: 12.6 MySQL Upgrade Fails
                                    123kateb

                                    Created a ticket also, 00192403, a noncommital response?

                                     

                                    "I do not have information yet if a fix is being made by our Dev Team so we cannot provide an ETA, if there is any at all."

                                      • Re: 12.6 MySQL Upgrade Fails
                                        jcutrone

                                        Got a reply back today. My case was put into "Archived - Bug Jury" status. Response that I got:

                                         

                                        -----------------

                                         

                                        "Thank you for reporting this issue to SolarWinds Support. Our development group has investigated this issue and have identified the bug. We've added this bug to our list of known defects and will prioritize it according to the severity and scope of impact. Be sure to review the Release Notes where bug fixes are documented within the Fixed Issues section.

                                         

                                        Although this case will be archived, we will continue to track this issue internally through our engineering processes. You can use this case number to inquire about updates to the bug if necessary.

                                         

                                        As a reminder, SolarWinds does not announce future release content. We prefer to focus on what our products can currently do rather than talking about possible future functionality."

                                         

                                        -----------------

                                         

                                        I replied back asking them to at least confirm whether or not the development team responsible for testing the rest of the new 12.6.0 code did so using a case-sensitive database server. Also I asked for clarification on whether or not SW recommends we proceed with the 12.6.0 upgrade using the workaround I came up with or if we should wait for some future 12.6.x release that lists the bug with the upgrader is fixed. My reply back opened up a new case number since I guess when an existing ticket is put into "archived" status it is basically closed. Hopefully I will get a quick response and not have to wait to be bumped up to the app engineer level again.

                                          • Re: 12.6 MySQL Upgrade Fails
                                            123kateb

                                            And will the release notes be amended to reflect additional steps required to upgrade?

                                             

                                            Seems like they should be fixing the code, instead of developing a workaround, right?

                                              • Re: 12.6 MySQL Upgrade Fails
                                                jcutrone

                                                My impression is that they recognize the issue and will fix it in a future release such that the normal upgrade process we have been doing in the past will work. The workaround I came up with is basically the code fix that needs to be done for the 12.6.0 upgrader. My concern is if there are any more case-sensitive issues like this in any of the new 12.6.0 code. If there is, there is potential for things to get out of whack in the database via partially failed SQL commands which could snowball into a corrupted WHD database requiring a bunch of manual fixes to clean everything up.

                                                  • Re: 12.6 MySQL Upgrade Fails
                                                    123kateb

                                                    "Be sure to review the Release Notes where bug fixes are documented within the Fixed Issues section."

                                                     

                                                    Trying to figure out what that statement means?  Do they intend to fix on the fly?  This is not so much

                                                    a bug as a regression that needs to be repaired, and the distribution release changed to 12.6.1.

                                                     

                                                    Thoughts?

                                                      • Re: 12.6 MySQL Upgrade Fails
                                                        typhoon87

                                                        Given SW typical MO I would think that a fix for this in a 12.6.X service release is most likely. Unless a large number of customers open tickets its unlikely they pull and replace the 12.6.0 release to fix an installer bug.

                                                        1 of 1 people found this helpful
                                            • Re: 12.6 MySQL Upgrade Fails
                                              bshopp

                                              I would recommend a support case so we can dig into.  Feel free to reference me, not sure if that will help though

                                              1 of 1 people found this helpful
                                            • Re: 12.6 MySQL Upgrade Fails
                                              123kateb

                                              The KB mentions as part of their steps:

                                               

                                              Change the file name from:

                                              whd-persistence.jar

                                              to

                                              whd-persistence.zip

                                              Note: You can also use 7-Zip for extraction.

                                              Unzip the JAR/ZIP file (from step 4)

                                              Locate upgrade-mysql-12.0.sql and open in an editor.

                                              Locate:
                                              UPDATE client SET email = NULL WHERE email LIKE '%<%>%';
                                              UPDATE client SET secondary_email = NULL WHERE secondary_email LIKE '%<%>%';

                                              Change this code to:
                                              UPDATE CLIENT SET email = NULL WHERE email LIKE '%<%>%';

                                              UPDATE CLIENT SET secondary_email = NULL WHERE secondary_email LIKE '%<%>%';

                                               

                                              Does that file they are referencing in the KB match the one you edited?  Not sure why

                                              that would be the one?  If there aren't other steps, that jar file doesn't have the

                                              correct contents (assuming its an upgrade-mysql 12.6 version)...

                                               

                                              Asking SolarWinds to clarify also.

                                               

                                                • Re: 12.6 MySQL Upgrade Fails
                                                  jcutrone

                                                  Yes that is the file. The KB article is assuming you have already started the upgrade to 12.6.0 and it failed on you. So the newer whd-persistence.jar file which is included with WHD 12.6.0 is on your server already. If you are looking at the whd-persistence.jar file from your version of WHD that is older than 12.6.0 then that would explain why you don't see the .sql file you need to edit. You may want to see if SW support will provide you a copy of the modified jar file to save you some work. I originally had tried to repack the edited .jar file the way the KB article says but for whatever reason it didn't work for me. Most likely it was something I was doing wrong or something about my server caused the issue. I ended up having to use the actual jar command to "update" the contents of the existing jar file to get it to work with a command similar to "jar uf jarFileToUpdate.jar modified-file.sql". If after you follow the KB article and your upgrade fails again with error "Spring application did not start successfully" then you ran into the issue I had and you may need to use that "jar" command to update the 12.6.0 whd-persistence.jar file.

                                            • Re: 12.6 MySQL Upgrade Fails
                                              123kateb

                                              received this response to my ticket  00192403  - asking if they can close it with no additional information.

                                               

                                              I'm asking SW to escalate, seems very odd that they just want to close what is a big concern for us?

                                              -----------

                                               

                                               

                                              Just checking in to see if the information that I provided you is sufficient for your concern, or if you still need further assistance with our Web Help Desk product.

                                              Please let us know if you need additional assistance with our Web Help Desk product or if we close this ticket from our end.

                                               

                                              Thank you.

                                               

                                              Regards,

                                                • Re: 12.6 MySQL Upgrade Fails
                                                  jcutrone

                                                  My follow-up ticket where I asked if the rest of the 12.6.0 code besides the upgrader was tested against a case-sensitive database server was closed last week the same day that I submitted it. I didn't receive any response to the ticket so I re-opened yesterday. Still waiting for the answer to that question and clarification if Solar Winds is saying people with this problem need to not do any workarounds and instead wait for a fix in a future WHD release.

                                                  1 of 1 people found this helpful
                                                    • Re: 12.6 MySQL Upgrade Fails
                                                      123kateb

                                                      I, too, followed up yesterday and asked them to answer your questions for the rest of us.

                                                       

                                                      I also asked if the customer provided fix went through QA at SolarWinds? Seems odd that they would take this course.

                                                       

                                                      Here was what they had put in the case:

                                                      -----------------------------------------------

                                                       

                                                      As stated from my previous email, we don't have information yet if our Dev Team is making a fix so we cannot provide an ETA, as our Dev Team still reviewing it and currently in progress.

                                                      But you can try to visit the link below where a workaround is mentioned to solved the MySQL issue with WHD 12.6.

                                                      You will need to add lower_case_table_names=1 to the [mysqld] section of /etc/my.cnf. See link below:

                                                      https://thwack.solarwinds.com/thread/122051

                                                      Please let me know if given information will suffice.

                                                      Thank you.

                                                        • Re: 12.6 MySQL Upgrade Fails
                                                          jcutrone

                                                          I just got a response back from an app engineer. Looks like they are actively looking into the issue and testing the workaround I posted where I edited those SQL scripts inside the whd-persistence.jar file. They are still testing my fix so they aren't ready to recommend it yet but the case-sensitivity issue appears to be limited to just the upgrade script which is good news. I am going to wait to hear back from them before I attempt the upgrade on our production system. Here is the response I got today:

                                                           

                                                          -----------------------------

                                                           

                                                          Thus far, development has confirmed that the issue with case-sensitivity for installs with MySQL databases would be limited to just the upgrade scripts, which will be fixed in a future release. We are currently testing the work-around you found (thank you!) to be sure it will work before we give any recommendations while we await the proper fix. I'll let you know when testing is complete and can you give the go ahead/or alternative.

                                                          1 of 1 people found this helpful
                                                          • Re: 12.6 MySQL Upgrade Fails
                                                            jcutrone

                                                            Support has gotten back to me and confirmed that the workaround I mentioned with editing the SQL update script is all that needs to be done to successfully complete the 12.6.0 upgrade. They made a KB article that lists the steps to perform the workaround. You can see the KB article here: Upgrading to Web Help Desk 12.6 for MySQL users - SolarWinds Worldwide, LLC. Help and Support