[peterlaursen]

@rohit …

actually I have been discussing this with Manoj recently.

I think we should have a 'clone user' functionality.

In these organisations where every person has his/her individual MySQL user ID it is quite a monkey job to create 2000 user profiles – of which there are only 12 essentially different ones! Especially where there are hundreds of databases and there is made intensive use of table and column privileges.

With a 'clone user' functionality people could easily build their own 'templates'.

What do you think people do now? I'll tell you:

1) SELECT * from mysql.users where …

2 ) export as SQL .. edit a little .. import again ..

3) 'flush privileges' ..

voila! … 100 times faster than using SQLyog GUI when creating a user identical or almost identical to an eixsting user.

.. and I do realise that this is not quite what TG had in mind! You won't have to tell me

[peterlaursen]

confirmed!

Incredible that nobody and not ourselves either noticed before.

Same thing for views!

[peterlaursen]

Well yes .. and .. no

Wiht MySQL 5 you still need to have created an index on the referenced column (in the other table) in advance. But the index on the FK-column itself is created by MySQL itself if it is not there in advance.

So when you try to reference a column in another table that has no index, building the FK would fail and an error msg. should show. But it could be more specific.

But actually SQLyog DOES NOT prevent building the FK if the index on the FK-column is not there!

[peterlaursen]

Please read this FAQ:

http://webyog.com/faq/16_66_en.html

It depends on

* the connection method (direct connection, SSH or HTTP)

* the routing systems that are used on the remote network

[peterlaursen]

2 minutes only? don't you have some decent tables ? 😛

I think you have a point!

[peterlaursen]

OK. you are welcome 🙂

[peterlaursen]

SQLyog does not log individual INSERT, UPDATE and DELETE statements.

If you have access to server configuration you can enable the server 'general log' by inserting something like

Code:

log = [full_path_to]mylog.log

in the [mysqld] section of the server configuration file. Don't forget to restart the server.

BTW: I delete the other (identical) post of yours. It is VERY bad 'netiquette' to post dublicate posts! How do you think it is possible to follw a discussin that spreads over several posts?[code]

in the [mysqld] section of the server configuration file. Don't forget to restart the server.

BTW: I delete the other (identical) post of yours. It is VERY bad 'netiquette' to post dublicate posts! How do you think it is possible to follw a discussin that spreads over several posts?

[peterlaursen]

It is true that subselects can be slow, if it results in large temporary tables.

However this temporary tables has only 5 rows!

I data shall be SYNC'ed with SQLyog it is not alwas a good idea to have a auto-icrement PK.

Even the 'smartest' database experts disagree on that!

I think you coul JOIN the table to itself as well.

One day when I get the time I will study joins in details!

I think the issue here is the SCHEMA itself. When there is so much troubl with a query it often indicates that the SCHEMA design is not optimal! I think it was an option to have data in two InnoDB tables related with Foreign Keys – but I do not fully understand the data.

The reason the my proposal did not work was because of the max() aggregate function. Int would have worked with count().

[peterlaursen]

OK ..

I do not recall every detail here and am uncertain why 5.17 works and 5.13 not.

But probably there was some issue with the character set for the connection that was not correctly implemented for the export tool!

BTW: I apologize for all my typo's. Really looked bad last time! 😮

[peterlaursen]

Bikas was right all the time it seems!

Code:

select magasin,article_nt, date_tarif , marge_theo from tar where date_tarif in (select max(date_tarif) from tar group by article_nt);

Actually I think MySQL internally builds a temporary table to hold the values for the sub-select and next does a query with JOINs on both tables (yours and the temporary). But it is simpler than specifying the JOINs yourself!

The subselect simply returns:

max(date_tarif)

---

2006-05-02

2100-06-03

2005-07-01

2006-04-25

2002-08-08

But now .. you may have more records with the same value for date_tarif and article_nt! Considered how to handle this?

Your current Primary Key does not protect against this – it is defined as:

PRIMARY KEY (`MAGASIN`,`ARTICLE_NT`,`DATE_TARIF`)

so it is possble to create records with the same value for date_tarif and article_nt if only another column is different!

You could build a UNIQUE KEY with date_tarif and article_nt to prevent this!

[peterlaursen]

It must be then:

Code:

select magasin,article_nt, max(date_tarif) as date_tarif, marge_theo from tar group by article_nt order by date_tarif asc

'group by article_nt ' assures that only ONE row with identical article_nt value is returned

'max(date_tarif) as date_tarif' assures that this ONE row will be the one with the biggest value for data-tarif

' order by date_tarif ' specifies in what order the ONE row with max(date_tarif) for each existing value for article_nt is displayed

Actually I have a VIEW definition quite similar:

Code:

CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `albumtitel` AS select
count(*) AS `##`,`mp3_filer`.
`Albumtitel` AS `Albumtitel`,`mp3_filer`.
`Genre` AS `Genre`,`mp3_filer`.
`Kunstner` AS `Kunstner` from `mp3_filer`
where ((`mp3_filer`.`Kunstner` is not null) and (`mp3_filer`.`Albumtitel` is not null))
group by `mp3_filer`.`Albumtitel`,`mp3_filer`.`Kunstner`,`mp3_filer`.`Genre`
order by `mp3_filer`.`Albumtitel`,`mp3_filer`.`Kunstner`,`mp3_filer`.`Genre`

It builds an 'overview' from my music library based in Album Title. See attached.

You should export the table as SQL! because SQL can be executed and every bit of definition and data is inserted in one single operation!

🙂

[peterlaursen]

@violante

next time please post a COMPLETE DUMP ready for import! Why should I waste time TYPING when I could easily import your DUMP in a seconds.

[peterlaursen]

The order of ORDER BY and GROUP BY is wrong in your statement! GROUP BY comes first!

http://mysql.com/doc/refman/5.0/en/select.html

I do not understand what you mean by: “I would like to have it grouped by article_nt …” There is only ONE row with the date_tarif max-value, so I do not understand what to GROUP.

Please help us to understand:

* DUMP/export the table so that we have EXACTLY the same – the same data types etc.

* Write the output here EXACTLY and COMPLETELY as you want it!

[peterlaursen]

Really looks like some character set 'mess-up'

First: we need to be sure if it woked OM with a previsous version. Which version do you think worked? Do you still have the installer?

Second: It is important here to know if it is MySQL version 4.0 or 4.1. Execute “select version()” to find out! Wiht tell the exact version!

Third: Let us see the returns of ” show variables like '%character%' “

I think you mean SQLyog 5.13.

(if not I would like to know from where you got 5.31 🙂 )

Please try latest 5.17 RC too!

[peterlaursen]

Quote:

No, what Iwould like is the max. value for date_tarif and the marge_theo value of the row that corresponds to max. value for date_tarif

wel then I think that my first 'shot' will work!

Select * from tar ORDER BY date_tarif desc limit 1

.. will sort the rows in descending order by date_tarif and only 1 row will be shown – the one with the higest value of data_tarif. I see no reason to use aggregate functions ( max() ) here! Doing so will put an additional load on the server as the optimizer rewrites the query internally and probably needs to build temporary tables.

[Author]

Posts