Announcement

Collapse
No announcement yet.

SQL Exception adding record(s) to the ENERGY database(0)

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • SQL Exception adding record(s) to the ENERGY database(0)

    I've been away for a few months still running HS2, but it looked like build HS3 177 was stable so I jumped back in to HS3. Bang, bang I'm at 180 and 181. OK, and it looks like all the ZWave issues I'd experienced for the last year and a half are ironed out.

    It's the plug-ins that are killing me. HS2 to HS Touch to iPhone has been stable for years, but I couldn't get HS3 to work to save my life. Everything from HS2 I paralleled to HS3 but no joy. Uninstall, debug messages, damn - I spent hours on it. FWIW it turned out that for HS2 I opened the firewall for HomeSeer.exe, but for HS3 it had to be HSPI_HSTOUCH.exe. Maybe that will save someone a few hours headache.

    So then I've been beating my head against the wall over the Current Cost plugin. Installed, uninstalled, damn - many more hours thrown away. And then I came across the recommendations for MrHappy's "Current Cost 3P" - a bit gnarly to figure out that message 1 from more than a year ago contains a .zip that has been updated. And the install took some...ok, I had to read the documentation and follow the steps in detail.

    But it worked a peach. I cannot for the life of me understand why HS would keep their Current Cost plug-in ("free!", "included!", "jump down this rabbit hole and throw your valuable time away") when it clearly doesn't work.

    So now my previously unexciting Log is filling up with these errors:
    May-31 6:57:05 PM Error Energy data failed to be added to the energy database - see the log for possible reasons.
    May-31 6:57:05 PM Error SQL Exception adding record(s) to the ENERGY database(0): SQL logic error or missing database table EnergyData has no column named Duration, Code=1 SQL=INSERT INTO [EnergyData] ([dvRef],[Direction],[Amount],[Amount_Start],[Amount_End],[Duration],[Rate],[UserCode]) VALUES (285,1,0,'2015-05-31 18:55:05','2015-05-31 18:56:05',60,0.13,0);

    I disabled ALL plug-ins (starting with CC3P). Keeps happening.

    I went back and deleted all Current Cost files and settings. Keeps happening.

    Of course I've killed and restarted HS3 numerous times. Keeps happening.

    So...any idea what's going on here? Something is trying to log some Energy data and it's failing. The Energy tab in HS3 shows nada (and since I never got Current Cost to actually work, I kind of expected that). But is there some other setting I need to turn off? Or some other configuration setting I'm missing? Any help appreciated...

  • #2
    I would guess that somewhere along the way you missed an update the added the field duration to your energy database.

    Since I don't have any devices that report energy my database is empty. I checked and the field that you are missing is showing in my database.

    I put a copy in a zip file and you can download it from here:
    https://dl.dropboxusercontent.com/u/...HS3/Energy.zip

    Save the energy.hsd file to the HomeSeer HS3/Data/Energy folder.
    --
    Jeff Farmer
    HS 3.0.0.312, HSPhone
    My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
    Other Plugins In Use: APCUPSD, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

    Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

    Comment


    • #3
      The SQL statement is showing that it is trying to enter data from a device with the refID of 285. If you can find that device you can try to figure out why it is causing an entry to the energy database.
      --
      Jeff Farmer
      HS 3.0.0.312, HSPhone
      My HS3 Plugins: CFHSExtras, Random, Restart, Tracker, WeatherXML, PanaBluRay
      Other Plugins In Use: APCUPSD, Device History, EasyTrigger, HSTouch Server, PHLocation2, Pushover, RFXCom, UltraGCIR3, UltraMon3, UltraPioneerAVR3, X10, Z-Wave

      Hardware: GoControl Irrigation Controler, Schlage Lever Lock, Schlage Deadbolt, Way2Call Hi-Phone, RFXCom RFXrec433 Receiver, WGL 800, TI-103, Z-Net, Pioneer 1120, Pioneer 1021, Pioneer LX302, Panasonic BDT-110, Panasonic BDT-210 x2

      Comment


      • #4
        Originally posted by CFGuy View Post
        I would guess that somewhere along the way you missed an update the added the field duration to your energy database.

        Since I don't have any devices that report energy my database is empty. I checked and the field that you are missing is showing in my database.

        I put a copy in a zip file and you can download it from here:
        https://dl.dropboxusercontent.com/u/...HS3/Energy.zip

        Save the energy.hsd file to the HomeSeer HS3/Data/Energy folder.
        Thanks! I'll try that in the morning...

        Edit: I was able to push the file onto the server remotely. It worked! Now I have a single 500 watt-ish reading in the Energy graph. Wierd.
        Last edited by centerisland; May 31st, 2015, 11:22 PM.

        Comment


        • #5
          Originally posted by CFGuy View Post
          The SQL statement is showing that it is trying to enter data from a device with the refID of 285. If you can find that device you can try to figure out why it is causing an entry to the energy database.
          Well...I get a family of these errors together...refIDs from 282 to 285. And my log (showing online) is completely full of these messages now so it's hard to see what device it corresponds to. But...if I disable all 4 of my plugins (Current Cost 3P, HSTouch Server, RFXCOM, and Z-Wave) then I shouldn't have any devices left!

          Comment


          • #6
            The registration of data to log into the Energy DB does appear to have some issues, I get a mixed success rate of whether a device registers itself or not and I have logged it as a bug (#2180). I could not work out why and for the most part I just had to put a warning in the log suggesting a restart, I also got no explanation of when a device should be registered (start of plugin, only once etc).

            In your case I think you will need to find out what devices 282 to 285 actually belong to first and then work from there.
            My Plugins:

            Pushover 3P | DoorBird 3P | Current Cost 3P | Velleman K8055 3P | LAMetric 3P | Garadget 3P | Hive 3P |
            Yeelight 3P | Nanoleaf 3P

            Comment


            • #7
              Originally posted by mrhappy View Post
              The registration of data to log into the Energy DB does appear to have some issues, I get a mixed success rate of whether a device registers itself or not and I have logged it as a bug (#2180). I could not work out why and for the most part I just had to put a warning in the log suggesting a restart, I also got no explanation of when a device should be registered (start of plugin, only once etc).

              In your case I think you will need to find out what devices 282 to 285 actually belong to first and then work from there.
              It seems obvious now - these are the CC3P devices:
              Reference ID 282
              Technology Address CC-1-S0-TotalWatts
              Status 2345 = Unknown
              Value 2345 = "2345 Watts"
              And the values there seem to track with your graphs. But I was getting the errors even when I disabled your plugin. So I guess the devices still function independently of the plug-in (maybe they were just writing 0s?).

              And BTW, I'm really liking your plugin - is there a way to make a modest PayPal donation? Or should I just hoist a glass in your direction during the next "Happy Hour"?

              Comment


              • #8
                Originally posted by centerisland View Post
                It seems obvious now - these are the CC3P devices:
                Reference ID 282
                Technology Address CC-1-S0-TotalWatts
                Status 2345 = Unknown
                Value 2345 = "2345 Watts"
                And the values there seem to track with your graphs. But I was getting the errors even when I disabled your plugin. So I guess the devices still function independently of the plug-in (maybe they were just writing 0s?).

                And BTW, I'm really liking your plugin - is there a way to make a modest PayPal donation? Or should I just hoist a glass in your direction during the next "Happy Hour"?
                If it does it when the plugin is stopped then I am afraid it is going to be the processing in the background that HomeSeer is doing creating those errors, unfortunately I have no control over that because all the functions that write data to the energy API cease when the plugin is stopped.

                Are you still getting the errors? If so I think that I would disable all plugins, stop HS, delete the energy DB and then start HS. Then start the plugins one by one. That should ensure that a clean creation of the energy database before any of the plugins try and write data to it. There are unfortunately some little issues with the API that have never been explained so a bit of it is just little more than guessing what it wants me to do. Thanks for the offer of a donation but it is one thing I can't accept I am afraid but I will accept the compliment
                My Plugins:

                Pushover 3P | DoorBird 3P | Current Cost 3P | Velleman K8055 3P | LAMetric 3P | Garadget 3P | Hive 3P |
                Yeelight 3P | Nanoleaf 3P

                Comment


                • #9
                  Originally posted by mrhappy View Post
                  If it does it when the plugin is stopped then I am afraid it is going to be the processing in the background that HomeSeer is doing creating those errors, unfortunately I have no control over that because all the functions that write data to the energy API cease when the plugin is stopped.

                  Are you still getting the errors?
                  No - once I updated with CFGuy's (empty) database with the correct schema then all the errors stopped.

                  Since all of this is (now) working fine for me, then I'm not inclined to muck with it further. If I had to hazard a guess, I would say that the HS Current Cost device generation is broken but your CC3P generation not only works for your plugin, but has the side benefit of pushing data into the HS energy database.

                  Comment


                  • #10
                    I've started seeing similar errors in my log that I'm not sure where its coming from? I use the CurrentCost plug-in that I have disabled and still see the errors in the log. Any thoughts?


                    Jul-17 3:09:28 PM Energy DB SQL statments completed. Modifying database.
                    Jul-17 3:09:26 PM Energy DB Compaction completed, rechecking database size...
                    Jul-17 3:09:25 PM Error Exception adding record(s) to the ENERGY database(0): SQL logic error or missing database near "(": syntax error
                    Jul-17 3:09:19 PM Energy DB SQL statments completed. Modifying database.
                    Jul-17 3:09:18 PM Energy DB Compaction completed, rechecking database size...
                    Jul-17 3:09:16 PM Error Exception adding record(s) to the ENERGY database(0): SQL logic error or missing database near "(": syntax error
                    Jul-17 3:09:11 PM Energy DB SQL statments completed. Modifying database.
                    Jul-17 3:09:10 PM Energy DB Compaction completed, rechecking database size...
                    Jul-17 3:09:08 PM Error Exception adding record(s) to the ENERGY database(0): SQL logic error or missing database near "(": syntax error
                    Jul-17 3:09:03 PM Energy DB SQL statments completed. Modifying database.
                    Jul-17 3:09:02 PM Energy Database Generating compaction records.
                    Jul-17 3:09:02 PM Energy DB Records will be compacted to 2 minute blocks of data.
                    Jul-17 3:09:02 PM Energy DB Calculating compact ratio.
                    Jul-17 3:09:02 PM Energy DB Database is 0.008789063 MB larger than required and will have the data compacted.

                    Comment

                    Working...
                    X