|
|
Post by doxidad on Jun 21, 2022 16:47:58 GMT -8
That's strange. That script should have created the tables if they didn't exist. If you look at the script, what version is do you have. Here is the version I have: 1 -- phpMyAdmin SQL Dump 2 -- version 4.9.1 3 -- www.phpmyadmin.net/ 4 -- 5 -- Host: localhost:3306 6 -- Generation Time: Jan 30, 2021 at 10:00 AM 7 -- Server version: 10.3.23-MariaDB-0+deb10u1 8 -- PHP Version: 7.3.19-1~deb10u1
After checking to see if the database exists it checks for the tables and if they don't they are created.
Example - if table AQI433MHZ doesn't exist - create it
33 CREATE TABLE IF NOT EXISTS `AQI433MHZ` (
34 `ID` int(11) NOT NULL,
35 `timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
36 `messageID` int(11) NOT NULL,
37 `deviceid` int(11) NOT NULL,
38 `protocolversion` int(11) NOT NULL,
39 `softwareversion` int(11) NOT NULL,
40 `weathersenseprotocol` int(11) NOT NULL,
41 `PM1_0S` int(11) NOT NULL,
42 `PM2_5S` int(11) NOT NULL,
43 `PM10S` int(11) NOT NULL,
44 `PM1_0A` int(11) NOT NULL,
45 `PM2_5A` int(11) NOT NULL,
46 `PM10A` int(11) NOT NULL,
47 `AQI` int(11) NOT NULL,
48 `AQI24Hour` float NOT NULL,
49 `batteryvoltage` float NOT NULL,
50 `batterycurrent` float NOT NULL,
51 `loadvoltage` float NOT NULL,
52 `loadcurrent` float NOT NULL,
53 `solarvoltage` float NOT NULL,
54 `solarcurrent` float NOT NULL,
55 `auxa` int(11) NOT NULL,
56 `batterycharge` float NOT NULL,
57 `batterypower` float NOT NULL,
58 `loadpower` float NOT NULL,
59 `solarpower` float NOT NULL,
60 `test` text NOT NULL,
61 `testdescription` text NOT NULL
62 ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
63
|
|
|
|
Post by Powderjockey on Jun 22, 2022 13:16:30 GMT -8
Thanks for the info doxidad. Have a stupid question, how do I get the output of the script? |
|
|
|
Post by Powderjockey on Jun 22, 2022 14:43:22 GMT -8
 ? -- phpMyAdmin SQL Dump
-- TimeStamp fix
alter table AQI433MHZ change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
alter table Generic change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
alter table IndoorTHSensors change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
alter table SolarMax433MHZ change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
alter table TB433MHZ change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
alter table WeatherData change column `TimeStamp` `TimeStamp` timestamp NOT NULL DEFAULT current_timestamp();
-- version 4.9.1
-- https://www.phpmyadmin.net/
--
-- Host: localhost:3306
-- Generation Time: Apr 09, 2021 at 05:18 PM
-- Server version: 10.3.25-MariaDB-0+deb10u1
-- PHP Version: 7.3.19-1~deb10u1
SET SQL_MODE = "NO_AUTO_VALUE_ON_ZERO";
SET AUTOCOMMIT = 0;
START TRANSACTION;
SET time_zone = "+00:00";
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
--
-- Database: `WeatherSenseWireless`
--
-- --------------------------------------------------------
--
-- Table structure for table `AS433MHZ`
--
CREATE TABLE IF NOT EXISTS `AS433MHZ` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
`messageID` int(11) NOT NULL,
`deviceid` int(11) NOT NULL,
`protocolversion` int(11) NOT NULL,
`softwareversion` int(11) NOT NULL,
`weathersenseprotocol` int(11) NOT NULL,
`eqcount` int(11) NOT NULL,
`finaleq_si` float NOT NULL,
`finaleq_pga` float NOT NULL,
`instanteq_si` float NOT NULL,
`instanteq_pga` float NOT NULL,
`batteryvoltage` float NOT NULL,
`batterycurrent` float NOT NULL,
`loadvoltage` float NOT NULL,
`loadcurrent` float NOT NULL,
`solarvoltage` float NOT NULL,
`solarcurrent` float NOT NULL,
`auxa` int(11) NOT NULL,
`solarpresent` int(11) NOT NULL,
`aftershockpresent` int(11) NOT NULL,
`keepalivemessage` int(11) NOT NULL,
`lowbattery` int(11) NOT NULL,
`batterycharge` float NOT NULL,
`batterypower` float NOT NULL,
`loadpower` float NOT NULL,
`solarpower` float NOT NULL,
`test` text NOT NULL,
`testdescription` text NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- --------------------------------------------------------
--
-- Table structure for table `SkyCamPictures`
--
CREATE TABLE IF NOT EXISTS `SkyCamPictures` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
`messageID` int(11) NOT NULL DEFAULT -1,
`cameraID` varchar(10) NOT NULL,
`picturename` varchar(100) NOT NULL,
`picturesize` int(11) NOT NULL,
`resends` int(11) NOT NULL,
`resolution` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
-- --------------------------------------------------------
--
-- Table structure for table `SkyCamSensors`
--
CREATE TABLE IF NOT EXISTS `SkyCamSensors` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`timestamp` timestamp NOT NULL DEFAULT current_timestamp(),
`cameraID` varchar(11) NOT NULL,
`messageID` int(11) NOT NULL,
`softwareversion` int(11) NOT NULL,
`messagetype` int(11) NOT NULL,
`rssi` int(11) NOT NULL,
`internaltemperature` float NOT NULL,
`internalhumidity` int(11) NOT NULL,
`batteryvoltage` float NOT NULL,
`batterycurrent` float NOT NULL,
`loadvoltage` float NOT NULL,
`loadcurrent` float NOT NULL,
`solarvoltage` float NOT NULL,
`solarcurrent` float NOT NULL,
`batterypower` float NOT NULL,
`loadpower` float NOT NULL,
`solarpower` float NOT NULL,
`gndrreboots` int(11) DEFAULT 0,
`batterycharge` float DEFAULT 0,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
-- --------------------------------------------------------
--
-- Table structure for table `RAD433MHZ`
--
CREATE TABLE IF NOT EXISTS `RAD433MHZ` (
`ID` int(11) NOT NULL AUTO_INCREMENT,
`TimeStamp` timestamp NOT NULL DEFAULT current_timestamp(),
`messageID` int(11) NOT NULL,
`deviceid` int(11) NOT NULL,
`protocolversion` int(11) NOT NULL,
`softwareversion` int(11) NOT NULL,
`weathersenseprotocol` int(11) NOT NULL,
`CPM` int(11) NOT NULL,
`nSVh` int(11) NOT NULL,
`uSVh` float NOT NULL,
`uSVh24Hour` float NOT NULL,
`batteryvoltage` float NOT NULL,
`batterycurrent` float NOT NULL,
`loadvoltage` float NOT NULL,
`loadcurrent` float NOT NULL,
`solarvoltage` float NOT NULL,
`solarcurrent` float NOT NULL,
`auxa` int(11) NOT NULL,
`keepalivemessage` int(11) NOT NULL,
`lowbattery` int(11) NOT NULL,
`solarpresent` int(11) NOT NULL,
`radiationpresent` int(11) NOT NULL,
`batterycharge` float NOT NULL,
`batterypower` float NOT NULL,
`loadpower` float NOT NULL,
`solarpower` float NOT NULL,
PRIMARY KEY (`ID`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
COMMIT;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
|
|
|
|
Post by doxidad on Jun 22, 2022 16:48:48 GMT -8
You can use the more command to look at text files (.sql file is a text file).
more WeatherSenseWireless.sql
Will display the file. Use the enter key to move by line or a space bar to move by 1 page at a time. See man more for complete explanation.
If what you posted is the contents of WeatherSenseWireless.sql, the first 6 alter statements will cause an error since they are trying to alter the the columns in the tables that don't exist yet.
|
|
|
|
Post by Powderjockey on Jun 22, 2022 18:11:58 GMT -8
Yes that is the contents of the WeatherSenseWireless.sql.
I downloaded the .zp file from GitHub again it has the same contents as the one I posted.
|
|
|
|
Post by doxidad on Jun 23, 2022 4:21:36 GMT -8
I just looked and yep the alter statements are in the current repository.
They shouldn't be there in that spot for tables that don't exist yet.
I have an older version without the alter statements so couldn't figure out why they are present.
If you want to use that script, comment out the alter statements with '--' and run it again and it should work.
edit it with nano
nano WeatherSenseWireless.sql
Hey BP!! This is a problem for those that are trying to create tables using this script!! (WeatherSenseWireless.sql)
|
|
|
|
Post by Powderjockey on Jun 23, 2022 9:22:48 GMT -8
I just looked and yep the alter statements are in the current repository.
They shouldn't be there in that spot for tables that don't exist yet.
I have an older version without the alter statements so couldn't figure out why they are present.
If you want to use that script, comment out the alter statements with '--' and run it again and it should work.
edit it with nano
nano WeatherSenseWireless.sql
Hey BP!! This is a problem for those that are trying to create tables using this script!! (WeatherSenseWireless.sql)
I tried that and I received an error about line 41. This is the line where it says CREATE......... I'll try it again. |
|
Sopwith
Junior Member
"If it works out of the box - what fun is that?"
Posts: 69
Raspberry Pi: Yes
Other Device: Pico Pi
|
Post by Sopwith on Jun 23, 2022 10:05:00 GMT -8
Is this a new installation or an upgrade from an existing installation? The reason I ask - we had the same problem with the WeatherSense2 scripts. I had to create a new script that creates the two databases and creates all the base tables. Then updated those databases with the update scripts.
This only applies for users that are trying to install the SkyWeather software themselves, without the SDCard image provided by SDL.
I may be talking out of turn here so consider this advice free and potentially useless.
Let me know if you need the script and I will post it here.
Sopwith
|
|
|
|
Post by Powderjockey on Jun 23, 2022 12:19:14 GMT -8
This is a new install. I just installed SolarMax2 to work with SkyWeather1. I'm try to get the readings for the SolarMax2 so that I can monitor the battery etc. Perhaps I'm going about this all wrong.
If you can post the script I will give it a go and see where I get.
Thanks
Scott
|
|
|
|
Post by Powderjockey on Jun 23, 2022 12:48:18 GMT -8
The two sql files in the SDL_Pi_WeatherSense.zip appear to be exactly the same.
|
|
|
|
Post by SDL on Jun 24, 2022 7:43:10 GMT -8
Let me ask a couple of questions.
1) Is this on the SDL Card? Or on your image?
2) is this the first time you have tried to install SkyWeather2 OR WeatherSense on your image?
I'm thinking we might have a problem with the WeatherSense scripts on a completely clean install.
Sopwith? Did you try that?
BP
|
|
|
|
Post by Powderjockey on Jun 24, 2022 10:10:03 GMT -8
Let me ask a couple of questions. 1) Is this on the SDL Card? Or on your image? 2) is this the first time you have tried to install SkyWeather2 OR WeatherSense on your image? I'm thinking we might have a problem with the WeatherSense scripts on a completely clean install. Sopwith? Did you try that? BP 1) This is my own image with SkyWeather (1) One working. Trying to add SolarMax2 to the system. 2) I downloaded the WeatherSense.zip from GitHub and unzipped it. Then tried to follow the directions on GitHub to get it started. I've tried to comment out the lines 5 thru 10, no luck. Error at line 41, can't create the AS433MHZ. Also tried to add CREATE DATABASE WeatherSense; to the script which also failed. |
|
|
|
Post by Powderjockey on Jun 24, 2022 15:13:22 GMT -8
Played with some more. Removed the lines 5 thru 10 from WeatherSenseWireless.sql. I created a blank database in mysql called WeatherSenseWireless. Then I used the following command.
sudo mysql -u root -p WeatherSenseWireless < WeatherSenseWireless.sql
No errors were encountered. I ran the show tables; in the database and had 4 tables.
MariaDB [WeatherSenseWireless]> show tables;
+--------------------------------+
| Tables_in_WeatherSenseWireless |
+--------------------------------+
| AS433MHZ |
| RAD433MHZ |
| SkyCamPictures |
| SkyCamSensors |
+--------------------------------+
4 rows in set (0.001 sec)
|
|
|
|
Post by doxidad on Jun 24, 2022 16:34:13 GMT -8
Your making progress. I would assume that you would at least need SolarMax433MHZ
If I do show tables on my WeatherSenseWireless I get the following
MariaDB [WeatherSenseWireless]> show tables;
+--------------------------------+
| Tables_in_WeatherSenseWireless |
+--------------------------------+
| AQI433MHZ |
| AS433MHZ |
| Generic |
| IndoorTHSensors |
| RAD433MHZ |
| SkyCamPictures |
| SkyCamSensors |
| SolarMax433MHZ |
| TB433MHZ |
| WeatherData |
+--------------------------------+
10 rows in set (0.001 sec)
|
|
|
|
Post by Powderjockey on Jun 24, 2022 17:58:14 GMT -8
Looking at the GitHub download, there is no part of the script that will create the rest of the tables. As I stated earlier, both of the .sql files in the download are the same.
There has to be a problem with the files on GitHub.
|
|
?
