SQL-Statement MySQLSyntaxErrorException

Ste3et_C0st

Mitglied
Leider stehen ich und die Datenbankverarbeitung auf Kriegsfuß ich weiß nicht warum aber meine query funktionieren meist nicht beim ersten versuch villeicht fehlt einfach die Erfahrung. Ich möchte für meine Projekt etwas in eine Datenbank schreiben die Datenbank liegt auf meinem Webspace den ich bei qLoc beziehe. Ich möchte gerne nur 1000 Rows pro Column haben sprich wird der Eintrag 1001 eingetragen soll Rows 1 gelöscht werden mein SQL query an sich funktioniert bereits:

Code:
INSERT INTO `GRASS_BLOCK`(purchasePrice,retailPrice,amount) VALUES (300,500,600);
SELECT @lastid := max(id) from `GRASS_BLOCK`;
DELETE from `GRASS_BLOCK` WHERE id < (@lastid - 1000)

Ich hab das über phpMyAdmin getestet da geht das Einwand frei, da ich ein ziemlich fauler Mensch bin habe ich mir eine eigene API gepastelt welche mir über Java verschiedene SQL Aktionen ausführt die API greift auf die HikariCP zu und das async so sieht mein Java Code aus:

Java:
    public void marktAction(){
        if(task == null) {
            task = Bukkit.getScheduler().runTaskLaterAsynchronously(DiceItemMarkt.getInstance(), () ->{
                save();
                String query = "INSERT INTO `GRASS_BLOCK`(purchasePrice,retailPrice,amount) VALUES (300,500,600);" +
                        "SELECT @lastid := max(id) from `GRASS_BLOCK`; " +
                        "DELETE from `GRASS_BLOCK` WHERE id < (@lastid - 1000)";
                DiceServerAPI.getInstance().getDatabaseAPI().getServerWebsite().callbackUpdate(query, new CallbackStatement<Statement, SQLException>() {
                    @Override
                    public void onResult(Statement statment, SQLException arg1) {
                        if(arg1 != null) {
                            arg1.printStackTrace();
                        }else {
                           
                        }
                    }
                });
               
            },20*5);
            setAction(SQLAction.UPDATE);
        }
        caclulateNewPrice(getAmount());
        DiceItemMarkt.getInstance().updateAllGui();
    }

Nun erhalte ich folgende Exception in der Console

Code:
29.08 17:13:42 [Server] WARN com.mysql.jdbc.exceptions.jdbc4.MySQLSyntaxErrorException: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'SELECT @lastid := max(id) from `GRASS_BLOCK`; DELETE from `GRASS_BLOCK` WHERE id' at line 1
29.08 17:13:42 [Server] WARN at sun.reflect.NativeConstructorAccessorImpl.newInstance0(Native Method)
29.08 17:13:42 [Server] WARN at sun.reflect.NativeConstructorAccessorImpl.newInstance(NativeConstructorAccessorImpl.java:62)
29.08 17:13:42 [Server] WARN at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
29.08 17:13:42 [Server] WARN at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.Util.getInstance(Util.java:408)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:944)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
29.08 17:13:42 [Server] WARN at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
29.08 17:13:42 [Server] WARN at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)
29.08 17:13:42 [Server] WARN at com.zaxxer.hikari.pool.HikariProxyStatement.executeUpdate(HikariProxyStatement.java)
29.08 17:13:42 [Server] WARN at de.Ste3et_C0st.Dicecraft.DatabaseAPI.DatabaseObj.lambda$callbackUpdate$0(DatabaseObj.java:50)
29.08 17:13:42 [Server] WARN at org.bukkit.craftbukkit.v1_13_R1.scheduler.CraftTask.run(CraftTask.java:76)
29.08 17:13:42 [Server] WARN at org.bukkit.craftbukkit.v1_13_R1.scheduler.CraftAsyncTask.run(CraftAsyncTask.java:52)
29.08 17:13:42 [Server] WARN at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
29.08 17:13:42 [Server] WARN at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)

Mir ist das nun schon öfters aufgefallen das SQL querys die anscheinend funktionieren sollten über Java anscheinend nicht funktionieren hab ich 2 Vermutungen entweder die mySQL Schnittstelle zu externen Diensten ist veraltet oder ich bin doof ! Meine API funktioniert sobalt ich nur den INSERT ausführe !
 

Meniskusschaden

