Saturday, July 13, 2013

[how to] COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query [on hold]

[how to] COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query [on hold]

COUNT of rows with parent id equal to the row we have with a parent id of 0 in one query [on hold]

Posted: 13 Jul 2013 09:00 PM PDT

I'd like to do the following in one query:

  • grab a row that has a parent_id of 0
  • grab a count of all the rows that have a parent_id of the row that we grabbed which has a parent_id of 0

How can I accomplish this in one query? Please let me know if you need more information, I will GLADLY be as assistive as I can

Heres an example of what im doing now:

select id from messages where parent_id=0

AND THEN

select id from messages where parent_id={{previously_chosen_id}}

Thanks

Help creating this query

Posted: 13 Jul 2013 08:35 PM PDT

I have the following tables in a MySQL Database:

messages => contains email messages  addresses => contains email addresses  address_parts => contains email address parts (to,from,replyto,cc,bcc)  message_address_parts => connects an email address to an address part and to a message

What I'd like to accomplish is one query that fetches the messages and within that query the email address of a user that has an address_parts of 4 (from field).

Currently, I have separated the query to obtain the from email address into its own query, but that means I have to run it for each message (so i can find the from email address), when I'd prefer to just get it from the query that grabs the messages.

This is one of the first posts Ive made on DBA Stack. I recognize its not complete. Please assist me in making this question better

Thanks.

Inconsistent trigram indexing runtimes?

Posted: 13 Jul 2013 06:14 PM PDT

I have a table residences...

I installed trgm: create extension pg_trgm;

After adding trigram indexes to both grp and name columns using:

CREATE INDEX residences_name_trgm ON residences USING GIN (name gin_trgm_ops);  CREATE INDEX residences_grp_trgm ON residences USING GIN (grp gin_trgm_ops);

And checking performance of a simple query... it appears that the total runtime for a query on the name column runs ~100x faster. Why is this?

EXPLAIN ANALYZE SELECT * FROM residences WHERE name ILIKE '%Sutton%';                                                              QUERY PLAN                                                              ----------------------------------------------------------------------------------------------------------------------------------   Bitmap Heap Scan on residences  (cost=36.02..47.90 rows=3 width=1872) (actual time=0.390..0.720 rows=21 loops=1)     Recheck Cond: ((name)::text ~~ '%Sutton%'::text)     ->  Bitmap Index Scan on residences_name_trgm_gin  (cost=0.00..36.02 rows=3 width=0) (actual time=0.354..0.354 rows=21 loops=1)           Index Cond: ((name)::text ~~ '%Sutton%'::text)   Total runtime: 0.814 ms  (5 rows)

and

EXPLAIN ANALYZE SELECT * FROM residences WHERE grp ILIKE '%Sutton%';                                                                    QUERY PLAN                                                                     -----------------------------------------------------------------------------------------------------------------------------------------------   Bitmap Heap Scan on residences  (cost=97.93..8879.41 rows=5927 width=1872) (actual time=5.516..115.634 rows=5968 loops=1)     Recheck Cond: ((grp)::text ~~ '%Sutton%'::text)     ->  Bitmap Index Scan on residences_grp_trgm_gin  (cost=0.00..96.45 rows=5927 width=0) (actual time=4.366..4.366 rows=5968 loops=1)           Index Cond: ((grp)::text ~~ '%Sutton%'::text)   Total runtime: 119.779 ms  (5 rows)

For reference, both name and grp columns are CHARACTER VARYING(50).

Futhermore... counting distinct values in each column yields:

SELECT COUNT(DISTINCT grp) FROM residences; -> 421

SELECT COUNT(DISTINCT name) FROM residences -> 7750

That is, the name column actually has ~20x more distinct values, but returns values much faster.

More information on this technique can be found on depesz's article WAITING FOR 9.1 – FASTER LIKE/ILIKE and another similar tutorial on the PaliminoDB blog.

How to use a database accross two availability zones

Posted: 13 Jul 2013 06:07 PM PDT

I have servers in the USA and Japan for my website, at the moment they are both connecting to a MySQL server in the USA, which is obviously perfect for my local USA clients, but quite slow for my Japanese clients.

What is the best strategy for reducing latency? How can I introduce a local mysql server for the Japanese end and sync these up?

