New Sqlyog Error Reading Old Sqlyog Database

[CoastalGuy]

I have installed SQLyog 8.82 installed on my Windows XP and as a backup on my Ubuntu 10.10 under Wine. Under Ubuntu, I have the Windows partition with the databases mounted so that Ubuntu can access the same databases as SQLyog under XP. My original databases were all created with an old version of SQLyog where the database created files including .frm, .myd and .myi. The new SQLyog (8.82) creates files of db.opt and .frm.

On the old databases (.frm, .myd and .myi), SQLyog on both Ubuntu and XP opens the database table information great. However, when I created new databases under XP, while the XP version reads the new database table info ok, the Ubuntu version displays an error 1146 when trying to open a table of the new databases, advising that the table doesn't exist.

I have updated both versions to the 9.02 versions, with the same problem. Possibly there might be a setting under Ubuntu that I haven't discovered that would fix this problem? Any ideas?

[peterlaursen]

I really do not understand the statement that “The new SQLyog (8.82) creates files of db.opt and .frm”. SQLyog *does not* create such files. DEFINITELY not! Those are files belonging to the server and not the client (SQLyog). The server creates the files. SQlyog (as any client) only sends SQL to the server.

Frankly I think you are making assumptions in the attempt to understand the problem you have. Why do you think like you do? A client cannot create files used and accessed by the server – it is plain impossibe. Only the server can.

I tried to google on this error http://www.google.dk…channel=suggest . Anything you can use?

Now: on what partition is the MySQL server (I think you have on either the Linux or Windows partition) ? What version is the MySQL server? What is the setting of 'lower_case_table_names' variable on the server? If you try with another client (on both partitions) than SQLyog will you receive the same error?

[CoastalGuy]

Peter,

I probably don't understand everything on this, but I went into SQLyog and using the menus in it, created a new database and table and entered some data. I then went to the folder where databases are stored, and under the folder for the new database, were the files db.opt and tablename.frm. When I looked in the folders for the other databases that I previously created through the old SQLyog, inside were .frm, .myd and .myi files. I haven't a clue what you mean by they can't be created through a client but only on a server, or that they couldn't be created with just db.opt and tablename.frm files, but they were the only files for the database I created using the SQLyog, 8.82, application for my local LAMP.

But since then, I did some research, and found notations for the .myd and myi files and the table type of MylSAM. I went back into SQLyog, accessed the table and changed the type to MylSAM. Sure enough, when I looked in the folder for that database, there were .myd and .myi files. Additionally, I could then successfully open the table in the SQLyog installed on my Ubuntu OS.

Obviously, the default must have been for MylSAM table types when I created databases under my old SQLyog as .myd and .myi files were created for all of them. Now I am not sure what table type was created by default when I created the new table that only created db.opt and tablename.frm files, and that could be opened in the XP version but not the Ubuntu version, but apparently there is still a quirk for whatever table type it was.

Anyway, MylSAM table type is fine with me and it works for everything I need, so please forget the entire thing. I'm not sure why you are jumping on my request, apparently expecting me to be an expert and possibly quoting something in error. Anyway, in the future, I will do Google searches to hopefully find a resolution for any problem I encounter.

[peterlaursen]

You should simply not care about files when working with a database server. Let the server store its data as it wants. Communicate wiht the server through a client (like SQLyog) and do not interfere in what the server is doing with the file system. The storage is different for different storage engines (as you have observered yourself). MyISAM has 3 files per table. InnoDB has (normally) only the .frm file. Data and indexes for all tables go to one file (.ibd). The (discontinued) Falcon engine has one file per database. Some engines will not wirte directly to the table storage but to logs in the first place. Only with MyISAM you can clearly identify what files contain your data (and only after FLUSH TABLES you can be perfectly sure that everything from memory has been written to disk).

[CoastalGuy]

Peter,

I only checked into the files, to see if I could see any difference on the databases where it's tables opened on SQLyog under both Ubuntu and XP, versus the new database I created under XP, but then could not open the table under the Ubuntu installed SQLyog.

That is when I found that the table type of MylSAM created .frm, .myd and .myi files and worked under both OS's. The one I created under XP that wouldn't work under the Ubuntu installed SQLyog, was as you noted, possibly created as InnoDB table type.

My question then, is why a table created as a InnoDB under the XP Sqlyog, will not open under the Ubuntu Sqlyog? Trying to open this table under Ubuntu results in a “Mysql Error #1146, table does not exist. My databases are fairly simple where I can use the table type MylSAM and not have this problem, but I was just curious if you had any ideas, in case I ever need to use a InnoDB table type. If not, don't worry about it.

Regards,

[peterlaursen]

“My question then, is why a table created as a InnoDB under the XP Sqlyog, will not open under the Ubuntu Sqlyog?”

Looks like an issue with the mounting mechanism in Ubuntu. Exactly how did you mount the NTFS partition? (I think you access the Windows MySQL datadir from Linux – correct me if I am wrong)

[Supratik]

Hello Dave,

As far as I remember you have XAMPP configured in both Windows and Ubuntu.

In addition to Peter's question, please let us know the details of your MySQL server if you are not using XAMPP ?

Warm Regards

Supratik

[CoastalGuy]

