[peterlaursen]

@The Mask

Quoting:

Quote:

BIT: A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

With this meaning of the BIT type are you sure then that really is what you want and not a BOOL or TINYINT(1) ? If you want a behaviour as with previous MySQL versions you should in my opinion change column-definition to TINYINT(1).

As of now a BOOL and a TINYINT(1) are identical, but won't continue to be so, since MySQL write

Quote:

In the future, full boolean type handling will be introduced in accordance with standard SQL.

… hmmm … this must be for the FAQ when everything is sorted out!

[peterlaursen]

Did the same with MySQL 4.1.14

Here a BIT and a BOOL are both transformed to a TINYINT(1) by the server.

pic nr4 shows “create table”

pic nr5 shows how it displays in “alter table” when reopened

[peterlaursen]

Searching the MySQL docs:

http://dev.mysql.com/doc/mysql/en/numeric-type-overview.html

BIT[(M)]

A bit-field type. M indicates the number of bits per value, from 1 to 64. The default is 1 if M is omitted.

This data type was added in MySQL 5.0.3 for MyISAM, and extended in 5.0.5 to MEMORY, InnoDB, and BDB. Before 5.0.3, BIT is a synonym for TINYINT(1).

TINYINT[(M)] [UNSIGNED] [ZEROFILL]

A very small integer. The signed range is -128 to 127. The unsigned range is 0 to 255.

BOOL , BOOLEAN

These are synonyms for TINYINT(1). The BOOLEAN synonym was added in MySQL 4.1.0. A value of zero is considered false. Non-zero values are considered true.

In the future, full boolean type handling will be introduced in accordance with

standard SQL.

Changenotes of MySQL 5.0.3 say:

“BIT in column definitions now is a distinct data type; it no longer is treated as a synonym for TINYINT(1).”

http://dev.mysql.com/doc/mysql/en/news-5-0-3.html

SQLyog issues this SQL:

select * from `test`.`mytable` limit 0, 50;

RESULTS as shown in pic nr0.

compare the result of

select distinct * from `test`.`mytable` limit 0, 50;

RESULTS as shown in pic nr1.

With the table definition om pic. nr2

Server changes to as shown in pic nr3

… all testet with MySQL 5.0.12

[peterlaursen]

A solution could be an option to hide columns in DATA as well as RESULT-pane.

Hidden columns should not be INSERTED or UPDATED. Also there should be a general program option to hide “first TIMESTAMP in table definition”. The flag could be displayed in object browser for each column, and change when you Ctrl-click it for instance. A small green dot for visible, a red dot for hidden. Optimally these settings should be saveable.

Could something like that be done?

Of course the simple “select * …” would have to be replaced with select col1, col2, col3, col5 (NB! no col4!), col6 etc when sending data to data-pane.

Comments?

[peterlaursen]

Or

CODE

select a as mynumber ,c as mystring , date_format(b,'%W %d-%m-%y') as mytimeway from mytable;

returns

QUOTE

mynumber  mystring  mytimeway       

---

 

---

 

---

      1  aaaa      Monday 17-09-01 

      2  bbbb      Saturday 30-09-06

[peterlaursen]

Look at attached image.

DATA-pane shows data with standard formatting

In RESULT-pane you can format as you like with the date_format() function.

[peterlaursen]

The “other poster” was that me ?

http://www.webyog.com/forums/index.php?sho…view=getnewpost

Actually timestamp should not change UNLESS it is defined as “default CURRENT_TIMESTAMP on update CURRENT_TEMESTAMP”.

However after some version/release MySQL defines THE FIRST timestamp defined with a table that way! But there might be several timestamps in a table and no2 + 3 etc should not be affected.

It is a little tricky, because there should also be a way to overwrite a timestamp ON PURPOSE 😀

But personally I agree that this issue should be fixed very soon.

[peterlaursen]

It is a user authentication/privilege problem.

Did you read this FAQ item:

http://www.webyog.com/faq/23_18_en.html ?

I have sync'ed often between two PC's on a LAN with a router as DHCP server. No problems in that. Type of (physical) network does not matter either.

I believe in your case the problem that ROOT is only allowed to connect from 'localhost'. If not that is it, I don't understand you perfectly and I shall then ask you to explain a little more detailed (we love screenshots!)

You could install SQLyog on PC2005 too and change the root's hosts' access from 'localhost' to '%' or just '192.168.0.%' (in case you only want to connect from your LAN).

If you connect on port 3306 it is irrelevant if there is a webserver installed. But of course you will need it for HTTP-tunnelling. But not much use of that on a LAN!

[peterlaursen]

Quote:

How do I configure SQLyog to connect thru Proxy ?

You need to use the Enterprise version to connect through a proxy.

You enter details in the Connection Manager's tunnel tab … HTTP tunneling .. advanced.

Did you read the help file item “getting started” ?

[peterlaursen]

You can read about MySQL date and time types here:

http://dev.mysql.com/doc/mysql/en/date-and-time-types.html.

Basically these variables are stored a strings. A datetime as a 14 character string. A date as an 8 character string.

The functions to use on these variable types are described here:

http://dev.mysql.com/doc/mysql/en/date-and…-functions.html

(have a look in particular at the DATE_FORMAT(date,format) function)

… but actually any string function will work too.

I think you mismatch how a variable is stored and how it is displayed!

The formatting string to use is '%d %m %y' to get all three as numericals and year in two-digit-format and in order day-month-year (like today 19-09-05).

[peterlaursen]

And one detail more.

A TIMESTAMP defined with

Code:

default CURRENT_TIMESTAMP on update CURRENT_TIMESTAMP

Shall always be left untouched by any INSERT or UPDATE statement.

The idea with this definition is to let the server handle it!

[peterlaursen]

Proposal for a solution:

Write only:

Code:

insert into `test`.`tablename1` ( `a`, `c` ) values (  2,  'ff' );

If a column has a DEFAULT and input cell in DATA pane is empty then there is no use in writing anything, when inserting data. The server handles it!

I a column has NO DEFAULT and input cell in DATA pane is empty then probably it should be left as NULL.

This must apply to all datatypes, I think?

There is a problems with strings. Does an empty cell mean NULL or empty string? I think the best solution is that the empty row at the buttom for new data display DEFAULT if there is one or NULL if there is no default.

hhmmmmmm …

NB: Edited!

[peterlaursen]

OK … I also justed worked a little bit more on it.

I believe it was a problem with the CREATE TABLE and ALTER TABLE -panes, that we were discussing about a year ago and that was (more or less) fixed.

As far as i remember the ALTER TABLE pane replaced “default CURRENT_TIMESTAMP” with ” default 'CURRENT_TIMESTAMP'”, but that does not happen anymore.

And there might be details that I don't remember. I can't find the old threads in the Forums.

[peterlaursen]

I believe the solution with MySQL is to use a TIMESTAMP variable with “default CURRENT_TIMESTAMP” with the column definition.

The date is then created from this variable with the date() function. like:

select date(column_name) from table_name;

[peterlaursen]

You won't “transfer files from SQLyog” to access a MySQL server. SQLyog is a client. SQLyog has no database files of its owm. It is not like Access.

You also do not open port 80 to use a webbrowser! It is accurately the same!

[Author]

Posts