https://www.icyphoenix.com/viewtopic.php?f=35&t=3892
-----------------------------------
Shadowed
Tue 18 Mar, 2008 22:00

MySQL Problems
-----------------------------------
Well I have failed to install Icy Phoenix successfully! It seems my MySQL is not liking the DB!
Here are some specs:
System: Windows
MySQL: 5.1 - 4.1(tried both)

My Error:
default db engine doesn´t support the fulltext index 

I have tried 2 guides and no luck with them.
[url=http://www.icyphoenix.com/viewtopic.php?f=4&t=2520]Guide 1
During the extraction, I received errors [cannot extract or w/e]. I still managed to place the 2 files in the right spot. After entering the install info, I got a MySQL error - Look in handbook for... not correct version... and so on. 

[url=http://www.icyphoenix.com/viewtopic.php?f=4&t=3313]Guide 2
I then tried this, but in my.ini file it was different. but i found INNODB and changed it to MyISAM. That also didnt work during the install.

Please help!


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 00:52

Re: MySQL Problems
-----------------------------------
Hi, this might seem a silly / obvious question, but are you sure it is MySQL and not MSSQL?


-----------------------------------
Shadowed
Wed 19 Mar, 2008 02:21

Re: MySQL Problems
-----------------------------------
Naw, It is MySQL. I have used it before but now when i try installing it on my Windows Server (Friends and their games... it would be linux) it gives me errors all over the place. It is after the screen where you enter you MySQL info and all [I get the following errors]:

First - TEXT ERROR thing (MyISAM)
Second[After Guide 1] - Wrong MySQL Version Error or w/e
Third[After Guide 2] - Same as first.


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 02:43

Re: MySQL Problems
-----------------------------------
Yes sorry, suddenly realised after posting mssql would make a hell of a lot more error messages than that if you tried it! 
Did you actually use the files from guide 1? Because the files there are out of date for the latest Icy Phoenix. I also think there is a fundamental problem in the second part of guide 1 (the INNODB zip file) because all that has been done is append TYPE=INNODB to all the tables, but nothing has been done to change the indexes that are used - where there are text indexes in the MYISAM tables, they remain text indexes in INNODB tables, surely this will cause the same "The used table type doesn't support FULLTEXT indexes" error... (in fact I'm just about to post this in that guide link).

I am thinking it would be better to modify the latest install script and update all the tables to type=myisam. I can do this very quickly in tswebeditor if you want...


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 02:52

Re: MySQL Problems
-----------------------------------
Did it in two shakes... actually I used ENGINE = MYISAM, as TYPE is very outdated - it is possible that the MS mysql server is not so backward compatible, and possibly why part 1 of guide 1 didn't work...

You probably know where to put this file, but I'll explain for future ref: ip_root/install/schemas

Hope it helps ;)


-----------------------------------
Shadowed
Wed 19 Mar, 2008 03:04

Re: MySQL Problems
-----------------------------------
Tried it on both MySQL 5.1 and 4.1 and received this error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE = MYISAM' at line 6


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 03:16

Re: MySQL Problems
-----------------------------------
Well, that rules out MS MYSQL being up-to-date...

Try this instead...


-----------------------------------
Shadowed
Wed 19 Mar, 2008 03:34

Re: MySQL Problems
-----------------------------------
Another error! =(

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE=MyIsam' at line 1


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 03:55

Re: MySQL Problems
-----------------------------------
Well this is really strange... all these variants work fine on mysql on a linux box (just tried them).

Just another shot in the dark now then, but perhaps it wants spaces between ) TYPE = xx, or MYISAM in caps... (this also works fine on my server, can't check windows server) - try this updated file then... although I'm beginning to think nothing will work... and that you might need to check with your host if MYISAM support is even installed on the mysql server...


-----------------------------------
Shadowed
Wed 19 Mar, 2008 04:06

Re: MySQL Problems
-----------------------------------
Naw same thing!
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE = MYISAM' at line 1

Well I host my own server and your saying i need support for MyISAM? Where would i get this?

Oh and thanks so much btw!


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 16:47

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26477"]Oh and thanks so much btw![/quote]You're welcome, sorry I am not much use here! I don't know about running mysql on windows, so missing engine support was just a guess, and probably wrong, since going by [url=http://dev.mysql.com/tech-resources/articles/4.1/installer.html]this article it appears myisam should be available no matter what install options you choose. (Perhaps it might be worth reinstalling mysql and changing the default engine choice, perhaps using the above article link for reference)?

Another possibility here may be the choice of delimiter at the end of the statement... the default in mysql is ";", but maybe your install has something else. It is easy to specify the delimiter by used of the delimiter command - just put [code linenumbers=false]delimiter ;[/code]on a line before the first create table statement, maybe that will help?

BTW do you have something like phpmyadmin running on your server? Maybe if will help for you to try running a single create table query in there - it being more straightforward to test different queries than modifying, uploading and executing the db schema file and install script...


-----------------------------------
moreteavicar
Wed 19 Mar, 2008 17:09

Re: MySQL Problems
-----------------------------------
Hmm actually a delimiter error would display the delimiter and the following CREATE TABLE line, not the type=myisam that precedes it...

Also, which version of MYSQL did you try the last file on? I say that because I've just found in mysql bugs section that mysql version 5.1 definitely produces the error "check the manual that corresponds to your MySQL server version for the right syntax to use near 'TYPE = MYISAM' at line 1 " - it now only supports ENGINE and not TYPE, but some people call this a bug because the main documentation specifies that although TYPE is deprecated, it is "still supported", which is incorrect... i.e. a bug with the documentation (or else they should include backward functionality again)! This is perhaps why I don't have any problems, my server is still running Server version: 5.0.51...

So... perhaps have a try in the mysql command window with a single create table declaration from the mysql schema file, but with ENGINE instead of TYPE...


-----------------------------------
novice programmer
Wed 19 Mar, 2008 21:00

Re: MySQL Problems
-----------------------------------
[b]moreteavicar[/b], you have to type this exactily:

[code linenumbers=false]TYPE=MyISAM[/code]

Notice, all caps are capital except the "y".


-----------------------------------
Shadowed
Thu 20 Mar, 2008 02:01

Re: MySQL Problems
-----------------------------------
Well now i have tried: MySQL - 4.1 ~ 5 ~ 5.1
Same all the way through... Im going to try whatever i can do tomorrow to get this. I am up for any suggestions!


-----------------------------------
moreteavicar
Thu 20 Mar, 2008 03:20

Re: MySQL Problems
-----------------------------------
[quote user="novice programmer" post="26521"][b]moreteavicar[/b], you have to type this exactily:

[code linenumbers=false]TYPE=MyISAM[/code]

Notice, all caps are capital except the "y".[/quote]No [b]novice programmer[/b]... mysql is [u]case insensitive[/u]. Read the docs next time ;) 

To help out, here we go or 5.1:

[quote="MySQL 5.1 Documentation"]"Keywords may be entered in any lettercase"[/quote]
Source: http://dev.mysql.com/doc/refman/5.1/en/entering-queries.html: 

Official engine declaration example with all CAPS:
[quote="MySQL 5.1 Documentation"]CREATE TABLE t (i INT) ENGINE = MYISAM;[/quote]
Source: http://dev.mysql.com/doc/refman/5.1/en/myisam-storage-engine.html

That caps are used at all is convention - but a logical convention, since it helps to distinguish between queries and table / field names. look in some of the phpbb / icy phoenix code, you sill see a few sql queries dotted about in lowercase - "select" as opposed to SELECT being a common one.

You should be able to issue the command: 
[code linenumbers=false]create table tablename (id int) engine = myisam; [/code] or 
[code linenumbers=false]create table tablename2 (id int) type = myisam; [/code] Try it... ;)


-----------------------------------
moreteavicar
Thu 20 Mar, 2008 03:22

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26535"]Well now i have tried: MySQL - 4.1 ~ 5 ~ 5.1
Same all the way through... Im going to try whatever i can do tomorrow to get this. I am up for any suggestions![/quote]

Will that be running a linux server? :mrgreen: 

BTW Have you tried installing just the latest phpbb3 just to see if that works?


-----------------------------------
Shadowed
Thu 20 Mar, 2008 04:07

Re: MySQL Problems
-----------------------------------
Alright that seems to have worked with 5.0.

[IMG]http://i235.photobucket.com/albums/ee170/ShadowedIMG/MySQL_IP.jpg[/IMG]
[url=http://i235.photobucket.com/albums/ee170/ShadowedIMG/MySQL_IP.jpg]Link [Bigger?]


-----------------------------------
moreteavicar
Thu 20 Mar, 2008 14:33

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26544"]Alright that seems to have worked with 5.0.
[/quote]

Bravo! So does it also work with 5.1?

Have you also tried with CAPS?:

[code linenumbers=false]CREATE TABLE tablename (id int) ENGINE = MYISAM;[/code]
[code linenumbers=false]CREATE TABLE tablename2 (id int) TYPE = MYISAM;[/code]

The latter (type declaration) should generate an error in 5.1 unless mysql put backward compatibility back in.
We need to try all these different things see why / where there might be a bug (in mysql) ;)


-----------------------------------
Shadowed
Thu 20 Mar, 2008 21:39

Re: MySQL Problems
-----------------------------------
[quote user="moreteavicar" post="26539"]

Will that be running a linux server? :mrgreen: 

BTW Have you tried installing just the latest phpbb3 just to see if that works?[/quote]

Arggg, I wish I could just use linux... Dang friends and their games...

[quote user="moreteavicar" post="26558"][quote user="Shadowed" post="26544"]Alright that seems to have worked with 5.0.
[/quote]

Bravo! So does it also work with 5.1?

Have you also tried with CAPS?:

[code linenumbers=false]CREATE TABLE tablename (id int) ENGINE = MYISAM;[/code]
[code linenumbers=false]CREATE TABLE tablename2 (id int) TYPE = MYISAM;[/code]

The latter (type declaration) should generate an error in 5.1 unless mysql put backward compatibility back in.
We need to try all these different things see why / where there might be a bug (in mysql) ;)[/quote]

