 | Errors when trying to manually do SQL |  |
 Posted: 06/21/2007 8:46 AM |
 |
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
 |
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
 |
I am having some problems running the SQL for two different mods.
When running the following SQL
| Code:
|
CREATE TABLE phpbb_smilies_cat (
`cat_id` smallint(3) unsigned NOT NULL auto_increment,
`cat_name` varchar(50) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`cat_order` smallint(3) NOT NULL default '0',
`cat_perms` tinyint(2) NOT NULL default '10',
`cat_group` varchar(255) default NULL,
`cat_forum` mediumtext NOT NULL,
`cat_special` tinyint(1) NOT NULL default '-2',
`cat_open` tinyint(1) NOT NULL default '1',
`cat_icon_url` varchar(100) default NULL,
`smilies_popup` varchar(20) NOT NULL default '',
PRIMARY KEY (cat_id)
);
TRUNCATE phpbb_smilies;
ALTER TABLE phpbb_smilies ADD cat_id smallint(5) NOT NULL default '';
ALTER TABLE phpbb_smilies ADD smilies_order smallint(5) NOT NULL default '';
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_columns', '4');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_rows', '5');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_icon_path', 'images/smiles');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_posting', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_popup', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_buttons', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_random', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_removal1', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_removal2', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_usergroups', '0');
|
I get the following error on this line;
| Code:
|
ALTER TABLE phpbb_smilies ADD cat_id smallint( 5 ) NOT NULL default '';
#1067 - Invalid default value for 'cat_id'
|
With this error below I can see that the table does not exist, but I am not sure why it doesn't. The mod doesn't state that it needs to have any other mods installed first in order to work, and I don't know if those tables are standard to phpbb or why they aren't there.
When trying to run the following SQL
| Code:
|
ALTER TABLE prefix_forums ADD forum_icon VARCHAR(100);
ALTER TABLE prefix_categories ADD cat_icon VARCHAR(100);
|
I get the following error
| Code:
|
ALTER TABLE prefix_forums ADD forum_icon VARCHAR( 100 ) ;
MySQL said:
#1146 - Table 'showpug1_phpb1.prefix_forums' doesn't exist
|
|
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/21/2007 4:27 PM |
|
|
|
|
|
| Site Admin |
| Posts |
30757 |
| Word Cnt. |
2,628,690 |
| BDay |
Jul 28 |
| Sign |
Leo |
| Sex |
 |
|
|
|
Joined: Sep 25, 2004
Local time: 10:35 AM
Location: St Pete, FL
|
|
|
|
|
|
It's probably too late, but make sure you have a good backup copy of the phpbb_smilies table before you run these queries. If this is the MOD I'm thinking of, it may be a real challenge to successfully install it, so it may take several tries before you are successful. Without a good backup copy of the phpbb_smilies table, you could lose all of the smilies and have a bigger mess on your hands...
| Code:
|
ALTER TABLE phpbb_smilies ADD cat_id smallint( 5 ) NOT NULL default '';
#1067 - Invalid default value for 'cat_id'
|
Since the cat_id is defined as a small integer value, it can only store numerals, not text. So the default must be a number, not a null value. You could change the default to a number such as 0:
| Code:
|
|
ALTER TABLE phpbb_smilies ADD cat_id smallint( 5 ) NOT NULL default 0;
|
These tables are misnamed. The prefix_ should be phpbb_ or whatever table prefix is used in this database. So it should look something like this:
| Code:
|
ALTER TABLE phpbb_forums ADD forum_icon VARCHAR(100);
ALTER TABLE phpbb_categories ADD cat_icon VARCHAR(100);
|
 |
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/21/2007 6:34 PM |
|
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
|
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
|
 on the second one.
