Category: Database MySQL (MyISAM)

I have a problem with search in tiki.I wish i can search blog,article,forum... belong to author or creator.I'm researching Zend_Search_Lucene.If you have a solution of this problem.Please help me!!!thankful for all.

The plugin Exercise is a nice way to for students to get feedback on their learning. For an instructor or an admin it would be very useful to have a way of checking student's success. That way, the instructor could evaluate how easy the material is, or if certain students need extra help with something.

As far as I understand, right now exercise input data is not stored in the database so there is no way to track the exercise outcome. It would be great if a table could be added to the database where the this data could be stored and accessed.

This tracker is made to help filtering and sorting the features vs categories vs options we need to tag a bug/regression/wish with.

This should be migrated to the community site, and handled with ((doc:Organic groups)) and ((doc:User Trackers))

last_insert_id() is used in SQL in a couple of places in TikiWIki. This is a mysql-specific function that breaks functionality when other databases are used.

For TikiWiki CVS HEAD (1.10):

lib/articles/artlib.php:152: $query = "select `subId` from `tiki_submissions` where `subId`=last_insert_id()";

lib/multilingual/multilinguallib.php:28: $query = "insert into `tiki_translated_objects` (`type`,`objId`,`traId`,`lang`) values (?,?,last_insert_id(),?)";

Also present in TikiWiki 1.9.x

Reported by Thrain - thanks.

in file tiki-admin_include_login.php
if (isset($_REQUEST["auth_pear"])) {
check_ticket('admin-inc-login');
simple_set_toggle('auth_create_user_tiki');
............
....
.....
missing 2 lines below.
simple_set_value('auth_ldap_emailattr');
simple_set_value('auth_ldap_countryattr');

Missing database entries for ldap email and country

We now need to fiddle in the templates to remove the print icon. In many cases, a print option is not useful.

Need both a tiki_p style permission and a feature_print style feature for this. Need to document the actual names we used, too.

One tiki-wide setting to deactivate all printable pages all links to these pages (with the little printer icon).

This will also be useful for http://mobile.tikiwiki.org/ Let's try to do before 1.9.1

obs: waiting for cvs to unlock to commit

This manual time / date selection (code below) should be optional off

__Sorry, code plugin doesn't work so below looks weird...__

{CODE()}
<input type="text" name="start_freeform" value="{$start_freeform}">
<a {popup text="{tr}Format: mm/dd/yyyy hh:mm<br />...}{/tr} {tr}See strtotime php function{/tr}"}><img src="img/icons/help.gif" border="0" height="16" width="16" alt='{tr}help{/tr}'></a>
{tr}or{/tr}

<input type="text" name="end_freeform" value="{$end_freeform}">
<a {popup text="{tr}Format: mm/dd/yyy hh:mm<br />...}{/tr} {tr}See strtotime php function{/tr}"}><img src="img/icons/help.gif" border="0" height="16" width="16" alt='{tr}help{/tr}'></a>
{tr}or{/tr}
{CODE}

Add a feature to move files in between the file system and the database

This feature:
tiki-admin.php?page=trackers
tiki-admin.php?page=fgal
tiki-admin.php?page=wikiatt

should be added here:
tiki-admin.php?page=gal

(In fact, it should be backported from 1.10) "Move images from filesystem storage to database storage"

There seem to be some issue with MySQL 5.7 and Tiki.

For instance, mysql table tiki_payment_requests is not created due to issue with date with __no null__ but with a missing default value):

{CODE(colors="sql")}
DROP TABLE IF EXISTS `tiki_payment_requests`;
CREATE TABLE `tiki_payment_requests` (
`paymentRequestId` INT NOT NULL AUTO_INCREMENT,
`amount` DECIMAL(7,2) NOT NULL,
`amount_paid` DECIMAL(7,2) NOT NULL DEFAULT 0.0,
`currency` CHAR(3) NOT NULL,
`request_date` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
`due_date` TIMESTAMP NOT NULL,
`authorized_until` TIMESTAMP NULL,
`cancel_date` TIMESTAMP NULL,
`description` VARCHAR(100) NOT NULL,
`actions` TEXT,
`detail` TEXT,
`userId` int(8),
PRIMARY KEY( `paymentRequestId` )
) ENGINE=MyISAM;
{CODE}

{CODE(colors="sql")}
#1067 - Invalid default value for 'due_date'
{CODE}

