Setting Defaults

[Junk1958]

I set up our mysql db with default char set utf8 and using Innodb, and it appears correctly in the my.ini file.

However, when I run Table->Backup/Export->Scheduled Backups, the file always exports as MyISAM and if I don't set the char set to utf8 by hand, the default is latin1.

I would have expected that SQLyog would have used the database settings (perhaps by reading my.ini or inferring them from something in the db?) as the default. Clearly, it knows these values since they also appear in the Tools->Show->Variables display in SQLyog.

So aside from setting the char set for the export to utf8 manually, I don't see how to make SQLyog export the right char set. And I do not see anywhere where I can say to SQLyog that the db engine statement in the table export should be InnoDB.

The result is that I have to remember for each export to go in and edit the statements in the .sql file manually before I use the .sql file.

Is there some sort of SQLyog preference file so I don't have to go back and edit the table export files every time afterwards?

MySQL: 5.1.41

SQLyog: 8.2

Win2008 R2

Thanks and kind regards,

PBW

[peterlaursen]

However if you have defined server default as utf8/innodb databases and tables may have other defaults (for instance if you have imported your data from an older installation using latin1/myisam.  Server/database character settings for charset are only defaults. As are server/database settings for storage engine. The defaults may always be overridden at lower level (ie: you can have a latin1 table in a database that has default utf8).

SQLyog will dump data as they are stored.  All backup tools will. The setting in SQLyog 'secheduled backup' is a setting of how the exported file shall be encoded ONLY.  The interactive export tool will always use utf8-encoding. Only if you need to read the file in some non-unicode editor you will need to use this option. 

[Junk1958]

Interesting.

I have looked in my SQLyog (v 8.2) and I see under Table->More Table Operations->View Advanced Properties that there is a value for Engine for each table. It also seems to give me the ability to change the engine type for the table. This is good.

However, on the same tab, I do not see anything about char set, although I do see what looks to be a particular char set listed under Collations. Is Collation the right thing to look at?

And how would I use SQLyog to change/convert the data in the table to utf8?

Lastly, I looked under database for similar information/operations on the databse itself. However, I do not see anything similar. How would I see what the defaults are for the database and how would I change them using SQLyog?

Thanks kindly!

[peterlaursen]

The option to change charset is is ALTER TABLE .. advanced properties.  Also if you write a SQL statement you will writhe ALTER TABLE tablename CHARACTER SET … “.  

[attachment=1358:charset.jpg]

'View advanced properties' is read-only information.  The charset is implicitly described by the collation. 

[Junk1958]

Thank you.

[Author]

Posts