[mythtv-users] error during upgrade from V30 to V31

[Bill Meek]

On 3/24/20 7:01 PM, James Abernathy wrote:
> On Tue, Mar 24, 2020 at 7:59 PM James Abernathy <jfabernathy at gmail.com>
> wrote:
> 
>>
>>
>> On Tue, Mar 24, 2020 at 7:22 PM Bill Meek <keemllib at gmail.com> wrote:
>>
>>> On 3/24/20 3:44 PM, James Abernathy wrote:
>>>>> I can login with the --default-extra-file
>>>> The sudo dpkg-reconfigure mythtv-database didn't add much new text.
>>>>
>>>> jim at mythbuntu:~$ sudo dpkg-reconfigure mythtv-database
>>>> Failed to create/alter user or grant permissions to database (incorrect
>>>> admin username/password?)
>>>> Try:
>>>> sudo dpkg-reconfigure mythtv-database
>>>
>>> On line 42: mysql $SECURITY_INFO "$database" >/dev/null 2>&1;
>>>
>>> remove the:
>>>
>>>       >/dev/null
>>>
>>> That should allow the SQL error to print.
>>>
>>> The three queries (run as the MySQL root user) are:
>>>
>>> CREATE USER IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH
>>> mysql_native_password;
>>> ALTER USER 'mythtv'@'%' IDENTIFIED BY 'your password here';
>>> GRANT ALL ON mythconverg.* TO 'mythtv'@'%';
>>>
>>> --
>>> Bill
>>>
>> jim at mythbuntu:~$ sudo dpkg-reconfigure mythtv-database
>> ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check
>> the manual that corresponds to your MariaDB server version for the right
>> syntax to use near 'IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH
>> mysql_native_password' at line 1
>> Failed to create/alter user or grant permissions to database (incorrect
>> admin username/password?)
>> Try:
>> sudo dpkg-reconfigure mythtv-database
>> jim at mythbuntu:~$
>>
> 
> If I run the command from the console I get this:
> MariaDB [mysql]> CREATE USER IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH
> mysql_native_password;
> ERROR 1064 (42000): You have an error in your SQL syntax; check the manual
> that corresponds to your MariaDB server version for the right syntax to use
> near 'IF NOT EXISTS 'mythtv'@'%' IDENTIFIED WITH mysql_native_password' at
> line 1
> MariaDB [mysql]>

You're on Server version: 10.0.36-MariaDB-0ubuntu0.16.04.1 Ubuntu 16.04.

My MariaDB tests are on Server version: 10.3.22-MariaDB-0ubuntu0.19.10.1 Ubuntu 19.10.

https://mariadb.com/kb/en/create-user/#if-not-exists says IF NOT EXISTS is OK
for 10.3. Sounds like 10.0 doesn't support it.

What happens if you do: CREATE USER 'blah'@'%'  IDENTIFIED WITH mysql_native_password;
and then do it again?

DROP USER blah; will get rid of the new user.

-- 
Bill