Top Contributor
Das ist eben nicht nur ein Query, sondern es sind drei SQL-Anweisungen. Mit einem Aufruf von Statement.executeUpdate() kann man aber nur eine ausführen (INSERT, UPDATE oder DELETE). Wenn du sie im Block ausführen willst, könntest du stattdessen Statement.executeBatch() nutzen. Da kann man allerdings auch keine SELECT-Anweisung verwenden. Die ist aber nicht zwingend nötig, weil du sie mit etwas Fummelei als Sub-Select in deine DELETE-Anweisung verschieben und die beiden dann verbliebenen Anweisungen mittels Statement.executeBatch() ausführen könntest.

Alternativ könntest du natürlich auch eine SQL-Prozedur programmieren und von deinem Java-Programm aufrufen lassen.

Oder du schickst mit zwei Anfragen ein INSERT und ein DELETE an die Datenbank.
 
X

Xyz1

Gast
Bitte mach das so, aber füge vielleicht eine id hinzu:
Java:
public class STest {
    static Connection connection;
    static Statement sta;

    public static void main(String[] args) throws SQLException {

        sta.execute("CREATE TABLE TEST (Text text);");
        for (int i = 0; i < 1010; i++) {
            instertAll(new String[]{"" + i});
        }
    }

    static void instertAll(String[] array) throws SQLException {
        for (String s : array) {
            sta.executeUpdate("INSERT INTO TEST VALUES ('" + s + "');");
        }
        deleteSome();
    }

    static void deleteSome() throws SQLException {
        int i = sta.executeQuery("SELECT COUNT(Text) FROM TEST;").getInt(1);
        if (i > 1000) {
            ResultSet r = sta.executeQuery("SELECT * FROM TEST LIMIT " + (i - 1000) + ";");
            while (r.next()) {
                sta.executeUpdate("DELETE FROM TEST WHERE Text='" + r.getString(1) + "';");
            }
        }
        printSome();
    }

    static void printSome() throws SQLException {
        int i = sta.executeQuery("SELECT COUNT(Text) FROM TEST;").getInt(1);
        ResultSet r = sta.executeQuery("SELECT * FROM TEST LIMIT 2;");
        while (r.next()) {
            System.out.println("r.getString(1) = " + r.getString(1));
        }
        System.out.println(".");
        System.out.println(".");
        r = sta.executeQuery("SELECT * FROM TEST LIMIT 2 OFFSET " + (i - 2) + ";");
        while (r.next()) {
            System.out.println("r.getString(1) = " + r.getString(1));
        }
        System.out.println("");
    }
}

...
Code:
r.getString(1) = 9
r.getString(1) = 10
.
.
r.getString(1) = 1007
r.getString(1) = 1008

r.getString(1) = 10
r.getString(1) = 11
.
.
r.getString(1) = 1008
r.getString(1) = 1009

Bearbeitung , instertAll soll eigentlich insertAll lauten.
 

Ste3et_C0st

Mitglied
Das ist eben nicht nur ein Query, sondern es sind drei SQL-Anweisungen. Mit einem Aufruf von Statement.executeUpdate() kann man aber nur eine ausführen (INSERT, UPDATE oder DELETE). Wenn du sie im Block ausführen willst, könntest du stattdessen Statement.executeBatch() nutzen. Da kann man allerdings auch keine SELECT-Anweisung verwenden. Die ist aber nicht zwingend nötig, weil du sie mit etwas Fummelei als Sub-Select in deine DELETE-Anweisung verschieben und die beiden dann verbliebenen Anweisungen mittels Statement.executeBatch() ausführen könntest.

Alternativ könntest du natürlich auch eine SQL-Prozedur programmieren und von deinem Java-Programm aufrufen lassen.

Oder du schickst mit zwei Anfragen ein INSERT und ein DELETE an die Datenbank.

Bitte mach das so, aber füge vielleicht eine id hinzu:
Java:
public class STest {
    static Connection connection;
    static Statement sta;

    public static void main(String[] args) throws SQLException {

        sta.execute("CREATE TABLE TEST (Text text);");
        for (int i = 0; i < 1010; i++) {
            instertAll(new String[]{"" + i});
        }
    }

    static void instertAll(String[] array) throws SQLException {
        for (String s : array) {
            sta.executeUpdate("INSERT INTO TEST VALUES ('" + s + "');");
        }
        deleteSome();
    }

    static void deleteSome() throws SQLException {
        int i = sta.executeQuery("SELECT COUNT(Text) FROM TEST;").getInt(1);
        if (i > 1000) {
            ResultSet r = sta.executeQuery("SELECT * FROM TEST LIMIT " + (i - 1000) + ";");
            while (r.next()) {
                sta.executeUpdate("DELETE FROM TEST WHERE Text='" + r.getString(1) + "';");
            }
        }
        printSome();
    }

