The Arcive of Official vBulletin Modifications Site.

About Developer · **Released**: 06-27-2009

**Text Removed**

NLP-er · #**282** 07-07-2009, 12:09 PM

Quote:

Originally Posted by Dave Hybrid

To combat them I set a new email in vb config and an outlook rule and now they all go to their own folder in outlook. No big deal.

What do you think Dave to write about it in mod description as known issues instead of telling new people about it over and over again...

I saw question about this issue so many times... Also in PM.

NLP-er · #**283** 07-07-2009, 01:20 PM

Was asking about miracle - I give you miracle. I changed one query in deletion of duplicated data and now it is very fast. I will just tell that before changes 1 query on 100 000 rows with 1000 duplicated rows took 10 minutes before update, now it takes less than 1 second!!!

So cleaning each table has 5 queries. 2 are instant, 2 are very fast and one took me 7 seconds on 100 000 rows - this one cannot be optimized, there is no any subquery, we just need to ask DB about those data.

Hope now Dave will made new official release, which will not allow for data duplication in 2 of our 3 cache tables, and makes whole mod works faster, and your DB smaller.

Below you have again full and updated description how to clean duplicated data. I was able to run this by my browser client, but be aware, that in case of some large databases, server can go away by this client and in such case you will need to use some other client than www.

