[not reproducible] MySql Unable to upgrade database (1 Viewer)

[spiderwheels]

I encountered a problem with my upgrade from 1.1.3 to 1.2RC. It seems to be specific to MySql 5.1 installs.

It has been discussed here:
https://forum.team-mediaportal.com/general-support-51/unable-upgrade-database-99857/

I have added the solution to the problem in post #24 (page 3) and I am flagging it in this forum so it can be added to the final release.

 

[infinite.loop]

I have just had the same issue.

I have MySql 5.1.38 (installed by MediaPortal way back when..)

The problem is that this version of MySql doesn't like the strings to be identity strings to be quoted e.g. where it says "SoftwareEncoder" or "idEncoder" should just be SoftwareEncoder and idEncoder.

I am sorry but this can not be the case.

I use the very same MySQl Server Version 5.1.38 on my TV-Server, and the upgrade works just nicely.
So it can not be a general problem with that specific MySQL version. Otherwise why do I and others not encounter it?

 

[spiderwheels]

I have just had the same issue.

I have MySql 5.1.38 (installed by MediaPortal way back when..)

The problem is that this version of MySql doesn't like the strings to be identity strings to be quoted e.g. where it says "SoftwareEncoder" or "idEncoder" should just be SoftwareEncoder and idEncoder.

I am sorry but this can not be the case.

I use the very same MySQl Server Version 5.1.38 on my TV-Server, and the upgrade works just nicely.
So it can not be a general problem with that specific MySQL version. Otherwise why do I and others not encounter it?

Others have encountered the issue e.g. the person who started the other thread with the same issue so it shouldn't be dismissed so easily. This is an RC so lots of people won't be installing it until final release and it won't be very nice for them when the install fails.

Anyway, as it occurs on some machines and not others you are right to say it's not a general MySQL issue so it must be something to do with the MySQL installation. As it happens MySQL has a mode named ANSI_QUOTES. Normally MySQL identifiers should be unquoted or quoted with the back tick character "`". If (and only if) the ANSI_QUOTES mode is enabled you may also use double-quotes. The only reason I have MySQL installed is because MediaPortal installed it. Why this mode should be disabled on my machine and enabled on your machine is lost in the history of past installations.

I don't have a copy of the scripts but it is likely the SoftwareEncoder upgrade script is the only one to contain double quotes. Simply remove the quotes and make it policy to only use unquoted identifiers or backticks.

 

[infinite.loop]

I don't have a copy of the scripts but it is likely the SoftwareEncoder upgrade script is the only one to contain double quotes. Simply remove the quotes and make it policy to only use unquoted identifiers or backticks.

It might not be that "simple" to do that.
Also I'd like to find out the reason for the upgrade failure rather than working around it.

 

[mm1352000]

Well, technically the unquoted versions should work whether ANSI_QUOTES mode is on or off, hence I'd consider it "safer" to use that approach.

[Edit: but is it safe to update ~60 scripts?]

 

[infinite.loop]

but is it safe to update ~60 scripts?

I do not dare to suggest such for 1.2.0.
Maybe 1.3.0. and this needs one hell of testing to get all scripts tested.

 

[spiderwheels]

Well, technically the unquoted versions should work whether ANSI_QUOTES mode is on or off, hence I'd consider it "safer" to use that approach.

[Edit: but is it safe to update ~60 scripts?]

Yes ANSI_QUOTES is an addition to using unquoted IDs or back ticks. As it might be necessary to quote some identifiers then replacing the double-quotes with back ticks would be better than unquoted IDs.

But I agree modifying a large number of scripts is bound to cause a problem.

Is it possible instead for the installer to restart the MySQL server with the ANSI_QUOTES mode enabled? It can be set as default in my.ini. I will check if the option is missing from my system when I get back home.

In general case it would help to have the installer ensure MySQL is configured correctly. There could be any number of reasons why my system is miss-configured (e.g. file corruption, installing a Beta, installing a patch to a beta.. maybe I did a manual install of MySQL - I can't remember that far back ) and it would be a waste of time to try to pin point one.

 

[infinite.loop]

spiderwheels
that MySQL install you have there. Was that done by the MediaPortal installer or did you install it manually? (maybe someone can check what default setting MySQL has when installed manually?)

It is impossible that MediaPortal altered your present MySQL install. The DeployTool is no capable of doing such.

 

[spiderwheels]

As I said it may be possible it was a manual install but I can't remember. It is a long time since I did a fresh install.

I would only have done so if there had been a reason that one of the previous MediaPortal releases or betas didn't install on my system. I had installed the administration tools (MySQL Workbench etc..) quite a long time ago too.

As a slight aside: Is the MySQL service actually removed completely when you un-install MediaPortal or could it be possible settings are left hanging around?

So if I did install it manually then there is a discrepancy between the default configuration and the MediaPortal configuration.

It is not unreasonable to expect the MediaPortal installer (or even the TV service) to ensure MySQL is configured correctly and certainly not impossible for it to do so (stop MySQL service, modify settings files, restart MySQL service). Whether or not it is worthwhile to do so is debatable as if it does turn out to be a manual MySQL install it may not affect a lot of people. I guess that's your call.

Edit: Would someone kindly post a correct MySQL my.ini file so I can compare?

 

[infinite.loop]

As a slight aside: Is the MySQL service actually removed completely when you un-install MediaPortal or could it be possible settings are left hanging around?

No.
MediaPortal, TV-Server and MySQL are seperate installers. And uninstallers.
The "MediaPortal Setup" (or DeployTool) is nothing else than a tool that does some system checks, downloads required components (DirectX, vc++, mysql, etc) and then launches the actual installers.

So if you want to remove any of these applications, you have to run the individual uninstaller.
Furthermore, when uninstalling MySQL, all your database files will remain on the HDD.

C:\ProgramData\MySQL\MySQL Server 5.1\data

So you can not use MediaPortal Setup to automatically install MySQL as long as you have those files sitting there. The mysql setup will fail because of the presence of those old files. You have to (re)move them first.

It is not unreasonable to expect the MediaPortal installer (or even the TV service) to ensure MySQL is configured correctly and certainly not impossible for it to do so (stop MySQL service, modify settings files, restart MySQL service). Whether or not it is worthwhile to do so is debatable as if it does turn out to be a manual MySQL install it may not affect a lot of people. I guess that's your call.

I think that it would not be wise to just alter the present MySQL install. What if that is a custom install that requires to run as configured?
We could upset a lot of users by "fixing" their MySQL Servers configuration. Next issue will be that their SQL server might not be running on the same PC - so I am not sure if we can even check for the configuration?
Any db guru can answer that?

What we should do is (if possible) check the MySQL Servers settings and if we find something that will cause issues for the MP install, warn the user.

Would someone kindly post a correct MySQL my.ini file so I can compare?

[client]
port=3306
[mysql]
default-character-set=utf8
[mysqld]
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.1/"
datadir="C:/ProgramData/MySQL/MySQL Server 5.1/Data"
default-character-set=utf8
default-storage-engine=myisam
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=100
query_cache_size=32M
table_cache=64
tmp_table_size=18M
thread_cache_size=4
thread_concurrency=4
myisam_max_sort_file_size=100M
myisam_max_extra_sort_file_size=100M
myisam_sort_buffer_size=64M
key_buffer_size=16M
read_buffer_size=2M
read_rnd_buffer_size=16M
sort_buffer_size=2M
innodb_additional_mem_pool_size=2M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=1M
innodb_buffer_pool_size=96M
innodb_log_file_size=50M
innodb_thread_concurrency=8