Entries filed under Datenbanken

Connect SequelPro with DigitalOcean Managed MySQL 8.0.X

Posted on 19. September 2019 Comments

ALTER USER 'doadmin' IDENTIFIED WITH mysql_native_password BY 'secret_password';

Change ’secret_password‘ to the password given to you by DigitalOcean.

The reason behind this is that SequelPro and other MySQL Clients only work with the old (5.X) way of MySQL authentication vs. the new (8.X) caching_sha2_password. It has nothing to do with the MySQL version itself as Sequel Pro seems to work just as well with version 8.X. You can find more information in the MySQL documentation.

Fix shipping providers for FBA orders in Plentymarkets

Posted on 27. Februar 2017 Comments

The popular ERP Plentymarkes let’s you send your orders via the Fulfillment by Amazon program to your customers. Amazon is just used as a logistics partner, the order doesn’t have to come via Amazon. It could e.g. be a Rakuten or eBay order. The problem that Plentymarkets didn’t address at all and is discussed in the private forums, is that Amazon chooses whatever shipping company they seem fit.

Every order has a standard shipping provider and with it comes a URL for tracking the package. Most of the vendors would implement an event procedure („Ereignisaktion“) that sends an email to the customer containing the order and the TrackingID as soon as it’s shipped. The TrackingIDs are directly imported from Amazon. Unfortunately Plentymarkets does not change the shipping provider in case it’s not the default one (which is often the case). So the customer gets wrong information.

In one of last last blogposts I introduced a PHP package to check which shipping provider a TrackingID belongs to by either scraping their website or using a regular expression and filtering for common patterns. In this blog post I want to explain how to use it to fix those orders. You will need a webserver with PHP and MySQL and a little knowledge of programming and Linux.

