Type "timestamp_on_update" are not supported

Schema & Data
1

I have defined a column with type “timestamp_on_update”, it will be automatically filled by system when someone modifies the record. It worked perfectly in DF2.0-beta2, but now it will show the following error in DF2.0-beta4. By the way, it does not work either in online sandbox.

{
“error”: {
“context”: null,
“message”: “Failed to create records in ‘buddyface’.\nCommand failed to prepare the SQL statement: SQLSTATE[HY000]: General error: 1 no such function: NOW”,
“code”: 500,
“trace”: [
“0 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Resources/BaseDbTableResource.php(514): DreamFactory\Core\Resources\BaseDbTableResource->createRecords(‘buddyface’, Array, Array)”,
“1 [internal function]: DreamFactory\Core\Resources\BaseDbTableResource->handlePost()”,
“2 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Components/RestHandler.php(271): call_user_func(Array)”,
“3 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Components/RestHandler.php(177): DreamFactory\Core\Components\RestHandler->processRequest()”,
“4 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Components/RestHandler.php(224): DreamFactory\Core\Components\RestHandler->handleRequest(Object(DreamFactory\Core\Utility\ServiceRequest), ‘buddyface’)”,
“5 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Services/BaseDbService.php(116): DreamFactory\Core\Components\RestHandler->handleResource(Array)”,
“6 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Components/RestHandler.php(172): DreamFactory\Core\Services\BaseDbService->handleResource(Array)”,
“7 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Services/BaseRestService.php(65): DreamFactory\Core\Components\RestHandler->handleRequest(Object(DreamFactory\Core\Utility\ServiceRequest), ‘_table/buddyfac…’)”,
“8 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/dreamfactory/df-core/src/Utility/ServiceHandler.php(57): DreamFactory\Core\Services\BaseRestService->handleRequest(Object(DreamFactory\Core\Utility\ServiceRequest), ‘_table/buddyfac…’)”,
“9 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Controllers/RestController.php(223): DreamFactory\Core\Utility\ServiceHandler::processRequest(‘v2’, ‘db’, ‘_table/buddyfac…’)”,
“10 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Controllers/RestController.php(154): DreamFactory\Http\Controllers\RestController->handleService(‘v2’, ‘db’, ‘_table/buddyfac…’)”,
“11 [internal function]: DreamFactory\Http\Controllers\RestController->handlePOST(‘v2’, ‘db’, ‘_table/buddyfac…’)”,
“12 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Controller.php(256): call_user_func_array(Array, Array)”,
“13 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(164): Illuminate\Routing\Controller->callAction(‘handlePOST’, Array)”,
“14 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(112): Illuminate\Routing\ControllerDispatcher->call(Object(DreamFactory\Http\Controllers\RestController), Object(Illuminate\Routing\Route), ‘handlePOST’)”,
“15 [internal function]: Illuminate\Routing\ControllerDispatcher->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))”,
“16 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(139): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“17 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Middleware/DataCollection.php(32): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“18 [internal function]: Dreamfactory\Http\Middleware\DataCollection->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“19 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“20 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Middleware/Limits.php(127): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“21 [internal function]: Dreamfactory\Http\Middleware\Limits->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“22 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“23 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Middleware/AccessCheck.php(233): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“24 [internal function]: DreamFactory\Http\Middleware\AccessCheck->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“25 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“26 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“27 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“28 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(114): Illuminate\Pipeline\Pipeline->then(Object(Closure))”,
“29 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/ControllerDispatcher.php(69): Illuminate\Routing\ControllerDispatcher->callWithinStack(Object(DreamFactory\Http\Controllers\RestController), Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), ‘handlePOST’)”,
“30 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Route.php(203): Illuminate\Routing\ControllerDispatcher->dispatch(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request), ‘DreamFactory\Ht…’, ‘handlePOST’)”,
“31 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Route.php(134): Illuminate\Routing\Route->runWithCustomDispatcher(Object(Illuminate\Http\Request))”,
“32 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Router.php(704): Illuminate\Routing\Route->run(Object(Illuminate\Http\Request))”,
“33 [internal function]: Illuminate\Routing\Router->Illuminate\Routing\{closure}(Object(Illuminate\Http\Request))”,
“34 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(139): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“35 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“36 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“37 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Router.php(706): Illuminate\Pipeline\Pipeline->then(Object(Closure))”,
“38 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Router.php(671): Illuminate\Routing\Router->runRouteWithinStack(Object(Illuminate\Routing\Route), Object(Illuminate\Http\Request))”,
“39 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Routing/Router.php(631): Illuminate\Routing\Router->dispatchToRoute(Object(Illuminate\Http\Request))”,
“40 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(236): Illuminate\Routing\Router->dispatch(Object(Illuminate\Http\Request))”,
“41 [internal function]: Illuminate\Foundation\Http\Kernel->Illuminate\Foundation\Http\{closure}(Object(Illuminate\Http\Request))”,
“42 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(139): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“43 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/barryvdh/laravel-cors/src/HandleCors.php(43): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“44 [internal function]: Barryvdh\Cors\HandleCors->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“45 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“46 /var/www/_releases/instance/dreamfactory/2.0.0-beta/app/Http/Middleware/FirstUserCheck.php(39): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“47 [internal function]: DreamFactory\Http\Middleware\FirstUserCheck->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“48 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“49 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/View/Middleware/ShareErrorsFromSession.php(49): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“50 [internal function]: Illuminate\View\Middleware\ShareErrorsFromSession->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“51 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“52 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Session/Middleware/StartSession.php(62): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“53 [internal function]: Illuminate\Session\Middleware\StartSession->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“54 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“55 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/AddQueuedCookiesToResponse.php(37): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“56 [internal function]: Illuminate\Cookie\Middleware\AddQueuedCookiesToResponse->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“57 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“58 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Cookie/Middleware/EncryptCookies.php(59): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“59 [internal function]: Illuminate\Cookie\Middleware\EncryptCookies->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“60 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“61 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Foundation/Http/Middleware/CheckForMaintenanceMode.php(42): Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“62 [internal function]: Illuminate\Foundation\Http\Middleware\CheckForMaintenanceMode->handle(Object(Illuminate\Http\Request), Object(Closure))”,
“63 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(124): call_user_func_array(Array, Array)”,
“64 [internal function]: Illuminate\Pipeline\Pipeline->Illuminate\Pipeline\{closure}(Object(Illuminate\Http\Request))”,
“65 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Pipeline/Pipeline.php(103): call_user_func(Object(Closure), Object(Illuminate\Http\Request))”,
“66 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(122): Illuminate\Pipeline\Pipeline->then(Object(Closure))”,
“67 /var/www/_releases/instance/dreamfactory/2.0.0-beta/vendor/laravel/framework/src/Illuminate/Foundation/Http/Kernel.php(87): Illuminate\Foundation\Http\Kernel->sendRequestThroughRouter(Object(Illuminate\Http\Request))”,
“68 /var/www/_releases/instance/dreamfactory/2.0.0-beta/public/index.php(53): Illuminate\Foundation\Http\Kernel->handle(Object(Illuminate\Http\Request))”,
“69 {main}”
]
}
}