I have considered master-master replication but this appears to be an incomplete and unstable solution.

Are there any other suggestions?

Should I use a separate table to store image file names?

Posted: 13 Jul 2013 04:58 PM PDT

I am learning about database design and I'm writing a Java GUI program with a back-end database. The main table in my database stores a different product in each row. I have some columns such as: product_id (PK), price, stock_quantity, etc. I also have eight columns that store the names of the file names for that product's images: img_file_1, img_file_2 ... img_file_8. The Java program uses those file names to find the images to display on the screen for that product.

Is this poor design? Should I be storing these file names in their own table, and adding a foreign key to that table in my main table?

The program works fine as is, but I want to make sure I am learning good habits.

SPI postgresql - insert data into table from postgres.c

Posted: 13 Jul 2013 04:45 PM PDT

I'm working inside postgres.c of postgresql. I need to insert some information in a table and I'm trying to do it with SPI using these three lines:

SPI_connect();  SPI_exec("INSERT INTO testvalues (11,6)", 5);  SPI_finish();

but when I start the server and I send a query I get segmentation fault:

LOG:  server process (PID 13856) was terminated by signal 11: Segmentation fault  LOG:  terminating any other active server processes  WARNING:  terminating connection because of crash of another server process  DETAIL:  The postmaster has commanded this server process to roll back the current transaction and exit, because another server process exited abnormally and possibly corrupted shared memory.  HINT:  In a moment you should be able to reconnect to the database and repeat your command.  FATAL:  the database system is in recovery mode  LOG:  all server processes terminated; reinitializing  LOG:  database system was interrupted; last known up at 2013-07-14 00:40:22 CEST  LOG:  database system was not properly shut down; automatic recovery in progress  LOG:  record with zero length at 0/17D7368  LOG:  redo is not required  LOG:  autovacuum launcher started  LOG:  database system is ready to accept connections

Do you have any idea how I can fix it? Is there a better way to insert data from the postgres.c?

MariaDB CUstom Functions

Posted: 13 Jul 2013 10:02 AM PDT

In MySQL, I wrote functions in C++, compiled them as .so, and added them to MySQL. I am trying to find a website that shows an example for doing the same in MariaDB. I cannot find any instructions, tutorials, or examples of writing custom functions in MariaDB. Does anyone know of any or is anyone willing to write a very simple one here - like a md10 function that returns concat(md5(val),md5(val))?

Just to make it clear - I am looking to write compiled code, not define a function in SQL. It isn't just for speed. We need to clone the boxes and have all MariaDB engines fully functional.

Error: "Storage Engine for the Table Doesn't Support Nullable Columns" (SequelPro)

Posted: 13 Jul 2013 01:20 PM PDT

I'm trying to load a very normal .csv file (that was created from Excel 2011 for Mac) into SequelPro (using MySQL) with my Mac -- and I've recently started getting this error consistently. Can anybody let me know what it is and how to fix it?

An error occurred while trying to add the new table 'wblist' by    CREATE TABLE `wblist` (  `FILE` VARCHAR(255),   `FIRSTNAME` VARCHAR(255),   `MIDDLE` VARCHAR(255),   `LASTNAME` VARCHAR(255),   `FULLNAME` VARCHAR(255),   `GENDER` VARCHAR(255),   `ADDRESS` VARCHAR(255),   `CITY` VARCHAR(255),   `STATE` VARCHAR(255),   `ZIP` VARCHAR(255),   `PHONE` BIGINT(11),   `UNIT` VARCHAR(255),   `JOB` VARCHAR(255),   `AREA` VARCHAR(255),   `TIME` VARCHAR(255),   `MAILINGADDRESS` VARCHAR(255),   `MAILINGCITY` VARCHAR(255),   `MAILINGSTATE` VARCHAR(255),   `MAILINGZIP` VARCHAR(255),   `ID` BIGINT(11),   `CONFIDENCE` VARCHAR(255),   `BIRTHDATE` VARCHAR(255),   `AGE` INT(11),   `RACE` VARCHAR(255),   `ETHNICITY` VARCHAR(255),   `RELIGION` VARCHAR(255),   `PARTY` VARCHAR(255),   `REGISTRATIONDATE` VARCHAR(255),   `VOTERSTATUS` VARCHAR(255),   `OtherPhone` VARCHAR(255),   `POSSIBLEADDRESS` VARCHAR(255),   `POSSIBLEMAILADDRESS` VARCHAR(255),   `RECID` VARCHAR(255)) ENGINE=CSV;    MySQL said: The storage engine for the table doesn't support nullable columns

