Re: [gentoo-server] MySQL upgrade guide

[Francesco Riosa]

Alex Efros ha scritto:
> Hi!
>
> On Fri, Dec 08, 2006 at 01:45:31AM +0200, Alex Efros wrote:
>
>>     # mysql_upgrade_shell --user=root --password='your_password' \
>> 	 --protocol=tcp --datadir="${DATADIR}"
>>
>> In original MySQL upgrade documentation they recommend to run mysql_upgrade
>> program and there is nothing about mysql_upgrade_shell script.
>> AFAIK mysql_upgrade_shell is older version of this program (mysql_upgrade
>> program was added because there no bash to execute mysql_upgrade_shell
>> script on Win32).
>> So, what's better to run - mysql_upgrade_shell or mysql_upgrade? Or there
>> no difference between them now?
>>
>
> BTW, I got error while running mysql_upgrade_shell:
>
> ---cut---
> # mysql_upgrade_shell --user=root --password='MYPASS' --protocol=tcp --datadir="${DATADIR}"
> This script updates all the mysql privilege tables to be usable by
> MySQL 4.0 and above.
>
> This is needed if you want to use the new GRANT functions,
> CREATE AGGREGATE FUNCTION, stored procedures, or
> more secure passwords in 4.1
>
> Got a failure from command:
> cat /usr/share/mysql/mysql_fix_privilege_tables.sql | /usr/bin/mysql --no-defaults --force --user=root --host=localhost --password=MYPASS --database=mysql
> Please check the above output and try again.
>

> Running the script with the --verbose option may give you some information
> of what went wrong.
>
> If you get an 'Access denied' error, you should run this script again and
> give the MySQL root user password as an argument with the --password= option
> ---cut---
>
> As you see, there no "above output", so I've tried to run failed command
> manually. And got strange output with some errors:
>
> ---cut---
> ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
> @hadGrantPriv:=1
>
^^^ these are good, the colum is already in place and don't need to be
created === the database is up to date
> ERROR 1054 (42S22) at line 277: Unknown column 'Create_routine_priv' in 'where clause'
> ERROR 1054 (42S22) at line 313: Unknown column 'Create_user_priv' in 'where clause'
> ---cut---
>

> Running mysql_upgrade result in similar errors but looks like it stop on
> first error:
>
> ---cut---
> # mysql_upgrade -p -v --basedir=/usr
> Enter password:
> mysql_upgrade already done for this version
> ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
> ---cut---
>
>
This is what I get with an already updated db:

---cut---


/usr/bin/mysql_fix_privilege_tables --verbose  -uroot  --password=${PASS}

This script updates all the mysql privilege tables to be usable by
MySQL 4.0 and above.

This is needed if you want to use the new GRANT functions,
CREATE AGGREGATE FUNCTION, stored procedures, or
more secure passwords in 4.1

You can safely ignore all 'Duplicate column' and 'Unknown column' errors
because these just mean that your tables are already up to date.
This script is safe to run even if your tables are already up to date!

ERROR 1060 (42S21) at line 22: Duplicate column name 'File_priv'
ERROR 1060 (42S21) at line 28: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 29: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 30: Duplicate column name 'Grant_priv'
ERROR 1060 (42S21) at line 41: Duplicate column name 'ssl_type'
ERROR 1060 (42S21) at line 70: Duplicate column name 'Routine_type'
ERROR 1054 (42S22) at line 94: Unknown column 'Type' in 'columns_priv'
ERROR 1060 (42S21) at line 100: Duplicate column name 'type'
ERROR 1060 (42S21) at line 110: Duplicate column name 'Show_db_priv'
ERROR 1060 (42S21) at line 127: Duplicate column name 'max_questions'
ERROR 1060 (42S21) at line 137: Duplicate column name
'Create_tmp_table_priv'
ERROR 1060 (42S21) at line 140: Duplicate column name
'Create_tmp_table_priv'
ERROR 1061 (42000) at line 145: Duplicate key name 'Grantor'
ERROR 1060 (42S21) at line 252: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 253: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 254: Duplicate column name 'Create_view_priv'
ERROR 1060 (42S21) at line 259: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 260: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 261: Duplicate column name 'Show_view_priv'
ERROR 1060 (42S21) at line 282: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 283: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 284: Duplicate column name 'Create_routine_priv'
ERROR 1060 (42S21) at line 289: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 290: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 291: Duplicate column name 'Alter_routine_priv'
ERROR 1060 (42S21) at line 293: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 294: Duplicate column name 'Execute_priv'
ERROR 1060 (42S21) at line 306: Duplicate column name 'max_user_connections'
ERROR 1060 (42S21) at line 315: Duplicate column name 'Create_user_priv'
done

---cut---

/usr/share/mysql/mysql_fix_privilege_tables.sql
is plain sql you can try single statments from there to see what's the
result

good night CET time remember ? ;)


-- 
gentoo-server@g.o mailing list