Migrate work with create table but not work with inset sql
170 views
Skip to first unread message
Blastiko
Feb 14, 2022, 6:21:42 AM2/14/22
to CFWheels
Hi all.
Migrate for creating table work, but following migrate, for insert record to table just created, not work, i obtain:
Error migrating to 20220202175942.Datasource finder verification failed.
The root cause was that: java.sql.SQLException: Usernames and Passwords for all the database tags within the cftransaction tag must be the same.
The sql string copy directly to sql manager work well.
Any idea?
Risto
Feb 15, 2022, 2:11:46 PM2/15/22
to CFWheels
Hi,
What database are you using?
What version of cfwheels are you using?
Are you using adobe Coldfusion or Lucee?
Have you verified your datasource connection in the Lucee or ACF admin?
Can you create a simple insert statement in a <cfquery> tag and run it in a simple .cfm page in your app and see what happens?
Are you using the same database user to connect to the database from ACF or Lucee as you are in your sql manager?
For example, is your sql manager using "root" and your cfml app using a different user?
Generally speaking, if you can create a table but not do an insert it's most likely a permission issue on the database user.
Blastiko
Feb 19, 2022, 6:31:31 AM2/19/22
to CFWheels
Hi Risto
Thank for your answer and I'm sorry to have omitted important info, so:
Database: MySql 5.6.34 on Windows Server 2012
Engine: Coldfusion 11 on different (respect mysql) Windows Server 2012
My cfwheels app works great with any sql type (crud), i have problems only with cfwheels migrations interface.
I have no problem olso with sql manager like HeidiSql with same db credential (db user is not root but have full grant set).
Engine: Coldfusion 11 on different (respect mysql) Windows Server 2012
My cfwheels app works great with any sql type (crud), i have problems only with cfwheels migrations interface.
I have no problem olso with sql manager like HeidiSql with same db credential (db user is not root but have full grant set).
The same migrations executes locally on a docker environment with Lucee and MySql containers, works great.
I notice that only create table ddl works: drop table (reverse migrations), insert records, ecc, nothing of this works, obtain same error.
Blastiko
Mar 3, 2022, 4:10:21 AM3/3/22
to CFWheels
Hi all
In "donwto" migration, for example, I found that the problem is in function "dropTable" in file "wheels/migrator/Migration.cfc".
If i comment the IF that test coldfusione server type (lucee), that drop foreing key before table, i have no error and the table is dropped.
I notice olso that the function "dropForeignKeyFromTable" is not present in MySQL.cfc adapter, but is present in H2, MSsql and PostgreSql adapters.
infact in another environment with lucee server, so not adobe coldfusion server, all work fine.
It is a bug?
dhga...@gmail.com
Mar 10, 2022, 8:06:09 PM3/10/22
to CFWheels
I get the same error while following instructions at
A) Using commandbox.
CommandBox:myApp> install cfwheels-example-app
Creating D:\program_files\CommandBox\myApp\plugins\/FlashMessagesBootstrap-1.0.3
.zip-----------------------------------------------------------------
√ | Installing package [forgebox:cfwheels-example-app]
| √ | Installing package [forgebox:cfwheels-flashmessages-bootstrap@^1.0.2]
| √ | Installing package [forgebox:cfwheels-authenticateThis@^1.0.1]
| √ | Installing package [forgebox:cfwheels-js-confirm@^1.0.5]
CommandBox:myApp> start exampleappACF
√ | Starting Server
| √ | Setting Server Profile to [development]
| √ | Installing package [forgebox:adobe@2016]
Creating D:\program_files\CommandBox\myApp\plugins\/FlashMessagesBootstrap-1.0.3
.zip-----------------------------------------------------------------
√ | Installing package [forgebox:cfwheels-example-app]
| √ | Installing package [forgebox:cfwheels-flashmessages-bootstrap@^1.0.2]
| √ | Installing package [forgebox:cfwheels-authenticateThis@^1.0.1]
| √ | Installing package [forgebox:cfwheels-js-confirm@^1.0.5]
CommandBox:myApp> start exampleappACF
√ | Starting Server
| √ | Setting Server Profile to [development]
| √ | Installing package [forgebox:adobe@2016]
CommandBox> start exampleappACF --verbose
√ | Starting Server
|------------------------------
| Looking for server JSON file by convention: D:\program_files\CommandBox\/
| server-exampleappACF.json
| webroot defaulted to location of server's JSON file: D:\program_files\Com
| mandBox\
| Switching to the last-used server JSON file for this server: D:\program_f
| iles\CommandBox\myApp\/server-exampleappACF.json
| Recalculating web root based on new server JSON file.
| webroot defaulted to location of server's JSON file: D:\program_files\Com
| mandBox\myApp\
| start server in - D:\program_files\CommandBox\myApp\
| server name - exampleappACF
| server config file - D:\program_files\CommandBox\myApp\/server-exampleapp
| ACF.json
| Contacting ForgeBox to determine the latest & greatest version of [adobe
| 2016]... Use an exact 'cfengine' version to skip this check.
| OK, [adobe 2016.0.17+325979] it is!
| WAR/zip archive already installed.
| Server start command:
| C:\Program Files\Java\jre1.8.0_321\bin\java.exe
| -jar C:\Users\dan\.CommandBox\lib\runwar-4.5.2.jar
| --background=true
| --host 127.0.0.1
| --stop-port 50121
| --processname exampleappACF [adobe 2016.0.17+325979]
| --log-dir C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD487089
| CA21E-exampleappACF/adobe-2016.0.17.325979/logs
| --server-name exampleappACF
| --tray-enable true
| --dock-enable true
| --directoryindex true
| --timeout 240
| --proxy-peeraddress true
| --cookie-secure false
| --cookie-httponly false
| --pid-file C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD48708
| 9CA21E-exampleappACF/adobe-2016.0.17.325979/.pid.txt
| --tray-icon C:\Users\dan\.CommandBox\cfml\system\config\server-icons\
| trayicon-cf2016-32px.png
| --tray-config C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD48
| 7089CA21E-exampleappACF/adobe-2016.0.17.325979/.trayOptions.json
| --gzip-enable true
| --cfengine-name adobe
| --dirs /cf_scripts=C:\Users\dan\.CommandBox/server/2D917F226B03C85E70
| 0FD487089CA21E-exampleappACF/adobe-2016.0.17.325979/cf_scripts
| -war D:\program_files\CommandBox\myApp\
| --web-xml-path C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD4
| 87089CA21E-exampleappACF/adobe-2016.0.17.325979/WEB-INF/web.xml
| --http-enable true
| --ssl-enable false
| --ajp-enable false
| --http2-enable true
| --open-browser true
| --open-url http://127.0.0.1:60051
| --port 60051
| --urlrewrite-enable true
| --urlrewrite-file D:\program_files\CommandBox\myApp\urlrewrite.xml
| --predicate-file C:\Users\dan\.CommandBox/server/2D917F226B03C85E700F
| D487089CA21E-exampleappACF/adobe-2016.0.17.325979/.predicateFile.txt
| The server for D:\program_files\CommandBox\myApp\ is starting on http://1
| 27.0.0.1:60051 ...
|------------------------------
| √ | Setting Server Profile to [development]
| |------------------------------------------------------
| | Profile set from server bound to localhost
| | Block CF Admin disabled
| | Block Sensitive Paths enabled
| | Block Flash Remoting enabled
| | Directory Browsing enabled
| |------------------------------------------------------
[INFO ] Runwar: Starting RunWAR 4.5.2
[INFO ] Runwar: HTTP2 Enabled:true
[INFO ] Runwar: HTTP sslEnable:false
[INFO ] Runwar: HTTP ajpEnable:false
[INFO ] Runwar: HTTP warFile exists:true
[INFO ] Runwar: HTTP warFile isDirectory:true
[INFO ] Runwar: Starting background exampleappACF [adobe 2016.0.17+325979] from:
/C:/Users/dan/.CommandBox/lib/runwar-4.5.2.jar
[INFO ] Starting in background -
CommandBox>
√ | Starting Server
|------------------------------
| Looking for server JSON file by convention: D:\program_files\CommandBox\/
| server-exampleappACF.json
| webroot defaulted to location of server's JSON file: D:\program_files\Com
| mandBox\
| Switching to the last-used server JSON file for this server: D:\program_f
| iles\CommandBox\myApp\/server-exampleappACF.json
| Recalculating web root based on new server JSON file.
| webroot defaulted to location of server's JSON file: D:\program_files\Com
| mandBox\myApp\
| start server in - D:\program_files\CommandBox\myApp\
| server name - exampleappACF
| server config file - D:\program_files\CommandBox\myApp\/server-exampleapp
| ACF.json
| Contacting ForgeBox to determine the latest & greatest version of [adobe
| 2016]... Use an exact 'cfengine' version to skip this check.
| OK, [adobe 2016.0.17+325979] it is!
| WAR/zip archive already installed.
| Server start command:
| C:\Program Files\Java\jre1.8.0_321\bin\java.exe
| -jar C:\Users\dan\.CommandBox\lib\runwar-4.5.2.jar
| --background=true
| --host 127.0.0.1
| --stop-port 50121
| --processname exampleappACF [adobe 2016.0.17+325979]
| --log-dir C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD487089
| CA21E-exampleappACF/adobe-2016.0.17.325979/logs
| --server-name exampleappACF
| --tray-enable true
| --dock-enable true
| --directoryindex true
| --timeout 240
| --proxy-peeraddress true
| --cookie-secure false
| --cookie-httponly false
| --pid-file C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD48708
| 9CA21E-exampleappACF/adobe-2016.0.17.325979/.pid.txt
| --tray-icon C:\Users\dan\.CommandBox\cfml\system\config\server-icons\
| trayicon-cf2016-32px.png
| --tray-config C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD48
| 7089CA21E-exampleappACF/adobe-2016.0.17.325979/.trayOptions.json
| --gzip-enable true
| --cfengine-name adobe
| --dirs /cf_scripts=C:\Users\dan\.CommandBox/server/2D917F226B03C85E70
| 0FD487089CA21E-exampleappACF/adobe-2016.0.17.325979/cf_scripts
| -war D:\program_files\CommandBox\myApp\
| --web-xml-path C:\Users\dan\.CommandBox/server/2D917F226B03C85E700FD4
| 87089CA21E-exampleappACF/adobe-2016.0.17.325979/WEB-INF/web.xml
| --http-enable true
| --ssl-enable false
| --ajp-enable false
| --http2-enable true
| --open-browser true
| --open-url http://127.0.0.1:60051
| --port 60051
| --urlrewrite-enable true
| --urlrewrite-file D:\program_files\CommandBox\myApp\urlrewrite.xml
| --predicate-file C:\Users\dan\.CommandBox/server/2D917F226B03C85E700F
| D487089CA21E-exampleappACF/adobe-2016.0.17.325979/.predicateFile.txt
| The server for D:\program_files\CommandBox\myApp\ is starting on http://1
| 27.0.0.1:60051 ...
|------------------------------
| √ | Setting Server Profile to [development]
| |------------------------------------------------------
| | Profile set from server bound to localhost
| | Block CF Admin disabled
| | Block Sensitive Paths enabled
| | Block Flash Remoting enabled
| | Directory Browsing enabled
| |------------------------------------------------------
[INFO ] Runwar: Starting RunWAR 4.5.2
[INFO ] Runwar: HTTP2 Enabled:true
[INFO ] Runwar: HTTP sslEnable:false
[INFO ] Runwar: HTTP ajpEnable:false
[INFO ] Runwar: HTTP warFile exists:true
[INFO ] Runwar: HTTP warFile isDirectory:true
[INFO ] Runwar: Starting background exampleappACF [adobe 2016.0.17+325979] from:
/C:/Users/dan/.CommandBox/lib/runwar-4.5.2.jar
[INFO ] Starting in background -
CommandBox>
B) What database are you using?
Driver: "MySql5" version 8.0.20
C) What version of cfwheels are you using?
"You have successfully installed version 2.2.0 of
CFWheels.
D) Are you using adobe Coldfusion or Lucee?
"CFML Engine
: Adobe ColdFusion2016,0,17,325979"
E) Have you verified your datasource connection in the Lucee or ACF admin?
username: admin
password: commandbox
[Data & Services] [Data Sources]
CF Data Source Name: exampleapp
Data base: exampleapp
Server: 127.0.0.1 Port: 3306
Username: root
Password: mypwd
Driver: MySQL 5
CF Data Source Name: exampleapp
Data base: exampleapp
Server: 127.0.0.1 Port: 3306
Username: root
Password: mypwd
Driver: MySQL 5
[Submit]
[Verify all connection] status is OK
[Migrate to Latest] [Execute]
Migrating from 0 up to 20180519105948.
-------- 20180519095317_Creates_User_Table -----------------
Created table users
-------- 20180519100056_Creates_Roles_Table ----------------
Created table roles
-------- 20180519100244_Creates_Permissions_Table ----------
Created table permissions
-------- 20180519100412_Creates_RolePermissions_Table ------
Created table rolepermissions
-------- 20180519100428_Creates_UserPermissions_Table ------
Created table userpermissions
-------- 20180519100429_Creates_Settings_Table -------------
Created table settings
-------- 20180519100430_Creates_Auditlog_Table -------------
Created table auditlogs
-------- 20180519105943_Adds_Default_Roles -----------------
Error migrating to 20180519105943.
Datasource exampleapp verification failed.
The root cause was that: java.sql.SQLException:
Usernames and Passwords
for all the database tags within the cftransaction tag must be the same.
Reply all
Reply to author
Forward
0 new messages