Database

[Bug][java.sql.DriverManager.getConnection(Unknown Source)]

25-Jun-14 20:11

Rent a DBA.

Wrong is evil and must be defeated. - Jeff Ello[^]

Thong LeTrung25-Jun-14 5:40

Thong LeTrung

25-Jun-14 5:40

Hi Everybody,
I installed mysql on Fedora 20 and I can access to mysql with java on eclip
however I would like to access mysql from other machine and I have used Windows 8 to access to mysql on fedora

I tried to configure on fedora (virtual machine) : Edit my.cnf File. But I can not access to mysql
please see following photos
There is a photo on fedora

HTML

<a href="https://onedrive.live.com/?cid=9581B52553DE3427&id=9581B52553DE3427%215729&v=3">Fedora</a>

There is error on Windows OS

HTML

<a href="https://onedrive.live.com/?cid=9581B52553DE3427&id=9581B52553DE3427%215730&v=3" >Windows 8</a>

Thanks
Thong LT

Performance problem

sma123#23-Jun-14 7:25

sma123#

23-Jun-14 7:25

Hi Currently we are using crystal report to display the data whihc is internally fetching almost 55,234,22 records from DB. Since it the no of records are huge it is taking almost 7 minutes to display the report which is very poor performance in nature.

Can anyone suggest me some useful sites to make it faster and can recommend some free tool to measure the exact point which is causing the issue?

Thanks & Regards,
SMA

Eddy Vluggen23-Jun-14 7:38

Eddy Vluggen

23-Jun-14 7:38

sma123# wrote:
Since it the no of records are huge it is taking almost 7 minutes to display the report which is very poor performance in nature.

Stop right there; that is not poor performance. Something taking a lot of time does not mean that it does not perform.

What's the size of a single record? Just use Google to look up the size of each field that has no length. Now multiply that by the amount of records. Now take into consideration that it is a Crystal Report, not some text-only output.

You could start with pasting the query here; you'd also want to download a profiler for Sql, and/or use SQL Management Studio to see if you can optimize the query.

That's the fetching-the-data part. The other part would be getting the fetched data into a report-format. If you do not need all the fancy stuff that CR provides, then try writing a simple HTML-report.

Bastard Programmer from Hell Suspicious | :suss:

If you can't read my code, try converting it here[^]

David Mujica23-Jun-14 8:35

David Mujica

23-Jun-14 8:35

I agree with Eddy, this is not a performance problem, seems more like a poor implementation of user requirements.

Am I correct in understanding that the report fetches 55 million records, but does it display that much data ?

Displaying 55 detail records per page would generate a 1 million page report. Really ? Does the user need to see all of that data ?

Is there any way to create summary data at an off-peak time so the report can generate from that summary data ?

What about implementing an asynchronous report generator where the user selects the input parameters, then "kicks off" the report in the background. The report could generate an HTML or PDF report, then send an email to the requestor with a hyperlink to the report when it is complete.

You would be surprised how agreeable a user would be to a solution like this. They kick off a report, then they can move on to something else while it generates. The email tells them when it is done.

Hope these ideas help. Smile | :)

jschell25-Jun-14 12:22

jschell

25-Jun-14 12:22

David Mujica wrote:
I agree with Eddy, this is not a performance problem, seems more like a poor implementation of user requirements.

I think your answer is more correct however.

David Mujica wrote:
Really ? Does the user need to see all of that data ?

No user needs to see that. Even if they say they want to see it they in fact want to look something up and that is what one should do instead.

Eddy Vluggen26-Jun-14 0:25

Eddy Vluggen

26-Jun-14 0:25

More complete and definitely friendlier Big Grin | :-D

Mycroft Holmes25-Jun-14 0:31

Mycroft Holmes

25-Jun-14 0:31

That is not a report it is a data dump and should not be produced via a reporting tool. Dump the data via CSV direct to the hard drive and give it to the user unformatted.

As suggested, this is a lousy implementation of the user requirements, go back and find out what the user wants with that many records.

Never underestimate the power of human stupidity
RAH

A small naming problem

Jörgen Andersson22-Jun-14 22:23

22-Jun-14 22:23

What would you call a field that can have the states Public or Private, as in being a publicly or privately owned/operated business?

Wrong is evil and must be defeated. - Jeff Ello[^]

modified 23-Jun-14 4:47am.

Kornfeld Eliyahu Peter22-Jun-14 22:42

Kornfeld Eliyahu Peter

22-Jun-14 22:42

In programming, Public, Private (and friends) are called access modifiers, the state achieved by assigning one of them called accessibility level - that may give you a name, like:
AccessLevel
Accessibility
Access

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Jörgen Andersson22-Jun-14 22:49

22-Jun-14 22:49

