database messed up?

tmjr · 2005-11-24 09:59:02

Hello all,

In restoring a database dump, I think I have done something to wreck my textpattern installation; any help you could provide is greatly appreciated, as I would hate to type in all what I have generated all over again… :(

My main installation directory is called textpattern-4.0; I inadvertently deleted the files in the textpattern-4.0/textpattern/ directory – not the directories, just the files in that directory. I was running textpattern-4.0.1 on a linux system; this is at home on a personal machine, so I have full access to the machine (obviously, not always a good thing… :/ ); the machine is not publicly accessible.

To rectify the problem, I thought I would upgrade to textpattern-4.0.2. Before doing so, I dumped my database using mysqldump —tab <path> —opt website_db
where website_db is the name of my database, and <path> is the directory to which all this was dumped.

I deleted everything in the textpattern-4.0 directory and installed the textpattern-4.0.2 stuff there. When I ran index.php, it said it could not continue because the database I specified already existed.

Using phpMyAdmin, I deleted the website_db (thinking this was safe to do, since I made a dump of the database), recreated it and then ran the textpatten installation script. This time, the installation ran fine.

I tried to restore all the work I did on my website (articles, pages, forms, etc) by restoring the database dump by going mysql website_db < textpattern.sql, etc
(I did this with all 18 files; I had trouble with txp_priv.sql, but searching this forum I found what that problem was…)

When I now go to the website homepage ( <url>/textpattern/index.php), I get a “Textpattern Error: 404 Not Found” page saying Unknown section.

When I try to go to the textpattern page ( <url>/textpattern/textpattern/index.php), I get

<code>
Notice: Undefined variable: language in /home/titus/textpattern-4.0/textpattern/index.php on line 63

Warning: Cannot modify header information – headers already sent by (output started at /home/titus/textpattern-4.0/textpattern/index.php:63) in /home/titus/textpattern-4.0/textpattern/include/txp_auth.php on line 117
</code>

I assume I’ve somehow messed up the mysql database. How do I restore my previous work that’s (presumably) stored in the database dump I made?

In case its not obvious from my post, I am not very familiar with mysql, textpattern, or php…

Thank you for your assistance!

T. =8)
—
Titus Mathews

Last edited by Mary (2005-11-25 19:37:46)

Mary · 2005-11-24 14:46:23

Your name is Titus? Cool name. :)

You’re likely getting errors because Textpattern is missing key information from the database that it expects to find, which triggers another error, which…

…I think I have done something to wreck my textpattern installation

Nah, just a complication. As long as you have your backup – don’t get rid of it! – you’ll eventually get it back the way it was. :)

…I did this with all 18 files…

18 files? You didn’t just dump the database to a gzipped file? If not, for future reference, do that. It makes restoring easier later. Then you’d just run SQL, and upload the file, and it’d do it all for you. :)

Check for a table named “txp_prefs”. Is it there?

tmjr · 2005-11-24 18:27:41

Hi Mary!
Thanks for responding; I notice you are in Southern alberta; I’m located in Calgary.

Your name is Titus? Cool name. :)

Thanks for saying so! :)

18 files? You didn’t just dump the database to a gzipped file? If not, for future reference, do that. It makes restoring easier later. Then you’d just run SQL, and upload the file, and it’d do it all for you. :)

Heh. I didn’t know that you could dump the database into a single file. I just used a command I found in the mysql manual regarding backing up. Its when I found the command to restore the dump that I realized there was an easier way of doing it… :)

Check for a table named “txp_prefs”. Is it there?

Yes it is.

Thanks again for your help!

T. =8) 
— 
Titus Mathews Jr.

Mary · 2005-11-24 19:58:33

Lethbridge.

The ‘notice’ about the language made me think it was your prefs table, but it isn’t. I forgot that it was a ‘notice’, not a warning. And it’s there, of course, because you haven’t logged in yet. :D

About the headers already sent: that error usually means there is whitespace in the files where there shouldn’t be. Check for and remove any whitespace before or after the <?php ?> tags within /textpattern/include/txp_auth.php.

About the unknown section: that error means it either couldn’t find the section itself, or the page that section uses. Check for the tables “txp_section” and “txp_page”. Are they there, and if so, what’s in them?

tmjr · 2005-11-24 22:05:17

Hi again,

About the headers already sent: that error usually means there is whitespace in the files where there shouldn’t be. Check for and remove any whitespace before or after the <?php ?> tags within /textpattern/include/txp_auth.php.

