2

I wrote a simple bash script, where I need to get from database 'fake' data, just want to simulate traffic using the REPEAT command. I wrote this line:

mysql -u root -p'password' -h x.x.x.x -D mysql -se "SELECT REPEAT('A', 26214400);"

Password and login are both fine. I try to connect to remote sql server (its sucessfull) but can't really perform the REPEAT select. What can be wrong and how to solve this?

The output I get:

REPEAT('A', 26214400)
NULL

I did the same thing in PostgreSQL:

"$(psql -h x.x.x.x -d test -U postgres -c $'SELECT REPEAT(\'A\', 26214400);')"

which works great, but I also need the mysql solution.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
yak
  • 3,770
  • 19
  • 60
  • 111
  • You are probably generating a string which is too long. Small test with a million 'A' worked fine. – andy Oct 08 '14 at 08:46

2 Answers2

4

I tried your query on MySQL and get this error message:

Result of repeat() was larger than max_allowed_packet (1048576) - truncated

So you must change max_allowed_packet or use smaller repeat count:

SELECT REPEAT('A', 1048576);

EDIT:

If you need to increase max_allowed_packet value, then you must do it on server and on client side:

  • to change on server run (or change permanently in my.cnf):

    SET GLOBAL max_allowed_packet=26214400;

  • to change on client add option --max_allowed_packet=26M to mysql command line:

    mysql --max_allowed_packet=26M -u root -p'password' -h x.x.x.x -D mysql -se "SELECT REPEAT('A', 26214400);"

Specify slightly greater max_allowed_packet than is SELECT output size.

More info: Server System Variables - max_allowed_packet

Community
  • 1
  • 1
Rimas
  • 5,904
  • 2
  • 26
  • 38
  • If there were problem with maxpacket, then postgresql will fail too. But it's works. – Jevgenijs Vaikulis Oct 08 '14 at 08:53
  • 1
    @JevgenijsVaikulis I think on PostgreSQL this limit is greater. – Rimas Oct 08 '14 at 08:55
  • Changed it but still have the error: `ERROR 2020 (HY000) at line 1: Got packet bigger than 'max_allowed_packet' bytes`. When I try with smaller repeat value - it works (unfortunately, that's not the solution since I need at least 25 MB of data) – yak Oct 08 '14 at 09:01
  • @yak you must change `max_allowed_packet` limit on client and on server side, see updated answer. – Rimas Oct 08 '14 at 09:30
1

Im not sure your shell command is correct. -p parameter will prompt for password, whereas --password='your_password' will send password to mysql.

Next command is working in my shell:

mysql -u root -h 127.0.0.1 --password='password' -D mysql -se "SELECT REPEAT('A', 26214400);"
Jevgenijs Vaikulis
  • 676
  • 4
  • 7