Advertisement:

Author Topic: SQL logic error or missing database  (Read 27331 times)

w5hro

  • Guest
SQL logic error or missing database
« on: March 29, 2014, 11:25:18 AM »
Hello,

I'm using SMF version 2.0.7 and an SQLite database install and I need to be able to set some member options via the Administration Center » Themes and Layout » Member Options location and I've been getting the error below. I need this to work with it. I've had this error ever since I first installed SMF. The SQLite version on my server is SQLite 3.3.6

SQL logic error or missing database
near "GROUP": syntax error
File: /home/mylocation/public_html/Sources/Themes.php
Line: 433

Thanks...

kat

  • Guest
Re: SQL logic error or missing database
« Reply #1 on: March 29, 2014, 12:46:22 PM »
I just looked at the stock file and there's no reference to "GROUP" around line 433.  So, I guess the first question has to be "What mods are you using?".

w5hro

  • Guest
Re: SQL logic error or missing database
« Reply #2 on: March 29, 2014, 01:14:34 PM »
I just looked at the stock file and there's no reference to "GROUP" around line 433.  So, I guess the first question has to be "What mods are you using?".

I actually had this error with the stock installation when I first installed the SMF package before I even installed any mods. However, with a few mods installed the line number may have changed to 433 and differs now from the stock file.

My installed mods are below…

1. Registered Links 3.0
2. Simple Youtube Video Embedder/BBC 1.1
3. Add IP2Location to Track IP 1.31
4. Default Avatar Per Membergroup 1.0
5. URL Popup 1.0.2

w5hro

  • Guest
Re: SQL logic error or missing database
« Reply #3 on: March 29, 2014, 01:22:43 PM »
Below is my current file starting with line 400 to line 499

Code: [Select]
// Need to make sure we don't do custom fields.
$request = $smcFunc['db_query']('', '
SELECT col_name
FROM {db_prefix}custom_fields',
array(
)
);
$customFields = array();
while ($row = $smcFunc['db_fetch_assoc']($request))
$customFields[] = $row['col_name'];
$smcFunc['db_free_result']($request);
$customFieldsQuery = empty($customFields) ? '' : ('AND variable NOT IN ({array_string:custom_fields})');

