[peterlaursen]

And this is what it should look like with 5.0.x

Code:

SHOW columns FROM mysql.user;

Field Type Null Key Default Extra
——————— ——————————— —— —— ——- ——
Host char(60) NO PRI
User char(16) NO PRI
Password char(41) NO
Select_priv enum('N','Y') NO N
Insert_priv enum('N','Y') NO N
Update_priv enum('N','Y') NO N
Delete_priv enum('N','Y') NO N
Create_priv enum('N','Y') NO N
Drop_priv enum('N','Y') NO N
Reload_priv enum('N','Y') NO N
Shutdown_priv enum('N','Y') NO N
Process_priv enum('N','Y') NO N
File_priv enum('N','Y') NO N
Grant_priv enum('N','Y') NO N
References_priv enum('N','Y') NO N
Index_priv enum('N','Y') NO N
Alter_priv enum('N','Y') NO N
Show_db_priv enum('N','Y') NO N
Super_priv enum('N','Y') NO N
Create_tmp_table_priv enum('N','Y') NO N
Lock_tables_priv enum('N','Y') NO N
Execute_priv enum('N','Y') NO N
Repl_slave_priv enum('N','Y') NO N
Repl_client_priv enum('N','Y') NO N
Create_view_priv enum('N','Y') NO N
Show_view_priv enum('N','Y') NO N
Create_routine_priv enum('N','Y') NO N
Alter_routine_priv enum('N','Y') NO N
Create_user_priv enum('N','Y') NO N
ssl_type enum('','ANY','X509','SPECIFIED') NO
ssl_cipher blob NO
x509_issuer blob NO
x509_subject blob NO
max_questions int(11) unsigned NO 0
max_updates int(11) unsigned NO 0
max_connections int(11) unsigned NO 0
max_user_connections int(11) unsigned NO 0

It looks like MySQL 5.0 was installed over 3.23 without following the correct upgrade procedure (executing the 'mysql_fix_privilege_tables.sql' script).

However in next release SQLyog will not crash, but we cannot guarantee that user management will be possible if the mysql.user table does not have the columns that it should have with the MySQL version.

[peterlaursen]

Basically you should always upgrade when having trouble with an old version!

But this looks pretty strange. Are you sure that you are trying to import the correct file? Ort did you cahnge the database schema in between?

[peterlaursen]

Beta 11 and RC 1 are not quite identical.

[peterlaursen]

OK ..

It should not happen with any version equal or higher than beta 8 or 10.

[peterlaursen]

There is one feature missing in 'Notifications Services' to make that possible with the SJA alone. And that is the option to to execute SQL from an external file. We plan to add this feature but not with high priority. It is not scheduled as neither 1st nor 2nd priority.

The workaround: Schedule a batch file calling

1) a SJA Backup Job

2) a script/program executing the restore to 2nd machine

3) a Notifications Service job executing 'TRUNCATE table …..'

[peterlaursen]

the FREE/COMMUNITY version does not need any installer. You can copy any existing installation to a removable media – or just install to that media and next move it around as you like.

The ENTERPRISE version depends on the installer for the registration.

[peterlaursen]

reproduced ..

.. but only on serververs where the mysql.user table does not have the correct fields corresponding to the MySQL version.

Please execute those statements:

1) SELECT version():

2) SHOW columns FROM mysql.user;

and paste the returns in here.

Am I correct in assuming that you uprgraded the current MySQL version from a previous version. If so you will have to upgrade the mysql.user table by EITHER running the 'mysql_upgrade' program (only available in recent versions) OR executing the 'mysql_fix_privilege_tables.sql' script. Refer to paragraph 2 in the MySQL documentation. For instance: http://dev.mysql.com/doc/refman/5.0/en/upgrade.html

However SQLyog should not crash, and we will fix in next release of course.

[peterlaursen]

Quote:

So is there any way to preview a SQL statement result before executing??

No, there is not.

[peterlaursen]

@Scottah ..

EXACT program version?

[peterlaursen]

Why?

[peterlaursen]

Quote:

click green arrow to execute current querry? Is this correct.

Basically yes!

You have 2 SQL-statements here. They are divided by semicolon. Every statement generates a Server Response when executed.

To execute ALL statements you click the 'double green arrow' . To execute a single statement you position the cursor inside that statement and click the 'single green arrow'. Keyboard shourtcuts are F5 and shift+F5 respectively.

[peterlaursen]

SQLyog does not yet support BIT types as of MySQL 5.

Before version 5 MySQL stored BITS internally as a TINYINT – from version 5 it uses a binary storage format. That means that the correct SQL is for instance:

Code:

alter table `test2`.`tablename1` change `morebits` `morebits` bit (6) DEFAULT false NULL

Note that a BIT is not a single binary value, but rather an array of such values. In this case an array of 6 of them. Refer to http://dev.mysql.com/doc/refman/5.0/en/num…e-overview.html.

If you true to use SQLyog GUI to set the default as FALSE, SQLyog will quote it as a string and it will not be accepted by the server. You will need to execute it from SQL pane. And still SQLyog cannot display the values and you cannot enter values from SQLyog either.

A single binary value is in SQL denoted as a BOOL type. MySQL still uses the TINYINT storage for this and you can work with it from SQLyog and use defaults '0' and '1' (without quotes).

[peterlaursen]

“don't forget my suggestion …”

We have not forgot! A few days ago we did a rough planning for the next about 3 quarters. this is scheduled for Q1.

[peterlaursen]

What we do plan to implement very soon is an option to include the MySQL status message like 'x rows affected'.

What is your concept of 'success' and 'failure'?

[peterlaursen]

The GUI does not provide hashing or encryption options. You can copy the INSERT or UPDATE from history and edit the statement using the md5() function and execute again (it will only work with string types however).

BTW: I am a little surprised that you talk about MD5 and not SHA1. You cannot 'reverse engineer' a MD5 hash, but a SHA1 encryption you can.

[Author]

Posts