[peterlaursen]

But this:

QUOTE

#1235 – Cannot call SHOW INNODB STATUS because skip-innodb is defined

could indicate that you simply need to comment out the line reading “skip-innodb” and stop and restart the server. That would be the easy way. If the binary code for the InnoDB storage engine is there!

[peterlaursen]

Well …

Is the MySQL database at your local machine or at an ISP or some other remote host ? The MySQL configuration file is normally named my.ini on Windows and my.cnf on Unix/Linux. And it is where the server is! So if you are connecting to a remote host you don't have any my.cnf file!

Some (cheaper) ISP's disable support for InnoDB. I don't know it this is the case or if it is an issue with (the availaable version of) phpmyadmin.

But this is the Webyog forum. Webyog has its own product SQLyog. We don't like people using phpmyadmin 😮

Did you try SQLyog ?

This

QUOTE

the file(my.cnf) in my computer, the type is SpeedDial, which I don't know how to open it to edit … when I installed MySQL(I used XAMPP)

actually confuses me!

I know about these “bundled Apache/php/Mysql packages”, nut never heard about xampp before, but googled and found this http://www.apachefriends.org/en/xampp.html . I don't recommend it! I would get Apache from Apache.org, PHP from php.net and MySQL from mysql.com. Maybe this “bundle” uses a mySQl server binary without support for InnoDB ?

Some specific answers:

1) on Windows and most Linux desktops you can open any file no matter its association by rightclicking .. open with. Open with Notepad (or another texteditor) and save as my.cnf/my.ini NOT mycnf.txt or my.ini.txt !

2) Get the program “MySQL Administrator” from http://dev.mysql.com. It lets you work with the server parameters from a GUI. But I can't be sure that it will work with this bundled verson of MySQL ..

I am not quite sure, but I believe I understand that you installed the XAMMP bundle on a local windows machine, and you don't use other servers? Please confirm!

[peterlaursen]

@jexxer

BTW – a mysql full-text index only indexes character sequences of 4 or more characters! If it should go lower the index would take more space than the data!

[peterlaursen]

it does not crash here, but it is not perfect either!

This table def. is used for the test

Code:

CREATE TABLE `tablename1` (                
             `id` bigint(20) NOT NULL auto_increment,  
             `t1` varchar(40) default NULL,            
             `s1` set('a','b','c') default 'a',        
             `s2` set('1','2','3') default NULL,      
             `e1` enum('red','blue') default NULL,    
             PRIMARY KEY  (`id`)                      
           ) ENGINE=MyISAM DEFAULT CHARSET=latin1  

1) first a cosmetic

pic2 shows that it displays only “ ( ” where it should display ” (NULL) “

2) and a more serious issue.

pic1 shows some garbage! There should be only two rows, and the 'a' on the last line should be in second row. A CSV-export drops

Code:

“1”,”t\t”,”a”,”2″,”blue”
“2”,””,\N,”1,2″,”red”

.. so the last 'a' does not seem to be in the database at all? I can shut down SQLyog and restart it and data are still displayed like in pic1. How could SQLyog enter dataa into a row without the PK-field having assigned a value?

and BTW: actually I have seen that before with some 4.2 release that some data from some rows get stored or displayed (?) wrong.

[peterlaursen]

Mark answered me by mail this:

Quote:

thanks for your reply

I put an info file up,

http://test.sigmahotels.com/info.php

to display the PHP settings,

you could be right, its a php timeout, I just wondered why SQLyog reports the export has been completed, I would expect it do detect the error, and report it.

erm to give you more info

I am using the free version, as a client side GUI, the database is on my hosting provider's server, I have a 256/64 ADSL connection, living Cyprus so its not so high tech here 🙂

I also get a lot of lost connection to mysql problems, i managed in the end to upload the table, my using a previous dump I had taken with mysql front, and editing that, it was about 6 meg, 25,000 rows, uploading with sqlyog caused many failures due to lost connection, however by using the error log, I was able to edit the .sql file to remove rows already inserted and continue with a smaller file, doing this about 5 times I managed to get all the data in. Of course this won't help me taking large dumps, I have a similar problem with phpmyadmin, it times out after 300 secs so I get a partial dump. As my provider doesn't give SSL access the option of using command line SQL commands to take a dump to the server and zip it and ftp it down is not available, but I do need to find a way to take full backups as this a commercial site and the hosting company only does backups weekly, maybe I need a better hosting provider 🙂

Thanks for you help anyway, all suggestions welcome

@mark – please use the Forums – simply because then not only one person will be able to help you then!

[peterlaursen]

We need more information to help you with this!

But my best guess is that you are connecting to a MySQL server at an ISP, using HTTP-tunnel. Is it so ?

