[peterlaursen]

There also is a relevant entry in our FAQ at

http://www.webyog.com/faq/16_66_en.html

[peterlaursen]

Error number 2013 is:

Message: Lost connection to MySQL server during query

according to http://dev.mysql.com/doc/refman/5.0/en/error-handling.html

This topic in the MySQL docs

http://dev.mysql.com/doc/refman/5.0/en/gone-away.html

could prove to have the solution. But you see there are a lot of possibilities.

Questions:

1) You don't try to use any other SSH software than the client that comes with SQLyog (plink.exe in the SQLyog installation directory) ?

2) If you 'test connection' from the connections manager what happens then ?

3) Did you read the help file regarding how to set up the Connections Manager with SHH? The description here is pretty good actually!

4) Couldn't you possibly disable the firewall for 5 seconds? But personally I don't believe that is it. Rather a networking issue on the remote server place involving SSH and MySQL. But that is a guess!

[peterlaursen]

Further research:

there is an sql_mode available to control this feature named NO_ENGINE_SUBSTITUTION

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

to disable this automatic substitution send

Code:

set session|global sql_mode = 'NO_ENGINE_SUBSTITUTION'

[peterlaursen]

MySQL writes:

Quote:

In MySQL 5.0, you can also change the SQL mode after startup time by setting the sql_mode variable using a SET [SESSION|GLOBAL] sql_mode='modes' statement. Setting the GLOBAL variable requires the SUPER privilege and affects the operation of all clients that connect from that time on. Setting the SESSION variable affects only the current client. Any client can change its own session sql_mode value at any time.

That lets us do the workaround to change the sql-mode to an empty string, create use and change back – as this example shows.

Code:

/* prepare 'strict mode' */
set session sql_mode = 'strict_all_tables';
/* now we are ready! */
set @themode = @@sql_mode;
set session sql_mode = '';
/* now create user with success*/
Insert into mysql.user ( host, user, password, select_priv, insert_priv, update_priv, delete_priv,  create_priv, drop_priv, reload_priv, shutdown_priv, process_priv,  file_priv, grant_priv, references_priv, index_priv, alter_priv ) values ( '%', 'cheatmodeuser', PASSWORD(''), 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N', 'N' );
set session sql_mode = @themode;
/* now old 'scrict mode' is re-established and create user fails */
select (@@sql_mode); /* returns 'strict_all_tables' */

Could something like it be implemented in SQLyog – either transparent/automatically or simply a 'change sql-mode for session'? Is it relevant or is there a better solution?

I think that 'strict mode' is without relevance for USER creation. It can be a relevant precaution when working with DATA that the user (or application) explictily specifies all colums. Correct me if I am wrong!

Finally we just repeat the link:

http://dev.mysql.com/doc/refman/5.0/en/server-sql-mode.html

[peterlaursen]

Unfortunately this is not only an autoincrement issue.

This table

Code:

CREATE TABLE `tablename1` (            
             `t` varchar(20) default NULL,        
             `n` bigint(20) default NULL          
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

makes SQLyog send this SQL

Code:

insert into `test`.`tablename1` ( `t`, `n` ) values (  'c',  '' )

when a value in first row is changed and the samme error occurs.

The MySQL docs says

Quote:

the docs say:

“If you are not running in strict mode, any column not explicitly given a value is set to its default (explicit or implicit) value. For example, if you specify a column list that doesn't name all the columns in the table, unnamed columns are set to their default values. Default value assignment is described in Section 13.1.5, “CREATE TABLE Syntax”. See also Section 1.8.6.2, “Constraints on Invalid Data”.

If you want an INSERT statement to generate an error unless you explicitly specify values for all columns that do not have a default value, you should use STRICT mode. See Section 5.3.2, “The Server SQL Mode”. “

in 'strict mode' it is necessary

1) either to explicitly write values to all colums

2) or completely omit the colums where there is no change from the statement. That would however create problems with colums defined as “NOT NULL – NO DEFAULT” as we have seen here http://www.webyog.com/forums/index.php?sho…=1724&hl=strict

[peterlaursen]

Quote:

but I´ve got error 1044 which I suppose to be an already existing db with same title)

Wrong!

MySQL error messages:

http://dev.mysql.com/doc/refman/5.0/en/error-handling.html

Error: 1044 : Access denied for user '%s'@'%s' to database '%s'

Does the user have the CREATE privilege for instance ?

[peterlaursen]

NO!

'change tabletype' behaves identically with @@sql_mode = 'STRICT_ALL_TABLE' and @@sql_mode = ''. Tables are changed to specified type when possible and to MyISAM when not specified table type is available. No matter whether keyword 'type' or 'engine' is used. Same warnings.

That is not very 'strict' 😮

[peterlaursen]

MySQL Administrator (latest version) has problems too:

http://bugs.mysql.com/bug.php?id=14020

[peterlaursen]

There should be no problems in importing a dump from MySQL 3.23 into 4.0!

I just created a database in MySQL 3.23 exported with SQLyog and got this

Code:

/*
SQLyog Enterprise – MySQL GUI v4.2 BETA 5
Host – 3.23.58-max-nt : Database – test
*********************************************************************
Server version : 3.23.58-max-nt
*/
create database if not exists `test`;
USE `test`;
/*Table structure for table `tablename1` */
DROP TABLE IF EXISTS `tablename1`;
CREATE TABLE `tablename1` (
 `id` bigint(20) NOT NULL auto_increment,
 `mynum` bigint(20) default NULL,
 `mytext` varchar(50) default NULL,
 PRIMARY KEY  (`id`)
) TYPE=MyISAM;
/*Data for the table `tablename1` */
insert into `tablename1` values (1,33,'thirtythree');
insert into `tablename1` values (2,44,'fortyfour');

It imports into MySQL 4.0 without any problems

What do you mean with a raw .sql dump file?

What program did you export with? If it was done with some php-script based tool from a forums system for instance it may not contain the necessary create and use statements. If that is it, try building the structure from SQLyog GUI first.

Also paste in the beginning of the file (no INSERTS are needed!) here so we can see what it is like!

[peterlaursen]

Quote:

set field to NULL

Agreed! When you enter NULL in a string-type is it then the literal string 'NULL' or is it NULL. It is ambigious. Do it just like the blob-viewer!

set to “NOW()”. Ritesh has pormissed that functions will be supported with 4.3 or 4.4.

[peterlaursen]

I'd like to se you type

Code:

select * from æøå;

on your keyboard – it is easy on mine! 😀

But now you can too! Just doubbleclick the øæå -table

[peterlaursen]

I did experience something similar.

But I thought it only occurred when a delimiter was used.

[peterlaursen]

I don't experience that at all!

I think Ritesh should give you a build with a debug library. Running a little bit slower but will give much more information if/when it crashes,

[peterlaursen]

Quote:

so it should be also easily possible via SQLyog

It is! use ALTER TABLE (from menu or popup menu) and type in the new name

[peterlaursen]

I disagree on your last post.

You might have some weird or long names. And if your are working in an international environment the characters used for the names may even not be available from your keyboard!

I don't how to input a czech hacek (sorry for the missing accent!), but with this new click-facility I can word with a databases created with national characters from all over the world.

[Author]

Posts