[IMG]http://i235.photobucket.com/albums/ee170/ShadowedIMG/MySQL_IP2.jpg[/IMG]
[url=http://i235.photobucket.com/albums/ee170/ShadowedIMG/MySQL_IP2.jpg]Link [Bigger?]

I havnt tried 5.1 again, ill do that soon!


-----------------------------------
moreteavicar
Fri 21 Mar, 2008 01:51

Re: MySQL Problems
-----------------------------------
As 5.0 seems to be doing sensible things, maybe also have a try with phpbb3 and icy phoenix again? Depends what's quicker - installing these boards or changing database servers (though I imagine you can have them all "installed", but simply choose which one actually runs)...


-----------------------------------
Shadowed
Fri 21 Mar, 2008 04:14

Re: MySQL Problems
-----------------------------------
Ok well my PHPBB is installed on my 5.1.
Just one question, which .sql files do i use?

EDIT: I used the one with ENGINE = MYISAM

Didnt work...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE = MYISAM' at line 6


-----------------------------------
novice programmer
Fri 21 Mar, 2008 13:51

Re: MySQL Problems
-----------------------------------
[quote user="moreteavicar" post="26538"]...
No [b]novice programmer[/b]... mysql is [u]case insensitive[/u]. Read the docs next time ;) 
...[/quote]