    static void printSome() throws SQLException {
        int i = sta.executeQuery("SELECT COUNT(Text) FROM TEST;").getInt(1);
        ResultSet r = sta.executeQuery("SELECT * FROM TEST LIMIT 2;");
        while (r.next()) {
            System.out.println("r.getString(1) = " + r.getString(1));
        }
        System.out.println(".");
        System.out.println(".");
        r = sta.executeQuery("SELECT * FROM TEST LIMIT 2 OFFSET " + (i - 2) + ";");
        while (r.next()) {
            System.out.println("r.getString(1) = " + r.getString(1));
        }
        System.out.println("");
    }
}

...
Code:
r.getString(1) = 9
r.getString(1) = 10
.
.
r.getString(1) = 1007
r.getString(1) = 1008

r.getString(1) = 10
r.getString(1) = 11
.
.
r.getString(1) = 1008
r.getString(1) = 1009

Bearbeitung , instertAll soll eigentlich insertAll lauten.

Vielen dank daran hab ich gar nicht gedacht das die mySQL Anweisungen nicht Kombiniert werden können ich hab das nun so gelöst:

Java:
    public void marktAction(){
        if(task == null) {
            final double d = getPurchasePrice();
            task = Bukkit.getScheduler().runTaskLaterAsynchronously(DiceItemMarkt.getInstance(), () ->{
                double a = getPurchasePrice();
                double k = (1 - a / d) * 100.0;
                double r = Math.round(k * 1000.0 ) / 1000.0;
                save();
                String material = getMaterial().name();
                String action = r == 0 ? "&#8212;" : r > 0 ? "&#11206;" : "&#11205;";
                String query = "INSERT INTO `"+material+"`(purchasePrice,retailPrice,amount,action,priceChange) VALUES ("+roundPurchasePrice()+", "+roundRetailPrice()+", "+getAmount()+", '"+action+"', "+Math.abs(r)+");";
                DiceServerAPI.getInstance().getDatabaseAPI().getServerWebsite().callbackUpdate(query, new CallbackStatementID<PreparedStatement, SQLException, Integer>() {
                    @Override
                    public void onResult(PreparedStatement statment, SQLException ex, Integer i) {
                        if(ex == null) {
                            try {
                                ResultSet rs = statment.getGeneratedKeys();
                                if (rs.next()) {
                                    if(rs.getInt(1) > 0) {
                                        PreparedStatement statemant = statment.getConnection().prepareStatement("DELETE FROM `"+material+"` WHERE `id` < " + (j - 500) + ";");
                                        statemant.execute();
                                    }
                                 }
                                statment.close();
                            } catch (SQLException e) {
                                e.printStackTrace();
                            }
                        }else {
                            ex.printStackTrace();
                        }
                    }
                });
            },20*5);
            setAction(SQLAction.UPDATE);
        }
        caclulateNewPrice(getAmount());
        DiceItemMarkt.getInstance().updateAllGui();
    }

Funktioniert auch soweit er schreibt alle 5 Sekunden die Daten falls vorhanden in die Datenbank. Eine weitere frage wäre ich zeige die Daten Online auf der Website an. Die Daten werden alle in eine Seperate Tabelle geschrieben somit umfast die Datenbank insgesamt 786 Tabellen mit bis zu 500 Datensätzen soweit so gut diese Daten sollen nun auf der Website angezeigt werden ich weiß selber das die Seite dadurch an Geschwindigkeit einbüßt https://dicecraft.de/forum/wcf/index.php?itemkurs/ ist aber ne schöne spielerei leider muss ich damit ich alle daten von der Datenbank laden möchte 786 querys schicken da ja jedes Material in einer eigenen Tabelle steht.

PHP:
                $db = new Database;
                if($db->connect_error > 0){die('Unable to connect to database [' . $db->connect_error . ']');}
               
                foreach ($json_a as $person_name => $person_a) {
                    $str = $person_name;
                    $lang = $person_a['lang'];
                    $texture = $person_a['texture'].".png";
                    $sql = "SELECT purchasePrice,amount,priceChange,action FROM `".$str. "` ORDER BY time DESC LIMIT 1;";
                    $price = 0;
                    $priceChange = 0;
                    $amount = 0;
                    $change = "&#8212;";
                    $result = $db->query($sql);
                    $class = "neutral";
                    while($row = $result->fetch_assoc()) {
                        $price = $row["purchasePrice"];
                        $amount = $row["amount"];
                        $priceChange = $row["priceChange"];
                        $change = $row["action"];
                        if($change != "&#11206;"){
                            $class = "positiv";
                        }else{
                            $class = "negativ";
                        }
                    }
HIER KOMMT NOCH HTML ...
$int++;
}

