0

I'm a developer in a local company, and I'm currently working on a project which involves migrating a website to my company's server, from some other hosting provider.

For some reason this other company won't give us access to the cPanel, but rather provided us with the project (files) and an exported db script(.sql).

Now I'm having issues after importing the db because it lacks few tables. I actually think the db export is okay itself. While the script file is rather large ~1.5GB, the db after importing is only ~1GB.

Q:

I'm using cPanel & phpMyAdmin, and now I'm wondering if there's any scenario where the db SQL script file could be larger than the database itself, or might there be a limitation within cPanel or phpMyAdmin regarding the size, which would cause the import to skip/stop or anything similar on about 1GB?

I always thought (and still think it makes more sense) that the db could be only bigger than the script file, due to how the SQL scripts are built, lack of indexes etc.

Just wanted to check if this is possible, before I start inspecting the 1.5GB sql file myself and see wether tables are missing there too.

Thanks PS couldn't find similar questions.

  • 2
    As far as I remember a MySQL dump is simply the object creation and data insert statements in a plain text file so it can easily be bigger than the database. If you create a database with a narrow table of integer fields and try the mysqldump you will see this for yourself. Could there be a timeout issue with phpMyAdmin? Running a 1.5GB script through a web console seems a bit risky. Are the missing tables the same each time you try to load the sql dump? – Dave Poole Apr 17 '18 at 21:00
  • 1
    MySQL internal representation for datetime is 5 bytes while text form used (2018-04-17 11:22:33) 19 bytes at least. Therefore if DB contains lot of datetimes then SQL-dump can be bigger than DB itself. – Kondybas Apr 17 '18 at 21:56
1

Lots of cases...

  • VARCHAR needs 1 or 2 extra bytes for the length; as text it needs two quotes, a comma, and possibly some escape characters.
  • The BIGINT 123456789012345 takes 8 bytes in the table, but 16 in the dump (15 digits, plus a comma).
  • A NULL value dumps as 5 bytes, regardless of what the datatype needs, which is probably 0.
  • DECIMAL(11,2) with -123456789.12: 5 bytes in the table -> 14 bytes in the dump
  • (and the DATETIME/TIMESTAMP example given in a Comment)

Going the other direction:

  • BIGINT 1: 8 bytes -> 2
  • CHAR(100) 'x': 100 characters -> 4 bytes. (This example varies with charset, version and engine.)
  • DECIMAL(11,0) with 0: 4 bytes in the table -> 2 bytes (0,) in the dump

Etc.

  • This actually is a great answer and it helps me understand this much better. Thanks Rick! I really like to learn, and being curious, would you mind if I'd ask how do you learn stuff like this? – demi May 4 '18 at 19:47
  • @demi - I "leaned by doing" -- on the job and at home. I learned by observing -- the output of mysqldump is human readable; elsewhere I learned the sizes of the datatypes (it's in the manual somewhere); this answer was simply putting those two together. The manual has the answer to your question, just not in an easy to find way. I learned by trying -- I experimented with several ways of using PARTITION, only to find that most are useless. – Rick James May 4 '18 at 21:49
  • @demi - (continued) I've been using MySQL for 17 years; I've been to most conferences on MySQL. Eventually, I got tired of repeating myself, so I wrote these docs 'here. (Your question is unusual, so I have not written about it.) – Rick James May 4 '18 at 21:56
  • Nice, that sounds cool. I'm actually an IT student but work full time, and I love to learn stuff, but ocasionally I end up with some questions that I can't getby/learn/study on my own and then seek for some help. This was very helpfull, thanks! The docs seem really nice and I'm sure those will be a nice source for a good read/learn :) Also, I'd be very grateful for any more tips/docs for improving and learning. Thanks alot! – demi May 6 '18 at 17:54
  • @demi - I enjoy imparting MySQL tidbits; keep the questions coming. – Rick James May 6 '18 at 22:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.