« first day (2104 days earlier)      last day (1879 days later) » 

12:01 AM
Good day... Gotta a quick question here if anyone's around...
well actually might be a bit more than a quick question... I've just got a mssql database that comes from 1988 with several transitions from Dataease into access into mssql... and I need to build a new frontend but I noticed just now... there no fk constraints...
so my question is, I guess... where should I start? Or under some very special circumstance should I preserve this beast?
 
12:19 AM
@NelsonCasanova is there any documentation?
is there code available from any application that access the database?
 
There's only a somewhat old Access frontend... ADE file
some kind of compiled access file, can only see the tables, so no good... thats it pretty much... by querying a bit I can tell there's all sorts of evil in this thing...
Im a bit lost, Im thinking maybe I can handpick the biggest tables and more important ones and recreate them on a new database, import one by one from the ones that should be core (Personal, Payments, etc) and see how that goes... or maybe not :s
 
How big is the design, how many tables?
Are they named sensibly (like Personel, Payments you mentioned) and the columns?
I had a friend who in his first job, he had to use the Access databases made by the boss, which were all named George-1, george-2, etc.
The same names for tables and columns ...
The boss name was George
Everything was George-X in tens of dbs
 
@ypercubeᵀᴹ hahaha omg that is hilarious
53 tables, about 10 of them seem to be main tables and I've got 2 that hold about 80% of the records... I can spot about 4 different naming conventions...
but yeah, one could say its not that hard to spot the main ones
 
Doesn't look so bad.
If there columns like PersonID, PaymentID, ..., you can conjecture that they are FKs
and then test for incosistencies
I don't know of any tool that can help you with automating this though
It wouldn;t hurt to post a question at the site. Someome might know
I rememebr a similar question but I can't find it now.
 
12:36 AM
@ypercubeᵀᴹ I found this one: stackoverflow.com/questions/12065526/…
 
Related, but I don't see any way to find them, if they are not defined/documented
 
but you're right... I'll focus on the Person, for a start, make sure there's no duplicates, if any sort them out across the database and then move on until I've got appropriate virtual relationships, then I'll create the constraints and see whats with that
long night....
 
@NelsonCasanova You can try to automate some stuff.
Like first count how many rows each table have.
(and save wverything you find somewhere)
in a board or something
Small tables will probably have other tables that reference them.
Big tables will have FKs that reference other tables.
Check datatypes. Floats,decimals,datetimes are raely FKs or tagets of FKs.
@NelsonCasanova Oh and do the tables at least have PRIMARY keys?
and other constraints (UNIQUE, CHECK)?
 
@ypercubeᵀᴹ they have, yeah, some of them
but its just... on one table I have PersonNo and another personid and then PersonID
hahaha this is just.. but I can correlate most of the records on the Person table with the records on the other tables, a very small amount of PersonNo's-Id's dont seem to be on the Person table... so yeah... thats not bad
 
12:53 AM
@NelsonCasanova and all the same type?
Is there any application that uses the db?
 
yes, an access FE
I figure I could profile some of the functions
 
If it working, you can use it and see what functionality is there
 
cuz I cant really make changes or see the forms in design
 
I haven't used Access for ages, so not sure how or if you can see source code.
But if you can operate the thing, you can go and add a Person or make a Payment and see what changes in the db
(after you take a backup of course!)
 
@ypercubeᵀᴹ I hate access so much, I started at my current job fixing Access Frontends... I learned to hate it with all my soul... every time I think about quitting my job or life, Access is in the mix
 
1:00 AM
@NelsonCasanova hah ;)
You could come to the Heap in the next days,again. Not very busy at weekends but I rememebr 1 or 2 uers that have expience with Acces. They might be of bigger help than me
Got to go now
Good luck with the project!
 
ok man, thanks for your help
I'll be around
 
 
9 hours later…
10:04 AM
@MikaelEriksson Thanks. I was surprised and a little irked by the official response at the time. And yes, they have been historically reticent about fixing bugs/oversights for various reasons, but just recently that has changed a bit. Perhaps that issue will be corrected at some stage. I certainly hope so, and will continue to advocate for it.
 
Paul Little-Bit-Irked White
 
Ha.
 
Mornin'
(My phone corrected that to "Mormon" the first time)
 
@ypercubeᵀᴹ Nice edit to the deterministic question. Only just catching up.
> Comments resulted in an answer, please clean up dear mods, thank you, much appreciated. Mods like the better class of users asking nice and thanking in custom flags. We like our mods. Thanks again!
LOL custom flag.
@Phil Mormon.
 
