SQL error from upgrading from 3.0.12 to 3.1

Get help with installation and running phpBB 3.1.x here. Please do not post bug reports, feature requests, or extension related questions here.
Scam Warning
Forum rules
READ: phpBB.com Board-Wide Rules and Regulations

NOTE: phpBB 3.1.x is at its End of Life stage and support will NOT be provided after July 1st, 2018.
Locked
Roxas240
Registered User
Posts: 2
Joined: Tue Oct 21, 2014 10:02 pm

SQL error from upgrading from 3.0.12 to 3.1

Post by Roxas240 »

Now after running /install/database_update.php
I get this:

General Error
SQL ERROR [ mysqli ]

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 'WHERE user_id = 1' at line 3 [1064]

Forums url: http://forums.xemplar.cf/
User avatar
KaileyT
Community Team Member
Community Team Member
Posts: 2845
Joined: Mon Sep 01, 2014 1:00 am
Location: sudo rm -rf /
Name: Kailey Truscott
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by KaileyT »

The Anonymous user is $user_id 1, and as of this post, your board shows up fine for me as a guest. Please fill out the Support Request Template and post it back here.
Kailey Truscott - Community Team
User avatar
bantu
3.0 Release Manager
3.0 Release Manager
Posts: 2523
Joined: Mon Jul 10, 2006 9:58 pm
Name: Andreas Fischer

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by bantu »

On which exact page are you getting this error message? Can you link to it?
Powered by Coffee
User avatar
Dragosvr92
Registered User
Posts: 1451
Joined: Sat Sep 12, 2009 7:27 am
Location: Romania
Name: Dragos Valentin Rădulescu
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Dragosvr92 »

I am also getting almost the same error.

Code: Select all

SQL ERROR [ mysql4 ]

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 '' at line 3 [1064]
I am getting the error after i run the db update and go to visit the phpbb3 root/index.
Formerly known as TheKiller
3.0| Avatar on Memberlist 1.0.3
User avatar
Noxwizard
Support Team Leader
Support Team Leader
Posts: 10406
Joined: Mon Jun 27, 2005 8:41 pm
Location: Texas, USA
Name: Patrick Webster
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Noxwizard »

Roxas240: It looks like you were using a style other than prosilver when you updated. Since that style no longer exists, you get an error. You can use this script to reset all users to prosilver:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Default style ID
$sql = 'SELECT config_value FROM ' . CONFIG_TABLE . " WHERE config_name = 'default_style'";
$result = $db->sql_query($sql);
$default_style_id = (int) $db->sql_fetchfield('config_value');
$db->sql_freeresult($result);

// Default style name
$sql = 'SELECT style_name FROM ' . STYLES_TABLE . ' WHERE style_id = ' . $default_style_id;
$result = $db->sql_query($sql);
$default_style_name = $db->sql_fetchfield('style_name');
$db->sql_freeresult($result);

// Active styles
$sql = 'SELECT style_id, style_name, style_active FROM ' . STYLES_TABLE;
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
    $styles_list[] = $row;
}
$db->sql_freeresult($result);


echo 'Default style: ' . $default_style_name . ' (' . $default_style_id . ')<br />';

$exists = file_exists('./styles/' . $default_style_name . '/style.cfg');

if ($exists)
{
    echo 'Default style exists, no work to be done.';
    exit;
}

if (!file_exists('./styles/prosilver/style.cfg'))
{
    echo 'Prosilver does not exist. Please upload a copy of prosilver from the <a href="https://www.phpbb.com/downloads/">3.1.0 Full Package</a>.';
    exit;
}

$prosilver = array();
foreach ($styles_list as $style)
{
    if ($style['style_name'] == 'prosilver')
    {
        $prosilver = $style;
        break;
    }
}