One could think in migrating to mariadb, but it seems that mariadb and mysql started to diverge before mysql 5.7.
Taken from: https://seravo.fi/2015/10-reasons-to-migrate-to-mariadb-if-still-using-mysql
{QUOTE()}
Migration might become difficult after 2015
In versions MariaDB 10.0 and MySQL 5.6 the forks have already started to diverge somewhat but most likely users can still just upgrade from 5.6 to 10.0 without problems. The compatibility between 5.7 and 10.1 in the future is unknown, so the ideal time to migrate is now while it is still hassle-free. If binary incompatibilities arise in the future, database admins can always still migrate their data by dumping it and importing it in the new database.

With the above in mind, MariaDB is clearly our preferred option.

One of our customers once expressed their interest in migrating from MySQL to MariaDB and wanted us to confirm whether MariaDB is bug-free. Tragically we had to disappoint them with a negative answer. However we did assure them that the most important things are done correctly in MariaDB making it certainly worth migrating to.
{QUOTE}

The database table tiki_user_assigned_modules is not created on install. I am using mysql version 5.7.17, but the error may occur on other versions.

The query that creates the table:
DROP TABLE IF EXISTS `tiki_user_assigned_modules`;
CREATE TABLE `tiki_user_assigned_modules` (
`moduleId` int(8) NOT NULL,
`name` varchar(200) NOT NULL default '',
`position` varchar(20) default NULL,
`ord` int(4) default NULL,
`type` char(1) default NULL,
`user` varchar(200) NOT NULL default '',
PRIMARY KEY (`name`(30),`user`,`position`, `ord`),
KEY `id` (moduleId)
) ENGINE=MyISAM;

mysql throws an error of Primary Key cannot have NULL values, and the table fails to be created.

The missing table was first noticed when trying to modify the modules listed in the Assigned Modules area of the tiki-admin_modules.php page.

Suggested fix:
This table seems similar to the tiki_modules table. I would suggest modifying the position and ord columns to have the NOT NULL DEFAULT '' and '0' to match that table. I don't know what affect this will have on the tiki code overall, but it does let the database table be created.

The suggested sql code then becomes:
DROP TABLE IF EXISTS `tiki_user_assigned_modules`;
CREATE TABLE `tiki_user_assigned_modules` (
`moduleId` int(8) NOT NULL,
`name` varchar(200) NOT NULL default '',
`position` varchar(20) NOT NULL default '',
`ord` int(4) NOT NULL default '0',
`type` char(1) default NULL,
`user` varchar(200) NOT NULL default '',
PRIMARY KEY (`name`(30),`user`,`position`, `ord`),
KEY `id` (moduleId)
) ENGINE=MyISAM;

I am trying to test 5.0 RC1 on locahost, but after installing database i received this error message


''Table 'dbxxxxxx.tiki_rss_items' doesn't exist
The query was:
SELECT * FROM `tiki_rss_items` WHERE `rssId` IN( ? ) ORDER BY publication_date DESC
Values:

1. 8

The built query was likely:
SELECT * FROM `tiki_rss_items` WHERE `rssId` IN( '8' ) ORDER BY publication_date DESC''

It was the same with 5.0 beta 2, and when i look to my database tiki_rss_items doesn't exist.
Is it a new table with tiki 5? not install with upgrader?

I got this on October 25th 2006:

Warning: mysql error: Can't connect to local MySQL server through socket
'/var/lib/mysql/mysql.sock' (2) in query:

select count(*) from `tiki_banners` where `wed` = ? and `hourFrom`<=?
and `hourTo`>=? and
( ((`useDates` = ?) and (`fromDate`<=? and `toDate`>=?)) or
(`useDates` = ?) ) and
`impressions`<`maxImpressions` and `zone`=?

in lib/tikidblib.php on line 134


How could we show a more meaningful error message?


---

mysql tables sometimes crash.

When they do, the error message is cryptic, at best.

An average Tiki admin won't know what the problem is and could think it's Tiki's fault.


A graceful error message would inform the admin that:

"There are problems with the following tables: ". Please repair (link to how to repair)

Especially the tiki_sessions, which shuts down the site completely...

Some others are not so bad. Just a path disclosure.
{img src=images/code.png}%%% {CODE()}
Warning: mysql error: Can't open file: 'tiki_images.MYI' (errno: 145) in query: select count(*) from `tiki_images` where `galleryId`=? in lib/tikidblib.php on line 134
{CODE}
---

