texts: hstore for versioning fields in postgresql

texts:
I have a postgresql database that will hold about 50 tables, each of them having about 15 fields, it would have at least 300.000 rows on each table.

In order to track the changes done on each field I am thinking on create a table defined by:

CREATE TABLE fieldhistory(tableid int, fieldid int, id bigint,value hstore);
CREATE INDEX fieldhistory_index ON fieldhistory(tableid, fieldid, id);

I would expect the table to grow and grow, and retrieve from fieldhistory data only by tableid, fieldid and id.

When the tables are modified I would add a new record with something like:

SELECT upserthistory(1,1,1,'first update','115435','3');
SELECT upserthistory(1,1,1,'second update','115435','3');

where upserthistory is defined by :

CREATE OR REPLACE FUNCTION upserthistory(key1 INT, key2 INT, key3 BIGINT, data TEXT, theuser TEXT, whend TEXT) RETURNS VOID AS
$$
BEGIN
    LOOP
        -- first try to update the key
        UPDATE fieldhistory SET value = value || ((whend||'.'||theuser) => data) WHERE tableid = key1 AND fieldid = key2 AND id = key3;
        IF found THEN
            RETURN;
        END IF;
        -- not there, so try to insert the key
        -- if someone else inserts the same key concurrently,
        -- we could get a unique-key failure
        BEGIN
            INSERT INTO fieldhistory(tableid,fieldid,id,value) VALUES (key1, key2, key3, hstore(array[whend||'.'||theuser,data]) );
            RETURN;
        EXCEPTION WHEN unique_violation THEN
            -- Do nothing, and loop to try the UPDATE again.
        END;
    END LOOP;
END;
$$
LANGUAGE plpgsql;

Are there better approaches to accomplish this?  the table would grow max to 225 millions of rows, or would it be better to have 50 tables of 4.5 millions of rows at max each? noting that the actual trace of each field will go to the hstore.


texts: MySQL any way to import a huge (32 GB) sql dump faster?

texts:
I have this huge 32 GB SQL dump that I need to import into MySQL. I haven't had to import such a huge SQL dump before. I did the usual:

mysql -uroot dbname < dbname.sql

It is taking too long. There is a table with around 300 million rows, it's gotten to 1.5 million in around 3 hours. So, it seems that the whole thing would take 600 hours (that's 24 days) and is impractical. So my question is, is there a faster way to do this?

Further Info/Findings

The tables are all InnoDB and there are no foreign keys defined. There are, however, many indexes.
I do not have access to the original server and DB so I cannot make a new back up or do a "hot" copy etc.
Setting innodb_flush_log_at_trx_commit = 2 as suggested here seems to make no (clearly visible/exponential) improvement.
Server stats during the import (from MySQL Workbench): https://imgflip.com/gif/ed0c8.
MySQL version is 5.6.20 community.
innodb_buffer_pool_size = 16M and innodb_log_buffer_size = 8M. Do I need to increase these?


texts: Postgresql: Move database to new tablespace

texts: I'd like to completely move a database (tables, indexes, etc.) to a new tablespace. I know how to move each item individually but is there a way to move it all in one go? I'm using postgresql 9.1.


texts: Cannot select features to install from SQL Server 2005 SP4 installation process

texts:
The issue: I cannot select MSSQLSERVER from the feature list I want to update as part of the SQL Server 2005 x64 Service Pack 4 installation process.
Please note exe file is being raised from C:\ with full administrador privilegies.

Server details: Windows Server 2008 R2 Standard x64 bits
SQL Server details: Microsoft SQL Server 2005 - 9.00.5294.00 (X64) (Build 7601: Service Pack 1)
Service Package Details: SQLServer2005SP4-KB2463332-x64-ENU.exe

I have spent the last hour trying to identify where the issue is or similar experiencies over internet but I didn't get it until now.
Any comments will be appreciated.

Thanks in advance


texts:
I have a table with the following data:

    unit_codes
    kind    | code
-------------------------
    UNIT    | PS3b
    UNIT    | PS3B
    UNIT    | PS3C

If I run this select I get the correct result.

select * from unit_codes where kind = 'UNIT';

Result:

UNIT    PS3b
UNIT    PS3B
UNIT    PS3C

If I filter on the code column I get no rows.

select * from unit_codes where code = 'PS3C';

No rows are retuned.

I suspect there is a non visible character on the code column for some rows. The problem started after some OS patches were installed.

We are running PostgreSQL 9.2.4 on sparc-sun-solaris2.10, compiled by cc: Sun C 5.9 SunOS_sparc 2007/05/03, 32-bit.


texts: Keeping MySQL staging database on same server updated from production

texts:
I just migrated my MySQL 5.5.20 to Amazon RDS from MySQL on EC2. The one thing I'm having difficulty with is how to keep my staging database updated from production. Before, I just ran a nightly mysqlhotcopy to overwrite staging from production and all was well. However, RDS doesn't give me shell access so I think I need a pure SQL solution to avoid doing a mysqldump to another server, and then restoring back to the RDS server, which seems unnecessary since I just want to copy to database-to-database on the same server. I know RDS supports read replicas, but I need a writable replica, and I don't want to have to fire up another instance for my staging database, which doesn't see a lot of action.

What's the best way to accomplish a database-to-database copy on the same server in pure SQL?


texts: is it good to add index on column used in group by clause?

texts:
I have a query like-

select x, sum(y) from xyz where z>=100 group by x order by sum(y) desc;

will it be faster if I am going to add index on x?


texts: What is the point of encryption at rest?

texts:
I am wondering what is the point of encryption at rest?
It is said that a hacker cannot steal the database and look through it. I'm afraid that it does not protect the database while the key is stored on the same machine.
Let's have a look at my MariaDB settings:
[mysqld]
# at-rest encryption
plugin_load_add              = file_key_management
file_key_management_filename = /etc/mysql/rest/keyfile.enc
file_key_management_filekey  = FILE:/etc/mysql/rest/keyfile.passwd
file_key_management_encryption_algorithm = AES_CBC

If keyfile.passwd and keyfile.enc are on the same machine, the hackers will steal all of them together and decrypt keyfile.enc by keyfile.passwd and then they will obtain the key in a plain text and via the key, they decrypt the encrypted database.
Where will be the protection?


texts:
Recently I got stucked with a table where its grown to +25million and I have got a query that's doing a JOIN on it. The query take 2.5hours to execute even though I got Good 96GB RAM and RAID10 with BBU config.

TableA has 958090  records
TablesB has 25Million Records.

The query join is simple.

select tableA.col1, tablesA.col2, tablesA.directory, tableB.col1, tableB.col2
from tableA left join tableB ON tableA.col1=tableB.col1 AND tablesA.col2=tableB.col2
where tablesA.col2='123'
OTHER BY tablesA.directory desc;

tablesA.directory is VARCHAR(950);
INDEX ON tablesA.col2
INDEX ON tableB.col1 and tableB.col2

Please help me how to gain performance in this scenario.


texts: cascade delete doesn't work even though constraint is added

texts:
I created the following table:

CREATE TABLE message_link
(
  id serial NOT NULL,
  thread_id integer,
  message_id integer,
  user_id integer,
  read boolean NOT NULL,
  CONSTRAINT message_link_pkey PRIMARY KEY (id),
  CONSTRAINT "FK_9F390228537A1329" FOREIGN KEY (message_id)
     REFERENCES message (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE CASCADE
  CONSTRAINT fk_9f390228a76ed395 FOREIGN KEY (user_id)
     REFERENCES fos_user_user (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT fk_9f390228e2904019 FOREIGN KEY (thread_id)
     REFERENCES message_thread (id) MATCH SIMPLE
     ON UPDATE NO ACTION ON DELETE CASCADE
)

The cascade delete constraints I added on message_id field don't work either from code or from pgadmin. When I delete a record from the message table, referenced records from the message_link table aren't deleted.

Everything works locally (vagrant) where I am using PostgreSQL 9.3 however staging is using 9.1.14. I don't understand how this can be linked to the PostgreSQL version?

Why isn't this cascade delete working?


texts: "Linked server does not contain table" error when using VFPOLEDB provider linked server

texts:
I am receiving the below error when attempting to run any queries via a linked server in SQL Server 2008 R2 using the Visual Fox Prox OLE DB Provider v9.0.0.3504.

I am attempting this as I need to import data from VFP .dbf files into a SQL database.

Error:

Msg 7314, Level 16, State 1, Line 1
The OLE DB provider "VFPOLEDB" for linked server "vfp" does not contain the table "pat". The table either does not exist or the current user does not have permissions on that table.

Example Query:

select * from vfp...pat

I have ensured that there are 'Full Control' AD permissions to the free table directory for the service account that SQL Server is running and the user account that is logged into SSMS on the server.
The server OS is Windows Server 2003 SP2 x86.
TSQL for linked server creation is below:

EXEC master.dbo.sp_addlinkedserver @server = N'VFP', @srvproduct=N'Visual FoxPro', @provider=N'VFPOLEDB', @datasrc=N'"C:\VFP"', @provstr=N'VFPOLEDB.1'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'VFP',@useself=N'False',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL

I've also used an alternate @provstr value of

Provider =vfpoledb; Data Source =C:\VFP\; Collating Sequence =general;

So my question is have I missed anything that I need to configure? Can anyone provide a few pointers as to what I should check to resolve this problem?

Thanks in advance

EDIT:

I should mention that "allow inprocess" is configured for the VFPOLEDB provider and queries fail when using either 4 part names or OPENQUERY().


texts: How to check/know the highest run queries

texts:
I would like to gather statistics on the highest run queries in my database.

For that I need to know how I can track queries so I can create views or materialized views to cache the results of the highest run queries since I have a big DB.


texts: How to dump to csv valid json with Postgresql?

texts:
I am trying to dump some data as json with:

\copy (SELECT json_build_object('areaSqft',area_sqft, 'titleNos',title_nos, 'buildingIds',building_ids, 'geometry',geom_json) FROM my_data) to my_data.csv with delimiter ',' csv header

what I am expecting is a valid json per row, but what I get is:

"{""areaSqft"": 214.394254595041, ""geometry"": {""type"": ""MultiPolygon"", ""coordinates"": [[[[0.000015, 51.449107], [0.000154, 51.441108], [0.000238, 51.44111], [0.00024, 51.441052], [0.000137, 51.441051], [0.000041, 51.441049], [0.000015, 51.441107]]]]}, ""titleNos"": [""ZB78669""], ""buildingIds"": [7521141, 9530393, 7530394]}"

There are extra " as first and last character and "" around instead of single ".

How can I get a valid json stripping unnecessary quotes?


texts: UPDATE query in small tables taking forever

texts:
I'm having trouble creating a UPDATE query to perform the following:

I have two tables, ed is a 11k rows table with areas from multiple constructions and a key lote_key wich links to the table unid, wich has 7k rows.

For each row in unid I have one or multiple correspondent rows in ed and what I want is to sum the areas of each construction and put them in a column area_const in unid

The code I wrote is the following:

UPDATE data.unid SET
    area_const = calc.area
    FROM
    (
        SELECT ed.lote_key, SUM(ST_Area(ed.geom)) AS area
        FROM data.ed
        GROUP BY ed.lote_key
    ) AS calc
    WHERE unid.lote_key = calc.lote_key

I don't know what I'm doing wrong, but the query is taking forever to perform, and by forever i mean that I haven't seen it through yet, I need this to be a trigger function so I can't have this code taking that long.

I ran a EXPLAIN to see the costs of the operation and it seems ordinary, as follows:

PS: I'm not able to post a EXPLAIN ANALYZE since the query has not been completed so far

    "Update on unid un  (cost=1531.07..1773.16 rows=5498 width=463)"
"  ->  Hash Join  (cost=1531.07..1773.16 rows=5498 width=463)"
"        Hash Cond: ((calc.lote_key)::text = (un.lote_key)::text)"
"        ->  Subquery Scan on calc  (cost=953.08..1026.10 rows=3651 width=72)"
"              ->  HashAggregate  (cost=953.08..989.59 rows=3651 width=24)"
"                    Group Key: ed.lote_key"
"                    ->  Seq Scan on ed ed  (cost=0.00..605.77 rows=11577 width=179)"
"        ->  Hash  (cost=488.55..488.55 rows=7155 width=407)"
"              ->  Seq Scan on unid un  (cost=0.00..488.55 rows=7155 width=407)"

I have another UPDATE code in my db with costs like this one that goes just fine, that got me thinking my mistake was logical or something

If I run only the select part, it goes just fine


texts:
I am pretty sure that Differential Backups with Replication is ok, but I am not finding anything that clearly says it. Nor am I finding anything indicating issues with it. Just looking for validation that two are completely compatible.

I have a server that has transaction replication and am considering a change to start using differential backups.

If the replication has to be rebuilt, a snapshot "copy" of the database is taken.  I am not seeing any way to ensure that happens with COPY_ONLY.

  Transactional replication is implemented by the SQL Server Snapshot Agent, Log Reader Agent, and Distribution Agent. The Snapshot Agent prepares snapshot files containing schema and data of published tables and database objects, stores the files in the snapshot folder, and records synchronization jobs in the distribution database on the Distributor.

I know that a full back up that does not use COPY_ONLY will distroy the backup chain if using Differential Backups.

  A copy-only backup is a SQL Server backup that is independent of the sequence of conventional SQL Server backups. Usually, taking a backup changes the database and affects how later backups are restored.

Question
Is there anything I need to address or adjust for if using Differential Backups and Replication?


texts: Performance first low, then high after adding index, then keep high after drop index

texts:
I am testing performance of a complicated select query. It is very slow.

Using explain, I have found a ALL in type COLUMN. Then I ADD INDEX. The result comes out fast. However, when I DROP the INDEX I added, the result comes out fast too.

I have set query_cache_size=0. There is no cache for each query result.

I am a new learner. Could some experts tell me why and what other items I need to check? How can I go back to the first slow scenario and verify that it is the index I need to add which has big impact of the performance?

Sample:

select col1,col2 from tbl1 groupby col1,col2;

show create table shows "index(col1), index(col2)"

explain shows the select query have type=ALL.

Then I add index(col1, col2) which made explain type=RANGE.


texts: In Oracle shared server mode, what is a virtual circuit?

texts:
I read that shared server mode allows dispatcher processes to use two new SGA sturctures : queues (request and response) and UGA (which is just PGA "flushed" in SGA)

So with a that, a single dispatcher can handle several client connections.

My issue is I saw an expression I don't understand : "virtual circuit". What does it do and where exactly is it positionned ?


texts:
I'm trying to generate unique purchase order numbers that start at 1 and increment by 1. I have a PONumber table created using this script:

CREATE TABLE [dbo].[PONumbers]
(
  [PONumberPK] [int] IDENTITY(1,1) NOT NULL,
  [NewPONo] [bit] NOT NULL,
  [DateInserted] [datetime] NOT NULL DEFAULT GETDATE(),
  CONSTRAINT [PONumbersPK] PRIMARY KEY CLUSTERED ([PONumberPK] ASC)
);

And a stored procedure created using this script:

CREATE PROCEDURE [dbo].[GetPONumber]
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO [dbo].[PONumbers]([NewPONo]) VALUES(1);
    SELECT SCOPE_IDENTITY() AS PONumber;
END

At the time of creation, this works fine. When the stored procedure runs, it starts at the desired number and increments by 1.

The strange thing is that, if I shut down or hibernate my computer, then the next time the procedure runs, the sequence has advanced by almost 1000.

See results below:

You can see that the number jumped from 8 to 1002!

Why is this happening?
How do I ensure that numbers aren't skipped like that?
All I need is for SQL to generate numbers that are:

a) Guaranteed unique.
b) increment by the desired amount.

I admit I'm not a SQL expert. Do I misunderstand what SCOPE_IDENTITY() does?  Should I be using a different approach? I looked into sequences in SQL 2012+, but Microsoft says that they are not guaranteed to be unique by default.


texts: SQL Query that Blocks itself in Adventure Works

texts: I know it can be common for a query to block itself, especially if the query is from a report or SSIS package. What I am trying to do is create such a query against the adventureworks database for testing purposes. Have a script that auto-kills any process that is blocking for over 5 minutes but want to exclude those that are blocking them self and just haven't been able to figure one out to test against.


texts: Return table based on 1, 2, 3, 4, 5 or 0 selected search options

texts:
Scenario: Lets say I have several tables for a software ticketing system: Developer, Supervisor, TicketType, TicketStatus and a Ticket table to join them on. Assume I have done my inner joins and now have a complete table listing all assigned Ticket info.

Id|TicketName|TicketStatus|Developer|Supervisor|DateAssigned| DateDue  |
------------------------------------------------------------------------------
         1  |  Report  |   Open     |   Josh  |    Bob   | 20130702   | 20130705
         2  |   App    |   Pending  |   Brian |    Bob   | 20130701   | 20130802
         3  |   App    |   Closed   |   Brian | Hannibal | 20130701   | 2013----

Obviously the SQL for this is pretty basic:

SELECT TicketId, TicketName, TicketStatus ...
FROM Ticket INNER JOIN TicketType ON [Ticket].TicketID = [TicketType].TicketName
     INNER JOIN [Status] ON .... ....

So that's the DEFAULT static portion of the report. This is what initializes first, however the client, customer, manager, etc. wants the option of selecting zero, one, or more fields to sort data on for all fields in the report. For example:

User may choose 'app' from the TicketName category, 'Josh' as Developer, and BETWEEN date1 AND date2, while TicketStatus Remains NULL or '  '. The table should display all 'app' tickets assigned to 'Josh' between selected dates, while not breaking due to TicketStatus not being selected. Any ticket status should appear.

User could choose not to select any values, in which case the report should default to the example above, without breaking. Meaning I cannot use NULL as a place holder bacause it would cause the initial report to not initialize (unless I did something wrong?)

User can basically mix and match any variation of the fields and the table should sort on those selected fields.

Question: How can someone return a table based on all or no options     being selected, without breaking the query?

Additional Info:

Using SQL Server 2008 r2, Business Intelligence Development Studio, T-SQL.

I have tried declaring variables and using LIKE statements, but this requires the user to select an option before the report will return anything. The User may not know what the field values are from memory.

CREATE PROCEDURE WorkAssignmentReport @Category varchar(25)
                                 ,@---Name varchar(25)
                                 ,@Stats varchar(25)
                                 ,@---Name  varchar(25)
                                 .........
                                 ......

FROM [Ticket]
INNER JOIN dbo.[TicketType] ON [Ticket].TicketTypeID = [TicketType].TicketTypeID
INNER JOIN dbo.[Status] ON [Ticket].Stat ....
.....

WHERE [TicketType].Name LIKE @Category
   AND [Ticket].Name LIKE @ReportName
   AND [Status].StatusName LIKE @Status
   AND [Phase].PhaseName LIKE @PhaseName
   AND (BA.FullName = @Name1 OR AD.FullName = @Name1)


texts: SQL Server 2016 still uses mixed_extent_allocation in TempDB

texts:
Today I was troubleshooting a problem on TempDB on SQL Server 2016 SP2 using extended events and added the mixed_extent_allocation and transaction_log event to my trace.

I expected mixed_extent_allocation wouldn't show up in my results because following query returned 0 as result:

select is_mixed_page_allocation_on
from sys.databases
where database_id=2

But to my suprise this event showed up several times. This was preceded by a transaction_log event with SGAM as context and operation LOP_SET_BITS.

This made me curious and I checked the content of the first SGAM page of TempdB:

DBCC TRACEON(3604)
dbcc page(tempdb,1,3,3)
DBCC TRACEOff(3604)

This is a snippet of the result:

╔═════════════════════════════════════════════╗
║ (1:0)        - (1:176)      = NOT ALLOCATED ║
║ (1:184)      -              =     ALLOCATED ║
║ (1:192)      -              = NOT ALLOCATED ║
║ (1:200)      - (1:208)      =     ALLOCATED ║
║ (1:216)      - (1:256)      = NOT ALLOCATED ║
║ (1:264)      -              =     ALLOCATED ║
║ (1:272)      -              = NOT ALLOCATED ║
║ (1:280)      -              =     ALLOCATED ║
║ (1:288)      - (1:296)      = NOT ALLOCATED ║
║ (1:304)      -              =     ALLOCATED ║
║ (1:312)      -              = NOT ALLOCATED ║
║ (1:320)      -              =     ALLOCATED ║
║ (1:328)      - (1:336)      = NOT ALLOCATED ║
║ (1:344)      -              =     ALLOCATED ║
║ (1:352)      - (1:65528)    = NOT ALLOCATED ║
╚═════════════════════════════════════════════╝

This made me conclude that mixed extents are still used.
I thought SQL Server 2016 only uses uniform extents (except for master, msdb and model). Or is my conclusion wrong?

Why am I still seeing the mixed_extent_allocation event popping up?


texts: multiple queries to multi_query while checking for affected rows

texts:
So I have the following update and conditional insert queries:

mysqli_query($con,"UPDATE ulogs SET invalid_hits = invalid_hits + 1 WHERE auser_id = $alink AND date = '$edate'");
if(mysqli_affected_rows($con) < 1) {
mysqli_query($con,"INSERT INTO ulogs (auser_id,date,invalid_hits,unique_hits,non_unique_hits,earned,ref_earned,bonus) VALUES ('$alink','$edate',1,0,0,0,0,0)");
}

mysqli_query($con,"UPDATE ref_rec SET invalid = invalid + 1 WHERE auser_id = $alink AND r_url = '$ref' LIMIT 1");
if(mysqli_affected_rows($con) < 1) {
mysqli_query($con,"INSERT INTO ref_rec (auser_id,r_url,count,invalid) VALUES ($alink,'$ref',0,1)");
}

mysqli_query($con,"UPDATE people SET invalid_v = invalid_v + 1 WHERE auser_id = $alink");
mysqli_query($con,"UPDATE global SET value = value + 1 WHERE name = 'invalid_h'");
mysqli_close($con);