Ah, I can see that I wasn't clear enough in my question (I've updated it).
Sorry about that.

I meant: What do you call a field that refers to privately or publicly owned businesses.

Wrong is evil and must be defeated. - Jeff Ello[^]

Kornfeld Eliyahu Peter22-Jun-14 23:00

Kornfeld Eliyahu Peter

22-Jun-14 23:00

Ownership?
I'm not sure now I understand you however...

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Jörgen Andersson22-Jun-14 23:34

22-Jun-14 23:34

Ownership it is.
Thanks!

Wrong is evil and must be defeated. - Jeff Ello[^]

How to connect with SSH Tunnel?

Shameel30-Jun-14 4:28

Shameel

30-Jun-14 4:28

I see you've already got Ownership as the answer. You can also try BusinessType?

Jassim Rahma22-Jun-14 21:30

Jassim Rahma

22-Jun-14 21:30

Hi,

I have just installed MySQL on Linux server.

The server has SSH on it as well.

Now I started facing problems (:

My .NET application is the problem....

How will connect using the MySQLConection if SSH is enabled on the server?

I am getting the following error:

Unable to connect to any of the specified MySQL hosts

when I try a basic MySQLConnection conection string.. anything special to add for the SSH?

Thanks
Jassim

Technology News @ www.JassimRahma.com

Re: How to connect with SSH Tunnel?

Simon_Whale22-Jun-14 22:02

Simon_Whale

22-Jun-14 22:02

I would have a look at connection strings website[^] this is the MySql page on there is a couple of SSL connection string examples

Every day, thousands of innocent plants are killed by vegetarians.

Help end the violence EAT BACON

Re: How to connect with SSH Tunnel?

Jassim Rahma22-Jun-14 23:13

Jassim Rahma

22-Jun-14 23:13

I tried this but same problem:

sql_connection = new MySqlConnection("server = 10.0.0.111; Port = 3306; Database = bizcards; Uid = root; Pwd = mujtaba; SslMode = Preferred;");

Technology News @ www.JassimRahma.com

Read Only Database

Bastien Vandamme22-Jun-14 18:43

Bastien Vandamme

22-Jun-14 18:43

For one of my project I need to create a read only database. I'm authorized to create INSERT and SELECT queries but not UPDATE or DELETE. To do that I suppose I need to add a DATE_STAMP column on each table and adapt all the SELECT to filter only the latest version of my record.

Is this realizable in term of performance, size of data, queries? Do you have suggestions, documentations, links about the subject.

Thank you,

Kornfeld Eliyahu Peter22-Jun-14 19:40

Kornfeld Eliyahu Peter

22-Jun-14 19:40

Of course you do not talk about 'read only' database as SQL means it - 'read only' is a state of the database when no-one can do any write functions, including insert.
What you are talking about is that your inserts are fixed and final, can't be removed or updated...
for that you have to learn about GRANT[^]/DENY[^]/REVOKE[^] of SQL permissions...
For instance, this will prevent from user MyUser to run UPDATE and DELETE queries on table MyTable...

SQL

DENY UPDATE, DELETE ON MyTable TO MyUser

I'm not questioning your powers of observation; I'm merely remarking upon the paradox of asking a masked man who he is. (V)

Jörgen Andersson22-Jun-14 20:21

22-Jun-14 20:21

B413 wrote:
Is this realizable in term of performance, size of data, queries

With proper indexing and eventually partitioning it won't be a problem.
Harddisk space is cheap nowadays.

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes23-Jun-14 1:13

Mycroft Holmes

23-Jun-14 1:13

I would call this auditing, unless I have the requirement completely wrong, where you need to track changes but retain only the current information in the table. This is one of the few valid uses for triggers spit.

Use a trigger to fire on inset/update/delete, on insert and delete grab the ID, you don't need each field value here, on update grab the before and after values, the ID on the user and shove them into an audit table.

If you are doing bulk inserts (etl data in) then turn off the triggers before processing.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson23-Jun-14 1:18

23-Jun-14 1:18

I think hell just froze over.
Did you just recommend the use of a trigger? (spit)

Wrong is evil and must be defeated. - Jeff Ello[^]

Mycroft Holmes25-Jun-14 0:26

Mycroft Holmes

25-Jun-14 0:26

Jörgen Andersson wrote:
Did you just recommend the use of a trigger

Yeah I feel dirty, had to do it again in the c# forum for the same reason ugh.

It must be the change in local, I'm on holidays in Cairns, it must be affecting my vitriol levels.

Never underestimate the power of human stupidity
RAH

Jörgen Andersson25-Jun-14 9:53

25-Jun-14 9:53

If it's of any consolation, I'm totally agreeing with you not just in general on this subject but also about this exception.

Wrong is evil and must be defeated. - Jeff Ello[^]