When cloning website from server to wamp which .sql is best to use?

[Billy]

Hello, I'm in the process of bringing over a very stubborn website from presta 1.6.10 to TB.

I have two spots I can download a data.sql file of my website. One is thru phpMyAdmin in WHM under ROOT or I can download one within cPanel itself under the backup section. However both data.sql files are different sizes and this confuses me. 

The data.sql for WHM is always 50mib bigger than the one from cPanel.

Any ideas why this? Which data.sql is the proper one to download?

Edit: Actually one other spot as well. I can also download from phpMyAdmin inside my cPanel. Which would have all the right privilege's as opposed to downloading under ROOT.   

---

Now when importing this database into MySQL what is the best way to do this. As phpMyAdmin just chokes on a direct import of the data.sql.zip file

I was thinking of using an old project called BigDump: Staggered MySQL Dump Importer or possible using the MySQL console.

Which one do you you guys prefer or is there another way?

Thanks Billy

 

Edit my bad: Please move to the migrations section. It's been a long day.

Edited August 5, 2021 by Script-Fu

[SLiCK_303]

I always use the file that TB makes when you ask it to do a db backup...Or the one i get from phpMyAdmin..

[Gotabor]

Hello Script-Fu,

When you download from CPanel -> Backup it gives you a mydatabase.sql.gz (compressed file) while from PhpMyAdmin you get mydatabase.sql

Bigdump worked for me several months ago, you should have no problem to import on WAMP or live server. It can work with both database file.

[Billy]

All I get is HTTP ERROR 500 when I try and do a backup inside my presta 1.6.1.10. Figure I better just leave that feature alone. Too many ghosts inside the machine.

My database is almost 400mib after I run Vekia's Database Optimization module. So pretty good size that certainly chokes phpMyAdmin on my Win10 computer.

I guess I had two questions really...

1) Why the different files sizes between cPanel backup vs phpMyAdmin? Also I unzip the .sql from cPanel to compare apples for apples. Again 50mib difference.

2) Is it a good idea to use MySQL Console? It's really a slick and fast solution with command line. 

 

[Traumflug]

2 hours ago, Script-Fu said:

My database is almost 400mib

No fear of big databases. phpMyAdmin doesn't load this DB into memory, just connects to the db server.

That said, you can go to phpMyAdmin and sort the database by table size. There are a number of tables getting huge over the years, but essentially collect just data for statistics. One can clear (but not delete) these tables.

For even better performance, look up a thing called 'Adminer'. It's kind of a lightweight phpMyAdmin.

Using MySQL command line is actually the best idea.  One can pipe its output right into a compressor, like:

 mysqldump db_name | gzip -c > db_name.sql.gz

[Billy]

So I'm back too hopefully understand why the .sql backups I download are different sizes. So in this first snap above I have my live site in maintenance mode. I have cleaned all the tables with "Database Optimization" module.   

 

In this second snap I download my website .sql file while the website is closed and tables are clean.

 

In this third snap I'm logged in as root in Web Host Manager (WHM) and I download my website .sql file again to compare with the first one from cPanel.

 

In this finial snap I am comparing the size of each .sql file and as you can see they are different. This is what I don't understand. Why are they different sizes?

brb with another post to finish up all the info I have gathered this morning. Again I'm just trying to understand why.

Ty Billy

[Billy]

 

This is my finial point and now I'm really confused about all this. lol

So I went ahead a created two new databases on my Windows 10 desktop computer. I am running Wamp Server with MySql 5.7.31 with phpMyAdmin 4.9.7.

 

So now that I have uploaded both of those .sql files to my local host via "MySQL Console" and have both perfect databases with different sizes files in them.

I turn around and download them again from my local host this time and now look at the files sizes??? This is the kind of stuff that drives me crazy.

 

Look I guess it doesn't matter to anyone but me. However if you were to do the same experiment your results would be the same as mine. I just wish I understood my results better...

----

Okay time to move on to my other threads here. I'm still trying to get to my finial thread on the copy_shopdata module post that I haven't made yet.

Let me finish my "theme thread" and the "database diff thread" first tho.

_Best Billy

 

 

 

 

Edited August 7, 2021 by Script-Fu

[musicmaster]

One tip on reducing database size: besides the usual suspects like ps_connections*, ps_guest, ps_page , ps_pagenotfound and ps_sekeyword you can also choose to truncate ps_search_index and ps_search_word. You just will need to regenerate them later on.

ps_pagenotfound registers when people try to access a non-existing page on your site.

ps_sekeyword registers with which keywords people found your site via the search engines.

Edited August 8, 2021 by musicmaster

[Billy]

1 hour ago, musicmaster said:

One tip on reducing database size: besides the usual suspects like ps_connections*, ps_guest, ps_page and ps_pagenotfound you can also choose to truncate ps_search_index and ps_search_word. You just will need to regenerate them later on.

