texts:
I am creating a toy website locally with XAMPP and I want it to support Unicode.

index.php contains a simple form that I can use to add a new entry (a UTF-8 string) to a table "myTable" in the DB. In addition to that, I am also echoing with PHP every entry from "myTable" on the body of index.php, so that I can see if the UTF-8 strings that I am entering through the form make all the way into the database and back without any issues.

Here are the measures that I took toward Unicode support:

Every file on my website (ex.: index.php) is encoded in UTF-8 without BOM;
<meta charset='utf-8'> everywhere applicable too;
mysqli_set_charset($myConnection, "utf-8"); in my "db.inc.php" file;
At the phpMyAdmin "control panel" (?), under "Database Server" I see "Server charset: UTF-8 Unicode (utf8)";
I created a database and a table for this specific purpose. Here is the result of SHOW CREATE TABLE `myTable`:

CREATE TABLE `myTable` (
`row1` varchar(16) COLLATE utf8mb4_bin NOT NULL,
`row2` varchar(32) COLLATE utf8mb4_bin NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

The outcome was as follows: when I print the entries of "myTable", everything does get displayed correctly on the browser indeed, however only owing to a stroke of luck, because apparently my strings are being internally represented badly. When I browse my table with phpMyAdmin I see mojibake, and I looked over at myTable.ibd and I see the same mojibake.
The string joão e maría for example, is displayed as joÃ£o e marÃa; for the first ã -> Ã£, it could be UTF-8 bytes being interpreted as latin-1, though I see Ã£ even when I tell npp to interpret the file as UTF-8.

Running the query SELECT `row1`, HEX(`row1`) FROM `myTable` WHERE 1 I see 6A6FC383C2A36F2065206D6172C383C2AD61 as the bytes of joÃ£o e marÃa, so in fact the mojibake is "correctly" encoded as UTF-8 (C3 83 C2 A3 is UTF-8 for Ã£).

Ideas?

UPDATE

After more careful inspection I verified that mysqli_set_charset($myConnection, "utf-8"); was returning false. I replaced it with mysqli_set_charset($myConnection, "utf8"); and voilà, strings with fancy characters that I enter through the form now make it all the way into the database unscathed. However, these new strings aren't correctly displayed by the browser.

For example: the string bênção is inserted into the DB through the form. I query the table with SELECT `row`, HEX(`row1`) (...) and find out that it is stored as 62C3AA6EC3A7C3A36F inside the table, which is the correct UTF-8 representation of the string in question. I access index.php and I see b�n��o being displayed. I download the HTML of the webpage and inspect it, and I find that the offending bytes are respectively EA, E7, E3, which, surely enough, correspond to ê, ç and ã in latin-1.


texts: Problem: Remote connection with pgAdmin4

texts:
I just installed the Java and PostgreSQL environment on a server (for remote testing) CentOS 7

From the command line using ssh would seem that there are no problems in the sense that I can access the user "postgres"

But when I try to access to PostgreSQL via PgAdmin4 I get an error, or get

Unable to connect to server:

could not connect to server: Connection refused
Is the server running on host "81.xxx.2.xx" and accepting
TCP / IP connections on port 5433?

I thought it was the firewall so I typed

firewall-cmd = public --zone --add-port = 5432 / tcp --permanent

but my server get

FirewallD is not running

Any idea to solve the problem ????


texts: Is it "OK" to have a table that only contain foreign keys?

texts:
By "OK", I mean "it doesn't have any bad impact with the overall database design".

For example, will it cause insertion problems?
Or will it be a problem in the future when I add field in the table?

For example, I have a simple database with three tables:

Customer(CustomerID, CustomerName)
Product(ProductID, ProductName)
Order(OrderID, CustomerID, ProductID)

It is clear that the Order table is only a mapping between the two tables (Customer and Product). The OrderID in table Order has no use, it is there only to identify each row.

How about if I don't want to identify each row because for example we have the rule:

  A user can't order the same product twice.

Of course, it is far from reality, but it is only an example.

The bottom line question is, in the example given above, is it OK to remove the OrderID in the table Order and setting both ProductID and CustomerID from table Order as a composite primary key? In this case, the Order table only contains foreign keys and are set as a composite primary key.

As a beginner, I have searched a lot about database design concepts and found some terms like "primary key", "composite primary key", "database normalization", etc., but I don't know what will happen in practice so I decided to ask here. I am just worried about its future implications because I don't know if there is a particular circumstance that it will be a disadvantage.


texts: Sync two mysql database one in intranet and the other on internet

texts:
I'm making the training and placement module for my college. But I'm facing problems to sync the databases, one that will be used by the students and the other that will be used by the HR of a company. As the students's DB is completely on intranet and is not accessible via internet. As the HR will be using the online version and will be providing information on that. I want to sync the data between both version.

I have read about database replication but that cannot be implemented as these versions cannot be connected.

We cannot use mysqldump as sometimes there will be modifications in the intranet DB that has to be replicated in the online DB.

As the operators of this system don't belong from technical background: I thought of coming with a file based solution where on just click of a button we can download the file and sync up with the intranet version and vica-versa.

But the problem is that I cannot come up with any decent solution till now.
How can I solve this problem?


texts: How to count all relationships in a database?

texts:
I am developing a web application, which stores data in a Microsoft SQL Server database. My project manager has to deliver a report to non-tecnhical higher-ups, and thinks that it will read impressive if we include the sentence "Our database has X tables, connected by Y relationships".

I just counted the tables by hand in the treeview of Management Studio. But how do I get a count of all relationships in the database? I'd prefer something quicker than generating the schema and ticking off each connector by hand.


texts: Will update occur even if the join fails?

texts:
I'm trying to debug some issues in an application and stumbled upon the following Query:

SET o.[IsProcessed] = 1,
    o.[Status] = f.[Flag],
    o.[IsProcessedTime] = GETDATE()
FROM [Customs].[dbo].[Out] o
INNER JOIN [Storage].[dbo].[Facts] AS f
ON o.Msgd = f.MsgID
WHERE o.IsProcessed = 0

Question, will this Query still update the Out table with the 1 and GETDATE() values even if the join fails due to missing corresponding data in the table f?


texts: Group by age range and gender with aggregation function on some columns

texts:
In Mysql, I have a patient table with id, birth_year and gender. Next table is transactions in which I have id, patient_id [FK from patient] and checkup_date. I need to select rows such that I get number of checkups for all males and females in the range of ages.

How do I query to achieve the following tabular result ?

under 20, male: 10 times, female: 15 times

21 -  30, male: 20 times, female: 35 times

31 -  40, male: 30 times, female: 45 times

....      .......        .........      .......     ........    ..........

81 -  90, male: 10 times, female: 6 times

above 90, male : 0 times, female: 4 times

Thank you.


texts: LATCH_EX Waits on Resource METADATA_SEQUENCE_GENERATOR

texts:
We have a process that generates an inventory report.  On the client side, the process splits of a configurable number of worker threads to build a chunk of data for the report that corresponds to one store out of many (potentially thousands, typically dozens).  Each worker thread calls a web service that executes a stored procedure.

The database process for processing each chunk gathers a bunch of data into a #Temporary table.  At the end of each processing chunk, the data is written to a permanent table in tempdb.  Finally, at the end of the process, one thread on the client side requests all the data from the permanent tempdb table.

The more users that run this report, the slower it gets.  I analyzed the activity in the database.  At one point, I saw 35 separate requests all blocked at one point in the process.  All these SPIDs had on the order of 50 ms waits of type LATCH_EX on resource METADATA_SEQUENCE_GENERATOR (00000010E13CA1A8).  One SPID has this resource, and all the others are blocking.  I did not find anything about this wait resource on a web search.

The table in tempdb that we are using does have an IDENTITY(1,1) column.  Are these SPIDs waiting for the IDENTITY column?  What methods could we use to reduce or eliminate the blocking?

The server is part of a cluster.  The server is running 64-bit SQL Server 2012 Standard Edition SP1 on 64-bit Windows 2008 R2 Enterprise.  The server has 64 GB RAM and 48 processors, but the database can only use 16 because it is the standard edition.

(Note that I'm not thrilled by the design of using a permanent table in tempdb to hold all this data.  Changing that would be an interesting technical and political challenge, but I'm open to suggestions.)

UPDATE 4/23/2013

We've opened a support case with Microsoft.  I'll keep this question updated as we learn more.

UPDATE 5/10/2013

The SQL Server support engineer agreed that the waits were caused by the IDENTITY column.  Removing the IDENTITY eliminated the waits.  We could not duplicate the issue on SQL 2008 R2; it occurred only on SQL 2012.


texts: Can a Key Preserved Table have a Composite Primary Key, on Oracle db?

texts:
I'm trying to create an updatable view.

I understand that the db must be able to identify one single row that I'm trying to update.

I believe I satisfied this requirement. There are no aggregates. Each row in the view has a 1-to-1 relationship with rows in the source table which I'm trying to update (MY_CFG_TABLE below).

But my view is not updatable. Why not? Does it have something to do with the composite key?

CFG_VALUE is the column I want to update.

I want a join-view, rather than subquery, so that i can see, in the view, rows in ALL_TAB_COLUMNS for which there's no corresponding row in MY_CFG_TABLE (for that, it will have to be changed to outer join, but for now just trying to make the update work with an inner.)

In the end, I may resort to an INSTEAD OF trigger, but for now I would like to make an updatable join-view-- or understand why I cannot.

Here's the update statement which fails:

update VBA_CFG_JOIN_V
SET CFG_VALUE = 'TRUE'
WHERE TABLE_NAME = 'APN_GR'
AND COLUMN_ID = 1
AND CFG_NAME = 'PART_ID';

Here's the error message:

  cannot modify a column which maps to a non key-preserved table

How can I make my source table, MY_CFG_TABLE, a Key Preserved Table?

View:

CREATE VIEW VBA_CFG_JOIN_V
AS SELECT F.TABLE_NAME, T.COLUMN_NAME, F.COLUMN_ID, F.CFG_NAME, F.CFG_VALUE
FROM ALL_TAB_COLUMNS T, MY_CFG_TABLE F
WHERE F.TABLE_NAME = T.TABLE_NAME AND F.COLUMN_ID = T.COLUMN_ID;

Source tables:

ALL_TAB_COLUMNS is a built-in Oracle view. (Note, I'm not trying to update ALL_TAB_COLUMNS).
https://docs.oracle.com/cd/B19306_01/server.102/b14237/statviews_2094.htm

MY_CFG_TABLE:

CREATE TABLE "GSM_OWNER"."MY_CFG_TABLE"
   (    "TABLE_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "COLUMN_ID" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_NAME" VARCHAR2(20 BYTE) NOT NULL ENABLE,
    "CFG_VALUE" VARCHAR2(20 BYTE) NOT NULL ENABLE,
     CONSTRAINT "VBA_FIELDS_PK" PRIMARY KEY ("TABLE_NAME", "COLUMN_ID", "CFG_NAME")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS"  ENABLE
   ) SEGMENT CREATION IMMEDIATE
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
  TABLESPACE "GSM_OWNER_TS" ;


texts: Strange UPDATE results in presence of merge replication

texts:
I have an MS Access front-end database that accesses an SQL Server backend. This (as far as we can tell) works flawlessly when the SQL Server database is not replicated. Once we introduce replication (from SQL Server Standard on a proper server as the publisher to SQL Server Express on a laptop as a subscriber using Merge Replication) we start getting weird database corruption that I can't explain.

What happens is that we have UPDATE queries which run against several tables in the database, and we also have Access itself doing CRUD operations against multiple tables as well (this corruption is not limited to one table). The result we see is that randomly (about 1 in 10 operations) a row other than the one we wanted to update is being updated, which overwrites data we didn't want to overwrite.

The replication, when set up, runs on demand on the laptop as a pull merge, and the corruption happens regardless of whether replication is performed. It only has to be enabled. No corruption seems to occur when replication isn't enabled.

I'm not accusing Microsoft of any fault here - it's entirely probable I've just forgotten to tick some box to prevent this. I'm just not sure what it is that I need to look for.

Edit: What I mean by corruption is this: Let's say I have rows:

ID | FirstName | LastName
--------------------------
1  | John      | Smith
2  | Emma      | Citizen
3  | Bob       | May

I then run something along the lines of:

UPDATE Table SET FirstName = "Test" WHERE ID = 1

And after that happens I end up with this:

ID | FirstName | LastName
--------------------------
1  | Test      | Smith
2  | Test      | Citizen
3  | Bob       | May

There are no error messages in any of the system tables dealing with replication. The only change in the schema is that when replication is enabled it creates the rowguid column.


texts:
I have a very slow running SQL query that is run from my Rails app using the Model.find_by_sql method. I've made a number of optimisations but it's still extremely slow. It would be great if anyone can offer some guidance on ways this could be further optimised.

Currently there are approx 16 million lines in the DB and it's growing by around 100k per day.

The schema (taken from my Rails App's schema file) is as follows

create_table "daily_sales", :force => true do |t|
  t.date     "date"
  t.string   "vendor"
  t.string   "media"
  t.string   "upc"
  t.string   "isrc"
  t.string   "product_type"
  t.integer  "units"
  t.string   "country_code"
  t.string   "vendor_identifier"
  t.string   "parent_identifier"
  t.integer  "client_id"
  t.integer  "sub_client_id"
  t.integer  "status",               :default => 0
  t.integer  "a1"
  t.integer  "a2"
  t.integer  "a3"
  t.integer  "a4"
  t.integer  "a5"
  t.integer  "vendor_type"
  t.integer  "release_id"
  t.integer  "product_id"
  t.integer  "t_id"
  t.string   "a_name1"
  t.string   "a_name2"
  t.string   "a_name3"
  t.string   "a_name4"
  t.string   "a_name5"
  t.string   "release_title"
  t.string   "product_cat_no"
  t.string   "product_format"
  t.string   "label_group_name"
  t.string   "sub_label_name"
  t.string   "t_name"
end

Indexes are as follows:

add_index "daily_sales", ["date", "vendor_type", "vendor", "product_type", "client_id", "sub_client_id", "release_id", "product_id", "t_id", "status", "country_code", "media"], :name => "main_index"
add_index "daily_sales", ["client_id", "vendor", "date"], :name => "index_daily_sales_on_client_id_and_vendor_and_date"
add_index "daily_sales", ["status", "client_id"], :name => "index_daily_sales_on_status_and_client_id"
add_index "daily_sales", ["upc", "media", "vendor"], :name => "index_daily_sales_on_upc_and_media_and_vendor"

The offending query, which is designed to get headline sales figures for a two given date ranges (current and previous period for comparison) is below:

SELECT
 SUM(CASE WHEN date BETWEEN '2015-05-12' AND '2015-05-25' AND vendor_type = 1 AND vendor <> 'xyz' AND product_type = 'a' AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p1_dad,
 SUM(CASE WHEN date BETWEEN '2015-05-12' AND '2015-05-25' AND vendor_type = 1 AND vendor <> 'xyz' AND product_type = 't' AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p1_dtd,
 SUM(CASE WHEN date BETWEEN '2015-05-12' AND '2015-05-25' AND vendor_type = 2 AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p1_dts,
 SUM(CASE WHEN date BETWEEN '2015-05-12' AND '2015-05-25' AND vendor_type = 3 AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p1_ps,
 SUM(CASE WHEN date BETWEEN '2015-04-28' AND '2015-05-11' AND vendor_type = 1 AND vendor <> 'xyz' AND product_type = 'a' AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p2_dad,
 SUM(CASE WHEN date BETWEEN '2015-04-28' AND '2015-05-11' AND vendor_type = 1 AND vendor <> 'xyz' AND product_type = 't' AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p2_dtd,
 SUM(CASE WHEN date BETWEEN '2015-04-28' AND '2015-05-11' AND vendor_type = 2 AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p2_dts,
 SUM(CASE WHEN date BETWEEN '2015-04-28' AND '2015-05-11' AND vendor_type = 3 AND status = 1 AND client_id = 1 THEN units ELSE 0 END) as p2_ps
 FROM daily_sales

Finally the output of explain:

 ------------------------------------------------------------------------------
  Aggregate  (cost=2809686.80..2809686.81 rows=1 width=34)
    ->  Seq Scan on daily_sales  (cost=0.00..637481.52 rows=15515752 width=34)
 (2 rows)

Completed 200 OK in 21372ms

I imagine my indexing may be off, or could it be that certain fields (product_type for example) would be better suited to being an integer or even boolean field. I'm clearly no DBA and would appreciate some help.

Thanks in advance!


texts: Ignoring specified tables on replication primary

texts:
I need to make a primary-secondary replication for some joomla portals. However, high traffic means that a lot of inserts, deletes and updates happen on the %_session tables, something that isn't really necessary to replicate.
So, I've added the following line to my primary's my.cnf file:
replicate-wild-ignore-table=%.%\_session

I've stopped and started the primary server, but when I check the bin.logs, I can see that all operations with _session tables are still recorded and prepared to be sent to the secondary.
So my question is, how do I force the primary to ignore any and all operations on tables that end in _session?


texts: Lost admin user ; Update not allowed on current user in MySQL

texts:
I work on MySQL. For making my database more secure I created new user and than deleted 'root' user which had all privileges. I think I did not give full privileges to new user. That's why it is giving error whenever I try to update a table in database.
UPDATE command denied to user 'crm_user'@'localhost' for table

In case I lost the superuser (in my case it was root) how can I retrieve or get all privileges to get control on my DB. DB is installed on my VPS so I have full control whatever need to do but I have no idea what to do. Please help. Thanks


texts: Index creation leads to large transaction log growth

texts:
I have an approximately 1 billion row table. I then create an index as follows:

CREATE NONCLUSTERED INDEX [IX_Index1]
ON [dbo].[MyTable]( [CustomerID] ASC )  -- CustomerID is uniqueidentifier
WITH (DATA_COMPRESSION=PAGE);

This results in a 9.15GB index (compressed), however the transaction log went from empty to 158GB while the index was being created (no other DB activity). Why is the amount of log space used that much higher than the resulting size of the index?

The database is SQLServer 2014 Enterprise in full recovery mode in an availability group.

Update
I dropped and recreated the index with the option SORT_IN_TEMPDB = ON. The resulting transaction log writes were 10.4GB for the DB's tlog and 1.27GB for the tempdb tlog.


texts: Connecting to database engine again when a new query or running view

texts:
When I connect to a server

via SSMS, I can connect fine albeit a slow.

When I click on a new query or try to run a view in a database on this server, the same prompt comes up but with everything greyed out and then after a while the prompt disappears and the view runs or new query tab opens:

It's like SSMS is trying to connect to the server again every time I run something or even when clicking new query.  How do I stop this prompt appearing?


texts:
OK I have 3 tables:

client:

ClientID (int)auto-inc

clientName (varchar)

tnt_group:

groupID (int)auto-inc

ClientID (int)foreign-key

tnt_item:

itemID (int)auto-inc

itemNo (int)

groupID (int)foreign-key

Now, I need to generate an itemNo for each record in tnt_item, starting from 1 for each ClientID so each client see's their item count starting from 1. I have tried adding a trigger as follows:

BEGIN
    SET NEW.itemNo = (
        SELECT IF(t.itemNo IS NULL, 1, IFNULL(MAX(t.itemNo), 0) + 1)
        FROM (
            SELECT IFNULL(i.itemNo, 0) AS itemNo, g.groupID, g.ClientID, cg.ClientID AS cgClient, cg.groupID  AS cgGroup
            FROM tnt_group cg
            INNER JOIN tnt_group g ON cg.ClientID = g.ClientID
            INNER JOIN tnt_item i ON i.groupID = g.groupID
        ) AS t WHERE t.cgGroup = NEW.groupID
        GROUP BY cgClient
    );
END

This is working, in the case where a client already has at least 1 asset, it gets the max itemNo then adds one. where it is failing, is if a client does not have any items yet, no matter what I do, it sets the first itemNo as NULL

Any tips on what i am doing wrong?


texts:
I'm trying to install Oracle 12 but at the end of the installation I got this error

  Invalid SID or Service name.
  Enter valid SID or Service name.

  ERROR - RCU-6090 Connection step validation failed.
  CAUSE - RCU-6090 Skipping main operation: failed to connect to database because database details were missing or invalid.
  ACTION - RCU-6090 Provide correct database details and try again.

I'm lost and I don't know what to do at the moment.


texts: Two master databases for two separate apps

texts:
We have two different websites one is hosted in Asia datacenter called it asiadata.com and other site is hosted in Europe datacenter called it europedata.com. Currently we have only one master database server which is hosted in Europe datacenter and it’s responsible for all writes from both websites and to replicate on underlying slaves on Europe and Asia datacenter.Also both apps writes on common database.

We have complains from asiadata.com users about site slow browsing and posting and things are not visible immediately on asiadata.com. After digging we found that database is culprit because it needs to write on Europe datacenter master server first and needs to come back on Asia data center database servers slaves to synchronize data and on Europe datacenter slaves as well. All this process takes time because of geographical distance between Asia and Europe and because of network latency.

Now we decided to resolve this issue by having multiple write servers (masters) each on Asia and Europe datacenter so each site will write data locally and both masters will also be slaves of each other. And each master is responsible to replicate data on it’s own datacenter only on underlying slaves. For conflict resolution we will control it on code level. e.g. to avoid same username and other unique constraint. Hopefully, this will resolve split brain situation and this will make sure neither master broke.

Can someone please let me know if i missed anything on multiple write masters or any scenario on database level which break up replication ? I thought this will be the best way to improve my apps performance but along with that I am afraid to built this solution because MySQL community doesn’t encourages multiple write masters. Also, 90% of the database tables are MyISAM and replication is single-threaded. I am afraid both masters might will not produce the best performance because of table-level locking as both masters will also be slaves at the same time.

Also, is there any automated tool available to resolve conflicts and solve replication issues between both masters ?

Overall, please comment multi master be the best solution or is there any better alternatives.

Thank you.

Thank you for the suggestions.
But still many questions left.

Is there any automated tool available to resolve conflicts and solve replication issues between both masters ?

As long as database data concerns, we have to synchronize both datacenter data to each other (Asia & Europe). We can't restrict data to it's related datacenter only.


texts:
I am trying to setup a cluster of 3 servers in 3 different locations; Dallas-US, London-UK, Mumbai-India. On each location I have setup a webserver and db server. On db server I have configured Galera Mariadb Multi-Master cluster to replicate db among all three servers. My each webservers are connected with local IP to their regional db server. I am expecting that my Dallas webserver will fetch db records from Dallas db server only; London webserver from London db server only and Mumbai webserver from Mumbai db server only. So I am having this setup
Dallas: 1 Webserver, 1 db server connected to webserver with local IP
London: 1 Webserver, 1 db server connected to webserver with local IP
Mumbai: 1 Webserver, 1 db server connected to webserver with local IP
Everything is working well but I have found that mysql query takes much time above 100s while fetching record. I have tried Mariadb with single instance and its fetching data within 5s.
What am I doing wrong? Is it possible to setup something like this where every db server will geo-replicate its data but every webserver will fetch data only from its local db server?


texts: Getting slaves of a master-master setup stopped in sync

texts:
I have a master-master setup where each master has it's own slave.  They layout might be illustrated as:

 M1 <===> M2
 |         |
 \/        \/
 S1        S2

That is both S1 and M2 slave from M1;  both S2 and M1 slave from M2.

I'm wanting to get it to a point where S1 and S2 are stopped and should be in exact sync.

As I try thinking this out I keep running into gotchas that won't quite work out.

Initial niave approach:

Flush tables with read lock on both masters, ensure the slaves are all caught up
to their respective masters position and just stop slave.  Even if that's scripted to happen one after another it's just a race condition where say a write got to M2 and subsequently S2, but had been been blocked from getting to S1 b/c of M1's read lock (which made it in before).

My second plan was:

Stop slaving on S1, M2.
Flush tables with read lock on M1.
Start slave until M1's master log pos on S1,M2
Stop slave on S2, M1.
Flush tables with read lock on M2.
Start slave until M2's master log pos on (S2,M1)
Start slave on M1, M2, leaving s1,s2 stopped

The problem though is by step 4, there might have been writes on M2 which had since propagated to S2 but where blocked from getting to S1 b/c of M1's read lock.

Another idea:

Stop slaving on M2 and S1.
Start slaving on M2/S1 until some common position on M1.
Change master on s1 to M2 (s1 and s2 are now slaving directly from M2)
stop slaving s1, s2, start until common position on M2

Problem again being during the shuffling there could have been some M2 writes that made it to s2 but never s1.

Can anyway think of a clean way to tweak these approached to make it work?

One of my requirements is having minimal downtime for the Master-Master cycle while allowing more extended replication downtime for the slave-only instances.

Even if it's overly complicated what ever approach I go would with end up being scripted so if there's some kind of rapid looping/checking until just the right time that shouldn't be a problem.

Edit In Response to Rolondo's solution:

An end goal of what I'm trying to accomplish with this is to stop slaving (at least sql_thread execution) on S1 and S2 to have time to do some lengthy comparisons (checksuming etc).

A concern I have is after step 04 is even though these steps are stopping/starting the master SQL threads, the slaves (S1/S2) replication to their respective masters (M1/M2) is still going.  There is a non zero amount of time that occurs between measuring/comparing the M's relationship with each other.  Even if they look good by the time I would issue stop slave to S1/S2 something could have come into M1 or M2 and made it into just their slave.

Would you agree with this or am I missing something?


texts:
I came to work today and I saw the transaction log with 441GB, using almost all disk space.
I use SQL Server 2012 and have Full backup, but somehow I lost control of the t-log.
So, I need to do 2 things: first, clear the log, so I have the disk space back and control it.
I thought to control it doing daily backups. But I just can't figure how to reduce it.
Shriking is not a good thing to do, and anyway will take AGES to do it. I've try to bakcup the log like this: Backup Log DB_1 TO DISK = 'Nul:' but nothing changed.
So what exatcly can I do to fix this situation?

Thank you!


texts: scheduling medical appointment challenge

texts:
I'm working on a medical appointment scheduling system project.

What is the best way to get the first free time of a doctor and schedule it for the patient who just asked for an appointment?

database structure:

appointments table:

doctor_id
patient_id
appointment_start_time
appointment_end_time

doctors_working_hours table:

doctor_id
day_of_week
start_time
end_time

When a patient asks for an appointment with a doctor, I want to schedule the first next free time of the doctor according to the doctor's working hours.

the default duration for each appointment is 15 minutes.

What is the most efficient way to do this?


texts: SSAS - what could I monitor in SSAS to fine tune its memory settings?

texts:
This question is a follow up from the
SSAS - what could be a good starting point for memory settings?

What and how can I monitor the server resources in order to best identify the settings that I should apply to my SSAS instance?

In this case we're talking about sql server 2014, and the server is a windows server 2012, with the spect below


texts: Introduce partitions into an existing non-partitioned table

texts:
What has to be done to introduce range partitions into an existing non-partitioned table in oracle? Is there an easy to do and safe way to do this?

Especially in cases where there are many constraints on the table like foreign keys.


texts: The log in this backup set is too recent, but it's from before the last full backup!

texts:
I'm trying to restore a full database backup plus transaction logs to the most recent transaction log. I've set up transaction logs to ship out every 15 minutes. This setup was performed via the SMSS GUI. The full database backup starts at 12 AM daily, and usually finishes before 2 AM.

First, I restore the full backup with NORECOVERY.:

USE [master]
RESTORE DATABASE [DBNAME] FROM  DISK = N'X:\path\to\DBNAME.bak' WITH  FILE = 1,  NORECOVERY,  NOUNLOAD,  REPLACE,  STATS = 5
GO

This completes successfully.

Then, I grab the oldest transaction log and run the following:

RESTORE LOG DBNAME FROM DISK = 'X:\path\to\OldestTransactionLog.trn' WITH NORECOVERY;

However, I get the following error:

Msg 4305, Level 16, State 1, Line 10
The log in this backup set begins at LSN 421814812000000025600001, which is too recent to apply to the database. An earlier log backup that includes LSN 421787067000000013800001 can be restored.
Msg 3013, Level 16, State 1, Line 10
RESTORE LOG is terminating abnormally.

I don't understand why this is too recent!

My backup takes place at 12 AM and finishes by 2 AM.

At first I tried the transaction log from 12 AM, then seeing the "too recent" message, tried the oldest one I have, from 11:30 AM yesterday.

So this transaction log should be half a day older than the most recent full database backup.

Am I missing something here? How can this be too recent?

Any help is appreciated - and this is not on a production system, don't worry!


texts: Is PostgreSQL function parallel safety intended for functions which update data?

texts:
I am having some trouble understanding function parallel safety. I can understand what it does - which is to specify whether the function will be executed by the parallel master node or whether the query will totally disable parallelism.

But I am having a hard time understand WHEN to use it. Let me give an example. Say I have a function which only creates tables and does not update any data or insert any rows. What should the parallel safety of this be marked as? I would think to mark it as parallel safe, but then I cannot understand why the manual would list the following as parallel restricted:

Scans of common table expressions (CTEs).
Scans of temporary tables.

Since both points do not necessarily involve any data update/insert (race condition)?

The case for CTE is particularly strange. I am understanding that if I add a parallel safe function to a CTE call, it will not be executed as parallel safe any longer? And, outside of function parallel safety, I am understanding that all CTEs are executed as parallel restricted? Is there a difference between materialized and not materialized CTEs here?


texts: DB Engine Stolen Server Memory is too high

texts:
I get error from System Center Operations Manager (SCOM).

How to solve this error?

  SQL DB 2012 Engine Stolen Server Memory is too high.

I run query and get this counters:

╔═══════════════════════════════╦═════════════╗
║ Stolen Server Memory (MB)     ║ 7354.773437 ║
║ Lock Memory (MB)              ║ 106.195312  ║
║ Free Memory (MB)              ║ 64.632812   ║
║ Connection Memory (MB)        ║ 24.203125   ║
║ Log Pool Memory (MB)          ║ 14.085937   ║
║ Optimizer Memory (MB)         ║ 2.351562    ║
║ Granted Workspace Memory (MB) ║ 1.296875    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
║ Cursor memory usage           ║ 0.000000    ║
╚═══════════════════════════════╩═════════════╝


texts: Map auto increment ids on multi-row insert

texts:
I have the following table with an AUTO_INCREMENT column.

CREATE TABLE animals (
  id MEDIUMINT NOT NULL AUTO_INCREMENT,
  name CHAR(30) NOT NULL,
  PRIMARY KEY (id)
);

Usually I load multiple rows with one INSERT like this:

INSERT INTO animals (name)
VALUES  ('dog'),('cat'),('cow');

Sometimes though I want assign to each value its ID. Like this:

1 -> dog
2 -> cat
3 -> cow

If I insert simple values I can select them back. But when I insert JSON documents I'm worried about using syntax like SELECT WHERE json IN (...).

I thought to get MAX(id) before selection, then get last_insert_id after selection and using count of inserted rows assign id to each. But I haven't found in documentation anything about order that rows in insert are processed, so I'm worried I won't be able to know what the ID's are.

Inserting rows one by one and get last_insert_id for each is not good solution. Is there a way to insert multiple rows at once and return to the application the IDs for each row?


texts:
I have a table that has some missing values for certain transactions.  The value for a particular transaction is going to depend on the value of all preceding transactions.  Because the preceding nulls need to be populated before the next value can be calculated, I didn't see a good way of writing this as a single query and instead went with a cursor.

In the row_type field a row with a 17 is a transfer from, and a value of 18 is transfer to.  The carrier_id of these two corresponding rows will never match and the amounts will always be opposite sign of each other.

Indexes are good on the table, and overall the execution time isn't bad, it's just that we need to do this on 20 million rows in a batch operation.  I've also looked at using an array based variable, but I think that might be even uglier than the cursor.

The one other thing that I haven't shown here, and may end up being the best result, is to add a modulus parameter, use that to filter by charge_id, and then execute the same cursor in parallel as each individual execution uses up a tiny fraction of the server's resources.

This is a table example for one particular charge_id.

trx_type    row_type trx_id carr_ID post_date   Amount
charge      11      15979   550 2012-10-17  92.00
Payment     13      34224   550 2012-10-17  -30.69
Adjustment  15      34225   550 2012-10-17  -53.64
Transfer    17      34226   550 2012-10-17  -7.67(originally NULL)
Transfer    19      34226   210 2012-10-17   7.67(originally NULL)

Cursor:

DECLARE @transaction_id int
DECLARE @transaction_date datetime
DECLARE @charge_id int
DECLARE @dataset varchar(50)
DECLARE @carrier_id varchar(50)
DECLARE @amount float

DECLARE db_cursor CURSOR FOR
SELECT
    transaction_id,
    post_date,
    charge_id,
    dataset,
    ins1_ID
FROM .dbo.transaction_details
WHERE row_type = 17 AND amount is null
ORDER BY transaction_id

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @transaction_id, @transaction_date, @charge_id, @dataset, @carrier_id

WHILE @@FETCH_STATUS = 0
BEGIN

    SELECT
        @amount = coalesce(SUM(amount),0)
    FROM
        .dbo.Transaction_Details t1
    WHERE
        t1.dataset = @dataset AND
        t1.Charge_id = @charge_id AND
        (
        t1.post_date < @transaction_date OR
        (
            t1.post_date = @transaction_date AND
            t1.transaction_id < @transaction_id
        )
        ) AND
        t1.Ins1_ID = @carrier_id

    UPDATE
        .dbo.Transaction_Details
    SET
        amount = CASE
                    WHEN row_type IN (17,20) THEN -1 * @amount
                    WHEN row_type IN (19,18) THEN @amount
                END
    WHERE
        dataset = @dataset AND
        transaction_id = @transaction_id AND
        row_type between 17 and 20

FETCH NEXT FROM db_cursor INTO @transaction_id, @transaction_date, @charge_id, @dataset, @carrier_id
END

CLOSE db_cursor
DEALLOCATE db_cursor


texts: migrate mysql DB between two servers using replication

texts:
I need to perform some steps, I have to migrate our company live DB to a new server, if I use normal way such as lock table, mysqldump, scp, mysql restore, it take around 5-6 hours, so I want to make it quicker by those steps:
( note that i already have master/slave replication on backup server )
1- take full mysql backup from backup server and write down pos.
2- restore backup from previous step in the new DB server.
3- create master/slave replication between old & new Live DB from pos I took from step 1.
4- lock write on Live DB.
5- ensure that new Live DB are up to date (how to ?).
6- publish new Live DB server.
so steps from 1 to 3 take whatever it take but my Live DB still working, and i will take around 1/2 an hour between steps from 4 to 6, is my theory working well ?, any suggestion ?
also how to ensure that my new Live DB are up to date, i think like when there is no one write on master db (by lock write on DB) the current pos Counter on slave db stop counting..?


texts: Copy one database to another (existing) database on the same server

texts:
I have a "Live" database and a "Development" database and occasionally, I want to replace the entire contents of the development database with the live data. Ideally, without taking the live database offline. Based on similar questions here and on other forums, I've pieced together this TSQL script:

USE MASTER;

BACKUP DATABASE LiveDB TO DISK = '[tempfile]' WITH COPY_ONLY

GO

ALTER DATABASE DevDB SET SINGLE_USER WITH ROLLBACK IMMEDIATE

GO

RESTORE DATABASE DevDB
FROM DISK = '[tempfile]'
WITH
MOVE 'Live_db'   TO N'...\Dev.mdf',
MOVE N'Live_db_log' TO N'...\Dev_log.ldf',
REPLACE, RECOVERY, NOUNLOAD

GO

ALTER DATABASE DevDB SET MULTI_USER

The script runs, I get no error messages, but if I query any table in the development database afterwards, I see the old, unchanged development data.

I get a success message (in German), which translates to:

  RESTORE DATABASE has successfully processed 49809 pages in 16.393 seconds (23.737 MB/s)."

Immediately after the restore, I execute:

SELECT * FROM [LiveDB].dbo.SomeTable;
SELECT * FROM [DevDB].dbo.SomeTable

...and I see two completely different results. It looks as if the DevDB hasn't changed at all. I see the same rows I saw before the restore operation.

The live data is not changing; that particular table hasn't changed in the last 2 weeks at least.

What am I doing wrong? I thought WITH REPLACE would force RESTORE to overwrite the development database.


texts: SSIS Package Fails when run from sql agent

texts:
I have an SSIS package that is loaded onto the a SQL Server.  The package fails when executed as a sql agent job, but the package runs fine when the user goes into the ssisdb catalog.
The job is accessing two different databases, one SQL Server instance using integrated security and one oracle database.  The job step is being run as a proxy user that has access to the SQL Server database.  The oracle connection string has the server name and oracle service name, as well as the oracle username and password.
When the job runs via the agent it fails with the following error

"An OLE DB error has occured. Error code: 0x80004005. An OLE DB
record is available. Source: "OraOLDEDB". Hresult: 0x80004005
Description: "ORA-12154: TNS:could not resolve the connect identifier
specified"

This strikes me as odd because when specifying the servername and oracle service name, the tnsnames.ora file should not be needed.  This also seems like an unlikely root cause, because the package runs fine when the developer executes the package from the ssisdb catalog.  To further rule this out, I changed the connection string from server/service to just the database name found in the tnsnames.ora file.  I verified the name is resolved via tnsping, which works successfully.
I am beginning to think that the error is being caused because the proxy account can not access the oracle client, so I tried to make the proxy account a local admin on the sql server, where the oracle client is installed.  This didn't change anything.


texts: Oracle scheduler chain priority in data warehouse applications

texts:
The scenario is as follows:
We have a data warehouse type application that relies on the Oracle job scheduler and a Chain.  This chain is responsible for generating materialized views (caches) that are to be queried against based on data that exists in the primary tables.  For the sake of discussion the materialized views are :
select * from Cats,
select * from Dogs,
 select * from Humans

During this cache creation process there are arbitrary tasks that are constantly populating the Cats, Dogs, and Humans tables.  My question is:

Given the above scenario is it possible for the creation of the materialized views (a read statement) to block during the insertion of new records from an abritrary program (these are bound by a transaction).  I believe that there should be zero conflict, but I want to bring this question to more experienced DBAs.


texts: connecting to an oracle sql database from sql developer on the same computer. When the computer is a domain computer

texts:
Hi I am trying to do DBA course that requires that I use Oracle database.

I am using my corporate pc to do it.

I set up the loopback adapter to enable localhost communication and setup the hosts file in system 32. When I start lsnrcrl

However when I try to connect I get an error.

<msg time='2016-07-27T08:59:00.083+02:00' org_id='oracle' comp_id='tnslsnr'
 type='UNKNOWN' level='16' host_id='ABCDEF121221'
 host_addr='192.168.1.168'>

<txt>27-JUL-2016 08:59:00 * (CONNECT_DATA=(SID=xe)(CID=(PROGRAM=SQLDeveloper)(HOST=__jdbc__)(USER=ABC))) * (ADDRESS=(PROTOCOL=tcp)(HOST=127.0.0.1)(PORT=50619)) * establish * xe * 12505
 </txt>

Extra info

Listener.ora is set to localhost.
hostsfile in system32 is set to 192.168.1.1/domain name.ABCDEF1212221/ ABCDEF1212221

The lstnrctrl status is here.

C:\Windows\system32> lsnrctl status

LSNRCTL for 32-bit Windows: Version 11.2.0.2.0 - Production on 27-JUL-2016  13:19:16

Copyright (c) 1991, 2010, Oracle.  All rights reserved.

Connecting to (DESCRIPTION=(ADDRESS=(PROTOCOL=IPC)(KEY=EXTPROC1)))
STATUS of the LISTENER
------------------------
Alias                     LISTENER
Version                   TNSLSNR for 32-bit Windows: Version 11.2.0.2.0 -       Production
Start Date                26-JUL-2016 15:30:00
Uptime                    0 days 21 hr. 49 min. 16 sec
Trace Level               off
Security                  ON: Local OS Authentication
SNMP                      OFF
Default Service           XE
Listener Parameter File      C:\oraclexe\app\oracle\product\11.2.0\server\network\admin\listener.ora
Listener Log File         C:\oraclexe\app\oracle\diag\tnslsnr\OLDKHQ133304\listener\alert\log.xml
Listening Endpoints Summary...
(DESCRIPTION=(ADDRESS=(PROTOCOL=tcp)  (HOST=abcdefg1212221.corporate.corporate.net)(PORT=1521)))
Services Summary...
Service "CLRExtProc" has 1 instance(s).
Instance "CLRExtProc", status UNKNOWN, has 1 handler(s) for this service...
Service "PLSExtProc" has 1 instance(s).
Instance "PLSExtProc", status UNKNOWN, has 1 handler(s) for this service...
The command completed successfully


texts:
We have 2 Databases whose backup files are close to 900 GB each.Backup Drive is 2 TB.Now the drive is fully occupied.The Database Backup files are compressed ones.

Database size in sp_helpdb shows as 171250 MB and 182350 MB.

Each week we have full backups and each day we have diff backups and .trn logs every 15 minutes. Last full backup size is getting close to 960 GB.

My question is apart from increasing backup drive size.what else should think of to free up some space.The drive contains all wanted files.

Thanks


texts: Sharding PostgreSQL with postgres_fdw (Foreign Data Wrapper)

texts:
We want to shard a single PostgreSQL 10.2 database by tenant (client id) to multiple servers. One of the easiest approach is to use Foreign Data Wrapper (postgres_fdw extension).

It will looks like:

We have a single "master" and several data nodes with equal schema. Master node has log table replaced with a view. View consists of unions of foreign tables. There are rules for insert/update/delete on view to forward operations to specific server. Foreign tables are created with check constraint on tenant field so SELECT from the view only requests required servers (if tenant is specified in query).

Simplified example:

CREATE FOREIGN TABLE log_data_01 (
    tenant varchar(10) NOT NULL,

    CONSTRAINT tenant_check CHECK (tenant = '01')
)
SERVER logstore_01
OPTIONS (schema_name 'public', table_name 'log_data');

CREATE FOREIGN TABLE log_data_02 (
    tenant varchar(10) NOT NULL,

    CONSTRAINT tenant_check CHECK (tenant = '02')
)
SERVER logstore_02
OPTIONS (schema_name 'public', table_name 'log_data');

CREATE VIEW log_data AS
    SELECT * FROM log_data_01
        UNION ALL
    SELECT * FROM log_data_02;

CREATE RULE log_data_insert AS ON INSERT TO log_data DO INSTEAD NOTHING;
CREATE RULE log_data_update AS ON UPDATE TO log_data DO INSTEAD NOTHING;
CREATE RULE log_data_delete AS ON DELETE TO log_data DO INSTEAD NOTHING;

CREATE RULE log_data_insert_01 AS ON INSERT TO log_data
    WHERE (tenant = '01')
DO INSTEAD INSERT INTO log_data_01 VALUES (NEW.*);

CREATE RULE log_data_insert_02 AS ON INSERT TO log_data
    WHERE (tenant = '02')
DO INSTEAD INSERT INTO log_data_02 VALUES (NEW.*);

The question is:

Is it "production ready" approach? Are there known issues with postgres_fdw on high load (there will a lot of INSERTs to the table)?
Is postgres_fdw can handle network issues (temporary connection lost)? Are there automatic reconnect or something?
All queries will be handled by "master" node and then forwarded. So I will have a single point of failure. Is it a good idea to have multiple replicas of master and load balance queries between them?


texts: Kafka Partitions vs Mongo Sharding which one is better throughput

texts:
I am using Mongo Sharding to register page views on my website. We have hashed shard key to evenly distribute data in multiple shards. Then our aggregation queries run over time range at interval to aggregate this data and provide trends on site.
We came across Kafka for write distribution for heavy load and this kind of streaming.
I compared both systems and both provide distribution on partitions in a topic with leader follower approach.
Kafka does it using multiple partition on different brokers with partition replication and Mongo does it with multiple shards which have replica sets.
As aggregation query will always be on time range than it will go to multiple shards/ partitions always.
My question is how we can compare which will provide better through put and run time scalability in case of heavy load as i understand both use same mechanism adding new partitions in case of Kafka or adding new shards in case of Mongo.
Please provide suggestions.


texts: Unable to update MySQL table from linked server on SQL Server 2014

texts:
I'm using SQL Server 2014, with a linked server to a MySQL database on AWS. I need to update rows on that linked server, using data that exists on another linked server. And if that wasn't complicated enough, I need the server and the database names to by parameterized, so I can easily configure my SQL Server Agent job to run against test or prod... so everything is dynamic-SQL.

I tried this:

exec (@remoteSql);

where @remoteSql contains a would-be-valid openquery update statement. This blows up with this error:

  The OLE DB provider "MSDASQL" for linked server "AWS" could not UPDATE table "[MSDASQL]". The rowset was using optimistic concurrency and the value of a column has been changed after the containing row was last fetched or resynchronized.

So I tried this:

    declare @remoteSql nvarchar(max);
    declare c cursor for select RemoteSql from @updates;
    open c;
    fetch next from c into @remoteSql;
    while @@fetch_status = 0
    begin
        exec (@remoteSql) at AWS;
        fetch next from c into @remoteSql;
    end;
    deallocate c;

where @updates contains a handful of records and @remoteSql contains a perfectly valid and rather simple update statement (no joins, just 3 fields and a where clause) to run on MySQL. This blows up with this ever so helpful error message:

  Could not execute statement on remote server 'AWS'.

I get the same error if I take the statement, escape the double-quotes and go exec ('update ... where ...') at AWS;, which worked here.

I have the Return matched rows instead of affected rows checkbox checked on my ODBC data source, and quadruple-checked that my linked server has RPC enabled.

The same stored procedure has a rather complex dynamic-SQL update statement involving a join to a local table and a join to another remote table through openquery, and that one works perfectly - however that same approach fails for this table.

Why is it refusing to update that remote table (the login I'm using does have the necessary permissions), and what else can I try?


texts: Can Postgres raise warnings if joins are inconsistant with foreign keys?

texts:
Like most relational databases, PostgreSQL provides foreign keys as a means of providing referential integrity

CREATE TABLE job (
  id serial NOT NULL PRIMARY KEY
)

CREATE TABLE jobtuning (
  id serial NOT NULL PRIMARY KEY,
  job_id integer REFERENCES job (id)
)

CREATE TABLE jobstep (
  id serial NOT NULL PRIMARY KEY,
  jobtuning_id integer REFERENCES jobtuning (id)
)

In a case such as this when an intermediate join table is used it becomes very easy to write a query which appears to work, but is wrong:

JOIN jobstep ON jobtuning_id=jobtuning.job_id

It should be:

JOIN jobstep js ON js.jobtuning_id=jobtuning.id

One of the strengths of SQL is the ability to form arbitrary relationships, but is it possible to ask Postgres to issue a warning when a join is written that is inconsistent with the way foreign keys or other constraints are defined?


texts:
I have some trouble with a query definition. My database is about operations in a bank and I want to show the card numbers which were used in transactions at all ATMs.
This is how my tables DDL:
CREATE TABLE Cards(
    card_id INT PRIMARY KEY IDENTITY(1,1),
    number VARCHAR(25),
    CVV CHAR(3),
    bankAccount_id INT REFERENCES BankAccount(account_id)
);
CREATE TABLE Transactions(
    transaction_id INT PRIMARY KEY IDENTITY(1,1),
    ATM_id INT REFERENCES ATM(id),
    card_number INT REFERENCES Cards(card_id),
    sum_money INT,
    transaction_time DATETIME
);

And I tried below query:
SELECT *
FROM Cards C
WHERE C.card_id = ALL (SELECT *
                        FROM Transactions T
                        WHERE T.card_number = C.card_id
                        )

And it doesn't work since I used card_id on both sides. Can somebody help me, please?


texts: Selecting, ranking and applying a point score based on rank

texts:
I am writing an MVP system based on votes from users. The table will look like

vote     | game | year
--------- ------ ------
player 1 | 2    | 2015
player 1 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 2 | 2    | 2015
player 3 | 2    | 2015
player 1 | 2    | 2015
player 3 | 2    | 2015
player 4 | 2    | 2015

An entry here equates to a vote. So out of this we would group and get a count of

Player 1 = 3
Player 2 = 3
Player 3 = 2
Player 4 = 1

The MVP system is a "3-2-1" score system. So points are awarded to the players in reverse order. So it should only apply winning scores to the top 3 positions. i.e.

1st gets 3 points
2nd gets 2 points
3rd gets 1 point

This gets really complicated when there is a tie but top 3 should get MVP points.

In the case above the point spread would be

Player 1 = 2.5 points
Player 2 = 2.5 points
Player 3 = 1 point

This is worked out because the top 2 players are effectively 1st and 2nd, there is no second so the next person gets 1 point for placing 3rd.

Effectively (points = points_available / players_in_rank)

however that equation is wrong. It gets extra complicated if we have a tie for 3rd, where you could possibly end up with lots of people in 3rd spot and therefore they get 1 point divided by them.

It is really complicated when you have ties in 1st or second. I have worked around this poorly in PHP code but would like to be able to do this in SQL and do away with PHP.

Just wondering if anyone has any simple methods for this?

These scores points are then later added to an overall standings table

Regards
Dan


texts: How do I create a new mongo collection with custom indices from an existing mongo db?

texts: I have a mongo database collection, that I want to modify. I would like to add new indices (text and geo) to the existing data, and remove unnecessary fields. What would be the best way to do this? There are about 20 million records in my data set.


texts: How to update cust_id from Customer table to order table Cust_id field while inserting customer name using trigger

texts:
I have two tables 1. Customer_details and 2. Order_details.

1.Customer_details table -->fields--> Cust_Id(Primary Key) and Customer_Name,
2.Order_details table --> fields-- Ord_Id(Primary Key), Cust_id

(There is no field for Customer name in Order_details table). I am trying to import customer orders to order_details from a CSV file through an external application.  The CSV File contains the customer name not the Cust_ID. When inserting into order_details I need to look up the Cust_ID based on the name in the file and insert the Cust_ID into order_details instead. This needs to be implemented at the Database level not in the application code. I have been searching a lot for answers but cant figure it out. Could I possibly use a trigger to accomplish this?
Thanks, Acube.


texts: innobackupex is failing while exporting backup

texts:
I am using perl script which is working fine in one of my other box which used to take individual schema backups & finally take all schema together using percona innobackupex.

I have following information in the Log(posting One day Log details):

--slave-info is used with --no-lock but without --safe-slave-backup. The
binlog position cannot be consistent with the backup data.
Died at /apps/mysql/scripts/mysql_backup.pl line 214.

  [Fri Oct 26 04:20:01 2012] [14670]
  [Fri Oct 26 04:20:01 2012] [14670] Starting individual backups
  [Fri Oct 26 04:20:01 2012] [14670]
  [Fri Oct 26 04:20:01 2012] [14670] Backing up proddb
  [Fri Oct 26 04:20:01 2012] [14670] Creating /tmp/mysql_backup-14670.fifo
  [Fri Oct 26 04:20:01 2012] [14670] Starting /usr/local/bin/zipmt -s -t 4
  -c - < /tmp/mysql_backup-14670.fifo > /apps/dbbackup/proddb02.backup-
  2012-10-26-042001-proddb.dmp.bz2
  [Fri Oct 26 04:20:01 2012] [14670] Starting /usr/bin/innobackupex
  --slave-info --no-lock --stream=tar --databases proddb ./ >
  /tmp/mysql_backup-14670.fifo
  [Fri Oct 26 04:20:02 2012] [14670] FAIL: /usr/bin/innobackupex
  --slave-info --no-lock --stream=tar --databases proddb ./ >
  /tmp/mysql_backup-14670.fifo failed 256

Full details are in /apps/dbbackup/backup.log.

 Cause:
   /usr/bin/innobackupex --slave-info --no-lock --stream=tar --databases
  proddb ./ > /tmp/mysql_backup-14670.fifo failed 256

Named pipes are existing in the /tmp even after backup getting failed :

  ls -lrth /tmp
  =============

 prw-r--r--. 1 root root    0 Oct 25 04:20 mysql_backup-18215.fifo
 prw-r--r--. 1 root root    0 Oct 26 04:20 mysql_backup-14670.fifo
 prw-r--r--. 1 root root    0 Oct 27 04:20 mysql_backup-11278.fifo
 prw-r--r--. 1 root root    0 Oct 28 04:20 mysql_backup-7163.fifo
 prw-r--r--. 1 root root    0 Oct 29 04:20 mysql_backup-4191.fifo
 prw-r--r--. 1 root root    0 Oct 30 04:20 mysql_backup-595.fifo

Any idea, why the backup is failing?


texts: SQL Server backups strategy with limited storage available

texts:
I've a SQL 2012 Server setup on a VM, with 3 disks:

Disk 1: 150GB --> System
Disk 2: 750GB --> SQL mdf
Disk 3: 750GB --> SQL ldf

DBs are in full recovery mode.
Let's say we'll have 350GB of data in the DBs.
This is a SharePoint 2013 databases server, but I don't think this impacts the backup strategy to adopt here.
The VM itself (and all its disks) is replicated via Hyper-V replicas.

Is the following backup strategy acceptable?

I plan full data backups every week to Disk 3. As far as I understand, this flushes out all transaction logs. I then remove backups from previous week.
I plan logs backups every 1 hour to disk 2. I remove log backups older than 1 week.

With this strategy, I should optimize my disk space, and at worse, I lose 1 hour data? Am I correct?
Will the logs backups (every 1 hour) have a noticeable impact on my server performances when they occur?

Thanks,


texts: "Table doesn't exist in engine" after moving tables from one VPS to another

texts:
I have two VPS's running right now. One, which I will refer to as the "old" server, runs on Debian 7 and gives this output for mysql --version:

mysql  Ver 14.14 Distrib 5.5.44, for debian-linux-gnu (x86_64) using readline 6.2

The other, which I will refer to as the "new" server, is running on a brand new installation of Debian 8, and gives this output:

mysql Ver 15.1 Distrib 10.0.20-MariaDB, for debian-linux-gnu (x86_64) using readline 5.2

I am trying to move my databases & tables from the old server to the new server.  I thought that I could do this by simply copying the /var/lib/mysql/ directory from the old server and overwriting the same directory on the new server. However after doing this, I now receive this error in phpMyAdmin on the new server when I click on any of the tables or databases:

#1932 - Table 'phpmyadmin.pma__tracking' doesn't exist in engine

However I can clearly see that it exists:

Is there a solution to this? I'm not sure what I did wrong. Thanks!


texts:
I have an Oracle database dump which contains the dump of three schemas for e.g. alpha bravo charlie in one file, exported through EXPDP.

Now when I am trying to import these with the following command:

IMPDP alpha/alpha FULL=Y DUMPFILE=alpha06022014.dmp LOGFILE=alpha.log;

...after a couple of lines I get this error which is probably because I am supplying the credentials of only one schema in the command?

...
...
...

ORA-31684: Object type VIEW:"alpha"."table1" already exists
ORA-31684: Object type VIEW:"alpha"."object" already e
xists
ORA-39126: Worker unexpected fatal error in KUPW$WORKER.PUT_DDL [VIEW:"bravo"."
VW_NDC_CONFIG_REPLY"]
ORA-44001: invalid schema

ORA-06512: at "SYS.DBMS_SYS_ERROR", line 95
ORA-06512: at "SYS.KUPW$WORKER", line 6273

----- PL/SQL Call Stack -----
  object      line  object
  handle    number  name
25BA9BC0     14916  package body SYS.KUPW$WORKER
25BA9BC0      6300  package body SYS.KUPW$WORKER
25BA9BC0     12689  package body SYS.KUPW$WORKER
25BA9BC0     11968  package body SYS.KUPW$WORKER
25BA9BC0      3279  package body SYS.KUPW$WORKER
25BA9BC0      6889  package body SYS.KUPW$WORKER
25BA9BC0      1262  package body SYS.KUPW$WORKER
25A03A14         2  anonymous block

Job "alpha"."SYS_IMPORT_FULL_02" stopped due to fatal error at 11:26:41

D:\oracle\product\10.2.0\client_1\BIN>

If yes, then how can I import all three schemas?.


texts: Archiving of old data combined with replication to data warehouse

texts:
I have a setup that I'm sure is not unheard of (this is MySQL 5.5, but upgrading):

I have a fast growing OLTP database, that I am replicating to an OLAP database. I want to start archiving the fastest growing tables in the OLTP db, but I want the OLAP db to accumulate everything without the archiving part. Is there an easy way to achieve this, or so I have invent my own procedures?


texts: Moving a Postgres database from standalone local DB to Amazon RDS

texts:
I have a working Ruby on Rails 4 app running locally on my Mac. I'm running Postgres locally.

I want to move the database (and only the database, not the whole app) to Amazon RDS Postgres.

What is the easiest way to setup all of the tables on the new database and migrate the data over to it?


texts: Creating user accounts with different roles in postgresql

texts:
Let say I want to create some accounts for my app. The first type is common user, who is going to ask for a service; the second type of account is for a user of type service deliverer, wich is going to deliver a service to the common user and there is a third table called services, which references both the common user table and the service deliverer table.
Question:
Is it better to just make one account table, which holds both user and service deliverer data and a second table for managing the roles of both types of users? or
it is better to just separate both tables user and deliverer in order to be referenced easily from table service?
If your answer is the first one. How could I reference the account table twice, one for the role common user and the other for the role service deliver?


texts:
I have a problem with a SQL linked server connected to a DB2 database on an AS400 IBM iSeries.

I configured an ODBC connection from the ODBC Data Source utility. Once configured the ODBC, i created the LinkedServer and start using it for querying the DB2 database using  SELECT * FROM OPENQUERY([LINKED_SERVER], 'SELECT * FROM [TABLE] FOR READ ONLY').

Once the query is executed, the DB2 table is locked and when the query ends the lock is not released. I temporarily solved the issue setting the ODBC connection type as read only and not as default.

Also all the DB2 tables that are used by the linked server are locked at every query even if they are not involved in the query.

Can someone help me figuring out how to not lock all the tables and set the linked server to read/write and not in read only mode.
Like in the image, i executed a query only on SHPAV20F but locks are taken also for TTB03ACF and TTB0301P that are not involved in the query.

ODBC settings  for commit mode is *CHG, PREFETCH for query data is enabled and LAZYCLOSE is disabled.


texts: Mysql Performance tuning for myisam tables.

texts:
Folks

I have a 4-core MySQL server with 4 GB of RAM, holding 19 MyISAM tables (total: ~164GB of data) and 32 innodb tables ( 5mb ) and we use it to process CDR files( basically call detail records files)

I have a table(billing_datas) with about 200 million records.( myisam table). ( basically we store all the cdr files data with the ids of carriers etc) which keeps on growing at a rate of 90 million rows per month

We need to generate reports based on selected "date from" and "date to" criteria. I m confused with the approach

The current scenario that we have is we are firing the query on the main table i.e billing_datas and creating the temporary table with "date from" and "date to" parameters and performing the aggregate calculations on this temp table. But the real performance hit is creating the temporary table is taking about 18 seconds for 1 month records.

should we create 3 summary tables based on
summary_monthly,summary_weekly,summary_daily and run the reports
query on these summary tables??
will it improve the performance as described in point 1?


texts: Multicondition Subquery Max Date and Item Number

texts:
The following is a query to get latest Sales date for a customer.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
)

Now my next question is, for a given purchase date, a Customer can have multiple purchase order numbers.

How would I acquire the largest order number for a given Maximum Purchase date using subquery? Order numbers can be random, and we just want a random or largest order number line.

We want to utilize subquery. Otherwise, I will have to rewrite as partition order by.

select pt.CustomerId, pt.SalesDate, pt.*
from dbo.PurchaseTransactions pt
where pt.SalesDate = (
    select max(SalesDate)
    from dbo.PurchaseTransactions subpt
    where pt.CustomerId = subpt.CustomerId
) and pt.OrderNumber = ??


texts: Failed installation of SQL 2012 on Windows Server Core

texts:
I'm in the process of building the lab for 70-462 exam.  On page 49 of the text, it provides the command to install SQL 2012 on Windows Server Core:

Setup.exe /qs /Action=install /Features=SQLEngine,IS,Conn
/InstanceName=MSSQLServer /SQLSYSADMINACCOUNTS="Contoso\kim_akers"
/IAcceptSQLServerLicenseTerms

When I run this code, it appears things are running fine, the GUI window comes up and shows progress, then all the sudden it quits, the GUI goes away, and then I'm left with the following at the CLI:

The following error occurred:
Object reference not set to an instance of an object.

Error restult: -2147467261
Result facitlity code: 0
Result error code: 16387

Please review the summary.txt log for further details

I've done some searching and haven't come up with much.  Further, I don't know how to access this "summary.txt" file as it isn't anywhere to be found on the C:\ drive, so I can't see what file is stating.

Other details:

This is a lab built on top of Windows 8.1 (x64) using the Hyper-V feature.
16 GB of RAM, with 2GB installed for each
Huge hard drive
8-core CPU
The same ISO/DVD was used to install all the graphical versions of
SQL server, so I don't think it is corrupt download
All OSes are x64 Windows Server 2008 R2
SQL Server 2012 x64 SP1 (I've also tried with SQL 2012 minus SP1)

If anything else is needed, let me know.

Edit #1 5-10-15 1:15pm CST

When I execute the dir command in the folder suggested by Shanky, I get:

(DIR)    Datastore_ComponentUpdate
(DIR)    Datastore_GlobalRules
(DIR)    resources
(DIR)    Watson_1
Detail_ComponentUpdate.txt
Detail_GlobalRules.txt
Registry_SOFTWARE_Microsoft_MSSQLServer.reg_
Settings.xml
Setting_GlobalRules.xml
SQL-CORE_20150510_091104_Watson_ComponentUpdate.txt
Summary_SQL-CORE_20150510_091104_ComponentUpdate.txt
Summary_SQL-CORE-20150510_091104_GlobalRles.txt
SystemConfigurationCheck_Report.htm


texts:
Tried to find an answer for this, but couldn't. So bear with me and apologies if this has been answered elsewhere. It may be just me not being able to find it.

Here's the setup: SQL 2008 R2 EE. Table partitioned on column1, integer (YYYYMMDD). Clustered index and a couple of NC indexes, all aligned, using the same partition function.

Facts: table has close to 300 million records. Query would return around 2-3% of the data (around 6million records).

Here's the query:

SELECT column1, column2, column3 FROM table1 WHERE column1 = 20131120

I'm expecting this query to use the clustered index (seek or scan) as all data is there and there'll be no need for additional I/O. But instead of using the clustered index, Query Optimizer decided to use a NC index defined on one column: column5 (not included in any way in the query above). No included columns. Stats all updated yesterday, full sample.

My logical explanation is that it uses the partitioning column to force partition elimination and for this it does not need necessarily the clustered index, any index is good as it uses the same partition function.

Am I way off with my expectations or is it something I'm missing ?

Update

Here's the DDL. As you can see, the second index is indeed narrower, but only covers the column1 (as the parititioning column is included in each partitioned index even if not explicitly part of the key), but that may explain why it is being chosen. I had to anonymize it first, but kept the actual column positions in place in the code.

CREATE TABLE [dbo].[table1](
[c1] [bigint] NOT NULL,
[c2] [int] NOT NULL,
[c3] [smallint] NOT NULL,
[c4] [int] NOT NULL,
[c5] [int] NOT NULL,
[c6] [int] NOT NULL,
[c7] [int] NOT NULL,
[column1] [int] NOT NULL,
[c8] [int] NOT NULL,
[column3] [int] NOT NULL,
[c9] [int] NOT NULL,
[c10] [int] NOT NULL,
[c11] [int] NOT NULL,
[column2] [bigint] NOT NULL,
[column5] [int] NOT NULL,
[c13] [bigint] NOT NULL,
[c14] [datetime] NOT NULL,
[c15] [datetime] NOT NULL,
[c16] [datetime] NULL,
[c17] [datetime] NULL,
[c18] [tinyint] NOT NULL,
[c19] [tinyint] NOT NULL,
[c20] [tinyint] NOT NULL,
[c21] [tinyint] NOT NULL,
[c22] [tinyint] NOT NULL,
[c23] [int] NOT NULL,
[c24] [int] NOT NULL,
[c25] [int] NOT NULL,
[c26] [int] NOT NULL,
[c27] [int] NOT NULL,
[c28] [int] NOT NULL,
[c29] [bigint] NOT NULL,
[c30] [int] NULL,
CONSTRAINT [PK_table1] PRIMARY KEY CLUSTERED
(   [column1] ASC,
[column2] ASC,
[column3] ASC)
WITH ...
(   PAD_INDEX  = OFF,
STATISTICS_NORECOMPUTE  = OFF,
IGNORE_DUP_KEY = OFF,
ALLOW_ROW_LOCKS  = ON,
ALLOW_PAGE_LOCKS  = ON)
)
ON [partition_scheme]([column1])
GO

CREATE NONCLUSTERED INDEX [IX_table1_column5] ON [dbo].[table1]
(
[column5] ASC
)
WITH (  PAD_INDEX  = OFF,
    STATISTICS_NORECOMPUTE  = OFF,
    SORT_IN_TEMPDB = OFF,
    IGNORE_DUP_KEY = OFF,
    DROP_EXISTING = OFF,
    ONLINE = OFF,
    ALLOW_ROW_LOCKS  = ON,
    ALLOW_PAGE_LOCKS  = ON
)
ON [partition_scheme]([column1])
GO


texts: Performance improvement after table analyze

texts:
I used a function that contains 3 tables a,b, and c. Table a has an index ind1 on two columns.

I call a stored procedure that contains code and gets data using the above function. I analyzed my session it gives a direct path read event, and produces output in 1 minute.

When I analyze the above 3 tables, the procedure runs faster and generates data within 10 seconds. What is table analyze and what causes this performance difference?


texts: Best way to compare multiple non null/non zero fields in a row in T-SQL?

texts:
I need to compare figures across a row and show where there is any mismatch, excluding zero and null values. So in the example, ids 1, 4 and 5 would be returned. What is the most painless way of doing this without a ton of where and or statements?

CREATE TABLE #XX
(
   ID  INT IDENTITY(1,1) NOT NULL PRIMARY KEY,
   VAL1  INT,
   VAL2  INT,
   VAL3  INT,
   VAL4  INT,
   VAL5  INT
)

INSERT INTO #XX (VAL1,VAL2,VAL3,VAL4,VAL5) VALUES
(5,10,NULL,17,12),
(12,12,12,NULL,0),
(15,15,15,15,15),
(3,NULL,6,0,NULL),
(10,NULL,15,0,20)

Cheers.


texts: What makes an OLTP database an OLTP database?

texts:
This is a pretty broad question.  I know the definition of OLTP.  But it seems like unless you have an environment that has a clear distinction between OLTP and OLAP, in the sense of a database that handles DML transactions and a replicated database that handles the reporting, then this is a bit of an abstraction.

Would it be safe to say that most production databases are OLAP and OLTP?


texts: Agregate data from multiple rows in one row

texts:
I have a SQL server DB for credit card processing system with 5 Tables

MerchantContract

PaymentKey   NVARCHAR(50)
ProcessingID INT

DealTrace

Status       NVARCHAR(250)
Tag          NVARCHAR(MAX)

DealSummmary

RefNumber    NVARCHAR(100)
DealAuthCode NVARCHAR(100)

Processing

Name         NVARCHAR(50)

And Deal with whole bunch of fields of which the most important one are

OriginalOrderId  NVARCHAR(50)
MerchantContract INT
Amount           INT

Currently I'm querying these tables with such query:

SELECT  d.id,
        d.date,
        d.ip,
        d.panmask,
        d.merchantorderid,
        d.amount,
        d.cardholder,
        d.bankhumanname,
        d.cardtypeid,
        d.bankcountrycode,
        d.usercountrycode,
        mc.paymentkey as merchantname,
        dt.status,
        d.merchantcontract,
        dt.tag,
        d.originalorderid,
        ds.refnumber,
        ds.dealauthcode,
        mc.processingid,
        pc.Name as processing,
        d.customparams
FROM Deal as d
LEFT JOIN MerchantContract as mc ON mc.Id = d.MerchantContract
LEFT JOIN DealTrace as dt ON d.Id = dt.DealId
AND dt.id = (SELECT MAX(id) FROM DealTrace WITH (nolock) WHERE DealId = d.id)
LEFT JOIN DealSummary ds ON d.Id = ds.DealId
AND ds.id = (SELECT MAX(id) FROM DealSummary WITH (nolock) WHERE DealId = d.id)
LEFT JOIN Processing pc on mc.ProcessingId = pc.id WHERE (d.MerchantContract IN ('13'))
ORDER BY ID desc OFFSET 0 ROWS FETCH NEXT 1000 ROWS ONLY

However for each MerchantContract there might be several rows with OriginalOrderId but different values in other columns. For example

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge|101|...
3|13_Qrta|refund|94|...
4|15_Utq1|charge|56|...

I would like for each unique pair of MercahntContract OriginalOrderId aggregate data in one row, so above resuklt would look like

MerchantContract|OriginalOrderID|Status|Amount|...
3|13_Qrta|charge,refund|101,94|...
4|15_Utq1|charge|56|...

I was looking around but unfortunately couldn't find solution. I was trying to modify first FROM to

SELECT ...
(SELECT * FROM Deal d1 WHERE d1.Id=(SELECT MAX(Id) FROM Deal d2 "+
    "WHERE d2.OriginalOrderId=d1.OriginalOrderId)) d
LEFT JOIN ...

But it didn't give the required result. Would really appreciate if anybody could give any hints as to how go about my problem.


texts: Automatically keep synchronized copy of database

texts:
I want to create a working copy of my main database to hold it on a separate server to run queries and report generation on to take the load of the main server.

I'm looking into SQL Server Synchronization, but I'm not sure about it. If I use synchronization, does the slave database need to remain identical to the first? That is to say, I want to keep some new denormalized tables in the slave database for some frequently run, otherwise intensive queries and I'm worried these tables would disappear after every synchronization or would otherwise interfere with the process?

I only need the slave database to be updated once a day, none of the data I query off it needs to be up-to-the-hour.

Is SQL Synchronization the way to go or should I be looking at another alternative?


texts: postgres full text search modifies ts_query

texts:
I have following query:

select
  vec.vec,
  vec @@ to_tsquery($$'fabr':*A$$),   -- true
  to_tsquery($$'fabr':*A$$),          -- fabr
  vec @@ to_tsquery($$'fabry':*A$$),  -- false
  to_tsquery($$'fabry':*A$$),         -- fabri <-- "i" instead of "y"
  vec @@ to_tsquery($$'fabryc':*A$$), -- true
  to_tsquery($$'fabryc':*A$$)         -- fabryc
from (
  select setweight(to_tsvector('english', coalesce('fabryczna')), 'A') as vec
) vec

It more details this query converts "fabry" to "fabri":

select to_tsquery($$'fabry':*A$$) -- will return "fabri" instead of "fabry"

Is it happening because I am using "english" dict while querying with "polish" word?
Is it possible to avoid this situation without installing polish dict? Is there any option to just force pg to match EXACT characters? (I don't want to use like/ilike)


texts:
Will the indexing affect insert/update/delete operation in Mysql 5.1
Will it take down performance of insert update delete operations,
If yes, why it will happen so.

So are indexes always meant for static table only?


texts: Can standby database of a physical database be pluggable or vice versa?

texts:
We are currently planning to upgrade our databases from 11g to 12c. But I have some confusions regarding 12c. Here are my questions.

Can standby database of a non-container database be pluggable or vice versa?
Suppose there are 2 CDBs on 2 different server. And one has 3 primary PDBs, the other has 3 standby PDBs of the primary with dataguard configuration. If the switchover executed on one primary, will it execute switchover on other 2 PDBs?


texts: How to re-arrange rows to best fit within groups?

texts:
I categorized a table with the method introduced here to categorize my items to folders with a limit size of 100.
mixing_order  id     num_items  cat order_in_cat cumulative_sum folder
1             1      4          1   1            4              1
2             2      33         1   2            37             1
3             3      74         2   1            74             2
4             4      41         1   3            41             3
5             5      24         1   4            65             3
6             6      44         2   2            44             4
7             7      16         1   5            57             4
8             8      55         1   6            55             5
9             9      11         1   7            66             5
10            10     37         1   8            37             6

Now, I want to go further and re-arrange the rows to better fit within folders. Consider that the records are of two categories (indicated by column cat). We can make a better fit just by altering the order of mixing. Then, we can produce the following table as each folder has a cumulative_sum close to the folder size limit (i.e. 100 here). As you can see, the order in each category (order_in_cat) has not been changed, only the order that the rows from different categories are mixed.
mixing_order  id     num_items  cat  order_in_cat cumulative_sum folder
1             1      4          1    1            4              1
2             2      33         1    2            37             1
3             4      41         1    3            78             1
4             3      74         2    1            74             2
5             5      24         1    4            98             2
6             7      16         1    5            16             3
7             8      55         1    6            67             3
8             9      11         1    7            78             3
9             6      44         2    2            44             4
10            10     37         1    8            81             4

In other words, the mixing_order was random in order of INSERT, but now we want to re-arrange the mixing_order to best fit within folder.
Probably, we need a loop in which re-calculating SUM to find the best match out of possible choices, but I have no idea how to conduct such loop in SQL.


texts: Optional oneToOne relations and criteria field

texts:
Introduction:
Visitors can fill reports on my website.
Those report can either be filled by a legalPerson or a naturalPerson.

Constraint: I don't like empty fields and I would like to normalize as much as possible my database.
Hence, I have those relations:

Question: For these kind of optional relations, should I add the field notifier_type (we could call it a criteria field) in Report Object? I mean I can obtain a close result with:

if($this->getNaturalPerson) {
    $notifierType = 'naturalPerson';
} ...

The only drawback would be that I can't query on notifier_type if it's not in DB.

What is the best practice here?


texts: What's the difference between Conditionals and Predicates

texts:
I am having difficulties finding what the actual difference between "conditionals" and "predicates" are in the context of SQL and Relational Database Management Systems. Do they mean the same thing?
For example, is a WHERE clause a conditional statement or a predicate? Or both?


texts: SQL Server 2005 get execution plan of a overnight stored procedure

texts: There is a overnight job runs long time. I have used profiler to get the trace and identified a statement (in a stored procedure) that takes most of execution time. Now I need to see the execution plan of the statement so I can see what I can do. But I can't rerun the query since it uses a temp table created in the proc. And the logic of the proc is rather complex and very hard to reproduce (involving getting data from file system and data exchanging with Oracle database via linked server). I am not saying reproducing is not possible but would be rather time consuming. So is there a way I can see the execution plan of the proc or the statement in particular?


texts:
mysql> alter table bill_item add  index idx_comp(created_date,product_id);
Query OK, 0 rows affected, 1 warning (1 min 2.62 sec)
Records: 0  Duplicates: 0  Warnings: 1

Trying to drop the index:

mysql> alter table bill_item drop index created_date;
ERROR 1091 (42000): Can't DROP 'created_date'; check that column/key exists


texts:
Oracle SQL and Microsoft SQL Server:-

I have a single table in which I wish to set two 'USERFIELD' column values, for all rows having their other column values set to a given literal.

SET ESCAPE ON
UPDATE DOCUMENT
SET USERFIELD4 = 'Europe \& International', USERFIELD7 = 'E\&I Engineering'
WHERE DOCNO in
   (SELECT d.DOCNO FROM DOCUMENT d
    WHERE d.USERFIELD4 = 'Combat Air'
      and d.USERFIELD3 = 'Engineering'
      and d.WITHDRAWN=0)

DOCNO is the table unique key column.

So, what I want is that if for a given row, USERFIELD4 = 'Combat Air' and USERFIELD3 = 'Engineering' and WITHDRAWN=0, then for that same row, set USERFIELD4 to 'Europe & International' and set USERFIELD7 to 'E&I Engineering', and for this to apply to all rows in the table.

I am getting the following error:

  SQL Error: Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.


texts: SQL Server - Defining and populating a new temp table column fails only once

texts:
How can I add a new column to a temp table dynamically in a stored procedure and populate it with some values, without resorting to dynamic SQL? I am getting an error only on the first time I run the code. It seems to be side-effecting in tempdb, and being inconsistent with Deferred Name Resolution.

Consider this sample stored procedure that has been enhanced to populate a newly-added Column2 that is only used by some callers:

CREATE PROCEDURE TestAddTempTableColumn AS BEGIN
    IF NOT EXISTS
    (--don't require all callers to have their table declarations updated
        SELECT 1
        FROM tempdb.sys.columns
        WHERE
            object_id = OBJECT_ID('tempdb..#TestTempTable')
            AND name = 'Column2'
    ) ALTER TABLE #TestTempTable ADD Column2 VARCHAR(MAX) NULL

    INSERT INTO #TestTempTable(Column1, Column2)
    VALUES ('column1 value', 'column2 value')
END

Then running this code in a new query window throws an error only in the first batch:

PRINT 'Test1'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
)

SELECT [Case1_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case1_2]='', * FROM #TestTempTable

GO

PRINT 'Test2'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
    ,Column2 VARCHAR(MAX) NULL
)

GO

SELECT [Case2_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case2_2]='', * FROM #TestTempTable

PRINT 'Test3'

IF OBJECT_ID('tempdb..#TestTempTable') IS NOT NULL DROP TABLE #TestTempTable
CREATE TABLE #TestTempTable
(
     Column1 VARCHAR(MAX) NULL
)

SELECT [Case3_1]='', * FROM #TestTempTable
EXEC TestAddTempTableColumn
SELECT [Case3_2]='', * FROM #TestTempTable

Redefining the stored procedure or changing query window are the only ways I have found to make the error recur after ever declaring #TestTempTable with Column2, even if #TestTempTable gets dropped! But then why doesn't it choke on the ALTER TABLE ... ADD COLUMN statement when I call it with the column already existing?

When adding a new optional column to this output temp table, I'd much rather make one code change in the stored procedure populating it, rather than every single reference throughout the codebase (and there are indeed numerous). And of course, while testing I defined it with the new column before testing other code that doesn't, so it passed my testing and is now breaking on the live server.


texts: Getting an error in MySQL Workbench: Cannot Open Administrator

texts:
I'm using local installation of MySQL CE on Windows 7 64-bit.  Tried to use Administrator part for the first time.

Here's the error I'm getting:

I also see the following in the log:

<object db.mgmt.Connection> key=connection could not be resolved

This information is probably not enough to diagnose the problem.  Please let me know what I need to post so I can get some help.


texts: How to fill GROUP_CONCAT() with 0's when JOIN data missing

texts:
I'm using MySQL 8.0's date generation and am joining in data when/where available.  This technique allows me to ensure this query returns zero values.  This works perfectly when I'm pulling data for a single stat.  Now I'm trying to adjust it to pull multiple stats at the same time so I can generate reports pretty easily.

Here's the current output:

[2019-01-24] 0
[2019-01-25] 0
[2019-01-26] 0
[2019-01-27] 62
[2019-01-28] 64,22,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21

My objective would be to adjust this query so that any specific stat that doesn't have an entry gets filled with zeros so the ideal output would look like:

[2019-01-24] 0,0,0
[2019-01-25] 0,0,0
[2019-01-26] 0,0,0
[2019-01-27] 62,0,0
[2019-01-28] 64,0,7
[2019-01-29] 65,21,7
[2019-01-30] 66,21,0

        WITH RECURSIVE dates (date) AS
        (
          SELECT :startingDate
          UNION ALL
          SELECT  date + INTERVAL 1 DAY FROM dates WHERE date <= DATE_SUB(:endingDate, INTERVAL 1 DAY)
        )
        SELECT
            COALESCE(daily_stats.date, dates.date) AS label,
            GROUP_CONCAT(COALESCE(daily_stats.value, 0) ORDER BY FIELD(stat, 132, 120, 111)) AS value
        FROM dates
        LEFT JOIN daily_stats ON stat IN(132, 120, 111) AND daily_stats.date = dates.date
        GROUP BY label;

I'm unsure how to accomplish this without doing a UNION on several select queries.  Is there a more efficient approach?

My table:

CREATE TABLE `daily_stats` (
  `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `date` date NOT NULL,
  `stat` int(11) NOT NULL,
  `value` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  `updated_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `daily_stats_date_stat_unique` (`date`,`stat`),
) ENGINE=InnoDB AUTO_INCREMENT=4412 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;


texts: SQL Server Truncates Transaction Logs with Copy Only Backups

texts:
I have a database that is set to "Full" recovery mode. The database doesn't show that a backup of the transaction log has ever been made.  backup_finish_date shows null for my logs.

I take backups nightly of the database Full (Copy Only). The backup appears to truncate the transaction log. The transaction log is about 40GB.  Before the backup it is 75% full.  When the backup runs, it is only 1% full. Using DBCC SQLPERF(logspace).

The sys.databases shows: recovery_model_desc FULL, log_reuse_wait 0, log_reuse_wait_desc NOTHING.

I have a copy of that database on a different server, same setup, but the transaction logs are NOT truncating.  Just growing.

The sys.database on the copy shows FULL, 2, LOG_BACKUP.  Which is what I would expect.

I understand the copy of my database is the way things should work.
But would like to know why/how the logs are being truncated on my production environment.

No Mirroring, No Transaction Log Shipping.


texts: Log file on SQL Server 2014 grown out of control - LOP_SHRINK_NOOP

texts:
Server is SQL Server 2014 running in SQL Server 2008 compatability mode.
Recovery Model is Full. We have auto shrink turned on (seems this is related to LOP_SHRINK_NOOP)

We have about 4.5 million rows in the log database of which roughly 4 million rows look identical and continue to grow. These rows all have the same information:

Incrementing: Current LSN
Operation: LOP_SHRINK_NOOP
Context: LCX_NULL
Transaction ID: 0000:0000000
LogBlockGenerated: 0
TagBits: 0
Log Record Fixed Length: 24
Log Record Length: 12316
Previous LSN: 00000:00000000:00000
Flag Bits:0
Every other column past this is NULL

Does anyone know what causes this?


texts:
I have a solution in SSDT with a SSIS and a SSAS project, with missing values in both numeric and text columns.

Is it a good practice converting all the missing values of these columns to NULL during SSIS and then handle in SSAS?

Is there a way to ignore all NULL entries during every query in a SSAS OLAP Cube, besides defining calculated measures etc. for each possible query?


texts:
What should I check to solve this error that appears in the error log?
Do I need to add RAM to the server or adjust some configuration variable?

I copy part of the log:

  180102 10:45:53  InnoDB: Started; log sequence number 0 267632652
  mysql3: Out of memory (Needed 2299002880 bytes)

  180102 10:45:54 [Note] mysql3: ready for connections. Version:
  '5.0.27-community-log'  socket: ''  port: 3307  MySQL Community
  Edition (GPL)

  180102 10:46:14180102 10:46:14 [ERROR] Cannot find table
  test/cliconcepw from the internal data dictionary of InnoDB though the
  .frm file for the table exists. Maybe you have deleted and recreated
  InnoDB data files but have forgotten to delete the corresponding .frm
  files of InnoDB tables, or you have moved .frm files to another
  database?

  See http://dev.mysql.com/doc/refman/5.0/en/innodb-troubleshooting.html
  how you can resolve the problem.

  180215 20:15:05 [ERROR] Out of memory; check if mysqld or some other
  process uses all available memory; if not, you may have to use
  'ulimit' to allow mysqld to use more memory or you can add more swap
  space

Windows Server 2008 64 bits / 8 GB RAM

MySQL 5.0


texts:
MySQL 8.0 has a new feature to allow a transaction to timeout immediately by throwing an error if it tried to access records that are locked by another transaction instead of waiting for that transaction to commit, or waiting for the time-out.

Is there a way to achieve the same effect in MySQL 5.7?


texts: Using Master Key & Certificate on Always On Replica

texts:
Im running on a SQL 2017 Always On 14.0.3381.3 DB.
Im trying to use EncryptByKey and DecryptByKey feature to encrypt sensitive info in one column in one table.
I've created the MasterKey, Certificate and the Symmetric key on the Prime server on MyDB NOT Master db.
Up until now everything works as it should. I can Encrypt and Decrypt data.
When I failover, I see the MasterKey & Symmetric keys (select * from sys.symmetric_keys SELECT * FROM sys.certificates;) along with the certificates on the now New Primary on MyDB (even though I haven't backed up or restored them on the secondary server). But in order to execute my Decrypt feature, it asks me to "Open the Master Key"
Did I miss some step? Do I have to open the Master key (OPEN MASTER KEY DECRYPTION BY PASSWORD = 'xxx')  each time for each session after I fail over?


texts: Need to query records based on Date only (Sample Query given)

texts:
sSQL = "
SELECT ordID, ordName, ordDate, ordShipping, ordStateTax,
       ordHandling, ordTotal, ordDiscount, ordPrivateStatus
FROM orders
WHERE (ordStatus = 7)
"

I want to display orders that came in yesterday as a daily report script I am building

How would I go about it?

SQL Server 2005

ordDate has this format: 2015-01-28 14:58:49.000


texts: Is it possible to change between SELECT column based on an "IF" statement? and use INNER JOIN variables?

texts:
I have a table of 'mapped_products':

  product_id | price

A table of 'product_id_to_name':

  product_id | product_name

(please don't ask me why, this is an already built web-app i am working on).

A table of 'non_mapped_products':

  product_id | product_name

and a table of 'orders_history':

  prodeuct_id | order_date

My task was to get a list of 'product_names' that were previously ordered (by checking in the 'orders_hisoty' table).
This is the query for this situation:

SELECT `non_mapped_products`.`product_name`
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

Now I noticed that I have the same products in my 'non_mapped_pruducts' within my 'mapped_products' table - same 'product_id', different 'product_name':
I want to use the "product_name" if exist on my 'mapped_products' table instead of the one in tne 'non_mapped_products' table - if they do not exist I want to use the current 'product_name' from within the 'non_mapped_products'

I'm not so sure how to write this kind of query, it seems like an if/case statement under the select with an inner join (?)

SELECT (IF 'mapped_products'.'product_id' USE 'product_id_to_name'.'product_name' ELSE `non_mapped_products`.`product_name` )
FROM `non_mapped_products`
WHERE EXISTS ( SELECT 1  FROM `orders_history` WHERE `orders_history`.`product_id` = `non_mapped_products`.`product_id`)

***** It's important not to use the orders_history table (it's a huge table, and indexed badly).

Using MySQL database.

SELECT

    CASE
        WHEN mapped_products.product_id = non_mapped_products.product_id
        THEN product_id_to_name.product_name
    ELSE
        non_mapped_products.product_name
    END
        AS product_name

FROM non_mapped_products

INNER JOIN mapped_products
    ON mapped_products.product_id = non_mapped_products.product_id
INNER JOIN product_id_to_name
    ON product_id_to_name.product_id = mapped_products.product_id

WHERE EXISTS ( SELECT 1  FROM orders_history WHERE orders_history.product_id = non_mapped_products.product_id)

returns :
duplicate product names only from the mapped_products table


texts:
I am trying to use 0xDBE and connect it to my testing MS SQL Server database.

When I connect to it 0xDBE doesn't display schemas names but shows only users in visual tree.

There is a view in 0xDBE:

And the same database in Microsoft SQL Server Management Studio:

There are no errors in the console.


texts: Monitoring DB activity in a large server environment

texts:
Apologies if this is ambiguous. In an environment with a small number of servers it is relatively easy to install and maintain stored procedures, etc. I normally build up some stored procedures to notify by DB mail when there are events that I should be aware of,  maybe build some triggers to notify when there are new databases in the system.

How do I build, deploy and maintain these kind of objects in an environment with a large number of servers where it becomes impossible to do so individually.

Secondly, we use log forwarding on all individual servers except for the central management server, and only have DB mail active on the central management server. How can I return relevant results to the DBAs when I can't use DB Mail?


texts:
I want to know the object referenced as an associatedObjectId in a deadlock graph, so I ran the below query:

SELECT OBJECT_NAME(1234560112364076)

I get the following error:

  Arithmetic overflow error converting expression to data type int.


texts: UPDATE query periodically is slower by factor of 5000x

texts:
I run a site (Magento) which have a problem with a specific UPDATE query.

Under heavy load, this query runs ~200 times/minute, and takes in average 1 ms (stdev 0.2 ms). However, at intervals of 1-2 hours, the query starts to take a lot of time to complete, 5-35 seconds. Restarting the database server (mysql) does not stop the issue, nor does restarting php-fpm. The only thing that restores the normal state is rebooting the server.

The query looks rather innocent to me:

UPDATE {table} SET {54 columns are set} WHERE {primary key = value}

The table is ~5.000-20.000 rows big. I have tried to check memory usage of mysql, and there is plenty of available ram and htop gives Mysql memory usage as 7.5G and mysqltuner says InnoDB data size is 5.9 G with a buffer pool of 10G. Mysqltuner also says open file limit used is 0% (8 of 5k).

Do you have any ideas what I can investigate to find the issue?