0

I have created a block whose content uses the PHP as input filter. What I want to do is inserting a record into a separate database.

The code I am using is the following one.

$tracker = array(
  'database' => 'tracker',
  'username' => 'tracker',
  'password' => 'PASSWORD',
  'host' => 'localhost',
  'driver' => 'mysql',
);

Database::addConnectionInfo('tracker', 'default', $tracker);
db_set_active('tracker');

$time = microtime();
$time = explode(' ', $time);
$time = $time[1] + $time[0];
$finish = $time;
$total_time = round(($finish - $start), 4);
$t = $total_time;
$url = $_GET['url'];
$current_ip = $_SERVER['REMOTE_ADDR'];
$user_agent = $_SERVER['HTTP_USER_AGENT'];
$date = date("Y-m-d");
$time =  date("h:i:s");

function is_bot() {
  $botlist = array(
    "Teoma", "alexa", "froogle", "Gigabot", "inktomi",
    "looksmart", "URL_Spider_SQL", "Firefly", "NationalDirectory",
    "Ask Jeeves", "TECNOSEEK", "InfoSeek", "WebFindBot", "girafabot",
    "crawler", "www.galaxy.com", "Googlebot", "Scooter", "Slurp",
    "msnbot", "appie", "FAST", "WebBug", "Spade", "ZyBorg", "rabaz",
    "Baiduspider", "Feedfetcher-Google", "TechnoratiSnoop",
    "Rankivabot", "Mediapartners-Google", "Sogou web spider",
    "WebAlta Crawler", "TweetmemeBot",
    "Butterfly", "Twitturls", "Me.dium", "Twiceler"
  );
    
  foreach ($botlist as $bot) {
    if(strpos($_SERVER['HTTP_USER_AGENT'], $bot) !== false)
      return true;
    }
    return false;
  }
}

if (is_bot()) {
  $isbot = 1;
}
else {
  $isbot = 0;
}

db_insert('ap_visits')
  ->fields(array(
    'id' => NULL,
    'length' => $t,
    'url' => '$url',
    'current_ip' => '$current_ip',
    'user_agent' => '$user_agent',
    'date_start' => date("Y-m-d H:i:s"),
    'is_bot' => $isbot,
  ))
  ->execute();
db_set_active();

When executed, the code returns the following error.

PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S02]: Base table or view not found: 1146 Table 'c1tracker.semaphore' doesn't exist' in /var/www/clients/client1/web2/web/includes/database/database.inc:2168
Stack trace:
#0 /var/www/clients/client1/web2/web/includes/database/database.inc(2168): PDOStatement->execute(Array)
#1 /var/www/clients/client1/web2/web/includes/database/database.inc(680): DatabaseStatementBase->execute(Array, Array)
#2 /var/www/clients/client1/web2/web/includes/database/database.inc(2347): DatabaseConnection->query('SELECT expire, ...', Array, Array)
#3 /var/www/clients/client1/web2/web/includes/lock.inc(167): db_query('SELECT expire, ...', Array)
#4 /var/www/clients/client1/web2/web/includes/lock.inc(146): lock_may_be_available('schema:runtime:...')
#5 /var/www/clients/client1/web2/web/includes/bootstrap.inc(420): lock_acquire('schema:runtime:...')
#6 /var/www/clients/client1/web2/web/includes/bootstrap.inc(442): DrupalCacheArray->set(Array)
#7 [internal function]: DrupalCache in /var/www/clients/client1/web2/web/includes/database/database.inc on line 2168
PDOException: SQLSTATE[HY000]: General error: 1364 Field 'is_logout' doesn't have a default value: INSERT INTO {ap_visits} (id, length, url, current_ip, user_agent, date_start, is_bot) VALUES (:db_insert_placeholder_0, :db_insert_placeholder_1, :db_insert_placeholder_2, :db_insert_placeholder_3, :db_insert_placeholder_4, :db_insert_placeholder_5, :db_insert_placeholder_6); Array
( [:db_insert_placeholder_0] => [:db_insert_placeholder_1] => 1375693838.8169 [:db_insert_placeholder_2] => $url [:db_insert_placeholder_3] => $current_ip [:db_insert_placeholder_4] => $user_agent [:db_insert_placeholder_5] => 2013-08-05 17:10:38 [:db_insert_placeholder_6] => 0 )
in eval() (line 61 of /var/www/clients/client1/web2/web/modules/php/php.module(80) : eval()'d code).

It seems that the code is trying to access 'semaphore' table when using the external database.

How can I fix that error?

2
  • 1
    This is just a word of advice. The PHP module is evil. There's even (still) talk about removing it from core. I'd definitely suggest spend a few minutes learning about the block hooks and putting this in a module instead.
    – Chapabu
    Aug 5 '13 at 9:50
  • I missed that part of the question. @Chapabu is right.
    – Letharion
    Aug 5 '13 at 9:52
0

I could be wrong, but looking at the database code, there is not manual locking going on, so most likely you have a contrib module that causes the above error.

Regardless, you'll need to debug the lock_acquire function to find out which lock is being attempted, and work back from there to understand why the lock is needed.

10
  • Hi, instead of putting it in the block, I transfer it on my theme under html.tpl.php with the same code. Is what I am doing correct?
    – jaypabs
    Aug 5 '13 at 9:51
  • Is the code/functionality related to your theme?
    – Letharion
    Aug 5 '13 at 9:53
  • @jaypabs There shouldn't be any logic in XYZ.tpl.php files, this should be reserved for modules and preprocess functions :)
    – Chapabu
    Aug 5 '13 at 9:54
  • @Letharion the code will simple record the URL accessed.
    – jaypabs
    Aug 5 '13 at 9:55
  • 1
    @jaypabs Yes. Templates should only be used for HTML output, anything that requires any logic should be dealt with in a preprocess function in either template.php or a custom module. From the looks of it, you would be better off with a custom module. I'd suggest breaking down what you are actually trying to do (without all the DB code) and posting that as a question. E.g. "I'm trying to track page views, how would I go about it."
    – Chapabu
    Aug 5 '13 at 10:02
0

Essentially, what happens is that the code executes the following query, which doesn't set a value for the is_logout database field which has been defined without a default value.

db_insert('ap_visits')->fields(array(
  'id' => NULL,
  'length' => $t,
  'url' => '$url',
  'current_ip' => '$current_ip',
  'user_agent' => '$user_agent',
  'date_start' => date("Y-m-d H:i:s"),
  'is_bot' => $isbot,
))
  ->execute();

An internal function in the /includes/database/database.inc file (line 2168) tries to get the database schema, probably to show it in the error message. The function tries to populate the cache containing the database schema data, and that causes the call to lock_may_be_available(), which tries to access a database table that doesn't exist in that database.

It's impossible to know which function causes that without knowing the exact Drupal 7 version. It would require to find a version where line 2168 the /includes/database/database.inc file is inside a function/method and, between all those versions, the exact version.

Since the main cause is the query that doesn't set a value for a database field that doesn't have the default value, the fix doesn't require to know exactly which function is calling lock_may_be_available(). Rather, it requires to either:

  • Change the database table definition for ap_visits and add a default value for the is_logout field
  • Change the query to set a value for the is_logout field in the ap_visits database table

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.