| At line 1 added 3 lines |
| __IMPORTANT: due to the security updates since CrushFTP version 10.5.2+ any JDBC driver jar file needs to be placed into the CrushFTP10/plugins/lib/ directory, or it won't load.__ |
| \\ |
| \\ |
| At line 5 added 6 lines |
| \\ |
| [https://dev.mysql.com/downloads/connector/j/] |
| \\ |
| Choose the 'platform independent' type download, and extract the archive. There will be a jar file inside that extracted folder. |
| \\ |
| I suggest placing the mysql-connector-java-8.0.28.jar file from this in the plugins/lib folder of CrushFTP and giving it the name "mysql.jar". |
| At line 3 removed 5 lines |
| [https://dev.mysql.com/downloads/connector/j/5.1.html]\\ |
| Direct link: [https://dev.mysql.com/get/Downloads/Connector-J/mysql-connector-java-5.1.47.zip] |
|
| I suggest placing the mysql-connector-java-5.0.8-bin.jar file from this in the plugins/lib folder of CrushFTP and giving it the name "mysql.jar". |
|
| At line 9 changed one line |
|
| \\ |
| [{Image src='sql_stats1.jpg' width='..' height='..' align='left|center|right' style='..' class='..' }] |
| \\ |
| At line 12 changed one line |
| stats_db_driver: com.mysql.jdbc.Driver |
| stats_db_driver: com.mysql.cj.jdbc.Driver |
| At line 15 changed 4 lines |
| }}} |
|
| Use MySQL Workbench and make a new DB with the name "crushftp_stats" (or adjust the URL above to reference the name you choose.) Below is the schema to put in it: |
|
| }}}\\ |
| \\ |
| Use MySQL Workbench and make a new DB with the name "crushftp_stats" (or adjust the URL above to reference the name you choose.) Below is the schema to put in it:\\ |
| \\ |
| At line 28 changed one line |
| }}} |
| }}}\\ |
| \\ |
| That is it. Now the statistics data should be going to the MySQL tables, and reports will automatically run against those tables too.\\ |
| \\ |
| !! Setup Auto increment primary keys |
| \\ |
| 1. On prefs.XML (find it at CrushFTP install folder) change the tag value "__stat_auto_increment__" to __true__.\\ |
| \\ |
| 2. Change the RID column (on META_INFO, SESSIONS and TRANSFERS table) to be auto incremented and data type from DOUBLE to BIGINT.\\ |
| \\ |
| {{{ |
| ALTER TABLE `META_INFO` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT; |
| At line 30 changed one line |
| That is it. Now the statistics data should be going to the MySQL tables, and reports will automatically run against those tables too. |
| ALTER TABLE `SESSIONS` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT; |
|
| ALTER TABLE `TRANSFERS` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT; |
| }}}\\ |
| \\ |
| 3. Adjust insert queries. Remove the __RID__ value from all insert query.\\ |
| \\ |
| [attachments|insert_methods.png]\\ |
| \\ |
| Change __stats_insert_meta_info__ from:\\ |
| {{{ |
| INSERT INTO META_INFO (RID, SESSION_RID, TRANSFER_RID, ITEM_KEY, ITEM_VALUE) VALUES (?,?,?,?,?) |
| }}}\\ |
| to:\\ |
| {{{ |
| INSERT INTO META_INFO (SESSION_RID, TRANSFER_RID, ITEM_KEY, ITEM_VALUE) VALUES (?,?,?,?) |
| }}}\\ |
| \\ |
| __stats_insert_sessions__ from:\\ |
| {{{ |
| INSERT INTO SESSIONS (RID, SESSION, SERVER_GROUP, USER_NAME, START_TIME, END_TIME, SUCCESS_LOGIN, IP) VALUES (?,?,?,?,?,?,?,?) |
| }}}\\ |
| to:\\ |
| {{{ |
| INSERT INTO SESSIONS (SESSION, SERVER_GROUP, USER_NAME, START_TIME, END_TIME, SUCCESS_LOGIN, IP) VALUES (?,?,?,?,?,?,?) |
| }}}\\ |
| \\ |
| __stats_insert_transfers__ from:\\ |
| {{{ |
| INSERT INTO TRANSFERS (RID, SESSION_RID, START_TIME, DIRECTION, PATH, FILE_NAME, URL, SPEED, TRANSFER_SIZE, IGNORE_SIZE) VALUES (?,?,?,?,?,?,?,?,?,'N') |
| }}}\\ |
| to:\\ |
| {{{ |
| INSERT INTO TRANSFERS (SESSION_RID, START_TIME, DIRECTION, PATH, FILE_NAME, URL, SPEED, TRANSFER_SIZE, IGNORE_SIZE) VALUES (?,?,?,?,?,?,?,?,'N') |
| }}}\\ |
