search

ox-it / ords

Automatically exported from code.google.com/p/ords
0 stars 0 forks source link

database SQL export isn't working #781

Closed jajwilson closed 8 years ago

jajwilson commented 8 years ago

I don't know what's happened here, but when I try to export a database as an SQL dump in the current version of Staging (1.0.27 - 5532) in Chrome, I just get an empty file. Ah. But this isn't happening consistently - that would make it too easy!

I see an empty file when I create an SQL dump of these databases: https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=7928&dbProjectId=2813&pName=null https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=8518&dbProjectId=8511&pName=null

Yet it works fine when I create an SQL dump of this database: https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=4814&dbProjectId=4807&pName=new%20test%20project%20October%202015

I've added you as a project member of all three. I wonder if it's the &pName=null part of the URL that's making the difference?

thestoat commented 8 years ago

Much as I hate this sort of comment, I have to give a "Works for me". To be clear, from your above links I click the "export database" button and then select "SQL Database Dump" before clicking export. Is this what you do?

jajwilson commented 8 years ago

Sigh. Yes, that's what I tried. And have since tried again, still with no success. The strange thing is that even within the same project I'm finding that I can export some databases as SQL dumps with no problem, but not others. If, for instance, I try to export https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=3034&dbProjectId=2813&pName=Here, it works fine. .csv exports work for all databases I've tested, even those with an empty SQL dump. Now tested with Firefox, and finding exactly the same thing happens.

thestoat commented 8 years ago

Well that rules out my next avenue of attack that it was to do with you being a db owner. Please try again, but this time append the exact time when you clicked the export button so I can check logs.

jajwilson commented 8 years ago

Ok. https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=7928&dbProjectId=2813&pName=null just failed at 9:43 on Wednesday 23/3/2016

jajwilson commented 8 years ago

Dur. I seem to have accidentally closed the issue, but it's not really sorted. and the db that failed at 9:43 was in fact this one: https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=8518&dbProjectId=8511&pName=null

MerielP commented 8 years ago

Is this related to issue #745? The description of the problem seems similar - SQL exports not working (but not consistently), while CSV exports are fine.

thestoat commented 8 years ago

I believe the two issues are unrelated because this one (781) still happens whereas the referenced issue (745) has been fixed. I agree, they did look the same.

Interestingly, Meriel's suggestion (in 781) - that the problem might be due to a hangover from the ODBC work - is correct for this issue. It is possible that the databases that currently don't work are old, and that new ones you create are always fine? I'm going to guess that is the case, in which case a change is needed to retrofit, which I shall look at now.

jajwilson commented 8 years ago

Actually this one: https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=8518&dbProjectId=8511&pName=null is a new database. I only put it together last week.

thestoat commented 8 years ago

Can you confirm there is data in the beneficiaries table?

jajwilson commented 8 years ago

There's no data at all in this one - it's just the structure.

thestoat commented 8 years ago

Is it possible this problem happens in databases with only structure? Can you attach the database here?

jajwilson commented 8 years ago