That's great info and I use to have a post somewhere with all the correct snippets to run inside phpMyAdmin. I can't find the post atm and I'm not sure if it was here or over on prestashop. I have googled and googled for it.

I would truncate each one - one by one... I just need to find it.

Edited August 7, 2021 by Script-Fu

[musicmaster]

11 hours ago, Script-Fu said:

That's great info and I use to have a post somewhere with all the correct snippets to run inside phpMyAdmin. I can't find the post atm and I'm not sure if it was here or over on prestashop. I have googled and googled for it.

I would truncate each one - one by one... I just need to find it.

If you are exporting you don't need to truncate. You can also just export the structure and not the data of those tables.

[Billy]

11 hours ago, musicmaster said:

If you are exporting you don't need to truncate. You can also just export the structure and not the data of those tables.

In a round about way I got their by exporting from the server then importing back to wamp then exporting from wamp. Convoluted yes but it worked. I would need to nail all this down in a short cut .txt document to cheat from. Which I will make just not atm.

These are what I found digging around... Please NO one run this unless a Dev like @musicmaster confirms them.

TRUNCATE TABLE ps_connections;

TRUNCATE TABLE ps_connections_source;

TRUNCATE TABLE ps_connections_page;

TRUNCATE TABLE ps_guest;

TRUNCATE TABLE ps_log;

TRUNCATE TABLE ps_referrer;

TRUNCATE TABLE ps_referrer_shop;

TRUNCATE TABLE ps_referrer_cache;

TRUNCATE TABLE ps_pagenotfound;

TRUNCATE TABLE ps_mail;

TRUNCATE TABLE ps_statssearch;

 

Anyway since your around I might as well throw the kitchen sink at you so to speak.

---

In my MySQL database I have...

(19, 1, 0, 'Frontcontroller::init - Cart cannot be loaded or an order has already been placed using this cart', 'Cart', 10790, 0, '2015-09-12 21:30:59', '2015-09-12 21:30:59'),

all the way to...

(8420, 1, 0, 'Frontcontroller::init - Cart cannot be loaded or an order has already been placed using this cart', 'Cart', 30725, 0, '2021-08-03 19:09:46', '2021-08-03 19:09:46'),

Which is a lot... going all the way back to 2015 so I googled it and found this...

https://stackoverflow.com/questions/54042393/prestashop-1-7-5-cart-cannot-be-loaded-or-an-order-has-already-been-placed-using?

If that's correct on "Stack" how would one correct that issue?

 

Now @musicmaster I asked my server company this question above. I will post their answer in the next post to keep this tidy.

Edited August 8, 2021 by Script-Fu

[Billy]

 

Hi,

Checking the Apache log doesn't show any errors that appear to be related to this recently. The MySQL error log hasn't logged any recent errors.
-----
root@xxx [~]# awk '$9 ~ /5../ {print}' /usr/local/apache/domlogs/*-ssl_log | grep -i cart
114.xxx.xxx - - [07/Aug/2021:10:06:48 -0400] "GET /cart?add=1&id_product=xxx&token=xxxxxx

HTTP/1.1" 503 1202 "-" "Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)"
114.xxx.xxx - - [07/Aug/2021:10:09:42 -0400] "GET /cart?add=1&id_product=xxx&token=xxxxxx HTTP/1.1" 503 1202 "-" "Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)"
-----
root@xxx [~]# tail -5 /var/lib/mysql/xxx.com.err
2021-07-26 11:51:15 559 [Note] Shutting down plugin 'mysql_native_password'
2021-07-26 11:51:15 559 [Note] Shutting down plugin 'binlog'
2021-07-26 11:51:15 559 [Note] /usr/sbin/mysqld: Shutdown complete

210726 11:51:15 mysqld_safe mysqld from pid file /var/lib/mysql/xxx.com.pid ended
-----

I ran the queries suggested by the StackOverflow article. While we can't guarantee this analysis, it does seem that the cart numbers in the ps_cart and id_cart tables are separated by a few degrees.
=====
mysql> SELECT id_cart FROM ps_cart ORDER by id_cart DESC LIMIT 1;
+---------+
| id_cart |
+---------+
| 30787 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT id_cart FROM ps_orders ORDER by id_cart DESC LIMIT 1;
+---------+
| id_cart |
+---------+
| 30761 |
+---------+
1 row in set (0.00 sec)
=====
mysql> SELECT `AUTO_INCREMENT`
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'xxx'
-> AND TABLE_NAME = 'ps_cart';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 30788 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME
= 'ps_orders';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 6946 |
+----------------+
1 row in set (0.00 sec)
======

You will want to confirm these results and a proposed solution with a qualified Prestashop developer, their forums, documentation or support before any changes are made here. We can assist with creating a database backup and running commands you provide to make the necessary changes.

Please let me know if you have any questions.

Edited August 8, 2021 by Script-Fu

[Billy]

