Download the latest mysql connector J for you mysql server from their website.

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".

Then go to Preferences, stats config and set the following items:


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);

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);

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);

ALTER TABLE `TRANSFERS` ADD INDEX  (`SESSION_RID`,`TRANSFER_SIZE`,`DIRECTION`,`IGNORE_SIZE`,`START_TIME`);



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;

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

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')

Add new attachment

Only authorized users are allowed to upload new attachments.

List of attachments

Kind Attachment Name Size Version Date Modified Author Change note
png
insert_methods.png 112.2 kB 1 20-Aug-2020 04:28 krivacsz
jpg
sql_stats1.jpg 163.2 kB 1 07-Jul-2020 14:16 Ada Csaba
« This page (revision-37) was last changed on 20-Aug-2020 04:41 by krivacsz
G’day (anonymous guest)
CrushFTP9 | What's New

Referenced by
General Settings

JSPWiki