Icy Phoenix

[quote user="Shadowed" post="26677"]
EDIT: I tried a band new .sql and imported it again, here are the errors:

Quote:

[Err] 1214 - The used table type doesn't support FULLTEXT indexes
[Err] ## `phpbb_pa_cat`


## --------------------------------------------------------

## `phpbb_pa_comments`

CREATE TABLE `phpbb_pa_comments` (
`comments_id` int(10) NOT NULL auto_increment,
`file_id` int(10) NOT NULL default '0',
`comments_text` text NOT NULL,
`comments_title` text NOT NULL,
`comments_time` int(50) NOT NULL default '0',
`comment_bbcode_uid` varchar(10) default NULL,
`poster_id` mediumint(8) NOT NULL default '0',
PRIMARY KEY (`comments_id`),
KEY `comments_id` (`comments_id`),
FULLTEXT KEY `comment_bbcode_uid` (`comment_bbcode_uid`)
);
[Msg] Finished - Unsuccessfully
--------------------------------------------------
[Err] ALTER TABLE `phpbb_topics` ADD FULLTEXT (topic_title);

Hi Shadowed, its good that you're getting slightly further, however it looks like we're going in circles here. It looks to me like you're trying to import tables without any engine statement. By virtue of your very first post in this topic, you had innodb as default, which doesn't support fulltext keys, and the work around here was to try and get you installed with myisam, because it allows fulltext keys.

The point of of the last few suggestions was to try and see why conventional engine declarations weren't working with the install script, yet worked as expected with small (single table) queries. If we aren't going to get to the route of why you're server rejects the install scripts with the engine declarations, then you could just simply insert all the tables that don't work manually. What I mean here, is that MySQL allows you to have a database with mixed table types - you could have most of your tables in your default innodb, and the ones that don't work, you insert through command line (or maybe import through a small sql file with just those tables in). For example, in the above table you would simply use:



Anywhere where you see the FULLTEXT KEY, the error occurs, so these can be defined as ENGINE=MyISAM; by default. The only thing is we are none the wiser as to the real cause of the problem, so in the future, if you need to back-up and reinsall your database, you could have similar problems.

There is one thing which I have forgotten to ask, which should have been the very first thing, except normally its even more obvious than what I asked due to a couple of topics here: did you actually specify MYSQL4 or MYSQL as the database on install.php? (this also goes with the engine modded files)...

During the install I go with MySQL 4.x/5.x -

So I started manually adding some tables and removing them from the schema. I got the whole schema in and during an update I recieve a new error:

Quote:

An error occurred trying to update the database
BLOB/TEXT column 'long_desc' can't have a default value

Shadowed wrote: [View Post]

During the install I go with MySQL 4.x/5.x -

So I started manually adding some tables and removing them from the schema. I got the whole schema in and during an update I

:shock: Each one individually? Like the dedication ;)

recieve a new error:

Quote:

An error occurred trying to update the database
BLOB/TEXT column 'long_desc' can't have a default value

Hmmm... what table type were you trying to set? Either way I think you can safely skip the default line - its not actually important, I think its used only in checking routines to see if its empty or not...

come to think of it... this is for table "downloads" right? in my database, there isn't even a default for this table... I think you could just use:



Actually, you know what... I'll give you another sql file to use, but instead of using icyphoenix installer, try my EasyDump Installer which should help out. SQL dump is based on a fresh install - its just dumped out again using my EasyDump, which uses a direct syntax return from MYSQL, so it should shouldwork. One snag is that my installer doesn't take admin user name to insert into your dB, as I built it to install backups, where such details would normally be in. I think you can get round this by modifying the table directly, and then changing password after logging in (password is blank in SQL, so you can log in without a password at all).

BTW put the zip in your icy root directory, not install, and then extract it ;)

