[peterlaursen]

I think it always was the idea that you should escape yourself. The comment string is not read character-by-character but as a whole.

But then it should not be 'parsed away' or 'hidden' at a later point!

Your are right – it is not functional the way it is now.

[peterlaursen]

hmmm .. there might be some ALIAS issue with VIEWs.

Let Ritesh research into it next week.

[peterlaursen]

a further explanation:

You define a VIEW as

Code:

CREATE VIEW .. as SELECT …

the SELECT statenent may use UNIONs and JOINs etc. and thus several (dozens, hundreds!) of tables may be involved in defining a VIEW. When you are exporting only a subset of a database it is not at all uncomplicated to test whether the tables needed for building a VIEW are there!

So VIEWS, SP's and FUNCTIONS are exported with the complete database only (if you choose).

[peterlaursen]

Which version of SQLyog are you using? You must use 5.0 or higher.

Please read through the COMPLETE .sql-file !! First a Table is created, then a View, next the Table is dropped. Maybe Ritesh can explain the exact reasons for this coding. But it works perfectly here! Try yourself! edit the file .. replace the DB-name in 'use' statement and import it. Does it not import correctly?

But this

Quote:

And the check boxes for the 5.x Objects are only available when all tables in the database are selected – grayed-out otherwise.

— is as it must be! SP's, Functions and Views 'belong to' the Database, Triggers 'belong to' a Table. However .. yes .. if all tables used for defining a VIEW are there then it COULD be done that those VIEWs that are sufficiently defined were exported. But it is not like that as of now. Would be quite complicated coding and would violate the MySQL OBJECTS concept to some degree. OBJECT (except for TRIGGERS) 'belong to' a Database – not a set of Tables!

[peterlaursen]

hmmm …

I have no needs of such research my own. But if you ever run across a similar thing again it would be very nice if you were able a very small 'reproducable test case' – ie. a dump with a single database table and a few rows and colums only.

I very rarely use phpmyadmin myself and I am mot familiar with the different versions.

What is the 'history' of your local mysql server. Has the 4.1 version been updated from an earlier version, and did you run the update scripts? I wonder if there are some old-format system tables there.

[peterlaursen]

and ..

what happens in MySQL command-line client ?

[peterlaursen]

that is strange … 😮

When you click on TABLE tab this SQL

Code:

select * from `db_name`.`tbl_product` limit 0, 50

is executed. You can see it in the HISTORY pane.

Try copy/paste from HISTORY to SQL-pane it and exectute. What happens?

Any difference to see? Do you use back-quote as Yog does ? Do you use full syntax (`db`.`table`). Try omitting first the one thing, then the next … Can you dump from your local? If yes try edit the table name. Does it import? We gotta try a lot of mad things to find some idea to follow here … Does remote server and local use the same charset ? phpmyadmin has implemented som non-standard 'encoding' (NOT a MySQL term!!) management.

Do you have a chance to get data from the server using SQLyog (Can you connect? ) ? Can you import them then and problem persists or not? If server is a Linux server (and you have physical access to it) you can install SQLyog in WINE and make the dump. Refer to http://www.webyog.com/faq/5_71_en.html. Or use SJA for Linux.

It remember some situation (but I don't remember details) with some problem with data imported from a phpMyAdmin dump some moths ago. So I think we will have to try with a dump NOT from phpmyadmin.

A hexeditor also can be a wonderful thing …

[peterlaursen]

BTW:

I have PM'ed CalEvans and asked for his assistance. He probably knows more about MySQL on Linux than anybody else here. So if there is some way to configure -away this problems he knows.

[peterlaursen]

I think it is different situations …

YOUR problem is that there is one single INSERT statement that needs a bigger buffer than you can allocate. It is the max_allowed_packet issue.

THE OTHER PERSONs problem is a problem with the file size – not the row/record size. I don't know the reason for that. It would be nice if you had posted a link to that thread. But it could be a PHP configuration issue.

It looks like you have free swap memory, but I don't know if this buffer can be swapped on Linux.

It should not be hard manually to find that insert statement the causes the trouble. Probably is the first one in the biggest table (the one with LONGBLOBs).

Try this SQL

Code:

create test_table select * from that_table_that_troubles limit x,1;

where x is a value of a row that you know is particularly big. That will create a new table with that row only. Export that table. How big is it?

I know no tool to convert 'Bulk Inserts' to 'Single Row Inserts'. It is not a big deal to write some program that does if you are familiar with coding. But that was not the idea, i know!

[peterlaursen]

http://dev.mysql.com/doc/refman/4.1/en/sto…quirements.html

says storage requirement for a longblob is 2^32+4 Bytes. The 4 bytes are for adressing – the 2^32 bytes is for data.

2^32 Bytes = (2^32)/(2^10)/(2^10)/(2^10) GB = 4 GB.

That is what data ONE SINGLE Longblob can hold.

And with 'Bulk Insert' you have more rows in one insert statement.

How is your swap partition mounted? I don't know if it will help to increase it (on Windows I believe it would). Maybe CalEvans knows ??

[peterlaursen]

and …

do you have a lot of VERY BIG variable types such as LONGBLOBS in each row of data?

On which platform/OS is the server running? There is no option here to dynamically swap physical memory to 'virtual memory' ?

[peterlaursen]

Wasn't some crash issues of 5.0 fixed with 5.01? I think so!

Would you mind trying SQLyog 5.01 or 5.02 beta?

[peterlaursen]

Actually it is about ½ year ago that I read about the plans with FK's and MyISAM at the MySQL Forums. Oracle's purchase of InnoDB is only about 2 months. However the management at MySQL probably have been informed that something should happen. Most likely they have been asked too what they would pay!

[peterlaursen]

The restriction is a restriction with the InnoDB storage engine a such. Not the MySQL server and not SQLyog.

I know nothing about storage/indexing systems used by Oracle DBMS but actually Oracle just purchased InnoDB!!!

MySQL plan to allow for Foreign Keys with MyISAM. I believe it is in the roadmap for MySQL 5.1. So maybe in a year or so…

[peterlaursen]

Could you explain what you are missing then?

[Author]

Posts