Skip navigation links

MySQL Forums :: Views :: VIEW crashing MySQL...


Advanced Search

VIEW crashing MySQL...
Posted by: Christophe NOUVEL ()
Date: January 09, 2013 01:54AM

Hi everybody,

I don't frequently use views in MySQL, but a customer tried the following :
- create a table with a "text" column containing 2 digits integers,
- select avg on this field : OK
- create a view with this select : crash MySQL???
- the same view with a round(avg()) behaves correctly.

Crash occurs on genuine CentOS 6.3 32 bits updated :
LSB Version: :core-4.0-ia32:core-4.0-noarch:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 6.3 (Final)
Release: 6.3
Codename: Final
mysql-5.1.66-2.el6_3.i686
mysql-connector-odbc-5.1.5r1144-7.el6.i686
mysql-libs-5.1.66-2.el6_3.i686
MySQL-python-1.2.3-0.3.c1.1.el6.i686
mysql-server-5.1.66-2.el6_3.i686
perl-DBD-MySQL-4.013-3.el6.i686
php-mysql-5.3.3-14.el6_3.i686

The same view on
LSB Version: :core-4.0-amd64:core-4.0-ia32:core-4.0-noarch:graphics-4.0-amd64:graphics-4.0-ia32:graphics-4.0-noarch:printing-4.0-amd64:printing-4.0-ia32:printing-4.0-noarch
Distributor ID: CentOS
Description: CentOS release 5.8 (Final)
Release: 5.8
Codename: Final
mysql-5.0.95-1.el5_7.1
mysql-devel-5.0.95-1.el5_7.1
mysql-server-5.0.95-1.el5_7.1
perl-DBD-MySQL-3.0007-2.el5
php-mysql-5.1.6-39.el5_8

behaves as expected.

An export of the database :
test.sql
-- phpMyAdmin SQL Dump
-- version 3.5.4
-- http://www.phpmyadmin.net
--
-- Client: localhost
-- Généré le: Mer 09 Janvier 2013 à 08:28
-- Version du serveur: 5.1.66
-- Version de PHP: 5.3.3

SET SQL_MODE="NO_AUTO_VALUE_ON_ZERO";
SET time_zone = "+00:00";


/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;

--
-- Base de données: `test`
--

-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `avg`
--
DROP VIEW IF EXISTS `avg`;
CREATE TABLE IF NOT EXISTS `avg` (
`Moyenne` double
);
-- --------------------------------------------------------

--
-- Doublure de structure pour la vue `round_avg`
--
DROP VIEW IF EXISTS `round_avg`;
CREATE TABLE IF NOT EXISTS `round_avg` (
`Moyenne` double(17,0)
);
-- --------------------------------------------------------

--
-- Structure de la table `t_tir`
--

DROP TABLE IF EXISTS `t_tir`;
CREATE TABLE IF NOT EXISTS `t_tir` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`score` text,
PRIMARY KEY (`id`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=26 ;

--
-- Contenu de la table `t_tir`
--

INSERT INTO `t_tir` (`id`, `score`) VALUES
(1, '87'),
(2, '93'),
(3, '90'),
(4, '83'),
(5, '90'),
(6, '83'),
(7, '80'),
(8, '77'),
(9, '73'),
(10, '87'),
(11, '83'),
(12, '73'),
(13, '93'),
(14, '87'),
(15, '77'),
(16, '80'),
(17, '73'),
(18, '93'),
(19, '80'),
(20, '93'),
(21, '93'),
(22, '83'),
(24, '83'),
(25, '77');

-- --------------------------------------------------------

--
-- Structure de la vue `avg`
--
DROP TABLE IF EXISTS `avg`;

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost` SQL SECURITY DEFINER VIEW `avg` AS select avg(`t_tir`.`score`) AS `Moyenne` from `t_tir`;

-- --------------------------------------------------------

--
-- Structure de la vue `round_avg`
--
DROP TABLE IF EXISTS `round_avg`;

CREATE ALGORITHM=UNDEFINED DEFINER=`test`@`localhost` SQL SECURITY DEFINER VIEW `round_avg` AS select round(avg(`t_tir`.`score`),0) AS `Moyenne` from `t_tir`;

/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;


Any idea??

Thanks,

Christophe.



Edited 1 time(s). Last edit at 01/09/2013 04:07AM by Christophe NOUVEL.

Options: ReplyQuote


Subject Views Written By Posted
VIEW crashing MySQL... 1232 Christophe NOUVEL 01/09/2013 01:54AM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.