This is stopping me before I'm able to import the table. Thanks for the help!

IN and NOT IN for XML column

Posted: 13 Jul 2013 09:40 AM PDT

I have a table with a xml column. Xml is similar to 

<Root>    <Row>      <user>abc</user>      <Rowid>1</Rowid>    </Row>    <Row>      <user>vf</user>      <Rowid>2</Rowid>    </Row>    <Row>      <user>ert</user>      <Rowid>3</Rowid>    </Row>    <Maxrowid>3</Maxrowid>  </Root>

Now below query return sl_no column and myxmlcolumn of rows containing xml column having values 'abc' or 'xyz' in node 'user'().Below query i am using similar to IN option of sql. 

SELECT      [mytable].[Sl_no],      [mytable].[myxmlcolumn]      FROM [mydb].dbo.[mytable]      WHERE          [myxmlcolumn].exist('for $x in /Root/Row where (($x/user[fn:upper-case(.)=(''ABC'',''XYZ'')])) return $x') > 0

I want similar kind of query which does same work as sql 'NOT IN' does. That is in my case i want rows not having values 'abc' or 'xyz' in node 'user'() in xml column. So please help me on this.

alter or recreate?

Posted: 13 Jul 2013 05:38 AM PDT

I am developing a website whose functionality includes allowing user to submit some information to database.

Sometimes users will want to save those info and submit them latter. So they will be written into database.

Since these information have quite complicated structure(They uses several tables) it's kinda tricky to modify(Using alter lots of times) I wonder if it would be more efficient to delete the old info altogether and insert completely new info into database even though the new one and old one are largely the same?

Thanks in advance!

2NF relations of Normalization [on hold]

Posted: 13 Jul 2013 02:11 AM PDT

Is it possible to draw the ERD of 2NF to be 1:M diagram, because the question table is about agent_id and event_id. So it is impossible that customer will reserve the event by using two agent for one event, If I understand correctly.

Please help me and thank you!

Table about reserving event

Agent     agent_id,     agent_name,     agent_tel,     agent_salary    Event     even_Id,     event_name,     event_cusname,     event_date

Cassandra multidatacenter configuration with 1 external ip

Posted: 13 Jul 2013 06:07 PM PDT

I'm trying to setup a multi-datacenter Cassandra cluster. The problem is that my datacenters have only 1 external IP (wan IP), I can setup port forwarding on the data centers switchs to access each node from the outside world using a different port, but I don't know how to setup the cassandra.yaml file properly.

Is there a way to setup a multidatacenter cassandra cluster in this scenario?.

Thanks in advance!

Dump PostgreSQL without DROP DATABASE

Posted: 13 Jul 2013 03:58 AM PDT

I want dump a database, but, without DROP DATABASE, and CREATE DATABASE instructions, in .backup / .sql (generate file).

Actually I use this command:

pg_dump --format=c --no-privileges --no-owner --file=/tmp/`hostname`-`date +%d%m%Y`.backup --username=admin --host=127.0.0.1 database_name

But, in top lines of file, I have this:

DROP DATABASE...  CREATE DATABASE...

