Recent Topics

Will not post more than a few words. MYSQL ERROR

Started by on Oct 25, 2013 – Contents updated: Nov 20, 2013

Oct 25, 2013 16:58    

This blog has been working fine until about a month ago. Then it started producing the following error every time you try to post more than a few words.

MySQL error!

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'(Errno=1267)

Your query: Check URL against antispam blacklist

SELECT aspm_string

FROM btv_antispam

WHERE '

I just updated it to the latest stable version thinking that might solve the issue. It did not. :-(

Oct 26, 2013 02:42

Ok I've checked every single table and every field that has any collation listed is latin1_swedish_ci I still get the following when I try to post. This is copied directly from the error screen.

Additional information about this error:

MySQL error!

Illegal mix of collations (utf8_general_ci,COERCIBLE) and (latin1_swedish_ci,IMPLICIT) for operation 'like'(Errno=1267)

Your query: Check URL against antispam blacklist

SELECT aspm_string

FROM btv_antispam

WHERE '

In my last Journal, I addressed the possible change in the traditional pension system for new state workers. I have become aware that Rep. Randy McDaniel, R-Oklahoma City, as a financial adviser
and stockbroker, has become the point person for House of Representative leaders on pension negotiations. Currently, he proposes a shift to a 401(k) style retirement account for state workers in which the state would match employee contributions in a range
from 6 to 7 percent. He claims this would increase the actuarial strength of existing programs by statutory requirements to redirect revenue to shore up the unfunded liability of the existing pension systems. Part of his plan has been to do away with the possibility of Cost of Living Adjustments without direct dedicated revenue to cover their cost. This change reduced the unfunded liability of the state’s pension systems for 2010/11, but it went back up in 2012. This statistic indicates Rep. McDaniel
and his followers have not found the solution to the state employee’s retirement system yet. It is very likely this issue will provide significant battle ground for the upcoming 2nd Session of the 54th Legislature, since the rank
and file state worker assumes their retirement system has become somewhat of a political football
and the state’s political power brokers have the ball
and hope to score a winning touchdown.
\r\n


\r\n

As a long time educator, I believe competitive athletics have played a very important role in the development of students
and has played a critical balance between their maturation
and academic growth
and maturity. As a result of interim studies on this issue, there is currently a focus on the oversight role of the Oklahoma Secondary Schools Activities Association (OSSAA). Additionally, the Oklahoma Supreme Court is placing the Association activities under the Courts scrutiny. The conflict that has been raised is the issue of whether the funding for its existence
and activities are primarily
from state revenue through the schools
or the revenue generated by play-off gate revenue. If it is the latter, then the Court gave greater latitude in the Association’s rule making. However, to me the issue often relates to their contradictory rulings as they apply to urban
and rural school systems. This area of rule making is problematic
and has been evident over the years. It may be that I have always been associated with rural schools, but I have always sensed a discrepancy in rules related to these areas.
\r\n


\r\n

As a result of the focus on problems in the OSSAA, State Rep. Bobby Cleveland, R-Slaughterville, is calling for legislative interim studies to be held on the operation of the OSSAA. His focus, supported by former Oklahoma University football coach, Barry Switzer, however, is on requiring our public schools to accept home school students to participate in the school’s competitive programs, especially athletics. This issue was brought up last session but did not receive any traction. I have multiple concerns about this proposed rule change. If this is allowed, it will place our enrolled students at a disadvantage in terms of playing time as well as having a negative fiscal impact on the school in light of the cost of maintaining the required infrastructure for these competitive programs. Another issue relates to a recent memo sent out by the State Dept. of Education ruling on what is referred to as the “student cohort” condition. Specifically, as I have addressed in previous Journals, schools are given a negative hit on their graduation rate data when children within the school’s geographic boundaries are home schooled. As these home schooled youth arrive at the age associated with graduation, but do not graduate, as they would not since they are not enrolled in the school, the school is scored negatively on their graduation rate score on their A-F school grade. This is based on the Department of Education classifying these students as “drop-outs” rather than “transfer” students. If these drop-outs are now allowed to participate in secondary school activity programs while causing a school to be downgraded on their A-F score, this will be a form of double punishment on our public schools—one fiscal
and the other grading classification. We, as the legislature, must be exceptionally vigilant on this matter. It is my hope that Rep. Cleveland’s study takes into consideration the probability of the conundrum that is created by his request for this rule change
' LIKE CONCAT("%",aspm_string,"%")

LIMIT 0, 1

I copied the query listed in this error and submitted it in phpmyadmin and it runs returning 0 rows but NO error.

THINGS ARE STILL NOT WORKING!!! :-(

Oct 26, 2013 02:47

for a failsafe operation make sure all your database tables -and the subtables- are utf8

Oct 26, 2013 02:57

So you are saying they don't all have to be only the same they have to be utf8. I went with the latin1_swedish_ci because 90% were already that. Changing them all is going to take forever. :-(

Oct 26, 2013 12:26

Hi @minifairy,

There is a script that could do this automatically. If you want, please wait until I search for it and publish here.

Regards !

Oct 26, 2013 13:15

@minifairy here is the script. Before to following this instructions, please make a copy of your entire database.

1 Create a new file named ut8_upgrade.php and paste this code:


<?php
	require_once dirname(__FILE__).'/conf/_config.php';
	require_once $inc_path.'_main.inc.php';

	echo '<h2>'.T_('Upgrading all tables in b2evolution MySQL database to UTF-8...').'</h2>';
	evo_flush();

	$db_tables = $DB->get_col( 'SHOW TABLES FROM `'.$db_config['name'].'` LIKE "'.$tableprefix.'%"' );
	foreach( $db_tables as $table )
	{
		echo "Converting $table...<br />\n";
		evo_flush();
		$DB->query( 'ALTER TABLE `'.$table.'`
			DEFAULT CHARACTER SET utf8 COLLATE utf8_general_ci,
			CONVERT TO CHARACTER SET utf8 COLLATE utf8_general_ci' );
	}

	echo "Changing default charset of DB...<br />\n";
	evo_flush();
	$DB->query( 'ALTER DATABASE `'.$db_config['name'].'` CHARACTER SET utf8 COLLATE utf8_general_ci' );
	?>
	<p><?php echo T_('Upgrading done!')?></p>
?>

2 Put it in /blogs/..
3 Run the script on php command line or directly at your web browser: http://yourdomain.com/blogs/ut8_upgrade.php
4 Delete the file /blogs/utf8_upgrade.php (optional - there is no need to use it again)

This will change your database charset to utf8 and the collations to utf8_general_ci.

For further reference, this instructions will be published at: http://b2evolution.net/man/advanced-topics/troubleshooting/illegal-mix-of-collations

Regards !

Nov 20, 2013 02:14

@manuel

thanks for posting this script. very helpful in finding/converting a last hidden table, which i missed to convert earlier when i did sort out my database.

i found one problem that might cause confusion for other users. in my case it found several 'duplicate entries' in a table, which killed the script with error message

'An unexpected error has occurred!
..
MySQL error!
Duplicate entry 'regenwald ubungen' for key 'keyp_phrase'(Errno=1062)'

and left the rest of tables unchecked/unconverted.

Deleting this manipulated entries and restarting the script allowed converting the whole database in the end.


Form is loading...

Web Site Builder – This forum is powered by b2evolution CMS, a complete engine for your website.