[R63B] mySQL issues

[Stevee]

Hey there,

I'm experiencing some mySQL issues, I've tried fixing these myself and what not, but I do not seem to know what the issues are, if someone could help me possibly fix this, I'd really appreciate it.

Here are the errors:

You must be registered for see links

(PS There's multiple)

The client works and everything as well, it's just certain items are being a bitch and causing mySQL errors.


Thanks,
Stevee

 

[Sledmore]

Some strange errors there (I say strange because you should have not encountered some).

This query should fix the groups issue:

ALTER TABLE `groups`
MODIFY COLUMN `Petitions`  text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

The next error (ip_cache) query error, you shouldn't encounter as it shouldn't be enabled, seems that someone ran the :language command.

This query should fix it (but I'd suggest disabling that command):

CREATE TABLE `ip_cache` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(25) NULL DEFAULT '' ,
`ip`  int(11) NULL ,
PRIMARY KEY (`id`));

The next error is an error pointing out that the `activity_points_lastupdate` field size is too small/value entered is too large, try this:

ALTER TABLE `users`
MODIFY COLUMN `activity_points_lastupdate`  double(5,0) NOT NULL DEFAULT 0;

Okay, for this error you shouldn't of encountered this, so this query will delete the `is_activated` row and then re-add it giving all default values a value of '0'.

ALTER TABLE `user_effects`
DROP COLUMN `is_activated`,
ADD COLUMN `is_activated`  enum('0','1') NULL DEFAULT '0' AFTER `total_duration`;

Here is a query that should fix the current issue with pets and future issues (I downloaded a database and gave it some random values, since mine is clean):

ALTER TABLE `user_pets`
MODIFY COLUMN `room_id`  int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `user_id`,
MODIFY COLUMN `expirience`  int(11) NOT NULL DEFAULT 0 AFTER `type`,
MODIFY COLUMN `energy`  int(11) NOT NULL DEFAULT 100 AFTER `expirience`;

You shouldn't encounter the issue that you are having with the moodlights but this should fix it: (It will TURN OFF all current moodlights)

ALTER TABLE `items_moodlight`
DROP COLUMN `enabled`,
ADD COLUMN `enabled`  enum('0','1') NULL DEFAULT '0' AFTER `item_id`;

This next error could be due to the fact that the navigator tries to update the values to nothing or it could be your database, try this:

ALTER TABLE `rooms`
MODIFY COLUMN `allow_pets`  enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '1' AFTER `landscape`,
MODIFY COLUMN `allow_pets_eat`  enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '1' AFTER `allow_pets`;

You may have to do some fixes to your source, but some of the querys should help you.

 

[Stevee]

Some strange errors there (I say strange because you should have not encountered some).

This query should fix the groups issue:

ALTER TABLE `groups`
MODIFY COLUMN `Petitions`  text CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

The next error (ip_cache) query error, you shouldn't encounter as it shouldn't be enabled, seems that someone ran the :language command.

This query should fix it (but I'd suggest disabling that command):

CREATE TABLE `ip_cache` (
`id`  int(11) NOT NULL AUTO_INCREMENT ,
`username`  varchar(25) NULL DEFAULT '' ,
`ip`  int(11) NULL ,
PRIMARY KEY (`id`));

The next error is an error pointing out that the `activity_points_lastupdate` field size is too small/value entered is too large, try this:

ALTER TABLE `users`
MODIFY COLUMN `activity_points_lastupdate`  double(5,0) NOT NULL DEFAULT 0;

Okay, for this error you shouldn't of encountered this, so this query will delete the `is_activated` row and then re-add it giving all default values a value of '0'.

ALTER TABLE `user_effects`
DROP COLUMN `is_activated`,
ADD COLUMN `is_activated`  enum('0','1') NULL DEFAULT '0' AFTER `total_duration`;

Here is a query that should fix the current issue with pets and future issues (I downloaded a database and gave it some random values, since mine is clean):

ALTER TABLE `user_pets`
MODIFY COLUMN `room_id`  int(10) UNSIGNED NOT NULL DEFAULT 0 AFTER `user_id`,
MODIFY COLUMN `expirience`  int(11) NOT NULL DEFAULT 0 AFTER `type`,
MODIFY COLUMN `energy`  int(11) NOT NULL DEFAULT 100 AFTER `expirience`;

You shouldn't encounter the issue that you are having with the moodlights but this should fix it: (It will TURN OFF all current moodlights)

ALTER TABLE `items_moodlight`
DROP COLUMN `enabled`,
ADD COLUMN `enabled`  enum('0','1') NULL DEFAULT '0' AFTER `item_id`;

This next error could be due to the fact that the navigator tries to update the values to nothing or it could be your database, try this:

ALTER TABLE `rooms`
MODIFY COLUMN `allow_pets`  enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '1' AFTER `landscape`,
MODIFY COLUMN `allow_pets_eat`  enum('0','1') CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL DEFAULT '1' AFTER `allow_pets`;

You may have to do some fixes to your source, but some of the querys should help you.

Thank you for posting! That has really helped me...

BUT
I'm getting this error still (When I close my emulator)

Error in query:
UPDATE users SET users.online = '0', users.last_online = '9/14/2013 10:45:09 PM', activity_points = '6500', activity_points_lastupdate = '1379223365', credits = '290980626', achievement_points = 65 WHERE id = '2'; DELETE FROM user_online WHERE userid = 2; UPDATE users SET users.online = '0', users.last_online = '9/14/2013 10:45:09 PM', activity_points = '6500', activity_points_lastupdate = '1379223418', credits = '17200', achievement_points = 75 WHERE id = '317'; DELETE FROM user_online WHERE userid = 317;
MySql.Data.MySqlClient.MySqlException (0x80004005): Data too long for column 'last_online' at row 1
  at MySql.Data.MySqlClient.MySqlStream.ReadPacket()
  at MySql.Data.MySqlClient.NativeDriver.GetResult(Int32& affectedRow, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.GetResult(Int32 statementId, Int32& affectedRows, Int32& insertedId)
  at MySql.Data.MySqlClient.Driver.NextResult(Int32 statementId, Boolean force)
  at MySql.Data.MySqlClient.MySqlDataReader.NextResult()
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteReader(CommandBehavior behavior)
  at MySql.Data.MySqlClient.MySqlCommand.ExecuteNonQuery()
  at Database_Manager.Database.Session_Details.QueryAdapter.runQuery()

Also, can you help me fix Diamonds?

 

[Sledmore]

That is a new error, this (should) fix it:

ALTER TABLE `users`
MODIFY COLUMN `last_online`  varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

I personally use Unix timestamps in my edit, also what is the error with Diamonds?

 

[Stevee]

That is a new error, this (should) fix it:

ALTER TABLE `users`
MODIFY COLUMN `last_online`  varchar(30) CHARACTER SET latin1 COLLATE latin1_swedish_ci NOT NULL;

I personally use Unix timestamps in my edit, also what is the error with Diamonds?

After running your SQL's above, I Get these mySQL errors:

You must be registered for see links

Critical Exception:

You must be registered for see links

I've got a few packet errors as well..


Diamonds, when you try to give someone diamonds, it just remains like this:

You must be registered for see links

Alsp @Sledmore

You must be registered for see links

- Packet errors