So, I my case, I want duplicate this database, and, if I use this script with pg_restore I drop the other database (I don't want that).

optimizing MySQL for traffic analytics system

Posted: 13 Jul 2013 05:20 PM PDT

background :

I've developed a URL shortener system like Bitly with same features , so the system also tracks clickers info and represent as graphs to the person who has shorten the link as analytics data. currently I'm using MySQL and have a table to store click info with this schema:

visit_id (int)  ip (int)  date (datetime)  country  browser  device  os  referrer (varchar)  url_id (int)  //as foreign key to the shortened URL

and for now , just the url_id field has index

The system should represent click analytics in the time periods the user wants, for example past hour, past 24 hours , the past month , ...

for example to generate graphs for past month , I do following queries: 

SELECT all DAY(date) AS period, COUNT( * )                           FROM (                            SELECT *                           FROM visits                          WHERE url_id =  '$url_id'                          ) AS URL                          WHERE DATE > DATE_SUB( CURRENT_TIMESTAMP( ) , INTERVAL 1 MONTH )                           GROUP BY DAY( DATE )    //another query to display clicker browsers in this period  //another query to display clicker countries in this period  // ...

issues:

  • for a shortened link with about 500,000 clicks , it takes about 3-4 seconds to calculate just the first query , so for total queries about 10-12 seconds which is terrible.
  • lots of memory and CPU is needed to run such queries

questions :

1- how to improve and optimize the structure , so the analytics of high traffic links will be shown in less than 1 second(like bitly and similar web apps) and with less usage of CPU and RAM ? should I make an index on the fields date, country, browser, device, os, referrer ? if yes , how to do that for the field date because I should group clicks some times by DAY(date), sometimes by HOUR(date), sometimes by MINUTE(date) and ...

2- is MySQL suitable for this application? assume at maximum my application should handle 100 million links and 10 billion clicks on them totally. Should I consider switching to an NoSQL solution for example?

3- if MySQL is ok , is my database design and table structure proper and well designed for my application needs? or you have better recommendations and suggestions?

UPDATE: I made an index on column referrer but it didn't help at all and also damaged the performance and I think that's because of the low cardinality of this column (also others) and the big resulting index size related to the RAM of my server.

I think making index on these columns would not help to solve my problem, my idea is about one of these:

1- if using MySQL, maybe generating statistics using background processing for high traffic links is better instead of calculating lively at the user request.

2- using some caching solution like memcached to help MySQL with high traffic links.

3- using a NoSQL such as MongoDB and solutions like Map-Reduce which I am poorly familiar with and haven't used ever.

what do you think?

SSIS organization

Posted: 13 Jul 2013 06:48 AM PDT

I'm familiar with SSMS, but I've never used .net, c# or visual studio (been doing other things: Assembler, c, unix, vi, oracle, etc).

I'm trying to figure out basic 2012 SSIS ETL (no BI or data warehouse) and can't find anything that explains how all of these fit together:

catalog
solution
project
package any others?

Say I just want to build a simple test using SSIS, import a file into a table, which of the above are required, and in what order do I create them?

Also, do you have any good links to basic SSIS 2012 ETL overviews and/or how to articles.

LDAP in SQL Server 2012

Posted: 13 Jul 2013 04:58 PM PDT

In our current system, we query the LDAP using SQL Server 2008R2 but are moving to SQL Server 2012 where we have hit a snag. According to http://msdn.microsoft.com/en-us/library/cc707782.aspx one of the discontinued features of SQL Server 2012 is the AD helper service.

Does anyone know of a way to query the AD from SQL Server 2012?

Percona Xtrabackup on Suse EL 10 -- Failing

Posted: 13 Jul 2013 05:58 AM PDT

I have evaluated Percona Xtrabackup successfully and it works like a charm on my several CentOS / RHEL servers! Hats off to the team!

But my problem starts when I tried to install this on one of my client's Suse EL 10 server. I believe Xtrabackup is not natively supported on Suse. But being an RPM based platform, I tried installing RHEL5 rpms from percona site. But they don't work. They install (a fairly older version 1.5.x) but when run they throw the error like

Command I am running : /usr/bin/innobackupex --ibbackup=xtrabackup --defaults-file=/etc/my.cnf --user=some_user --password=password /root/backups

Error shown is : innobackupex: fatal error: no 'mysqld' group in MySQL options

I have made sure that my.cnf exist, it contains all necessary parametes like datadir / log files related parameters.

See if you can help me with this and let me know if you need any more help / logs that I can provide.

MySQL replication - slave update

Posted: 13 Jul 2013 03:58 AM PDT

I have a master slave setup of MySQL and my question is that if I make any changes in the slave database: 

1. Will it mess up the sync in any way  2. Will the changes get overwritten from the master during the next replication event   3. Can I make above (2) option happen ?

Fulltext search limiting users possibilities

Posted: 13 Jul 2013 07:57 PM PDT

We have a web site with a search box. The search uses a fulltext index column. However, we just cant feed the text the user has input into this stored procedure.

CREATE PROCEDURE [dbo].[SearchPages]      @Term varchar(200)  AS  BEGIN      SELECT pc.SearchData, from PageContent pc      where contains(pc.SearchData, @Term)  END

Searches with space in them fails, also there is a pletora of sql functions we do not want to expose to the users like

NEAR((bike,control), 10, TRUE)

and the like or binary operators like AND or OR.

So we need to escape the term in some way.

One way that immediately comes in mind is to put an AND between every word. However, it doesnt feel like thats a good solution. It reminds me to much about 1998 style coding.

So is there any better suggestions?

Having too many connection problem

Posted: 13 Jul 2013 02:06 AM PDT

We have an magento store and having 9K products and 2 store views the trouble is we are getting too many conection error while the site is in normal loads.

1) We have 100 connectoin open in mysql.
2) We have PhP/apache/Mysql on the same box with 16Gb Ram Quad Core processor.
3) A cron run on each 5 mins for submitting the feed to google and sync with amazon.


