Forum Replies Created
-
AuthorPosts
-
peterlaursen
Participantread here:
http://www.webyog.com/forums/index.php?sho…st=0&#entry6846
Simply delete the charset's definition from the SQL-file using Notepad or another editor.
Quote:Can SQLyog please be made to ignore the charset for databases that don't support charset?it is not a SQLyog issue – it is a MySQL issue!
ParticipantYes it does <_< and the funny thing it no matter if you choose “structure only”, “data only” or “structure and data” it is always the “data only” that is highligted. It should be the one executing in my opinion. Is a GUI-cosmetics only. Nothing wrong with the file!
ParticipantI'll warn you about another problem that might occur too!
You might experience that the INSERT-statements in the sql-file is too long for the server-configuration at the webhost.
The MySQL server has a setting namend MAX_ALLOWED_PACKET, and if you use HTTP-tunneling there is as a MAX_POST setting with php configuration. Both settiings must be greater than the memory-buffer needed to execute the INSERT-statements. And those settings are probably not under your control! And they are often very low at webhosting …
From the export-dialogue in SQLyog you can then uncheck the “create bulk insert statements” and each row of data will have its own INSERT-statement. It is a little bit slower to import this way, but you might need to do it!
Participantprobably the MySQL server versions are “too much different” for “copy to other host” and the “sync-tool” to work. It looks like you are running 4.1 og 5.0 on local and on the webhost 3.23 or 4.0. Charset management (and localization issues in general) with MySQL is fundamentally different up to version 4.0.x and from 4.1.x. I believe that simply is the problem
However the dump-upload should work if you simply delete this
“DEFAULT CHARSET=latin1”
from the definitions section of the sql-file – provided that the server version running at the remote host supports INNODB tables.
You could also delete all this
“ENGINE=InnoDB DEFAULT CHARSET=latin1”
and tables will be created using the default storage engine (probably MYISAM) and charset (probably utf7) on the remoste host. But of course if you need INNODB/Foreign Keys you should not delete the INNODB specification
Don't delete the “;” character that teminates the create statement!
ParticipantQUOTEMy question is therefore:- Is there a way to join the timestamp value with text and numeric data at insert time via php?there is absolutely no correlation between SQLyog an the scripting language php. But a PK can be built from several fields – just coneect to the database(s) with SQLyog and select the fields that you want to use for the PK from ALTER TABLE functionality.
Myabe you can use something form this article
http://www.webyog.com/forums/index.php?showtopic=1507
there is a simple trick desribed here
QUOTEIf you want to be 100% sure that you don’t lose newly created data with the sync and also want to ensure that all data is synced you can build a PK composed of any field(s) that you would normally for a PK AND any other having different DEFAULT value on the two hosts. It doesn’t matter if that variable is a number-type and has defaults for instance “0†and “1†respectively or it’s a char-type having defaults for instance “webhost†and “localhostâ€. With this new PK “conflicting primary keys†will not occur during sync, since the same value for the PK would never exist on both hosts with data created since last sync. The host-specific part of such PK will always tell on which host the row was first createdParticipantThat also raises (again!) the discussion of what to do with the “personal folder”.
I'd propose that the “personal folder” is replaced with a “favorites” folder, just like IE and many other programs. The “favorites” folder could be a subfolder to the SQLyog installation folder, but should not contain files, only .lnk's.
I know that is probably not for 4.2, but let us hear your opinion!
Participanthmmm …
it could be that you must use a suffix with the user name.
For instance at my webhosting let's say my userid is 'asdfgh'. The name of the users at my DB is prefixed with that. I can create user 'peter' in control panel but to connect with that user I must use string 'asdfgh_peter'. Database names must be prefixed the same way.
Participantseems to be this one.
It sure is a user-authentification or -privilege problem
ParticipantQuote:I can't connect with Direct connectionI get the Error No 1045
I don't remember error no.'s!
What is error msg in txt?
If could also be that your webhosting has blocked the MySQL port.
Then you cant connect directly from remote.
Then you must use either HTTP- or SSH-tunnelling
ParticipantQuote:With phpMyAdmin all works fine so I don't understand!phpMyAdmin is running on the server itself so when connecting to MySQL with phpMyAdmin you are NOT connecting from a remote host! The user that you are connecting with maybe has no privilige to connect from remote.
Note that MySQL by default only gives access for users to connect from localhost. If you want to give some user access to the server from another host you must specify the hostname (an ip or a URL). You can use the SQL wildcards “%” and “_” (but not windows wildcards like “*”). Simply giving permission for a user to access the server from “%” means from everywhere.
There must be som kind of “Control Panel” application at your webhost from where to configure users.
Participantif the proces proces shall be completely automated you can add an ALTER TABLE adding an index at the end of the sequence. To get the exact syntax do it manually once and copy SQL from HISTORY-pane.
it is something like
ALTER TABLE `mydb`.`mytable` add index `indexname` ( `column(s)` )
ParticipantI'm ashamed! 🙁
Totally overlooked that that setting was there too in export tool.
ParticipantTried with this DB (shown i Access “relations” view”).
here you will have to set FOREIGN KEYS CHECK = 0 in Backup tool.
So it probably was a mere coincidence that with only two tables they came in the right order.
Actually I don't know if this is a bug. There can be DB-design's where no sort order will do and you will have to use FOREIGN KEYS CHECK = 0. A solution could be always using FOREIGN KEYS CHECK = 0 with InnoDB from export tool. However then it must be user's responsibility to always exprot related tables! Quite a difficult choice I believe …
I have the Enterprise version 😀
ParticipantI sent the two small files to Ritesh instead.
ParticipantI can confirm this bug.
Actually I tried to attach a small zip illustrating it, but it won't let me!
However I experience that the backupwizard sorts the create statements correctly. Further there is here a CONSTRAINTS_CHECK = 0 -option. But of course this is only available in Enterprise version.
-
AuthorPosts
