Ok guys trying to do something complicated In my where clause I am comparing a numerical value with the result
I want to move a node in "nested tree" and i would like to make sure if my actions are good. So…
how to move a node if it has subcategories (or subcategories have another subcategories)?
http://paste2.org/p/5487
have pb with this trigger
#1064 - You have an error in your sql hosting syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'delete from document_has_viewer where document_id= OLD.document_id' at line 4
Depends how you implemented the recursive structure. Are you using nested sets?
any idea man?
yes, nested trees
http://paste2.org/p/5487
need help plz
You'll need to reassign the left/right values, possibly over the entire parts of the tree involved (source and destination).
How can I import an .sql file into an existing database?
mysql -u uname -p dbname file.sql
thanks.
how can i show all added users?
If I assign UNIQUE parameter to a key, will it be slower?
or faster? because i probably don't need it on LFT and RGT fields
hello
I haven't quite understood the main difference between the community server and the non-community server…. could someone enlighten me on that subject?
it's complicated algoritm but i will try to solve that problem
primary: support
the rest is not really worth to mentioned
so the community server has "commnity" support, am I right ?
…. Getty
what is the difference between COUNT(*) or COUNT(ID)?
COUNT(*) and no where is a maintained number in myisam
is it the same as count(ID) if i want to count amount of rows?
between 15 and 20 - is it from 15 to 20 or from 16 to 19?
SettlerX, normally used with a group by
Only if id is not allowed to be null.
COUNT(id) counts non-null values.
and there is no speed difference between count(*) and count(ID)?
There can be a difference. One might count over an index, instead of the table.
so if ID is an index, count(ID) better?
Possibly. Worry more about the algorithm and use EXPLAIN to find areas to improve.
Can you make a query that does not find any rows return an array with the empty fields? (A bit like a left/right join)
outer join it with SELECT 1;
Thanks
hi.. is it okay to ask SQL query related questions here ?
ask away.
okay i have one query which fails.. and one which works.. and i cant see why
this DOESNT work:
SELECT *
FROM products, categories
left join manufacturers on (manufacturers.manufacturers_id = products.manufacturers_id)
how to see php mysql web hosting username and password
this one DOES work:
SELECT * FROM products left join manufacturers on (manufacturers.manufacturers_id = products.manufacturers_id)
#1054 - Unknown column 'products.manufacturers_id' in 'on clause'
which doesnt make any sense.. the field is there..
FROM (A,B) LEFT JOIN C ON …
someone help me how to know mysql username and password
so i just need () ?
or use JOIN and not a comma.
ya its part of a massive query which was failing with that error, so i stripped it down to the bare minimum which was broken
You should always group (with ()s) 3 or more joins if the join order matters.
okay cool thanks so much.. its working now
In this case the join order matters.
not my code either i might add
You're welcome.
hi all
i have a table with a primary key defined as id serial, and a secondary table referint to that id as transaction_id and also defined as bigint. i'm load testing with wbox (http://hping.org/wbox), once i the foreign key gets to 999, i begin to get 'Data Truncated
for column transaction_id', so from id 1000 and above… i have exceptions all thru… any ideas what's going on?
anyone here familiar with setting up an odbc connection to mysql
?
BETWEEN 15 AND 12 - how does MySQL understand it?
Moving categories in nested tree is difficult but operations on numbers are nicer than recurenction or loops
Hi. Something seems to be hogging our mysql daemon. Trying to log on with my default pass at these times says 'too many connections'. The server is already tweaked.. accepting even more connections is out of the question. But is there a way that it will accept 1 or 2 more connections from
another user or something? I'd like a backdoor to see wich processes are running
T-Start, there is a spare for root
BETWEEN min AND max
archivist, mysqladmin -u root processlist works indeed… but shows nothing. I need to take a look at all the queries the server is handling
are you there ?
MySQL5 returns null so i have to write 2 queries (depending if i move a node onto left or right).
i have mysql installed on my machine but
sivaji@sivaji-desktop:~$ mysql -u root
Access denied for user 'root'@'localhost' (using password: NO)
if you leave off the -p argument, mysql assumes you know what you're doing and don't want to use a password. Try 'mysql -u root -p'
threnody ya i can login now is my username is root ?
before you do anything, read this and follow the directions. http://dev.mysql.com/doc/refman/5.1/en/post-installation.html
ok
threnody thank you
my username is root is it ?
nope, your username is ksivaji
ks i am not asking irc user name i need mysql username 
need help on optimizing query http://pastebin.ca/644323
those are search queries, I have setup query cache, but think the query needs tweaking to decrease logical/physical IO
query cache tends to grow, so it's just a temporary solution
i am trying to connect to mysql using jdbc "connection con=DriverManager.getConnection("jdbc:mysql://l","sivaji", "password");" please someone help me what that url should be ?
localhost ?
com.mysql.jdbc.Driver i got this error
ks get rid of LIKE '%funding%' it cant use an index
ks just "jdbc:mysql://localhost ?
archivist it's search keyword, how do I get rid of it ? do you mean restructure the statement ?
please someone help me what url should i choose to connect to local database ?
ksivaji it's #java question
ks yes use fulltext
ksivaji, jdbc:mysql://localhost:" + port + "/" + dbName
how to find that port ?
3306
The requested URL /3306 was not found on this server.
Isn't there a sample connect line in the docs?
ksivaji do you have mysql hosting server installed and runnning at all ?
ks ya
Your MySQL connection id is 22
hi, where can I find older mysql binaries (looking for 4.1.11)
com.mysql.jdbc.Driver
archivist should I create fulltext index on a single column or rather two ?
i'm going to add ft index on wid and baseword in index_words table
can we set multiple values in an update?
yes
nm
update sometable set a=1, b=2, c=3 where d=4
i am trying to setup a simple master-slave replication situation but no matter what I do the master rejects me: .#28000Access denied for user 'replication'@'my-host.com' (using password: YES)
LOOK AT THIS PAGE IST VERY FUNNY
http://www.pennergame.de/ref.php?uid=2349
THANKS
3.63 sec went down to 0.05 sec !
however i can login use the same info using mysql -u replication -p -h my-host.com
from the slave
anyone have any ideas — replication has been given all priv's
debian packaged mysql
I have to practise creating algoritms - what can you advice me?
create table patient(name varchar(10),age varchar(3), sex varchar(8), disease varchar(10));
No database hosting selected
how to select database ?
use database;
you mean use patient ?
ksivaji, might want to use an ENUM for the 'sex' column, otherwise people might put "yesbaby" in it
and it's not as if there are more than 3 types (male/female/other)
HarrisonF
use patient;
Unknown database 'patient'
raar i was trying to do something like this
UPDATE domains,login,mail_service
SET login.manager_access = 0, mail_service.manager_access = 0
WHERE domains.domain='testdomain.com'
AND domains.domain_id=login.domain_id
AND domains.domain_id=mail_service.domain_id
ksivaji, create a new database then
create database hosting patient;
Can't create database 'patient' (errno: 13)
!perror 13
Permission denied
anyone know why i would get a 28000
access denied when using replication
but i can get in via mysql client directly from the same server?
Wondering what the convention is to have a column with a space in it ie CREATE TABLE test ('Column 1', varchar(20)); does not work - any ideas as to what I can do?
quotes
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
how do i turn on 'debugging' in mysql?
so i know what the replication slave is doing
thank you
if i remove master-password from my.cnf the error I get still says Using Password: Yes
JerJer[mobile]: for replication you need replicate slave privilege
it has it
!man replication
see http://dev.mysql.com/doc/refman/5.0/en/replication.html
plus i've given it every other priv
and % for the host
just to get in - always denied
but again mysql -u replication -p -h my-host.com works totally fine
JerJer[mobile]:what show slave status output or complete error message?
where my-host.com is master
connecting to master - io state
it's all?
last error is null
well - 'empty'
slave io running - no
slave sql running - yes
i'll see
try set server-id on the master
its set to the value of 1
strange
server-id = 1
log_bin = /var/log/mysql/mysql-bin.log
binlog-do-db = my db
all within [mysqld]
on slave and master
slave and master have same server-id?
no
hm
i have followed the oreilly onlamp howto
and oddly enough just yesterday I did a circular replication system on a whole different set of boxes - yet today i cannot even do a simple master-slave
so i am begnning to wonder if there is some other situation here
try use change master
!man change master
see http://dev.mysql.com/doc/refman/5.0/en/change-master-to.html
i am running mysql in linux vserver instances
but i've got 4 other mysql vserver based systems very actively doing circular replication now, so who the heck knows
trying change master
JerJer[mobile], you shouldn't use the my.cnf options
JerJer[mobile], they are confusing and depreciated
JerJer[mobile], they are only read once initially, then changes are ignored after that, you should instead use CHANGE MASTER TO
schweet change master to works!
ugh - i just configured all of my other systems using my.cnf
bugh - i just configured all of my other systems using my.cnf /b
since that's what google presented me with
well it is okay to do that initially
i would just go back and remove them to prevent future confusion
so build stored procedures ?
to setup replication, etc ?
JerJer[mobile]: no just use change master to
ok
ok how do i then restrict what to get replicated ?
you are already do that in your my.cnf
oh - so i still need my.cnf for some things
?
yep
JerJer[mobile], they are only read once initially, then changes are ignored after that, you should instead use CHANGE MASTER TO
ok bye all, have a good weekend
ERROR 2013 (HY000): Lost connection to MySQL server during query
timeout
I can't find out why my server is dropping the connection
how do i adjust that?
is there a way to say on duplicate key do nothing?
!m _psychic gone away
_psychic see http://dev.mysql.com/doc/refman/5.0/en/gone-away.html
INSERT IGNORE
_psychic_, vi my.cnf
its not mid-query, its on login even
is this the same issue?
seekwill, thanks
_parser_, that page has a number of issues
Read it?
reading it now
oops wrong nick complete
Can someone help me out with an SQL query? Simplified, I have two tables, orders and line_items. Line items have a boolean column, in_stock. I want to select all orders where every line item is in stock. Any ideas? I'm using MySQL.
(Of course I'm using MySQL. Sorry, I copy-pasted that from another room.)
ozzilee, not just in stock but enough to supply the orders
hm. ps shows that the server is running the command with the –skip-networking option
thanks for the docs help
slackware?
external
remove bind-address= and skip-networking from my.cnf and grant permission to the external 'user'@'host' and remove any firewall rules blocking port 3306 and make sure no overrides on the mysqld commandline
Well, the actual query is more complicated, but no, let's just say in_stock is boolean.
If I have a MyISAM table that uses symlinks to its data and/or index files, what happens if I then run "ALTER TABLE ENGINE = INNODB"? Will the statement fail? Will the table become inaccessible? Will the data be corrupted? Or will I simply wind up with a new, un-symlinked InnoDB table?
ozzilee, i am saying that may not be enough for the real world
It's certainly not, but the real world query is much more complicated. I simplified it down to the bare minimum to make it easier to understand my question.
eg its silly to send an order for ten to the warehouse when you have 9 in stock
ozzilee, where not exists (select from line item where in_stock is false)
Hmm… thanks 
I'll see if I can figure out how to make that work.
hmm - this is odd to me… do i no longer need to have the data setup on the 'slave' ? i have an existing database i want to add master-slave replication into
i am now getting a can't create database on the start slave; command
since my data already exists (in 4.x i had to have the data already manually synced up to the master)
can I use 5.0.45 utils like mysqlcheck with previous 5.x versions?
or 5.0.x versions rather
Yes
thanks..
ahha!!!! the replication user also needs the File priv
on what size databases, i.e 10s, 100s, 1000s, etc of rows is ORDER BY RAND() , not going to be too painful?
my_haz, my guess is when the the sort stops fitting in memory
order by rand
….!
random
http://jan.kneschke.de/projects/mysql/order-by-rand/
ah
my_haz, in other words, depends on row size
how, erhm, appropriate
order by rand
http://jan.kneschke.de/projects/mysql/order-by-rand/
(no "the_wench:"
maybe one day
Hi all
I'm getting this MySQL error, but I can't see a problem with the area it tells me I have an error on…
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'group='support' AND name='default'' at line 1
reserved word
What is?
group
Oh, ok
What can I do about that?
quotes
`group`
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
quotes
Use ` around identifiers (database/table/column/alias names) and ' around strings and dates. MySQL does allow " for strings, but ANSI standard uses " for identifiers (which you can enable with ANSI QUOTES option)
archivist is slow
even better, change the column name
I'll change the name, thanks
WOuld set be ok or is that reserved?
!tell mtappenden about reserved words
mtappenden http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html Use backticks (`) to quote your reserved words.
Thanks
Shrews slow?
point proven, thank you
hehe
its friday
woohoo!
"The third is it's slowness in taking a jest. You will find, if you venture on one, that is sighs like a thing that is deeply distressed, and always looks grave at a pun."
friday and I have a day off!
and yet you choose to spend it with us… how thawtful
briefly
away with you!
I just got back from a *fun* morning swimming with the kids and spending money around town!
good. you need some sun.
if we actually *had* sun!
have the waters receded?
yep!
we walked down the river as well today
went to a hotel by the side of it for a couple of drinks!
beats working
scirrosis of the river
hehe
Where are you?
in the wet south
worcester, englan
d
midlands!
anyone have any clues for me on what changing the engine type for a symlinked table will do? My simple/naive experiments seem to indicate /part #mysql
south of mee
oops
lol
guess he was right.
I imagine him coming back with corruption problems
The best thing about England is
ahhh
Huh, well, nevermind then
the English muffins!
So what is the best thing about England?
There isn't anything.
as in NULL?
More like ''!
that would indicate there might be something he just doesn't know about.
The #1 bestestestest thing about the USA is that the USAianites live there instead of everywhere else!
Good to know that England IS NOT NULL
lol
The best thing about the USA is that the British gave up on them
I agree, that IS the best thing about the USA.
Careful, or I'll get you put on Nabisco's blacklist.
ohnos! no more oreos!
Or at least the best thing that ever happened to *us*
I'm on my last big pack from DC
You know, you could just order them from Amazon instead of flying clear out here to go shopping.
I was flown over there for 'free'
i did a thing like this
We'll let you off this time. Just don't do it again.
GRANT ALL PRIVILEGES ON xxxxx.* TO 'xxxxuser'@'localhost'
IDENTIFIED BY 'xxxx' WITH GRANT OPTION;
does that give all the user privs on that db or no ?
yes
I'm still working on the "Lost connection to MySQL server during query" error. I've enabled logging, and I get "Aborted connection 8 to db: 'database' user: 'user' host: 'localhost' (Got an error reading communication packets)"
it gives the *specified* user all privs on that db
anyone got that before?
the client dies with an error before I can connect
has anyone else run into this?
what client?
cli mysql client
or php
so it has all privs ?
weird
fo sho
Hey guys - How can I create a Multipoint from a table of points in MySQL spacial. This does not work: select MultiPoint((select g from my_point_table));
yes
all database specific privs anyway
can root do anything to any db ?
they don't call it 'root' for nothin'
I can from the local box
but I can't connect from my web server
Lost connection to MySQL server during query
yes
(that's after supplying a password)
Didn't I give you a link to read?
I read it.
And nothing applied to you?
!man adding new user
I've removed –skip-networking
see http://dev.mysql.com/doc/refman/5.0/en/adding-users.html
I added a new user
Restarted MySQL?
I restarted
I enabled logging
How did you add the new user?
GRANT ALL… from the machine locally
Exact GRANT
GRANT ALL PRIVILEGES ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password' WITH GRANT OPTION;
(FLUSH PRIVILEGES)
is that what you're asking?
Yes
And what else did you do?
log shows this, btw:
[Warning] Aborted connection 8 to db: 'database' user: 'user' host: 'localhost' (Got an error reading communication packets)
Funny now 'user' and 'username' doesn't match…
I thought it went without saying that I'm not supplying the real usernames and passwords to the channel
sorry
they match
I've done it numerous times
It doesn't deny me access, it just closes the connection.
That's all I've tried. :/
but the error is pointing at your network
I get a password prompt, though
How are you connecting?
mysql cli client
but PHP is also trying connections
Stick with the mysql cli
that's what I've been trying thus far
Just making sure
any other ideas?
I can't seem to find much on the "(Got an error reading communication packets)" warnings in the error log.
hm
server shows 586 aborted connections
shucks - Artemis left just before I had an answer for him.
haha, mysql segfaults when I hit the up arrow…
I bet the pg client doesn't do this
which is the better way to connect webpage and mysql ?
cant i do this using jdbc
snoyes do you have any idea ?
seekwill, sounds like your readline is fooked
It's on Solaris
Told you yesterday. You need some server side language. Javascript will probably not talk to mysql directly, even with jdbc.
uTold you yesterday. You need some server side language. Javascript will probably not talk to mysql directly, even with jdbc./u
buTold you yesterday. You need some server side language. Javascript will probably not talk to mysql directly, even with jdbc./u/b
I have a table with a date column and a time column - how can I get a timestamp from both of them together?
When you join a table is there a way to prefix the fields so that the tables dont mix their fields together?
hey quick q
how can I check the db engine being used for each of my tables?
information schema
information_schema is a virtual database of databases inside of MySQL 5.0. You can use it to get schema, table and column metadata. Please note: It only shows information the user has access to. For more info: http://dev.mysql.com/doc/refman/5.0/en/information-schema.html
alternatively, mysqldump -d | grep -i "engine"
or a script and show…
ahh, gotcha
one more thing, where can i find out variables like how many tables are currently open etc?
show status; show variables;
something weird, I did two queries… one after one, less then a second between them
"SELECT username, password FROM users WHERE id = '3'" takes 0.038293838501 second
hi guys, I have two mysql 5 servers, one master, one slave. I've got low disk space on /var/lib/mysql (data) and noticed that several of the binary log files are 1-2GB each and I have 12 of them. Can I safely delete some of those without breaking my sync with the save?
and second query is
how can i run my own mysql server?
"SELECT * FROM bm_users WHERE id = '3'" takes 0.00265097618103
Naturally. The first time, it has to open tables, maybe populate the query cache, etc. The second time, a lot of the work is buffered.
I see
I see those are different tables - are they radically different sizes? How many entries in users vs. bm_users?
Ok guys, trying to do something complicated. In my where clause, I am comparing a numerical value with the result of a subquery which returns a SUM. The problem is, sometimes the subquery returns NULL, in which case the comparison doesn't appear to work. I want these null values to be treated
as a zero. Help?
!man ozzilee coalesce
Sorry - I have no idea what function you're talking about! but try http://dev.mysql.com/ozzilee coalesce
!m ozzilee coalesce
ozzilee see http://dev.mysql.com/doc/refman/5.0/en/comparison-operators.html
snoyes, "bm_" is a prefix, forgot to remove it from second example
How would I use that? My subquery only ever returns a single row, either a number or NULL if there were no rows to sum.
I love it when people change the names to protect the innocent…
so any advice on my question?
eww! you guys, I simplified query… actually it's about 5 lines long
SELECT COALESCE(SUM(x), 0)…
When you're dealing in milliseconds, you have a billion other things to consider.
If it was a significant difference (a few seconds, at least ), then it might be worth investigating
Ah, thank you.
seekwill, so cache plays its game
Probably
thank you
netdur, disk,opsystem and mysql
I have two mysql 5 servers, one master, one slave. I've got low disk space on /var/lib/mysql (data) and noticed that several of the binary log files are 1-2GB each and I have 12 of them. Can I safely delete some of those without breaking my sync with the slave?
Is your slave caught up to the master?
yes
Then yes, you can delete it
Do it properly though
http://dev.mysql.com/doc/refman/5.0/en/purge-master-logs.html
linuxkrn, that was good question, thanks
I thought the max_binlog_size = 1073741824 would mean I get only 1GB of bin logs anyway. :-/ Not 12 1.2GB files
It would be very bad if MySQL did that
My SQL command line client dies after like 10 seconds or something ?automatically? what is wrong with it? I just installed mysql 5.0 or w/e ?
Imagine if you're slave was taken down for a while, and the master exceeded 1GB…
hum, should have a auto-purge.
expire_log_days
You should be monitoring your system
I am, hence I see the partition is filling up and wanted to do something about it
will read up on that, my.cf setting I assume?
yep. http://dev.mysql.com/doc/refman/5.0/en/server-system-variables.html#option_mysqld_expire_logs_days
is that for me?
When you make successful backups of your slave, you can purge the files
mysql_expire?
no
why is my command line client quitting
by itself
Do you get some error message?
nope!
I login to the server instance pass right, i enter it.. i start typign or w/e and within 10 seconds it closes by itself
dont type ctrl c
oh really
how do you abort the command then?
i was doing that
sign comes up for a command
how do you go b ack to the normal screen
means it's waiting for you to finish the query with a ;
;
or \G if you like
or \c to stop it
oooooooh1
thanks a lot.
:-)
or \p to print what you've entered so far
of course, you can change that ; to something else with the DELIMITER statement.
Confused yet?
or cursor up to repeat
will I need to run purge on the slave or will the master signal it to purge as well?
oh i see
hi
nope, i just forgot that you have to end each command with a delimiter.
bnope, i just forgot that you have to end each command with a delimiter./b
how do i use regex on mysql wueries
?
queries*
!man regexp
see http://dev.mysql.com/doc/refman/5.0/en/regexp.html
seems that my slave doesn't have any bin logs, or maybe it's broken?
relay logs are deleted automatically when no longer needed.
Slaves often don't have a binary log of their own.
on slave you mean.
Yes, the master probably doesn't have a relay log.
hello
ah, ok. Thanks for all your help, got it all working, and setup expire_logs_days to take care of that in the future
Typically a slave only has a binary log of its own if you are daisy chaining replication.
does mysql require OUTER in LEFT JOINs?
no
thanks
hello
is anybody here from MySQL AB interested in working with the PostgreSQL people on a "rosetta stone?"
hi davidfetter
wouldn't that be SQL-92 or similar?
don't i wish
it would have to be a "full outer join" because mysql has things postgresql doesn't and vice versa
"you stop using cuneform first"
:-)
hey guys, is there a way to join 3 different queries horizontally like you can do vertically with a UNION ? i have 3 queries that return the same number of rows in order that match between all three, i want to get a single result set for the 3 queries like q1_field1, q1_field2, q2_field1,
q2_field2, q3_field1, q3_field2
hi all. how can i monitor incoming sql queries in "real time"?
i suppose you could JOIN them. does MySQL have something like generate_series() ?
hi
what is wrong in this query insert into sponsors (name,created) values ('hola','NOW()');
now is not translating the correct time
the ' ' around NOW
NOW is a function, not a string
adptr, thank you very much
does now() mean "this instant on the clock," or "the instant that the current transaction started," or…?
it's the instant the function was called, obviously
there is no way for mysql to reliably deduce anything else
I need to do a complicated data move - I have never done this before
I need to get spesific data out of columns in a table and then move that to another table and insert it
how do i concatene strings? '.'? '+'? '||'?
Instant this transaction started.
seekwill how can it ever give you that moment ? does it pre-evaluate the statement before executing it ?
CONCAT(), or CONCAT_WS()
!m SamuraiDio string functions
SamuraiDio see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
Yes
ah
seekwill, thanks
and immediately resolves NOW
!m SiliconG insert-select
SiliconG see http://dev.mysql.com/doc/refman/5.0/en/insert-select.html
adaptr, I have got a function so I don't know how I could pass the now function
thanks
seekwill, cannot i just use an operator?
No
wfq I don't know what that means
SELECT * FROM table WHERE timestamp = NOW() - INTERVAL 1 SECOND — imagine if that query took a few seconds…
adaptr, I have this function insert ($table, $var=null, $value) and values is an array. In this array I have to pass now, and I have to do it by 'now', otherwise I would get a php's parse error and on this way mysql is not taking the correct function
seekwill, tnx
np
I could do it by php, but I would like to do it by mysql
wfq then determine NOW in your PHP application
not really an option, unless you pass NOW() as the PHP application's value
I don't know if there ia an eval-like functionality available
is the value always NOW ?
anyway, inserting rows without specifying the columns causes these kinds of problems - specify your columns!
can i substr using regex?
i dont think so
adaptr, yes it is always, I kno how i can do it by using php
i mean, if i find a regex pattern on a string i wanna substitute that for another string
Not with the built in regular expression tools. There are some UDFs available, google for "mysql regexp udf"
wfq if the value is always NOW, then set the column to NOT NULL and default it to NOW, and always pass NULL as the value
ok, tnx
You can't set a function as the default value for a column, unless you make it a timestamp column and default CURRENT_TIMESTAMP
wfq then mysql will fill in NOW for you upon insert
hey adaptr, that is a good solution.
adaptr, thank you
snoyes whatever, I know it's possible
it's not MY problem I'm trying to solve
I have a table with Date and Time columns - how do I select the unix timestamp from it?
!man unix_timestamp
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
how do I select both the Date and Time as inputs for that, though?
!man CONCAT
see http://dev.mysql.com/doc/refman/5.0/en/string-functions.html
or ADDTIME()
!man ADDTIME
see http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html
ok, thanks
alter table sponsors change created datetime default NOW(); that is wrong
?
You can't set a function as the default value for a column, unless you make it a timestamp column and default CURRENT_TIMESTAMP
SELECT Date, Time, UNIX_TIMESTAMP(CONCAT_WS(' ', Date, Time)) as timestamp
i missed created again
it makes sense in my head that that should work, but the timestamp is always 0
Pastebin the results?
and issue SHOW WARNINGS;
0'); gives 0,
wfq, can't you use CASE for your 'now' problem ?
timestamps can't go below 1980
1970
ah right
gnari, CASE? I never used that
when timestamps were born
what about negative numbers for before?
doesn't that work?
no
the programming world has trouble with real world dates
summer of the 15th century
circa 1902
2037+
death of timestamp
hopefully past the beginning of 64-bit timestamp already…
it could record both the birth and the end of teh universe in nanoseconds
admittedly, determining what value represents "now" could be a bit of a problem
lets kill timestamps as a type and train computers with real dates
train them ? like gerbils ?
real dates in whose timezone?
alter table sponsors change created created datetime default NOW();?
well some date types are a bit odd
Told you twice. no functions as defaults.
wfq as snoyes already told you…
year of the dragon…
wfq make the column a timestamp and set it to current_timestamp
datetime conversion for everything that wants to read/write
heh the current date type cant handle BC
Hi all
I need a little help.
Sorry, we only give out a lot of help
*tries to word the question*
I can try guessing if you want
I need to do a select to see if there are records where fk_Parent=SPECIFIED and then update the record where pk_ID=SPECIFIED so that its fld_HasChildren field is TRUE, else false if there aren't any fk_Parent=SPECIFIED
that nake sense?
Why not calculate that value on the fly?
using count()?
hmm
good call
Using whatever you want
Otherwise you'll have to run this query all the time.
well the difference is, if I have a query / statement to update the fld_HasChildren field I can do that when-ever I add or remove a row. If I calculate HasChildren on the fly it adds an additional select statement to every query I make.
(adding and removing rows is far less frequent that queries in this case)
True
Depends on your needs. You're trying to denormalize, which works in different situation.
s
Usually, MySQL can calculate something like that pretty darn fast
erm
well
ok
What if you make a modification to the child table, but forget to run the query?
(to update the parent)
well there are no constraints but what I write myself, but as it happens there is no parent / child table - the parents and children are stored in the same table Same problem still exists but all access is done through a single php file - so all I need do is make damn sure I update the
field each time an add or remove is done.
Or you can just leave it normalized
can u explain normalized ?
cos u lost me
normalization
http://dev.mysql.com/tech-resources/articles/intro-to-normalization.html and http://datamodel.org/NormalizationRules.html and http://mysqldump.azundris.com/archives/20-Nermalisation.html and some here http://www.keithjbrown.co.uk/vworks/mysql/
ick - reading
It is good reading!
sh*t - I just tired reading the first paragraph of wiki for a breif and didn't get that either LOL
Which wiki?
What part do you not understand?
its ok - I went to wikipedia on database normalization and it referred to 1NF, 2NF etc - looked deeper into their defs and it starts to become more clear.
but
You don't think the articles we refer to are better?
Probably, but given that I'm mid way through the practical I wanted to avoid heavy reading on database design theory for the moment so went looking for a brief.
heh
you'll be back
lol
I've no doubt
I've bookmarked it
hello
0 { update cats set fld_HasChildren=TRUE where pk_ID = x } - just hoped to do a little more of this in sql
insert into foo (bar, foo_bar) values (b1,fb1),(b2,fb2),(b3,fb3)…. (bn,fbn)..
well is there a number that it's silly, or illegal for n to exceed
bar and foo_bar are both ints, btw..
max_packet_size
Or something of that name
seekwill - nice one, I'll toss up my google salad with a bit of that.
seekwill, either max_packet_size or something else, also called "max_packet_size"?
show variables; should provide the name
gnari, are you using a different encoding to us? because they look the same.. :p
"max_packet_size ,Or something of that name"
He's making fun of me
ah, I see.. spot the shameless noob.. yep, it's me.
seekwill, sorry
I feel… used.
I feel informed, if that helps - thanks!
it's great that you peeps answer questions here, btw.. some of this stuff is impossible to google, and is way beyond my experience.
uit's great that you peeps answer questions here, btw.. some of this stuff is impossible to google, and is way beyond my experience./u
infrid, lurk and learn then you can answer the questions
beeps
heh
I answered one already yesterday! it was an easy one.. but I will lurk and help if I can.
though I can't run my irc long at till I get more ram (please, Monday!!) because I'm now doing java, and it's killing my system…
java is the suc suc
?
megasuck
what is better
lol
jbalint, depends on the job
any ideas on why it doesnt seem that my php apps can connect to my local mysql server?
it doesn't seem ? so there's no actual problem ?
Hi there
i cant figure this out. from the console i can login as the user for the app. but not through php. something must have got borked with php
What's the command to get the last error thrown by the dbms?
how do you set a root pass for mysql / phpmyadmin
I'm not used to this multiple insert .. values (a),(b),(c). From what I gather if I'm under 65536 bytes, I'm fine, but if I say, insert (a1),(a2)…(a200) in one insert statement, does that kill performance?
are you using php for web apps..and then connecting to mysql ?
connection to mysql…might be some security issues…
talking to me ?
no sorry…i replied to blobaugh|ct
no more insecure than an of the other millions of webapps out there that do the same thing
well i need to set a root pass for mysql ASAP… before someone discovers it
how do i do it
!man set password
see http://dev.mysql.com/doc/refman/5.0/en/set-password.html
k
Is there a way to query for the last error caused?
My client is declaring I had an error, but I cannot find it
im not familiar with this
is there a way to do it in phpmyadmin
or command line
the_wench is a bot
rer
i meant snoyes
hello all
why couldnt you do it from the console?
you can run SET PASSWORD just like any other query. With phpMyAdmin, you can to the privileges section and set the password.
i dont know how to get into the mysql console
(i used to know)
just run a query in the mysql query box then. same thing
at the command prompt, type "mysql"
steve@galatea:/etc/apache2/sites-available$ mysql
Access denied for user 'steve'@'localhost' (using password: NO)
What application are you using?
mysql -p
try from root
i cant i dont have root
only sudo
sudo wil do i guess…
does it give the same error?
infrid, bulk inserts are much faster eg can be 100 times
you don't need root on the box, just privileges within mysql to set the password.
mysql -u root -p, if you need to.
yup that wil do fine..
it seems to work
thanks, again!
ugh
why doesnt it tell me if its working or not
SET PASSWORD FOR 'root' = PASSWORD('pass')
-
is all it shows
you forgot;
ah
it says syntax error now
jeez
!man securing
see http://dev.mysql.com/doc/refman/5.0/en/default-privileges.html
read that page
I guess its faster just to catch the error for duplicate entry than doing an extra query to check if the row is already existent
right?
I prefer not to get errors…
But then of course, there are race conditions
my problem is that with the mysql module in ruby errors would cause exceptions
and I'm not a real fan of using exceptions this way
Well, what do you want to happen if there is a duplicate?
Nothing, but..
there is a real problem to this
INSERT IGNORE?
oh
thanks!
or on duplicate …..
But he said "nothing"
well, the problem is a bit complicated
let me explain
I was trying in a round about way to prompt a man page read
hehe
blank wallism, methinks
I have two unique keys, and if there is a duplicate error on one key I just want to ignore it, and I would like to do an ON DUPLICATE UPDATE if I get a dup. entry for the other key.
(I'm trying create small but fast code)
if your indexen are sane, IF EXIST is fast enough
what do you mean by sane
efficient, and actually used by the query
(it's quite possible to create indexes that actually LOWER performance)
mhmm
well, I can worry about the indexen later than, thanks for your suggestion adaptr
bwell, I can worry about the indexen later than, thanks for your suggestion adaptr /b
s/than/then/
it's actually rather easy.. just set a reverse cluster on something that';s utterly random
as in PRIMARY KEY stupid_idx (random_GUID, actual_ID)
norc dont leave indexes till later if you want fast code
mhm
that will A take forever to insert in the middle - and it will *always* insert in the middle - and B. not use the index at all when searching for the actual_ID
What's the best way to move an entire mysql installation to a new server?
the installation itself or just the tables?
All databases, grant permissions, etc.
mysqldump ?
I've used mysqldump for individual databases, before. Will it work with multiple databases at a time? And is there a way to preserve the permissions so I don't have to run a GRANT statement afterwards?
http://dev.mysql.com/doc/refman/5.0/en/mysqldump.html
This site will actually tell you what it is capable of.
maybe mysqlhotcopy is also something worth taking a look it
*at
is mysql.com still producing mysql.info for consumption, or am I doomed to formats listed on the documentation download page
kitchen, there is a svn xml version of the manual
good to know, but I'm not up for the conversion process, really.
Did you check?
its fun
Is there a way to prevent certain rows from updating when using INSERT .. ON DUPLICATE KEY UPDATE?
Don't include them in the list?
I google some but the keys I used didn't unlock success
nor did rtfm on the mysql.com site
I want them in the table
I just dont want users to alter the rows after it has been inserted.
err snoyes
Then don't include them in the INSERT statement.
that wouldnt insert this value at all.
insert them, but don't include them in any insert..on duplicate key update statements that follow.
problem is, im using only one insert command
and its supposed to stay this way
norc you havnt read the update syntax yet
UPDATE col_name=expr, … ]
oh
actually I havent - thanks, I was just using somebody told me to use.
can I also use UPDATE (keys, …) VALUES (values, …) ?
whats the code if nothing exists in a field in a table
null or " "?
null means nothing. " " means a single space
but if your fields have a " ", null won't match to it
I imagine you're after either WHERE field IS NULL, or WHERE field = ''
if i have a field (which is suppose to display a link to a website) is blank i don't want an image to display
not really sure if i understood the difference yet, and i know it's very small
basically, a " " is SOMEthing, NULL is NOthing
but it sounds like you want SELECT * FROM `Table` WHERE `Field` IS NULL;
ya, i think null will work, but it's going to be in an if statement
thanks, i'll give it a shot
if statement? what language are you coding in?
php
oh, I was assuming SQL …
and i can't remember, whats the difference between = and ==
sorry
= is assignment which sets a variable to a value …
== is a "weak" comparision operator
=== is the "strong" comparision
both will match, but === is more stringent
e.g., FALSE == 0 is true, but FALSE === 0 is false because the types are different
ok
so i would need to use == if my field was blank with my null statement
or ===, yep
but why don't you just change the SQL call to exclude NULL values?
If i exclude tables from replication are they written to the binlog and ignored by the slaves or filtered on the master?
because the image would still show
ahhh, ok
hendrik, if you are using replicate-ignore* then they are still in the binary log and still downloaded by the slave and then filtered there by the SQL thread
hendrik, if you use the binlog-* options on the master, then they do not go in the binary log and do not get sent to the slave
i got it, thanks for the help
i'm not really a programmer, ha
np
HarrisonF, i will have a look at binlog-*. Thanks.
hendrik, be careful with those, they work based on the currently active database, not what one you are actually affecting
!tell nils_ about doesn't work
nils_ Doesn't work is not a helpful statement. Was there an error? Unexpected results? Does it sit on the couch all day eating all your cheetos and ignoring the classifieds? Be specific!
strange factoid for a self tell nils_
just needed to paste it to someone on icq
hehe
factoid theft
ICQ is weird.
Hi again. I Want to do an "insert on key conflict update"… over an array of int pairs, and figure that inserting the values into a temp table is most efficient way, so - With mysql, is there a quick easy way to create a temp table from an insert statement? Am I taking the wrong
approach?
Do you have a commercial license for that, nils_?
do you have a commercial license for asking me that?
the_wench sends an invoice to nils_
botsnack
ooooh let me bend over and lift my skirt for that
I don't have to; conversations on Freenode are GPL.
haha
unless they start a flamewar, in which case they are under the duel license.
unless they start a flamewar, in which case they are under the duel license.
ok
if they kiss and make up its gpl69
do you have a commercial license for asking me that, and please not that postgresql is way better than mysql.
note
even
what does table_locks_waited=1261 mean ?
something waited to get a lock
it's output of show status;
Table_locks_immediate 127390 ; table_locks_waited 1261
yeah
immediate locks are locks that are granted immediately after requesting them
if they can't be granted as some other thread holds a lock the thread has to wait for the other lock to be released
it's mostly read environment, almost no updates
it swapped heavily today, maybe it was waiting for resources
also wonder if I have threads cache set correctly
time to work out why
i have it set to 30
check slow queries
hi, did I understand the mysql documentation right that it is possible to exclude tables from replication but this is done on the slave after downloading the queries?
threads_cached 2; th_connected 26; th_created 28; th_running 4
archivist indeed thanks
yes. replicate-ignore-* options
thanks very much
should be enough
create table test ( veldje integer ) engine = innodb; ?
maybe innodb is disabled?
show variables like 'Have_innodb';
have_innodb | DISABLED
because you disabled it
or have_innodb | NO
because you didn't compile mysql with innodb
You smart
indeed.
I didn't compile it at all
it's a XAMPP distribution
finally someone notices
oh well
Thanks!
Is that something I can switch.. I don't know if it was compiled or not
set have_innodb=1; ?
no
insert curse
there is an option skip-innodb in my.cnf
Thanks
Interesting
FCC approved cursing is allowed here
checking
Ah kewl!
you may say Damn it, Son of a Bitch etc., but not **** or ****, or even ****
Well, what if it is after 10PM?
it's after 10pm someplace, *right now*
foxtrot uniform charlie kilo osca foxtrot foxtrot
well, there is a timezone problem. There could be children!
I heard that in some TV show they yell "Frack" instead of "fuck". That's keeping american children from becoming murderers, terrorists or worse (democrats?)
oh no I said it!
ME EYES
btw, lol
hey hey
we used to do phonetic swearing down the pub most coudnt catch on
anhy body can asnwer ?
Thanks for the my.cnf comment.. it was skipped.. now it's enabled
noo
ok
postgresql not good?
dunno
postgresql = good;
zooZZ, nobody can answer till we get a crytal ball to read your mind
lol
wizard
Perhaps you should try posting your question?
I will try
SELECT MAX( messages.date ) AS maxdate,
topics.id AS topicid, topics.*, users.*
FROM messages, topics, users WHERE messages.topic_id
= topics.id AND topics.user_id = users.id AND
topics.forum_id = 6 GROUP BY
messages.topic_id ORDER BY maxdate DESC
take this
it's for a forum to get the latest threads and order them 1st
using PHP
too hard ?
I knew that you can't solve it ,,
What's the question?
I don't know why this works properly …though it looks wrong
it rearranges the threads in a forum
using MAX()
to show latest posts
So the problem is that it works as intended?
first
what error message have you got (.err file in the datadir or wherever debian puts it)
nope
groupwise max
http://jan.kneschke.de/projects/mysql/groupwise-max/ http://dev.mysql.com/doc/refman/5.0/en/example-maximum-column-group-row.html
the problem is by using MAX()
that's what I don't understa d why
why not DESC date
i'm learning mysql and wants to know how it works
You need to GROUP BY every column you are not using an aggregate function on
If I have an existing table with a list of categories, to what do I set tree_left, tree_right, tree_parent, scope to make all nodes roots?
fantastic amount of implied stuff here
hehe, sorry
I'm wondering how to reset a nested tree
:P
row1 lft=1 right=2 row2 lft=3 right=4 etc?
ratonnn, sounds plausible. depends on the implementation details of your particular tree
Is there a way to not check references (innodb) while executing a DDL script?
set foreign_key_checks=0;
Thanks!
don't forget to set it back to 1 when you're done.
Good advice
Thanks a lot!
Shariff
?
you was asking about the question
and now you can't find an answer
shame on you
Actually I didn't pay attention
drug abuse has severe consequences…
lol
haha
any idea ??
You were going on and on about needing help without saying what was up.. then I tuned out
Shariff ,, you have a strange name
And?
answer why .. and I will arrange my first question
get busy with this till I'm ready
I have no clue why to use the max() in the select clause.. I would order by date desc..
O_o
no no
tune in
are you arabian , Sharrif ?
Shariff?
Kevin doesn't sound arabic
I'm !
Lol ,, we have lots of Kevins
there is a common psychological condition here in germany, it's called Kevinism.
Search and you will get thousand or two
basically resulting in stupid names for children. English or french names which the parents can't even pronounce right
But I'm sure ,, this name means in Arabic ' Honest '
didn't know that
in fact I don't speak any arabic
you knew then
How do Germans pronounce "Kevin"?
Arabic is the most diffcult language on earth
depends on intelligence
No other language has same vocabuary and grammers
The ones for whom the condition was named.
and sometimes regional dialect. kevin isn't the real problematic name though
I cant imagine german kevins
oh , deutsch sucks … I don't like this lang .. sorry nils_
more critical are names like John and Jason, some like to speak the J as Sh… Especially funny with Jason because it sounds like 'scheissen' then (taking a dump in the non DBA sense…)
ithehe
sprache is talk in dutsch ?
sprache = language
I studied it for 2 years and I only can type Ich liebe dich
hehe
"talk" is "gespräch"
yes ,, seems to
well I had 4 years of french in school, I don't remember a single word (only when I'm drunk)
hahaha
Drunk … ?? Beer ?
well, I had 4 years of latin in school back then, and I still don't know what "ire" means.
you can't get seriously drunk with beer
It's what happens when Cockneys get a job.
ire ?,,, not a big deal
I had a few years of arabic, dont remember much
nils_ I don't know much of these stuffs
it's quit prohibited in my religion
not very spread in the arabic world I guess
nils_ yes ,, it's prohibited
by religion
also by law in some countries I heard
but no here ,, non-religous ppl can still drink in our countires
not*
I know, I did
Only in KSA( SAUDI ARABIA )
nils_ lol
sorry for non-religious
I mean ,, non - muslims
both apply for me
lool
yeah ,,whole europe don't believe ion GOFD
\GOD
depends.
I hadn't met one who do
well, take Vatican for example
Aha ,, yes ,, sure ,, 100% of Vatican are pure christians
but it's too small ,, it has 200 000 citizens as I think or less
well Italy or Poland are examples
20,000 ? o_0
yep ,, but I say whole for large numbers not means ALL of them
783
indeed
lollol
but you can't take europe as a whole anyways because there are strong regional differences
yep ,, but will not affect the wide space of Europe
they consists 1/100 of it
hello friends, can anybody suggest a good way to store month data in a table?
so ,, it's fair to say ALL
: with or without year?
that is, month and year
anyway ,, i lake much info in this field
hmm there is the date type which is close…
NOW()
well compared to the USA for example you are right.
and select date("Y M ", strtotime("") ) in php
numeric might also do.
how would numeric work?
USA by the way has 7 million muslims and increasing 20.000 yearly as CNN reported
sounds like the best choice to me
just 1.5 mil after 9/11
ah, you mean something to the effect of 0608 for june 08?
yes. Depending on wether you need dates before 2000 or after 2100.
okay. i think this century will suffice
AgentQ77 lol
let's talk about that on december 31st 2099
hahahaha
I have SQL that will give me a list of ZIPs based on CITY
sami-boy ,, sell it $$$$
sami-boy: I even have that list with longitude and latitude
ebay members will give you 2$ for it
nils_ lol
I also have a seondary link table that gives each zip an AREA identifier
now I need to modifiy this ID for a subset of ZIP
sami-boy: which ID?
nil_ so , can you anser my q.
I have no IDEA how to SET the Identifier filed in the second table based upon the retuern list of the first
and Shariff who ran away
area identifier
not record id
Shariff ,,, tune on !!!
in English
sami-boy: can you show some examples I don't get it
I need to take all the 'area_id' that means one city and set them to another city ID
2 tables
list of businesses, anmes, addr, zip
second table
should paste the query again ?
lsit of zips with "area ids"
I can get a DISTINT list of ZIPS for all businesses in LA
now I need to find that list in the second table and change their area_id from 19 to 24
hmmmm , ok .. it's ur problem …. TAKE THIS
SELECT MAX( messages.date ) AS maxdate,
topics.id AS topicid, topics.*, users.*
FROM messages, topics, users WHERE messages.topic_id
= topics.id AND topics.user_id = users.id AND
topics.forum_id = 6 GROUP BY
and I don't wnat to do it via EXCEL
messages.topic_id ORDER BY maxdate DESC
sami-boy: UPDATE businesses JOIN zipAreas USING (zipId) SET zipAreas.area_id = 24 WHERE businessCity = 'LA'; something like that?
Hmm ,, give us the q ,, give us the q .. AND what after???
lol
it seems no thing after
this gave me an error
UPDATE abp AS a
JOIN marketing_areas_zips AS m
USING (a.business_zip) SET m.area_id = 24 WHERE a.business_city = 'Los Angeles';
there should not be a table in the USING() statement.
nils_ ? Shariff ?
if I remove 'a.' than the error says UNKNOWN COLUMN
so one of the tables doesn't have a field named business_zip
the first table does
lol
meaning you need to use ON a.x = b.y instead of USING()
Oh, you're an op?
seekwill , ofcourse
nils_ ,, come on
almost
reply or you will catch Shariff in ban list
what was the question again?
this gives me a DUPLICATE KEY error
I want to know how he made this to work
Let's not talk like that…
I'm learnign MySQL and find this strange
presumably then you already have records with those values.
seekwill sry
hmm what does the query return?
You can use UPDATE IGNORE to do the rest of them. Then see which ones are left in area 19 and decide what to do about them.
I guess so
it returned the latest threads on a forum ordered by the date it was posted on
last first
I need to modify existing records in the second table
sorry, I don't understand the UPDATE IGNORE
threads ( topics ) have messages
and it orders it by the message with last date
sami-boy: It will just ignore the ones that would cause a duplicate key error.
and show authors form table user
and where doe the UPDATE IGNORE go in the suntax?
and date !
yeah, the MAX() and group by stuff is basically to get the last message to join in
Sorry seekwill again
UPDATE IGNORE apb AS a JOIN…
yes ,, but max does return one message only
and what about the remainder of messages that need to be arranged
not with group by. Do you know how group by works?
orderd as well
the remainder of messages is thrown away while grouping
yes ,, grouping each message with same Topic_id together
thrown away
?
with aggregate functions
aggregate function ?
or discarded because they don't have the max(date)
yes
MAX(), MIN(), COUNT(), AVG() and so on
if you select max(message_id) you geht the highest message_id, if you have select max(message_id) from table group by topic_id you get the max message id *for every topic*, that's the group by magic
first it calculate the last message date and set it as maxdate
and then it orders them by maxdate
yeah that's the result set as there are multiple messages returned because of the group by clause
it may become clearer when you strip the MAX() and group by stuff and look at the result, then you can see what happens after group by.
aha ,, so it's an ( like ) exceptional case
or .. I dont know what to call it
when gruoping them all each under their respective topic
and then gets the max date for messages
then It will return max date for each topic message ?
it worked, after a fashin
thx
or for them all and ordering them by date ?
why the hell he didn't use order by DESC date
Because that's wrong syntax
without group by?
using another form of conditions
that would join in every message, you want one row per topic in the result
that's slow
is there some good documentation/howto for sphinx and php? Seems like there isn't even an API Documentation
newbie here
when grouping messages by their topic ID ,, what will happen ?
then you get one message per topic id
and with MAX(date) you get the last message
nils_ oh ,, this is not what I understood gorup
it's best to experiment a bit with group by
and then try around with the aggregate function, you'll get the hang of it faster than by listening to my confusing attempts to explain it
nils_ ,,
i'm reading a PHP & MySQL ebook that concentrate on PHP
and thus ,, unfair explaining on mysql
book
http://www.kitebird.com/mysql-book
seems good
snoyes ,, yeah,, but my main study is on PHP
but it seems that I must study MySQL
to understand these queries
Ok ,,, visting amazon again and paying for a mysql
my money
lol
anyway ,, thx guys
good day or night ,,, time zone matter
bye bye
bye ??
Hello. Is there a built in limit in GROUP_CONCAT()? I only get a max of 18 and a half elements.
