Hi,

I am trying to connect WL with a MySQL database I have built in my local machine. I use the provider System.Data.Odbc and the Connection string is the following:

Driver=MySQL ODBC 5.2 ANSI Driver; Server=127.0.0.1; Database=kibot; User Id=root; Password=mypwd;

Unfortunately I am getting the following error: "Access denied for user 'ODBC'@'localhost' (using password: YES)"

Could you possibly provide some help to resolve teh issue?

Thanks,
Christos

Hi,

Try finding an answer among the many examples of various connection string flavors here:

MySQL connection strings

Especially let me suggest you focus on the .NET provider that comes with MySQL or is downloadable from MySQL website (I don't remember exactly). I'd fall back on ODBC/OLEDB only when a native provider is not available, or in other special cases.

Eugene,

I finally managed to connect to the database.

The whole process is completed without any problem. However when I am trying to plot the data on a chart I get a message "No Available Data".

Any idea what the problem could be?

Thanks,
Christos

First thing to check is that there is data for the period that your chart is requesting... select "All Data" in the Data Loading control to remove all doubt that that's the problem.

And should it happen after following Cone's advice that the problem is rooted in the DB provider, please give us all the details required to reproduce and understand it.

The key info such as connection string, field names, and provider factory will be easy for us to find out if you zip and attach the DataSet's XML file. In addition, indicate the following:

1. which MySQL version you're running
2. which DB connector was used
3. your database schema.

I think that'd be enough to start my troubleshooting.

Cone,

I had selected "All Data" in my previous attempts so that was not the problem.

Eugene,

You can find attached the DataSet's XML file zipped to get the relevant information.

I am using MySQL 5.6.15 and ODBC 5.2.6 connector.

Below you can find the scripts to create the database, It is a simple schema with one table.

CREATE DATABASE `kibot` /*!40100 DEFAULT CHARACTER SET utf8 */;

CREATE TABLE `kibot` (
`rownames` bigint(20) NOT NULL,
`symbol` varchar(10) DEFAULT NULL,
`mydatetime` datetime DEFAULT NULL,
`open` double DEFAULT NULL,
`high` double DEFAULT NULL,
`low` double DEFAULT NULL,
`close` double DEFAULT NULL,
`volume` bigint(20) DEFAULT NULL,
PRIMARY KEY (`rownames`),
UNIQUE KEY `rownames_UNIQUE` (`rownames`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

Let me know if there is any other information I could provide to assist with the troubleshooting.

Thanks for your help,
Christos

Christos,

Thanks for the details.

As I already recommended, before anything else you need to install a native .NET connector for MySQL (i.e. non-ODBC/OLEDB). Additional problem with ODBC drivers is that you have to be sure that they have appropriate "bitness". 64-bit Wealth-Lab can't use 32-bit ODBC drivers, and vice versa.

During the DB provider development I used this native ADO.NET driver called Connector/Net 6.2.4, not sure if I tried ODBC/OLEDB at all:

Connector/Net 6.7.4

Turning on 'SQL Server mode' and 'Allow Zero Datetime' (re: `mydatetime` datetime DEFAULT NULL), as suggested in Usage Notes and Limitations, sometimes might also do the trick.

Eugene,


Thanks for your reply. It seems that WL locates my MySQL database, identifies all the fields and tables so I would say that the connection occurs.

I tried the Allow Zero Datetime and the SQL Server mode "tricks" but they didn't work.

I have noticed the "bitness" issue when searching in the web for potential solutions but I am positive that my versions are all 64bit. (I have connected a 64 bit version of R with this particular database and fetched data into my R session.

So it seems to me that I need to try the ADO.NET driver solution. I have attempted the following but it seems that there is something I am not doing right.

I have installed the Connector/NET 6.7.4 (it is I the 32 bit program files folder) but the provider is not autodetected in the "Select provider" dropdown box.

Have I understood correctly that I would have to see something like "MySQL" in the drop down list added to the already existing members of the list; (the already eisting members are: Odbc, OleDb, OracleClient, SqlClient).

Thanks,
Christos

Christos,

You're right, one would naturally expect MySql.Data.MySqlClient to appear in the dropdown box. I just installed the Connector 6.7.4 and it turned out that the MySQL guys have screwed it up in Connector 6.7.4:

MySQL Bugs: Bug #69760 Connector 6.7.4 does not register as dataprovider in machine.config

You might want to try falling back on a previous build of Connector. If this doesn't work, we're back on ODBC route (where your connection string looks fine to me). As I don't have MySQL installed, I'll have to defer the investigation at a later time.

Eugene,

Thanks a lot for your support - I finally made it implementing your suggestion to fall back on to a previous version of MySQL .NET connector.

Just for the record, this is so much faster in backtesting terms. Why don't you guys stress the importance of having the data into a database. It makes a huge difference speedwise compared to the ASCI provider.

Thanks again for your precious help,
Christos

Christos,

Glad to have helped. Thank you for the feedback.

Eugene,

I just want to refer to my feedback regarding the importance of having the data into a database. I hadn't performed a proper speed comparison so consider my comment cancelled.

What I would like to ask in order to make a more extensive investigation with regards to database vs. ASCII provider is how WL actually performs the data requests. I hope the example below is more specific and will help you answer my question.

So for example lets suppose that we have 500 stocks with 10 years of 1 minute data and a moving average crossover strategy. If I want to backtest the full amount of data (all 500 symbols for 10 years on a minute by minute basis) we would expect the MySQL database solution to be more time consuming due to connection overhead (?). However if we want to run the same backtest for a 100 out of 500 symbols (located in watchlist) for a 3 years period instead, should I expect it to be a lot faster (you can assume localhost for MySQL database)?

Do you think it is also possible to optimize database so as to surpass ASCII provider performance (assuming that we enable cache) even in the first case?

Would it be possible to suggest an optimum data provider solution for the needs of extensive intraday portfolio backtesting?

Thanks

P.S. If in any case you need the machine specifications to answer that as well it is a Windows 7: Intel i7 32GB DDR3 and 200GB SSD.

Christos,

Never performed an in-depth comparison using MySQL, only fixed a bug. I know that MS SQL Server works fine and most are using it. There's no visible performance decrease compared to ASCII data. Of course I assume that your database is properly indexed, or querying it will be slow as hell.

Eugene,

If I may ask what would properly indexing be; create a composite key for Symbol and Date?

Since you haven't performed an in-depth comparison using MySQL would you recomend MS SQL over ASCII provider for the purposes I described in my previous post?

Christos,

1 - Yes, making Symbol + Date a key is what works.

2 - It's been a while since I compared MS SQL to ASCII, so I would recommend giving MS SQL Express a try only if you have the time and desire for such a demanding operation.

Try this connection string samples

Mark

Or these: http://www.connectionstrings.com/mysql/