Help - "error while connecting to database:"

[yorkshire_boy1]

Hi

I Have installed and used this CMS 4 or 5 times over the last 3 years, but now have the following problem after a recent installation of 1.3.3.

I installed the CMS as usual, everything seems fine. then after logging into the backend, i tried to add a test article in the home page, and when I click "Create new article" i get the following error:

-------
error while connecting to database:
INSERT INTO dal_phpwcms_article (article_cid, article_uid, article_username, article_title, article_keyword, article_public, article_aktiv, article_begin, article_end, article_subtitle, article_redirect, article_sort, article_notitle, article_created, article_cache, article_nosearch, article_nositemap, article_aliasid, article_headerdata, article_morelink, article_pagetitle) VALUES ('0','1','Webmaster','ef','','1','1','2007-09-20 09:11:49','2017-09-17 23:59:59','','', 20,0, '1190275912', '', '', 1, 0, 0, 1, '')
--------

I don't understand this, as the database exists, and some parts of the CMS work, ie I can add site structure fine, but not articles.

So I logged into my database using a VPN and HeidiSQL, and tried to run the above SQL statement that is causing the error direct to the database, and get an error saying article_summary does not have a default value. So it looks like the CMS is trying to send incomplete data to the database, how can this be?

i have wiped the CMS, reuploaded, reinstalled and retried 5 times now, and still the same error. Each time I also prepend the database tables with something new, so in effect I create new database tables each time.

Does anyone have any ideas?

Many thanks

[DeXXus]

Fresh NEW install -or- upgrade? New VIRGIN database -or- imported from prior installation? What version of MySQL? Is SQL strict mode enabled in MySQL (try disabling).

[yorkshire_boy1]

Thanks for the reply Dexxus, its a new install, not upgrade, and a virgin database (or was on first installation attempt!!)

Mysql version is 5.0.2. Ill check the SQL mode when I get home tonight and try that and let you know.

[aaronazz]

