Database cached by the application? / GoDaddy problems

[dadkind]

Hello All,
I'm having DB issues on our GoDaddy hosted site and I need to get some info to continue diagnosing the problem.

I have a dev server on which everything runs as expected (of course). We have a goDaddy shared hosting account for our staging server and DB.

I then used the MS SQL Database Publishing Wizard to push our DB to the GoDaddy DB Servers.

Immediately afterwards, everytime I put something in the cart and started the checkout process, I get ForeignKey exception between the BasketItems table and the Products table. But, obviously, the products MUST exist since that's how they got in the basket in the first place.

Now, I am no longer able to create any categories because I get other foreign key exceptions.

I've tried re-publishing the database with no success.

To top it off, there seems to be some latency between the data that I publish and what I see on the site.

Does the application cache catalog data? Is there a way to clear the cache?

Has anyone come across DB problems using GoDaddy?

Thanks for any insights,
-tomas

[jmestep]

The problem could be that the tables are using identity fields for incrementing the primary keys. The dataport utility evidently turns that off if you want to retain original numbers (like in a 5.5 store import) and it checks to update rather than add items if you select that. I had to do an import into the database for a client where he needed to retain numbers, but he had botched the database up- I think it was before the dataport came out. I had to turn the identity fields off, turn some of the FK constraints off, then turn them back on when I was done.

[dadkind]

Thanks for the info!!

It turns out that problem was partly mine and partly due to the fact that we can not just upload a DB backup and run a restore on GoDaddy.

I did not know about the data port utility (where do I go to get some info on that?).

So I populated the DB from the existing DB, and yes, I did see where I had to turn off the CategoryID auto-generate behavior in order to keep my existing category/product relations intact. So everything worked well in my DevBox, almost.

I did not populate the ac_CategoryParents table and this is what was causing my bread crumbs to fail. But after a little "sleuthing" on the dev box, I could see how the table is normally populated, and so I wrote a SQL script to re-populate that table.

The GoDaddy problem came in because we have to use the MS SQL Database Publishing Wizard. We can't just "restore from a backup", because the backup was not created on the GoDaddy DB Server (this is in line with the MS Security guidelines according to Tech Support).

When I published my data, the ac_CategoryParents went from 63 rows to 630 rows. So all my relations were borked (my guess is that the table was not removed/recreated/populated properly by the "Wizard"). I wonder how many other tables are borked up like this!

So, I re-ran my ac_CategoryParents SQL script on the GoDaddy DB and now the breadcrumbs are working, for now.

But I still can not create new categories (on the GoDaddy server) because I'm getting foreign key errors reported back from the DB.

So I still have some sleuthing ahead of me.

Bottom line so far: The publishing Wizard is not fool proof! Beware!

-tomas

The problem could be that the tables are using identity fields for incrementing the primary keys. The dataport utility evidently turns that off if you want to retain original numbers (like in a 5.5 store import) and it checks to update rather than add items if you select that. I had to do an import into the database for a client where he needed to retain numbers, but he had botched the database up- I think it was before the dataport came out. I had to turn the identity fields off, turn some of the FK constraints off, then turn them back on when I was done.

[AbleMods]

Restoring a db backed up from a different SQL server is always tricky, regardless of the tool used.

Many times it's the result of a different set of schema permissions. Schemas are predefined permission sets associated with a SQL db.

When a SQL db and its associated objects are created, they are assigned the current schema in effect. That schema info is stored when a backup is made. When you see "[dbo]" in front of each table name, the schema is dbo.

However when the backup is restored to a different server, the new server may not have the exact same schema setup. As a result, the db restores with a schema that no user can possibly match and you will get permission errors all over the place.

Sometimes the only way to fix it after a restore is to run some manual queries against your Master tables - painful but it works.

[afm]

Bottom line so far: The publishing Wizard is not fool proof! Beware!

Did you publish both the schema and data? (I would). And did you have the wizard generate drop commands? (I would).

When I do both of those things, I have pretty good luck. [Note: The wizard uses the word "schema" to mean the tables, keys, etc; not the schema name in front of the table name.]

The 3rd thing I do is a search/replace of all the odd-ball schema names in the script. For example if GoDaddy has configured your SQL username as the database owner (typical), then I make sure all of schema names in the generated script are "dbo", such as "[dbo].[Products]".

After doing all 3, I have not had trouble with generated ids or foreign keys. The wizard does a good job of inserting the right values.

[afm]

I did not know about the data port utility (where do I go to get some info on that?).

• viewforum.php?f=61
• http://wiki.ablecommerce.com/index.php/ ... rt_Utility

[dadkind]

Thanks!! This looks just like what I needed last week! haha.

Oh well, the next time will be THAT much better.

-tomas

I did not know about the data port utility (where do I go to get some info on that?).

• viewforum.php?f=61
• http://wiki.ablecommerce.com/index.php/ ... rt_Utility

[jmestep]

Dataport utility doesn't officially work with Able 7.0.2 yet- I don't know what build you are running.
Our shared hoster locked down the databases more so that I couldn't use the Database Publishing Wizard and it was impossible to get a backup downloaded. That's when I started using the dataport to get an occasional backup of the store. It takes awhile, but it doesn't bog the server down as it is doing it, and I just go away and leave it running. I wish it had a scheduler, though.

[dadkind]

I know that the DROPS were generated (I recall the status displaying "Dropping table...").

But I don't think I published the schema (does this mean that the word "schema" is interpreted differently by the wizard than by others in some situations? Yikes!). That may be the problem.

Lastly, you mention updating the script. So you generate a SQL script from the wizard? And then run the script manually in the GoDaddy DB console?

I was just letting the Wizard push the data directly.

Regardless, I will make sure that I publish the schema as well next time. Thanks for the tip!

-tomas

Bottom line so far: The publishing Wizard is not fool proof! Beware!

Did you publish both the schema and data? (I would). And did you have the wizard generate drop commands? (I would).

When I do both of those things, I have pretty good luck. [Note: The wizard uses the word "schema" to mean the tables, keys, etc; not the schema name in front of the table name.]

The 3rd thing I do is a search/replace of all the odd-ball schema names in the script. For example if GoDaddy has configured your SQL username as the database owner (typical), then I make sure all of schema names in the generated script are "dbo", such as "[dbo].[Products]".

After doing all 3, I have not had trouble with generated ids or foreign keys. The wizard does a good job of inserting the right values.

[afm]

Lastly, you mention updating the script. So you generate a SQL script from the wizard? And then run the script manually in the GoDaddy DB console?

Yep. I generate the script, make any corrections I want, then run the script.

I'm pretty sure the wizard will not include drops unless you publish both schema and data, so I think you are OK.

And yes, the word schema means different things in different contexts. From a programmer's point of view, schema is shorthand for "all the table, column, index, etc. definitions in the database". From a SQL Server database admin's point of view, schema is part of a name, such as the second part of a full table name ([database].[schema].[tablename]).