Lost whole site in development... SQL Sessions?

[meltymik]

I'm developing a (second) site using oscommerce and have never had to post to the forums before, but this one is beyond me... This is what I just did: I backed up my database in admin, then did an easy populate import, but forgot categories, so I went and restored the database and now have this error:

 

1146 - Table 'retropolitan.sessions' doesn't exist

 

select value from sessions where sesskey = 'res4791amc9coi86lp92u0cts3' and expiry > '1232062533'

 

[TEP STOP]

 

Site is www.retropolitan.com if that helps anyone. Any assistance would be greatly appreciated! Thank you, all.

[zeldaz]

What procedures did you do to backup and restore your database? Are you sure you didn't delete any databases by accident or anything?

 

You might try running the following SQL statement taken from the osC installation:

CREATE TABLE sessions (
 sesskey varchar(32) NOT NULL,
 expiry int(11) unsigned NOT NULL,
 value text NOT NULL,
 PRIMARY KEY (sesskey)
);

[meltymik]

Hi. Thank you so much for replying. I simply backed up the database using pure SQL from admin. Then, about 2 minutes later, I restored the database as it had been 2 minutes beforehand. Nothing more. That's a great suggestion. I'm going to compare my SQL database with another shop I did and see if there are other column differences.

[meltymik]

I think this is worse than I thought. I am missing all of the following:

products_notifications

products_options

products_options_values

products_options_values_to_products_options

products_to_categories

reviews

reviews_description

sessions

specials

supertracker

tax_class

tax_rates

whos_online

zones

zones_to_geo_zones

 

Meaning that the last table I now have is product_description.

[zeldaz]

Well the error you are getting is due to the table "sessions" not being present. I'd imagine that the suggested SQL fix I posted above would work.

 

Did you do the backup and restoring inside phpMyAdmin? If so what steps did you do specifically? If not, how did you do it?

 

Edit: Sorry, didn't see that post above. Sounds like you've deleted some of your tables. You can either restore them with the backup (assuming you did it correctly) or you can run the following SQL. I sure hope that this wasn't a live store as you may find that some of your data is missing.

 

DROP TABLE IF EXISTS products_notifications;
CREATE TABLE products_notifications (
 products_id int NOT NULL,
 customers_id int NOT NULL,
 date_added datetime NOT NULL,
 PRIMARY KEY (products_id, customers_id)
);

DROP TABLE IF EXISTS products_options;
CREATE TABLE products_options (
 products_options_id int NOT NULL default '0',
 language_id int NOT NULL default '1',
 products_options_name varchar(32) NOT NULL default '',
 PRIMARY KEY  (products_options_id,language_id)
);

DROP TABLE IF EXISTS products_options_values;
CREATE TABLE products_options_values (
 products_options_values_id int NOT NULL default '0',
 language_id int NOT NULL default '1',
 products_options_values_name varchar(64) NOT NULL default '',
 PRIMARY KEY  (products_options_values_id,language_id)
);

DROP TABLE IF EXISTS products_options_values_to_products_options;
CREATE TABLE products_options_values_to_products_options (
 products_options_values_to_products_options_id int NOT NULL auto_increment,
 products_options_id int NOT NULL,
 products_options_values_id int NOT NULL,
 PRIMARY KEY (products_options_values_to_products_options_id)
);

DROP TABLE IF EXISTS products_to_categories;
CREATE TABLE products_to_categories (
 products_id int NOT NULL,
 categories_id int NOT NULL,
 PRIMARY KEY (products_id,categories_id)
);

DROP TABLE IF EXISTS reviews;
CREATE TABLE reviews (
 reviews_id int NOT NULL auto_increment,
 products_id int NOT NULL,
 customers_id int,
 customers_name varchar(64) NOT NULL,
 reviews_rating int(1),
 date_added datetime,
 last_modified datetime,
 reviews_read int(5) NOT NULL default '0',
 PRIMARY KEY (reviews_id),
 KEY idx_reviews_products_id (products_id),
 KEY idx_reviews_customers_id (customers_id)
);

DROP TABLE IF EXISTS reviews_description;
CREATE TABLE reviews_description (
 reviews_id int NOT NULL,
 languages_id int NOT NULL,
 reviews_text text NOT NULL,
 PRIMARY KEY (reviews_id, languages_id)
);