Any idea how we can overcome this problem.

Many thanks

Primary replica set server goes secondary after secondary fails

Posted: 13 Jul 2013 04:26 PM PDT

I have a 2 servers replica set that, after the secondary fails the primary goes into secondary mode while the secondary is in STARTUP2 (recovering). The problem with this is that I can't use the collection stored in that replica set freely, I'm getting errors trying to use the collection:

pymongo.errors.OperationFailure: database error: ReplicaSetMonitor no master found for set: rs2

Sometimes if I restart the mongod instances, the server rs2-1 is the primary for a while, but after some time (while the secondary is recovering) I see this in the logs of rs2-1 (the primary):

Tue May  7 17:43:40.677 [rsHealthPoll] replSet member XXX.XXX.XXX.XXX:27017 is now in state DOWN  Tue May  7 17:43:40.677 [rsMgr] can't see a majority of the set, relinquishing primary  Tue May  7 17:43:40.682 [rsMgr] replSet relinquishing primary state  Tue May  7 17:43:40.682 [rsMgr] replSet SECONDARY  Tue May  7 17:43:40.682 [rsMgr] replSet closing client sockets after relinquishing primary

Is there an easy way to make the primary keep being primary after the secondary fails? Am I doing something wrong?

Thanks in advance!

MySQL backup InnoDB

Posted: 13 Jul 2013 12:58 PM PDT

I have a VoIP server running 24x7. At low peak hour at lease 150+ users are connected. My server has MySQL running with InnoDB engine on Windows 2008 platform. I like to take at least 2 times full database backup without shutting down my service.

As per Peter Zaitsev - the founder of percona, mysqldump –single-transaction is not always good.

read here if you are interested

As I'm not a DBA, I like to know in my scenario, which would be best solution to take a database backup?

Thanks,

Strange characters in mysqlbinlog output

Posted: 13 Jul 2013 10:58 AM PDT

Has anyone experienced this? Data replicates fine but when output in mysqlbinlog there are hidden characters that break the input?

  • mysqlbinlog Ver 3.3 for Linux at x86_64
  • mysql 5.5.28 server

Thanks! Julie

12c Grid Agent installation issues - Shell path is incorrect or not defined

Posted: 13 Jul 2013 04:58 AM PDT

I am installing 12c Grid Control agent on my server via Enterprise manager.

The targer server is a UNIX server with AIX OS on it. I add target host manually, enter authentication information, host name, installation directory, etc. and all goes well untill I click "Deploy Agent" button, then I get the following error:

"Shell path is incorrect or not defined.:/bin/bash(SH_PATH),-c(SH_ARGS) on host MyHostName"

and details are :

"Check the property values in the following files in this order, ssPaths_.properties or sPaths.properties or Paths.properties, in "/u02/app/oracle/middleware/oms/oui/prov/resources"

There isn't /u02/app" directory on my target server, only /u01/app/oracle" so I assume the above directory is on the same server as Enterprise manager.

Although I did find a directory where I have files of the format ssPaths_.properties - /u01/app/oracle/product/11.2.0.3/oui/prov/resources

Since my platform ia AIX, I used file ssPaths_aix.properties