// Install style if it doesn't exist
if (!sizeof($prosilver))
{
    $sql_ary = array(
        'style_name'        => 'prosilver',
        'style_copyright'    => '&copy; phpBB Group',
        'style_active'        => 1,
        'style_path'        => 'prosilver',
        'bbcode_bitfield'    => 'lNg=',
        'style_parent_id'    => '0',
        'style_parent_tree'    => '',
    );

    $sql = 'INSERT INTO ' . STYLES_TABLE . '
        ' . $db->sql_build_array('INSERT', $sql_ary);
    $db->sql_query($sql);

    $id = $db->sql_nextid();
    $prosilver = array(
        'style_name'    => 'prosilver',
        'style_id'        => $id,
        'style_active'    => 1,
    );

    echo 'Installed prosilver (' . $id . ').<br />';
}
else
{
    // Activate prosilver
    $sql = 'UPDATE ' . STYLES_TABLE . " SET style_active = 1 WHERE style_name = 'prosilver'";
    $db->sql_query($sql);
}

// Set it as the default style
$sql = 'UPDATE ' . CONFIG_TABLE . ' SET config_value = ' . $prosilver['style_id'] . " WHERE config_name = 'default_style'";
$db->sql_query($sql);
$cache->purge();
echo 'Set prosilver as the default style.<br />';

// Set all users' styles to prosilver
$sql = 'UPDATE ' . USERS_TABLE . ' SET user_style = ' . $prosilver['style_id'];
$db->sql_query($sql);
echo 'Updated user styles to prosilver.<br />';
echo 'Done.';
?>
Just make a file called "fix.php" with that as the contents, upload it to your forum's root (same place as config.php) and then point your browser to it.

Dragosvr92 wrote:I am also getting almost the same error.
No, you are not getting the same error. Please make a new topic.
[Support Template] - [Read Before Posting] - [phpBB Knowledge Base]
Do not contact me for private support, please share the question in our forums.
User avatar
Dragosvr92
Registered User
Posts: 1451
Joined: Sat Sep 12, 2009 7:27 am
Location: Romania
Name: Dragos Valentin Rădulescu
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Dragosvr92 »

Noxwizard wrote:
Dragosvr92 wrote:I am also getting almost the same error.
No, you are not getting the same error. Please make a new topic.
Actually i did. I have followed your instructions and now it is working.
Pretty much everyone has styles that are not prosilver. The database update should do the changes you posted to avoid this error.... And also provide a more clear error message.
Formerly known as TheKiller
3.0| Avatar on Memberlist 1.0.3
User avatar
infamanious
Registered User
Posts: 54
Joined: Sun Dec 17, 2006 7:16 am

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by infamanious »

Noxwizard wrote:Roxas240: It looks like you were using a style other than prosilver when you updated. Since that style no longer exists, you get an error. You can use this script to reset all users to prosilver:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Default style ID
$sql = 'SELECT config_value FROM ' . CONFIG_TABLE . " WHERE config_name = 'default_style'";
$result = $db->sql_query($sql);
$default_style_id = (int) $db->sql_fetchfield('config_value');
$db->sql_freeresult($result);

// Default style name
$sql = 'SELECT style_name FROM ' . STYLES_TABLE . ' WHERE style_id = ' . $default_style_id;
$result = $db->sql_query($sql);
$default_style_name = $db->sql_fetchfield('style_name');
$db->sql_freeresult($result);

// Active styles
$sql = 'SELECT style_id, style_name, style_active FROM ' . STYLES_TABLE;
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
    $styles_list[] = $row;
}
$db->sql_freeresult($result);


echo 'Default style: ' . $default_style_name . ' (' . $default_style_id . ')<br />';

$exists = file_exists('./styles/' . $default_style_name . '/style.cfg');

if ($exists)
{
    echo 'Default style exists, no work to be done.';
    exit;
}

if (!file_exists('./styles/prosilver/style.cfg'))
{
    echo 'Prosilver does not exist. Please upload a copy of prosilver from the <a href="https://www.phpbb.com/downloads/">3.1.0 Full Package</a>.';
    exit;
}