You can find some simplified code examples on GitHub soon.

  1. Take your FBA orders out of the event procedures that sends the email to the customers, filter e.g. via WarehouseID
  2. Instead, use an event procedure to mark those orders with a flag, e.g. a star (ID 2)
  3. Create a DynamicExport of the type OrderComplete. Filter for your Sent Status (usually 7) and WarehouseID of FBA orders. Include these fields:
    1. OrderID
    2. OrderPackageNo
    3. OrderParcelServiceID
    4. OrderParcelServicePresetID
    5. OrderLastUpdateTimestamp
  4. Implement the SOAP Call GetDynamicExport (will be replaced by REST via Plugin mid-2017)
  5. Write a Cronjob that calls the GetDynamicExport script with the FormatID of the DynamicExport from #2
  6. Create a database table called ‚pm_shipping_providers_check‚ with the fields from #2
  7. Write a script that imports the CSV you downloaded with the GetDynamicExport call into your MySQL database
  8. Create another DynamicExport, this time of the type Order. Include these fields:
    1. OrderID (Synchronisation)
    2. ParcelServiceID (Import)
    3. ParcelServicePresetID (Import)
    4. PackageNo (Import)
  9. Create a table with these fields called ‚pm_shipping_providers_correction
  10. Create a script, that uses the shipping-service-provider-check library to check the every PackageNo in ‚pm_shipping_providers_check‘ and – if the provider is different from the default one – writes it into ‚pm_shipping_providers_correction‘
  11. Implement the SOAP Call SetDynamicImport (see #3)
  12. Write a script that exports the table ‚pm_shipping_providers_correction‘ into a (semicolon seperated) CSV.
  13. Create a cronjob that uploads the file regularly
  14. Create an event procedure on the event PackageNo that sends out emails, but only for the previously flagged orders

MySQL Workbench Error: line contains NULL byte

Posted on 21. April 2016 Comments

When importing CSV (or other) files into the database, scripts (especially PHP or C-related languages), will stop if there is – for whatever reason – a NULL byte in your file because it signals end of file/string, see Null bytes related issues.

So when importing a file like that with MySQL Workbench you will get this error:

line contains NULL byte

You can solve this by using the commandline tool tr (from coreutils):

tr < file-with-nulls -d '\000' > file-without-nulls

To check if there are any null bytes in your file, use the python IDE and type in:

open('filename.ext').read().index('\0')

Thanks to Pointy from Stackoverflow

Collect currency exchange rates in a MySQL database with PHP and fixer.io API

Posted on 4. August 2015 Comments

If you work in a company that buys and sells goods in many different currencies, it might be a good idea, to use the latest exchange rates. Also, it might be useful, to store old exchange rates to clarify/verify old business decisions. If once a day is enough for you, fixer.io offers a free simple Rest API. A lot of the code at my work is written in PHP but I usually use the request library in JavaScript and Python, so I’m using it in this example too. A common PHP solution would be guzzle. But first, get composer (the PHP counterpart to npm or pip):

$ curl -sS https://getcomposer.org/installer | php

$ php composer.phar require rmccue/requests

The mysql_ commands are deprecated (and removed in PHP 7), use mysqli or PDO. Also you should use some sort of framework for the database access, like medoo or a proper ORM. This is just proof of concept.


$base = 'EUR';
$request = Requests::get('https://api.fixer.io/latest?base=' . $base, array('Accept' => 'application/json'));
if ($request->status_code == 200) {
$response = json_decode($request->body);
$GBP = $response->rates->GBP;
$CAD = $response->rates->CAD;
$USD = $response->rates->USD;
$NOK = $response->rates->NOK;
$CNY = $response->rates->CNY;
$rBase = mysql_real_escape_string($response->base);
$date = mysql_real_escape_string($response->date);
$currencies = mysql_real_escape_string("1.0, $USD, $GBP, $NOK, $CNY, $CAD");
$qry = "INSERT INTO `exchange_rates_fixerio`(date, base, eur, usd, gbp, nok, cny, cad) VALUES ('$date', '$rBase', $currencies);";
$insert = mysql_query($qry, $mysqlConnection) or print mysql_error();
}

I assume the database connection is defined earlier, there’s lot’s of documentation for that. Because we are from Europe, I chose Euro (EUR) as the base currency. Apart from the get() method, you need nothing else, to send a request. If the request returns an OK(200), the response is read and saved into different variables, e.g. for British Pounds, US Dollar, Canadian Dollar, Chinese Renminbi and Norwegian Krone. Just to make sure we have the right base, it’s also parsed. From there it’s only a simple INSERT INTO (as said before, use a framework for that)

The table could look like this:

CREATE TABLE `echange_rates_fixerio` (
`date` date NOT NULL,
`base` varchar(3) NOT NULL,
`eur` double NOT NULL,
`usd` double NOT NULL,
`gbp` double NOT NULL,
`nok` double NOT NULL,
`cny` double NOT NULL,
`cad` double NOT NULL
)

 

You can also find this code on GitHub.

WordPress cache mit redis und uberspace

Posted on 24. Oktober 2014 Comments

Redis ist eine In-Memory Key-Value Datenbank. Das heißt, dass die Daten im Arbeitsspeicher gehalten werden und deswegen besonders schnell verfügbar sind. Dafür sind sie aber auch nicht persistent gespeichert, d.h. bei einem Neustart sind die Daten weg. Das ist aber im Grunde perfekt für einen Cache Speicher.

WordPress ist ja nicht gerade für seine Schnelligkeit bekannt und so gibt es diverse Plugins zum besseren Cachen. Seine eigene Seite kann man z.B. bei Google Page Speed testen. Jedes Mal wenn eine Seite abgerufen wird, muss PHP Daten aus der Datenbank holen und eine Seite generieren. Je nach Hoster, Anbindung etc kann das ganz schön lange dauern.

Die Idee für diesen Post ist Daten, die sich ohnehin nicht so häufig ändern, in einer Redis Datenbank vorzuhalten und statt jedes Mal eine Seite generieren zu lassen einfach diese Daten aus dem Redis Cache abzufragen. Dazu braucht man aber eine Verbindung zwischen PHP und Redis und überhaupt die Möglickeit Redis auf demselben Webserver zu installieren auf dem auch WordPress liegt. Leider bieten das nicht allzu viele Anbieter, mein Anbieter Uberspace jedoch schon. PHP kann über die Bibliotheken Predis oder  PHPRedis auf Redis Server zugreifen.

Das Plugin wp-redis-cache übernimmt jegliche Arbeit. Zum Installieren muss man einfach nur der Anleitung folgen. Die IP seines eigenen Webservers bekommt man z.B. über Ping. Bei Uberspace läuft Redis nicht über TCP sondern über einen Socket. Dieser befindet sich im Home-Verzeichnis ~/.redis/sock.

Als Erstes muss man auf seinem Uberspace Redis installieren:

$ test -d ~/service || uberspace-setup-svscan
$ uberspace-setup-redis

Wenn man statt dem mitgelieferten Predis 5.2 lieber PHPRedis benutzen möchte kann man dies folgendermaßen installieren:

$ uberspace-install-pecl redis

Mit meinem Patch für wp-redis-cache kann man nun auch Sockets mit Predis nutzen.
Wichtig ist nun noch die Variable $redis_server auf folgenden Wert zu setzen.

/home/username/.redis/sock

Die Abkürzung ~/.redis funktioniert hier nicht.

Solange die Variable $debug noch auf true steht, kann man beim Aufruf der Website im Quelltext die gemessene Zeit bis zur Ausgabe sehen. Diese sollte deutlich unter vorherigen Werten liegen.

Im WordPress Backend kann man unter Einstellungen/Wp Redis Cache noch eine maximale Zeit (in Sekunden) angeben, in denen der Cache geleert und neu befüllt wird, um ggf. Änderungen anzuzeigen. Zum Testen kann man aber auch einen $secret_key in der index-wp-redis.php festlegen und seinen Blog so aufrufen: http://blog-url.tld/?refresh=refreshpasswort
Oder man startet einfach Redis neu:

$ svc -du ~/service/redis

MySQL String Vergleiche in WHERE Klausel: Groß- und Kleinschreibung

Posted on 9. September 2014 Comments

MySQL unterscheidet standardmäßig nicht zwischen Groß- und Kleinschreibung, außer in speziellen Zeichensätzen. Bei z.B. UTF-8 muss man bei String Vergleichen in der WHERE Klausel deshalb immer das Schlüsselwort BINARY benutzen, wenn man Strings auf exakte Gleichheit überprüfen möchte. Dies kann z.B. praktisch sein beim Korrigieren von Rechtschreibfehlern oder der Groß- und Kleinschreibung von Usernamen. Ein SELECT Statement zum Prüfen von Ungleichheit sieht z.B. dann so aus:

SELECT feld FROM tabelle WHERE BINARY zeile1 <> zeile2;

Ist Groß- und Kleinschreibung irrelevant bietet sich immer an Vergleiche von Strings prinzipiell mit einem UPPER() zu versehen, um alle Buchstaben groß zu schreiben, vor allem, wenn man nicht sicher ist, welchen Zeichensatz man verwendet. Ein SELECT Statement würde dann so aussehen:

SELECT feld FROM tabelle WHERE UPPER(zeile1) <> UPPER(zeile2);

Show Pictures in a Form on a Table with Report in APEX

Posted on 10. Dezember 2013 Comments

The table is called WPDBD_TEIL. The primary key is TID. In WPDBD_TEIL there is a column called BILD(german: picture/image) from type BLOB.

First click on your form and keep the name of the picture in mind. In this case, it’s P9_BILD.
report_bild

Then go to your report, click on edit for the whole report and use the following code

apex_editreport

select "TID", 
"TNAME",
"PREIS",
decode(nvl(dbms_lob.getlength("BILD"),0),0,null, '<img height="100px" width="100px" src="'||apex_util.get_blob_file_src('P9_BILD',TID)||'" />') "BILD"
from "#OWNER#"."WPDBD_TEIL"

If you’d open the report now, you only get the HTML Code. So you have to change the way APEX displays the entry. So go to the report, click on BILD and change Display as Text to Report Standard Column

 

edit_bildstandardreportcolumn

 

The form I used was the following.

formreport

Also, in the last page of creating the form, you have to chose your own primary key, not the ROWID.

 

primarykeyapexform

 

MySQL Verbindungen in C++ mit mysqlpp

Posted on 6. November 2013 Comments

MySQL Server und Header installieren:

sudo apt-get install mysql-server  libmysqlclient-dev libmysql++-dev

In Eclipse C++-Projekt anlegen, Rechtsklick auf das Projekt:

Properties>C/C++-Build>Settings>Tool Settings>GCC C++ Compiler>Includes
Dort die Pfade /usr/include/mysql und /usr/include/mysql++ als Include paths eintragen.

cpp_mysql1

 

Weiter unten unter GCC C++ Linker>Libraries>Libraries „mysqlpp“ eintragen und unter Library search paths ebenfalls /usr/include/mysql und /usr/include/mysql++.

cpp_mysql2

 

Unter Miscellaneous dann noch bei Other objects die Datei /usr/lib/x86_64-linux-gnu/libmysqlclient.so angeben:

cpp_mysql3

 

Beispieldatei: mysqlpptest.cpp

 

Credits: C++ with a shot of MySQL on Ubuntu 10.10

Owncloud News Reader Error after update to 5.0.11

Posted on 21. Oktober 2013 Comments

After updating to owncloud 5.0.11 I got the following error in my Owncloud News Reader:

Screenshot_2013-10-21-10-20-56

(For Google: A Toast saying „End of input at line 1 column 1“)

In my owncloud instance, I got an error in the administrator menu saying:

SQLSTATE[HY000]: General error: 1 no such column: feeds.articles_per_update at db.php#391

I updated the owncloud instance and app framework/news app but it still showed me that everything was uptodate, even when 5.0.12 was released. Via phpMyAdmin I could see that the feeds were still in the database but I couldn’t see them in the web app or Android app. So I just deleted the owncloud folder, downloaded the new .tar.bz2 from owncloud.org, removed the oc_news tables(do not delete e.g. the contacts or bookmarks!). Then I changed my username in oc_users, used my old username as my new username and reinstalled owncloud, this time version 5.0.12.  I could then delete the old (changed) username via the admin panel.

This is the third time I had to reinstall owncloud, because the update mechanism didn’t work properly… And yes, as advised, I did deactivate the news app and app framework for the update process 😉

I just had a look at the SQL-statements from my last backup and saw that articles_per_update is supposed to be a column in oc_news_feeds in the new version of the news app. Somehow, the owncloud update process didn’t update the news app or didn’t trigger a 3rd-party update process. Anyways, I guess this could also be solved by just adding this column manually:
`articles_per_update` bigint(20) NOT NULL DEFAULT '0'

MRBS: Check if user has booked 2 rooms at the same time

Posted on 17. Oktober 2013 Comments

This little function checks if the same user has booked $room1 and $room2 at the same time.

function checkForSameUser($room1,$room2,$user,$starttime,$endtime,$tbl_entry) {
$sql = "SELECT id, name, start_time, create_by, status
FROM $tbl_entry
WHERE start_time < $endtime
AND end_time > $starttime
AND (room_id=$room1 OR room_id=$room2)
AND create_by=\"$user\"";
$res = sql_query($sql);
if (empty($res))
{
// probably because the table hasn't been created properly
trigger_error(sql_error(), E_USER_WARNING);
fatal_error(TRUE, get_vocab("fatal_db_error"));
}
if (sql_count($res) != 0)
{
sql_free($res);
return "error";
}
}

 

You can use it e.g. in mrbs_sql.inc in the function mrbsCheckFree(..) to check if a user is allowed to book a certain room like this.

// 1 and 2 are the roomIDs
$result = checkForSameUser(1,2,$user,$starttime,$endtime,$tbl_entry);
if ($result == "error"){
$err[] = get_vocab("multiple_rooms");
return $err;
}
}

As you can see the first function returns the string „error“ in case an error occured. I translated the string for multiple_rooms in every language used on this MRBS system and edited the the lang.x file in the main folder, e.g. lang.de:

$vocab["multiple_rooms"] = "Mehrere Räume können nicht gleichzeitig vom selben User gebucht werden";