On my target server if I run which bash I get /usr/bin/bash and which ssh returns /usr/bin/ssh ahd which sh returns /usr/bin/sh

So I edited the ssPaths_aix.properties file so that SH_PATH=/usr/bin/sh but still getting the same error. I have also tried setting to SH_PATH=/usr/bin/bash but nothing seems to make any difference. I am thinking one possibility would be I am editing wrong file.

Can anyone help?

How to increase fast backup and restore of 500GB database using mysqldump?

Posted: 13 Jul 2013 02:57 AM PDT

There is a database A size of 500GB. Tables in database A contains both MyISAM and INNODB tables. MyISAM tables are master tables and Innodb tables are main transaction tables.

Backup and restore using mysql dump taking quit a long time or days.

  • max_allowed_packet=1G
  • foreign_key_checks=0
  • auto_commit = off

Connecting to a SQL Server database from a Flash program

Posted: 13 Jul 2013 11:58 AM PDT

I currently have the ability to utilize Microsoft SQL Server 2012. I am developing a project with Adobe Flash Builder 4.7.

If I link my database with Adobe Flash Builder is there any additional steps I must take in order to make the database live, or as long as my computer is running will this database be accessible from any device that is utilizing it?

In other words is this a LAN only system or does it automatically make itself available for the programs I link to it?

Oracle Express edition on Ubuntu - control file missing

Posted: 13 Jul 2013 05:58 PM PDT

I have installed the Oracle Express edition on Ubuntu as mentioned here.

I am facing issues when I try to create a sample table.

Started oracle

$ sudo service oracle-xe start   Starting Oracle Database 11g Express Edition instance.

Started sqlplus

$ sqlplus / as sysdba

Executed the CREATE command

SQL> CREATE TABLE SAMPLE (ID NUMBER);  CREATE TABLE SAMPLE (ID NUMBER)  *** ERROR at line 1: ORA-01109: database not open**

After a series of research on web, I tried to shutdown and restart oracle:

Shutdown command

SQL> shutdown  ORA-01507: database not mounted  ORACLE instance shut down.

Started the oracle instance

SQL> startup    ORACLE instance started.  Total System Global Area  688959488 bytes Fixed Size                   2229688 bytes Variable Size             411044424 bytes Database  Buffers          272629760 bytes Redo Buffers                3055616  bytes ORA-00205: error in identifying control file, check alert log  for more info

I realized that the control file is missing at /u01/app/oracle/oradata/XE. XE is the DB name.

So I tried to create the control file as follows:

SQL> CREATE CONTROlFILE SET DATABASE XE RESETLOGS;    Control file created.

Tried to create the sample table again

SQL> CREATE TABLE SAMPLE(ID NUMBER);      CREATE TABLE SAMPLE(ID NUMBER)    ERROR at line 1: ORA-01109: database not open

So I tried to issue the following command

SQL> ALTER DATABASE OPEN RESETLOGS;  ALTER DATABASE OPEN RESETLOGS    ERROR at line 1:  ORA-01194: file 1 needs more recovery to be consistent**  ORA-01110: data file 1: '/u01/app/oracle/product/11.2.0/xe/dbs/dbs1XE.dbf'

What should be done next? I am clueless as I am not a database guy.

Note:

Output of 

$ lsnrctl services    LSNRCTL for Linux: Version 11.2.0.2.0 - Production on 04-JAN-2013 09:15:37    Copyright (c) 1991, 2011, Oracle.  All rights reserved.    Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC_FOR_XE)))    Services Summary...    Service "PLSExtProc" has 1 instance(s).   Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...      Handler(s):        "DEDICATED" established:0 refused:0           LOCAL SERVER  Service "XE" has 1 instance(s).    Instance "XE", status READY, has 1 handler(s) for this service...      Handler(s):        "DEDICATED" established:0 refused:0 state:ready           LOCAL SERVER  The command completed successfully

How do I minimise logging during a large insert?

Posted: 13 Jul 2013 01:23 PM PDT

During a nightly scheduled task I am creating a large table from scratch using select into and then adding a clustered index. I would like to minimise the amount of logging during the whole process as I have limited bandwidth for log backup shipping.

I'm happy to consider an alternative approach if that would help.
Posted by at

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)

Search This Blog