Yes, as you suggested, I changed the LAMP's for both XP and Ubuntu to XAMPP, both running the same versions (instead of my Sokkit). Also, both of your SQLyog's are running the 9.0 version (just updated).

Now the SQLyog in Ubuntu is running under the Wine application as a Windows program. Again, all of my databases created under the “very” old version of SQLyog created files of .frm, .myd and .myi (so I assume they were all defaulted to MylSAM tables)? When I use MylSAM tables (.frm, .myd & .myi files) under the 9.0 version of SQLyog, no problem. But, when creating InnoDB tables (under XP) (.frm files only) it works under XP but not Ubuntu, where I received that error that the database doesn't exist.

I have not tried this in reverse though, creating an InnoDB table in Ubuntu, and then trying to open it under XP. If you think this might be an important factor, I will give it a try.

Coastalguy

[CoastalGuy]

Ok,

Further to my response above, I had to reboot to Ubuntu to get the mount information. I am mounting the Windows partition (holding the databases used by both Ubuntu and XP), in the fstab file for mounting on boot up.

UUID=XXXXXXXXXXXXXXXx /media ntfs defaults 0 0

Now, under XP, I created 2 databases, one with a MylSAM table and one with a InnoDB table, entering several records into each. As thought, under Ubuntu, the MylSAM table opened fine while the InnoDB file produced a does not exist error.

Trying it in reverse, under Ubuntu, I deleted both of these databases and re-created them as I did in XP, one with a MylSAM table and one with a InnoDB table. Back in XP, both opened fine.

Obviously, under Ubuntu, there is a problem opening InnoDB tables created under XP, but not visa versa. Consequently, for the few InnoDB tables I might need, I can always create them under Ubuntu and have them available under both OS's. For MylSAM tables, I can create them under either OS.

This is fine with me, “unless you want to pursue this further”. I have a solution which I am fine with so as to not have to bother you further.

Regards,

Coastalguy

[peterlaursen]

We will check this. Just wnated to be perfectly sure about the environment/setup. However if SQLyog cannot handle InnoDB on a MySQL instance on Linux with a datadir on a mounted NTFS partion, I would be surprised if other clients can. But at least we could file a bug report with MySQL then. What is weird is that you tell MyISAM work fine and InnoDB does not.

I think you did not reply to one question, and that is: what is the lower_case_table_name setting on both MySQL instances. Execute “SHOW VARIABLES LIKE 'lower_case_table_names';” Also tell: do you use 'mixed case' table and database names? Maybe you could to to set lower_case_table_names to “1” on both instances (and also try to create a new InnoDB table on the Windows partion after this and try to access if from Linux)? Refer http://dev.mysql.com/doc/refman/5.1/en/identifier-case-sensitivity.html.

[CoastalGuy]

I wasn't certain how to run the EXECUTE command you provided, but I went into the Query box and entered that command. The result was: variable name: “lower_case_table_names”, Value: 1. If I did this right, I guess the value of 1 is already set? If I did it wrong, please let me know the steps for this.

Also, on all of the databases and tables, I always use purely lower case, but sometimes with an underscore if 2 words (i.e. database = test_myisam, table = table_myisam. I'll try it using just one lowercase word for both the database and table.

Remember that I am a total novice using your client, especially any Query or other commands. If you need any info requiring commands, please provide how I should perform the task. I know how to create databases, tables, fields etc. and enter records either directly or via PHP code. Anything else on your client, I have to research and will be guessing at the very best.

Thanks,

[CoastalGuy]

Just an aside, I was wondering why I wasn't getting any emails (or RSS feed) on your responses? I copied the url from the RSS feed at the top right “View New Content”, but get nothing. I checked my spam site and they aren't there either. Thanks,

[Supratik]

We are able to reproduce this case at our end and we are looking into this with priority.

Unfortunately due to holiday in between we will be able to update you with the status on Monday.

Quote:

Just an aside, I was wondering why I wasn't getting any emails (or RSS feed) on your responses? I copied the url from the RSS feed at the top right “View New Content”, but get nothing. I checked my spam site and they aren't there either. Thanks,

You may click on the “Watch Topic” button at the top and select appropriate notifications settings.

Please let know if the problem still persists.

Warm Regards

Supratik

[CoastalGuy]

Supratik,

Getting emails now.. thanks,

No rush on this. Glad we finally had a problem you were able to replicate at your end.. was thinking maybe it was me? If it will help, I created another InnoDB table under XP where I tried to change the table type under Ubuntu. As with opening the table, changing the table type also errs that it cannot find the database. Apparently, no matter what you try to do with a InnoDB table created under XP, it can't find it under Ubuntu.

I await your findings… good luck,

Coastalguy,

[peterlaursen]

Let me add taht it is also reproducible with command line client.

[Supratik]

Hello Dave,

By default in the XAMPP MySQL configuration file “innodb_data_home_dir” is set to “/opt/lampp/var/mysql/”.

Normally in default MySQL installations this variable is commented so by default it uses the datadir.Please refer here.

Changing “innodb_data_home_dir” variable to the same path as “datadir” variable fixed the issue at our end.

Can you please try the same at your end and confirm us if it is working for you.

[CoastalGuy]

Supratik,

Seems you did it again. Changed the InnoDB dir path in my.cnf and it seems to be working fine now. Thanks again for your great advice.

Regards,

Coastalguy

[Author]

Posts