DROP TABLE IF EXISTS sessions;
CREATE TABLE sessions (
 sesskey varchar(32) NOT NULL,
 expiry int(11) unsigned NOT NULL,
 value text NOT NULL,
 PRIMARY KEY (sesskey)
);

DROP TABLE IF EXISTS specials;
CREATE TABLE specials (
 specials_id int NOT NULL auto_increment,
 products_id int NOT NULL,
 specials_new_products_price decimal(15,4) NOT NULL,
 specials_date_added datetime,
 specials_last_modified datetime,
 expires_date datetime,
 date_status_change datetime,
 status int(1) NOT NULL DEFAULT '1',
 PRIMARY KEY (specials_id),
 KEY idx_specials_products_id (products_id)
);

DROP TABLE IF EXISTS tax_class;
CREATE TABLE tax_class (
 tax_class_id int NOT NULL auto_increment,
 tax_class_title varchar(32) NOT NULL,
 tax_class_description varchar(255) NOT NULL,
 last_modified datetime NULL,
 date_added datetime NOT NULL,
 PRIMARY KEY (tax_class_id)
);

DROP TABLE IF EXISTS tax_rates;
CREATE TABLE tax_rates (
 tax_rates_id int NOT NULL auto_increment,
 tax_zone_id int NOT NULL,
 tax_class_id int NOT NULL,
 tax_priority int(5) DEFAULT 1,
 tax_rate decimal(7,4) NOT NULL,
 tax_description varchar(255) NOT NULL,
 last_modified datetime NULL,
 date_added datetime NOT NULL,
 PRIMARY KEY (tax_rates_id)
);

DROP TABLE IF EXISTS geo_zones;
CREATE TABLE geo_zones (
 geo_zone_id int NOT NULL auto_increment,
 geo_zone_name varchar(32) NOT NULL,
 geo_zone_description varchar(255) NOT NULL,
 last_modified datetime NULL,
 date_added datetime NOT NULL,
 PRIMARY KEY (geo_zone_id)
);

DROP TABLE IF EXISTS whos_online;
CREATE TABLE whos_online (
 customer_id int,
 full_name varchar(64) NOT NULL,
 session_id varchar(128) NOT NULL,
 ip_address varchar(15) NOT NULL,
 time_entry varchar(14) NOT NULL,
 time_last_click varchar(14) NOT NULL,
 last_page_url text NOT NULL
);

DROP TABLE IF EXISTS zones;
CREATE TABLE zones (
 zone_id int NOT NULL auto_increment,
 zone_country_id int NOT NULL,
 zone_code varchar(32) NOT NULL,
 zone_name varchar(32) NOT NULL,
 PRIMARY KEY (zone_id),
 KEY idx_zones_country_id (zone_country_id)
);

DROP TABLE IF EXISTS zones_to_geo_zones;
CREATE TABLE zones_to_geo_zones (
  association_id int NOT NULL auto_increment,
  zone_country_id int NOT NULL,
  zone_id int NULL,
  geo_zone_id int NULL,
  last_modified datetime NULL,
  date_added datetime NOT NULL,
  PRIMARY KEY (association_id),
  KEY idx_zones_to_geo_zones_country_id (zone_country_id)
);

[♥geoffreywalton]

Your restore might be too big to complete in 1 go.

 

Create a copy of your backup.

 

Edit the backup, does it have drop and create statements for all tables?

 

Split the file into several parts preferably starting each part with the

 

if exists drop table command.

 

Import each one of the files.

 

Good luck

 

G

[meltymik]

Thanks, again. Yeah, that's definitely what happened (somehow). I didn't do the restoration from phpMyAdmin, just used the backup database function in oscommerce admin. And then used the restore function in oscommerce admin. I just FTP'ed the backup out and am thinking that I can use that to restore everything. I need to know more SQL! Can I just import the backup (it seems complete)??

[meltymik]

Thanks. It's 940kb, so I think it should work in one go.

[meltymik]

Thanks for all the help. I finally figured it out! For some reason, the restore was only partially successful and only restored 38 tables, then cut off the rest. I used the back up (Glad I did that!) and it restored everything. Pheeeeeeewww!