@PaulWhite That's the seond one, right?
from @TomV?
morning
@PaulWhite What threw off at first, was that DATEDIFF, day, 0, some_date) is deterministic but DATEDIFF(, day, '19000101', some_date) is not.`
Having read all of @AaronBertrand posts about how good is to use the YYYYMMDD format. He certainly knows this weird case where 0 works while the YYYYMMDD fails ;)
(and oh, several missing parenthesis and extra commas there ...)
 
10:30 AM
@ypercubeᵀᴹ Yes.
@ypercubeᵀᴹ Using 0 there is 'offensive' to me, but the implicit conversion to datetime is deterministic.
 
 
1 hour later…
12:29 PM
Poorly-formatted code is rarely good code in my experience. And rarely written by someone competent.
2
 
 
4 hours later…
4:37 PM
@PaulWhite Haha
19 hours ago, by Tom V
I intentionally attempted to overdo it :)
Sorry :)
Trolls be trollin'
 
Looks like a useful template for future custom flags to me :)
 
@PaulWhite No seriously, not joking, I (and I think I can say we) really appreciate our mods, you and the others too
There must be quite a bit of effort involved, and maybe not enough reward
 
4:56 PM
@TomV Oh there's plenty of reward. And we all knew what we were getting into, and can stop at any time, so it's no biggie.
But thanks.
We appreciate the non-mod users as well. They make the job significantly easier than on other similarly-sized sites, I'd say.
</mutual love in>
 
5:35 PM
@TomV regarding what we were discussing the other day, about manuals: PostgreSQL manuals
 
 
1 hour later…
6:46 PM
@ypercubeᵀᴹ I couldn't find a good duplicate either. Was surprised.
I wonder how long Rolando's ! key lasts on average.
This is such a comprehensive answer, including mentioning MyISAM being spectacularly fast (RIP MyISAM). Also, loved your mentioning of the information_schema since it now tries to keep the count. My guess is that the count could be more accurate with innodb_stats_on_metadata set to 0 before ANALYZE TABLE. BTW +1 !!! — RolandoMySQLDBA 8 mins ago
 
7:03 PM
@PaulWhite The default is 3 I think ;)
 
@ypercubeᵀᴹ I mean I wonder how often he has to replace it, physically.
 
Ah ... didn't get it
Is MySQL 5.7 installed as a service ??? — RolandoMySQLDBA Jul 6 at 18:01
 
LOL
 
Like in Spanish, where exclamation and question marks are also in the start of a sentence (inversed), in Rolando, it's 3 times !!!
 
@ypercubeᵀᴹ What is going on with your keyboard lately?
Or are you on a phone or something?
 
7:13 PM
@PaulWhite Today I'm on the PC, normal keyboard. I have been using lately a laptop, in the bus or train, so that explains the typos. (is that what you mean, typos?)
 
Yep, skipped letters, oddly placed spaces...
"th start", Rolnado
Just wondered, doesn't bother me at all
 
The font size is probably also an issue. I'll have to change it. Didn't even notice the Rolnado, otherwise I would have edited.
CTRL+, CTRL+, CTRL+
 
High DPI display?
3
A: MySQL - Difference between using count(*) and information_schema.tables for counting rows

ypercubeᵀᴹThere are various ways to "count" rows in a table. What is best depends on the requirements (accuracy of the count, how often is performed, whether we need count of the whole table or with variable where and group by clauses, etc.) a) the normal way. Just count them. select count(*) as tabl...

 
@PaulWhite I guess. I have to calculate.
Is 100 dpi high?
 
That can be our dupe target now.
@ypercubeᵀᴹ Don't think so. Some people have flash very high DPI (4K?) displays.
 
7:23 PM
must be my deteriorating vision then
 
I run 1920 x 1080 on 15" at 175% (Windows) scaling.
 
I have a small laptop screen and a 22"
 
I've often though about getting a larger external display, but I would hardly ever use my laptop with it I think.
 
Stop at 00:58 and find bluefeet!
 
ha ha ha awesome
MySQL doesn't have indexed views?
Oh nvm. Like I care anyway.
 
7:33 PM
@PaulWhite No.
Computed columns only. So kind of.
 
 
2 hours later…
9:07 PM
The issue was that Field3 did not result in any listed table values, so instead of another INSERT INTO SQL statement, I made a form for TableB because I needed that, anyway. TableB uses a combo box that hides a Field4 and lists Field3 content from TableC. Filling out the form allows for the entry of Field4 into TableB that I may then INSERT INTO TableA. I think that I needed TableC.Field4 selected so that TableB.Field3 = TableC.Field3 worked. In either case, I think I may run a SQL Query for TableA based on TableB contents. — JMK 9 mins ago
omg
 
9:42 PM
oh, silver somehow
Where did I steal that
 
can I somehow tell mysql/innodb to choose a data directory for each table, from a list of directories I provide?
I have the issue that the server host I use allows me to only generate multiple volumes of a maximum size, my total database size can exceed that limit
so id like to have mysql put table 1 onto the first drive, table 2 onto the second etc. Preferably without having to manage that manually.
I guess I can just set the DATA DIRECTORIES myself, but I feel like mysql could be more efficient at that.
 
yeah exactly that. Id like to automate that. I guess it isnt possible?
I might try to use LVM or software raid to just bind the disks into one large disk
 
10:00 PM
@CBenni I think you may use this as well: dev.mysql.com/doc/refman/5.7/en/tablespace-placing.html
Or better, ask a question at the site
 
@ypercubeᵀᴹ is that the same link? heh
And yeah I will ask, but I feel like using LVM might be more reasonable
since I can dynamically add more disks later
 
If the volumes have a max size, perhaps you can create fixed, large tablespaces, without autoextend.
@CBenni Sorry, I meant this link: dev.mysql.com/doc/refman/5.7/en/…
which has examples of how to setup tablespaces with and without autoextend.
And I have no idea how/if it would work with file-per-table setting
 
mh
sounds to me like I could set
innodb_data_home_dir to /
and then set
innodb_data_file_path to /mnt/disk1/mysql/ibdata:12M:autoextend;/mnt/disk2/mysql/ibdata:12M:autoextend
ill post a question on the site, its ... kinda hard to phrase, which is why I asked here first ;_;
 
10:58 PM
mh, never mind, only works if file_per_table is disabled
 

« first day (2104 days earlier)      last day (1879 days later) »