I did take a full database backup before I started but should I take a backup of just the smilies table as well? Will it be easier to get it if we need it?
Thank you for your help.  |
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/21/2007 6:55 PM |
|
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
|
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
|
| You were right, it completely messed up the table and all of the smilies on the forum are gone. Can we get those back from my backup? I did a full back-up of the public_html folder as well as a full database backup before I started. |
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/21/2007 7:53 PM |
|
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
|
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
|
ok, I got the smilies back and with your suggestions I was able to run the SQL through with no errors, now it's on to the edits. Thank you nightrider!!  |
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/21/2007 9:28 PM |
|
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
|
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
|
Well, after installing and fixing a few bugs, there was still one major bug that was making it so you couldn't see any smilies already posted in the forum in IE, but firefox was fine. Also, in IE the drop down box for the categories wasn't showing up, even though it was in firefox. And the pop up window wasn't working in either browser. So after all of that she decided to forget about this mod as it's just being a pain in the butt.
Do you think you could help me write some SQL to reverse what we did, or should we just leave it well alone now... |
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/22/2007 4:31 AM |
|
|
|
|
|
| Site Admin |
| Posts |
30757 |
| Word Cnt. |
2,628,690 |
| BDay |
Jul 28 |
| Sign |
Leo |
| Sex |
|
|
|
|
Joined: Sep 25, 2004
Local time: 10:35 AM
Location: St Pete, FL
|
|
|
|
|
|
I'm sorry I wasn't around for most of this. Someone's site got hacked, so I've spent the night getting her forum back up and running. I've never seen a site get hacked so badly. I think someone gained host access to the server, then modified EVERY SINGLE php and HTM file as well as MOST HTML files. Needless to say, she didn't have good backups of her phpBB files or any of the other utilities on her server. This is a perfect example of why you should maintain good and current backups of all of your files and database, just in case. Since her host had their hands full from being hacked, they were of no help...
The main part to worry about with this MOD is the smilies table. If you restored it back to what it was before running the included SQL queries, I doubt that you have to worry about the rest of what was modified. You could probably leave things the way there are in that case...
|
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/22/2007 7:23 AM |
|
|
|
|
|
| Caravan |
| Posts |
282 |
| Word Cnt. |
31,380 |
| BDay |
Apr 14 |
| Sign |
Aries |
| Sex |
|
|
|
|
Joined: Nov 01, 2006
Local time: 10:35 AM
Location: Denver, PA
|
|
|
|
|
|
That's ok Nightrider, you are the nicest guy I know to help someone fix their board like that! You are definitely only as secure as your latest back-up....running off to do a back-up of my boards right now.
all of the provided SQL for this mod was run through successfully with your help above, and I have not reversed that SQL yet, only taken off all of the incuded files, as well as putting the back-up files back onto the server. For some reason I cant figure out if I can delete the added fields in the smilies table or not. I don't know why I didn't just put the back-up of that table back on after we decided not to use it. I was thinking you could maybe help me create some SQL to reverse what was added, if you think we should. I ran this through phpmyadmin
| Code:
|
CREATE TABLE phpbb_smilies_cat (
`cat_id` smallint(3) unsigned NOT NULL auto_increment,
`cat_name` varchar(50) NOT NULL default '',
`description` varchar(100) NOT NULL default '',
`cat_order` smallint(3) NOT NULL default '0',
`cat_perms` tinyint(2) NOT NULL default '10',
`cat_group` varchar(255) default NULL,
`cat_forum` mediumtext NOT NULL,
`cat_special` tinyint(1) NOT NULL default '-2',
`cat_open` tinyint(1) NOT NULL default '1',
`cat_icon_url` varchar(100) default NULL,
`smilies_popup` varchar(20) NOT NULL default '',
PRIMARY KEY (cat_id)
);
TRUNCATE phpbb_smilies;
ALTER TABLE phpbb_smilies ADD cat_id smallint(5) NOT NULL default 0;
ALTER TABLE phpbb_smilies ADD smilies_order smallint(5) NOT NULL default 0;
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_columns', '4');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_rows', '5');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_icon_path', 'images/smiles');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_posting', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_popup', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_buttons', '1');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_random', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_removal1', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_removal2', '0');
INSERT INTO phpbb_config (config_name, config_value) VALUES ('smilie_usergroups', '0');
|
|
|
|
|
|
| Back to Top |
|
|
| Re: Errors when trying to manually do SQL | |
| Posted: 06/22/2007 11:15 PM |
|
|
|
|
|
| Site Admin |
| Posts |
30757 |
| Word Cnt. |
2,628,690 |
| BDay |
Jul 28 |
| Sign |
Leo |
| Sex |
|
|
|
|
Joined: Sep 25, 2004
Local time: 10:35 AM
Location: St Pete, FL
|
|
|
|
|
|
| FTM wrote:
|
|
For some reason I cant figure out if I can delete the added fields in the smilies table or not. I don't know why I didn't just put the back-up of that table back on after we decided not to use it. I was thinking you could maybe help me create some SQL to reverse what was added, if you think we should. I ran this through phpmyadmin
|
The MOD totally changed the Smillies table. So the easiest way to fix this is to restore the phpbb_smilies table from the backup that you made. I would just change the new phpbb_smilies table to phpbb_smilies_new, then import the old one back...
If you don't have a backup of only the phpbb_smilies table, you can edit your database backup file and copy the phpbb_smilies query to a new sql file. Then you can import it...
|
|
|
|
|
| Back to Top |
|
|
| Information | |
|
Calendar