I’m assuming you mean before or after the <?php ?> on the particular line containing the tag. There isn’t any whitespace before or after either tag.

About the unknown section: that error means it either couldn’t find the section itself, or the page that section uses. Check for the tables “txp_section” and “txp_page”. Are they there, and if so, what’s in them?

I’m not quite sure how to answer your question. Both txp_section and txp_page do exist in the database. I tried to find out what is in the tables by doing a query (again from something I found in the mysql manual – I DO NOT know what I am doing here…!):
<code>
mysql> select * from txp_section;
Empty set (0.00 sec)
</code>
<code>
mysql> select * from txp_page;
Empty set (0.00 sec)
</code>

In fact, whatever table I query, I get an empty set. Either I am not doing the query properly, or there’s nothing in the tables, or???

Thanks again!
T. =8)

zem · 2005-11-24 22:07:36

Something went wrong with the database backup or restore, by the look of it. Perhaps you used a different database name or table prefix last time?

Mary · 2005-11-24 22:12:39

Yes, you are doing the query correctly, and your tables are empty.

If you open up your whatever.sql, there is actual content in it, right? INSERT INTO... and so on?

tmjr · 2005-11-24 22:37:39

<blockquote>
> zem wrote: Something went wrong with the database backup or restore, by the look of it. Perhaps you used a different database name or table prefix last time?
</blockquote>

Certainly not knowingly. I used the same database name I always use for this website: website_db, and I didn’t specify a table prefix.

<blockquote>
> mary wrote: 
If you open up your whatever.sql, there is actual content in it, right? INSERT INTO… and so on?
</blockquote>
Yes there is. The txp_section.sql looks like this:
<code>

— MySQL dump 8.23 — — Host: localhost Database: website_db ————————————————————————————- — Server version 3.23.58-log — — Table structure for table `txp_section` — DROP TABLE IF EXISTS txp_section; CREATE TABLE txp_section ( name varchar(128) NOT NULL default ‘’, page varchar(128) NOT NULL default ‘’, css varchar(128) NOT NULL default ‘’, is_default int(2) NOT NULL default ‘0’, in_rss int(2) NOT NULL default ‘1’, on_frontpage int(2) NOT NULL default ‘1’, searchable int(2) NOT NULL default ‘1’, title varchar(255) NOT NULL default ‘’, PRIMARY KEY (name), PRIMARY KEY (name), UNIQUE KEY name (name) ) TYPE=MyISAM PACK_KEYS=1; /*!40000 ALTER TABLE `txp_section` DISABLE KEYS */;

</code>

and the other files are similar (I’m sorry – I can’t get the formatting on that file listing to look right; I tried wrapping the text in code tags and used br tags to break lines, but to no avail…).

There is an associated txp_section.txt file that contains data I recognise: my section names and such. Perhaps I am supposed to do something with .txt files as well? I strongly suspect that’s the problem, but I have no idea what to do with those files…

Thanks so much for both of your help!

T. =8)

</code>

zem · 2005-11-24 23:15:54

It looks like the mysql dump contains only the table structure, not the contents. And the first thing it does, when loading the dump, is delete the existing tables.

tmjr · 2005-11-25 01:11:57

Hallelujah!!! Problem solved!

As zem said, only the structure of the database was being created.

I looked around for more information on the mysqldump command I used (<notextile><code> mysqldump —tab <path> —opt website_db </code></notextile>). That command produces .sql files for the recreation of the table, and a separate .txt file for the data.

It turns out I have to use mysqlimport to load the tables once they have been created by the .sql scripts.
More formally, I have to issue a <code> mysqlimport website_db *.txt</code>.

Having done that, my data is back and textpattern is working fine again!!!

Mary and Zem, thanks so very much for your willingness to help me out; without your assistance I would not even had a clue where to look or what the problem might be!

Thanks again!!
T. =8)

Textpattern CMS

Textpattern CMS support forum

#1 2005-11-24 09:59:02

database messed up?

#2 2005-11-24 14:46:23

Re: database messed up?

#3 2005-11-24 18:27:41

Re: database messed up?

#4 2005-11-24 19:58:33

Re: database messed up?

#5 2005-11-24 22:05:17

Re: database messed up?

#6 2005-11-24 22:07:36

Re: database messed up?

#7 2005-11-24 22:12:39

Re: database messed up?

#8 2005-11-24 22:37:39

Re: database messed up?

#9 2005-11-24 23:15:54

Re: database messed up?

#10 2005-11-25 01:11:57

Re: database messed up?

Board footer