I did not read the SQL documentation.

It did not work on a localhost (Wamp) server until I built it like that I posted before.


-----------------------------------
moreteavicar
Fri 21 Mar, 2008 14:34

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26594"]Ok well my PHPBB is installed on my 5.1.
Just one question, which .sql files do i use?

EDIT: I used the one with ENGINE = MYISAM

Didnt work...
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ENGINE = MYISAM' at line 6[/quote]

If you've moved on to 5.1 again, then just try all the simple single "create table" statements you've tried previously on 5.0 that worked - with each version, do the simple things first before trying to do a full script. Don't try and run before you can walk. ;)


-----------------------------------
moreteavicar
Fri 21 Mar, 2008 14:51

Re: MySQL Problems
-----------------------------------
[quote user="novice programmer" post="26626"][quote user="moreteavicar" post="26538"]...
No [b]novice programmer[/b]... mysql is [u]case insensitive[/u]. Read the docs next time ;) 
...[/quote]

I did not read the SQL documentation.

It did not work on a localhost (Wamp) server until I built it like that I posted before.[/quote]Perhaps this is a new phenomena. Queries in MySQL have always been case insensitive - you don't even need to read the docs, just look at all the code thats out there (or in phpbb / icy) full of mixed case queries. If this has changed, then its a new development. We should distinguish here between SQL queries, and the field names, e.g. table names generally are case-sensitive - it depends on the OS - usually its Unix / Linux that is case sensitive and Windows that is case insensitive - because thats exactly how the file system works. It is for this reason that mysql advises all table names to be in lower case, to avoid problems in porting databases from one OS to another. Fields can be made case-sensitive if its defined as a binary field or a "blob".

