simply accounting - How to connect to MySQL Server

simply accounting - How to connect to MySQL Server

Since you do not seem very familiar with ODBC and MySQL, we may be able to help you more if you describe what it is you are trying to do.

To fill the Connector/ODBC form, do the following:

1-Open Simply, go to Help > About Simply Accounting by Sage and click on Support Info... On the right side, take note of the Computer name and Port number.

2-On the ODBC form you need to fill the following fields:

Server: The Computer name from Help > About

User: Your user name (sysadmin or other with 3rd party access rights)

Password: Well, the user's password or blank if no password

Database: simply

Click on the Connect Options tab:

Port: the port number from Help > About

You'll then be able to connect. Note that Simply must be opened to connect through ODBC.

Some Files/Directories you might be interested in:
[] C:\Documents and Settings\All Users\Documents\Sage Software\ConnectionManager\ConnectionManager.ini

[] C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\dbengine.ini

[] C:\Documents and Settings\All Users\Documents\Sage Software

[] C:\Documents and Settings\All Users\Documents\Sage Software\Simply Accounting

[] C:\Documents and Settings\All Users\Documents\Simply Accounting

[] C:\Documents and Settings\danny\My Documents\Simply Accounting

Add these lines to C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\dbengine.ini
# binary log
log-bin=mysql-bin
# port number
port = 13541
# data dir
datadir = C:\\test\\asdf.SAJ
# log all the queries log (including select, insert, update, and all).
log

Create a file called c:\mysql-init.txt with following content:

GRANT ALL PRIVILEGES ON *.* TO 'root'@'localhost' IDENTIFIED BY 'YourPassWordHere' WITH GRANT OPTION;
GRANT SUPER ON *.* TO 'root'@'localhost';
FLUSH PRIVILEGES;

Note: without granting the super privileges, you will see the following message:

Sage.Simply.DataTypes.ConnectionException: TRIGGER command denied to user 'root'@'localhost' for table 'titrec' ---> MySql.Data.MySqlClient.MySqlException: TRIGGER command denied to user 'root'@'localhost' for table 'titrec'

Run MySQL and create a new user called root on start up:
start > run > cmd.exe

C:\> "C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\mysql\mysqld-nt.exe" --defaults-file="C:\\Program Files (x86)\\winsim\\ConnectionManager\\MySqlBinary\\5.0.38\\dbengine.ini" --init-file="C:\\mysql-init.txt"

http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html

Commands you might be interested in:

Login to mysql:
cmd> mysql -u sysadmin -p --port=13541
cmd> mysql -u root -p --port=13541

Note: Simply Accounting default username: sysadmin.

Manually shutdown mysql:
cmd> mysqladmin -u root -p -P 13540 shutdown

View mysql binary log:
cmd> mysqlbinlog C:\test\asdf.SAJ\mysql-bin.000007

Simply Accounting MySQL database fine tune Tips:

Edit C:\Documents and Settings\All Users\Documents\Sage Software\ConnectionManager\ConnectionManager.ini, under the [settings] section:
[settings]
# Note: Simply Accounting Connection Manager will use below setting to overwrite mysql's "innodb_buffer_pool_size".
Buffer Pool Size=1024

Edit C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\dbengine.ini, under the [mysqld] sectioin:
[mysqld]
### Modified by Jun
# Note: do not change innodb_buffer_pool_size here, since it will have no effect because Simply Accounting Connection Manager has its own setting to overwrite it. You can either go to Simply Accounting Connection Manager and adjust the "memory buffer size",
# or edit C:\Documents and Settings\All Users\Documents\Sage Software\ConnectionManager\ConnectionManager.ini, under the settings section:
# [settings]
# Buffer Pool Size=1024
#
# Note: the "number of connection attempts" in Simply Accounting Connection manager is "max_connect_errors".
#
# Note: If you modify the innodb_log_file_size, MySQL will fail to restart and InnoDB will complain about the size of the changed log file.
# The proper way to increase the innodb_log_file_size:
# 1. shutdown mysql server.
# 2. make backup of data and log files (ibdata1 and ib_logfile*).
# 3. remove InnoDB log files (ib_logfile*).
# 4. set new value for innodb_log_file_size in my.cnf
# 5. start mysqld
# 6. check error logs to ensure everything went fine.
# Reference: http://dev.mysql.com/doc/refman/5.5/en/innodb-data-log-reconfiguration.html
#innodb_buffer_pool_size=1G
innodb_additional_mem_pool_size=16M
innodb_log_buffer_size=8M
innodb_log_file_size=128M
innodb_log_files_in_group=2
max_allowed_packet = 16M
default-storage-engine = InnoDB
# turn this on if you prefer separate inoodb file for each table.
#innodb_file_per_table
# log all the queries log (including select, insert, update, and all).
#log
# Port number to use for connections.
#port=13540
# Path to the database root.
#datadir=D:/SimplyAccounting/company1/9901.SAJ
### skip DNS domain name resolve to avoid network slow issue.
skip-name-resolve

