Unsupported Screen Size: The viewport size is too small for the theme to render properly.

Forum Replies Created

Viewing 15 posts - 3,241 through 3,255 (of 7,398 total)
  • Author
    Posts
  • in reply to: #25928
    peterlaursen
    Participant
    PerFredlund wrote on Feb 14 2008, 02:43 PM:
    Hi 🙂

    Yet another option is to create a trigger, for instance an on after update trigger. This could be set to update the time-stamp field as required. This would work regardless if you work from SqlYog or from some other scripting solution or other client.

    An advanced solution might be to come up with some stored procedure doing essentially the same as the trigger, assuming you are using MySql 5+ 🙂

    – Per

    But a trigger cannot write to the table ON which it is defined!

    in reply to: #25925
    peterlaursen
    Participant

    OK … we have no place where to display the “on”update” clause. But that is a display issue only. OBJECTS tells the *full* CREATE STATEMENT.

    We cannot display “CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” in the default column, for two reasons actually

    1) that string is not a valid DATETIME string constant. A default must be valid for the column type! “on update …” is NOT a *default* setting – it is an *on update* setting!

    2) SHOW FULL FIELDS only (and accordingly) only returns CURRENT_TIMESTAMP as default

    We also do not want to add yet another column to the ALTER TABLE grid for this purpose alone.

    Once this is understood I think it is more a cosmetical than a practical issue. You can ALTER other columns of the table and the TIMESTAMP does not change.

    We will not change anything at the moment. Anyway one day some good idea may come!

    in reply to: #25923
    peterlaursen
    Participant

    I cannot reproduce that latest issue reported.

    With this table difinition

    CREATE TABLE `tstest` (

    `id` bigint(20) unsigned NOT NULL auto_increment,

    `ts` timestamp NOT NULL default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP,

    PRIMARY KEY (`id`)

    ) ENGINE=InnoDB DEFAULT CHARSET=utf8

    the ALTER TABLE GUI displays this:

    [attachment=828:alter.gif]

    Please tell SQLyog version and server version!

    And please post a similar example: CREATE statement (from OBJECTS tab) and screenshot.

    in reply to: #25921
    peterlaursen
    Participant

    if you do like Per proposes – using PHP “time()” function you should be aware that the MySQL “time()” function is different. It will need two arguments (starttime and endtime) and will calculate the difference.

    The MySQL “unix_timestamp()” function is (when used without arguments) similar to PHP “time()” function. This one can be used from non-PHP clients. But MySQL has no automatic 'ON UPDATE' option for plain string and number types!

    in reply to: #25841
    peterlaursen
    Participant
    in reply to: #25840
    peterlaursen
    Participant

    OK .. we should pop up a dialogue box “SQLyog will need to retrieve data from the server again – do you want to save your changes?”!

    It is correct that not all users will realize that sorting in the DATA tab will send a new SELECT to the server and not automatically INSERT/UPDATE before doing so.

    in reply to: #25918
    peterlaursen
    Participant

    The draft text for this FAQ is ready. FAQ will be updated later tonight. But you can read the text here:

    How do I define TIMESTAMP properties with SQLyog.

    *************************************************

    The TIMESTAMP datatype is the MySQL datatype that causes most problems. Basically there are three reasons for this

    * the implementation for the TIMESTAMP type was changed between servers 4.0.x and 4.1.x. What used to work with old versions may not work with recent versions.

    * The CREATE/ALTER TABLE statement supports (with servers >= 4.1) a parameter for the TIMESTAMP type that is not supported with any other datatype. That parameter is the 'ON UPDATE' clause. As a value for this clause only 'CURRENT_TIMESTAMP' (and the synonyms 'now()' and 'localtimestamp') can be used.

    * The MySQL server (also with servers >= 4.1) 'silently' transforms some TIMESTAMP definitions specified by user:

    1) 'TIMESTAMP NULL' becomes 'TIMESTAMP NULL DEFAULT NULL'

    2) 'TIMESTAMP NOT NULL' becomes 'TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP' provided that the 'CURRENT_TIMESTAMP' keyword is not used for a previous TIMESTAMP declaration in the table definition. The 'CURRENT_TIMESTAMP' keyword (and the synonyms) can only be used once in a table definition.

    For details refer to:

    http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

    The SQLyog facility to define TIMESTAMPs (the CREATE TABLE and ALTER TABLE GUI's) is designed to work with this. You can define TIMESTAMPS like

    a ) NULL DEFAULT NULL

    b ) NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

    c ) (NOT) NULL DEFAULT 'yyyy-mm-dd hh:mm:ss'

    d ) (NOT) NULL DEFAULT CURRENT_TIMESTAMP

    Details on how to do this:

    a ) Just define the column type and name/identifier and do nothing else with that row of the CREATE/ALTER GUI (leave the default column empty and do not check the NOT NULL checkbox)

    b ) Just define the column type and name/identifier and check the NOT NULL checkbox and do nothing else with that row of the CREATE/ALTER GUI (leave the default column empty)

    c ) Just define the column type and name/identifier and enter the default in 'yyyy-mm-dd hh:mm:ss' format. You may check the NOT NULL checkbox or not and the column will be created accordingly.

    d ) Just define the column type and name/identifier and enter “CURRENT_TIMESTAMP” (without quotes) in the default column. You may check the NOT NULL checkbox or not and the column will be created accordingly

    Note that only 'CURRENT_TIMESTAMP' (and the synonyms 'now()' and 'localtimestamp') entered in the default column of CREATE/ALTER TABLE GUI's will be treated as keywords. Everything else will be considered literal strings and any such literal string that is not recognized by the MySQL server as a valid DATETIME specification will return an error! Refer to http://webyog.com/faq/content/8/99/en/does…l-keywords.html for more details of KEYWORDS support in SQLyog.

    One more detail about the situation described with point 'b' above is that if a column already exists that uses the CURRENT_TIMESTAMP keyword the server will create as “NOT NULL default '0000-00-00 00:00:00'” if the session sql_mode allows for it. Currently SQLyog always uses the '' (empty) sql_mode for its connection/session, unless changed by user. For such sql_modes where ZERO-dates are not allowed setting an explicit default is required (and also note that ZERO-time – as opposite to ZERO-date – is perfectly legal in every sql_mode – however some specific server versions are buggy in this respect!)

    With the CREATE TABLE and ALTER TABLE GUIs, however, you cannot create TIMESTAMP definitions like

    e ) NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP

    f ) (NOT) NULL DEFAULT 'yyyy-mm-dd hh:mm:ss' ON UPDATE CURRENT_TIMESTAMP

    Those constructions – that are both very rare – can be handled from the editor. Users who want to use those will undoubtedly also understand. We do not find it justified to add yet another column to the CREATE TABLE and ALTER TABLE GUI's that can be used only for a single datatype and can only have one single value when it is not really required.

    With MySQL version 4.0 and before it is a little more simple. The server will create the TIMESTAMP as defined by user – simply. But the first timestamp in the table definition will be handled by the server like described here: http://dev.mysql.com/doc/refman/4.1/en/tim…mp-pre-4-1.html

    “Automatic updating of the first TIMESTAMP column in a table occurs under any of the following conditions: …The column is not specified explicitly in an UPDATE statement and some other column changes value…” In other words: with those server versions the first timestamp will basically be treated like 4.1+ TIMESTAMPS defined 'NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP'. But with those versions this is not implemented in the CREATE STATEMENT for the table. The server simply 'silently' treats the first TIMESTAMP like this! Also note that with those versions a DATETIME string constant is 'yyyymmddhhmmss' only (no “-“s, no “:”s and no SPACE). Further the parameters 'default CURRENT_TIMESTAMP' and 'on update CURRENT_TIMESTAMP' does not apply to those versions. They will return a server syntax error.

    in reply to: #25917
    peterlaursen
    Participant

    btw: we have another issue recorded here:

    http://code.google.com/p/sqlyog/issues/detail?id=488

    I added details about this post!

    in reply to: #25916
    peterlaursen
    Participant

    Additionally please consider:

    As default for a TIMESTAMP you must specify a TIME value (like 'yyyy-mm-dd hh:mm:ss').

    The literal string “CURRENT_TIMESTAMP on update current_timestamp” is not a valid time value just like 'Peter,Mahesh&uyuni' is not either (but “CURRENT_TIMESTAMP” alone is!) !

    MySQL docs:

    http://dev.mysql.com/doc/refman/5.0/en/datetime.html

    http://dev.mysql.com/doc/refman/5.0/en/timestamp.html

    The server (servers >= 4.1) automatically transforms

    1) TIMESTAMP NULL >> TIMESTAMP NULL DEFAULT NULL

    2) TIMESTAMP NOT NULL >> TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP (for the first timestamp column where 'CURRENT_TIMESTAMP' keyword is used).

    Please simply check the NOT NULL checkbox and nothing else. That will create a “TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” if “CURRENT_TIMESTAMP” keyword was not used before in the table definition. The server will take care of that. That TIMESTAMP implementation in MySQL is used by SQLyog CREATE/ALTER TABLE GUI. But of course you may write the full CREATE/ALTER statement in the editor yourself.

    I will start writing that FAQ today and post the link (tonight probably)!

    in reply to: #25915
    peterlaursen
    Participant

    To create a TIMESTAMP defined like “DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP”.

    .. you simply check the NOT NULL checbox and nothing else. Then the server will make it “DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” (provided that the keyword CURRRENT_TIMESTAMP has not been used in teh table definition already – it can only be used once). Try yourself to create a TIMESTAMP NOT NULL from the editor, next SHOW CERATE TABLE and you will see what the server did!

    The 'ON UPDATE CURRENT_TIMESTAMP' clause does not at all apply as default! In other words “DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP” is not a single clause but two: “DEFAULT CURRENT_TIMESTAMP” and ” ON UPDATE CURRENT_TIMESTAMP”. ON UPDATE clause only applies to timestamp types and we will not need a facility to handle it in SQLyog because the server does automatically. You may of course use the editor of course!

    I disagree with my colleague that there is something to be fixed here. But maybe something to clarify! I think I should write a FAQ on how to operate the CREATE/ALTER TABLE GUI with diffferent TIMESTAMP definitions!

    in reply to: #25910
    peterlaursen
    Participant

    Please try Notepad instead of Wordpad. I am 95% sure that Wordpad has a problem with utf8. Also I am not sure about Excel (btw: what Excel version?) and utf8.

    Try open in Notepad .. save as and select ANSI or 'unicode' ('unicode' = Microsoft term for what in unicode standards is 'little endian UTF16'). After saving try open in your preferred programs!

    in reply to: #25908
    peterlaursen
    Participant

    Also, if you open in Notepad, 'save as …' and select ANSI, will it then display in the program you use?

    in reply to: #25907
    peterlaursen
    Participant

    I what program do you display? Does this program support unicode?

    See attached how it displays for me in Notepad. I think the accents here are OK?

    [attachment=825:csv.gif]

    Also the save dialogue tells that the file is utf8-encoded. SQLyog will save files as utf8-encoded from version 6.0.

    in reply to: #25905
    peterlaursen
    Participant

    You should try version 6.16 RC2!

    Changelogs for this version:

    http://www.webyog.com/forums//index.php?showtopic=3762

    Quote:
    * Added an option to specify CHARSET parameter for CSV-import. Unicode (UTF8 and UCS2/UTF16) will be detected automatically. For files encoded with a non-unicode encoding, user must select the MySQL charset used for the import. Alternatively there is a option that will not use the CHARSET parameter at all (= old program behaviour).

    I think the file you have is utf8-encoded and the server has a non-unicode default charset (latin1). Accented latin characters are 2 bytes in utf8 and if the CHARSET parameter is not specified every byte will be mapped to a latin1 character.

    If this will not help, please zip and attach a small example of such file and tell the server version!

    in reply to: #25776
    peterlaursen
    Participant

    We have released 6.16 RC2 with the fix for the issue reported by Fuzzy, as well as similar issues.

    There are still 'corners' of the program code where we will need to check if the reconnect wrapper is called properly every time. This will be completed in 6.2.

    But with common GRID operations using this version this error should not appear again.

Viewing 15 posts - 3,241 through 3,255 (of 7,398 total)