| At line 1 changed one line |
| Download the latest mysql connector J for you mysql server from their website. I suggest placing the jar file this contains in the plugins/lib folder of CrushFTP and giving it the name "mysql.jar". |
| Download the latest mysql connector J for you mysql server from their website. |
| At line 3 changed one line |
| Then edit the prefs.XML file and set the following items: |
| [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] |
| \\ |
| Alternatively, can browse the Maven repository for the appropriate JDBC drive matching your SQL server version, if not using the latest build of it |
| [https://mvnrepository.com/artifact/mysql/mysql-connector-java] |
| \\ |
| 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 12 added 4 lines |
| Then go to Preferences, stats config and set the following items: |
| \\ |
| [{Image src='sql_stats1.jpg' width='..' height='..' align='left|center|right' style='..' class='..' }] |
| \\ |
| At line 6 changed 5 lines |
| <stats_db_url>jdbc:mysql://127.0.0.1:3306/crushftp_stats</stats_db_url> |
| <stats_db_driver>com.mysql.jdbc.Driver</stats_db_driver> |
| <stats_db_user>root</stats_db_user> |
| <stats_db_driver_file>plugins/lib/mysql.jar</stats_db_driver_file> |
| }}} |
| stats_db_url: jdbc:mysql://127.0.0.1:3306/crushftp_stats |
| stats_db_driver: com.mysql.jdbc.Driver |
| stats_db_user: root |
| stats_db_driver_file: plugins/lib/mysql.jar |
| }}}\\ |
| \\ |
| 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 12 changed one line |
| Make a new DB and call it "crushftp_stats". Below is the schema for 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 14 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 24 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 36 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 50 changed one line |
| }}} |
| 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') |
| }}}\\ |