Simply Accounting Default dbengine.ini:
[mysqld]
max_connections=100
innodb_log_buffer_size=1M
innodb_log_file_size=2M
innodb_thread_concurrency=8
innodb_lock_wait_timeout=10
innodb_support_xa=0
innodb_autoextend_increment=1
wait_timeout=2147483
lower_case_table_names=1
connect_timeout=10

Use following commands to verify the settings:
mysql> SHOW VARIABLES LIKE 'innodb_buffer_%';
mysql> SHOW VARIABLES LIKE 'innodb_additional_%';
mysql> SHOW VARIABLES LIKE 'innodb_log_%';

MySQL fine tune Tips:
What is the buffer size currently set to? If it is 56 MB, try increasing to 128 and test. Here is the recommendation from the Help Menu:

Memory buffer size (in MB). The amount of system memory allocated to cache company data used to populate reports, search/lookup results, and record lists in Simply.

Tips:

If a large number of records and transactions are stored in your company database, and you have sufficient RAM installed on your computer, you may be able to fine-tune Simply Accounting's performance by increasing the memory reserved for Connection Manager operations. Be careful! If you set this number too high, you may prevent other applications from running, including Simply Accounting.

We recommend that you increase the buffer size in increments of 10MB, and never exceed 60% of total physical RAM installed in your PC. If you are unable to run other applications after increasing the buffer size, click the Default button to reset the buffer to it's default size.

You can manually edit the Buffer Pool Size setting in the ConnectionManager.ini file.This setting will overwrite any existing value for innodb_log_buffer_size set in the MySQL dbengine.ini file.
I would increase to 128 as a starting place and keep it there for a while and observe. If you experience resources issues (either with Simply Accounting or another product) begin lowering the buffer.

Use bigger log files and third party database
If you want to use bigger log files setting, you have to dump the simply database and restore it to avoid "log sequence number in the future" error

- close all simply clients.

- open Simply Connection Manager, and make sure there is no connection at all.

- copy the simply db folder to Jun's machine.

- start working on jun's machine

- use the latest Simply DB

- start simply with default setting

- look at the error logs, make sure there is no error.

- close simply and restart it

- look at the error logs, make sure there is no error.

- do a mysql dump with -R option (to include routines, functions, stored procedures):

cmd> e:
cmd> mysqldump -u root -p -P 13540 -R simply > 2012-07-20_1126_simply.sql
cmd> mysqldump -u root -p -P 13540 mysql > 2012-07-20_1126_mysql.sql

Note: You don't need to backup information_schema database, since information_schema is a virtual database, rebuilt each time MySQL is restarted, so there is no point in backing it up because you can't restore it anyway.

- close simply client, and go to Simply Connection Manager to make sure there is no more connections.

- Go to Task Manager to make sure mysqld-nt.exe is not running.

- start mysql server manually:
cmd> "C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\mysql\mysqld-nt.exe" --defaults-file="C:\\Program Files (x86)\\winsim\\ConnectionManager\\MySqlBinary\\5.0.38\\dbengine.ini"

- delete simply database:
cmd> mysql -u root -p -P 13540
mysql> drop database simply;
mysql> exit

- shutdown mysql manually:
cmd> mysqladmin -u root -p -P 13540 shutdown

- Go to Task Manager to make sure mysqld-nt.exe is not running.

- Go to c:\mycompany\9901.SAJ, and backup and delete these files:
ib_logfile*
ibdata1

- use the custom dbengine.ini setting:
C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\dbengine.ini

- Use Simply Connection Manager to adjust the Memory Buffer size to 1024 MB.

Note: make sure you do "Stop service" and "Start Service" in Simply Connection Manager.

or you prefer to modify the file manually:

add/modify this line in C:\Documents and Settings\All Users\Documents\Sage Software\ConnectionManager\ConnectionManager.ini:
Buffer Pool Size=1024

- start mysql server manually:
cmd> "C:\Program Files (x86)\winsim\ConnectionManager\MySqlBinary\5.0.38\mysql\mysqld-nt.exe" --defaults-file="C:\\Program Files (x86)\\winsim\\ConnectionManager\\MySqlBinary\\5.0.38\\dbengine.ini" --init-file="C:\\mysql-init.txt"

- use navicat to create a new simply database.
name: simply
character set: latin1 -- cp1252 West European
collation: latin1_general_ci

- restore simply database:
cmd> e:
cmd> mysql -u root -p -P 13540 simply < 2012-07-20_1126_simply.sql

 - check log files

- close all the connections.

- shutdown mysql manually:
cmd> mysqladmin -u root -p -P 13540 shutdown

- start simply client

- make sure mysql setting is on:
mysql> SHOW VARIABLES LIKE '%pool%'
innodb_additional_mem_pool_size 16777216
innodb_buffer_pool_size 1073741824

- check log files

- create a drupal database

- restore drupal database
cmd> mysql -u root -p -P 13540 drupal < drupal.sql

- make the www user have the right to access drupal database.

 - make another backup (with -R option) after everything is done:
cmd> e:
cmd> mysqldump -u root -p -P 13540 -R simply > 2012-07-20_1818_simply.sql

Reference:

http://www.simplyaccounting.com/community/forums/p/5399/19091.aspx#19091
http://community.simplyaccounting.com/community/forums/p/3326/11683.aspx