$prosilver = array();
foreach ($styles_list as $style)
{
    if ($style['style_name'] == 'prosilver')
    {
        $prosilver = $style;
        break;
    }
}

// Install style if it doesn't exist
if (!sizeof($prosilver))
{
    $sql_ary = array(
        'style_name'        => 'prosilver',
        'style_copyright'    => '&copy; phpBB Group',
        'style_active'        => 1,
        'style_path'        => 'prosilver',
        'bbcode_bitfield'    => 'lNg=',
        'style_parent_id'    => '0',
        'style_parent_tree'    => '',
    );

    $sql = 'INSERT INTO ' . STYLES_TABLE . '
        ' . $db->sql_build_array('INSERT', $sql_ary);
    $db->sql_query($sql);

    $id = $db->sql_nextid();
    $prosilver = array(
        'style_name'    => 'prosilver',
        'style_id'        => $id,
        'style_active'    => 1,
    );

    echo 'Installed prosilver (' . $id . ').<br />';
}
else
{
    // Activate prosilver
    $sql = 'UPDATE ' . STYLES_TABLE . " SET style_active = 1 WHERE style_name = 'prosilver'";
    $db->sql_query($sql);
}

// Set it as the default style
$sql = 'UPDATE ' . CONFIG_TABLE . ' SET config_value = ' . $prosilver['style_id'] . " WHERE config_name = 'default_style'";
$db->sql_query($sql);
$cache->purge();
echo 'Set prosilver as the default style.<br />';

// Set all users' styles to prosilver
$sql = 'UPDATE ' . USERS_TABLE . ' SET user_style = ' . $prosilver['style_id'];
$db->sql_query($sql);
echo 'Updated user styles to prosilver.<br />';
echo 'Done.';
?>
Just make a file called "fix.php" with that as the contents, upload it to your forum's root (same place as config.php) and then point your browser to it.

Dragosvr92 wrote:I am also getting almost the same error.
No, you are not getting the same error. Please make a new topic.

I received the error same mysql user id error after upgrading from 3.0.12 to 3.1 . Turns out i used an older theme previously, so this php script did the trick. Thanks!! :)
User avatar
P_I
Registered User
Posts: 1240
Joined: Tue Mar 01, 2011 8:35 pm
Location: Staying home - Calgary
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by P_I »

Noxwizard wrote:Roxas240: It looks like you were using a style other than prosilver when you updated. Since that style no longer exists, you get an error. You can use this script to reset all users to prosilver:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Default style ID
$sql = 'SELECT config_value FROM ' . CONFIG_TABLE . " WHERE config_name = 'default_style'";
$result = $db->sql_query($sql);
$default_style_id = (int) $db->sql_fetchfield('config_value');
$db->sql_freeresult($result);

// Default style name
$sql = 'SELECT style_name FROM ' . STYLES_TABLE . ' WHERE style_id = ' . $default_style_id;
$result = $db->sql_query($sql);
$default_style_name = $db->sql_fetchfield('style_name');
$db->sql_freeresult($result);

// Active styles
$sql = 'SELECT style_id, style_name, style_active FROM ' . STYLES_TABLE;
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
    $styles_list[] = $row;
}
$db->sql_freeresult($result);


echo 'Default style: ' . $default_style_name . ' (' . $default_style_id . ')<br />';

$exists = file_exists('./styles/' . $default_style_name . '/style.cfg');

if ($exists)
{
    echo 'Default style exists, no work to be done.';
    exit;
}

if (!file_exists('./styles/prosilver/style.cfg'))
{
    echo 'Prosilver does not exist. Please upload a copy of prosilver from the <a href="https://www.phpbb.com/downloads/">3.1.0 Full Package</a>.';
    exit;
}

$prosilver = array();
foreach ($styles_list as $style)
{
    if ($style['style_name'] == 'prosilver')
    {
        $prosilver = $style;
        break;
    }
}