Also note that if you made changes described here (https://vborg.vbsupport.ru/showpost....&postcount=242) which I hope will be included in official release, then you will not have to remove duplicated data from wt_cache_short and wt_cache_medium anymore (only once during described procedure). And after that only wt_cache will need to delete duplicated data from time to time.

So here you have again description and procedure, but much faster this time

:
If you would like to check do you have data duplication in your cache execute those queries (time consuming). Execute one by one - each one works on other cache table and tells you how many times and which data is duplicated (1st column duplication counter, 2nd for which originaltext, 3rd for which language):

Code:

select count(*) counter, originaltext, tl from wt_cache group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_short group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_medium group by originaltext, tl having count(*) > 1 order by counter desc;

If you want to delete data duplication first need to create 2 tables for temporary data:

Code:

CREATE TABLE saver (
id INT,
tl VARCHAR(10),
originaltext VARCHAR(65000)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE cleaner (
id INT
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

And when you have those you can execute clearing queries - note those will leave first translation in your database and only remove next translations for same text and language.

Code:

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_short group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_short cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_short USING wt_cache_short INNER JOIN cleaner ON wt_cache_short.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_medium group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_medium cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id;

racale · #**284** 07-07-2009, 01:33 PM

This product works with version 3.8.3
I tried but it does not work
thanks

Code:

CREATE TABLE wt_cache (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(65000),
translated TEXT,
INDEX(originaltext(323), tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE wt_cache_medium (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(255),
translated VARCHAR(1000),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE wt_cache_short (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(50),
translated VARCHAR(255),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

NLP-er · #**285** 07-07-2009, 01:43 PM

Quote:

Originally Posted by racale

This product works with version 3.8.3
I tried but it does not work
thanks

Code:

CREATE TABLE wt_cache (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(65000),
translated TEXT,
INDEX(originaltext(323), tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_bin;

CREATE TABLE wt_cache_medium (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(255),
translated VARCHAR(1000),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE wt_cache_short (
id INT AUTO_INCREMENT NOT NULL PRIMARY KEY,
tl VARCHAR(10),
originaltext VARCHAR(50),
translated VARCHAR(255),
INDEX (originaltext, tl)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

You already created table wt_cache

If you want to update - just fallow update procedure, or drop table first (will lose all data if have any).

racale · #**286** 07-07-2009, 02:08 PM

Quote:

Originally Posted by NLP-er

You already created table wt_cache

If you want to update - just fallow update procedure, or drop table first (will lose all data if have any).

First of all I wanted to say that nn are very experienced

I'm running the full instructions ......
just that when I send Code: mida that error
I cancel the DB and tried to do anything but

I do not know how to do

NLP-er · #**287** 07-07-2009, 03:17 PM

Quote:

Originally Posted by racale

First of all I wanted to say that nn are very experienced

I'm running the full instructions ......
just that when I send Code: mida that error
I cancel the DB and tried to do anything but

I do not know how to do

You aready have DB set. So what is the problem?

Dave Hybrid · #**288** 07-07-2009, 04:03 PM

Quote:

Originally Posted by NLP-er

Was asking about miracle - I give you miracle. I changed one query in deletion of duplicated data and now it is very fast. I will just tell that before changes 1 query on 100 000 rows with 1000 duplicated rows took 10 minutes before update, now it takes less than 1 second!!!

So cleaning each table has 5 queries. 2 are instant, 2 are very fast and one took me 7 seconds on 100 000 rows - this one cannot be optimized, there is no any subquery, we just need to ask DB about those data.

Hope now Dave will made new official release, which will not allow for data duplication in 2 of our 3 cache tables, and makes whole mod works faster, and your DB smaller.

Below you have again full and updated description how to clean duplicated data. I was able to run this by my browser client, but be aware, that in case of some large databases, server can go away by this client and in such case you will need to use some other client than www.

Also note that if you made changes described here (https://vborg.vbsupport.ru/showpost....&postcount=242) which I hope will be included in official release, then you will not have to remove duplicated data from wt_cache_short and wt_cache_medium anymore (only once during described procedure). And after that only wt_cache will need to delete duplicated data from time to time.

So here you have again description and procedure, but much faster this time

:
If you would like to check do you have data duplication in your cache execute those queries (time consuming). Execute one by one - each one works on other cache table and tells you how many times and which data is duplicated (1st column duplication counter, 2nd for which originaltext, 3rd for which language):

Code:

select count(*) counter, originaltext, tl from wt_cache group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_short group by originaltext, tl having count(*) > 1 order by counter desc;

select count(*) counter, originaltext, tl from wt_cache_medium group by originaltext, tl having count(*) > 1 order by counter desc;

If you want to delete data duplication first need to create 2 tables for temporary data:

Code:

CREATE TABLE saver (
id INT,
tl VARCHAR(10),
originaltext VARCHAR(65000)
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

CREATE TABLE cleaner (
id INT
) ENGINE = MYISAM, CHARACTER SET utf8 COLLATE utf8_general_ci;

And when you have those you can execute clearing queries - note those will leave first translation in your database and only remove next translations for same text and language.

Code:

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache USING wt_cache INNER JOIN cleaner ON wt_cache.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_short group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_short cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_short USING wt_cache_short INNER JOIN cleaner ON wt_cache_short.id = cleaner.id;

delete from cleaner;
delete from saver;
insert into saver (SELECT min(id) as id, tl, originaltext from wt_cache_medium group by originaltext,tl having count(*) > 1);
insert into cleaner (SELECT cache.id from saver, wt_cache_medium cache where saver.originaltext=cache.originaltext and saver.tl=cache.tl and saver.id<>cache.id);
DELETE FROM wt_cache_medium USING wt_cache_medium INNER JOIN cleaner ON wt_cache_medium.id = cleaner.id;

That worked much better, will test and release shortly. Are you planning any more optimization? Or are we 100%? :up:

LoveStream · #**289** 07-07-2009, 04:09 PM

Hello.
Thanks for your hack.

This is my first trial, but I encount below error.

Code:

Fatal error: Call to undefined function: mysql_set_charset() in /home/hosting_users/www/forum/translate.php on line 35

My target goal is that;

our origin text is mainly Korean
other translate to all other languages.

Above line 35 is,

Code:

////////////////////////////////////////////////////
if ($enablecache) {
mysql_connect ($mysqlserver, $dbusername, $dbpassword);
mysql_select_db ($dbname);
#35: mysql_set_charset('utf8');
}
////////////////////////////////////////////////////

Though, our main lanuage is Korean, but I did not change it to "ko".

PHP Code:


			
<?php
////////////////////////////////////////////////////
// Global Translator API
////////////////////////////////////////////////////

global $enablesession, $enablecache, $originalEncoding, $fl;
////////////////////////////////////////////////////
//        SETTINGS
////////////////////////////////////////////////////
$enablesession = false; //ignore
$enablecache = true; //true - enable cache setting, false - disable cache setting
$originalEncoding = 'iso-8859-1'; - //refer to your forum source code for your base encoding
$fl = 'en'; //current language (original) - refer table for language varibles

I had tried this $originalEncoding = 'iso-8859-1'; to $originalEncoding = 'utf-8';, but it doesn't work with the same error.

My Server set is,

PHP 4.4.1
MySQL 5.0.19
cURL: enable
mysql character set : utf-8

What it's problem?
help me. Thank you.

1Unreal · #**290** 07-07-2009, 04:52 PM

Thanks for the updates

1Unreal · #**291** 07-07-2009, 04:57 PM

Quote:

Originally Posted by 1Unreal

Would you be able to create something which will detect the users language. You can get it from $_SERVER['HTTP_ACCEPT_LANGUAGE']. It gives a list of their accepted languages.

Bumping my post.

I think this would be a very valuable feature

X vBulletin 3.8.12 by vBS Debug Information
Page Generation 0.11101 seconds Memory Usage 2,337KB Queries Executed 25 (?)
More Information
Template Usage: (1)SHOWTHREAD (1)ad_footer_end (1)ad_footer_start (1)ad_header_end (1)ad_header_logo (1)ad_navbar_below (1)ad_showthread_beforeqr (8)bbcode_code (1)bbcode_php (6)bbcode_quote (1)footer (1)forumjump (1)forumrules (1)gobutton (1)header (1)headinclude (1)modsystem_post (1)navbar (4)navbar_link (120)option (1)pagenav (1)pagenav_curpage (4)pagenav_pagelink (2)pagenav_pagelinkrel (11)post_thanks_box (11)post_thanks_button (1)post_thanks_javascript (1)post_thanks_navbar_search (11)post_thanks_postbit_info (10)postbit (11)postbit_onlinestatus (11)postbit_wrapper (1)spacer_close (1)spacer_open (1)tagbit_wrapper Phrase Groups Available: global inlinemod postbit posting reputationlevel showthread	Included Files: ./showthread.php ./global.php ./includes/init.php ./includes/class_core.php ./includes/config.php ./includes/functions.php ./includes/class_hook.php ./includes/modsystem_functions.php ./includes/functions_bigthree.php ./includes/class_postbit.php ./includes/class_bbcode.php ./includes/functions_reputation.php ./includes/functions_post_thanks.php Hooks Called: init_startup init_startup_session_setup_start init_startup_session_setup_complete cache_permissions fetch_threadinfo_query fetch_threadinfo fetch_foruminfo style_fetch cache_templates global_start parse_templates global_setup_complete showthread_start showthread_getinfo forumjump showthread_post_start showthread_query_postids showthread_query bbcode_fetch_tags bbcode_create showthread_postbit_create postbit_factory postbit_display_start post_thanks_function_post_thanks_off_start post_thanks_function_post_thanks_off_end post_thanks_function_fetch_thanks_start post_thanks_function_fetch_thanks_end post_thanks_function_thanked_already_start post_thanks_function_thanked_already_end fetch_musername postbit_imicons bbcode_parse_start bbcode_parse_complete_precache bbcode_parse_complete postbit_display_complete post_thanks_function_can_thank_this_post_start pagenav_page pagenav_complete tag_fetchbit_complete forumrules navbits navbits_complete showthread_complete
Messages:

The Arcive of Official vBulletin Modifications Site.

It is not a VB3 engine, just a parsed copy!