Database

MySQL Versions and Installers

For compatibility with Camile PC, we should use server 8.0.*

LTS and EOL Listing

v8.0.25 32 bit ODBC download

MySQL Installer 8.0.41 with Server & Workbench

MySQL Version Notes

• MySQL Versions
  • MySQL 8.0.x is LTS, but ending this year.
  • MySQL 8.4 is the most recent LTS, with support ending in 4 years.
  • MySQL 9.7 will be the next LTS, but is not out yet.
  • MySQL 9.1 is the most recent version, but is not LTS.
• MySQL Workbench is deprecated and will reach EOL the same time as MySQL 8.0.x
• Workbench MAY support MySQL server > 8.0, but it's not guaranteed to be supported.
• MySQL Installer (vs MySQL Server) is tied to <= 8.0.41 and likely includes MySQL Workbench.
• Mysql shell for VS Code is the replacement for MySQL Workbench, but people (Reddit) don't like it.

Which Database is the correct one

From: Safwat, Amr amr.safwat@aptim.com Sent: Friday, February 7, 2025 11:57 AM To: Sudhir K sudhir@gqc.com Subject: Re: MySQL DB Question

Hi Sudhir,

I am not the one who setup any of the original databases. I just helped in the past to query the database and helped retrieve the missing data from the broken database.

Camile2015 is the old database that has been rebuilt from files retrieved from the old failed hard drive. The files have been used to recreate the Camile2015 schema. Camile2024 is the new working database which has been updated and is used to record measurements from CamileTG.

Please, make sure that you have a copy of the correct database with data from last year. The other databases where there and I think someone set them up, but I don’t think they are being updated or maintained. I would still make sure to keep a copy of them.

As for the error I am not sure maybe do a quick google search. Check or update the Definer in the database.

SELECT * FROM mysql.proc WHERE definer LIKE 'camile@%';

SELECT * FROM information_schema.views WHERE definer LIKE 'camile@%';

SELECT * FROM information_schema.triggers WHERE definer LIKE 'camile@%';

You could also just export the database without the definer something like this.

mysqldump --skip-triggers --no-data --skip-definer -u root -p database_name > backup.sql

Thanks,

Amr

2024 Schema

CREATE DATABASE  IF NOT EXISTS `camile2024` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `camile2024`;
-- MySQL dump 10.13  Distrib 8.0.36, for Win64 (x86_64)
--

--
-- Table structure for table `edmr_801`
--