Alright well I spent a while trying some new things out. Still some errors but I am getting closer!
Heres some steps I followed:
1)Fresh Icy Phoenix
2)Ran Install (hoping something magic happened) [Failed with MyISAM Error]
3)Changed MySQL Default to MyISAM and Restart
4)Ran Install [Failed with BLOB/TEXT column 'long_desc' Error]
5)Ran EasyDumpInstaller and something happened [Error Bellow]
6)Changed MySQL Default to MYISAM and Restart
7)Ran Install [Failed with BLOB/TEXT column 'long_desc' Error again]
8)Ran EasyDumpInstaller and something happened again [Error Bellow]
9)Post all findings here!

Alright so I know I am going to get this soon, just going to research more into this stuff. But the reason why I am posting (asking for help) is to cut down research time. Also I would like to thank moreteavicar for all the help you have provided and maybe more help to come!

Hile, no problem :wink:

Since I am author of a couple mysql database backup utilities, I want to know why these errors occur...

now for the errors you experienced using my installer and the database dump above... now this is my fault :oops:
That dump is an actual database dump from a live more-or-less empty site, but I removed some fields relevant to my site, I think I had one or two posts, and a cms block. But I might have removed something else in the INSERT line by mistake - I will have to check.

It is why those errors appear where they do in the list - they come just after the cms block, users and posts tables have been created (the error is happening in the insert, since it is between create table statements - perhaps I should improve the output there). When I find what I did wrong, I'll upload a correct dump - in fact I'll test it first, like I should have done last time :oops:

:v: But we are on the right track - you actually have all your tables created, which never worked before... :D
...Although we are none the wiser as to why the original phpbb / icy phoenix installer didn't work... :?
My installer is obviously much better :mrgreen:

Well I have done it! I got it installed (With the Icy Phoenix Installer) (dont know if errors may happen later on but thats what testing is for!)
Every error that popped up, I searched, then fixed (to my knowledge of MySQL)

What I fixed: Some errors were occurring with BLOB/TEXT Column so I googled it and found a site that helped me with my problem. I changed all BLOB/TEXT errors to VARCHAR(255). Hope that will do it!

Thank you once again for all the help!

Here is how I managed to do it (for those that have ran into the same problem):
Change my.ini (MySQL Directory) [Default Database=INNODB TO MyISAM]
Restart Computer
Go thru your mysql_schema.sql and change all errors that occur (may be lots)
or
Replace with mine and try!

Good to see you've found your own solution ;)

I am still confused though, because surely you could resolve this just by removing the default assignments, rather than changing field type to VARCHAR(255)?
Consider your original BLOB/TEXT column error:

Quote:

An error occurred trying to update the database
BLOB/TEXT column 'long_desc' can't have a default value

The field quoted is 'long_desc', which is from phpbb_downloads table, and indeed it has a default assignment. Here is the create table for this from the icy 12027 mysql_schema.sql:

Quote:

CREATE TABLE phpbb_downloads (
id INT(11) auto_increment NOT NULL,
description VARCHAR(255),
file_name VARCHAR(255) DEFAULT '',
klicks INT(11) DEFAULT '0',
free TINYINT(1) DEFAULT '0',
extern TINYINT(1) DEFAULT '0',
long_desc TEXT DEFAULT '',
sort INT(11) DEFAULT '0',
cat INT(11) DEFAULT '0',
hacklist TINYINT(1) DEFAULT '0',
hack_author VARCHAR(255) DEFAULT '',
hack_author_email VARCHAR(255) DEFAULT '',
hack_author_website TINYTEXT DEFAULT '',
hack_version VARCHAR(32) DEFAULT '',
hack_dl_url TINYTEXT DEFAULT '',
test varchar(50) DEFAULT '',
req TEXT DEFAULT '',
todo TEXT DEFAULT '',
warning TEXT DEFAULT '',
mod_desc TEXT DEFAULT '',
bbcode_uid VARCHAR(10) DEFAULT '',
mod_list TINYINT(1) DEFAULT '0',
file_size BIGINT(20) NOT NULL DEFAULT '0',
change_time INT(11) DEFAULT '0',
add_time INT(11) DEFAULT '0',
rating SMALLINT(5) DEFAULT '0' NOT NULL,
file_traffic BIGINT(20) NOT NULL DEFAULT '0',
overall_klicks INT(11) DEFAULT '0',
approve TINYINT(1) DEFAULT '0',
add_user MEDIUMINT(8) DEFAULT '0',
change_user MEDIUMINT(8) DEFAULT '0',
last_time INT(11) DEFAULT '0',
down_user MEDIUMINT(8) DEFAULT '0' NOT NULL,
thumbnail VARCHAR(255) DEFAULT '' NOT NULL,
broken TINYINT(1) NOT NULL DEFAULT 0,
PRIMARY KEY (id)
);

