Insert Record Using db_insert in Block

Question

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?

Answer 1

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.

Answer 2

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