$request = $smcFunc['db_query']('themes_count', '
SELECT COUNT(DISTINCT id_member) AS value, id_theme
FROM {db_prefix}themes
WHERE id_member > {int:no_member}
' . $customFieldsQuery . '
GROUP BY id_theme',
array(
'no_member' => 0,
'custom_fields' => empty($customFields) ? array() : $customFields,
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
$context['themes'][$row['id_theme']]['num_members'] = $row['value'];
$smcFunc['db_free_result']($request);

// There has to be a Settings template!
foreach ($context['themes'] as $k => $v)
if (empty($v['theme_dir']) || (!file_exists($v['theme_dir'] . '/Settings.template.php') && empty($v['num_members'])))
unset($context['themes'][$k]);

loadTemplate('Themes');
$context['sub_template'] = 'reset_list';

return;
}

// Submit?
if (isset($_POST['submit']) && empty($_POST['who']))
{
checkSession();

if (empty($_POST['options']))
$_POST['options'] = array();
if (empty($_POST['default_options']))
$_POST['default_options'] = array();

// Set up the sql query.
$setValues = array();

foreach ($_POST['options'] as $opt => $val)
$setValues[] = array(-1, $_GET['th'], $opt, is_array($val) ? implode(',', $val) : $val);

$old_settings = array();
foreach ($_POST['default_options'] as $opt => $val)
{
$old_settings[] = $opt;

$setValues[] = array(-1, 1, $opt, is_array($val) ? implode(',', $val) : $val);
}

// If we're actually inserting something..
if (!empty($setValues))
{
// Are there options in non-default themes set that should be cleared?
if (!empty($old_settings))
$smcFunc['db_query']('', '
DELETE FROM {db_prefix}themes
WHERE id_theme != {int:default_theme}
AND id_member = {int:guest_member}
AND variable IN ({array_string:old_settings})',
array(
'default_theme' => 1,
'guest_member' => -1,
'old_settings' => $old_settings,
)
);

$smcFunc['db_insert']('replace',
'{db_prefix}themes',
array('id_member' => 'int', 'id_theme' => 'int', 'variable' => 'string-255', 'value' => 'string-65534'),
$setValues,
array('id_theme', 'variable', 'id_member')
);
}

cache_put_data('theme_settings-' . $_GET['th'], null, 90);
cache_put_data('theme_settings-1', null, 90);

And line 432 to 436 is below to narrow it down closer

Code: [Select]
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
$context['themes'][$row['id_theme']]['num_members'] = $row['value'];
$smcFunc['db_free_result']($request);

kat

  • Guest
Re: SQL logic error or missing database
« Reply #4 on: March 29, 2014, 01:34:09 PM »
The minimum requirements states that a minimum of SQLite v2.8 is required. So, I would imagine that v3.3.6 is OK. But, have you noticed how some updates screw things, in all sorts of places?

"New and improved" often means We've screwed it up!", doesn't it? :(

I can only suspect that the problem, itself, is with SQLite. But, that's only a suspicion. maybe someone with more knowledge of such things can help you rather better than I've been able to.

It's the weekend, though. So, you might need to be a bit patient. ;)

w5hro

  • Guest
Re: SQL logic error or missing database
« Reply #5 on: March 29, 2014, 01:38:46 PM »
Ok, here is line 428 to 426, maybe the line gets shifted down from 428 in the Themes.php file to line 433 in the actual application after all of the files get pulled up as a whole, but the error does say "around" line 433.

Code: [Select]
GROUP BY id_theme',
array(
'no_member' => 0,
'custom_fields' => empty($customFields) ? array() : $customFields,
)
);
while ($row = $smcFunc['db_fetch_assoc']($request))
$context['themes'][$row['id_theme']]['num_members'] = $row['value'];
$smcFunc['db_free_result']($request);
« Last Edit: March 29, 2014, 05:58:09 PM by w5hro »

Offline emanuele

  • SMF Super Hero
  • *******
  • Posts: 14,156
  • Gender: Male
  • THERE'S JUST ME
Re: SQL logic error or missing database
« Reply #6 on: April 04, 2014, 09:22:59 AM »
The SQLite version on my server is SQLite 3.3.6
Just to be sure, SMF supports by default only SQLite 2.8, not 3.x.
@K@, unfortunately no, SQLite 3.x is not backward compatible with 2.x, so something that works with 2.x doesn't work with 3.x.

There is a mod for SQLite 3.x.

That said I'm a bit surprised it even installs, so most likely you are either using the mod, or your server has 3.x as PDO and 2.x as PHP extension. But whatever.

@w5hro try looking in the error log, there should/may be the entire query that fails, that would give some more hints about the issue.

ETA: the mod is here.


Take a peek at what I'm doing! ;D



Hai bisogno di supporto in Italiano?

Aiutateci ad aiutarvi: spiegate bene il vostro problema: no, "non funziona" non è una spiegazione!!
1) Cosa fai,
2) cosa ti aspetti,
3) cosa ottieni.

w5hro

  • Guest
Re: SQL logic error or missing database
« Reply #7 on: April 04, 2014, 10:13:27 PM »
There is a mod for SQLite 3.x.

That said I'm a bit surprised it even installs, so most likely you are either using the mod, or your server has 3.x as PDO and 2.x as PHP extension. But whatever.

@w5hro try looking in the error log, there should/may be the entire query that fails, that would give some more hints about the issue.

ETA: the mod is here.

That actually worked! I didn't know there was a conversion utility.

The only thing I've noticed now though is that once and a while when clicking on a topic, function or a link I get an error page with the following message, but it only happens every once and a while.

Notice: Undefined variable: err_msg in /home/mylocation/public_html/Sources/Subs-Db-sqlite3.php on line 374
database is locked

Not sure what that is, but if I look at line 374 I see some code above and below it that looks like it might be some kind of delay or overload timing for the database connection. Any idea what that is and how to correct it or adjust the time? If I hit the back arrow on my browser and then go there again the problem is gone.

Line 374 is: $ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

Code: [Select]
$st = microtime();
// Don't overload it.
$db_cache[$db_count]['q'] = $db_count < 50 ? $db_string : '...';
$db_cache[$db_count]['f'] = $file;
$db_cache[$db_count]['l'] = $line;
$db_cache[$db_count]['s'] = array_sum(explode(' ', $st)) - array_sum(explode(' ', $time_start));
}

$ret = @$connection->query($db_string);
if ($ret === false && empty($db_values['db_error_skip']))
$ret = smf_db_error($db_string . '#!#' . $err_msg, $connection);

// Debugging.
if (isset($db_show_debug) && $db_show_debug === true)
$db_cache[$db_count]['t'] = array_sum(explode(' ', microtime())) - array_sum(explode(' ', $st));

return $ret;
}

P.S. After converting the db to 3.3 it actually reduced it's size by about 1/2. SQLite 3 must be way more efficient than SQLite 2.
« Last Edit: April 04, 2014, 11:27:50 PM by w5hro »