leider weiß ich nicht ob man diese Funktion in php besser schreiben könnte das große Problem ist das jedes Material einzeln in einer Tabelle liegt das war die einzige Möglichkeit die mir eingefallen ist. Ich möchte halt gerne einen graphen anzeigen der das Darstellt:

https://dicecraft.de/forum/wcf/index.php?itemkurs/&item=GRASS_BLOCK sollte jemand eine bessere Idee haben dann gerne her damit :D
 
X

Xyz1

Gast
Vielen dank daran hab ich gar nicht gedacht das die mySQL Anweisungen nicht Kombiniert werden können ich hab das nun so gelöst:
Ja schaut ganz gut, aber eine id Folge kann auch Lücken aufweisen,
vielleicht alle beziehen in eine Liste und sortieren und 'deleten'.

und php ist jetzt nicht JAVA. ;)

bis dann
 

Ste3et_C0st

Mitglied
Die Überlegung war dabei das jedes material um die 1000 Datensätze speichern kann dann würde ich auf 786.000 Datensätze kommen ich weiß nicht was performanter ist direkt nach der Tabele in der Datenbank suchen und von dort die Daten zu beziehen oder alle daten in einer Tabelle abspeichern und der Query sucht in den 786.000 Datensätzen nach den Richtigen :rolleyes:
 

Meniskusschaden

Top Contributor
Du kannst ja ggf. Indizes anlegen, um die Zugriffe zu beschleunigen. Für diesen Fall wäre vermutlich ein zusammengesetzter Index über Material-ID und Zeitstempel sinnvoll.
 

mihe7

Top Contributor
Leider stehen ich und die Datenbankverarbeitung auf Kriegsfuß ich weiß nicht warum
Das liegt an der fehlenden Interpunktion... ;)

Die Überlegung war dabei das jedes material um die 1000 Datensätze speichern kann dann würde ich auf 786.000 Datensätze
Im Gegensatz zu 786 SELECTs langweilt sich die DB bei 1 Mio. Sätzen, insbesondere wenn ein Index verwendet wird (s. Beitrag von @Meniskusschaden).
 

Ste3et_C0st

Mitglied
Das liegt an der fehlenden Interpunktion... ;)


Im Gegensatz zu 786 SELECTs langweilt sich die DB bei 1 Mio. Sätzen, insbesondere wenn ein Index verwendet wird (s. Beitrag von @Meniskusschaden).

Naja lassen wir mal die Interpunktion Geschichte sein das ist ein anderes Thema. Ich hab das nun mit einer mischform gelöst, zum einen hab ich die 786 Tabellen beibehalten und eine neue Tabelle angelegt welche mir nur die nötigsten Daten speichert.

