Version 3

    Problem1:

     

    Post NPM 12.0 upgrade, when executing solarwinds database maintenance application either manually or via schedule an error of “input string was not in a correct format.” is presented and application stops there after.

     

     

    Problem2:

     

    When going into Settings > All Settings > Polling settings > any settings change… when values for any settings change are modified nothing is actually saved after hitting the submit button at the bottom of the page.

     

    Problem3:

     

    Post NPM 12.0 upgrade newly discovered nodes or changes via scheduled discovery will not allow you to import them.  When going through the motion of importing you are given an error that an exception error has occurred and import can not be completed at this time.

     

    Solution to All three problems:

     

    While working with Solarwinds Support, they indicated that this could be caused by incorrect values in column currentvalue of the settings table.  Possible that some values are in decimal or scientific notation when they shouldn’t be or are out of range when compared to maxvalue column.  Link to KB - https://support.solarwinds.com/Success_Center/Network_Performance_Monitor_(NPM)/Database_Maintenance_failing,_Input_string_was_not_in_a_correct_format

     

    I checked all the values about 20 times and they all looked good and Support also checked the same thing but nothing seems to work. 

     

    Support asked me to repair and reinstall core services, re-run configuration wizard and various other options but nothing seems to work.  It is time now to take matters into my hands as best I can.

     

    My goal was to see what exactly is causing my error of “input string was not in the correct format” and I knew dotnet framework 4.5 was required in NPM 12.0 so, I downloaded and installed a decompiler called Telerik JustDecompile and loaded the database-maint.exe file into it to see what it was doing.  Confession, I am not a developer by any means, but my goal was to see what this application was doing from an error message point of view to get more detail. 

     

    There is a log file that is created in: C:\programdata\solarwinds\Logs\Orion\swdebugMaintenance.log and when looking inside this file I noticed the following information:

     

    2016-06-24 22:04:58,299 [4] ERROR SolarWinds.Data.DatabaseMaintenance.Settings - Error retrieving settings:
    System.FormatException: Input string was not in a correct format.
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
       at System.Convert.ToInt32(String value)
       at SolarWinds.Data.DatabaseMaintenance.Settings.Refresh()
    2016-06-24 22:04:58,346 [1] ERROR DatabaseMaintenanceGui.MainForm - Database Maintenance Error.
    System.FormatException: Input string was not in a correct format.
       at System.Number.StringToNumber(String str, NumberStyles options, NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal)
       at System.Number.ParseInt32(String s, NumberStyles style, NumberFormatInfo info)
       at System.Convert.ToInt32(String value)
       at SolarWinds.Data.DatabaseMaintenance.Settings.Refresh()
       at SolarWinds.Data.DatabaseMaintenance.MaintenanceEngine..ctor()
       at DatabaseMaintenanceGui.MainForm._backgroundWorker_DoWork(Object sender, DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
       at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

    Now, this message indicating that a conversion of string to number didn’t go as planned and the question remains as what is it actually trying to convert.   While looking in the decompiler for hours and days it seems I found a sub function under solarwinds.data.databasemaintenance called settings and inside of it I see the following:

     

       public static void Refresh()

            {

                try

                {

                    Settings.RetainDetailed = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Detail"));

                    Settings.RetainHourly = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Hourly"));

                    Settings.RetainDaily = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Daily"));

                    Settings.ContainerRetainDetailed = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Container Detail"));

                    Settings.ContainerRetainHourly = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Container Hourly"));

                    Settings.ContainerRetainDaily = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Container Daily"));

                    Settings.RetainEvents = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Events"));

                    Settings.RetainSyslogMessages = Convert.ToInt32(SettingsDAL.Get("SysLog-MaxMessageAge"));

                    Settings.RetainTrapMessages = Convert.ToInt32(SettingsDAL.Get("Trap-MaxMessageAge"));

                    Settings.RetainDiscovery = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Discovery"));

                    Settings.RetainAuditingTrails = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Retain Auditing Trails"));

                    Settings.BaselineCollectionDuration = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Baseline Collection Duration"));

                    Settings.ForecastCollectionDuration = Convert.ToInt32(SettingsDAL.Get("SWNetPerfMon-Settings-Forecast Collection Duration"));

                    Settings.MaxRowCountSyslog = Settings.GetMaxRowCount("Syslog");

                    Settings.MaxRowCountTraps = Settings.GetMaxRowCount("Traps");

                    Settings.MaxRowCountTrapVarbinds = Settings.GetMaxRowCount("TrapVarbinds");

                    string str = SettingsDAL.Get("SWNetPerfMon-Settings-MaxMaintenanceThreads");

                    Settings.MaxMaintenanceThreads = Convert.ToInt32((string.IsNullOrWhiteSpace(str) ? 1.ToString(CultureInfo.InvariantCulture) : str));

                    if (Settings.MaxMaintenanceThreads < 1)

                    {

                        Settings.MaxMaintenanceThreads = 1;

                    }

                }

                catch (Exception exception)

                {

                    Settings._log.Error("Error retrieving settings:", exception);

                    throw;

                }

     

    Now that I know what it is looking for, I next looked up what the function SettingsDAL was doing and noticed the following:

     

    public class SettingsDAL : ISettingsDAL

        {

            private readonly static Log log;

     

            static SettingsDAL()

            {

                SettingsDAL.log = new Log();

            }

     

            public SettingsDAL()

            {

            }

     

            public static string Get(string settingName)

            {

                return SettingsDAL.Get(settingName, null);

            }

     

            public static string Get(string settingName, SqlConnection conn)

            {

                string str;

                using (SqlCommand textCommand = SqlHelper.GetTextCommand(string.Format("SELECT CurrentValue FROM Settings WHERE SettingID LIKE '{0}'", settingName)))

                {

                    object obj = SqlHelper.ExecuteScalar(textCommand, conn);

                    str = (DBNull.Value == obj || obj == null ? string.Empty : obj.ToString());

                }

                return str;

            }

    More to this function then entered here…



    So it appears that database-maint.exe is doing the following upon clicking on start:

     

    1. It converts values from currentvalue column of settings tables from being in string type to interger.
    2. In order to do the conversion the settingsDAL function is going to the database and preparing a query of: SELECT CurrentValue FROM Settings WHERE SettingID LIKE '{0}' where {0} is the value it is looking for in the settingID column of the table settings.
      1. Example:


    This lookup of current value is done for all values identified in the refresh() function. 
    Now I am seeing the problem clear as day, in our instance something went wrong where lots of blank spaces were entered right after all values in the settingID column.  This means that when the query is run to get the current value nothing is returned thus the exception and database-maint.exe failing to run.
    Example:

    NOTE: No values are returned
    Now if I add % at the end of the like statement we see what we want to see:

    I removed the extra blanks manually and the initial query works as expected:

    The final fix it to apply this SQL change to all values in the settingID column for table settings.  I did this table wide by using RTTRIM() function of MS SQL

    1. Open Database Manager
    2. Add default server
    3. Open your database and right click on the settings table and choose query table
    4. Delete all the text in the query and enter the following:

    UPDATE SETTINGS

    SET SETTINGID=rtrim(SETTINGID)

           5. Click on Execute query and check the message to see if ~247 rows are changed

     


    Finally you can now run database-maint.exe and click on start and it will run as expected.

     

    This solved problem 1, 2 and 3 along with another problem of MAPS not loading as expected.