BTW What sql server version? (maybe it has some bearing here)...


-----------------------------------
Shadowed
Fri 21 Mar, 2008 15:50

Re: MySQL Problems
-----------------------------------
[quote user="moreteavicar" post="26633"]If you've moved on to 5.1 again, then just try all the simple single "create table" statements you've tried previously on 5.0 that worked - with each version, do the simple things first before trying to do a full script. Don't try and run before you can walk. ;)[/quote]

I am still trying on 5.0, and no luck there. I have tried all your .sql files that you suggested on 5.1 ~ 4.1 ~ and 5.0.


-----------------------------------
novice programmer
Sat 22 Mar, 2008 00:19

Re: MySQL Problems
-----------------------------------
[quote user="moreteavicar" post="26636"][quote user="novice programmer" post="26626"][quote user="moreteavicar" post="26538"]...
No [b]novice programmer[/b]... mysql is [u]case insensitive[/u]. Read the docs next time ;) 
...[/quote]

...

BTW What sql server version? (maybe it has some bearing here)...[/quote]

Server version: 5.0.45-comunity-nt

so it is going to being case sensitive in newest versions :mricy: 

I did not know this, I just pasted the phpmyadmin code for a MyISAM engine table into the scripts...


-----------------------------------
moreteavicar
Sat 22 Mar, 2008 13:51

Re: MySQL Problems
-----------------------------------
[quote user="novice programmer" post="26651"]Server version: 5.0.45-comunity-nt

so it is going to being case sensitive in newest versions :mricy: 

I did not know this, I just pasted the phpmyadmin code for a MyISAM engine table into the scripts...[/quote]I have 5.0.51 on my server (linux). Its still case-insensitive for queries. Maybe this is config related, though I haven't seen anything on mysql site about setting up config in this way... :?


-----------------------------------
moreteavicar
Sat 22 Mar, 2008 14:33

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26639"][quote user="moreteavicar" post="26633"]If you've moved on to 5.1 again, then just try all the simple single "create table" statements you've tried previously on 5.0 that worked - with each version, do the simple things first before trying to do a full script. Don't try and run before you can walk. ;)[/quote]

I am still trying on 5.0, and no luck there. I have tried all your .sql files that you suggested on 5.1 ~ 4.1 ~ and 5.0.[/quote]I think its better just to try a single CREATE TABLE statement from the SQL files, not the entire script. It has all the appearance of being perhaps another issue, because the very simple create table statements you have tried earlier all worked ok. So here are two things to try, we need to try and rule out each step in order:

1) Try running the first create table statement in the sql files in your server (CREATE TABLE phpbb_acronyms, up to the delimiter ";" so you include the full statement, otherwise you'll get errors for sure! ;) ). 

2) Following this, if any (or all) of the first create table statements from the schema files work, then it could be that the errors you're experiencing is through parsing the sql statements from the install script to the MySQL server. The only possible reason I can think of is that the full database schema is loaded into memory for the phpbb sql parser & query functions, and maybe there is insufficient resources to generate the query properly, or that the sql parser is doing something unexpected through some subtle, as yet unknown difference between php functions for commands like htmlspecialchars & explode. So I suggest step is to try installing the database sql schema not through the icyphoenix installer, but by importing it directly in your mysql server. I think you do this with:[code linenumbers=false]mysql -u -p -h localhost dbname < schemafile.sql[/code]where you substitute the appropriate names for localhost, dbname, and schemafile.sql appropriately. I'm not sure if this is completely correct (-u and -p should prompt for user name and password), as I can't test it, I also don't know where you place the schemafile.sql - usually its your home directory. BTW if you are already logged into your database (prompt shows mysql>) you should be able to install the sql file with just [code linenumbers=false]. schemafile.sql[/code] 

Some info on executing batch commands from text file can be found from [url=http://dev.mysql.com/doc/refman/5.0/en/batch-commands.html]here, though can't see anything to explain where exactly the file should be located.


-----------------------------------
Shadowed
Sat 22 Mar, 2008 16:32

Re: MySQL Problems
-----------------------------------
Alright i gave the one table thing a try and then i took it further. I found 3 things that error me:
[quote]	FULLTEXT KEY `comment_bbcode_uid` (`comment_bbcode_uid`) [/quote]
[quote] ALTER TABLE `phpbb_topics` ADD FULLTEXT (topic_title);[/quote]
[quote]## `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`),
);