Code:
CREATE TABLE `quickINFO` (
  `material` varchar(255) NOT NULL,
  `action` varchar(8) NOT NULL DEFAULT '&#8212;',
  `priceChange` double NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `price` double NOT NULL DEFAULT '0',
  `amount` int(11) NOT NULL DEFAULT '0',
  `name` varchar(255) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
Dadurch spar ich mir ganze 785 SELECTs ich Update einfach immer die Einträge über REPLACE INTO. Das passt mir sehr da ich auf der Website immer die letzten 10 Einträge anzeigen möchte warum ich da vorher nicht drauf kam ¯\_(ツ)_/¯ ich bedanke mich für eure hilfe.
 

Thallius

Top Contributor
Damit schießt du dir irgendwann böse ins Bein...

Sowas macht man einfach nicht. Nie niemals niocht hält man die gleichen Daten mehrfach gespeichert....
 

Ste3et_C0st

Mitglied
Hab nun nochmals die komplette Datenbank zurückgesetzt und von vorne angefangen und zwar erstell ich nun so eine Tabelle:

Code:
CREATE TABLE IF NOT EXISTS `itemKurs` (
        `material` VARCHAR(255) NOT NULL,
        `materialID` MEDIUMINT NOT NULL AUTO_INCREMENT,
        `purchasePrice` DOUBLE NOT NULL DEFAULT 0,
        `retailPrice` DOUBLE NOT NULL DEFAULT 0,
        `amount` int NOT NULL DEFAULT 0,
        `action` VARCHAR(8) NOT NULL DEFAULT '&#8212;',
        `priceChange` DOUBLE NOT NULL DEFAULT 0,
        `lang` VARCHAR(255) NOT NULL,
        `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (material,materialID)
) ENGINE=MyISAM;

Ich bilde nun einen seperaten PRIMARY KEY für jedes material einzeln somit kann ich alles in eine Tabelle schreiben. Ich hoffe das war das was @Meniskusschaden meinte. Ich hab nur noch eine zentrale Tabelle in der die Werte eingefügt werden und ausgelesen werden. Ich hoffe das stimmt soweit. Eine frage hätte ich noch da ich gleich in die Arbeit musss, kann ich nun via executeBatch gleichzeitig einen INSERT machen und den LAST_INSERT_ID() - 1000 Löschen ? Ich möchte halt nur immer 1000 Transaktionen speichern pro material geht das ?
 

Meniskusschaden

Top Contributor
Ich bilde nun einen seperaten PRIMARY KEY für jedes material einzeln somit kann ich alles in eine Tabelle schreiben. Ich hoffe das war das was @Meniskusschaden meinte.
Nein, ich meinte nicht den Primary Key, sondern einen zusätzlichen Index über Material-ID und Zeitstempel, damit für ein gegebenes Material performant auf die neuesten Datensätze zugegriffen werden kann. Ich würde aber erst einmal austesten, wie schnell oder langsam es ohne den zusätzlichen Index überhaupt ist. Dann kannst du auch besser einordnen, ob der Index überhaupt etwas gebracht hat. Für den Primärschlüssel wäre die id ein Kandidat .
kann ich nun via executeBatch gleichzeitig einen INSERT machen und den LAST_INSERT_ID() - 1000 Löschen ?
Ja. Insert und Delete kann man mit einem gemeinsamen executeBatch-Aufruf ausführen. Man kann es aber nicht mit Select mischen.
 

Meniskusschaden

Top Contributor
Ich bemerke jetzt erst, dass die Material-ID in der neuen Tabelle offenbar nicht die Identifikation des Materials darstellen soll, sondern die Identifikation des Preis-Datensatzes. Deshalb könnte man hier
Nein, ich meinte nicht den Primary Key, sondern einen zusätzlichen Index über Material-ID und Zeitstempel, damit für ein gegebenes Material performant auf die neuesten Datensätze zugegriffen werden kann
doch den Index aus Post #14 des TE verwenden. Allerdings würde ich ihn dennoch nicht als Primary-Key verwenden. Ich würde die Spalte auch lieber anders benennen.

LAST_INSERT_ID() - 1000 Löschen ?
Die zu löschenden Datensätze können jetzt natürlich nicht mehr über Differenzbildung der IDs ermittelt werden. Da wären dann ja auch die falschen Materialien enthalten. Das müsste in dem vorgeschlagenen Trigger von @tommysenf deshalb auch angepasst werden.
 

Ste3et_C0st

Mitglied
Villeicht hab ich das nicht ganz so super rübergebracht. Ich möchte von meinem Server in die MySQL Tabelle die Daten Schreiben, es wird immer in die Datenbank geschrieben sobald sich der Preis eines Materials ändert mit einem Delay X.
Ich habe insgesamt 786 Materialien somit wollte ich das Material als Primary Key setzen, da ich es vernünftig machen möchte, wollte ich nun die Datenbank euren Vorschlägen dementsprechend anpassen.
Meine Idee war nun folgendes damit ich alles in eine Tabelle schreiben kann brauch ich ja zwingen nen Primary Key meine Überlegung war nun jeden Material eine eigene Id zu geben.

material | materialId | purchasePrice ...
STONE | 1 | 100.6
STONE | 2 | 101.0
STONE | 3 | 101.5
GRASS | 1 | 50
GRASS | 2 | 70

So ist momentan die Tabelle aufgebaut.
0CGg4Op.jpg

rFIqIdM.jpg


Ne normale Datenbank Struktur bekomm ich ja hin, da ist nichts dabei aber bei diesem "speziellen" Fall vergeht mir echt der Spaß. Ich möchte nunmal maximal 1000 Transaktionen eines Materials anzeigen, ich würde es echt gerne vernünftig machen. Den perfekten Weg hab ich leider noch nicht gefunden.
 

mihe7

Top Contributor
Als erstes würde ich das Material mal separat darstellen. Die Anforderung, nur 1000 Sätze je Material zu speichern, lässt sich auf verschiedene Arten umsetzen. Hier mal eine Möglichkeit, die für Kurse eine bzgl. des Materials eindeutige ID verwendet:
SQL:
CREATE TABLE IF NOT EXISTS material (
    id BIGINT NOT NULL AUTO_INCREMENT,
    description VARCHAR(255) NOT NULL,
    lastKursId BIGINT NOT NULL DEFAULT 0,
    PRIMARY KEY (id)
);

CREATE TABLE IF NOT EXISTS `itemKurs` (
        `materialID` BIGINT NOT NULL,
        `kursId` BIGINT NOT NULL,
        `purchasePrice` DOUBLE NOT NULL DEFAULT 0,
        `retailPrice` DOUBLE NOT NULL DEFAULT 0,
        `amount` int NOT NULL DEFAULT 0,
        `action` VARCHAR(8) NOT NULL DEFAULT '&#8212;',
        `priceChange` DOUBLE NOT NULL DEFAULT 0,
        `lang` VARCHAR(255) NOT NULL,
        `time` TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
         PRIMARY KEY (materialId, kursId),
         FOREIGN KEY (materialId) REFERENCES material(id)
) ENGINE=MyISAM;
CREATE INDEX ix_itemKurs_time ON itemKurs(time);
Dann kann so eingefügt werden:
SQL:
SELECT @kid := lastKursId+1  FROM material WHERE id=@mid FOR UPDATE;
INSERT INTO itemKurs(materialId, kursId, ...) VALUES (@mid, @kid, ...);
DELETE FROM itemKurs WHERE materialID = @mid AND kursId < @kid - 1000;
UPDATE material SET lastKursId = @kid WHERE id = @mid;
Je nach Hintergrund der Forderung nach 1000 Einträge je Material kann man die veralteten Einträge auch verzögert (z. B. einmal je Tag) entfernen. Eine andere Überlegung wäre, über die Zeit zu gehen (alles, was älter als 24 h ist etc.).

Die jüngsten zehn Einträge (je nach Reihenfolge des Einfügens kann es genügen, nur über ID zu sortieren):
SQL:
SELECT * FROM itemKurs ORDER BY time DESC LIMIT 10;
 

Thallius

Top Contributor
Zunächst mal nur so als Tipp: speichere den Preis als int nicht als Double. Das ist schneller und erzeugt keine Rundungsfehler wenn du später mal damit rechnen willst.

Zweitens: wozu denn die Material Id? Du hast doch das Datum. Ich würde mich hör auch nicht auf die 1000 festlegen sondern, wie Mihe schon schrieb, auf einen Zeitraum. Ich weis nicht in welchen Intervallen deine Datensätze kommen aber wenn z.b. Jede Sekunde einer kommenwürde, dann würde ich einfach alle Datensätze löschen die älter als eine stunde sind. Damit hast du dann immer 3600 Datensätze vorliegen, was dann etwa 3mio Rows in der DB wären. Ob das performanter genug ist hängt halt auch von der Hardware ab. Das Must du auf dem Server ausprobieren. Wenn es zu langsam wird setzt du das Intervall eben runter.

Und selbst wenn du nur 1000 Datensätze haben wilst zum verwenden, Damm kannst du beim Select immer noch mit Limit 1000 arbeiten.

Gruß

Claus
 

Ste3et_C0st

Mitglied
Zunächst mal nur so als Tipp: speichere den Preis als int nicht als Double. Das ist schneller und erzeugt keine Rundungsfehler wenn du später mal damit rechnen willst.

Zweitens: wozu denn die Material Id? Du hast doch das Datum. Ich würde mich hör auch nicht auf die 1000 festlegen sondern, wie Mihe schon schrieb, auf einen Zeitraum. Ich weis nicht in welchen Intervallen deine Datensätze kommen aber wenn z.b. Jede Sekunde einer kommenwürde, dann würde ich einfach alle Datensätze löschen die älter als eine stunde sind. Damit hast du dann immer 3600 Datensätze vorliegen, was dann etwa 3mio Rows in der DB wären. Ob das performanter genug ist hängt halt auch von der Hardware ab. Das Must du auf dem Server ausprobieren. Wenn es zu langsam wird setzt du das Intervall eben runter.

Und selbst wenn du nur 1000 Datensätze haben wilst zum verwenden, Damm kannst du beim Select immer noch mit Limit 1000 arbeiten.

Gruß

Claus

Zu eins die werte die ich dort reinschreibe sind alles Preis angaben, ich muss diese in ein Double Speichern da ich nicht möchte das die Materialien Ingame 6.51 € Kosten und auf der Website ein 6€ oder 7€ Betrag steht.

Zu zwei es ist nicht so das jede Sekunde ein Datensatz kommt die kommen eher sporadisch, nicht jedes Material bekommt sofort einen Datensatz da ich das ganze in einen Graphen darstellen möchte nützt es mir absolut garnichts die Datensätze über den Zeitraum zu löschen, da es sonst einige Graphen gibt bei denen absolut keine Daten vorhanden sind.
 

Thallius

Top Contributor
Wie wäre es wenn du die Preise einfach in Cent speicherst? Schwupp schon hast du keine Double mehr...
Sorry aber ich bin doch nicht blöd....
 
X

Xyz1

Gast
https://dev.mysql.com/doc/mysql-reslimits-excerpt/5.7/en/table-size-limit.html

Das ist auch interessant
  1. Maximum Number Of Rows In A Table

    The theoretical maximum number of rows in a table is 264 (18446744073709551616 or about 1.8e+19). This limit is unreachable since the maximum database size of 140 terabytes will be reached first. A 140 terabytes database can hold no more than approximately 1e+13 rows, and then only if there are no indices and if each row contains very little data.
https://www.sqlite.org/limits.html
 

Ste3et_C0st

Mitglied
Hab es nun so aufgebaut wie @mihe7 meinte

SQL:
CREATE TABLE `material` (
  `id` int(11) NOT NULL,
  `material` varchar(255) NOT NULL,
  `action` varchar(8) NOT NULL DEFAULT '&#8212;',
  `priceChange` double NOT NULL DEFAULT '0',
  `lang` varchar(255) NOT NULL,
  `lastCourseID` int(11) NOT NULL DEFAULT '0'
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `priceCourse` (
  `id` int(11) NOT NULL,
  `materialId` int(11) NOT NULL,
  `purchasePrice` double NOT NULL DEFAULT '0',
  `retailPrice` double NOT NULL DEFAULT '0',
  `amount` int(11) NOT NULL DEFAULT '0',
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TRIGGER `updateMaterial` AFTER INSERT ON `priceCourse`FOR EACH ROW UPDATE `material` SET lastCourseID = NEW.ID WHERE id = NEW.materialId

Ich hab nun soweit verstanden wie man das am besten macht und zwar hab ich nun 2 Tabellen eine mit alle Tansaktionen und eine mit den Material Definitionen.
Den trigger updateMaterial hab ich mit eingebaut damit ich das Feld lastCourseID richtig beschreiben kann.
Das mit dem Löschen ist noch so ne sache das bekomme ich noch nicht gebacken. Kann ich nun einen 2. Trigger erstellen der mir den 1. Eintrag von 1001 Einträgen für sagen wir mal die Material ID 1 löscht ? Kann man das irgendwie über SQL Ereignisse machen ?

So zeige ich mir immer die Letzten 10 änderungen an:

SQL:
SELECT material.material,material.lang,material.action,priceCourse.purchasePrice,priceCourse.time 
FROM `material` INNER JOIN priceCourse ON priceCourse.id=material.lastCourseID ORDER BY time LIMIT 10;
 
Zuletzt bearbeitet:

mrBrown

Super-Moderator
Mitarbeiter
Zu zwei es ist nicht so das jede Sekunde ein Datensatz kommt die kommen eher sporadisch, nicht jedes Material bekommt sofort einen Datensatz da ich das ganze in einen Graphen darstellen möchte nützt es mir absolut garnichts die Datensätze über den Zeitraum zu löschen, da es sonst einige Graphen gibt bei denen absolut keine Daten vorhanden sind.
Ein ähnliches Problem hast du, wenn du immer 1_000 behältst.
In dem einem Graphen reichen die 1_000 Werte dann ab jetzt 15min zurück, im anderen Graphen gibt es den neusten Wert vor 3 Stunden, dafür reichen sie 3 Wochen zurück.
 

Meniskusschaden

Top Contributor
Kann ich nun einen 2. Trigger erstellen der mir den 1. Eintrag von 1001 Einträgen für sagen wir mal die Material ID 1 löscht ?
Das Löschen könnte im Trigger schwierig werden. Ich weiß nicht, ob es da überhaupt möglich ist, Datensätze derselben Tabelle zu löschen.
Kann man das irgendwie über SQL Ereignisse machen ?
Den Weg finde ich besser. Da müsste es genügen, mit CREATE EVENT einen geeigneten Löschbefehl (oder vielleicht lieber eine Prozedur) mit dem gewünschten Intervall einzuplanen. Wenn ich mich richtig erinnere, ist der Scheduler aber in einer Standardinstallation nicht aktiviert und muss erst eingeschaltet werden.
In dem einem Graphen reichen die 1_000 Werte dann ab jetzt 15min zurück, im anderen Graphen gibt es den neusten Wert vor 3 Stunden, dafür reichen sie 3 Wochen zurück.
Das sehe ich auch so. Es ist eigentlich nicht logisch, dass unabhängig von der Häufigkeit, die ersten 1000 Werte so wichtig sind, dass man sie sehen muss und ab 1001 so unwichtig, dass man sie löschen kann.
 

Ste3et_C0st

Mitglied
Naja das mit den 1000 ist das letzte Problem vielleicht sollte ich mir es einfach machen und die Daten der Letzten Woche einmal pro tag einfach löschen, hab langsam keine Lust mehr.
 

mihe7

Top Contributor
Hab es nun so aufgebaut wie @mihe7 meinte
Da bin ich mir nicht so sicher :)

Wenn ich mir Deinen JOIN ansehe, dann gehst Du davon aus, dass lastCourseId eindeutig ist. Das ist nicht der Fall. Vielmehr soll lastCourseId nur zusammen mit materialId eindeutig sein (sprich: die Preisänderungen werde je Material durchnummeriert 1, 2, 3, ...).

Was das Löschen im Trigger betrifft, so müsstest Du das ausprobieren (weiß ich auswendig auch nicht, ob man im Trigger von der selben Tabelle löschen kann):
Code:
DELIMITER //
CREATE TRIGGER `updateMaterial` AFTER INSERT ON `priceCourse`FOR EACH ROW
BEGIN
 UPDATE `material` SET lastCourseID = NEW.ID WHERE id = NEW.materialId;
 DELETE FROM priceCourse WHERE materialId = NEW.materialId AND id = NEW.id - 1000;
END;//
DELIMITER ;

Nachtrag: so was mache ich normalerweise in der Anwendung, die die Datensätze einfügt und habe daher keinen Bedarf für einen Trigger.
 
X

Xyz1

Gast
@mihe7 sql geht auch ;) ;)

SQL:
DELIMITER //
CREATE TRIGGER `updateMaterial` AFTER INSERT ON `priceCourse`FOR EACH ROW
BEGIN
 UPDATE `material` SET lastCourseID = NEW.ID WHERE id = NEW.materialId;
 DELETE FROM priceCourse WHERE materialId = NEW.materialId AND id = NEW.id - 1000;
END;//
DELIMITER ;

sieht doch hübsch aus
 

Ste3et_C0st

Mitglied
Na das hatte ich auch schon probiert sorry das ich das nicht erwähnt hatte da wirft der Server nen schönen error aus

Code:
16.09 01:29:43 [Server] WARN java.sql.SQLException: Can't update table 'priceCourse' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:965)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3976)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.MysqlIO.checkErrorPacket(MysqlIO.java:3912)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2530)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2683)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2482)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.StatementImpl.executeUpdateInternal(StatementImpl.java:1552)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.StatementImpl.executeLargeUpdate(StatementImpl.java:2607)
16.09 01:29:43 [Server] WARN at com.mysql.jdbc.StatementImpl.executeUpdate(StatementImpl.java:1480)
16.09 01:29:43 [Server] WARN at com.zaxxer.hikari.pool.ProxyStatement.executeUpdate(ProxyStatement.java:120)
16.09 01:29:43 [Server] WARN at com.zaxxer.hikari.pool.HikariProxyPreparedStatement.executeUpdate(HikariProxyPreparedStatement.java)
16.09 01:29:46 [Multicraft] Skipped 24 lines due to rate limit (30/s)

@mihe7 ich dachte in der lastCourseID wird immer die letzte Kursänderungs id hinterlegt für das Material.
Oder hab ich da was falsch verstanden ?
 

mihe7

Top Contributor
da wirft der Server nen schönen error aus
Fast befürchtet :) Du könntest allerdings einfach einen UPDATE-Trigger für material anlegen: "DELETE FROM priceCourse WHERE materialId=:NEW.ID AND id = NEW.lastCourseId-1000"

ich dachte in der lastCourseID wird immer die letzte Kursänderungs id hinterlegt für das Material.
Ja, (aber) für jedes Material separat. D. h. jedes Material (materialId) bildet eine Gruppe von Kursänderungen und innerhalb der Gruppe wird unabhängig von anderen Gruppen hochgezählt. Es gibt also in der Tabelle priceCourse ggf. mehrere Datensätze mit gleicher Kursänderungs-ID. Eindeutig wird es erst, wenn die materialId hinzugenommen wird.
 

Neue Themen


Oben