Hi all,
I'm getting completely hacked off with mysql on Windows, last night I downloaded their GUI tools and even one of them had problems connecting to the database at one point. Does anyone have a Windows system that actually works with MySQL?
Is Postgres any better as a database, as in actually work? I can fix code if the database itself works ok ...
- sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
works fine for me, i've run some fairly heavy duty things on mysql in windows before .. postgres is apparently also supported in fpdb, but i haven't tried that.
is your mysql server actually running? if you used the FPDB Installation program, it seems that it gives you an incorrect MySQL configuration, unless you're in French(?), and then the server won't run until you fix it.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
hehe, yeah I saw some French looking things, but it seems to work roughly ok. Maybe that's because I installed mysql first before trying out the installer. (I hadn't found the detailed installation instructions on here when I was installing so I was largely making it up as I went along - preferred to try installing individidual things myself but ended up giving up because I couldn't figure out how to get pygtk installed/configured - then I just removed python and used the installer - should have done that first!)
I installed the mysql GUI tools so that I could look at the db setup, and I changed a few settings to try and increase limits, turn logging on and so on. But at one point I clicked on the users page and got some "connection dead" type error - not pleasing :-(
I'll keep googling and reading mysql.com to see if there's some more tests/config I can do - maybe there is some setup problem - let me know if there's any settings you think I should check ...
-sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
i don't know.. normally when i configure a mysql, i go and download WAMP .. in this case, I just used the installer provided, figured out the service wasn't running, found it's config file, and changed "C:\Programmes\" to "C:\Program Files\", restarted the service, and it's been working fine since. I haven't tried to install any additional tools, I'm really disgustingly low on drive space right now .. need some big wins so I can score one of those new 2TB drives ;)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I am a Linux user and pretty shaky on using windows. I have installed both MySQL and PostgreSQL on my windows box and have fpdb running with each of them. I have used mysql for several years (on Linux) and am pretty comfortable with it. One thing I really like about postgres is that it automatically installs the gui tool (PGadmin III) and it really works on both Windows and Linux.
One other thing: The installer does not properly install mysql on Vista. On vista you need to install mysql before running the installer. There is a link to Vista mysql installation instructions at the top of the windows installation instructions.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hmmm, not sure the flush privileges is a problem for me.
When I found that install page the step that bothered me slightly was step 10 (Decision Support) I think I just took the default (multi-functional or something?), I might look into what settings this alters ... or just reinstall MySQL again, that might be easier! ( but then I'd have to re-import :-( )
- sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Found out more about the problem now, but not sure what's causing it.
The get_table_name function in Database.py is returning None after a while. It starts off ok, finding the details for the latest imported hand, and the HUD gets created, but soon it starts returning None instead :-(
Debugging it, it seems to stop seeing updates to the Hands table, it only sees the ones that were imported initially. If I add in a select max(id) from Hands it gets stuck ... so the None is correct in a way, that connection doesn't seem to be able to see the hand it's looking for.
(eg. import 3 hands 10,11,12. get_table_name(10) says max is 12 and works ok, get_table_name(11) and (12) also say max is 12 and work ok ... but when it looks for hand 13 ... :-( )
But dunno why this is, I've looked at caching on the server (now definitely off) and fiddled with altering the connection variables but haven't been able to fix it yet ...
- sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Silly question, did you have problems importing initially, and now that you have MySQL running, have you recreated and reimported say 1000 hands to see if you get the same results?
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I thought it sounded like some read consistency thing when I was posting this morning, but I didn't really believe mysql would be doing that. So I figured I just had to read up on how mysql works differently to how I expected and there it was on: http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-isolation.html
Maybe you guys have the database isolation level changed to read committed in the database settings?
I just added some code like this in Database.py to set it for these connections:
cur_iso = self.connection.cursor()
cur_iso.execute('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED')
cur_iso.close()
:-)
I think all this has given me a handle on python (like it!) and this app so hopefully I can start helping out properly now ...
- sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I've made these changes and they work perfectly after testing ~100 hands. I read the mysql docs on this and a couple of articles I found on line and I'm still not sure why this change makes such a big difference. "READ COMMITTED" seems to be the safest option in any case. I'll push this in a bit.
Thanks for the tip.
I agree wit you about python. Python is becoming my favorite language--it is very efficient of coder time, especially for team projects where we need to read each others code.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sorry that wasn't the best page to link to, what you need is an explanation of the different modes: http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
>> "This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other."
The isolation level thing alters how different connections see each others data. If using a connection that is only reading data, I am used to every statement getting the latest data from the database (obviously not including uncommitted changes by other connections).
In the default mode in mysql, if you execute "select max(id) from hands" you get the largest hand id from the hands table. If some other connections inserts more hands and your connection then repeats that query, mysql returns the same result as the first time, effectively it caches the previous result.
Apparently if you issue a commit, then you will see new data in the next select, so you could issue regular commits to solve this but setting a more appropriate mode when the connection is first created seems a better fix.
- sqlcoder
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Thanks for the explanation--I think I get it now. The way I implemented the HUD_main code, I was expecting to always get the most recent data, not data consistent with the previous iteration.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Hi all,
I'm getting completely hacked off with mysql on Windows, last night I downloaded their GUI tools and even one of them had problems connecting to the database at one point. Does anyone have a Windows system that actually works with MySQL?
Is Postgres any better as a database, as in actually work? I can fix code if the database itself works ok ...
- sqlcoder
works fine for me, i've run some fairly heavy duty things on mysql in windows before .. postgres is apparently also supported in fpdb, but i haven't tried that.
is your mysql server actually running? if you used the FPDB Installation program, it seems that it gives you an incorrect MySQL configuration, unless you're in French(?), and then the server won't run until you fix it.
hehe, yeah I saw some French looking things, but it seems to work roughly ok. Maybe that's because I installed mysql first before trying out the installer. (I hadn't found the detailed installation instructions on here when I was installing so I was largely making it up as I went along - preferred to try installing individidual things myself but ended up giving up because I couldn't figure out how to get pygtk installed/configured - then I just removed python and used the installer - should have done that first!)
I installed the mysql GUI tools so that I could look at the db setup, and I changed a few settings to try and increase limits, turn logging on and so on. But at one point I clicked on the users page and got some "connection dead" type error - not pleasing :-(
I'll keep googling and reading mysql.com to see if there's some more tests/config I can do - maybe there is some setup problem - let me know if there's any settings you think I should check ...
-sqlcoder
i don't know.. normally when i configure a mysql, i go and download WAMP .. in this case, I just used the installer provided, figured out the service wasn't running, found it's config file, and changed "C:\Programmes\" to "C:\Program Files\", restarted the service, and it's been working fine since. I haven't tried to install any additional tools, I'm really disgustingly low on drive space right now .. need some big wins so I can score one of those new 2TB drives ;)
I am a Linux user and pretty shaky on using windows. I have installed both MySQL and PostgreSQL on my windows box and have fpdb running with each of them. I have used mysql for several years (on Linux) and am pretty comfortable with it. One thing I really like about postgres is that it automatically installs the gui tool (PGadmin III) and it really works on both Windows and Linux.
Regarding the problem at hand. Take a look at steps 18 and 19 of the windows install procedure (= http://fpdb.sourceforge.net/docs-install-windows.php\). I think there should be a step after step 19 that says:
FLUSH PRIVILEGES;
That is also omitted from the installer.
If that doesn't fix it, post your HUD-error.txt here.
One other thing: The installer does not properly install mysql on Vista. On vista you need to install mysql before running the installer. There is a link to Vista mysql installation instructions at the top of the windows installation instructions.
Hmmm, not sure the flush privileges is a problem for me.
When I found that install page the step that bothered me slightly was step 10 (Decision Support) I think I just took the default (multi-functional or something?), I might look into what settings this alters ... or just reinstall MySQL again, that might be easier! ( but then I'd have to re-import :-( )
- sqlcoder
any of those mysql install options should be ok
Found out more about the problem now, but not sure what's causing it.
The get_table_name function in Database.py is returning None after a while. It starts off ok, finding the details for the latest imported hand, and the HUD gets created, but soon it starts returning None instead :-(
Debugging it, it seems to stop seeing updates to the Hands table, it only sees the ones that were imported initially. If I add in a select max(id) from Hands it gets stuck ... so the None is correct in a way, that connection doesn't seem to be able to see the hand it's looking for.
(eg. import 3 hands 10,11,12. get_table_name(10) says max is 12 and works ok, get_table_name(11) and (12) also say max is 12 and work ok ... but when it looks for hand 13 ... :-( )
But dunno why this is, I've looked at caching on the server (now definitely off) and fiddled with altering the connection variables but haven't been able to fix it yet ...
- sqlcoder
Silly question, did you have problems importing initially, and now that you have MySQL running, have you recreated and reimported say 1000 hands to see if you get the same results?
Figured it out. :-)
I thought it sounded like some read consistency thing when I was posting this morning, but I didn't really believe mysql would be doing that. So I figured I just had to read up on how mysql works differently to how I expected and there it was on: http://dev.mysql.com/doc/refman/5.1/en/innodb-transaction-isolation.html
Maybe you guys have the database isolation level changed to read committed in the database settings?
I just added some code like this in Database.py to set it for these connections:
cur_iso = self.connection.cursor()
cur_iso.execute('SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED')
cur_iso.close()
:-)
I think all this has given me a handle on python (like it!) and this app so hopefully I can start helping out properly now ...
- sqlcoder
Care to elaborate on what the problem was a little more. My SQL knowledge is ok but not guru.
We are using the defaults i believe, what problems would repeatable-read be causing?
I've made these changes and they work perfectly after testing ~100 hands. I read the mysql docs on this and a couple of articles I found on line and I'm still not sure why this change makes such a big difference. "READ COMMITTED" seems to be the safest option in any case. I'll push this in a bit.
Thanks for the tip.
I agree wit you about python. Python is becoming my favorite language--it is very efficient of coder time, especially for team projects where we need to read each others code.
Sorry that wasn't the best page to link to, what you need is an explanation of the different modes:
http://dev.mysql.com/doc/refman/5.1/en/set-transaction.html
>> "This convention means that if you issue several plain SELECT statements within the same transaction, these SELECT statements are consistent also with respect to each other."
The isolation level thing alters how different connections see each others data. If using a connection that is only reading data, I am used to every statement getting the latest data from the database (obviously not including uncommitted changes by other connections).
In the default mode in mysql, if you execute "select max(id) from hands" you get the largest hand id from the hands table. If some other connections inserts more hands and your connection then repeats that query, mysql returns the same result as the first time, effectively it caches the previous result.
Apparently if you issue a commit, then you will see new data in the next select, so you could issue regular commits to solve this but setting a more appropriate mode when the connection is first created seems a better fix.
- sqlcoder
Thanks for the explanation--I think I get it now. The way I implemented the HUD_main code, I was expecting to always get the most recent data, not data consistent with the previous iteration.
Thanks again for the explanation.
I've been wondering why the grapher doesn't update after the first draw. This would explain it.