Machform error: Cardinality violation: 1242 Subquery returns more than 1 row

We have a multiuser Machform PHP application running on a Redhat 7 Linux server with Apache webserver and MariaDB database.

In a specific form after page 3, accessing this URI: confirm.php?id=NNNNNNNN&mf_page_from=3 where NNNNNNNN is the id number of the form,

The following error message appears:

SQL: [671] select A.element_id, A.option_id, (select if(B.element_matrix_parent_id=0,A.option, (select C.`option` from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id)) ) ‘option_label’ from ap_element_options A left join ap_form_elements B on (A.element_id=B.element_id and A.form_id=B.form_id) where A.form_id=? and A.live=1 and B.element_type=’matrix’ and B.element_status=1 order by A.element_id,A.option_id asc Sent SQL: [680] select A.element_id, A.option_id, (select if(B.element_matrix_parent_id=0,A.option, (select C.`option` from ap_element_options C where C.element_id=B.element_matrix_parent_id and C.form_id=A.form_id and C.live=1 and C.option_id=A.option_id)) ) ‘option_label’ from ap_element_options A left join ap_form_elements B on (A.element_id=B.element_id and A.form_id=B.form_id) where A.form_id=’NNNNNNNN’ and A.live=1 and B.element_type=’matrix’ and B.element_status=1 order by A.element_id,A.option_id asc Params: 1 Key: Position #0: paramno=0 name=[0] “” is_param=1 param_type=2 Query Failed: SQLSTATE[21000]: Cardinality violation: 1242 Subquery returns more than 1 row

The solution is to change these files:

/includes/entry-functions.php and export_entries.php

where line: C.option_id=A.option_id))

is changed to:

C.option_id=A.option_id LIMIT 1))

Mysql / Mariadb performance test

I have two different Linux servers, and wanted to test if one where faster than the other. I used mysqlslap together with the “time” command in a bash shell.

# time mysqlslap --user=root --password=$(cat /root/mysql) --host=localhost concurrency=50 --iterations=100 --number-int-cols=5 --number-char-cols=20 --auto-generate-sql --verbose

In this way I could get out different values from the server which I could compare.

Benchmark
Average number of seconds to run all queries: 0.007 seconds
Minimum number of seconds to run all queries: 0.006 seconds
Maximum number of seconds to run all queries: 0.024 seconds
Number of clients running queries: 1
Average number of queries per client: 0

real 0m9.390s
user 0m0.085s
sys 0m0.165s

Referance: https://www.digitalocean.com/community/tutorials/how-to-measure-mysql-query-performance-with-mysqlslap

 

Upgrade Moodle 2.2.1 to 2.6.2+

I tried to upgrade Moodle 2.2.1 to 2.6.2+ and got this error message:

