| 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 one line |
| }}} |
| }}}\\ |
| \\ |
| 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:\\ |
| \\ |
| {{{ |
| CREATE TABLE META_INFO(RID DOUBLE NOT NULL PRIMARY KEY,SESSION_RID DOUBLE NOT NULL,TRANSFER_RID DOUBLE NOT NULL,ITEM_KEY VARCHAR(100) DEFAULT NULL,ITEM_VALUE VARCHAR(2000) DEFAULT NULL); |
| At line 17 changed one line |
| 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: |
| CREATE TABLE SESSIONS(RID DOUBLE NOT NULL PRIMARY KEY,SESSION VARCHAR(100) DEFAULT NULL,SERVER_GROUP VARCHAR(50) DEFAULT NULL,USER_NAME VARCHAR(100) DEFAULT NULL,START_TIME DATETIME DEFAULT NULL,END_TIME DATETIME DEFAULT NULL,SUCCESS_LOGIN VARCHAR(10) DEFAULT NULL,IP VARCHAR(50) DEFAULT NULL); |
| At line 19 changed 9 lines |
| {{{ |
| CREATE TABLE META_INFO ( |
| RID double NOT NULL, |
| SESSION_RID double NOT NULL, |
| TRANSFER_RID double NOT NULL, |
| ITEM_KEY varchar(100) default NULL, |
| ITEM_VALUE varchar(2000) default NULL, |
| PRIMARY KEY (RID) |
| ); |
| CREATE TABLE TRANSFERS(RID DOUBLE NOT NULL PRIMARY KEY,SESSION_RID DOUBLE NOT NULL,START_TIME DATETIME DEFAULT NULL,DIRECTION VARCHAR(8) DEFAULT NULL,PATH VARCHAR(255) DEFAULT NULL,FILE_NAME VARCHAR(2000) DEFAULT NULL,URL VARCHAR(2000) DEFAULT NULL,SPEED INTEGER DEFAULT NULL,TRANSFER_SIZE DOUBLE DEFAULT NULL,IGNORE_SIZE VARCHAR(1) DEFAULT NULL); |
| At line 29 changed 11 lines |
| CREATE TABLE SESSIONS ( |
| RID double NOT NULL, |
| SESSION varchar(100) default NULL, |
| SERVER_GROUP varchar(50) default NULL, |
| USER_NAME varchar(100) default NULL, |
| START_TIME datetime default NULL, |
| END_TIME datetime default NULL, |
| SUCCESS_LOGIN varchar(10) default NULL, |
| IP varchar(50) default NULL, |
| PRIMARY KEY (RID) |
| ); |
| ALTER TABLE `TRANSFERS` ADD INDEX (`SESSION_RID`,`TRANSFER_SIZE`,`DIRECTION`,`IGNORE_SIZE`,`START_TIME`); |
| At line 41 changed 13 lines |
| CREATE TABLE TRANSFERS ( |
| RID double NOT NULL, |
| SESSION_RID double NOT NULL, |
| START_TIME datetime default NULL, |
| DIRECTION varchar(8) default NULL, |
| PATH varchar(255) default NULL, |
| FILE_NAME varchar(2000) default NULL, |
| URL varchar(2000) default NULL, |
| SPEED int default NULL, |
| TRANSFER_SIZE double default NULL, |
| IGNORE_SIZE varchar(1) default NULL, |
| PRIMARY KEY (RID) |
| ); |
| }}}\\ |
| \\ |
| 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 55 changed one line |
| }}} |
| ALTER TABLE `SESSIONS` CHANGE COLUMN `RID` `RID` BIGINT(20) NOT NULL AUTO_INCREMENT; |
| At line 57 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 `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') |
| }}}\\ |