2

Are you certain you’re not calling the API (either directly from your app or via a server-side script) with any sort of SQL injection, like putting NOW() in a filter or POSTing it to a table?

3

No, I tries to test create records API from API Docs page. Only fill the “body” with content of record and click test.

5

It works fine in DF2.0-beta2 (Windows) with the same steps.

6

Thanks, I will test. What flavor of SQL is this?

7

I think it is a problem with SQLite Database. Online sandbox is SQLite only, and I also tested it with official DF1.0 instance on local Windows desktop. MySQL database is OK, while the SQLite is failed.

8

I wasn’t able to find anything on timestamp_on_update by searching the SQLite docs. I’m not sure if they support it.

The online sandbox supports connections to SQL Server, MySQL, and PostgreSQL also. If one of those supports this type as you said, I recommend trying that.

9

But, timestamp_on_update is defined by DF as data type when I create database column in DF instance, is it? I like DF, because it provides some predefined services, like database or file storage that do not need me to find other providers. It is convenient for client developers, like me, to create a prototype without any backend knowledge and support. If prototype is accepted, I could get more resource to support me to connect DF services with 3rd party provider, like MySQL, SQL Server, etc.

I remember 1.9.x, the default Local Database service is basing MySQL server, is it? Why does it switch to SQLite in 2.0? It has a lots of constraints of SQLite. :pensive:

10

We are looking into mocking up our timestamp_on_update and timestamp_on_create simplified types and applying them to SQLite. They do currently work in other flavors of SQL, although in MySQL versions earlier than 5.6 you cannot have more than one timestamp field for table. We are also looking into that.

The free sandbox for 2.0 only provides hosted SQLite so that it’s easier to test and more lightweight. Of course MySQL, SQL Server, PostgreSQL, etc. are still supported, we just don’t host them for you for free.

11

We’ve figured out the SQLite timestamp issue. If you’re running a local instance and want the update, pull from the develop branch. Otherwise you can wait for a release (which should be soon) and pull from master, or wait a little bit longer than that for the hosted sandbox system to be updated.

12

Great. Thank you for your help. Looking forward to updates.

13

Let me know what updates you need.