As you can see it does have a default value, and there are plenty of other TEXT fields with default assignments in the same table. However, before this table is created, there are plenty of tables with TEXT fields, which I take it didn't produce errors, since you got though up to the downloads table. A good example is:

Quote:

CREATE TABLE phpbb_ajax_shoutbox (
shout_id MEDIUMINT(9) UNSIGNED NOT NULL AUTO_INCREMENT,
user_id MEDIUMINT(8) NOT NULL,
shouter_name VARCHAR(30) NOT NULL default 'guest',
shout_text TEXT NOT NULL,
shouter_ip VARCHAR(8) NOT NULL default '',
shout_uid VARCHAR(10) NOT NULL default '',
shout_time INT(11) NOT NULL,
PRIMARY KEY ( shout_id )
);

So it seems possible to generate a text filed, without limiting it to VARCHAR(255), just by removing the default assignment (BTW the problem here would be that if you were entering the text into those fields, if you went over the limit of 255 characters, you might get a mysql server error, and also the text/data would be cut off - probably not a big problem for the tables in question, unless somebody had a download which needed a lot of explaining!).

The interesting point here though is that if you use the query SHOW CREATE TABLE phpbb_downloads on a MYSQL server which installed the above phpbb_downloads without a problem (so not yours ;) ) then you see these TEXT fields with the default values missing!...

Quote:

Result from SHOW CREATE TABLE icy_downloads:

CREATE TABLE `icy_downloads` (
`id` int(11) NOT NULL auto_increment,
`description` varchar(255) default NULL,
`file_name` varchar(255) default '',
`klicks` int(11) default '0',
`free` tinyint(1) default '0',
`extern` tinyint(1) default '0',
`long_desc` text,
`sort` int(11) default '0',
`cat` int(11) default '0',
`hacklist` tinyint(1) default '0',
`hack_author` varchar(255) default '',
`hack_author_email` varchar(255) default '',
`hack_author_website` tinytext,
`hack_version` varchar(32) default '',
`hack_dl_url` tinytext,
`test` varchar(50) default '',
`req` text,
`todo` text,
`warning` text,
`mod_desc` text,
`bbcode_uid` varchar(10) default '',
`mod_list` tinyint(1) default '0',
`file_size` bigint(20) NOT NULL default '0',
`change_time` int(11) default '0',
`add_time` int(11) default '0',
`rating` smallint(5) NOT NULL default '0',
`file_traffic` bigint(20) NOT NULL default '0',
`overall_klicks` int(11) default '0',
`approve` tinyint(1) default '0',
`add_user` mediumint(8) default '0',
`change_user` mediumint(8) default '0',
`last_time` int(11) default '0',
`down_user` mediumint(8) NOT NULL default '0',
`thumbnail` varchar(255) NOT NULL default '',
`broken` tinyint(1) NOT NULL default '0',
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1

So what we have here is some perhaps unanticipated behaviour when creating tables: MySQL usually removes the default assignment on text fields, however in your case it doesn't - perhaps there is an option in the MySQL server installation that enables this? Or perhaps this is a particular problem with the windows version of MySQL?

Actually I found perhaps the real cause of this problem:

http://bugs.mysql.com/bug.php?id=14306

You need to check your server my.ini configuration file

Quote:

opent my.ini in folder D:Program FilesMySQLMySQL Server 5.0 , or whatever folder
i changed
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
to
sql-mode=""

restart mysql service
and work fine for me