Bash - mysql simple select does not work, returns null

Question

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.

You are probably generating a string which is too long. Small test with a million 'A' worked fine. — andy
– andy, Commented Oct 8, 2014 at 8:46

Community · Accepted Answer · 2017-05-23 11:49:37Z

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

edited May 23, 2017 at 11:49

CommunityBot

11 silver badge

answered Oct 8, 2014 at 8:49

Rimas

6,0332 gold badges28 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Jevgenijs Vaikulis Over a year ago

If there were problem with maxpacket, then postgresql will fail too. But it's works.

Rimas Over a year ago

@JevgenijsVaikulis I think on PostgreSQL this limit is greater.

yak Over a year ago

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)

Rimas Over a year ago

@yak you must change max_allowed_packet limit on client and on server side, see updated answer.

Jevgenijs Vaikulis · Accepted Answer · 2014-10-08 08:48:51Z

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);"

answered Oct 8, 2014 at 8:48

Jevgenijs Vaikulis

6864 silver badges7 bronze badges

Collectives™ on Stack Overflow

Bash - mysql simple select does not work, returns null

2 Answers 2

4 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related