Default exception handler: DDL sql execution error Debug: Data truncated for column 'institution' at row 999
ALTER TABLE mdl_user MODIFY COLUMN institution VARCHAR(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL DEFAULT '' after phone2
Error code: ddlexecuteerror

The solution was to run these sql’s at the Mysql database:

update mdl_user set institution="" where institution is NULL;
update mdl_user set department="" where department is NULL;
update mdl_user set address="" where address is NULL;

This issue seems to be related to:

Mysql SQL in bash one-liner

If you just need a quick way to get some data from a mysql database in your shell (bash), you could do something like this in one line:

mysql -h your.server.edu -u db_username -p`cat /path/to/your/homedir/secretpasswordfile` -e ";use databasename; SELECT tablename.columnname FROM tablename where id like '421111' and something like '1' and option like '23';"; > /tmp/datayouwant.txt; while read i; do echo ";$i";; done < /tmp/datayourwant.txt | sort | uniq

If you don't like to scroll:
-bash-3.2$ mysql -h your.server.edu -u db_username -p`cat /path/to/your/homedir/secretpasswordfile` -e "use databasename; SELECT tablename.columnname FROM tablename where id like '421111' and something like '1' and option like '23';" > /tmp/datayouwant.txt; while read i; do echo "$i"; done < /tmp/datayourwant.txt | sort | uniq

On my server I would then get a list of words/numbers or whatever you might have in the database, which one might want to use further in another script or command:

Dikult
Drupal
DSpace
Mediawiki
Open Journal Systems
Piwik
Postgresql og Mysql
Redhat Enterprise Linux 6 (RHEL6)
Redmine
Solr
Webmail (RoundCubemail)
Wordpress
Xibo

Finding spam users

We had a lot of spam users in our multisite wordpress system. This was because we had self-registration enabled for a period. Not a smart thing to do…

anyway, I wrote a bash script in order to find which users id’s from the Mysql database that could potentially be spam users. With this list of ID’s I would run another SQL directly where I would set the “spam” to the number “1”. This would make sure the user could not log in, and after a month or so, the user could be deleted.

As you can see from one of the sql’s, the “%uib.no” is the trusted emails from our organization.

 

[bash]
#!/bin/bash

# Which one of all the users in the multisite blog system are spamusers?
# If the user doesn’t have any relations in users_blogg, most likely the user is a spam user.
# But the user can have a meta_value in wp_usermeta table like source_domain.
# These are valid users, and should not be marked as spam

# Find all users ID’s
dbquery=$(mysql -u root -p`cat /root/mysql` -e “use blog; select ID from wp_users;”)
array=($(for i in $dbquery; do echo $i; done))

# echo ${array[@]}

# for all the users, do:
for i in ${array[@]}
do
dbquery2=$(mysql -u root -p`cat /root/mysql` -e “use blog;SELECT wp_bp_user_blogs.id FROM wp_bp_user_blogs WHERE wp_bp_user_blogs.user_id = $i”;)
array2=($(for j in $dbquery2; do echo $j; done))
if [ ${#array2[@]} -eq 0 ];then
dbquery3=$(mysql -u root -p`cat /root/mysql` -e “use blog; select user_email from wp_users WHERE ID = $i and user_email not like ‘%uib.no’;”)
dbquery4=$(mysql -u root -p`cat /root/mysql` -e “use blog; select meta_value from wp_usermeta WHERE user_id = $i and meta_key like ‘source_domain’;”)
array4=($(for r in $dbquery4; do echo $r; done))
for n in ${array4[@]}
do
echo “User $i has a blogg with name $n! Please don’t delete this user.”
done

array3=($(for k in $dbquery3; do echo $k; done))
for m in ${array3[@]}
do
echo “User $i with email $m is not connected to any blog and should be marked as a spam user!”
done
fi
done
[/bash]

Alert Blog Admin php script

I had to send emails to all the Blog admins in our WordPress Multisite installation. This is how I did it:

[code lang=”php”]
<?php
# Alert Blog Admin php script
# connect to blog db, select from, find all blog admin email adresses
# get subject, body from pre-created local files, send the email

# how to run the script
# php alertblogadmin.php -s subjecttext.txt -b bodytext.txt

$mysqlserver="localhost";
$mysqluser="dbuser";
$mysqlpassword="XXXXXXX";
$database="blog";

$ERROR=0;

# This script require arguments
$arguments = getopt("s:b:f:dr");

# Test if array $arguments as at least s,b and r
# maybe to make sure that $arguments array contains at least 3 items?
if ( count($arguments) < 3 ){
echo "Missing arguments…\n";
echo "Usage: php alertblogadmin.php -s subjecttext.txt -b bodytext.txt\n";
die;
}

# 2 text files need to exist
if ( !file_exists($arguments["s"])) {
echo "File $arguments[s] does not exist?\n";
$ERROR=1;
}
if ( !file_exists($arguments["b"])) {
echo "File $arguments[b] does not exist?\n";
$ERROR=1;
}

if ($ERROR == "1"){
die;
}

$link = mysql_connect($mysqlserver, $mysqluser, $mysqlpassword);
if (!$link) {
die(‘Could not connect: ‘ . mysql_error());
}
mysql_select_db ($database,$link);

# collect blogs
$sql="SELECT blog_id from wp_blogs where deleted not like ‘1’";
$result=mysql_query($sql,$link);
if (!$result) {
die(‘Invalid query: ‘ . mysql_error());
}

$i = "0";
$emailarray = array();

while ($row = mysql_fetch_array($result, MYSQL_NUM)) {
$blog_id = $row[0];
$table = "wp_" . $blog_id . "_options";

# Find email adresses to admins in each blog in the system
$sql2="SELECT option_value FROM $table WHERE option_name = ‘admin_email’";
$result2=mysql_query($sql2,$link);
while ($row2 = mysql_fetch_array($result2, MYSQL_NUM)) {
$emailarray[$i] = $row2[0];
}
$i++;
}

# sort the email array and find unique emails
asort($emailarray);
$emailarray = array_unique($emailarray);

$emails = "";
foreach ($emailarray as $email) {
$emails .= $email.",";
}

$emails = substr($emails,0,-1);

# Could be smart to see who you will be sending your email to
echo $emails;

# open subject and body text files
$subject = file_get_contents($arguments["s"]);
$body = file_get_contents($arguments["b"]);
$from = $arguments["r"];

# Use this one to test before you actuelly send to $emails, which is a variabel that might contain hundreds of emails.
# You really would like to test first
$bcc = "user1@something.com,user2@something.net";
//$bcc = $emails; # uncomment this one, when you are ready to go

$headers = "From: noreply@something.com" . "\r\n" .
"Reply-To: noreply@something.com" . "\r\n" .
"Bcc: $bcc" . "\r\n" .
"X-Mailer: PHP/" . phpversion();
$header_ = ‘MIME-Version: 1.0’ . "\r\n" . ‘Content-type: text/plain; charset=UTF-8’ . "\r\n";

mail(”, ‘=?UTF-8?B?’.base64_encode($subject).’?=’, $body, $header_ . $headers);
mysql_close($link);
?>

[/code]