[mythtv-users] mythtv dropping mysql???

[Michael T. Dean]

On 10/21/2014 02:47 AM, Michael Watson wrote:
> On 21/10/2014 12:55 PM, Michael T. Dean wrote:
>> And it will be possible for you to access the "full raw data".  It's 
>> still a MySQL database file.  We're just saying we're not going to 
>> make people install the MySQL server to use it. 
>
> If its just going to be an MySQL database file sitting in a folder 
> somewhere, then someone could simply point a MySQL Server to that 
> file, and really fubar the database if they left the MythDB engine 
> running at the same time, maybe file locks will prevent this, but 
> where there is a will, there's a way....

Right.  It's your data, so you're more than welcome to break it as much 
as you like using whatever process you like.  We're not trying to stop 
that, we're just trying to make it easier to set up and use and maintain 
MythTV, make it less likely for accidental data breakage (especially 
when some user is trying to fix a problem and sees an ancient or 
incorrect-for-their-specific-situation post with some SQL 
hack/workaround that seems like it would help), and make it so that 
everyone--developers included (especially!)--have to use the database 
properly when writing code for MythTV.

It's actually unbelievable how many people are complaining about the 
idea, especially as some of these people are the same people who have 
complained in the past that, "It's just terrible design that every 
single MythTV application (backends, frontends, jobqueues, MythWeb, 
scripts, 3rd-party client, ...) needs direct database access.  Any sane 
design keeps clients from directly accessing the database."  An embedded 
database is just a good way of enforcing that sane design--since 
embedded MySQL is not a networked/multi-client database engine, only one 
process (the one in which the database is embedded) can ever read from 
it.  We could do a good design with a client/server, networked, 
multi-client database engine--but the only way to enforce the design 
with that approach is policy.  (And if you have much experience with 
tech/techies, you'll know how well policy does at enforcing behaviors.)

> Recently there has been a little bit of chatter about optimising MySQL 
> for MythTV, and how much of a difficult task it is, I wonder how Myth 
> (with embedded MySQL/MariaDB) will achieve this, or will there be more 
> config variables to cater for this.  If more config variables are 
> added, brings me back to wondering why the DB needs to be embedded.  
> Is it merely to prevent the odd user from destroying there database, 
> or simply to prevent the odd user difficulty in setting up MythTV

More than just set up--as you say below, distros like Mythbuntu or 
LinHES have done an excellent job of making setup nearly foolproof.  
However, even just maintaining the database is difficult enough for 
non-DBA's (even those using distros like Mythbuntu/LinHES that do their 
best to help maintain the DB).  See, for example, the tip of the iceberg at:

https://code.mythtv.org/trac/ticket/12299
https://code.mythtv.org/trac/ticket/10831
https://code.mythtv.org/trac/ticket/10348
https://code.mythtv.org/trac/ticket/10177
https://code.mythtv.org/trac/ticket/9978
https://code.mythtv.org/trac/ticket/9300
https://code.mythtv.org/trac/ticket/8404
https://code.mythtv.org/trac/ticket/8473
https://code.mythtv.org/trac/ticket/8089
https://code.mythtv.org/trac/ticket/6429
https://code.mythtv.org/trac/ticket/5415
https://code.mythtv.org/trac/ticket/3804
(That being just a sampling of the ones that were actually reported as 
bugs that doesn't even touch the significantly greater number of posts 
on list with the same issue, let alone the questions in IRC, or the 
people who didn't know what was going on and also never asked on a 
MythTV list/forum/irc channel and eventually either figured it out 
themselves or deleted their DB and started over or waited long enough 
for the scheduled optimize_mythdb.pl their distro had set up to fix it 
or ...)

There's also the issue of backing up and restoring the DB--something 
that's very difficult to do properly if you don't know much about 
databases.  As a matter of fact, there's a significant portion of users 
(even SQL-knowledgeable (especially SQL-knowledgeable?) users) who have 
corrupt schemas because of improperly backing up/restoring their DBs.

And you would be surprised just how much those who think they know 
MySQL/SQL/MythTV's schema/... still don't know about it.  There are a 
significant number of bugs that were put into MythTV by us developers 
(who, arguably, understand more about MythTV's database usage than a 
majority of users) that came down to a misunderstanding of some little 
MySQL nuance or even a misinterpretation of the MythTV data.  For 
example, take the whole store "UTF data in columns that MySQL considers 
Latin-1 so we don't triple the size of the text data" as a major 
example--a significant percentage of users had corrupt data because 
they/their distros/scripts they used with the database (including 
mysqldump)/approaches they used to restore data/... were not configured 
to allow MythTV to use the columns like that--and no one (MythTV devs or 
otherwise) realized the extent of the configuration requirements until 
we tried to switch to UTF-8 columns.  Now, if there were only one 
configuration used by every MythTV user--even if it was a wrong 
configuration--it would have been straightforward to make that 
conversions because the approach would have been the same for everyone 
(even if it had to fix data corruption caused by previous misconfiguration).

Now, you may be saying, "If someone can't understand an error message 
that says, 'Table <whatever> is marked as crashed and should be 
repaired,' they shouldn't be running MySQL in the first place."  If so, 
we completely agree with you.  :)

> - A Firstime Myth User, is far better to try installing MythBuntu (or 
> one of the other dedicated distro's) than to try compiling/installing 
> themselves, but then if a user can compile Myth by themselves, surely 
> they can configure a DB server by themself.
>
> Is it that, it has come a time that MythTV needs a consistent DB 
> engine, much like the need for a consistent ffmpeg version.  Are the 
> differences in DB engines becoming to great across distros/platforms?

That's not really a concern at this point.  We only support "MySQL" 
(which includes--at least until there are significant differences--at 
least some MariaDB allowance) and we don't need any modifications to the 
MySQL code and only need to enforce a minimum DB version.

> Lets say, I want to enable/disable User Job 1 on all existing 
> recording rules, currently through the frontend or mythweb this is a 
> painfully slow process with more than a handful of rules.  Does the 
> framwork currently exist in Perl/Python Bindings or Services API to 
> achieve this operation?    An example of how something like this can 
> be scripted using the "proper" tools, could help me and others to use 
> the proper tools rather than resorting to "lazy" SQL commands.

While I don't have an exact example, there's 
http://www.mythtv.org/wiki/0.26_Python_bindings/Data_Handlers#Record and 
https://github.com/MythTV/mythtv/blob/master/mythtv/programs/mythbackend/services/dvr.h#L148 
.  The main reason there aren't many examples of how to use those things 
is because for years people have been getting used to doing it the wrong 
way (through direct database editing using SQL) so even though those 
approaches have existed for years, people haven't taken the time to 
learn a new/better approach.

Imagine if, instead of writing some SQL to just stick values into 
columns in the DB (where you have to figure out which columns and either 
assume or look up the specific values to insert into those columns), you 
had a generic script you could run to manipulate your recording rules en 
masse--for example, it first lets you select a group of rules (either 
one by one or using a "query"--some criteria such as "user job 1 is 
enabled") and then apply a modification to those selected rules (such as 
"enable user job 2").  Such a script would be straightforward to 
write--the hardest part being the generic UI.  Now imagine if that 
functionality were available directly from the backend web server (the 
new "MythWeb" so to speak).  It seems that bulk changes to recording 
rules is a common-enough requirement that we /should/ have such a 
capability, but no one (dev or otherwise) has been motivated enough to 
write a UI for it since there's a lazy way out.

Mike