DROP TABLE IF EXISTS `edmr_801`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `edmr_801` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `riverwater_flow_gpm` float DEFAULT NULL,
  `riverwater_pH` float DEFAULT NULL,
  PRIMARY KEY (`datetime`),
  KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream1`
--

DROP TABLE IF EXISTS `riverwater`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `riverwater` (
  `riverwater_id` int(11) NOT NULL AUTO_INCREMENT,
  `riverwater_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `riverwater_ph` float DEFAULT NULL,
  `riverwater_conductivity_micros_cm` float DEFAULT NULL,
  `riverwater_diss_oxygen_mg_l` float DEFAULT NULL,
  `riverwater_turbidity_ntu` float DEFAULT NULL,
  `riverwater_temparature_c` float DEFAULT NULL,
  `riverwater_orp_mv` float DEFAULT NULL,
  `riverwater_flow_gpm` float DEFAULT NULL,
  `tapwater_flow_gpm` float DEFAULT NULL,
  PRIMARY KEY (`riverwater_datetime`),
  KEY `riverwater_id` (`riverwater_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Table structure for table `stream1`
--

DROP TABLE IF EXISTS `stream1`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream1` (
    `Stream1_id` int(11) NOT NULL AUTO_INCREMENT,
    `Stream1_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
    `Stream1_flow1_gpm` float DEFAULT NULL,
    `Stream1_flow2_gpm` float DEFAULT NULL,
    `Stream1_Recirflow1_gpm` float DEFAULT NULL,
    `Stream1_Recirflow2_gpm` float DEFAULT NULL,
    `Stream1_1_ph` float DEFAULT NULL,
    `Stream1_1_conductivity_micros_cm` float DEFAULT NULL,
    `Stream1_1_diss_oxygen_mg_l` float DEFAULT NULL,
    `Stream1_1_turbidity_ntu` float DEFAULT NULL,
    `Stream1_1_temparature_c` float DEFAULT NULL,
    `Stream1_1_orp_mv` float DEFAULT NULL,
    `Stream1_2_ph` float DEFAULT NULL,
    `Stream1_2_conductivity_micros_cm` float DEFAULT NULL,
    `Stream1_2_diss_oxygen_mg_l` float DEFAULT NULL,
    `Stream1_2_turbidity_ntu` float DEFAULT NULL,
    `Stream1_2_temparature_c` float DEFAULT NULL,
    `Stream1_2_orp_mv` float DEFAULT NULL,
    PRIMARY KEY (`Stream1_datetime`),
    KEY `Stream1_id` (`Stream1_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream2`
--

DROP TABLE IF EXISTS `stream2`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream2` (
  `Stream2_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream2_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream2_flow1_gpm` float DEFAULT NULL,
  `Stream2_flow2_gpm` float DEFAULT NULL,
  `Stream2_Recirflow1_gpm` float DEFAULT NULL,
  `Stream2_Recirflow2_gpm` float DEFAULT NULL,
  `Stream2_1_ph` float DEFAULT NULL,
  `Stream2_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream2_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream2_1_turbidity_ntu` float DEFAULT NULL,
  `Stream2_1_temparature_c` float DEFAULT NULL,
  `Stream2_1_orp_mv` float DEFAULT NULL,
  `Stream2_2_ph` float DEFAULT NULL,
  `Stream2_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream2_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream2_2_turbidity_ntu` float DEFAULT NULL,
  `Stream2_2_temparature_c` float DEFAULT NULL,
  `Stream2_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream2_datetime`),
  KEY `Stream2_id` (`Stream2_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream3`
--

DROP TABLE IF EXISTS `stream3`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream3` (
  `Stream3_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream3_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream3_flow1_gpm` float DEFAULT NULL,
  `Stream3_flow2_gpm` float DEFAULT NULL,
  `Stream3_Recirflow1_gpm` float DEFAULT NULL,
  `Stream3_Recirflow2_gpm` float DEFAULT NULL,
  `Stream3_1_ph` float DEFAULT NULL,
  `Stream3_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream3_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream3_1_turbidity_ntu` float DEFAULT NULL,
  `Stream3_1_temparature_c` float DEFAULT NULL,
  `Stream3_1_orp_mv` float DEFAULT NULL,
  `Stream3_2_ph` float DEFAULT NULL,
  `Stream3_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream3_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream3_2_turbidity_ntu` float DEFAULT NULL,
  `Stream3_2_temparature_c` float DEFAULT NULL,
  `Stream3_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream3_datetime`),
  KEY `Stream3_id` (`Stream3_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream4`
--

DROP TABLE IF EXISTS `stream4`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream4` (
  `Stream4_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream4_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream4_flow1_gpm` float DEFAULT NULL,
  `Stream4_flow2_gpm` float DEFAULT NULL,
  `Stream4_Recirflow1_gpm` float DEFAULT NULL,
  `Stream4_Recirflow2_gpm` float DEFAULT NULL,
  `Stream4_1_ph` float DEFAULT NULL,
  `Stream4_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream4_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream4_1_turbidity_ntu` float DEFAULT NULL,
  `Stream4_1_temparature_c` float DEFAULT NULL,
  `Stream4_1_orp_mv` float DEFAULT NULL,
  `Stream4_2_ph` float DEFAULT NULL,
  `Stream4_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream4_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream4_2_turbidity_ntu` float DEFAULT NULL,
  `Stream4_2_temparature_c` float DEFAULT NULL,
  `Stream4_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream4_datetime`),
  KEY `Stream4_id` (`Stream4_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream5`
--

DROP TABLE IF EXISTS `stream5`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream5` (
  `Stream5_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream5_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream5_flow1_gpm` float DEFAULT NULL,
  `Stream5_flow2_gpm` float DEFAULT NULL,
  `Stream5_Recirflow1_gpm` float DEFAULT NULL,
  `Stream5_Recirflow2_gpm` float DEFAULT NULL,
  `Stream5_1_ph` float DEFAULT NULL,
  `Stream5_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream5_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream5_1_turbidity_ntu` float DEFAULT NULL,
  `Stream5_1_temparature_c` float DEFAULT NULL,
  `Stream5_1_orp_mv` float DEFAULT NULL,
  `Stream5_2_ph` float DEFAULT NULL,
  `Stream5_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream5_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream5_2_turbidity_ntu` float DEFAULT NULL,
  `Stream5_2_temparature_c` float DEFAULT NULL,
  `Stream5_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream5_datetime`),
  KEY `Stream5_id` (`Stream5_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream6`
--

DROP TABLE IF EXISTS `stream6`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream6` (
  `Stream6_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream6_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream6_flow1_gpm` float DEFAULT NULL,
  `Stream6_flow2_gpm` float DEFAULT NULL,
  `Stream6_Recirflow1_gpm` float DEFAULT NULL,
  `Stream6_Recirflow2_gpm` float DEFAULT NULL,
  `Stream6_1_ph` float DEFAULT NULL,
  `Stream6_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream6_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream6_1_turbidity_ntu` float DEFAULT NULL,
  `Stream6_1_temparature_c` float DEFAULT NULL,
  `Stream6_1_orp_mv` float DEFAULT NULL,
  `Stream6_2_ph` float DEFAULT NULL,
  `Stream6_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream6_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream6_2_turbidity_ntu` float DEFAULT NULL,
  `Stream6_2_temparature_c` float DEFAULT NULL,
  `Stream6_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream6_datetime`),
  KEY `Stream6_id` (`Stream6_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream7`
--

DROP TABLE IF EXISTS `stream7`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream7` (
  `Stream7_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream7_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream7_flow1_gpm` float DEFAULT NULL,
  `Stream7_flow2_gpm` float DEFAULT NULL,
  `Stream7_Recirflow1_gpm` float DEFAULT NULL,
  `Stream7_Recirflow2_gpm` float DEFAULT NULL,
  `Stream7_1_ph` float DEFAULT NULL,
  `Stream7_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream7_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream7_1_turbidity_ntu` float DEFAULT NULL,
  `Stream7_1_temparature_c` float DEFAULT NULL,
  `Stream7_1_orp_mv` float DEFAULT NULL,
  `Stream7_2_ph` float DEFAULT NULL,
  `Stream7_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream7_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream7_2_turbidity_ntu` float DEFAULT NULL,
  `Stream7_2_temparature_c` float DEFAULT NULL,
  `Stream7_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream7_datetime`),
  KEY `Stream7_id` (`Stream7_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `stream8`
--

DROP TABLE IF EXISTS `stream8`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `stream8` (
  `Stream8_id` int(11) NOT NULL AUTO_INCREMENT,
  `Stream8_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream8_flow1_gpm` float DEFAULT NULL,
  `Stream8_flow2_gpm` float DEFAULT NULL,
  `Stream8_Recirflow1_gpm` float DEFAULT NULL,
  `Stream8_Recirflow2_gpm` float DEFAULT NULL,
  `Stream8_1_ph` float DEFAULT NULL,
  `Stream8_1_conductivity_micros_cm` float DEFAULT NULL,
  `Stream8_1_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream8_1_turbidity_ntu` float DEFAULT NULL,
  `Stream8_1_temparature_c` float DEFAULT NULL,
  `Stream8_1_orp_mv` float DEFAULT NULL,
  `Stream8_2_ph` float DEFAULT NULL,
  `Stream8_2_conductivity_micros_cm` float DEFAULT NULL,
  `Stream8_2_diss_oxygen_mg_l` float DEFAULT NULL,
  `Stream8_2_turbidity_ntu` float DEFAULT NULL,
  `Stream8_2_temparature_c` float DEFAULT NULL,
  `Stream8_2_orp_mv` float DEFAULT NULL,
  PRIMARY KEY (`Stream8_datetime`),
  KEY `Stream8_id` (`Stream8_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `weather`
-- Tablename will be renamed to include building in the name 05/08/2024
--

DROP TABLE IF EXISTS `weather_building`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `weather_building` (
  `weather_id` int(11) NOT NULL AUTO_INCREMENT,
  `weather_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `weather_outside_temp_f` float DEFAULT NULL,
  `weather_outside_humidity_percent` float DEFAULT NULL,
  `weather_outside_light_meter` float DEFAULT NULL,
  `weather_barometric_pressure_psi` float DEFAULT NULL,
  `weather_precipitation_counter` float DEFAULT NULL,
  `building_temp_f` float DEFAULT NULL,
  `building_humidity_percent` float DEFAULT NULL,
  `streams_light_meter_1` float DEFAULT NULL,
  `streams_light_meter_2` float DEFAULT NULL,
  `streams_light_meter_3` float DEFAULT NULL,
  PRIMARY KEY (`weather_datetime`),
  KEY `weather_id` (`weather_id`)
) ENGINE=InnoDB AUTO_INCREMENT=955836 DEFAULT CHARSET=utf8;

--
-- Table structure for table `edmr`
--

DROP TABLE IF EXISTS `edmr`;

DROP TABLE IF EXISTS `edmr_outfall`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `edmr_outfall` (
  `idedmr_outfall` int(11) NOT NULL AUTO_INCREMENT,
  `edmr_outfall_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `edmr_outfall_pH` float DEFAULT NULL,
  `edmr_outfall_DO_mg_per_l` float DEFAULT NULL,
  `edmr_outfall_conductivity_microS_per_cm` float DEFAULT NULL,
  `edmr_outfall_temperature_deg_C` float DEFAULT NULL,
  PRIMARY KEY (`edmr_outfall_datetime`),
  KEY `idedmr_outfall` (`idedmr_outfall`)
) ENGINE=InnoDB AUTO_INCREMENT=7253697 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `edmr_801`
--

DROP TABLE IF EXISTS `nitrate`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `nitrate` (
  `idnitrate` int(11) NOT NULL AUTO_INCREMENT,
  `date_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `Stream1_1_Nitrate` float DEFAULT NULL,
  `Stream1_2_Nitrate` float DEFAULT NULL,
  `Stream2_1_Nitrate` float DEFAULT NULL,
  `Stream2_2_Nitrate` float DEFAULT NULL,
  `Stream3_1_Nitrate` float DEFAULT NULL,
  `Stream3_2_Nitrate` float DEFAULT NULL,
  `Stream4_1_Nitrate` float DEFAULT NULL,
  `Stream4_2_Nitrate` float DEFAULT NULL,
  `Stream5_1_Nitrate` float DEFAULT NULL,
  `Stream5_2_Nitrate` float DEFAULT NULL,
  `Stream6_1_Nitrate` float DEFAULT NULL,
  `Stream6_2_Nitrate` float DEFAULT NULL,
  `Stream7_1_Nitrate` float DEFAULT NULL,
  `Stream7_2_Nitrate` float DEFAULT NULL,
  `Stream8_1_Nitrate` float DEFAULT NULL,
  `Stream8_2_Nitrate` float DEFAULT NULL,
  PRIMARY KEY (`date_time`),
  KEY `idnitrate` (`idnitrate`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Table structure for table `ro`
--

DROP TABLE IF EXISTS `ro`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `ro` (
  `ro_id` int(11) NOT NULL AUTO_INCREMENT,
  `ro_datetime` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `ro_drain_flow_gpm` float DEFAULT NULL,
  `ro_conductivity_micros_cm` float DEFAULT NULL,
  `ro_feedwater_flow_gpm` float DEFAULT NULL,
  `ro_tapwater_flow_gpm` float DEFAULT NULL,
  `ro_product_flow_gpm` float DEFAULT NULL,
  PRIMARY KEY (`ro_datetime`),
  KEY `ro_id` (`ro_id`)
) ENGINE=InnoDB AUTO_INCREMENT=3635859 DEFAULT CHARSET=utf8;