antonioribeiro / antonioribeiro/tracker

DB Error - select '1' as row from `tracker_sessions`

Open
#460 1 comment 0 reactions 0 assignees View on GitHub
Dominant language
PHP
Stars
2.9k
Forks
594
PR merge metrics
No merged PRs in 30d

Description

After following all steps in the installation instructions. Laravel Log shows: user_agent column not exists. After create the column with migration. Now it show another error.

In browser shows Alert error:
`laravel stats tracker DataTables warning: table id=table_div - Ajax error`

Laravel Log show this error:
```
local.ERROR: SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row from `tracker_sessions` where `updated_at` >= ? and `updated_at` <= ?) AS co' at line 1 (SQL: select count(*) as aggregate from (select '1' as row from `tracker_sessions` where `updated_at` >= 2019-09-07 04:36:23 and `updated_at` <= 2019-09-08 04:36:23) AS count_row_table) {"userId":1,"exception":"[object] (Illuminate\\Database\\QueryException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row from `tracker_sessions` where `updated_at` >= ? and `updated_at` <= ?) AS co' at line 1 (SQL: select count(*) as aggregate from (select '1' as row from `tracker_sessions` where `updated_at` >= 2019-09-07 04:36:23 and `updated_at` <= 2019-09-08 04:36:23) AS count_row_table) at /home/markkhor/public_html/sms.360.my/vendor/laravel/framework/src/Illuminate/Database/Connection.php:664, Doctrine\\DBAL\\Driver\\PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row from `tracker_sessions` where `updated_at` >= ? and `updated_at` <= ?) AS co' at line 1 at /home/markkhor/public_html/sms.360.my/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:63, PDOException(code: 42000): SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'row from `tracker_sessions` where `updated_at` >= ? and `updated_at` <= ?) AS co' at line 1 at /home/markkhor/public_html/sms.360.my/vendor/doctrine/dbal/lib/Doctrine/DBAL/Driver/PDOConnection.php:61)
[stacktrace]
#0 /home/markkhor/public_html/sms.360.my/vendor/laravel/framework/src/Illuminate/Database/Connection.php(624): Illuminate\\Database\\Connection->runQueryCallback('select count(*)...', Array, Object(Closure))
```

I commented following line in /vendor/pragmarx/datatables/src/Bllim/Databases/Datatables.php:
`$countQuery->select(DB::raw("'1' as row"));`

Now can access every page in 'stats', but only Visits & Page View Summary is working.

**System**
- OS: MacOS
- Browser: Chrome
- Version 7.6

**Additional context**
Add any other context about the problem here.

PHP version 7.2
Laravel version 5.8
MySQL version 8.0

Contributor guide

No contributing guide indexed for this repository

Research direction

Start with the failing stats pages and the SQL error in vendor/pragmarx/datatables/src/Bllim/Databases/Datatables.php, especially the count query using DB::raw("'1' as row"). Reproduce with PHP 7.2, Laravel 5.8, and MySQL 8.0, then verify that all stats pages load without the SQL syntax error and that the affected DataTables responses work.

Written by the indexing model from the issue text.

Assessment

Tech stack
laravel, mysql, php
Domain
backend, database
Issue type
Bug
Difficulty
3/5
Estimated time
1-2 days
Activity status
Stale
Clarity
Mostly clear
Newbie friendliness
38/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.