Closed StarScream159 closed 1 year ago
ziebelje
commented
1 year ago In request.php:293 (the log() function) you can try updating it to just return at the top of the function. There's some other error happening that is getting logged and this particular path is apparently bugged. Doing this will at least sneak you past the first error while I look into it.
private function log() {
return;
...
}Additionally could you run this query on your database server and post the results?
select
`TABLE_NAME`,
`COLUMN_NAME`,
`DATA_TYPE`
from
`information_schema`.`columns`
where
`table_schema` = 'beestat'
and `table_name` = 'api_log'
;
StarScream159
commented
1 year ago Thanks for the prompt reply and possible work-around.
Unfortunately it is still failing. However, now all requests are 200 and nothing in the nginx error log.
I am getting a larger output within the beestat website now:
Results from your query:
| TABLE_NAME | COLUMN_NAME | DATA_TYPE |
|---|---|---|
| api_log | api_log_id | int |
| api_log | user_id | int |
| api_log | api_user_id | int |
| api_log | ip_address | int |
| api_log | timestamp | timestamp |
| api_log | request | longtext |
| api_log | response | longtext |
| api_log | error_code | int |
| api_log | error_detail | longtext |
| api_log | total_time | decimal |
| api_log | query_count | int |
| api_log | query_time | decimal |
ziebelje
commented
1 year ago Got 'em.
The database wrapper in beestat allows you to do things like $database->create($table, $attributes) where $attributes is an array where the keys are column names and the values are the data to go into the columns. Certain columns are the "json" data type, and when that happens beestat automatically encodes/decodes the JSON for you.
What's happening for you is the JSON columns are "longtext" instead of "json". Beestat actually runs the query I gave you to determine the column data type. Since it's not the expected result it doesn't encode the object and then it breaks when it goes to escape the alleged string.
Here's what you should expect to see from that query:
+------------+--------------+-----------+
| TABLE_NAME | COLUMN_NAME | DATA_TYPE |
+------------+--------------+-----------+
| api_log | api_log_id | int |
| api_log | api_user_id | int |
| api_log | error_code | int |
| api_log | error_detail | json |
| api_log | ip_address | int |
| api_log | query_count | int |
| api_log | query_time | decimal |
| api_log | request | json |
| api_log | response | json |
| api_log | timestamp | timestamp |
| api_log | total_time | decimal |
| api_log | user_id | int |
+------------+--------------+-----------+MariaDB behaves different than MySQL with the JSON data type: https://mariadb.com/kb/en/json-data-type/
Let me see if I can come up with a workaround.
ziebelje
commented
1 year ago Could you do one more query? I need to see if MariaDB internally marks these as JSON columns even though they are stored as longtext.
select
*
from
`information_schema`.`columns`
where
`table_schema` = 'beestat'
and `table_name` = 'api_log'
and `column_name` = 'request'
;*************************** 1. row ***************************
TABLE_CATALOG: def
TABLE_SCHEMA: beestat
TABLE_NAME: api_log
COLUMN_NAME: request
ORDINAL_POSITION: 6
COLUMN_DEFAULT: NULL
IS_NULLABLE: YES
DATA_TYPE: json
CHARACTER_MAXIMUM_LENGTH: NULL
CHARACTER_OCTET_LENGTH: NULL
NUMERIC_PRECISION: NULL
NUMERIC_SCALE: NULL
DATETIME_PRECISION: NULL
CHARACTER_SET_NAME: NULL
COLLATION_NAME: NULL
COLUMN_TYPE: json
COLUMN_KEY:
EXTRA:
PRIVILEGES: select,insert,update,references
COLUMN_COMMENT:
GENERATION_EXPRESSION:
SRS_ID: NULLAh. Good catch. Here's the output of your query (sorry don't know how to format it the same way you do).
$columns = array(
array(
"TABLE_CATALOG" => "def",
"TABLE_SCHEMA" => "beestat",
"TABLE_NAME" => "api_log",
"COLUMN_NAME" => "request",
"ORDINAL_POSITION" => 6,
"COLUMN_DEFAULT" => "NULL",
"IS_NULLABLE" => "YES",
"DATA_TYPE" => "longtext",
"CHARACTER_MAXIMUM_LENGTH" => 4294967295,
"CHARACTER_OCTET_LENGTH" => 4294967295,
"NUMERIC_PRECISION" => NULL,
"NUMERIC_SCALE" => NULL,
"DATETIME_PRECISION" => NULL,
"CHARACTER_SET_NAME" => "utf8mb4",
"COLLATION_NAME" => "utf8mb4_bin",
"COLUMN_TYPE" => "longtext",
"COLUMN_KEY" => "",
"EXTRA" => "",
"PRIVILEGES" => "select,insert,update,references",
"COLUMN_COMMENT" => "",
"IS_GENERATED" => "NEVER",
"GENERATION_EXPRESSION" => NULL,
),
);Looks like longtext. I don't think json column type exists outside of mysql.
ziebelje
commented
1 year ago I'm not seeing any trivial way to identify this column type in MariaDB once it's been created. I think the best quick solution is to add this line of code on database.php:840. It will assume all "longtext" columns are "json", unless the column is named "response", except if the table is "api_log"...so basically just converts only the columns that need to be converted without explicitly listing them all out.
$row['DATA_TYPE'] = $row['DATA_TYPE'] == 'longtext' ? ($row['COLUMN_NAME'] == 'response' && $row['TABLE_NAME'] !== 'api_log' ? $row['DATA_TYPE'] : 'json') : $row['DATA_TYPE'];
In context:
// Otherwise query the entire schema (and cache it) to see what the type is.
if(isset(self::$types) === false) {
self::$types = [];
$result = $this->query('
select
`TABLE_NAME`,
`COLUMN_NAME`,
`DATA_TYPE`
from
`information_schema`.`columns`
where
`table_schema` = ' . $this->escape($this->setting->get('database_name')) . '
');
while($row = $result->fetch_assoc()) {
$row['DATA_TYPE'] = $row['DATA_TYPE'] == 'longtext' ? ($row['COLUMN_NAME'] == 'response' && $row['TABLE_NAME'] !== 'api_log' ? $row['DATA_TYPE'] : 'json') : $row['DATA_TYPE'];
self::$types[$row['TABLE_NAME'] . '.' . $row['COLUMN_NAME']] = $row['DATA_TYPE'];
}
}Thanks. Quick work around, however it looks like this JSON issues causes errors elsewhere too. After implementing that change it fails on a new line:
"error_message": "Cannot access offset of type string on string",
"error_code": 0,
"error_detail": {
"file": "/opt/beestat/api/ecobee_thermostat.php",
"line": 1152,That line is a json string, I assume from the database. Nested JSON I'm guessing? Because it isn't auto parsed, it's being returned as a long string (i.e. $ecobee_thermostat['location'] is a string):
["location"]=>
string(250) "{"timeZoneOffsetMinutes":-300,"timeZone":"America\/Toronto","isDaylight"...I fixed it with a simple check:
if (!is_array($ecobee_thermostat['location'])) {
$ecobee_thermostat['location'] = json_decode($ecobee_thermostat['location'], true);
}
$time_zone = $ecobee_thermostat['location']['timeZone'];That got me past that one but then there was another one:
"error_message": "foreach() argument must be of type array|object, string given",
"error_code": 2,
"error_detail": {
"file": "/opt/beestat/api/ecobee_thermostat.php",
"line": 786,And I bet there are more and more. Seems as though this just won't run easily on MariaDB.
ziebelje
commented
1 year ago We missed a spot. Looks like I'm analyzing type two different ways as a slight optimization for read-only API calls.
On database.php:496 add the following line of code. I confirmed this fixes the latest issue you're having...as long as MariaDB doesn't return different codes for other field types this should fix it. If not it can be fixed other ways. :)
$field_info->type = $field_info->type == 252 ? ($field_info->name == 'response' && $field_info->table !== 'api_log' ? $field_info->type : 245) : $field_info->type;
In context:
$float_fields = [];
$boolean_fields = [];
$json_fields = [];
while($field_info = $result->fetch_field()) {
$field_info->type = $field_info->type == 252 ? ($field_info->name == 'response' && $field_info->table !== 'api_log' ? $field_info->type : 245) : $field_info->type;
if($field_info->type === 1 && $field_info->length === 1) {
$boolean_fields[] = $field_info->name;
}
else if($field_info->type === 246) {
$float_fields[] = $field_info->name;
}
else if($field_info->type === 245) {
$json_fields[] = $field_info->name;
}
}Thanks. That got us closer. No more error messages from the server. However now there is an error in the console.
load.js:161 Uncaught TypeError: Cannot read properties of undefined (reading 'thermostat_id')
at beestat.api.callback_ (load.js:161:46)
at beestat.api.load_ (api.js:265:10)
at XMLHttpRequest.<anonymous> (api.js:32:12)To confirm nothing was messed up from everything we've done. I cleared the database and re-initialized it, re-created the two api user accounts and set their keys in the settings file. Lastly I checked out the files that were changed and reapplied the two fixes to the database.php file.
So no errors reported from any xhr requests or nginx. But getting this console error.
// Set the active thermostat_id if this is your first time visiting.
if (beestat.setting('thermostat_id') === undefined) {
beestat.setting(
'thermostat_id',
$.values(beestat.cache.thermostat)[0].thermostat_id // error here
);
}Thanks for all the help.
StarScream159
commented
1 year ago I decided to redo a new VM with standard mysql: mysql Ver 8.0.30 for Linux on x86_64 (Source distribution)
I still have the old VM if you want me to test anything relating to MariaDB.
ziebelje
commented
1 year ago Closing as not planned for now. If someone wants to create a MariaDB fork or something go for it. I've noted this discussion in the self-hosting guide.
Describe the bug Getting an error while I sign in for the first time on a new self-hosted setup:
Version Information here: nginx version: nginx/1.20.1 mysql Ver 15.1 Distrib 10.5.16-MariaDB, for Linux (x86_64) using EditLine wrapper PHP Version 8.1.16
To Reproduce
Other Information I var_dumped the line that is producing the error. I got a bunch of strings until it hit this array:
Thanks so much.