A więc, muszę skopiować około 7 mln rekordów w obrębie tej samej bazy danych, z jednej tabeli do drugiej. Kopiuję je poniższym zapytaniem. Robię to po 100k rekordów jednorazowo, by nie zajechać serwera. Problem zaczyna się po którymś razie, gdy kopiuję kolejną paczkę, nagle w tabeli nowej jest 586k rekordów, zamiast 600k. I tak za każdym razem. Czasami trochę wcześniej się zepsuje, czasami trochę później.

Próbowałem też zmieniać ilość rekordów kopiowanych na raz, ale to nic nie dawało. Pierwsze kilka paczek daje poprawne rezultaty - okrągłą ilość rekordów, ale za którymś razem nagle jest na przykład 586329 zamiast okrągłe 600000.

Czy miał może ktoś z Was podobny problem?

Baza danych MySQL. Serwer dedykowany. Na serwerze jest wystarczająco miejsca na dane, ponieważ udało mi się skopiować ponad półtora miliona rekordów.

[SQL] pobierz, plaintext 
INSERT INTO table_new (SELECT * FROM table_old WHERE column1 != column2 LIMIT 0, 100000)
[SQL] pobierz, plaintext 

PS. Warunek w tym momencie jest konieczny, bo muszę skopiować tylko określone rekordy.

A jak wygląda struktura? Są tam jakieś unikalne pola? Spróbuj to kopiować z sortowaniem.

Tak, jest jedno unikalne pole.

Dzięki za podsunięcie pomysłu z sortowaniem. Zaraz sprawdzę.


EDIT 23:58

Niestety, dodałem sortowanie jak poniżej, ale nadal się psuje po kilku wykonaniach zapytania. Teraz dobrze wykonało się 5 zapytań, ale po szóstym mam 575219 rekordów w tabeli, zamiast 600k.

[SQL] pobierz, plaintext 
INSERT INTO table_new (SELECT * FROM table_old WHERE column1 != column2 ORDER BY id ASC LIMIT 0, 100000)
[SQL] pobierz, plaintext 

Spróbuj jeszcze zrobić INSERT IGNORE zamiast INSERT, a zamiast SELECT * wypisać wszystkie pola np. SELECT id, name itd.

Zerknij tutaj: http://derwiki.tumblr.com/post/24490758395...rows-into-mysql

Pytania zasadnicze:
1. Silnik tabel
2. Skąd wiesz, ile jest rekordów? Liczysz Count(*)?

Na czas importu usuń z tabeli, do której kopiujesz wszystkie klucze, indeksy itp.
Poza tym nie wiadomo, czy to INSERT źle dodaje, czy SELECT źle podaje.
A prawidłowa składnia INSERT to:
INSERT INTO nazwa_tabeli[kolumny,...] SELECT * | [kolumny, ...] FROM tabela ...
Bez żadnych nawiasów dookoła SELECT

Usuwanie kluczy nie koniecznie się opłaca, bo potem jak będzie musiał je założyć to będzie o wiele dłużej trwało.

My testowaliśmy na tabeli ~150GB ile trwa zmiana silnika z MyIsam na InnoDB. Rozważaliśmy różne opcje. Stanęło na tym że utworzenie bazy z kluczami i zrobienie insert select dało najlepsze efekty.

1. Dodałem listę kolumn i usunąłem nawiasy - niestety nie pomogło.
3. INSERT IGNORE również nic nie dało, zepsuło się na ponad 500k.
2. @Pyton_000 - Dziękuję za linka, ale niestety, nic nie rozumiem z tego tekstu. Nie mogę się doczytać w ogóle o co tak chodzi. Wiem, że miał kilka tabel, chciał je złączyć w jedną, ale nie rozumiem jak, czym, po co.

Silnik to InnoDB.

Zauważyłem, że za każdym razem jak już robi się ponad 500k rekordów w nowej tabeli to się psuje - za każdym razem. Ładnie dochodzi do 500k, ale jak wykonuję kolejny raz zapytanie to się już nie kopiuje równo 100k rekordów - tylko na przykład 55k, 64k, 59k. Trochę dziwne zachowanie.

Indeksy będę musiał później i tak usunąć bo te rzeczy wrócą potem do tabeli oryginalnej, tyle że ze zmienionymi danymi i zamienionymi wartościami kolumn, więc zdaję sobie sprawę z tego, że odbudowywanie indeksów będzie długo trwało.