Which I'm thinking of converting into a mysqli_multi_query format, however I have no idea on how to handle the "mysqli_affected_rows" part for the first two update queries.
The idea behind this is to (hopefully) gain some performance improvement and better consistency.
Any help would be appreciated. Thank you.


texts: How to execute a query based on the condition if a column exists?

texts:
I want to run the following query:

CREATE TABLE dummy AS
SELECT x, y, z
FROM table1
WHERE y!=0

then I want to do the following:

if z column has a non-null entry do:
1. copy the content of z into A
2. drop z
SELECT x,A
FROM Dummy

What the second query essentially tries to do is that copy those rows of z into A which have no missing values.
My question is how can we write query 2 and if there is a way to merge these two queries into one for optimisation?

Any help/advice much appreciated.


texts: Masking or substring data while replicating data

texts:
I  have a table that has  some  unencrypted data  that needs to be replicated to another server. I want to do :

Mask the data with an x except the last 5 characters e.g.  xxxx12345
just replicate the last 5 characters of the data in the column (substr the last 5 characters)

Is there a way I can do that other than writing a trigger on the subscriber ?

Thanks

R


texts: I cannot switch back to pgAdmin III's Database Designer's default font

texts:
I changed the default pgAdmin III's Database Designer's default font, which used to be MS Shell Dlg 2:

I cannot set it back to the default font, namely MS Shell Dlg 2. Why?

I don't see MS Shell Dlg 2 in the list of available fonts:

Typing MS Shell Dlg 2 myself does not work (it doesn't change the font):

Clicking on the "show more fonts" link also does show MS Shell Dlg 2:


texts:
On a datastax Cluster (6.7.8) I can run:
nodetool node_health --all
This gives a list of values for the cluster with IP addresses and health values - eg
IP Address    Node Health
1.2.3.4       1.0
1.2.3.5       0.5

Where the list of health values are between 1 and 0.
But it's not clear what this is telling you, when it is less that 1.0. I want another command to drill down into this value and find out all the things that make it up.
My question is: What does the node health value in a Datastax node compose of?


texts: Upgrading Greenplum DB that includes PostGIS extension

texts:
We've got a number of 4.2.3.2 Greenplum databases that are running PostGIS 1.4.  We are trying to get these databases onto a more recent version of Greenplum, such as 4.3.8.1 or the re-released version of 4.3.9.1, but we're running into a mess of issues when attempting to upgrade PostGIS to version 2.0.  We've attempted a number of different ways to upgrade the database without compromising data and functions that rely on PostGIS 1.4, but it looks like when we run the gppkg utility with the new PostGIS extensions we only get errors, similar to the following:

gppkg -i postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:21:20:024675 gppkg:s-md-pv-gp-01-01:gpadmin-[INFO]:-Starting gppkg with args: -i postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:21:20:024675 gppkg:s-md-pv-gp-01-01:gpadmin-[CRITICAL]:-gppkg failed. (Reason='__init__() got an unexpected keyword argument 'postupdate'') exiting...

We've also run gppkg passing the -u flag, with similar results:

gppkg -u postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[INFO]:-Starting gppkg with args: -u postgis-ossv2.0.3_pv2.0.1_gpdb4.3orca-rhel5-x86_64.gppkg
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-WARNING: The process of updating a package includes removing all
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-previous versions of the system objects related to the package. For
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-example, previous versions of shared libraries are removed.
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-After the update process, a database function will fail when it is
20160919:13:35:52:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[WARNING]:-called if the function references a package file that has been removed.
Do you still want to continue ? Yy|Nn (default=N):
> y
20160919:13:35:59:024930 gppkg:s-md-pv-gp-01-01:gpadmin-[CRITICAL]:-gppkg failed. (Reason='__init__() got an unexpected keyword argument 'postupdate'') exiting...

A major caveat may also be related to the fact that only Greenplum versions 4.2.6.X to 4.2.8.X support both version 1.4 and 2.0 of postGIS.  At this point, we're looking for some direction on how to proceed in hopes others may have come up against some of these hurdles.

We've got tickets in with the vendor, Pivotal, but these are not proving fruitful so I'm posting here in hopes the community has some direction they can point me in.

Thanks,

John


texts: SSAS : 2 linked facts, how to get number of items in dimension attribute?

texts:
In an SSAS project there is a TRANSFUSION fact, linked to a PATIENT fact. A transfusion has many attributes, and among them there is the patient who received the transfusion. A patient can receive zero, one or many transfusions.
Due to SSRS MDX query requirements, I would like to get a dimension attribute -- say 'isTransfused' -- that would tell me if a patient has received a transfusion. I need a dimension attribute because I need to construct an MDX query with filter parts like '[Dim Misc].[isTransfused].[isTransfused].[Yes]' or '[Dim Misc].[isTransfused].[isTransfused].[No]'.
'Dim Misc' is a fact dimension (linked to PATIENT).
Here is an example of the type of query I need to construct (in my new query I would replace GHM items by isTransfused):
SELECT {cohorte, TTAnemieEtOuCarenceMartiale, nombre} ON 0,
filter({
[GHM].[Severity].[Severity].[1]
,[GHM].[Severity].[Severity].[2]
,[GHM].[Severity].[Severity].[3]
,[GHM].[Severity].[Severity].[4]
,[GHM].[Severity].[Severity].[]
 }
* {
    ( [Dim misc].[FIVbool].&[1],[Dim misc].[FORbool].&[1],[Dim misc].[EPObool].&[1] )
    ,( [Dim misc].[FIVbool].&[1],[Dim misc].[FORbool].&[0],[Dim misc].[EPObool].&[1] )
    ,( [Dim misc].[FIVbool].&[1],[Dim misc].[FORbool].&[1],[Dim misc].[EPObool].&[0] )
    ,( [Dim misc].[FIVbool].&[0],[Dim misc].[FORbool].&[1],[Dim misc].[EPObool].&[1] )
    ,( [Dim misc].[FIVbool].&[0],[Dim misc].[FORbool].&[0],[Dim misc].[EPObool].&[1] )
    ,( [Dim misc].[FIVbool].&[0],[Dim misc].[FORbool].&[1],[Dim misc].[EPObool].&[0] )
    ,( [Dim misc].[FIVbool].&[1],[Dim misc].[FORbool].&[0],[Dim misc].[EPObool].&[0] )
   ,( [Dim misc].[FIVbool].&[0],[Dim misc].[FORbool].&[0],[Dim misc].[EPObool].&[0] )
},nombre>0)
 ON 1

Here is an example of my 2 facts(VPARCOURS stands for PATIENT).


texts: how to increase speed of this mysql query?

texts:
How I can increase speed of this search query?

This search bring news based on time and title and text. each item has own factor.

SELECT * , MATCH (`title`) AGAINST ('tax in work') * 1.65 AS `titlescore` ,
(

    (

       CASE
          WHEN time >=1432173380 THEN 21
          WHEN time >=1432162580 THEN 18
          WHEN time >=1432097780 THEN 15
          WHEN time >=1431903380 THEN 11
          WHEN time >=1431320180 THEN 7
          ELSE 0
       END
    ) * 1.75 ) AS `timescore` ,

    MATCH (`text`) AGAINST ('tax in work') * 0.65 AS `textscore`

    FROM `news`
    HAVING `titlescore` + `timescore` + `textscore` >= 35
    ORDER BY `titlescore` + `timescore` + `textscore` DESC
    LIMIT 0 , 25

Now this takes (4 to 20)sec relay on search query!

Explain query result:

id  select_type     table   type    possible_keys   key     key_len     ref     rows    Extra
1   SIMPLE         news     ALL     NULL            NULL    NULL       NULL 1656038     Using where; Using filesort

Table structure:

CREATE TABLE IF NOT EXISTS `news` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `title` text COLLATE utf8_persian_ci NOT NULL,
  `text` varchar(400) COLLATE utf8_persian_ci DEFAULT NULL,
  `time` int(10) unsigned NOT NULL,
  `cat_id` int(10) unsigned NOT NULL,
  `source_id` int(10) unsigned NOT NULL,
  `visit` int(10) unsigned NOT NULL,
  `url` text COLLATE utf8_persian_ci NOT NULL,
  `sha1` varchar(40) COLLATE utf8_persian_ci NOT NULL,
  `title_slug` text COLLATE utf8_persian_ci NOT NULL,
  `date` varchar(10) COLLATE utf8_persian_ci DEFAULT NULL,
  `day_time` tinyint(4) DEFAULT NULL,
  `week_day` tinyint(4) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `sha1Uniq` (`sha1`),
  KEY `cat_id` (`cat_id`),
  KEY `source_id` (`source_id`),
  KEY `time` (`time`),
  KEY `date` (`date`),
  FULLTEXT KEY `searchIndex` (`title`,`text`),
  FULLTEXT KEY `title` (`title`),
  FULLTEXT KEY `text` (`text`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 COLLATE=utf8_persian_ci AUTO_INCREMENT=2386224 ;


texts: Is it possible to configure exceptions for SQL Server Agent tasks?

texts:
I use SQL Agents tasks to execute my backup, maintenance & ETL tasks.

IT would like to have a regular maintenance window to apply server patches. They're suggesting the 3rd Tuesday of every month outside work hours.

Can I configure jobs to run on every day except that day?

The other option I thought might be possible was to have multiple schedules configured to somehow exclude that day.


texts:
I have a problem with my Pgadmin3.. I used to use the last year, but this year I can't connect with the localhost server with pgadmin

This is the message error:

FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL on
FATAL: no pg_hba.conf entry for host "127.0.0.1", user "postgres", database "postgres", SSL off

My pg_hba.conf file have this content:

TYPE       DATABASE USER                ADDRESS                 METHOD
local      all                          all                     trust

I can connect without problems with psql. My user is postgres and my pass is postgres too.

I have information in the database, i don't have idea what's happen.

My OS is Linux Lubuntu 14.04


texts: how do you revoke create table from a user on postgresql 9.4?

texts:
I'm trying to create a postgresql user that can select but can't create or drop, insert and so on on a database but I cannot for the life of me figure out how to prevent a user from creating its own tables.

What I've done so far:

create database test;
create role readonly;
alter role readonly with login;
alter role readonly with encrypted password 'test';
revoke all on schema public from public;
revoke all on schema public from readonly;
revoke all on database test from public;
revoke all on database test from readonly;
grant connect on database test to readonly;

Yet when I log in to the database as readonly I'm still able to create tables with impunity. What am I missing?


texts: Msg 245, Level 16, State 1, Line 1 Conversion failed when converting the varchar value '*%text%*4' to data type int

texts:
I have a database that holds information linking customer data to forms that are filled out by them, or on them by someone else. Our UI runs reports but can only run 1 form at a time.  I am trying to get an Excel type report out of the DB using SQL Query to combine tables to give me all forms and members connected to said form. Our Member table holds all the customer data but only has one column (UID) that has the same data on other tables with different column names. I have used the query below to fix this mismatch:

SELECT *
FROM dbo.Answer a
INNER JOIN dbo.tasklist_data tld ON a.source_taskID = tld.TaskID
INNER JOIN dbo.Member m ON tld.createdby = m.uid
INNER JOIN dbo.Form f ON a.FormID = f.FormID
INNER JOIN dbo.Element_Answer ea ON a.ID = ea.AnswerID
INNER JOIN dbo.Form_Elements fe ON ea.ElementID = fe.ElementID

The problem I am having is one table (Element_Answer) has a column called Element_answer that holds the data of what was picked when the form was filled out.  The data in this column is mainly integers ranging from 1 to 9 which link to another table (Form_Element_groups) that has the same number data in a column called sort_order. The ea.Element_Answer column also has text data (*%text%*75193) that represents another table (Element_answer_text) that holds the actual text that was written on the form.  The number next to the %text% represents the ID on the Element_Answer_text table.

I am trying to combine the two tables(ea and feg).  When I try to INNER JOIN tables Element_Answer to Form_Element_Groups using the ea.Element_Answer and feg.Sort_order columns as the common, it gives me the above error.

My desired output would be to be able to combine these two tables, so that I can see all my data, possibly ignoring all the %test% entries or possibly having the actual text from the Element_answer_text table replace the %text% entries.

I apologize in advance if I gave too much information. The tables in question are the Element_Answer table, which holds 5 columns (ID, AnswerID, ElementID, Element_answer, and status).  The Element_answer column is important because it holds the integer data that I need that links to the other table called Form_Element_Groups that has 7 columns (ID, elementID, option_span, sort_order, group_title, group_text, and category). The sort_order column is the column I am trying to match up with the element_answer column because the data that matches up on these two columns will give me the desired result of only what I want to see from the feg table's group_title column. The ea.Element_answer column also has text entries that link to the Element_Answer_Text table that has two columns (ID, and Element_Answer_text). The eat.element_answer_text column holds the actual text.

If I can get a query that ignores the text in the ea.Element_answer column and only gives me my integer data that matches up, that would be great.  If I can get a query that can not only give me my integer data that matches, but the actual text from the Element_Answer_text table, that would be even greater.

I can't figure out how to match the text data in the ea.Element_answer column with the actual text located on eat.Element_answer_text table/column because it has this weird *%text%*4 data within the ea.Element_answer column referring to the ID on the eat table. (I just picked 4 as an example, there are many other *%text% entries in that ea.Element_answer column that start with *%text%*4 and end with *%text%*95042.)


texts: Exclude rows with consecutive data that may repeat

texts:
I have a table showing statuses (0 - broken, 1 - live) for a particular device at a given timestamp:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0    -- leave this
owner1         device_1     2001-01-01 09:05   0    -- exclude
owner1         device_1     2001-01-01 09:10   0    -- exclude
owner1         device_2     2001-01-01 09:15   0    -- leave this
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:35   0    -- exclude
owner1         device_2     2001-01-01 09:45   1
owner1         device_1     2001-01-01 09:55   0    --leave this
owner1         device_1     2001-01-01 10:10   0    --exclude
owner1         device_1     2001-01-01 10:11   1

and so on.

I need to exclude consecutive zero statuses, and leave the one with earliest datetime. (So that I could define datetime difference between the device broken and live.)

The result:

device_owner   device_id    timestamp          status
owner1         device_1     2001-01-01 09:00   0
owner1         device_2     2001-01-01 09:15   0
owner1         device_1     2001-01-01 09:30   1
owner1         device_2     2001-01-01 09:45   1
owner1         device_1     2001-01-01 09:55   0
owner1         device_1     2001-01-01 10:11   1

and so on.

Then in my further calculations, total breakdown for device_1 would be between 09:00 and 09:30, 09:55 and 10:11, for device_2 – 09:15—09:45.

DDL:

DECLARE @t TABLE
      (
          device_owner VARCHAR(10),
          device_id VARCHAR(10),
          timestamp DATETIME,
          status BIT
      );

INSERT @t ( device_owner, device_id, timestamp, status )
SELECT *
FROM
(
  VALUES ('owner1', 'device_1', '2001-01-01 09:00',   0),    -- leave this
         ('owner1', 'device_1', '2001-01-01 09:05',   0),    -- exclude
         ('owner1', 'device_1', '2001-01-01 09:10',   0),    -- exclude
         ('owner1', 'device_2', '2001-01-01 09:15',   0),    -- leave this
         ('owner1', 'device_1', '2001-01-01 09:30',   1),
         ('owner1', 'device_2', '2001-01-01 09:35',   0),    -- exclude
         ('owner1', 'device_2', '2001-01-01 09:45',   1),
         ('owner1', 'device_1', '2001-01-01 09:55',   0),    --leave this
         ('owner1', 'device_1', '2001-01-01 10:10',   0),    --exclude
         ('owner1', 'device_1', '2001-01-01 10:11',   1)
) AS x ( device_owner, device_id, timestamp, status );

I'd appreciate any advice.


texts: sqlplus Setting the exit code of a script

texts:
How can I set the exit code of a script?

The following doesn't seem to work

WHENEVER SQLERROR EXIT 5

DECLARE
     retCode number := 0;

BEGIN
     retCode := 30;
END;
/
EXIT :retCode ;

Gives the usage for EXIT with an exit code of 1


texts:
I am running the following DELETE SQL on a MySQL database on a MyISAM/utf8 table.  The SQL is called in a loop, once time per Users (in this case):-

DELETE FROM regulatory WHERE documentid  IN
(
    SELECT * FROM
    (
        SELECT t1.`DocumentID` FROM regulatory T1, regulatory T2
        WHERE (t1.`group_ID`=v_user_id AND t2.`group_ID`=v_user_id AND
        TRIM(t1.`title`)=TRIM(t2.`title`) AND  TRIM(t1.`spare1`='red.gif')
        AND (t1.`docref`=v_temp) AND t1.`DocumentID`>t2.`DocumentID`)
    ) DocumentID
) ;

But it is taking over a minute per User - how can I optimize this at all so it takes far less time each user?


texts: Do management operations such as database copy affect our current db performance?

texts:
Example:

I have a database named "myDb"

I execute on the master:

create database mydb_copy as copy of myDb

Will the performance of "myDb" be affected? Specifically the avg_read_io (data reads)?


texts:
I already studied some databases like MySQL but I never professionally work with databases. I am working on a project and we intend to use SQLite (for some reasons: small, lightweight, fast, among others), then I have some questions about it:

SQLite support how many gigabytes of data? Is there a limit? (I will have some huge data tables (e.g. 18 columns X 80.000 rows) for each user)
Is it fast to read a lot of data? (e.g. 18 columns X 80.000 rows)
There is any other relevant information I need to know about SQLite? (Note: I will use it on Windows and OS X)

Extra information:

The software I am working on is a data analysis software, and I just need to use the DB to save the data collected from the users using some sensors.

I am using Qt 5.3.2 (C++) on the project.


texts: Why does json_agg fail with error "function json_agg(record) does not exist"?

texts:
With postgresql version 9.2, I'm trying to run a query that selects certain columns to build a json object. Here's the query, which I derived from this question:

select
  a.id
  , json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item
from a join b on a.id = b.foreign_key
limit 10

When I run this, I get an error that's rather hard to parse.

  ERROR: function json_agg(record) does not exist   Hint: No function
  matches the given name and argument types. You might need to add
  explicit type casts.

  , json_agg((SELECT x FROM (SELECT b.fieldA) AS x)) AS item

It seems as though the complaint is that json_agg is not receiving the right sort of input, but I don't understand why.

When I run the same query without the json_agg, i.e. just with the sub-select expression, I get a column with the value of b.fieldA inside parentheses. I'm not sure what the parens indicate in terms of a data type, but I could see that perhaps json_agg is only getting the values an so doesn't know how to supply the property name.

Can anyone explain where I've gone wrong?


texts: SSIS Package failing to access UNC path it has permissions to

texts:
Trying to run an SSIS package that tries to access \\fileserver\share\file.txt, but the SSIS job returns:

  "File System Task:ERROR: File or directory "\\fileserver\share\file.txt" represented by connection "Flat File Connection Manager" does not exist.

Permissions on the share and the NTFS permissions allow both the service account running SSIS and the user running the package read/write access to file.txt

If the user RDP's into the SSIS server they can access the UNC path without issue.

Triple-verified permissions using 'effective access' directly on file.txt and permissions are set correctly.


texts: Use cases for hstore vs json datatypes in postgresql

texts:
In Postgresql, the hstore and json datatypes seem to have very similar use cases. When would you choose to use one vs. the other? Initial thoughts:

You can nest with json; you can't with hstore
Functions for parsing json won't be available until 9.3


texts: MySQL 5.5 Runs Out of Memory, Drops All Connections When Creating Many Databases

texts:
I'm running into a problem when I create a large number of databases consecutively on an Amazon RDS db.m1.large instance. The MySQL Engine Version is 5.5.23. At some point, the RDS instance drops all connections and refuses any new ones for a period of about 10-20 minutes.

The database engine is InnoDB. Each database is exactly the same and is created from a MySQL Dump file. There are 65 tables, each is empty aside from one or two tables that have a row or two, so the data size is very small. Foreign key constraints are turned off before the tables are created and turned back on afterwards for each database created.

The following pseudo code essentially describes what I am doing:

For # of databases: // around 1000-5000 usually results in a crash
   CREATE DATABASE foo_num CHARACTER SET utf8 COLLATE utf8_bin; // done via Python Django connection
   mysql -u USER -h HOST --password=PASSWORD DBNAME < dbcreate.sql

The dbcreate.sql looks similar to:

SET FOREIGN_KEY_CHECKS=0;
DROP TABLE IF EXISTS `FooTable1`;
CREATE TABLE `FooTable1` (
   ...
)
...
DROP TABLE IF EXISTS `FooTable65`;
CREATE TABLE `FooTable65` (
   ...
)
SET FOREIGN_KEY_CHECKS=1;

Here is a snapshot showing the connections dropping.

I noticed, however, that the CPU is not pinning or anything before, during or after the connections drop:

However, I did notice the read throughput spikes during the connection drop:

Update 1

Okay, so as Rolando suggested, it may be that RAM is the limitation. The instance I am using has ~7GB of RAM available and as I was looking through the monitoring graphs I noticed the freeable memory metric was around the 7GB mark at the same time the connections were dropped. See the attached chart:

The question is, why is there this sudden spike in RAM usage and can it be avoided without having to scale up the hardware?


texts: How to retrieve or reset the password on postgresql on linux

texts: I Have Installed Postgres on my Linux system, I forgot the admin password I want to reset to password how can I do that..


texts: UPDATE id table on the columns with the same name

texts:
I may be a newbie question but I don't know how to resolve this. I have a table like:

name | id       | value
A      1286487    1286333
B      1286489    1286403
C      1286495    1286455
C      1286496    1286375
D      1286503    1286341
B      1286506    1286343

And I want to update this table to make it like this:

name | id       | value
A      1286487    1286333
B      1286489    1286403
C      1286495    1286455
C      1286495    1286375
D      1286503    1286341
B      1286489    1286343

So rows with name B and C have the same id like the first row with this name. Can anyone help me with that?


texts:
This is more of a theoretical question:

Let's say I have a collection of documents, my_collection:

{
    "_id": ObjectId("52c679ac7a23e6225b62c51f"),
    "date": ISODate("2014-01-06T03:48:30.737Z")
}
{
    "_id" : ObjectId("52c679b63cdecc6508c54fe5"),
    "date": "2014-01-06T03:48:30.737Z"
}
{
    "_id" : ObjectId("52ca15677a23e6d3354fb7b4"),
    "date": "2014-01-03 19:49:48"
}

and I then create an index

> db.my_collection.ensureIndex({date: 1})

Does Mongo automagically figure out it should be a date and then do a cast to date before it hashes for the index? Or does it treat the field as a string serialisation or something else?

What sort of performance impact would I get (if any) not having all the fields as Date BSON objects, or is there minimal to no impact?

EDIT: I think I failed to clearly communicate another part of the question, but your answer might have already addressed it. Dates stored as string will not be included in results where the query uses a Date()/IsoDate() and vice versa, right?


texts: Using a While function but running out of days

texts:
I have a DB which includes a datetime column (timestamp).
A new record is written every hour, so 24 records a day.

I want my procedure to look for conditions where SO2_7PCT > 44 and NOX_LB_Validity = 'Valid'.

I want to find the most recent 30 days in which any hour meets these conditions. On days where the conditions are met by at least one hour, I want to pull all 24 hours of that day into a temp table. If 0 hours meet the conditions, skip the day.

Here is the code I wrote. The problem I have with this code is if there aren't 30 days which meet the conditions, it doesn't stop, it keeps going day after day, earlier and earlier, forever. The earliest day I have in my DB is 10/1/2017 but it blows right past that and keeps looking, even though there are no more rows to look at!

What should I do to make it stop when it runs out of rows to look at?

Here's the code:

-- daycount is how many valid days it found.
-- daystart and dayend are the 24 hour period to look at the hours.
-- isonline counts how many valid and online hours that day.
-- daycounter counts how many valid days are in the roll.

declare @daycount int, @daystart datetime, @dayend datetime, @isonline int, @daycounter int

set @daycounter = 0
set @daycount = 30
set @dayend = @EndDate
--set @dayend = dateadd(DD,1,@dayend)
set @daystart = dateadd(DD,-1,@dayend)
print '@daystart'
print @daystart
print '@dayend'
print @dayend

While @daycount > 0 -- if we haven't found 30 useable days then:
Begin
  set @isonline = (select count(So2_7PCT)
                     from Auto_SixtyMinuteEmissionRecords
                    where SO2_7PCT > '44'
                      and NOX_LB_Validity = 'Valid'
                      and type = @Type
                      and timestamp >= @daystart and TimeStamp < @dayend)

  While @isonline > 0 -- Found a good day, need to pull the 24 hours into temp table, increment daycounter, decrement daycount.
  Begin
    insert into @temptable
    select @ from Auto_SixtyMinuteEmissionRecords
     where timestamp >= @daystart
       and TimeStamp < @dayend)

    set @isonline = 0
    set @daycount = @daycount - 1
    set @daycounter = @daycounter + 1
  END

  --Set start and end to -1 day to look at previous day.
  Set @daystart = dateadd(dd,-1,@daystart)
  set @dayend = dateadd(dd,-1,@dayend)
END;


texts: How can I identify non deterministic updates occurring in my SQL Server?

texts:
SQL Server books online states

  Use caution when specifying the FROM clause to provide the criteria
  for the update operation. The results of an UPDATE statement are
  undefined if the statement includes a FROM clause that is not
  specified in such a way that only one value is available for each
  column occurrence that is updated, that is if the UPDATE statement is
  not deterministic.

I need to try and identify cases where non deterministic updates are occurring in our database. It appears that SQL Server does not throw an error or warning in such a case.

Does anyone know if there is an easy way of identifying where these issues are occurring, querying plan cache etc?. Doing a full code review to identify these issues will be time consuming given the size and complexity of the application.

Tested on SQL Server 2008\2017 developer editions.

Here is an example:

declare @t1 table (id int, col int);
insert into @t1 values(1,1);

declare @t2 table (id int, col int);
insert into @t2 values(1,10), (1,20);

update t1
set t1.col = t2.col
from @t1 t1 join @t2 t2
         on t1.id = t2.id;

select *
from @t1;


texts:
I have a master table and another table with foreign key. I can easily add data to master table. But whenever I want to add data to the connected table, I get a message saying Foreign key doesn't have a default value. When I make the foreign key auto increment in MySQL then I can enter data, but it is not the way it should work. May be it can be acceptable for one to one relationship.
Shouldn't the connected table get the value from Master table? or is there sth else I'm missing here?

I'd really appreciate any suggestions.

Have a nice day.


texts:
So I got this view in mysql :

CREATE VIEW vote_question_totals
AS
SELECT question_id
, SUM((vote_question_type)='1') question_total_plus
, SUM((vote_question_type)='-1') question_total_minus
, count(*) question_total_mixed
,(SUM((vote_question_type)='1') - SUM((vote_question_type)='-1')) question_total_score
FROM vote_question
GROUP BY question_id;

and I'm doing a RIGHT JOIN with the question table using the question_id

but then I wanna ORDER by question_total_score and this is where it get problematical.
The question with no vote get a question_total_score which is NULL and cant get properly  ordered.
(I would like my NULL row to be considered like a 0)

What should I do ?


texts:
We recently noticed an issue on our MyCluster DB which is comprised of 2 instances hosted on AWS.

Our website grinds to halt in terms of performance and we noticed that there 2 processes that are stuck in commit state as shown below.

Id  User    Host    db  Command Time    State   Info
49  rdsadmin    localhost:56378 mysql   Sleep   1       \n
938 dbadmin 10.0.11.242:57130   REDACTED    Query   1   init    COMMIT
942 dbadmin 10.0.1.114:49236    REDACTED    Query   1   init    COMMIT

We are in the process of getting performance_schema enabled. How should we go about find the root cause of the performance bottleneck?


texts: How to determine if open_table_cache is set properly?

texts:
Does anyone know what this really means? (from MySQL docs)

  You can determine whether your table cache is too small by checking
  the mysqld status variable Opened_tables, which indicates the number
  of table-opening operations since the server started:

mysql> SHOW GLOBAL STATUS LIKE 'Opened_tables';
+ | Variable_name | Value |
+-| Opened_tables | 2741  |

  If the value is very large or increases rapidly, even when you have not issued many FLUSH TABLES statements, increase the table cache size.

What does 'very large' mean?  What does 'too rapidly' mean?

In my case I have open_table_cache set to 10,240 and table_open_cache_instances set to 16.  Anytime I query opened_tables it is higher than the last time.  Just now it read 122,261.

My DB has 2600 tables and maybe 300 connections regularly.  Any thoughts on how I might know what open_table_cache should be?


texts:
Does anyone know if there is a way to confirm if any my.cnf file is being read by MySQL?

I only have the one my.cnf on the machine (and there are no my.ini files) which I have in /etc/my.cnf.

When I run ./mysqld --help --versbose, it doesn't give me the variable I have set in the file, so my assumption was it wasn't loading it or that it was loading a different file.

I tested this by setting my.cnf to 777 and running the command again, this time I get a warning saying the file won't be loaded because it is world writeable.

Is this enough to imply the my.cnf has been loaded and perhaps there is an error in it, causing the variables not to be set or is there some other way of knowing for sure what (if any) my.cnf file is loaded?


texts: Concurrent UPDATEs on INNODB tables in MySQL

texts:
I need to UPDATE all rows for multiple columns on a large INNODB table. Each UPDATE processes one column separately and takes about 1 hour. I have to do this for 10 columns so it would take 10 h I do not want to wait. As I have a 16 core CPU I would like to UPDATE concurrently.

Currently, as I do UPDATE all rows for each column the whole table is locked and I cannot start a further UPDATE on the same table on another column. Is there a (unsafe) way to do it in parallel? Can I disable locking of the table / rows?

Maybe something equivilent to READ UNCOMMITTED...

I am using MySQL 8.0 with INNODB and I it is a single user system so I do not have to worry about uncommited changes.


texts: In mysql master-slave Replication which server requests the other?

texts:
I want to setup mysql replication between two servers one of them is my localhost and the other is online server.
I have all availability to make any one of them the master.
But according to that my localhost server doesn't have a static IP, i need to know which server of the two (master & slave) is the one which requests the other for doing updates.
Does the master sends the binlog updates, or the slave is the one which requests for new updates periodically ? so i will make it the localhost.
thank you in advance.


texts: Ubuntu PostgreSQL - Upgrade adding Python extension

texts:
I have an existing and populated PostgreSQL 10 instance, to which I'd like to add Python support as a Procedural language. As far as I can tell this can be done initially by installing postgresql and python at the same time, sudo apt-get install -y postgresql-plpython-10.
My question is: how do I upgrade my current plain postgresql installation to suport plpython ? Being new to Ubuntu and UNIX in general all these processes are really elusive to me.


texts: Oracle 11g see transaction or session start time

texts:
I want to know when a session or transaction started. The deadlock file doesn't give me this information. Is there some logfile that keeps these records?

I've got a transaction id "TX-1234-abcd", a session number and some "DID 000-000ABCD" number.


texts: Can I trust the effective permissions of default database roles?

texts:
I've granted membership in the db_datareader and db_datawriter roles for some testing being performed by a specific user.

Looking at the names of each role, I know what the roles do. However, I want to see where the permissions were assigned just to better understand.

To my surprise I see a blank slate for the db_datareader schema for that database. I go onto read Understanding SQL Server fixed database roles and from there I know why I cannot see anything in that properties dialog:

  The catch with this role is that the permission is implicit. That means if you query sys.database_permissions, you will not see any permission granted, either to the db_datareader role or directly to the user. Therefore, if you need to audit for everyone who has SELECT access to particular tables in a database, you'll have to query the membership of this group via the use of sp_helprolemember

I was aware of querying the roles which obviously works fine. So I thought this was perhaps a limitation of the GUI. I liked the permissions script from this answer and gave that a run. However, upon execution, that returns NULL for permissiontype and permissionstate which is consistent with the above quote.

All that said, are these pre-canned roles infallible? Can I assume that no one can mess around with the implicit nature of their respective permissions i.e you can't remove select rights from db_datareader behind the scenes?


texts:
I'm getting the below configure error while doing the install the Zabbix reporting tool on Linux.
i have tried both Versions(zabbix-2.0.9,zabbix-1.8.17), but getting same issue.

Log:

checking for macro __VA_ARGS__... yes
checking return type of signal handlers... void
checking for getloadavg... yes
checking for hstrerror... yes
checking for getenv... yes
checking for putenv... yes
checking for sigqueue... yes
checking for /proc filesystem... yes
checking for file /proc/stat... yes
checking for file /proc/cpuinfo... yes
checking for file /proc/0/psinfo... no
checking for file /proc/loadavg... yes
checking for file /proc/net/dev... yes
checking for long long format... no
checking for -rdynamic linking option... yes
checking for libperfstat 5.2.0.40 fileset... no
checking for libperfstat 5.3.0.60 fileset... no
checking for architecture... linux (linux-gnu)
checking for the linux kernel version... unknown family (3.0.76-0.11-default)
checking if Oracle support is enabled... no
checking for SQLite3 library >= 3.0.0... no
configure: error: SQLite3 library not found

Please help me how to fix this issue?

Thanks,


texts:
I'm not able to import the full database from oracle 10g to 11g using impdp FULL=Y. It seems that the file path is differing so what shall I do?
The impdp command is looking for oracle 10g path while importing into 11g db.
is there any way to import full database from 10g to 11g using impdp?


texts: Calculate percent of roads (by length) where condition is >=15

texts:
I have a road condition table:
CONDITION       LENGTH
    2              5
    4             10
    6             15
    8             20
   10             25
   12             30
   14             35
   16             40
   18             45
   20             50

Roads that are at a condition of 15 or greater ( a.k.a. 15_OG) are considered to be in a good condition.
I want to calculate the percentage of roads (by length) that are at a good condition.
LENGTH_15_OG    TOTAL_LENGTH    PERCENT_15_OG
     135             275            49.09%

How can I do this calculation?


texts: How to improve this query performance by indexing properly?

texts:
I have following select query, and I need to improve the performance of this query. Can anyone suggest me how to convert the clustered index scan to index seek?

DECLARE @now DateTime;
DECLARE @currentweekstart DateTime;
DECLARE @currentweekend DateTime;
SET @now = getDate();
SET @currentweekstart = DATEADD(
                 DD,
                 -(DATEPART(DW, @now - 7) - 1),
                 @now - 7);
SET @currentweekend = DATEADD(
               DD,
               7 - (DATEPART(DW, @now - 7)),
               @now - 7);

  SELECT  CHINFO.CHILDID,COUNT(*) AS CURRENTWEEKPOTTYBREAKS
          FROM  BKA.CHILDINFORMATION CHINFO
  JOIN  BKA.CHILDEVENTS CHE
         ON CHE.CHILDID = CHINFO.CHILDID
  WHERE TYPE ='POTTYBREAK'
          AND CHE.ADDDATE BETWEEN @currentweekstart AND @currentweekend
           GROUP BY CHINFO.CHILDID

Primary Keys:

  TableName --> ColumnName

  CHILDINFORMATION-->ChildId

  CHILDEVENTS-->EventId

Foreign Keys:

  CHILDEVENTS-->Here ChildId is the foreign key reference of
  CHILDINFORMATION table

NonClustered Indexes:

  CHILDINFORMATION-->No nonclustered indexes

  ChildEvents-->IX_ChildEvents_Type-->Type include EventId

  ChildEvents-->IX_ChildEvents_Type_AddDate-->Type,AddDate

  ChildEvents-->IX_ChildEvents_Adddate_Type-->Adddate,Type include
  ChildId,EventId


texts: How to Concatenate DateTime to nvarchar field

texts:
I have a table with a nvarchar(32) column and I want to update the value in this column with the first 10 bytes of another field on the table which is defined as nvarchar(32) after the 10 bytes I want to concatenate the first 22 bytes of the current datetime without the dashes in the date.

Update Bil_ReturnsRepository
  Set PaymentReference = CAST((Left(PolicyNumber, 10) + CONVERT(datetime,GETDATE(), 121)) as nvarchar
Where PolicyNumber = '1234567890'

I would like for the output to look like this for example:

1234567890201802130446386883


texts: error when starting up the mongodb cluster

texts:
I have a cluser of mongodb of 3 nodes:
After increasing the max-openfile-count of the linux system user,
 I tried the following cluster startup commands after a rebooting the linux system without shutdowning mongodb cluster normally.

# log in each node:
1, /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod -f /data/mongodbtest/config/mongo.conf
2, /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard1/mongo.conf --nojournal
3,-- /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard2/mongo.conf --nojournal
4,-- /var/opt/mongodb-linux-x86_64-rhel62-3.4.5/bin/mongod --maxConns=20000 -f /data/mongodbtest/shard3/mongo.conf --nojournal

when I run the step 2, the process stucked with following message:

I tried to delete the mongod.lock file in " /data/mongodbtest/shard1/data " and retry the process.
But the problem still exists.I don't know how can i fix this.


texts: Redis has "commands" that mutate the various data types it can store. Why? Redis is primarily a cache

texts:
If you look at something like https://redis.io/commands#list, you will see that types that Redis stores and supports are fully mutable, atomically, within the data store's memory.

This does not make alot of sense given that Redis exists as primarily a cache to accelerate applications who have a database separate from Redis and only use Redis as a place to read values back from.

What am I missing here? Why have mutability on values directly on Redis when that does not fit a caching model?


texts: Detect with Left Join duplicates entries and return the first value, also return sum of duplicates entries

texts:
¡Hi!, i have a case with two tables than can have a lot of matches entries and the time of fetching increases a lot.

This tables can be

Table A: Employees

-------------------------
| Name   | ID | Account |
-------------------------
| Nicole | 01 | 12345   |
| Alexis | 02 | 67890   |
-------------------------

And Table B: BankAccounts

--------------------------
| Name   | ID  | Account |
--------------------------
| Nicole | 01  | 12345   |
| Nicole | 01  | 67890   | //duplicates Accounts
| Alexis | 02  | 67890   | //duplicates Accounts
--------------------------

And i want to do this with a Left Join in a Table that can have more of 450,000 different entries

Result Table C

Column_A = ¿Exists the account number in other register?
Column_B = if(NumberOfMatches > 1) //this means that the account be found
in other user AND i want to get the first value of all posibles number of matches

                         |Account exists in other user|Match in User..
-----------------------------------------------------------------------------
| Name   | ID  | Account | Column_A | NumberOfMatches | Column_B    | BadID |
--------------------------------------------------------------------|-------|
| Nicole | 01  | 12345   | No       |       1         | Nicole (OK) | null  |
| Alexis | 02  | 67890   | Yes      |       2         | Nicole (BAD)|   01  |
-----------------------------------------------------------------------------

Thanks and regards!

Note: sorry for my english, im learning :p


texts: SQL Server 2005-2008 Compatibility Check

texts:
A few databases were recently migrated from SQL Server 2005 to SQL Server 2008, and I have to check if all the stored procedures, and triggers still work well. With 'checking', I mean that I should guarantee that they are compilable and executable.

There are many stored procedures and triggers to check. To do it manually, I would need maybe 1 month. So, I want to take another approach.

What kind of checking procedure would you recommend me, so that I can say surely that the stored procedures and triggers will also work in SQL Server 2008?

Thanks


texts: Oracle analytic functions - how to get the highest RANKing row?

texts:
Test data:

create table test (
  grp varchar2(16)
, mbr varchar2(16)
, reading1 number
, reading2 number
);

-- group A: 3 members, 1 duplicate set
-- group B: 2 members, 1 duplicate, one reading NULL
-- group C: 2 members, no repeats, no NULLs
begin
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'x', '1.0', '2.0' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'y', '1.1', '2.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'A', 'z', '1.2', '2.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'y', '20.2', null ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'B', 'x', '20.4', '40.4' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'C', 'r', '100.1', '200.2' ) ;
  insert into test ( grp, mbr, reading1, reading2 )
    values ( 'C', 's', '100.2', '200.4' ) ;
end;
/

See dbfiddle.

select * from test;
GRP  MBR  READING1  READING2
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
A    x    1         2
A    y    1.1       2.2
A    z    1.2       2.4
B    y    20.2      NULL
B    x    20.4      40.4
B    y    20.2      NULL
B    x    20.4      40.4
C    r    100.1     200.2
C    s    100.2     200.4

Problem:

Write a query that does all of the following:

{1} Find unique rows.

{2} Find the last 2 members (mbr) of each group (grp).  Assumption: when the members are put into alphabetical order, the last member is the one with the last letter (eg if we have 'x','y','z', the last letter is 'z').  Do not hard-code the letter into the query.

{3} Perform the following calculation: when rows are grouped (according to their grp letter), for each row containing the last letter:
reading1 - preceding reading2 ( ie reading2 of the row containing the letter 'y' ), and reading2 - preceding reading1.  Treat NULLs as 0.

Using our sample/test data:

-- {1}
GRP  MBR  R1     R2
A    x    1      2
A    y    1.1    2.2
A    z    1.2    2.4
B    x    20.4   40.4
B    y    20.2   0
C    r    100.1  200.2
C    s    100.2  200.4

-- {2}
GRP  MBR  RESULT1  RESULT2  RANK_
A    x    1        2        1
A    y    -0.9     1.2      2
A    z    -1       1.3      3
B    x    18       39.2     1
B    y    -20.2    -20.4    2
C    r    100.1    180      1
C    s    -100     100.3    2

-- {3} required/final result
grp  result1  result2
A      -1.0      1.3    -- (result1: 1.2-2.2)     (result2: 2.4-1.1)
B     -20.2    -20.4    -- (result1: 20.2-40.4)   (result2: 0-20.4)
C    -100.0    100.3    -- (result1: 100.2-200.2) (result2: 200.4-100.3)

Existing code:

This query returns result set {2}.

-- {2}
  select
    grp
  , mbr
  , r1 - lag( r2, 1, 0 ) over ( order by grp ) as result1
  , r2 - lag( r1, 1, 0 ) over ( order by grp ) as result2
  , rank() over ( partition by grp order by mbr ) as rank_
  from
  (
    select distinct
      grp
    , mbr
    , nvl( reading1, 0 ) r1
    , nvl( reading2, 0 ) r2
    from test
    order by grp, mbr
  ) ;

Question:
How can we get to resultset {3}, without using hard-coded values (such as rank_ = 2 in a WHERE clause)?
Not sure whether RANK() is needed (for the final query) at all...


texts: Restoring SQL Server databases - tips and tricks?

texts:
Anyone have any tips and tricks when it comes to restoring SQL Server databases?

I recently restored a .bak from one server/instance to another, using SQL Server 2005 on both, and have run into the following issues (so far):

All of the identity insert values were reset to their original values (1 in my case). A pain in the butt until I found the following command to go through all tables, and reseed to the current value + 1:

 exec sp_MSforeachtable @command1 = 'DBCC CHECKIDENT (''?'', RESEED)'

Permissions were gone. The database level accounts came over of course (with their role assignments), but most of the DB permissions for each Role were gone...meaning read,insert,delete,execute etc permissions were missing on each table,view, and stored procedure. I wrote some scripts like the below to rectify, but still a pain in the ass.

 select 'GRANT SELECT ON ' + TABLE_NAME + ' TO rolename' from Information_schema.tables

Instance level permissions obviously aren't present if restoring to a new instance...but that was kind of a pain since it seems you can't recreate an instance level account if the account already exists at the database level. What's the best approach here? In my case I deleted the database account, the created the instance, then granted permissions using scripts like the above.

Generally I'm just curious about some best practices, when it comes to restoring SQL Server databases... and/or tips on somehow avoiding the issues I encountered. I'm just trying to pave the way for the most error free restores I can.


texts: Field/datatype for searchable address components?

texts:
I'm using Django with Postgres, and I'm looking for a field/datatype to store address components that come from Google Places API in this format:

"address_components": [
  {"long_name": "27", "short_name": "27", "types": ["street_number"]},
  {"long_name": "Dromore Crescent", "short_name": "Dromore Crescent", "types": ["route"]},
  {"long_name": "Westdale South", "short_name": "Westdale South", "types": ["neighborhood", "political"]},
  {"long_name": "Hamilton", "short_name": "Hamilton", "types": ["locality", "political"]},
  {"long_name": "Hamilton Division", "short_name": "Hamilton Division", "types": ["administrative_area_level_2", "political"]},
  {"long_name": "Ontario", "short_name": "ON", "types": ["administrative_area_level_1", "political"]},
  {"long_name": "Canada", "short_name": "CA", "types": ["country", "political"]},
  {"long_name": "L8S 4A8", "short_name": "L8S 4A8", "types": ["postal_code"]}
],
"formatted_address": "27 Dromore Crescent, Hamilton, ON L8S 4A8, Canada",
"place_id": "ChIJtZiMplGbLIgRcGQ4Anc337s",

The goal is to allow users to see this row in their results if they search for westdale, L8S 4A8, l8s4a8, 27 dromore, hamilton, etc.

How should this data be saved?

Save it as-is in jsonb? There's a lot of duplicate text, for instance 'long_name' and 'short_name' keep appearing over and over, wasting space.
Strip all the keys and only keep the actual meat of the components in a varchar[], ex. ['27', 'Dromore Crescent', 'Westdale South', 'Hamilton', 'Hamilton Division', 'Ontario', 'Canada', 'L8S 4A8']?
Normalize this data in layers of foreign-key related tables? Four tables are required to normalize this: address_to_component, component, component_type, component_to_component_type. (I actually started with this, but couldn't wrap my head around how to go about querying it.)
Something else?


texts:
We are planing to setup an Oracle 12c RAC with a Logical standby in the DR site.

Since data consistency is extremely important, we our planning to setup redo log shipping to be in SYNC mode.

Our application which also runs on the Secondary site (redundant processes) requires the DB to be in write mode, hence the Logical standby option was chosen. Application on the Primary site DB with write to one tablespace (Say A). And on the secondary site it will write to a separate tablespace (Say B).

Our client's application writes to a tablespace (Say C) and needs it to be in sync across both sites.

The main idea behind doing the above is to stop large number of redo logs bring shipped to the secondary site via the WAN. Our application already has the ability to sync the inputs to the system. And since one input generates several DB inputs (and redo logs), we thought of writing to the databases independently will be the better option.

My Question is, can we configure the primary site to send selective redo logs to the logical standby. From what I know, all redo logs are shipped on-the-fly to the secondary site in SYNC mode and we can configure the logical standby to omit sqls for tablespace A.


texts: Type conversion in expression try_cast may affect cardinality estimate in query plan choice

texts:
I have a query that is running very slow so I started looking into the Execution Plan. TRY_CAST is a safe way of finding out if the data can be cast to a type. And there is a warning in the plan regarding TRY_CAST.

 Type conversion in expression try_cast may affect "CardinalityEstimate" in query plan choice

Please note that I am not a DBA but a coder. Treat me as a novice when I comes to database stuff.

What should be best approach to improve the speed of the query (based on the warning in the title of this question)? Is there anything other than TRY_CAST that might help speed up the query, for example, CAST on its own is prone to exceptions and the only way to safe cast that I know of is TRY_CAST?


texts: Oracle Min - ORA-00937 not a single-group group function

texts:
I try to find companies whose fare has minimum fare of destination which more than one companies use that destination. My command;

SQL> select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id;

       BUS       FARE DEST
---------- ---------- -------------------------
        11      21.36 Kayseri
         1      23.32 Kayseri
         1      63.32 Kars
         5      44.31 Edirne
         1      54.13 Edirne
         1      33.36 Fatsa
         4      33.37 Ankara
         4      33.39 Kayseri
         7      53.37 Istanbul
        10      51.36 Izmir
         7      51.36 Izmir
        10      32.35 Ankara
        10      42.34 Istanbul
        10      52.33 Hatay
        10      32.83 Bursa
         2      30.00 Izmir
        11      52.00 Kayseri
        11      42.00 Kayseri
         2      40.00 Kayseri

Output I want:

       BUS       FARE  DEST
---------- ---------- -------------------------
        10      32.83 Bursa
         2      30.00 Izmir

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
group by dest having count(bus)>1;

>ERROR at line 1: ORA-00937: not a single-group group function

When I try to group dest, It gives error. I can't understand what is mistake? Has anyone idea to fix it?

--- EDIT TABLES ---

CREATE TABLE buscompanies (
      bus_id  number(4) not null PRIMARY KEY,
      name    varchar2(25) not null);

table created.

CREATE TABLE route (
     route_id number(4) not null PRIMARY KEY,
     origin   varchar2(25) not null,
     destination varchar2(25) not null);

table created.

CREATE TABLE tickets (
     tickets_id number(4) not null PRIMARY KEY,
     route_id number(4) not null,
     bus_id number(4) not null,
     fare   number(5,2) not null,
     CONSTRAINT routeid_fk FOREIGN KEY (route_id) REFERENCES route(route_id),
     CONSTRAINT busid_fk FOREIGN KEY (bus_id) REFERENCES buscompanies(bus_id));

SQL> select * from route;

  ROUTE_ID ORIGIN                    DESTINATION
---------- ------------------------- -------------------------
         1 Ankara                    Kayseri
         2 Ankara                    Kars
         3 Ankara                    Edirne
         4 Ankara                    Fatsa
         5 Denizli                   Ankara
         6 Denizli                   Kayseri
         7 Hatay                     Istanbul
         8 Hatay                     Izmir
         9 Izmir                     Ankara
        10 Izmir                     Istanbul
        11 Izmir                     Hatay
        12 Izmir                     Bursa
        13 Bursa                     Izmir
        14 Bursa                     Kayseri

14 rows selected.

--EDIT--

I used this command but that shows only for 1 city

select bus, min(fare), dest from
     (select tickets.bus_id bus, tickets.fare fare, route.destination dest
     from tickets left join route on route.route_id=tickets.route_id)
     group by bus, dest having count(dest) > 1;

       BUS  MIN(FARE) DEST
---------- ---------- -------------------------
        11      21.36 Kayseri

there should be other destinations too. Question was;

  find cheapest company for each city such that more than 1 company
  travels to the city?


texts: MySQL Percrona 8.0 became suddenly slow until restart mysql

texts:
My website today suddenly became slow and all requests were taking about 2 seconds to complete.
Usually it takes like 0.2 second I checked available space on disk and I have a lot.

Memory and CPU usage are usual nothing weird, error log is empty.

Tried to login to MySQL using the following command:

mysql -u root -p

it says "Enter Password" then after I enter the password it takes forever.

then I decided to restart mysql sudo service mysql restart and it removed the slowness and fixed the problem which I hate, because I don't know what was the problem and how to avoid that in the future or fix.

What do I need to look at in this case, how to get more information about that issue?


texts:
I have a table which has 1 million record.
When I run

Select count (*) from accounts;

It took around 2 min.
But when I deleted 300 thousands record and run the same query it is still taking same time.
I read in Google many things. Should I do enable row movement and shrink space or
 should I gather the statistics. Which is helpful or both are necessary.


texts: Really big LOG backups on a DB with Image field

texts:
Normally, our log backups has < 10MB size In a big database with a lot of transactions per hour ).

But now we have a database, that is storing Images on a Image field.

the problem is, the database is HUGE ( it has now 500GB MDF, and 700GB on LDF file )and every log backup has more than 70GB !

What is the best way to store Images on a database? I've seen a lot of posts saying about store the Physical patch of the image on a VARCHAR field. But this way, we need to store the images in some place, right? It will fill the storage in the same way. Or am I wrong?

We have full backups every day at 22:00 and log backups every hour.

This is the sizes of the log backups.

EDIT1:

Each "Image" has a binary code, and it has 43679 characters ).
1 millions rows for this table ( 1.141.947 images ( with coduser, image, and etc )

EDIT2:
 We are now using varbinary(max), but it's not solving our problems. It has now almost 700mb after 20 minutes.


texts:
I was looking for the last time users have been logged into the system and saw SA as being used. Strange is that SA is disabled by default and not used on our systems. How is that possible, what am I missing?
Here is the code used to list users:
SELECT login_name [Login] , MAX(login_time) AS [Last Login Time]
FROM sys.dm_exec_sessions
GROUP BY login_name;
And here is the SA listed as disabled and as logged in:

Thanks.


texts: Why does the secondary server not failover?

texts:
I hope someone can help. I will try to summarise as best as I can.
We have four servers in a cluster with an AG set with Database Health Detection. Three servers are on site1 with the fourth on a separate DR site2. We are testing DR by cutting the link between the two sites.
As expected, when the link is cut, the three servers on site1 stay up and the server on site2 loses quorum and the database goes into recovery mode until the link is re-established.
If I perform a manual failover to site2 before we cut the link, the same thing happens and I have to force the quorum manually to bring the server back up on site2.
This isn't a big deal, but I would prefer it to stay up, so I added an Azure witness to the cluster. However testing has given us unexpected results.

With an Azure witness, if I shut down the 2 secondary servers on site1 before we cut the link (leaving just one server on each site), then cutting the link initialises an automatic failover to site2. This is great, but I want the other servers to be left up and running.
If I leave the 3 servers up on site1 up and we cut the link, the server on site2 loses Azure (even though it still has internet) and the server loses quorum.  That I don't understand at all.

Wouldn't the server in site2 think that it was the last remaining server and failover to become the primary?
Additionally, I've tried to find out what the exact events are that happen when a failover occurs.

Does the primary tell the secondary to failover and if so, how does that work if the link is cut? Surely it can't.
Or does the secondary realise that there is no handshake and assume it's now the Primary? If so, wouldn't I end up with both sites having a primary server?

I've tried searching for sources of information. Any help would be greatly appreciated.


texts: Suggestions on database model design/ performance

texts:
I'm currently building an 'online examination' type of web app using Ruby on Rails and PostgreSQL (or MySQL), which is going to be part of a bigger application once pushed to production. Since I just recently got into RD design I'd appreciate some feedback on the following design.

App Specifications

There are 1000+ different questions. Each question can have 2-5
different options, only 1 option is correct.
The user is presented with 10 questions at a time.
If a user answers a question correctly 2 times, the question will not
be shown to him again (let's call those questions now completed).
If a user answers a question incorrectly then he needs to answer the
question 2 MORE times correctly.
The user passes the exam when there are no more questions left to
show.

My current database schema is:

user
-user_id
-name etc

question
-question_id
-text
-correct_id (or option_id, stores the correct option id)

option
-option_id
-text
-question_id

user_answer
-user_id
-question_id
-is_correct

So in order to create a '10 question test' we could:

create a list of every completed question querying the user_answer table
create a list of possible questions (all questions - completed questions)
finally serve the user with 10 random questions from the last list.

Is there a better approach to represent "the remaining times a question needs to be answered" inside the database? Please feel free to suggest even a completely different design.

What concerns me is that for each single user there would be more than 2x1000 user_answer rows. This quickly becomes a huge number, so I guess there must be a better way.

Would using a json array be okay in this case? But as far as I understand it'd quickly become a hassle to add more questions etc.


texts: help in multi-language medical database?

texts:
My project is to design a medical database for many countries.

This database contains many parts: medicines, medical centers, patients, etc. for every country with its own language.

I started my design with medicine part but I'm having many issues continuing:

How can I put all these complex parts in one database with different languages? I can't imagine any suitable design for that complex idea.
How can I deal with the database growth from data coming fast for each part from each country?
I read about primary files and secondary files and how they increase performance of a database. Should I made them for my database? Or should I use the default files?
Should I separate every country with its own parts in a separated database?

I uploaded 2 pics for the initial design for medicines part in English language only.

NOTE: the 2 pics are only one but it's too large so I divided it into 2 halves

I use SQL Server 2008.


texts: SQL Get Users By One Group But Exclude Others

texts:
I'm trying to write a query that get's all users that belong to Group A, but excludes users that belong Groups B, C & D.

Users can belong to multiple groups.

I have the following query but it's not working:

SELECT id
FROM users
LEFT JOIN groups USING (user_id)
WHERE groups.name = 'A'
AND groups.name NOT IN ('B', 'C', 'D')

This just selects all users from group A, how do I exclude anyone who also belongs to groups B, C & D?


texts: sqlite database: advanced SQL or redesign

texts:
I've been programming for several years, but I'm pretty new to SQL(ite).

I'm designing a database for a sort of a bookkeeping program. What I've got so far:

orders
    (o INTEGER PRIMARY KEY,
     customer INTEGER REFERENCES customers(c),
     num_products INTEGER,
     date STRING,
     discount INTEGER,
     comments TEXT,
     status INTEGER)

o_products
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     p_0 INTEGER,
     p_1 INTEGER,
     p_2 INTEGER, etc.

o_qtys
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     q_0 INTEGER,
     q_1 INTEGER,
     q_2 INTEGER, etc.

o_prices
    (o INTEGER PRIMARY KEY REFERENCES orders(o),
     p_0 INTEGER,
     p_1 INTEGER,
     p_2 INTEGER, etc.

The customers and products tables are irrelevant.

Now, this design works well for retrieving the products, quantities and prices in an order, but I'm not sure how to do something like figure out total quantity of a product ordered in all orders. I know I could run a lot of queries and sort through the data in my code, but I'm looking for one elegant query, something like SELECT sum(ordered of this product). I could turn the o_products table on its side, to list what orders each product is in, but then it would be hard to retrieve what products are in one specific order. I've done some searching, but I can't seem to find an answer.

Is there a good way to do this, or should I redesign the database? If I should redesign it, how?


texts:
I am creating log shipping for two databases, db1 and db2.
It will be from SQL Server 2008 R2 to a newer version, SQL Server 2012.
On SQL Server 2008R2, they are both dbi_version = 661:
DBCC TRACEON (3604);
GO
DBCC PAGE (db1, 1, 9, 3);
GO
DBCC TRACEOFF (3604);

I restore them in standby mode:
RESTORE DATABASE db1 FROM DISK = 'Q:\db1.bak' WITH STANDBY = N'R:\SQLLog\db1.undo'

One of them restores ok, with upgrade, and another one is giving me error:

This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY.


texts: How to identify the ranges over which a postgres table was partitioned?

texts:
If I create a table and partitions like this...

CREATE TABLE tab1 (a int, b int) PARTITION BY RANGE(a);
CREATE TABLE tab1_p1 PARTITION OF tab1 FOR VALUES FROM (0) TO (100);
CREATE TABLE tab1_p2 PARTITION OF tab1 FOR VALUES FROM (100) TO (200);

how can I subsequently check the ranges? I've tried browsing the information_schema.tables and information_schema.table_constraints but no luck so far.


texts: What is the alternate to SA account in SQL Server?

texts:
My client is currently using SQL Server for their app. At this time they are using SA account everywhere. For security reasons we need to disable the SA account. The solution I've is create another power user (not admin), like standard user and root user. The development only needs access for CRUD operations. So, what should be the rights/roles/privileges for this new account? We need to replicate the same model to the internet server where only read rights are required (from viewer's end). How do I go about it?

Two different versions, 2008 R2 and 2012.


texts:
I am having a hard time creating a pub/sub database schema with stateful metrics.

Saying it aloud it sounds easy enough:

  "There are many organizations that will subscribe to checklists that
  are derived from guiding documents for ALL org's. The org's will
  maintain their own answers for the checklists (T/F), not all org's
  will need to answer all checklist items. When an author changes a
  checklist item, the org's should be notified."

What I have thus far:

ERR.mwb

The desired result would be the following:
Organization #42

Department

Checklist ABC Metrics = 8/10

Item Metric = True
Item n...

Checklist n...

Department n...

What I am looking for:

Is this architecture close to what I "say" I am looking for?
Do pub/sub schemas require many "bridged" tables, as I'ved used?
I know updated and created dates would be necessary too but, how do you maintain the state of each department and there answers (Yes/No, True/False) to all of these items within all of the checklists?
Is this a Key->Value, Document-Store, Relational, polyglot solution or etc... at its core?
I would like to receive any high-level or detailed schemas, links to resources/articles.

Thank you.


texts:
In DB2, there is this command that shows active EDUs (engine dispatchable units):
db2pd  -edus

There are two fields in the respective output: EDU ID and TID.
According to this db2pd page from the DB2 knowledge center, each is defined as follows:

EDU ID: The unique identifier for the engine dispatchable unit (EDU). Except on Linux operating systems, the EDU ID is mapped to the thread ID. On Linux operating system the EDU ID is a DB2 generated unique identifier
TID: Thread identifier. Except on Linux operating systems, the thread ID is the unique identifier for the specific thread. On Linux operating systems, this is a DB2 generated unique identifier

I wanted to know what is considered EDU or thread on a Linux/Unix operating system? Are they the same? What is the difference?


texts: How to shard 14 gb of data for archival database?

texts:
We have 14 GB worth of CSV's containing product information, with each CSV being all the records for a US state.  We now want to store this in a database.  There would be ~130 million records.

The data only needs to be written once.  No new data will be added to this database.  It will be read from ~10 times per hour.  Almost all of the queries will use one indexed field.  We're looking for a response time under 5 seconds.

Would MySQL be appropriate for this, and if so, what is the minimum amount of RAM you would think the server needs?  Should it be sharded by state?  If not MySQL, what other options do we have?


texts:
I tried searching on this topic but I couldn't find any help on it. I want to write a CREATE TABLE query with SELECT having NOT NULL fields. The query I am using to create the table is as follows:

CREATE TABLE tbl_tsLiLpLgIspFiFp
SELECT timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort,
COUNT(*) AS `countSamefPort`
FROM union_of_outbound_threats
GROUP BY timeStamp, localIp, localport, localgeo, isp, foreignip, foreignPort;

I want the fields to be set as NOT NULL. What extra parameters should I add? If this was addressed before please direct me to it :)


texts: Selecting non-aggregate column on 3 table inner join

texts:
I have 3 tables in a standard parent/child kind of "belongs to" relationship in postgres:

donations --(belong to)--> campaigns --(belong to)--> organisations

Each table has an id column as a primary key, that's used as a foreign key by the child table
If I'm doing a SELECT with a GROUP BY on the table, I can't select a column from the  related organisations table without an aggregate function, even though there can only be one joining row. I've always worked around this by using MAX() since I know there's only one row for the join, eg
SELECT
campaigns.name,
MAX(organisations.name),
SUM(amount)
FROM donations
JOIN campaigns ON campaigns.id = donations."campaignId"
JOIN organisations ON organisations.id = campaigns."organisationId"
GROUP BY campaigns.id;

but this feels like quite hacky. Is there a more correct way to do this?


texts: SQL Query to look for nulls from two different tables while adding in a new column that states what table its from

texts:
I need help with a query that I am trying to make. I have two tables, Orders and Process and I am trying to find instances where the OrderName and ProcessTitle in each table is null and I want to add in a column in the query to state what table it is in.

Orders Table

OrderName                   OrderNumber        OrderStatus
-------------------------------------------------------------
 xx                                   1          Active
 xx                                   2          Active
 bbb                                  3          Active
 abc                                  4          Active
 NULL                                 5          Inactive

Process Table

 ProcessTitle                   ProcessNumber   ProcessStatus
-------------------------------------------------------------
 abc                                  1          Active
 abc                                  1          Active
 db                                   2          Active
 BG                                   3          Inactive
 BG                                   3          Inactive
 Null                                 4          Active
 Null                                 4          Inactive

Here is what I have so far. I queried both tables to show where the OrderName
and ProcessTitle to display where there are nulls by using the union function.
I am still new to SQL and am still learning but I wasn't sure how to search
this question. I feel like I should be using a case statement to achive this but how
would I reference both tables? Thank you in advance.

Select OrderName,
       OrderNumber,
       OrderStatus
From Orders
where OrderName is null
UNION
Select ProcessTitle,
       ProcessNumber,
       ProcessStatus
where ProcessTitle is null


texts: What is the equivalent of OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5?

texts:
I need an equivalent way to getting OVER (PARTITION BY Account ORDER BY TranDate ROWS UNBOUNDED PRECEDING) for Firebird v2.5

Here is the full code:

DECLARE @Test TABLE (
    Account     INTEGER,
    TranDate    DATE,
    TranAmount  NUMERIC(5,2));
INSERT INTO @Test (Account, TranDate, TranAmount)
VALUES  (1, '2015-01-01', 50.00),
        (1, '2015-01-15', 25.00),
        (1, '2015-02-01', 50.00),
        (1, '2015-02-15', 25.00),
        (2, '2015-01-01', 50.00),
        (2, '2015-01-15', 25.00),
        (2, '2015-02-01', 50.00),
        (2, '2015-02-15', 25.00);
SELECT  Account, TranDate, TranAmount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS UNBOUNDED PRECEDING) AS RowNbr,
        COUNT(*) OVER (PARTITION BY TranDate) AS DateCount,
        COUNT(*) OVER (PARTITION BY Account
                       ORDER BY TranDate
                       ROWS BETWEEN 1 PRECEDING AND CURRENT ROW) AS Last2Count
FROM    @Test
ORDER BY Account, TranDate;

and ROW_NUMBER() equivalent if possible.

Thank you in advance.