Another error to watch for:
{img src=images/code.png}%%% {CODE()}
Unable to connect to the database !
User droit_tiki has already more than 'max_user_connections' active connections
{CODE}
---
Maybe Tiki admin email should be in db/local.php? So the system could send email alerts when DB can't connect.

I installed Mysql 5 alpha to with 1.9 from the head of the branch and found this error when I tried to enter the tiki...

Warning: mysql error: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '-1' at line 1 in query:
select `permName`,`type`,`level`,`permDesc` from `users_permissions` order by `permName` desc
in C:\isense\icms\lib\tikidblib.php on line 133

Fatal error: Call to a member function on a non-object in C:\isense\icms\lib\tikidblib.php on line 151

This happened after saving http://dev.tikiwiki.org/tiki-admin_trackers.php?trackerId=5 where I had checked "and display attachments in listing?" (sub-option of Tracker items allow attachments?). I was unable to reproduce thereafter.

However, I noticed after that the ratings column had moved from first (left-hand side) to last (right-hand side).

{THUMB(id=26, url="tiki-browse_image.php?imageId=26")}{THUMB}

This tracker item could not be found

I have another problem on TikiWiki 2.0RC2:

I have two similar TikiWiki-installations. One just for testing on my Linux-Server and the second on a WinNT-Server. The Windows-Server is the one we want to/have to use for our Wiki :-(

On my Linux-Server works everything without problems.

On the Win-Server don’t work "approve+staging".

If I wont approve changes it shows me this error:

An error occured in a database query!

Context:
File tiki-approve_staging_page.php
Url tiki-approve_staging_page.php?page=Baustelle_Test
Query:
update `tiki_pages` set `description`=? `data`=? `comment`=? `lastModif`=? `version`=? `user`=? `ip`=? `page_size`=? `is_html`=? `wysiwyg`=? `wiki_authors_style`=? `flag`=? `lockedby`=? where `pageName`=?
Values:
0
1 <p>Test</p>
2 [approved by ottonormal]
3 1217330865
4 6
5 ottonormal
6 10.40.153.101
7 225
8 1
9
10 NULL
11
12
13 Test
Message:

Built query was probably:
update `tiki_pages` set `description`='' `data`='<p>Test</p>' `comment`=' [approved by ottonormal]' `lastModif`='1217330865' `version`='6' `user`='ottonormal' `ip`='10.40.153.101' `page_size`='225' `is_html`='1' `wysiwyg`='' `wiki_authors_style`=NULL `flag`='' `lockedby`='' where `pageName`='Test'

In order to avoid duplication of effort, It would be very nice to be able insert a calendar item when editing an article in Tiki.

I imagine that as a new option in article edit form, for any article type or just some type, like "event", or so. You should be able to select the date or dates to insert that info in the calendar feature, select which calendar, etc.

So that calendar and upcoming_events module is updated when a new article is posted with that option selected.

using long Chinese characters as browser title will cause the browser title borken, for example, if you use '机电工程师' as browser title, the browser will show '机电工程帛'.

it's maybe an adodb problem.

On the Create/Admin Quizzes page, when the setting "Shuffle Questions" is checked, the questions are not shuffled when the user takes the quiz.

CREATE TABLE tiki_articles(

articleId int( 8 ) NOT NULL AUTO_INCREMENT ,
.
.
reads int( 14 ) default NULL ,
.
.
KEY reads( reads ) ,
KEY author( author( 32 ) ) ,
FULLTEXT KEY ft( title, heading, body )
) TYPE = MYISAM AUTO_INCREMENT =1

cause

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'reads int(14) default NULL,
votes int(8) default NULL,
points int(14) defaul' at line 28

Install:

tikiwiki 1.9.3.2
PHP 5.1.4 (Build 2600)
Apache 2.2.2
MySQL 5.0

When adding the following text to a page (text between but not including the "$" symbols) it will preview fine, but the Save operation causes an error with MySQL which shows up in the Apache logs as:
{CODE()}
[Thu Jun 22 17:04:50 2006] [notice] Parent: child process exited with status 3221225477 -- Restarting.
[Thu Jun 22 17:04:50 2006] [notice] Apache/2.2.2 (Win32) PHP/5.1.4 configured -- resuming normal operations
[Thu Jun 22 17:04:50 2006] [notice] Server built: Apr 29 2006 18:32:31
[Thu Jun 22 17:04:50 2006] [notice] Parent: Created child process 672
[Thu Jun 22 17:04:51 2006] [notice] Child 672: Child process is running
[Thu Jun 22 17:04:51 2006] [notice] Child 672: Acquired the start mutex.
[Thu Jun 22 17:04:51 2006] [notice] Child 672: Starting 250 worker threads.
[Thu Jun 22 17:04:51 2006] [notice] Child 672: Starting thread to listen on port 80.
{CODE}

Here is the text you can try:


$

))Cohen-Steiner(( “Restricted delauney triangulations and normal

cycles”. are estimates of

the principle curvature directions at the vertex. Once we have computed principle curvatures for every isosurface

vertex, we can use the jack triangle floodfiller to flood-follow lines of curvature. The complexity here is how to

sample the curvature fields to produce curves.

*)

$

{syntax type="tiki" editor="plain"}
After migration from 2.3 to 4.1 the following error occurs, if i try to open the image galerys with the link http://www.kfs-hg-test.de/tiki-galleries.php?filter=topgal:

select `sortorder`,`sortdirection` from `tiki_galleries` where `galleryId`='9'

The single pictures are available in the wiki oages as befiorder, but it is not possible to list the galerys.

10 MySQL "create table" commands are not compatible w/ the 3.23.58 version of MySQL

The last 2 use tables use ENGINE=MyISAM instead of TYPE=MyISAM and have a VARCHAR field of size 256 (255 is ok to use)

Here are those queries
Command: CREATE TABLE tiki_modules (
moduleId int(8) NOT NULL auto_increment,
name varchar(200) NOT NULL default '',
position char(1) default NULL,
ord int(4) default NULL,
type char(1) default NULL,
title varchar(255) default NULL,
cache_time int(14) default NULL,
rows int(4) default NULL,
params varchar(255) default NULL,
groups text,
PRIMARY KEY (name, position, ord),
KEY positionType (position, type),
KEY moduleId (moduleId)
) TYPE=MyISAM
Message: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead

Command: CREATE TABLE tiki_page_drafts (
user varchar(200) default '',
pageName varchar(255) NOT NULL,
data mediumtext,
description varchar(200) default NULL,
comment varchar(200) default NULL,
lastModif int(14) default NULL,
PRIMARY KEY (pageName(120), `user`(120))
) TYPE=MyISAM
Message: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead

Command: CREATE TABLE tiki_tracker_item_fields (
itemId int(12) NOT NULL default '0',
fieldId int(12) NOT NULL default '0',
lang char(16) default NULL,
value text,
PRIMARY KEY (itemId,fieldId,lang),
FULLTEXT KEY ft (value)
) TYPE=MyISAM
Message: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead

Command: CREATE TABLE tiki_user_assigned_modules (
name varchar(200) NOT NULL default '',
position char(1) default NULL,
ord int(4) default NULL,
type char(1) default NULL,
user varchar(200) NOT NULL default '',
PRIMARY KEY (name(30),user,position)
) TYPE=MyISAM
Message: All parts of a PRIMARY KEY must be NOT NULL; If you need NULL in a key, use UNIQUE instead

Command: CREATE TABLE `tiki_webmail_contacts_ext` (
`contactId` int(11) NOT NULL,
`fieldId` int(10) unsigned NOT NULL,
`value` varchar(256) NOT NULL,
`hidden` tinyint(1) NOT NULL,
KEY `contactId` (`contactId`)
) ENGINE=MyISAM
Message: Too big column length for column 'value' (max = 255). Use BLOB instead

Command: CREATE TABLE `tiki_webmail_contacts_fields` (
`fieldId` int(10) unsigned NOT NULL auto_increment,
`user` VARCHAR( 200 ) NOT NULL ,
`fieldname` VARCHAR( 256 ) NOT NULL ,
`order` int(2) NOT NULL default '0',
`show` char(1) NOT NULL default 'n',
PRIMARY KEY ( `fieldId` ),
INDEX ( `user` )
) ENGINE = MyISAM
Message: Too big column length for column 'fieldname' (max = 255). Use BLOB instead

I was running 1.9.9 on a SLES 10 linux box, and we are running MySQL on Novell Netware, MySQL version 4.0.26a.

I tried an upgrade directly over the top of 1.9.9, and I got that error. So, I tried a Clean install on a new database, and I got the same error.

As a troubleshooting step, I tried creating a blank database with the name that it specifies, but then it just gives the following error message
"Unknown column 'bits.translation_bit_id' in 'field list'".

I suspect the installer script isn't creating this table and fields properly.

This error occurs anytime any Page is clicked on, including the main index page. It is a major roadblock to basic functionality.