A SELECT po tych 500k zwraca 100k czy te ~60k?

Doszedłem z zapytaniami aż do LIMIT 900000, 100000 i za każdym razem dostawałem równe 100k. Czyli SELECT działa poprawnie.

Przy InnoDB aby wiedzieć, ile jest rekordó, to trzeba je policzyć. Czyli zrobić Count(*). O to pytałem, a nie odpowiedziałeś...

mmmmmmm zadał bardzo istotne pytanie, ilość rekordów jaką widać bez policzenia dodatkowym zapytaniem w przypadku InnoDB czy ogólnie transakcyjnych silników może być tylko przybliżona, a nawet przy bezpośredniim liczeniu dotyczy wyłącznie tego samego poziomu izolacji co liczące zapytanie, bo w zależności od tego poziomu może widzieć np rekordy w trakcie wstawiania a przed wykonaniem commit, poza tym mogą w tym czasie być w trakcie wykonywania inne transakcje na innych poziomach izolacji, coś tam może potem robić rollback itd. Dlatego najprawdopodobniej wszystkie rekordy przeszły przez kopiowanie bez żadnego problemu, tylko nie widzisz tego dokładnie patrząc na przybliżoną ilość rekordów. Oczywiście pod warunkiem że źródłowa tabela jest sortowana (bo jeśli nie ma jawnie podanego ORDER BY to mysql myśli że kolejność rekordów nas nie interesuje i ma pełną dowolność, choć w większości przypadków przy braku zaawansowanych indeksów zwraca w kolejności klucza głównego, stąd ludzie czasem błędnie zakładają że istnieje domyślne sortowanie).

Mała uwaga @pmlr13 jeśli nie ma sortowania to dane są zwracane wg. kolejności dodania. Indeksy nie mają znaczenia.

O rany Pyton_000, przecież sprawdzenie tego to jest mniej niż minuta, mógłbyś choć tyle trudu sobie zadać...

[SQL] pobierz, plaintext 
CREATE TABLE `ordertest` (
 `id` int(11) NOT NULL AUTO_INCREMENT,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB;
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
INSERT INTO `ordertest`(id) VALUES(2);
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
INSERT INTO `ordertest`(id) VALUES(1);
[SQL] pobierz, plaintext 

[SQL] pobierz, plaintext 
SELECT id FROM ordertest;
[SQL] pobierz, plaintext 

MySQL nie daje gwarancji na kolejność zwracanych wyników bez zastosowania order by

Nie tylko mysql ale ogólnie standard. Tutaj przypadkowo w zapytaniu jest użyty index z PK i włącza się sortowanie po indeksie. Ale zrób coś takiego:

[SQL] pobierz, plaintext 
CREATE TABLE `ordertest` (
     `id` int(11) NOT NULL AUTO_INCREMENT,
     PRIMARY KEY (`id`)
    ) ENGINE=Memory;
 
    INSERT INTO `ordertest`(id) VALUES(2);
INSERT INTO `ordertest`(id) VALUES(1);
INSERT INTO `ordertest`(id) VALUES(3);
SELECT id FROM ordertest;
[SQL] pobierz, plaintext 

Liczyłem wiersze z tabeli źródłowej, i mam ich około 7 milionów. Po wykonaniu tych zapytań, gdy się popsuło również liczyłem COUNT(*) - tutaj pokazywało mi mniej niż 600k.

To co napisałem w poprzednim poście miało na celu tylko sprawdzenie, czy SELECT działa poprawnie, bo zwraca za każdym razem poprawną ilość rekordów. Wygląda na to, że INSERT nie do końca działa tak jak powinien. Na ten nowej tabeli nie są wykonywane żadne inne operacje, jest to świeża tabela, utworzona tylko dla tej operacji.

Debuguj.

Jak masz w komunikacie diagnostycznym jakieś warningi, w stylu: to wyświetl je za pomocą SHOW WARNINGS.

Czy tabele są takie same:

[SQL] pobierz, plaintext 
CREATE TABLE tabela_nowa LIKE tabela_stara
[SQL] pobierz, plaintext 

Sprawdź których rekordów brakuje (a niby powinny być), w stylu:

[SQL] pobierz, plaintext 
SELECT COUNT(id) FROM tabela_nowa WHERE id NOT IN (SELECT id FROM tabela_stara AND ... [dodatkowe warunki] )
[SQL] pobierz, plaintext 

Zamiast LIMIT spróbuj porcjować po ID (id<10000 itd).