In that case it could be because of a not very generous PHP comfiguration at the webhost! Do you have some means to list your PHP configuration parameters ?

But … please … (much) more info!

[peterlaursen]

It is a 'strict mode' issue!

See:

http://www.webyog.com/faq/28_72_en.html

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

I cna reproduce this:

Code:

set sql_mode = 'STRICT_ALL_TABLES,NO_ZERO_IN_DATE';
load data infile 'c:\documents and settings\peter\skrivebord\test.csv' into table tablename1 FIELDS TERMINATED BY ',' ENCLOSED BY '”' LINES TERMINATED BY 'rn' (id,t,ts);

returns

Quote:

Error Code : 1292

Incorrect date value: '' for column 'ts' at row 2

(361 ms taken)

Either use latest SQLyog RC or issue the statement

Code:

set sql_mode = '';

manually

[peterlaursen]

Quote:

then i move the data from server 2 to the new server it deletes the data from server 1 that is stored on the new server.

HOW do you 'move' data? Importing an SQL file ? if that is the case you should omit the “drop .. ” statements.

But I dont understand how this is related to SJA?

Or meaybe it is not?

Did you consider using the sync-tool?

[peterlaursen]

Quote:

nother question i have is how can i not delete the data that is being backed up from another server..

Is it SJA for Windows or SJA for Linux that you use?

With the SJA for Windows coming with SQLyog 4.2 there is an option to use a timestamp for the backup file or the folder where it is stored. And to store in a subfolder.

But what does this

Quote:

im trying to combine data on two different servers

more precisely mean ?

[peterlaursen]

Nobody seems to have the answer.

And Ritesh & Co. are working hard with the last issues with 4.2 I know!

I remember not so long ago (a couple of months) we had a similar issue. I don't think there was any conclusion.

But it must be a network configuration issue at the remote host. Obviously the SSH-server and the MySQL server are able to exchange authentification information. And also obviously SQLyog is able to send that autenfification information too when testing the connection. So I think connection is established too. But after that some data don't reach where they should reach at. Either the queries don't reach the mySQL server or the result of queries dont reach the SSH-server or the SSH-server fails to transmit data.

Actually the SSH client that comes with SQLyog is the same plink-executable as Putty uses. What can you actually do from Putty? It could be that only one line of information is transmitted …

If you wait a VERY long time does still nothing happens? Not even a time-out? Timeout with a standard MySQL installation is very high (several hours or actually days I believe), but most ISP's set it considerably lower!

I think this is an issue for the support/helpdesk personel at the remote host (I suppose it is an ISP ?!).

[peterlaursen]

Quote:

but how do I tell yog which row ID to delete up to

Well …

you can simply edit your 'notify.xml' file before running the job. The SQL-statment is hardcoded inside it. You will then have to open your database to see what value to use.

But the timestamp is 'smarter' I think because you can write

Code:

delete from xx where myts < 'yyyymmdd000000'

Timestamps are internally stored as 14 character strings so if

yyyy=current year

mm = current month

dd = current day

the statement deletes everything from before last midnight. 000000 is 0 hours 0 minutes and 0 seconds.

Try creating a simple job with an unimportant local database, and you'll see that it is pretty simple!

[peterlaursen]

And …

Quote:

Is is possible to truncate only up to a specific row ID instead?

If the Primary Key is an autoincremented integer it is very simple

Code:

delete from `dbname`.`tablename` where `id` < 1234

[peterlaursen]

I already have a DATETIME field

>>> how is it maintained/altered ? May you can use this one. If you have some application code updating that datetime.

But MySQL prior to 4.1 works this way with timestamps

Quote:

Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions:

You explicitly set the column to NULL.

The column is not specified explicitly in an INSERT or LOAD DATA INFILE statement.

The column is not specified explicitly in an UPDATE statement and some other column changes value. An UPDATE that sets a column to the value it does not cause the TIMESTAMP column to be updated; if you set a column to its current value, MySQL ignores the update for efficiency.

according to http://dev.mysql.com/doc/refman/4.1/en/tim…mp-pre-4-1.html .

But note that this applies to the first TIMESTAMP column in a table only! With first TIMESTAMP column in a table you don't need the 'on update' in the definition. It is implicit!

This is also the key to a workaround when updating rows with SQLyog. You can work from the RESULT tab (and omit the column in the SELECT statement copying data here) and not the DATA tab

[peterlaursen]

http://www.databasejournal.com has a MySQL category.

[peterlaursen]

BTW … I think this TIMESTAMP issue is the most important thing to fix with SQLyog.

ANYONE I know using databases professionally on corporate systems have that sort of TIMESTAMP field in EVERY table!!

It would not even be acceptable in a HighSchool project! 😛 😀 😀 😛

[Author]

Posts