No clue how to get this right?

[expired user #1919]'s profile image [expired user #1919] posted 16 years ago in General Permalink
I am trying to import a database into HeidiSQL and it keeps giving me this:

SQL error: Table 'tblstores' already exists.


After I run repair or optimize it says the oposite.

error: Table 'tblstores' doesn't exist.



There must be a reference to this table somewhere else.
I have tried fushing everything with no luck.

How to fix this or where to look?

Using this one:
Version 3.2 Revision 1129

Cheers
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Tried Navicat MySQL, MySQL Administrator and PHPmyAdmin.
I can see the tables if I use show tables

But can't remove them, sh1t!

Is there a way to fix this?
Is there an application that can show all the hidden things in any table?

Cheers
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Ok opened the data folder in MySQL deleted the problem tables
Now importing the entire database again, hold thumbs
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Serious note here.

HeidiSQL is trying my patience!
I loaded the query to run the import (of the now new database structure)
It was running since 10:06 am (my last post here.)

I ended all the tasks of HeidiSQL and opened MySQL Administrator 1.0.20
Removed the all the data from the database folder and restored the database within 3.2 seconds

There is something wrong with HeidiSQL.
How do I get the logs or whatever you need to fix it?

Cheers
ansgar's profile image ansgar posted 16 years ago Permalink
There must be something in that SQL script that slowed down the import. Could you send me the zipped SQL file via mail (if there is no sensible data in it)?
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Cool, will do.
I have changed some of tables to MyISAM, but will send you the problem one instead.

Cheers
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Cool, sent
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
Did you get it?
ansgar's profile image ansgar posted 16 years ago Permalink
Yes, got it. Working on it.
ansgar's profile image ansgar posted 16 years ago Permalink
So, I just opened that SQL file in HeidiSQL and can reproduce that long during execution. The file has 5842 INSERTs which is the cause of the problem - HeidiSQL is slow when doing lots of small queries because there is some more overhead in the execution of one query than there should be. That is ugly and even more, it's not resolvable currently as this has its causes deep in the database abstraction layer.

I was working on a replacement some time ago but was stuck when it came to implement the pointer stuff. See the results here
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
I know, let it run for more than 2 hours.
I think it's the InnoDB parts not sure, you have more experience in MySQL.

I sent you the new MyISAM one,
love to here how that one works on your side?

Thanks
ansgar's profile image ansgar posted 16 years ago Permalink
The extended INSERT syntax is solving the problem - same as here.
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
I have no clue what that means.
As long as it's MyISAM it works, no more InnODB for me from now on.

Thanks
ansgar's profile image ansgar posted 16 years ago Permalink
Normal INSERT syntax:
INSERT INTO mytable (col1, col2) VALUES (val1a, val2a);
INSERT INTO mytable (col1, col2) VALUES (val1b, val2b);
INSERT INTO mytable (col1, col2) VALUES (val1c, val2c)


Extended INSERT:
INSERT INTO mytable (col1, col2) VALUES (val1a, val2a),
(val1b, val2b),
(val3a, val3b)


The exetended syntax is more efficient than the normal one because it reduces the total count of single queries and therefore reduces the overhead of query execution.

Could be that it's a MyISAM feature, not sure.
[expired user #1919]'s profile image [expired user #1919] posted 16 years ago Permalink
cool.

Please login to leave a reply, or register at first.