Support for Drupal 7 is ending on 5 January 2025—it’s time to migrate to Drupal 10! Learn about the many benefits of Drupal 10 and find migration tools in our resource center.
By ThomS on
I'm sorry but I don't where else to ask this question. I've googled tons of terms and I keep coming up empty. I know this isn't a Drupal problem but maybe someone can tell me where I can go for help.
I am simply trying to export my database to put on the web host. The only problem I'm having is that a table called menu_router doesn't seem to export completely. On my local site's database the table is 1.5MB but when I export it it is only half that size. None of my menus work properly.
I can't figure out why this one table will not export in its entirety.
Again, sorry because I know this is a problem I have with mysql and phpmyadmin and not a Drupal one, but I don't know where else to ask. I've tried believe me, I just can't find an answer.
Comments
_
Is it the biggest table in the db? If so, you're likely running into a time out or resource limit due to doing it with phpmyadmin. You're better off doing it through the command line using mysqldump.
Oh thank you for the reply.
Oh thank you for the reply. Actually the table is only a meg and a half (1.5 mb) under my hosts 2048kB limit. But just on the export it isn't complete. I get only a 700 kb table on export.
on edit: i forgot to mention that i am not having any timeout problems. the database exports and imports without any errors. i get a confirmation that the query completed successfully. it's just not all there.
=-=
if you export that table and only that table does it export in its entirety?
no, i'm afraid it does not.
no, i'm afraid it does not. i only get half.
If I export the complete
If I export the complete database the menu_router table is incomplete, if I export the only the table menu_router it is still incomplete. No matter what I do, the table menu_router will not export completely and all I get is a table with half the data no matter what I do. Is there a way I can export this table completely?
you can visit the site (as it
you can visit the site (as it is) at www.pontiac2020.ca to check it out. if you switch languages the menus will be all wrong.
Again, I appreciate the help. Thank you.
=-=
anything look odd in the row after the export stops?
um, no i didn't see anything
um, no i didn't see anything in the row where the the export stops. I did an excel output of both to try and compare and I can't find any difference.
More details
You can tell us, what app are you using? xamp, wamp? plataform? details.
You can try to uninstall your server, and install again. Or get .sql code or respectively the code of db.
php.ini
Are you able to edit your site's php.ini? Have a look in your status report (/admin/reports/status/php) to see where it's located.
As a previous poster mentioned you're probably running into resource limits.
You could increase the max_execution_time and/or memory_limit, if your hosting setup will let you.
Reload Apache after making any changes.
Also you might look in the PHP or Apache error logs to see if there's anything interesting in there.
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
yes, i ran into timeout
yes, i ran into timeout problems before and dealt with them by increasing the limit to 1800. since then I have had no timeout problems. when i built this new site, the timeout issue was resolved by then so it hasn't been a problem since then.
memory_limit you are perhaps
memory_limit
you are perhaps hitting on something important here. I'm not sure that this might be a part of the problem.
I will look into that to see if there is a relationship.
very sorry again, i should
very sorry again, i should have included that info. i am using wamp (2.2e-php5.4.3-httpd2.2.22-mysql5.5.24-32b)
Please understand that my
Please understand that my problem is not a Drupal one. When I go into phpmyadmin to export my database, or only the menu-router table, it always comes out incomplete. I cannot export the databast OR the table menu_router completely. All I ever get is an incomplete export of the complete data base OR the table menu_router.
There is no way I can export this table entirely (menu_router). I get only partial exports of this table. everything else is fine. all other tables export properly except menu_router. all i get is a partial of this dump (menu_router). i cannot find a way to export menu_router fully.
MySQL
phpMyAdmin may have resource limit issues too - have you tried from the command line?
mysqldump -uusername -p database > database.sql
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
i haven't tried that because
i haven't tried that because it is a little beyond my skillset at the moment. but you give me something to attempt. i didn't factor this into my situation. i thought i could simply export a database. but to be honest, i don't know what to do. i am not a sql expert. i didn't think i would run into this kind of problem. i built my drupal site, i built a few, and i could always get those databases to export entirely. this time no.
too many things stand in the way of a successful Drupal deployment. why is that? is Drupal 7 the equivalent of windows millennium? sorry, but i had to ask. i've gotten around just about every limitation D7 has to make my site. but a perfect version can only exist on my local computer. even though i've managed to work around all of drupal's limitations, i have the site i want on my local machine. but i'm not allowed to export it to my webhost?
i think that the problem here is a formatting problem caused by i18n. it is a well known fact that multilingual drupal 7 does not work - check the ocean of posts on the subject - and translation is a cluster_fug with D7. maybe i'm wrong, but that's my experience. some thing is wrong (i think) with the way this table is being created and phpmyadmin doesn't know how to deal with it. how else to explain that every other table in the database exports intact but this one doesn't?
I don't care, I'm not trying to pick any fights. But I don't understand why this is happening.
I spent the last 6 months building this site while pulling a lot of my hair out. is this supposed to be the Drupal experience. "try it and HATE it?"
i've tried everything. just seems to me that making a drupal site is too hard in 7 and maybe i'm just wasting my time.
i wanted a multilingual site, and all i wanted from drupal was to deliver on its promises. but i'm no longer sure it can.
if you look at it from my perspective, then you might understand. drupal is supposed to be able to render a multilingual site. but it doesn't. i had to work around menus that don't translate, taxonomy that just doesn't work in translation and the fact that all drupal can do is create separate nodes for everything like polls that make it not very translatable. despite all that, i got a site put together but it can only live on my local computer because i cannot export it.
so i accept that maybe its my fault that none of this works. maybe i'm not smart enough to make it work. and i realize those of you who are trying to help me don't need all this whining from me. so i'm sorry for that.
but i committed to drupal because i believed in it and its done nothing but me let me down every step of the way.
i regret my choice. j00mla could never do what drupal can, but can drupal do what it promises?
not sure anymore.
(sorry for the negativity, and I do VERY much appreciate all of you trying to help me out. but drupal is showing itself to be more trouble than it is worth. is that what the drupal community wants?)
MySQL
If you do the mySQL dump, you'll need to know how to import the resulting sql file into your production database - post again if you get that far and want more direction.
Sorry to hear you're not getting what you need. I've never attempted an internationalized Drupal site, so can't comment; but Drupal has been the one platform I've relied on for nearly everything for several years, and so far it's always worked out. Along the way I've had to learn some pretty technical stuff, but I'm Ok with that.
I can see how it might not be for everyone though.
Stick around, maybe we can help?
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
i will stick around because i
i will stick around because i really think drupal is a great thing. just not sure about 7.
And again, i'm sorry for sounding like a twit, but i REALLY appreciate you taking the time to try and help me. (drupal SHOULD be for everyone!-it has awesome potential)
ok, so back to business.
I can export my database complete. I can import my database complete from that file. but menu_router is half empty. i drop the menu_router table from my db, export only the menu_router table and import: same result. only half the data is there.
i am exporting a 1.5 MB file and it is coming out only as a 700 kb file. for whatever reason, when i export the whole db or just the table menu_router is getting half the data stripped and i end up with an incomplete export.
Export how?
Are you exporting from the command line or still from a GUI tool? Not sure I understand if you tried the mysqldump.
if you wanted to do just the one table from the command line it would look like:
mysqldump -uusername -p databasename tablename > databasename_tablename.sql
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
Could something be wrong with that table?
have you checked it?
http://www.thegeekstuff.com/2011/12/mysqlcheck/
/K
thanks for the link. after i
thanks for the link. after i get a few coffees in me i'll read it.
haven't tried that yet but i
haven't tried that yet but i will. i've been doing all my exporting/importing through the myphpadmin gui so far.
i'm having a ton of problems
i'm having a ton of problems with this solution. i can't even get into the mysql5.5.24 directory to launch it.
Mysqldump and mysqlcheck
You don't need to be in a specific directory to run these commands.
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
for some reason they're not
for some reason they're not working. i type in mysqldump and i get 'mysqldump' is not recognized as an internal or external command, operable program or batch file
Mysqldump and mysqlcheck
OK let's back up - perhaps WAMP doesn't include these mysql commands? That would be awkward...
Sorry, Unix/Mac guy here
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
=-=
are you using the windows command prompt? or the WAMP mysqlconsole?
don't be sorry, i'm the one
don't be sorry, i'm the one who's sorry for having done this on a windows machine lol
i suspect life is better on linux
i'm getting more confused as i try to find an answer online. wow... lots of reading. i feel as though my eyeballs have merged into one big eyeball.
i unfortunately at this point do not understand mysql enough to understand what the problem is.
it began with the first export/import of the database from my local machine to my webhost. after breaking up the db so i get in under my hosts 2048kb limit. i just had to do one export of everything except one table that was very large. the table i'm having trouble with, menu_router, stayed with the big export. the other large table was exported and zipped. but when i finished importing it host side, half of the total database was missing. tables all seemed to be there but lots of data missing.
i solved that problem and got two intact exports that i successfully imported. only one problem: menu_router is still missing stuff. at first was only importing a table with 30 rows when the original table has 601 rows. i managed to get that eventually to imports with all the rows but the size of the table is only half; should be 1.5MB and is only 760kb.
so that's where i am. i cannot get this table to completely export because the export file is never bigger than 750kb. i am struggling to understand what is changing and why?
i did a field my field export/import but the size is never bigger than 750kb. i don't understand where half the data is going.
is there a way to rebuild this table from drupal once i get the site re-exported with everything including the half-sized menu_router table? this seems to be effecting my menus and i need to solve this so i can move on with my life.
thanks again.
forgot to mention that all
forgot to mention that all exporting/importing done on phpmyadmin
(i did try the mysql console inside wamp but that confused more than anything else. couldn't get anything to happen. i also tried a windows command line prompt. but nothing works there either.)
At the DOS prompt entercd
At the DOS prompt enter
note you will have to adjust the mysql version in the path depending on the version in use.
then use
note udatabase_username, ppassword, database_name are place holders. You will need to utilize the actual information and filename.sql can be titled anything you want.
The above seems to work correctly on my windows machine.
There is function to rebuild the menus. However, because you can't get a proper backup, I wouldn't suggest it. If the rebuild goes haywire you're just compounding the problem.
my goodness this stuff is
my goodness this stuff is temperamental. a lot like kicking water uphill.
i did what you said and i got it to work... kind of. now i'm getting all kinds of grief over the database password.
mysqldump: Got error: 1045: Access denied for user '*******_****'@'localhost' (using password: YES) when trying to connect
=-=
that indicates that one of the three are incorrect: -udatabase_username -ppassword database_name
not sure why you've focused on the password as the giver of grief. the error is merely stating that a password was being used.
Backup & Migrate module?
This might be a little late, and it's possible you'll have the same issues, but do you have the Backup & Migrate module installed on your site (or could you install it)?
That module just offers another way to get ahold of your SQL file, and using it doesn't involve any action on the command line or outside of your Drupal admin interface.
B&M
I thought about suggesting that, but if he's hitting resource limits in phpMyAdmin, I figured he'd hit the same limits using B&M.
Maybe not though...
Certainly easier to use, that's for sure.
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
i had considered backup and
i had considered backup and migrate but i ran into something that stopped me from installing it. I will give it another try though because I am not getting anywhere with phpmyadmin or mysqldump.
Also, I have no timeout issues. Had lots a long time ago, but i've dealt with all of them. So I am good there.
I hope B&M will help because I at least at one point was able to get my whole database uploaded with only 1MB missing, but now no matter what I do, I can't get a database larger than 10MB (should be 24MB) so I'm losing ground.
I just can't figure out what is going on.
Just to be clear
the code should be
NOT
see the "space" between -u and database_username but no space between -p and password.
/K
=-=
good catch
i've installed B&M and it is
i've installed B&M and it is working and I get the entire database less 1MB in the router_menu table which is still disappearing. but i can live with that for now if need be.
now however, i cannot get the same database file to import completely on my host. i'm waiting for a response to my support ticket but this far they haven't answered. so i'm half way out of the water but still cannot get my full database uploaded to the online host server.
I've tested the database backup file on other local machines and it imports completely (also less the 1MB in menu_router). but it will import completely.
B&M import
Are you uploading the backup file from your desktop by browsing to it in the B&M "Restore" panel? If so you may be hitting a resource limit - PHP has a maximum size of file that it will allow you to upload, and your backup file is quite large. PHP's default is 2Mb, IIRC.
Are you getting any messages?
If this is an issue, try FTPing the file into your Manual backups directory (look in B&M's Destinations pane to see where that is).
Then reload B&M, and navigate to "Destinations" - your file should appear in the "Manual Backups" directory, and you'll be able to click "Restore" on it. This will get you around the PHP upload limit.
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
I really appreciate all the
I really appreciate all the time your taking to help me. I not sure that it is a resource limit because I always get the message that the queries completed successfully (although the number of queries has changed a few times).
But I cannot rule it out so I will have to try your method Sam because I am all out of options or understanding. My host is trying to help me but there is a long waiting period between communications and we're not really getting anywhere.
So I will attempt your suggestion now and get back to you.
Same result. something is
Same result. something is stripping out half the data on import.
i still cannot get a complete db import.
:(
B&M
Well if it's reporting that the Restore happened successfully, then it's not a resource limit.
The only thing I can really suggest is that one of your tables might be damaged, which a previous poster addressed by suggesting myisamchk (http://dev.mysql.com/doc/refman/5.0/en/myisamchk.html)
If your host can't help you with running mysql commands such as this from a console, maybe the only thing left to do is export a few tables at a time with phpMyAdmin...?
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
I will look into
I will look into myisamchk.
you can visit the site www.pontiac2020.ca to take a look. This is what I managed to get online.
Everything seems to be there and I've been playing around in it and I haven't run into any problems or errors yet. but i'm nervous as heck because half the database isn't there, but it's not showing itself anywhere. Yet.
I don't know what to do. Everyone is waiting to use this site and i'm too afraid to open it up because i'm convinced it has to blow up eventually.
I just don't know where the rest of the database is -- and to be honest, why everything seems to be working so far.
To explain what I did to get this all online...
And that is where I am.
there is an auth user account set up
username: drupal
password: helpers
that is available temporarily if you want to take it for a test drive.
again do appreciate all the time all of you are giving to help me out.
myisamchk
i am running InnoDB. Does myisamchk work with those db's too?
Myisamchk
No, sorry, it doesn't.
At this point if I were you I'd focus on making sure the production site is functioning properly. Something in your dev environment may be well and truly hosed, but your site seems to be working OK. If the menu router table were truly busted, you'd know right away.
If you really and truly want to fix the dev database, here's the official word on repairing InnoDB from mysql:
http://dev.mysql.com/doc/refman/5.5/en/forcing-innodb-recovery.html
However you could start by simply copying the table in question to a new table and truncating the old one, then copying everything back:
(These are mysql commands) -
You'll have to do this from the command line, or perhaps phpMyAdmin can do it...?
But again if I were you, I'd get production working and then port it back to dev.
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
Do you think the problem is
Do you think the problem is occurring when I export the dev database or when I import it to the production site? Because my dev site works fine and it's the one with the full database.
This is very confusing because I don't know if there is a problem in the exportation perhaps corrupting the file making it import missing half the data. (that's for the whole database. Every table imports to the production site half the size it is on the dev site.)
The production site you visited is running on a database in which every table is half the size it is on the dev site and yet it works.
I sent a copy of the database to the host support team to look over (at their request) but they haven't gotten back to me yet.
You see, I started with a situation where I transferred my database from the dev site to the prod site and it was complete except for one table, menu_router. When I went onto the site for the first time all the multilingual stuff was broken in the menus. In trying to fix that problem I've went from that to a situation where I import the database to prod site and ALL tables are cut in half but it works fine.
It defies reality.
=-=
IIRC the devel.module allows a rebuilding of the menus if you want to go down that road.
only problem is i don't need
only problem is i don't need to rebuild any menus any more.
i have a dev site with a 25MB database that works fine and I have a production site with a database thats only 15MB and it seems to be working fine too. it shouldn't, but it is.
so i'm more confused than a cat in a catnip factory.
=-=
based on reading the B&M project page, the difference between the two databases may be due to the module excluding the cache table when exporting.
DB sizes
VM is right - database size isn't necessarily going to be consistent, because your cache tables are going to vary, and they won't be exported by B&M.
Earlier you were having trouble exporting the menu_router table, but your menus and navigation are working fine on both sites, right?
============================
Resonetrics: Better Tools for Building Brands
http://resonetrics.com
http://technologyformarketers.com
http://kittenassociates.org
http://www.linkedin.com/in/sammooreatresonetrics
yes, that is right,
yes, that is right, everything is working on both sites. i'm a little relieved to think that maybe the production site might be usable anyways by what you say. Just nervous as heck.
And I have all these people who are going to the site and they don't understand why I won't let them use it. They don't know too many things about computers so it's almost impossible to make them understand this situation. Because I don't even understand it. I think they're starting to think I'm crazy.
I have stumbled upon
I have stumbled upon something quite curious. It seems that I am exporting a database with InnoDB tables and managing somehow to import a database with MyISAM tables. My inexperience allowed me to miss that difference for quite some time. My apologies to all of you trying to help me.
And my apologies to Drupal. The problem is a loose nut between the steering wheel and the seat.
Now all I have to do figure out how I managed this wonderful accomplishment.
Okay, new question
Okay, so now I know that I have built an entire website before I realized that I am trying to use a host that does not support InnoDB.
Is this fixable? Can I reliably convert my database tables to MyISAM before importing? Is this a good idea? Should I rebuild the site or change hosts?
=-=
I'd find a new host.
Again, sorry, but I'm about
Again, sorry, but I'm about to ask the dumbest question of all...
Is there a chance that I could convert these tables and the site would function properly on MyISAM?
I appreciate your answer, but finding another webhost is not that easy. And if in the short term I can be reasonably sure the site will function ok until I can properly resolve this issue, then I would prefer to proceed that way. I won my own domain name and I can't use free sites that use hyphenated urls. And IF this site ends up on paid hosting that will not be up to me nor will I be paying for it.
Would you risk it?
=-=
If they don't allow INNODB, are they allowing PDO? if not, you may have other issues on your hands. Before launching the current site, I'd try installing Drupal 7 on the server to see if it installs without issue.
As far as switching the database engine and how it will impact your site on the hardware in use, I can't say with any confidence. I've venture that no one can. Thus, testing is prudent.
That was very helpful, thank
That was very helpful, thank you!