CODEWITHSUNDEEP
mysqlsqlmysqlimport
danone
danone

Reputation: 158

Mysql update table by reading txt file?

/var/lib/mysql-files/myfile.txt file contain

23/08/2020 mycluster1 192.168.0.10 1515G 22G 1493G 2 15 17

I want to update servercheck table by reading /var/lib/mysql-files/myfile.txt I tried many combination but couldn't find proper solution I also search import command but couldn't find update

mysqlimport -u myuser -pxxXxX1xF databasename UPDATE 'servercheck' (`id`, `date`, `cluster`, `ip`, `totalsize`, `used`, `available`, `-10MBfiles`, `+10MBfiles`, `totalfiles`) VALUES LOAD_FILE('/var/lib/mysql-files/myfile.txt') Where ip='192.168.0.10';

How can I update this table ?

Thanks heap

Upvotes: 2

Views: 1170

Answers (2)

danone
danone

Reputation: 158

this is script if any one needed.

srvstat.txt

a=Australia b=04/09/2020 15:40 c=strg1-au.myserver.com d=192.168.0.15 e=/etc/mydata/ f=147G g=1G h=1% i= 147G j=99% k=0 l=0 m=0 n=OK o=OK p=32547320 KB r=27402 MB s=500G t=494G

and this php code reads and update table.

<?php
        $input = "/etc/system/srvstats/storage/srvstats.txt";
        $dbHost = "localhost";
        $dbUser = "myuser";
        $dbPass = "xxxxxxxxxxF";
        $db = "admin_my2";
        $table = "storagecheck";
        $id = "2";
        $data = explode('=', file_get_contents($input));

        $location = trim(substr($data[1], 0, -2));
        $date = trim(substr($data[2], 0, -2));
        $cluster = trim(substr($data[3], 0, -2));
        $ip = trim(substr($data[4], 0, -2));
        $mounted = trim(substr($data[5], 0, -2));
        $totalsize = trim(substr($data[6], 0, -2));
        $used = trim(substr($data[7], 0, -2));
        $usedPercent = trim(substr($data[8], 0, -2));
        $free = trim(substr($data[9], 0, -2));
        $freePercent = trim(substr($data[10], 0, -2));
        $minus50MBFiles = trim(substr($data[11], 0, -2));
        $plus50MBFiles = trim(substr($data[12], 0, -2));
        $totalFiles = trim(substr($data[13], 0, -2));
        $pinggateway = trim(substr($data[14], 0, -2));
        $raidhealth = trim(substr($data[15], 0, -2));
        $memorysize = trim(substr($data[16], 0, -2));
        $freememory = trim(substr($data[17], 0, -2));
        $backupsize = trim(substr($data[18], 0, -2));
        $backupspaceremaining = trim($data[19]);

        $conn = new mysqli($dbHost, $dbUser, $dbPass, $db);

        !$conn->connect_error OR die("Connection failed: " . $conn->connect_error);

        $sql = "UPDATE `$table` SET `location` = '$location', `date` = '$date', `cluster` = '$cluster', `ip` = '$ip', `mounted` = '$mounted', `totalsize` = '$totalsize', `used` = '$used', `used%` = '$usedPercent', `available` = '$free' , `free%` = '$freePercent', `-50MBfiles` = '$minus50MBFiles',`+50MBfiles` = '$plus50MBFiles',`totalfiles` = '$totalFiles',`pinggateway` = '$pinggateway', `raidhealth` = '$raidhealth', `memorysize` = '$memorysize', `freememory` = '$freememory', `backupsize` = '$backupsize', `backupspaceremaining%` = '$backupspaceremaining' WHERE `id` = $id";
        $conn->query($sql);

        echo "Entry ID $id updated succesfully.";

Upvotes: 1

Bill Karwin
Bill Karwin

Reputation: 562951

By all means, continue to try random combinations of syntax. You're almost there! You have only 25,622,702,110,040,813,336,081,912,836,183,802,580,535,642,835,740,419,266,502,085,577,211,882,272,650,414,517,527,799,478,743,488,400,747,943,069,000,615,135,250,963,344,833,331,485,320,251,677,534,098,797,378,129,631,890,777,593,821,450,771,100,601,051,040,658,154,732,825,998,434,179,861,242,822,823,294,569,561,935,370,384,214,295,397,821,780,452,001,056,445,606,246,731,902,062,784,801,234,354,567,343,837,156,149,230,451,302,848,181,975,698,028,988,742,337,466,917,653,761,882,052,114,517,822,309,817,669,841,068,938,391,156,712,267,414,065,202,136,112,693,807,721,164,567,033,575,479,857,283,752,212,725,180,807,950,664,529,926,143,586,635,589,599,609,374 more combinations to try.

(That's 95251, the number of strings of length 251, comprised of the 95 printable ASCII characters. Minus 1 for the example you showed above.)

Hmm. When we think of it that way, perhaps "trying every combination" isn't the best way to write code.

Read the documentation for the mysqlimport tool. It does not support a custom UPDATE statement.

You can import lines of text data into your table. One line of the text data corresponds to one row in your table.

By default, the line is inserted as a new row. You can also use the --replace option, so the line of text replaces an existing row, if the values in the text conflict with the primary key or unique keys of your table.

That's the only possibility of overwriting data. You must have a primary key or unique key in your table, and the the values in that primary key or unique key must conflict with values in the corresponding column of the text file you import. Otherwise, there's no way for MySQL to know which row to replace.

If you want some other way of matching up rows (i.e. not a primary key or unique key), or if you want to overwrite some columns but not others, then you should import your text data to a new table, and then you can do any SQL queries you want to match up newly imported rows with rows in the original table, and overwrite some of the columns.

Yet another solution is to forget about using mysqlimport, and instead write a script in your favorite language (I would use python for this, but any language could do it), read the text file line by line, and issue SQL statements row by row to change rows in your table. You could use UPDATE, or you could use INSERT...ON DUPLICATE KEY UPDATE, depending on what you want to do.

Upvotes: 0

Related Questions