## `phpbb_pa_comments`
[/quote]

After removing that i was able to Import the DB directly. I then tried it through Icy and got a new error:
[quote]Table 'ip_cmsdb.ip_cms_block_position' doesn't exist[/quote]
Kinda strange because i dont think i touched that. I am gonna continue testing all day. I hope i can get this soon!

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);[/quote]


-----------------------------------
novice programmer
Sun 23 Mar, 2008 22:36

Re: MySQL Problems
-----------------------------------
[quote user="moreteavicar" post="26665"][quote user="novice programmer" post="26651"]Server version: 5.0.45-comunity-nt

so it is going to being case sensitive in newest versions :mricy: 

I did not know this, I just pasted the phpmyadmin code for a MyISAM engine table into the scripts...[/quote]I have 5.0.51 on my server (linux). Its still case-insensitive for queries. Maybe this is config related, though I haven't seen anything on mysql site about setting up config in this way... :?[/quote]

My server is hosted in a Windows longhorn OS.


-----------------------------------
moreteavicar
Mon 24 Mar, 2008 02:47

Re: MySQL Problems
-----------------------------------
[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);[/quote]
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 [u]mixed table types[/u] - 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: 

[code linenumbers=false]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`)
) ENGINE=MyISAM;[/code]

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)...


-----------------------------------
Shadowed
Mon 24 Mar, 2008 17:08

Re: MySQL Problems
-----------------------------------
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[/quote]


-----------------------------------
moreteavicar
Sun 30 Mar, 2008 01:29

Re: MySQL Problems
-----------------------------------
[quote user="Shadowed" post="26741"]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 [/quote] :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[/quote]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:

[code linenumbers=false]CREATE TABLE `eezydemo_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;[/code]

Actually, you know what... I'll give you another sql file to use, but instead of using icyphoenix installer, try my [url=http://www.icyphoenix.com/viewtopic.php?f=21&t=3944]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 [i]should[/i]work. 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).


-----------------------------------
moreteavicar
Sun 30 Mar, 2008 01:39

Re: MySQL Problems
-----------------------------------
BTW put the zip in your icy root directory, not install, and then extract it ;)


-----------------------------------
Shadowed
Tue 01 Apr, 2008 03:49

Re: MySQL Problems
-----------------------------------
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!


-----------------------------------
moreteavicar
Tue 01 Apr, 2008 18:15

Re: MySQL Problems
-----------------------------------
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:


-----------------------------------
Shadowed
Wed 02 Apr, 2008 02:03

Re: MySQL Problems
-----------------------------------
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)

[color=#00ff4c]What I fixed:[/color] Some errors were occurring with BLOB/TEXT Column so I googled it and [url=http://www.mydigitallife.info/2007/07/09/mysql-error-1170-42000-blobtext-column-used-in-key-specification-without-a-key-length/]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!

[color=#ff0000]Here is how I managed to do it (for those that have ran into the same problem):[/color]
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!


-----------------------------------
moreteavicar
Wed 02 Apr, 2008 15:18

Re: MySQL Problems
-----------------------------------
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[/quote]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',
	 [color=#ff0019][b]long_desc TEXT DEFAULT '',[/b][/color]
	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 '',
[color=#008cff]req TEXT DEFAULT '',
	todo TEXT DEFAULT '',
	warning TEXT DEFAULT '',
	mod_desc TEXT DEFAULT '',[/color]
	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)
);
[/quote]

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',
	[color=#ff0033][b]shout_text TEXT NOT NULL,[/b][/color]
	shouter_ip VARCHAR(8) NOT NULL default '',
	shout_uid VARCHAR(10) NOT NULL default '',
	shout_time INT(11) NOT NULL,
	PRIMARY KEY ( shout_id )
);[/quote]

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 [b]missing[/b]!...

[quote]
[b]Result from SHOW CREATE TABLE icy_downloads:[/b]

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',
 [color=#008800][b]`long_desc` text,[/b][/color]
 `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 '',
[color=#008800][b] `req` text,
 `todo` text,
 `warning` text,
 `mod_desc` text,[/b][/color]
 `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[/quote]

[b]
So what we have here is some perhaps unanticipated behaviour when creating tables: [u]MySQL usually removes the default assignment on text fields[/u][/b], 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?


-----------------------------------
moreteavicar
Wed 02 Apr, 2008 15:47

Re: MySQL Problems
-----------------------------------
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 [/quote]