// Install style if it doesn't exist
if (!sizeof($prosilver))
{
    $sql_ary = array(
        'style_name'        => 'prosilver',
        'style_copyright'    => '&copy; phpBB Group',
        'style_active'        => 1,
        'style_path'        => 'prosilver',
        'bbcode_bitfield'    => 'lNg=',
        'style_parent_id'    => '0',
        'style_parent_tree'    => '',
    );

    $sql = 'INSERT INTO ' . STYLES_TABLE . '
        ' . $db->sql_build_array('INSERT', $sql_ary);
    $db->sql_query($sql);

    $id = $db->sql_nextid();
    $prosilver = array(
        'style_name'    => 'prosilver',
        'style_id'        => $id,
        'style_active'    => 1,
    );

    echo 'Installed prosilver (' . $id . ').<br />';
}
else
{
    // Activate prosilver
    $sql = 'UPDATE ' . STYLES_TABLE . " SET style_active = 1 WHERE style_name = 'prosilver'";
    $db->sql_query($sql);
}

// Set it as the default style
$sql = 'UPDATE ' . CONFIG_TABLE . ' SET config_value = ' . $prosilver['style_id'] . " WHERE config_name = 'default_style'";
$db->sql_query($sql);
$cache->purge();
echo 'Set prosilver as the default style.<br />';

// Set all users' styles to prosilver
$sql = 'UPDATE ' . USERS_TABLE . ' SET user_style = ' . $prosilver['style_id'];
$db->sql_query($sql);
echo 'Updated user styles to prosilver.<br />';
echo 'Done.';
?>
Just make a file called "fix.php" with that as the contents, upload it to your forum's root (same place as config.php) and then point your browser to it.

Dragosvr92 wrote:I am also getting almost the same error.
No, you are not getting the same error. Please make a new topic.
Can't this be solved via ACP->Board settings. There is a new section, Board style and the last item is
Override user style: Replaces user’s (and guest’s) style with the style as defined under "Default style".
Normal people… believe that if it ain’t broke, don’t fix it. Engineers believe that if it ain’t broke, it doesn’t have enough features yet. – Scott Adams
User avatar
Noxwizard
Support Team Leader
Support Team Leader
Posts: 10406
Joined: Mon Jun 27, 2005 8:41 pm
Location: Texas, USA
Name: Patrick Webster
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Noxwizard »

Only if you do that before you update the board. The users in this topic updated the board and were then unable to access it. Some boards may not even have prosilver installed to manually set it in the database, which is why a script is necessary.
[Support Template] - [Read Before Posting] - [phpBB Knowledge Base]
Do not contact me for private support, please share the question in our forums.
Roxas240
Registered User
Posts: 2
Joined: Tue Oct 21, 2014 10:02 pm

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Roxas240 »

Wow how silly of me. Anyway thanks it worked
User avatar
Dragosvr92
Registered User
Posts: 1451
Joined: Sat Sep 12, 2009 7:27 am
Location: Romania
Name: Dragos Valentin Rădulescu
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by Dragosvr92 »

Noxwizard wrote:Only if you do that before you update the board. The users in this topic updated the board and were then unable to access it. Some boards may not even have prosilver installed to manually set it in the database, which is why a script is necessary.
Of course they all have prosilver....
When we upgrade to 3.1.0, we only keep the database, avatars and attachments.
The rest of the files are fresh 3.1.0 files which include prosilver. So, during the update, the style should be changed automatically to prosilver.
Formerly known as TheKiller
3.0| Avatar on Memberlist 1.0.3
User avatar
vsabah
Registered User
Posts: 358
Joined: Wed Apr 05, 2006 5:57 pm
Location: Konya / Türkiye
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by vsabah »

Dragosvr92 wrote:I am also getting almost the same error.

Code: Select all

SQL ERROR [ mysql4 ]

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 '' at line 3 [1064]
I am getting the error after i run the db update and go to visit the phpbb3 root/index.
UP^
User avatar
vsabah
Registered User
Posts: 358
Joined: Wed Apr 05, 2006 5:57 pm
Location: Konya / Türkiye
Contact:

Re: SQL error from upgrading from 3.0.12 to 3.1

Post by vsabah »

Noxwizard wrote:Roxas240: It looks like you were using a style other than prosilver when you updated. Since that style no longer exists, you get an error. You can use this script to reset all users to prosilver:

Code: Select all

<?php
define('IN_PHPBB', true);
$phpbb_root_path = (defined('PHPBB_ROOT_PATH')) ? PHPBB_ROOT_PATH : './';
$phpEx = substr(strrchr(__FILE__, '.'), 1);
include($phpbb_root_path . 'common.' . $phpEx);

// Default style ID
$sql = 'SELECT config_value FROM ' . CONFIG_TABLE . " WHERE config_name = 'default_style'";
$result = $db->sql_query($sql);
$default_style_id = (int) $db->sql_fetchfield('config_value');
$db->sql_freeresult($result);

// Default style name
$sql = 'SELECT style_name FROM ' . STYLES_TABLE . ' WHERE style_id = ' . $default_style_id;
$result = $db->sql_query($sql);
$default_style_name = $db->sql_fetchfield('style_name');
$db->sql_freeresult($result);

// Active styles
$sql = 'SELECT style_id, style_name, style_active FROM ' . STYLES_TABLE;
$result = $db->sql_query($sql);
while ($row = $db->sql_fetchrow($result))
{
    $styles_list[] = $row;
}
$db->sql_freeresult($result);


echo 'Default style: ' . $default_style_name . ' (' . $default_style_id . ')<br />';

$exists = file_exists('./styles/' . $default_style_name . '/style.cfg');

if ($exists)
{
    echo 'Default style exists, no work to be done.';
    exit;
}

if (!file_exists('./styles/prosilver/style.cfg'))
{
    echo 'Prosilver does not exist. Please upload a copy of prosilver from the <a href="https://www.phpbb.com/downloads/">3.1.0 Full Package</a>.';
    exit;
}

$prosilver = array();
foreach ($styles_list as $style)
{
    if ($style['style_name'] == 'prosilver')
    {
        $prosilver = $style;
        break;
    }
}

// Install style if it doesn't exist
if (!sizeof($prosilver))
{
    $sql_ary = array(
        'style_name'        => 'prosilver',
        'style_copyright'    => '&copy; phpBB Group',
        'style_active'        => 1,
        'style_path'        => 'prosilver',
        'bbcode_bitfield'    => 'lNg=',
        'style_parent_id'    => '0',
        'style_parent_tree'    => '',
    );

    $sql = 'INSERT INTO ' . STYLES_TABLE . '
        ' . $db->sql_build_array('INSERT', $sql_ary);
    $db->sql_query($sql);

    $id = $db->sql_nextid();
    $prosilver = array(
        'style_name'    => 'prosilver',
        'style_id'        => $id,
        'style_active'    => 1,
    );

    echo 'Installed prosilver (' . $id . ').<br />';
}
else
{
    // Activate prosilver
    $sql = 'UPDATE ' . STYLES_TABLE . " SET style_active = 1 WHERE style_name = 'prosilver'";
    $db->sql_query($sql);
}

// Set it as the default style
$sql = 'UPDATE ' . CONFIG_TABLE . ' SET config_value = ' . $prosilver['style_id'] . " WHERE config_name = 'default_style'";
$db->sql_query($sql);
$cache->purge();
echo 'Set prosilver as the default style.<br />';

// Set all users' styles to prosilver
$sql = 'UPDATE ' . USERS_TABLE . ' SET user_style = ' . $prosilver['style_id'];
$db->sql_query($sql);
echo 'Updated user styles to prosilver.<br />';
echo 'Done.';
?>
Just make a file called "fix.php" with that as the contents, upload it to your forum's root (same place as config.php) and then point your browser to it.
Ohh thanks... It works like a charm 8-)
Locked

Return to “[3.1.x] Support Forum”