I am experiencing the exact same problem with a fresh install of PHPWCMS 1.3.3 (and I've done many successful installations before - even 2 recently with 1.3.3). I have tried setup and creating tables with the same result many times. I can do everything BUT create new articles. I can do template and page layouts with no problem.

The only difference this time around is the server platform, a Windows-based running Microsoft-IIS/6.0, PHP 5.2.5, MySQL 5.0.45 - Others have always been with CentOS/Plesk Virtuozzo platforms. I have looked at the suggestions of fixing auto-increment in the 'article' table.

Thanks for your help in advance.

[update]

Dit you enter the right MySql version into your conf.inc.php? MySQL 5.0.45 --> 50045 I suppose should be the right entry...

[aaronazz]

Changed the DB in conf.phpwcms.php to:

Code: Select all

$phpwcms['db_version'] = 50045;

The setup had originally created it as:

Code: Select all

$phpwcms['db_version'] = 50027;

This is the full conf (minus sensitive data):

Code: Select all

<?php

// database values
$phpwcms['db_host']           = 'localhost';
$phpwcms['db_user']           = 'XXXXXXXXXX';
$phpwcms['db_pass']           = 'XXXXXXXXXX';
$phpwcms['db_table']          = 'macchoice';
$phpwcms['db_prepend']        = '';
$phpwcms['db_pers']           = 1;
$phpwcms['db_charset']        = 'utf8';
$phpwcms['db_collation']      = 'utf8_general_ci';
$phpwcms['db_version']        = 50045;

// site values
$phpwcms['site']              = 'XXXXXXXXXX';
$phpwcms['admin_name']        = 'Webmaster'; //default: Webmaster
$phpwcms['admin_user']        = 'XXXXXXXXXX'; //default: admin
$phpwcms['admin_pass']        = 'XXXXXXXXXX'; //MD5(phpwcms)
$phpwcms['admin_email']       = 'XXXXXXXXXX'; //default: noreplay@host

// paths
$phpwcms['DOC_ROOT']          = $_SERVER['DOCUMENT_ROOT'];// real DOC_ROOT seems to be: 'XXXXXXXXXX' 
$phpwcms['root']         		= 'dev';         //default: ''
$phpwcms['file_path']         = 'filearchive';    //default: 'filearchive'
$phpwcms['templates']         = 'template';    //default: 'template'
$phpwcms['content_path']      = 'content'; //default: 'content'
$phpwcms['cimage_path']       = 'images';  //default: 'images'
$phpwcms['ftp_path']          = 'upload';     //default: 'upload'

// content values
$phpwcms['file_maxsize']      = 2097152; //Bytes (50 x 1024 x 1024)
$phpwcms['content_width']     = 538; //max width of the article content column - important for rendering multi column images
$phpwcms['img_list_width']    = 100; //max with of the list thumbnail image
$phpwcms['img_list_height']   = 75; //max height of the list thumbnail image
$phpwcms['img_prev_width']    = 538; //max width of the large preview image
$phpwcms['img_prev_height']   = 400; //max height of the large preview image
$phpwcms['max_time']          = 8600; //logout after max_time/60 seconds

// other stuff
$phpwcms['compress_page']     = 0; //0 = OFF, 1-9: page compression ON (1 = low level, 9 = highest level)
$phpwcms['imagick']           = 0; //0 = GD,  1 = ImageMagick, 2 = ImageMagick 4.2.9
$phpwcms['imagick_path']      = ''; //Path to ImageMagick (default='' - none)
$phpwcms['use_gd2']           = 1; //0 = GD1, 1 = GD2
$phpwcms['rewrite_url']       = 0;  //whether URL should be rewritable
$phpwcms['wysiwyg_editor']    = 2;  //0 = no wysiwyg editor, 2 = FCKeditor, 4 = spaw
$phpwcms['phpmyadmin']        = 0;  //enable/disable phpMyAdmin in Backend
$phpwcms['default_lang']      = 'en';  //default language
$phpwcms['charset']           = 'utf-8';  //default charset 'iso-8859-1'
$phpwcms['allow_remote_URL']  = 1;  //0 = no remote URL in {PHP:...} replacement tag allowed, 1 = allowed
$phpwcms['gt_mod']            = 1;  //0 = Graphical Text MOD disabled, 1 = enabled
$phpwcms['jpg_quality']       = 95; //JPG Quality Range 25-100
$phpwcms['sharpen_level']     = 1;  //Sharpen Level - only ImageMagick: 0, 1, 2, 3, 4, 5 -- 0 = no, 5 = extra sharp
$phpwcms['allow_ext_init']    = 1;  //allow including of custom external scripts at frontend initialization
$phpwcms['allow_ext_render']  = 1;  //allow including of custom external scripts at frontend rendering
$phpwcms['cache_enabled']     = 0;        //cache On/Off - 1 = caching On / 0 = caching Off (default)
$phpwcms['cache_timeout']     = 0;  //default cache timeout setting in seconds - 0 = caching Off
$phpwcms['imgext_disabled']   = '';  //comma seperated list of imagetypes which should not be handled 'pdf,ps'
$phpwcms['multimedia_ext']    = 'aif,aiff,mov,movie,mp3,mpeg,mpeg4,mpeg2,wav,swf,swc,ram,ra,wma,wmv,avi,au,midi,moov,rm,rpm,mid,midi'; //comma seperated list of file extensiosn allowed for multimedia
$phpwcms['inline_download']   = 1;  //1 = try to display download documents in new window; 0 = show safe under dialog
$phpwcms['form_tracking']     = 1; //make a db entry for each form
$phpwcms['formmailer_set']    = array('allow_send_copy' => 0, 'global_recipient_email' => 'form@localhost'); //for better security handling
$phpwcms['allow_cntPHP_rt']   = 0; //allow PHP replacement tags and includes in content parts
$phpwcms['GETparameterName']  = 'id'; //must have a minimum of 2 chars 
$phpwcms['no_session_start']  = array('googlebot', 'msnbot', 'ia_archiver', 'altavista', 'slurp', 'yahoo', 'jeeves', 'teoma', 'lycos', 'crawler'); //don't start session 
$phpwcms['mode_XHTML']        = 1; // Doctype: 1 = XHTML 1.0 Transitional, 0 = HTML 4.01 Transitional 
$phpwcms['header_XML']        = 1; // Content Type: 1 = application/xhtml+xml, 0 = text/html 
$phpwcms['IE_htc_hover']      = 1; // enables HTC Hover for IE < 7 - has no effect in other browsers 
$phpwcms['IE_htc_png']        = 1; // enables HTC pngbehavior for IE < 7 - has no effect in other browsers 
$phpwcms['Bad_Behavior']      = 0; // 1 enables spam blocking by Bad Behavior, 0 off 
$phpwcms['wysiwyg_template']  = array('FCKeditor' => 'phpwcms_basic,phpwcms_default,Default,Basic', 
										'SPAW' => 'default,mini,full,sidetable,intlink',
										'SPAW2' => 'toolbarset_standard,toolbarset_all,toolbarset_mini' );
$phpwcms['GET_pageinfo']      = 0; // will add "&pageinfo=/cat1/cat2/page-title.htm" based on the breadcrumb information for each site link 
$phpwcms['version_check']     = 1; // checks for current release of phpwcms online 
$phpwcms['SESSION_FEinit']    = 0; // set 1 to enable sessions in frontend, 0 to disable sessions in frontend 

// dynamic ssl encryption engine
$phpwcms['site_ssl_mode']     = '0'; // tuns the SSL Support of WCMS on(1) or off (0) DEFAULT '0'
$phpwcms['site_ssl_url']      = '';  //URL assigned to the SSL Certificate. DON'T add a slash at the End! Exp. 'https://www.yourdomainhere.tld'
$phpwcms['site_ssl_port']     = '443'; //The Port on which you SSL Service serve the secure Sites. Servers DEFAULT is '443'

// smtp values
$phpwcms['SMTP_FROM_EMAIL']   = 'XXXXXXXXXX'; // reply/from email address
$phpwcms['SMTP_FROM_NAME']    = 'phpwcms webmaster'; // reply/from name
$phpwcms['SMTP_HOST']         = 'localhost'; // SMTP server (host/IP)
$phpwcms['SMTP_PORT']         = 25; // SMTP-Server port (default 25)
$phpwcms['SMTP_MAILER']       = 'mail'; // default phpMailer: smtp, mail (default), sendmail
$phpwcms['SMTP_AUTH']         = 0; // sets SMTP_AUTH to ON/OFF
$phpwcms['SMTP_USER']         = ''; // default SMTP login (user) name
$phpwcms['SMTP_PASS']         = ''; // default SMTP password


define('PHPWCMS_INCLUDE_CHECK', true);


?>

Thanks for any help you might be able to provide.
Aaron.

[juergen]

INSERT INTO dal_phpwcms_article

Code: Select all

$phpwcms['db_prepend']        = 'dal';

[yorkshire_boy1]

Hi, i found a way to fix my problem, maybe it will help you Aaron. For me it turned out that the later versions of MySQL differ slightly, having an automatic strict mode switched on. This means that It doesn't like the assigning of a default nothing value to a NOT NULL field.

If you do a search for "mysql strict mode cms problem" you get a bazillion pages about this sort of problem, most saying disable strict mode. I couldnt as it was on a shared server, but I fixed it by going to the file

include/inc_lib/dbcon.inc.php

and changing line 50 from

Code: Select all

//@mysql_query("SET SQL_MODE='MYSQL323'", $db);

to

Code: Select all

@mysql_query("SET SQL_MODE='MYSQL40'", $db);

This basically temporarily sets the strict mode to off everytime phpwcms accesses the database.

Hope this helps

[aaronazz]

Woohoo! It appears to be working fine. Uncommenting the line and replacing the code solved the problem. I will begin transferring in all the site's content, so if it's gonna break, it should happen then. The problem wasn't apparent when I was adding in all the structure levels, so I'm glad to not have to re-enter anything. Having multiple conf.inc.php files and versions of prepended tables saved me time working out the problem... but I never would have thought of this.

Thanks very much for the solution YorkshireBoy (and the other folks who also helped).

Aaron in Australia.