Possibly, although as you can see from the other original example database it can also happen to databases with both structure AND content (that's why I included that other example). I can't attach the database here because it's an original database that I created in the ORDS, and I can't export it - which is the whole problem. Apols if I've misunderstood!

thestoat commented 8 years ago

Have they all been created in ORDS? Does tis happen on Dev too?

jajwilson commented 8 years ago

No. The first example is a copy of Mondial. I've not managed to reproduce the issue in Dev, and have now tried about 10 different databases. Are you certain that the issue is nothing to do with the "name=null" parts of the database URL that are present in the first two examples?

thestoat commented 8 years ago

No, the name=null doesn't appear when you reach the screen where the export button resides, and the project name is not part of the export process. The problem is that you don't have authorization to export the database sequences. I do, because when you added me the permissions were set up. But if there was a problem with import (or db creation) then it would be reproducible - sequence permissions never created - but that isn't the case, which makes it puzzling.

Because it doesn't happen on Dev, the obvious answer is that it has been fixed there, but I'm not convinced. Since you cleverly added the db upload time when you created the database I can at least try to look at logs around then too to see if anything is amiss.

jajwilson commented 8 years ago

Drat. Nothing is ever simple!

thestoat commented 8 years ago

Intermittent issues are the worst - especially when they appear to work for me ...

thestoat commented 8 years ago

This was assigned to James so I am now assigning to Meriel since James left ...

MerielP commented 8 years ago

I've been trying to reproduce the bug James encountered, but haven't succeeded so far. However, I have spotted something really strange...

I tried exporting the two databases on Staging that James links to above. I'm now the owner of both the containing projects, having inherited them when James left.

https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=7928&dbProjectId=2813&pName=null (the Mondial database) works absolutely fine for me: I can export it as both SQL and CSV, and if I reupload the SQL dump to ORDS, it all looks fine.

On the other hand, https://staging.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=8518&dbProjectId=8511&pName=null (the Family Policy database structure James created) is behaving really oddly. I can view the structure in the schema designer, but the table list on the 'View, Edit, and Query Data' page is empty. This means it's impossible to open any of the tables to add records. If I try to export this database, I just get an empty file - and this happens with both SQL and CSV exports.

So there's definitely something wrong here, but it doesn't look like the problem I'm encountering is exactly the same as the one James had.

I'll pass this back to Dave for the moment - but I'll also try exporting some databases from Dev and see what happens.

thestoat commented 8 years ago

This was just a permissions problem - I assigned ownership in ORDS but didn't re-assign permissions for the tables, sequences, etc. That should now be ok. It has nothing to do with the OP

MerielP commented 8 years ago

Thanks, Dave - the Family Policy database does indeed seem fine. Does the same problem apply to this database: https://dev.ords.ox.ac.uk/ordsProjectNode/database.jsp?dbProjectId=1699609&lid=1699797 ? I'm having a similar issue there: I can see the structure, but not the tables.

Currently exporting lots of SQL dumps from Dev - will let you know the results!

MerielP commented 8 years ago

I've now exported a dozen SQL dumps of databases on Dev. As far as I can tell, most of them are fine: when I open the exported files, they certainly contain what looks (to my inexpert eyes) like an SQL dump. I haven't reuploaded them all to check: if you think it would be worth doing that, I can - but as the files aren't empty, I'm certainly not getting the same problem as James did. I've also tried CSV exports for some of these databases, and they also seem fine.

The one database that isn't working smoothly is this one: https://dev.ords.ox.ac.uk/ordsProjectNode/database.jsp?lid=922197&dbProjectId=627348&pName=null - I'm not getting quite the same error as James did, but some attempts to export from that have resulted in this error message:

database export error message

The first time I tried it, I got that error message whatever format I tried to export in. Confusingly, when I went back and tried again, I could export as SQL, but not as CSV.

thestoat commented 8 years ago

I can see this problem and will fix it. However it really should be a separate issue. Else, I fix, you close and we lose the original problem ...

MerielP commented 8 years ago

OK - I'll create a separate issue. (I assume the problem in question is the one where I can view the structure but not the tables of a database?)

thestoat commented 8 years ago

This is part of the problem with adding new issues to old ones - it gets very hard to know which issue is being dealt with :-) Anyway - I have fixed the above IO exception

MerielP commented 8 years ago

Mea culpa! The problem is that I'm not always sure what's a separate issue, and what's a slightly different manifestation of the same one... But I will try to keep separate issues separate.

I can now export both CSV and SQL versions of the data from the database that was causing the IO exception - which is great.

Is there anything else we can usefully do in trying to track down James's mysterious empty SQL export bug?

thestoat commented 8 years ago

My feeling is to leave it until someone sees the problem again - though I hate the thought of it. The current situation is we have an issue that nobody here can reproduce.

MerielP commented 8 years ago

OK - let's mark this issue as closed for now. When we get to the stage of having a new Staging build to test, I'll try to be extra thorough of checking exports, just to be sure.