Problem z zapytaniem złożonym do bazy MYSQL + odwołanie do INFORMATION_SCHEMA

0

Mam problem z ze złożonym zapytaniem w którym chcę pobrać nazwy kolumn z bazy information_schema.
SELECT * FROM test.p WHERE name IN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 't' AND COLUMN_NAME LIKE 'p%')
To zapytanie działa na moim lokalnym "serwerze" a na hostingu w az.pl już nie, wynik jest pusty, błędów brak. W czym może być problem ?
Może to kwestia jakiś ustawień zmiennych serwera ?
Zaznaczam że pytanie to rozbite na dwa ale proste działa i zwraca poprawne wyniki.
SELECT * FROM test.p WHERE name IN ( ręcznie uzupełnione)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 't' AND COLUMN_NAME LIKE 'p%'

A to jest baza testowa

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE TABLE IF NOT EXISTS `p` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) CHARACTER SET utf8 DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_polish_ci;

DELETE FROM `p`;
/*!40000 ALTER TABLE `p` DISABLE KEYS */;
INSERT INTO `p` (`id`, `name`) VALUES
	(1, 'p1'),
	(2, 'p2'),
	(3, 'p3'),
	(4, 'p4'),
	(5, 'p5');
/*!40000 ALTER TABLE `p` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `t` (
  `id` int(11) NOT NULL,
  `p2` varchar(20) DEFAULT NULL,
  `p3` varchar(20) DEFAULT NULL,
  `p5` varchar(20) DEFAULT NULL,
  `p1` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

DELETE FROM `t`;
/*!40000 ALTER TABLE `t` DISABLE KEYS */;
/*!40000 ALTER TABLE `t` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;
0

Cześć, skąd możesz wiedzieć jakie ja mam bazy na swoim hostingu ?
Po drugie oczywiście że tam baza ma inną nazwę bo nie jest możliwe nazwać bazy na hostingu "test" bo są narzucane przedrostki dla tego specjalnie przesłałem uproszczoną bazę na potrzeby forum.

0

Jesteś adminem az.pl ?
Skąd CI przyszło do głowy że tam nie ma bazy ?
Baza tam jest.

0

Kolego no wiadomo że zapytanie puściłem na bazę o nazwie test bo nie będę zapodawać nazwy bazy z hostingu.
Reasumując, nazwy baz się zgadzają ! Jak testuję na hostingu to wiadomo że zmieniam nazwę na właściwą to oczywiste - ale nie działa.

0

@m72m72: widzisz, sam oczekujesz, żeby byle kto nie mógł wyświetlić twoich metadanych na dzielonym hostingu (listowania baz / tabel etc)

Sądzę, że reguły /prawa na dzielonym hostingu są a) ograniczone b) odmienne od prostej domowej instalacji

NA SZCZĘŚCIE nie prowadzę takiej działalności, ale na pewno bym ograniczał w konfiguracji, a nawet przez patchowany kod.

0

Nie wiem w czym problem ale jeśli to jest ograniczenie hostingu to im podziękuję, na linuxpl.com to działało. Nie może być tak żeby była blokada do information_schema.
Ale z kolei odpytywanie tej bazy jako pytania niezagnieżdżonego działa bez problemu.

1

Kolego @m72m72, czy ta Twoja nie-testowa-baza-na-hostingu, ma ustawione odpowiednie collation? Możliwe, że porównujesz upper/lower case i jest case sensitive...
Co więcej, lokalnie możesz mieć inne ustawienia domyślne, niż na hostingu, stąd odmienne zachowanie.

0

No właśnie się pytam czy to może jest jakaś kwestia ustawień na serwerze. Z pomocy technicznej się nie odezwali i pewnie nie odezwą. Co do collation to dokładnie nie wiem o co chodzi ale próbowałem wszędzie gdzie się tylko da poustawiać kodowania znaków itp na takie same, utf8_general_ci, inne też próbowałem. Podejrzewam że może chodzić o to że podzapytanie mimo że poprawne, jest zwracane w "innej formie", innym kodowaniu (nie wiem jak to nazwać) i nie jest rozumiane przez zapytanie główne. Jeszcze raz zaznaczam że obydwa zapytania rozdzielone działają poprawnie i dają dobre wyniki.

0

Panczo, barania łąko, może jeszcze podasz link do googli ? Jak nie wiesz w czym jest problem to się nie odzywaj. Przecież widzisz że w bazie którą udostępniłem wszystko gra jeśli chodzi o duże czy małe litery. Myślałem że tu jest wyższy poziom niż na forum_kretynów czyli elektroda.pl Nikt z wyżej piszących nie ma pojęcia w czym problem a pisze byle co.

0

Jeżeli dokumentacja to dla ciebie link do google to nie bardzo wiem co chcesz osiągnąć. Ale pobawimy się daj screeny z wyników jakie zwracają te zapytania

SELECT * FROM test.p WHERE name IN ( ręcznie uzupełnione)
SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='test' AND TABLE_NAME = 't' AND COLUMN_NAME LIKE 'p%'
0

Focie

0

@m72m72: do foci dodaj jeszcze rezultaty zapytań:

  • select column_name, hex(column_name) from information_schema...
  • select name,hex(name) from test where name in ('p1','p2','p3','p4');

edycja:
Nie wiem na ile mysql jest czuły na typy danych, ale:

  • INFORMATION_SCHEMA.COLUMN_NAME to NVARCHAR(128)
  • P.NAME to VARCHAR

Próbowałeś zmieniać P.NAME na NVARCHAR(128) ?

2

Ja zaczałbym od tego: http://sqlfiddle.com/#!9/10b523/9

3

Faktycznie to jest bug mysql-a zresztą zaraportowany: https://bugs.mysql.com/bug.php?id=77191

Obejście:


set optimizer_switch='semijoin=off';         

http://sqlfiddle.com/#!9/b60e73/12

0

A to (set optimizer_switch='semijoin=off';) trzeba przed każdym zapytaniem wstawiać ?

4

Jako barania łąka odeślę do "googla": https://dev.mysql.com/doc/refman/5.7/en/switchable-optimizations.html

0

Ciąg dalszy problemów. W złożonym zapytaniu niepotrzebnie wyniki się same sortują nie wiem czemu.
Są 2 tabele, t1 i t2. Celem jest odczyt nazwy kolumn z tabeli t2 w takiej kolejności jak są stworzone czyli p2 p3 p1 a następnie przyporządkowanie im nazw z tabeli t1.
Oczekiwałem wyniku p2 = nazwa2, p3=nazwa3 p1=nazwa1 a zapytanie powoduje że wyniki się same sortują i wyświetlają w kolejności p1=nazwa1 p2 = nazwa2 p3=nazwa3

Zapytanie jest w formie

set optimizer_switch='semijoin=off';

SELECT * FROM nazwabazy.t1 WHERE nr IN ( SELECT COLUMN_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA='nazwabazy'
AND TABLE_NAME = 't2' AND COLUMN_NAME LIKE 'p%')

ps. bez tego optimizera w ogóle nie działa.

Baza:

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET NAMES utf8 */;
/*!50503 SET NAMES utf8mb4 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

CREATE TABLE IF NOT EXISTS `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `nr` varchar(50) DEFAULT NULL,
  `nazwa` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin2;

DELETE FROM `t1`;
/*!40000 ALTER TABLE `t1` DISABLE KEYS */;
INSERT INTO `t1` (`id`, `nr`, `nazwa`) VALUES
	(1, 'p1', 'nazwa1'),
	(2, 'p2', 'nazwa2'),
	(3, 'p3', 'nazwa3');
/*!40000 ALTER TABLE `t1` ENABLE KEYS */;

CREATE TABLE IF NOT EXISTS `t2` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `p2` varchar(50) NOT NULL DEFAULT '0',
  `p3` varchar(50) NOT NULL DEFAULT '0',
  `p1` varchar(50) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin2;

DELETE FROM `t2`;
/*!40000 ALTER TABLE `t2` DISABLE KEYS */;
/*!40000 ALTER TABLE `t2` ENABLE KEYS */;

/*!40101 SET SQL_MODE=IFNULL(@OLD_SQL_MODE, '') */;
/*!40014 SET FOREIGN_KEY_CHECKS=IFNULL(@OLD_FOREIGN_KEY_CHECKS, 1) */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40111 SET SQL_NOTES=IFNULL(@OLD_SQL_NOTES, 1) */;

test1.png

1 użytkowników online, w tym zalogowanych: 0, gości: 1