I hope I didn't post any personal details... @musicmaster this is on a presta 1.6.1.xx site that I'm moving to Thirty Bees. However ghosts in the machine has prevented this site from being upgraded for years.

When using your prestools & copyshop_data does that automatically resync the cart process? Maybe this needs to be taken care of in the database?

---

When I found your copyshop_data I was able to pull off a first run easy and now have the site working with TB inside my WampServer.

However I still have Ghosts so I'm trying to clean up all this stuff before finial with your wonderful copyshop_data.

_Best Billy

 

Oh and BTW I was able to nail down those inconsistencies with Winmerge once I shut down stuff running in the background on Win10 Thanks for having me go back over that aspect in the other forum post. 

Edited August 8, 2021 by Script-Fu
Better description

[musicmaster]

1 hour ago, Script-Fu said:

 

Hi,

Checking the Apache log doesn't show any errors that appear to be related to this recently. The MySQL error log hasn't logged any recent errors.
-----
root@xxx [~]# awk '$9 ~ /5../ {print}' /usr/local/apache/domlogs/*-ssl_log | grep -i cart
114.xxx.xxx - - [07/Aug/2021:10:06:48 -0400] "GET /cart?add=1&id_product=xxx&token=xxxxxx

HTTP/1.1" 503 1202 "-" "Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)"
114.xxx.xxx - - [07/Aug/2021:10:09:42 -0400] "GET /cart?add=1&id_product=xxx&token=xxxxxx HTTP/1.1" 503 1202 "-" "Mozilla/5.0 (Linux; Android 7.0;) AppleWebKit/537.36 (KHTML, like Gecko) Mobile Safari/537.36 (compatible; PetalBot;+https://webmaster.petalsearch.com/site/petalbot)"
-----
root@xxx [~]# tail -5 /var/lib/mysql/xxx.com.err
2021-07-26 11:51:15 559 [Note] Shutting down plugin 'mysql_native_password'
2021-07-26 11:51:15 559 [Note] Shutting down plugin 'binlog'
2021-07-26 11:51:15 559 [Note] /usr/sbin/mysqld: Shutdown complete

210726 11:51:15 mysqld_safe mysqld from pid file /var/lib/mysql/xxx.com.pid ended
-----

I ran the queries suggested by the StackOverflow article. While we can't guarantee this analysis, it does seem that the cart numbers in the ps_cart and id_cart tables are separated by a few degrees.
=====
mysql> SELECT id_cart FROM ps_cart ORDER by id_cart DESC LIMIT 1;
+---------+
| id_cart |
+---------+
| 30787 |
+---------+
1 row in set (0.00 sec)

mysql> SELECT id_cart FROM ps_orders ORDER by id_cart DESC LIMIT 1;
+---------+
| id_cart |
+---------+
| 30761 |
+---------+
1 row in set (0.00 sec)
=====
mysql> SELECT `AUTO_INCREMENT`
-> FROM INFORMATION_SCHEMA.TABLES
-> WHERE TABLE_SCHEMA = 'xxx'
-> AND TABLE_NAME = 'ps_cart';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 30788 |
+----------------+
1 row in set (0.00 sec)

mysql> SELECT `AUTO_INCREMENT` FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'xxx' AND TABLE_NAME
= 'ps_orders';
+----------------+
| AUTO_INCREMENT |
+----------------+
| 6946 |
+----------------+
1 row in set (0.00 sec)
======

You will want to confirm these results and a proposed solution with a qualified Prestashop developer, their forums, documentation or support before any changes are made here. We can assist with creating a database backup and running commands you provide to make the necessary changes.

Please let me know if you have any questions.

I don't see any problem here. The highest id_cart in ps_orders is lower than the highest id in ps_cart - as it should be.

[Billy]

@musicmaster Interesting as I must have 7,000 rows of these in my database dating from 2015 to 2021... I know you can't really do much without the .sql in hand but if your were softballing and idea what would you think?

 (19, 1, 0, 'Frontcontroller::init - Cart cannot be loaded or an order has already been placed using this cart', 'Cart', 10790, 0, '2015-09-12 21:30:59', '2015-09-12 21:30:59'),

 

I hope didn't X out info that might have been needed. All the XXXX's I put there to hide some of my personal details so I wouldn't get hacked.

Edited August 8, 2021 by Script-Fu

[datakick]

9 hours ago, Script-Fu said:

 (19, 1, 0, 'Frontcontroller::init - Cart cannot be loaded or an order has already been placed using this cart', 'Cart', 10790, 0, '2015-09-12 21:30:59', '2015-09-12 21:30:59'),

Don't worry about this, it's harmless. This happens when your customer visits your site after they placed order. The cookie still reference the old cart that has been converted to order already. Thirtybees / ps16 detects this, and create a new cart. And in ps16 this 'notice' is generated. Not in thirtybees, though.

[Billy]

Man you just made my morning Petr! Ty