texts:
My master server ran out of space today (at 20:22). My transactions logs ended up broken into multiple files:

-rw-rw----  1 mysql mysql 228M Jul 31 20:22 mysql-bin.000914
-rw-rw----  1 mysql mysql  18K Jul 31 20:23 mysql-bin.000915
-rw-rw----  1 mysql mysql 1.5K Jul 31 20:24 mysql-bin.000916
-rw-rw----  1 mysql mysql 8.9K Jul 31 20:25 mysql-bin.000917
-rw-rw----  1 mysql mysql 1.2K Jul 31 20:25 mysql-bin.000918
-rw-rw----  1 mysql mysql 4.0K Jul 31 20:25 mysql-bin.000919
-rw-rw----  1 mysql mysql  982 Jul 31 20:26 mysql-bin.000920
-rw-rw----  1 mysql mysql 1.5K Jul 31 20:26 mysql-bin.000921
-rw-rw----  1 mysql mysql 2.1K Jul 31 20:26 mysql-bin.000922
-rw-rw----  1 mysql mysql  915 Jul 31 20:27 mysql-bin.000923
-rw-rw----  1 mysql mysql  17K Jul 31 20:28 mysql-bin.000924
-rw-rw----  1 mysql mysql  31K Jul 31 20:28 mysql-bin.000925
-rw-rw----  1 mysql mysql  54M Aug  1 02:00 mysql-bin.000926

This broke replication on my 4 slaves. Looking over other threads it seems I will have take a dump from the master and rebuild all my slaves. Is this correct?

Here's the slaves output.

Last_IO_Errno: 1595
Last_IO_Error: Relay log write failure: could not queue event from master
Master_Log_File: mysql-bin.000927
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000244
Relay_Log_Pos: 167
Relay_Master_Log_File: mysql-bin.000927

Skipping over the transactions that processed when it was out of space are fine. I tried advancing the position and file to the current file but that also didn't work.

stop slave;
change master to master_log_file='mysql-bin.000926', master_log_pos=0;
START SLAVE;
show slave status\G

which gave me:

Master_Log_File: mysql-bin.000926
Read_Master_Log_Pos: 4
Relay_Log_File: mysql-relay-bin.000001
Relay_Log_Pos: 4
Relay_Master_Log_File: mysql-bin.000926
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'Binary log is not open'

I'd prefer not having to rebuild my slaves if possible. Thanks.


texts: Double range query on one column uses wrong seek predicate

texts:
This problem comes up in the real world, but is already present in the following trivial example. I am using SQL Server 2017.
Given a table with only one non-id column, which takes only one value:
create table #test (id bigint not null, status smallint not null)

/* Populate with 10000 times value 10 */
;WITH numbers(Number) AS
(SELECT 1 AS Number
 UNION ALL
 SELECT Number+1 FROM numbers where Number<10000
)

insert into #test (id,status)
select number,10 from numbers option(maxrecursion 10000)

/* Create fresh index */
create index index_status on #test ([status])
DBCC SHOW_STATISTICS ("tempdb..#test", 'index_status') WITH HISTOGRAM

Now we query a double range on this column (0 results, since the only value is outside both ranges):
select 1
from #test
where status < 10
and status <> 2

The chosen execution plan uses an Index Seek, with

Seek Predicate "status < 2 AND status > 2"
Predicate "status < 10"

and consequently needs to read all 10000 rows. This surprises me, since I can read off the index statistics to conclude that a Seek Predicate on "status < 10" would be more efficient, since this already filters out all rows.
Question: Why are Seek Predicate and Predicate chosen 'the wrong way'?
I am not interested in alternatives or workarounds, I am only interested in understanding this choice of execution plan. For example we can rewrite the query using a UNION ALL on both range queries seperately, forcing two efficient Seek Predicates:
select 1
from #test
where status < 2

union all

select 1
from #test
where status > 2 and status < 10


texts:
I have tables like this:

ID|ServiceName|ServiceCode|Age      |Gender|Number|TotalCost|Location
0 | service1  |  0000234  |under 12 | Male | 121  |1234     |loc1

I want to change the table like this:

ID|ServiceName|ServiceCode|Age      |Gender|Measure    |Value |Location
0 | service1  |  0000234  |under 12 | Male | Number    |121     |loc1
1 | service1  |  0000234  |under 12 | Male | TotalCost |1234    |loc1

How can I do that?


texts: What's the best database if i want save data temporally?

texts: I want to create a datalog system so I need to store some int data temporally. What would be the best database (Relational, NO-SQL or some specific) for that purpose? And what would be the best way to do that?


texts: How do I create a query which takes certain time to execute?

texts:
I have configured statement_timeout=1000 in Postgres DB
I am accessing a DB table using a JDBC driver from a java application. JDBC driver will throw an exception if the statement times out. I have a logic that will get executed if the exception is thrown.
Now I want to test this functionality, is there a way to make a query not return the result within 1000 millsecs?


texts:
I have table A :

genre   collection
horror   20
action   50
rom-com  30
sci-fi   90

and table B:

MovieId   genre     performance
1         sci-fi     Hit
2         rom-com    Hit
3         sci-fi     Flop
4         rom-com    Hit
5         rom-com    Hit
6         horror     Flop
7         horror     Flop
8         sci-fi     Hit
9         rom-com    Hit

I am trying to get a count of hits per genre and also sum of box office collection of movies which were a Hit .

So I trying to get something like:

genre     hit_movies     total_collection
horror     0              0
action     0              0
rom-com    4              120
sci-fi     2              180

I am trying this by using a subquery:

select genre, sum(total_collection) as total_collection
    ,(select count(*) from  A  where genre = A.genre and A.performance = 'Hit') as hit_movies
    from
    (select A.genre as genre ,
        (select sum(B.collection) from B  where B.genre = A.genre and A.performance = 'Hit')
        as total_collection from A
    )x

group by 1

But I am not getting desired results. I am using postgresql. Any help is really appreciated.Thanks in advance.


texts: MySQL Range partitioning vs Hash partitioning

texts:
We have one large table with 100 million records and its size is 100 GB and data is continuously growing day by day. This table has 30 partitions with RANGE by client_id(table's column). One client_id is having minimum 1 million records.

For solution we have break this large table into 30 small tables with group of client_id data in each table. Now we want to do partition in each table.

So which partition would work for us best: Range or Hash?

We don't know which table has which set of client_id.

Thanks


texts: Innodb errors - considering changing to aria

texts:
On my local machine, using XAMPP, my MySQL would intermittently not start. I have followed lots of solutions for this, and the innodb_force_recovery=1 usually fixes it, at least for a while.

It is very annoying though, and I have been unable to find a permanent solution. I am therefore considering changing my storage engine to Aria, which is, as you know, MariaDB's enhancement for MyISAM.

My question: are there any reasons, for "general use" databases, why I SHOULD NOT do that?

Edit: I am aware of the differences of row-level locking and table-level locking, as well as that there is no transactions support. Any other reasons?


texts: Get incremental counts of an aggregated value in a joined table

texts:
I have two tables in a MySQL 5.7.22 database: posts and reasons. Each post row has and belongs to many reason rows. Each reason has a weight associated with it, and each post therefore has a total aggregated weight associated with it.

For each increment of 10 points of weight (i.e. for 0, 10, 20, 30, etc), I want to get a count of posts that have a total weight less than or equal to that increment. I'd expect the results for that to look something like this:

 weight | post_count
--------+------------
      0 | 0
     10 | 5
     20 | 12
     30 | 18
    ... | ...
    280 | 20918
    290 | 21102
    ... | ...
   1250 | 118005
   1260 | 118039
   1270 | 118040

The total weights are approximately normally distributed, with a few very low values and a few very high values (maximum is currently 1277), but the majority in the middle. There are just under 120,000 rows in posts, and around 120 in reasons. Each post has on average 5 or 6 reasons.

The relevant parts of the tables look like this:

CREATE TABLE `posts` (
  id BIGINT PRIMARY KEY
);

CREATE TABLE `reasons` (
  id BIGINT PRIMARY KEY,
  weight INT(11) NOT NULL
);

CREATE TABLE `posts_reasons` (
  post_id BIGINT NOT NULL,
  reason_id BIGINT NOT NULL,
  CONSTRAINT fk_posts_reasons_posts (post_id) REFERENCES posts(id),
  CONSTRAINT fk_posts_reasons_reasons (reason_id) REFERENCES reasons(id)
);

So far, I've tried dropping the post ID and total weight into a view, then joining that view to itself to get an aggregated count:

CREATE VIEW `post_weights` AS (
    SELECT
        posts.id,
        SUM(reasons.weight) AS reason_weight
    FROM posts
    INNER JOIN posts_reasons ON posts.id = posts_reasons.post_id
    INNER JOIN reasons ON posts_reasons.reason_id = reasons.id
    GROUP BY posts.id
);

SELECT
    FLOOR(p1.reason_weight / 10) AS weight,
    COUNT(DISTINCT p2.id) AS cumulative
FROM post_weights AS p1
INNER JOIN post_weights AS p2 ON FLOOR(p2.reason_weight / 10) <= FLOOR(p1.reason_weight / 10)
GROUP BY FLOOR(p1.reason_weight / 10)
ORDER BY FLOOR(p1.reason_weight / 10) ASC;

That is, however, unusably slow - I let it run for 15 minutes without terminating, which I can't do in production.

Is there a more efficient way to do this?

In case you're interested in testing the entire dataset, it's downloadable here.  The file is around 60MB, it expands to around 250MB.  Alternately, there are 12,000 rows in a GitHub gist here.


texts: function based index as a conditional unique key?

texts:
I have a search table which has a generated primary key PK_ID,and also has primary keys from three or four other tables in the FK_ID column

CREATE TABLE METADATA
(
  PK_ID        NUMBER(10)                       NOT NULL,
  OBJ_OWNER    VARCHAR2(35 BYTE)                NOT NULL,
  OBJ_PACKAGE  VARCHAR2(35 BYTE),
  OBJ_NAME     VARCHAR2(35 BYTE)                NOT NULL,
  OBJ_TYPE     NUMBER(10)                       NOT NULL,
  FK_ID        NUMBER(10)                       NOT NULL
);

For example, when OBJ_TYPE = 1 then every FK_ID should be unique for that value of OBJ_TYPE.

I think I can do this with a function based index but the link to Tom Kyte's discussion on this is broken across a few web sites and I'm not sure how.
(Oracle 9.2.0.8)

A composite index will do the job.  A bonus would be if  a function based index could be used as a foreign key for the other tables.  Is this possible?


texts:
This is a theoretical question to help my understanding. What is the process to switch from full to simple recovery model in sql server?

Is the process as follows:

Take a trans log backup before the switch to simple so I can do
point in time recovery up until the switch.
Switch to simple.
Possibly run DBCC SHRINKFILE with TRUNCATE ONLY to free up some disk
space.
Possibly alter the database to decrease the trans log file size.
Preform a full backup.

I guess my main questions about point 1. If I don't take a trans log backup will SIMPLE discard any data in the final trans log or does switching to SIMPLE backup the trans log (i don't think it does).

Also I believe that it is best practice to perform a full backup post switch to SIMPLE so new transactions can be pushed from the trans logs to the data files at check point.


texts: how to get back the modified INOUT parameter in postgres

texts:
I have two functions with a similar signature, lets say:

CREATE FUNCTION func1(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;
CREATE FUNCTION func2(INOUT new "My_Table") AS $$ ... $$ LANGUAGE plpgsql;

I call both of these functions from a BEFORE UPDATE TRIGGER where the parameter is the row being updated. I want to give the row to the function as an input but subsequently modify it inside the function (therefore the INOUT).

I call the first function as:

SELECT * FROM func1(NEW) INTO NEW;

Which works just fine.

However, I can't make it work with the second function because the type of NEW is no longer of "My_Table" but it is "record" and I am unable to cast it.

SELECT * FROM func1(NEW) INTO NEW;

or

SELECT * FROM func1(NEW::"My_Table") INTO NEW;

or

SELECT * FROM func1(CAST(NEW AS "My_Table")) INTO NEW;

gives

ERROR:  42846: cannot cast type record to "My_Table"

How can I do this please?


texts:
I have table data like below:

CLIAM_KEY   MBR_ID  SERV_FROM
clm1        mem1    01-JAN-18
clm2        mem1    02-JAN-18
clm3        mem1    03-JAN-18
clm3        mem1    04-JAN-18
clm4        mem1    07-JAN-18
clm5        mem1    08-JAN-18
clm5        mem1    09-JAN-18
clm5        mem1    16-JAN-18

My requirement is to get the output as shown below:

mbr_id    count(consecutive_dates)
mem1      4
mem1      3
mem1      1

Count of the dates must be consecutive and must start counting afresh when there is no consecutive date.

From the sample data, we have dates from 01-JAN-18 to 04-JAN-18 continuously. There is a gap after 4-JAN-2018 so it must stop counting after 4-JAN-2018 and start counting afresh from 07-JAN-2018. Since 7th, 8th, and 9th are consecutive, count must come as 3 and then 16th is alone date without consecutive dates so count of 16-JAN-2018 must be 1.


texts: MongoDB: Manual failover and failback between two datacenters?

texts:
This is about MongoDB and datacenter failover and failback. We're thinking about using two datacenters and this MongoDB replica set:

DC1-M1  prio 1, votes 1  (datacenter 1, member 1)
DC1-M2  prio 1, votes 1
DC2-M3  prio 0, votes 1
DC2-M4  prio 0, votes 0   <-- please not, 0 votes. DC1 has a majority of voters

We think that we'd like to do datacenter failover manually (because other parts
of the failover process happens manually). We'd do failover by running
rs.reconfigure() in DC2 and including only members DC2-M3 and DC2-M4, and
setting their priority and votes to 1. Then we'll get a replica set in DC2
with two members only (M3 and M4) and one of them will become the primary.

Does this make sense, or can you think of any problems? I think we'd need to
specify force = true when running rs.reconfigure(), since we'd be connected to a secondary.  We'd basically follow these instructions:
http://docs.mongodb.org/manual/tutorial/reconfigure-replica-set-with-unavailable-members/#reconfigure-by-forcing-the-reconfiguration
We'd do datacenter failback to DC1 by running rs.reconfigure() again, and
adding back the members in DC1.

Do you see any problems with this?

What happes if DC1 has accepted a few writes after we did manual failover to
DC2? Then we have a split brain problem, and then what happens when we run
rs.reconfigure() and add back the members in DC1? MongoDB's automatic
rollback system will kick in?  (See
http://docs.mongodb.org/manual/core/replica-set-rollbacks/ ) But will it keep
the changes made to DC1 or those made to DC2?  It'd be important that the
changes in DC2 were kept; how can we ensure that those changes aren't lost?

Background:

We have 2 datacenters, and want DC1 to survive an outage of DC2. So we want to
keep the majority of the voters in DC1. At the same time, we'd like to be able
to failover to DC2, and fail back to DC1. We'd prefer not to involve any third
datacenter (e.g. a third datacenter with an arbiter), because our team
thinks it's bad to have dependencies on an additional datacenter. And we
currently think that we want to do failover manually anyway because other
parts of the datacenter failover process happen manually.

Best regards,
KajMagnus


texts: One to one relationship naming conventions

texts:
I'm designing an ER diagram with MySQL Workbench.

I have two tables with a one-to-one relationship. Both tables have one column as primary key, but they don't have the same name.

Take a look:

But when I have added the relationship, it has added automatically the column Code_CodeId.

Is there a naming convention about how can I name primary columns with a one-to-one relationship? Do must have the same name? Why it has added that column?


texts: Calculate values in two different ways - A or B?

texts:
My question is about code and database design. How should I design my code to calculate simple variables (stored in sqlite db) like the balance of a wallet?

Example:
We have tables for customers, wallets, transactions. We have methods to fill these tables. When receiving money from a customer, we add the amount to the balance of a wallet and submit a transaction (date, amount, ...) into the transactions table.

I have 2 approaches to design my code and database - which one is more appropriate and bugfree?

A)
When doing a transaction I add / subtract the amount to my the balance of a wallet and submit a transaction into a table transaction with information like wallet_id, amount, date...

B)
I just submit the transaction to the transactions table and recalculate the balance of a specific wallet by checking for columns with the matching wallet_id and add up the wallet_balance from there.

Which is the right way? For me A) looks more prone to bugs and safe code requires more effort. I would go with B) which would be much easier then I would add more features like deleting transactions (+ recalculating balances) and so on.

What is your advice?


texts: KILLED/ROLLBACK query doesn't stop running

texts:
I've tried to kill a query on ms sql server management studio.
Now it has the status  killed/rollback.

When I use: kill 74 with statusonly it doesn't make any progress. It is still at 0%. How can I delete this query without restarting the sql server?

I will really appreciate all your answers.


texts:
Assume I have a products table with the following columns:

id (PK)
category_id (FK)
color_id (FK)
material_id (FK)
seller_id (FK)
size_id (FK)
name
price

Further assume that there could be 0 or more promotions that apply to a product. A promotion will have a start and end date / time, a discount amount (e.g. $100 off) or percentage (e.g. 10% off), etc.

The promotion will apply only to products that match a certain criteria, such as:

all products that are "red" color and "large" size
all products that are under the "shirts" category
all products sold by seller_id = 1 and price > $50

How would I represent this in a database? I feel that I need to associate one or more "query string" to the promotions table (e.g. ?color_ids[]=1&sizes[]=3) and test each product against all query strings to see if that product is part of that promotion. I expect this to be very inefficient, though.

Another option is to have a table products_promotions that gets set whenever a product or promotion is inserted / updated in the system, then it's just a matter of checking that table to see if the product has any promotions associated with it.

Thoughts?


texts: How to join unknown table from Xref table?

texts:
I have a database that was provided by one of our vendors that I am trying to tie into another system, but I have never come across something designed this way.

I want to search for "sensors" named x, and get their readings as the result. There is one main "Xref" table, and then about 500 other tables that contain the sensor readings.

I can search for my desired sensors with this query, which returns the table name and columns containing the readings:

select * from [Xref] where name like '%Sensor_a%'
----------------------------------------------
Name        TableColumn TableName
----------------------------------------------
Sensor_a1   2429        SensorData12
Sensor_a2   2430        SensorData12

With the results from above, I am able to find the sensor readings from the appropriate table:

select top 1 [Time],[2429],[2430] FROM [SensorData12]
----------------------------------------------
Time                2429        2430
----------------------------------------------
2015-10-01 00:00:48 88.7        37.1

Is there a way to get the readings in one request to the sql-server? I can't figure out how to do it with the dynamic table names that may change depending on the name of the sensor I search for.


texts:
Currently I am designing a mysql table structure that will hold a couple of million rows.
I want to search in it on datetime field by date year and so on but the date will be entered by user so it may not be full date all the time. The field is indexed.
Will it be fast enough to use wildcard on the datetime field. Like so

SELECT `id`, `moment`, `object_uid`, `content` FROM `events` WHERE moment like '2015%';

Will it use the index?

There is an option to determine whether or not the user have entered year or month and year and so on but I prefer to let MySQL do the job.

SQLfiddle


texts: Make Edition Upgrade in a SQL cluster scenario

texts:
I have a question in regards to Enterprise Edition to Standard Edition Upgrade. I need to move a SQL Cluster based on couple of SQL Evaluation Enterprise Edition to a licensed Standard Edition. How do I use the new license key?

OS: Windows Server 2012 Std Edition
SQL Server: SQL Server 2012


texts:
We had developer restore a .diff with overwrite, the .bak was about 115 GB  for compressed for a  600+ GB database; the .diff was about a GB compressed.
Seems some data may be missing, does it only overwrite the changes?


texts: How badly do SQL Compilations impact the performance of SQL Server?

texts:
I am profiling an instance of a SQL Server 2005 and I, via PerfMon's SQLServer:SQL Statistics - SQL Compilations/sec metric, I see that the average is about 170 or so.

I whipped out SQL Profiler and looked for SP:Compile or SQL:Compile events.  Apparently they do not exist.  I did find Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events.  The amount of data I see in the Profiler suggests that these are the wrong events to look at, though I am not sure.

So my questions.  Answers to any of these would be great.

How can I see what exactly is compiling in SQL Server?
Did I pick the wrong metrics to look at?  In either Perfmon or SQL Profiler?
With regards to Stored Procedure/SP:Recompile and TSQL/SQL:StmtRecompile events in SQL Profiler...they do not include the Duration metric.  How can I gauge the impact of these events to the system if they provide no way to see the timing impact to the system.


texts: A (simple?) solution to the "logical decoding / sequence" problem in Postgres?

texts:
After reading this mind-blowing answer by Craig Ringer to my previous question Achieving "sequence number", rather than dateTime, for updates; postgres specifically I've become interested in the "logical decoding / sequence" racetrack problem in Postgres.
As I understand it (I may have stuffed-up to begin with), here is the racetrack problem in question:

The write for "A" gets a time of 100, and is written eventually at 300.
"B" will get a time of 120, and is written at 200.
A syncer reads at 250, as shown.

It gets the B, with a value of 120.
The A (100) is lost forever.  (The syncer thinks that it has everything up to 120.)
Assuming I even grasp the full depth of the problem. I see two simple-minded solutions:

Anything that writes has to have a lock.  So, actually B would not be allowed to start until A is finished. (It does not matter if a read happens in the middle of one.)

The reads have to wait on a write-lock. So, the read can't happen in the example until B is finished. (It does not matter if two writes are overlapping.)

Questions

Am I so dense I don't even know why my solutions are bad?
Is there perhaps some obvious, everyday setting for Postgres I don't know about (like: "Only allow one complete write at a time!" or something, which would implement (1) without bothering about locking.
Indeed do I even fully grasp the problem?

Regarding the logical-decoding system in Postgres.  (Doco) In my limited understanding, it works on a "channel based" approach.  So you could make, say, 1, 2, 3 or more "channels" of your logical decoding, which will each individually be at a different "points" in the stream. With logical decoding you can not (as I understand it) just have "arbitrary streaming" where you can say "give me everything from point X".


texts: How to log verbose output from mysqldump?

texts:
You typically save the verbose output of a Linux command like this:

# command > output.txt

But when you use a command like mysqldump, the > option outputs the dump of the database tables to the desired file:

# mysqldump --username=whatever --password=whatever -h localhost database > dump.sql

If you add in the -v (verbose) option to mysqldump, it outputs help information about what the command is doing. How do I save this verbose output to a file since the > option is used to output the database table information to a file?

More specifically, I'm dumping the output of one database into another database like this:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

I tried the following but the output file was empty in the end

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename | mysql --user=username --password=p@ssw0rd -h localhost localdatabase > output.log

Also tried the following, but it redirected the database information to the output.log instead of the target databse:

mysqldump -alv -h 123.123.123.123 --user=username --password=p@ssw0rd --add-drop-table databasename > output.log | mysql --user=username --password=p@ssw0rd -h localhost localdatabase

And I don't see any sort of --log-file=FILE option for mysqldump either.


texts: Deleting large amounts of cascading records with minimal locking

texts:
I'm looking for a strategy to purge large amounts of logically deleted data from a MySQL database. The deleted records are in a relatively small table, but are referred to with many ON DELETE CASACDE records on several referring tables, which makes the deletion process very slow and time consuming, during which time some of the smaller but more frequently accessed tables are, apparently, locked (these are InnoDB tables).

Since these purges happen on regular intervals on a high-load database, I'm trying to figure out the best strategy to purge this data with minimal impact on other processes, i.e. with minimal locking of parent tables.

To give an example, I have a table structure similar to this:

+------------+    +------------+    +------------+    +-------------+
| accounts   |    | users      |    | messages   |    | attachments |
+------------+    +------------+    +------------+    +-------------+
| id         |    | id         |    | id         |    | id          |
| name       |    | account_id |    | user_id    |    | message_id  |
| is_deleted |    | name       |    +------------+    +-------------+
+------------+    +------------+

users.account_id REFERENCES accounts.id ON DELETE CASCADE
messages.user_id REFERENCES users.id ON DELETE CASCADE
attachments.message_id REFERENCES messages.id ON DELETE CASCADE

Assuming there are dozens of users per account, thousands of messages per user and potentially dozens attachments and other related data (e.g. tags, metadata etc.) per message - deleting an account will result in tens or hundreds of thousands of records to delete spanning multiple tables.

What would be a good strategy to take when purging data in such a case?


texts:
I have a table which registers orders with a PlanStartTime and PlanEndTime. The customer has three shifts in a day starting from 7 AM - 3 PM, 3 PM - 11 PM, 11 PM - 7 AM (next day)

Say an order is planned from 17-Mar-20 8 AM to 17-Mar-20 9 AM and counts of items to be produced is 10, then my first shift count for the work order is 10

E.g

PO#     StartDate           EndDate             Count
--------------------------------------------------------
A1      17-Mar-20 8:00      17-Mar-20 9:00      10
--------------------------------------------------------

Expected Results
--------------------------------------------------
Date            Shift       Items to be produced
--------------------------------------------------
17-Mar-20       1           10

My Current Problem Statement is in case the work order is to execute across days how can i have the counts distributed across days per shift

Problem Statement

PO#      StartDate           EndDate         Count
--------------------------------------------------------
A1      17-Mar-20 8:00      19-Mar-20 9:00      490
--------------------------------------------------------

Expected Results
--------------------------------------------------
Date            Shift       Items to be produced
--------------------------------------------------
17-Mar-20       1           70
17-Mar-20       2           80
17-Mar-20       3           80
18-Mar-20       1           80
18-Mar-20       2           80
18-Mar-20       3           80
19-Mar-20       1           20

Note Shift 1 stands for 7 AM to 3 PM
Shift 2 stands for 3 PM to 11 PM
Shift 3 stands for 11PM to 7 AM (next day)

The count for the work order needs to be equally distributed across different shifts. The start date to end date duration may vary from 1 hr to max 7 days.


texts: Check for existing matches to find the field their grouped-by

texts:
So I have a table of values associated with different sessions using a field called sessionid, lets say the table has 3 other fields: itemcode, itemcost, & itemeffect.

What I end up doing, is getting a standard set of items (rows) from another table that contains the defaults for each country, and then store it in this sessiontable with a guid in the sessionid field. The user can then modify the items for their session only.

What I want to do instead is to search for an existing session match that has the same items and respective values, to avoid session repetition as the processing later in the application is expensive (reports etc).
  A match would return the sessionid, otherwise I will stash the new session and items for repeated use.

I'm struggling to search for a solution on stackExchange (im working off this q/a, I've realised EXIST and UNION won't work because I dont know the sessionid and my users session guid wont match (hence the question to begin with).

I'm thinking something to do with group by sessionid, and check with the worlds longest sql query (or LINQ madness) with a check for each item and item-field, and addtionally a check that no other (unmatched) items exist in matching sessions, and in theory there will only ever be one match or none!

Here's my pseudo-code:

useritems = getCountryDefaultItems();
var existingid =(
                select grpresult from
                   (select item from db.sessionints
                   group by item.sessionid
                   where item in useritems (ignore sessionid)
                   ) g
                where g.count == useritems.count
                )
          .first().sessionid;

I've had a go, and I believe it should be FULL OUTER JOIN so I hear, but sqlfiddle says no so I played with left + right + inner joins :/ sqlfiddle

SELECT COUNT(1),a.SessionID
    FROM sessionitems a
    RIGHT JOIN useritems b   -- should be FULL OUTER JOIN ?
        USING (`SessionID`,`InterventionCode`, `Included`, `IntLvl`, `Uptake Baseline Smokers`, `RelativeEffect`, `Cost`, `IntType`, `Baseline`, `Current`, `Alternative`, `UpPopulation`, `EffPopulation`, `Name`)
       WHERE  (a.`InterventionCode` IS NULL OR b.`InterventionCode` IS NULL) AND  (a.`Included` IS NULL OR b.`Included` IS NULL) AND  (a.`IntLvl` IS NULL OR b.`IntLvl` IS NULL) AND  (a.`Uptake Baseline Smokers` IS NULL OR b.`Uptake Baseline Smokers` IS NULL) AND  (a.`RelativeEffect` IS NULL OR b.`RelativeEffect` IS NULL) AND  (a.`Cost` IS NULL OR b.`Cost` IS NULL) AND  (a.`IntType` IS NULL OR b.`IntType` IS NULL) AND  (a.`Baseline` IS NULL OR b.`Baseline` IS NULL) AND  (a.`Current` IS NULL OR b.`Current` IS NULL) AND  (a.`Alternative` IS NULL OR b.`Alternative` IS NULL) AND  (a.`UpPopulation` IS NULL OR b.`UpPopulation` IS NULL) AND  (a.`EffPopulation` IS NULL OR b.`EffPopulation` IS NULL) AND  (a.`Name` IS NULL OR b.`Name` IS NULL)
GROUP BY a.SessionID;

, but sqlfiddle is having errors doing MSSQL schema today, so I've used mysql for now which doesnt support FULL OUTER JOIN on sqlfiddle:
  I can get a count of exactly matching rows when there's an exact match, but the id comes back null, and if I remove the COUNT(1) and return * it returns only one row (is this a restriction on results in nested select queries) which might be okay as the session is a match, but I'm worried I have overlooked something and don't appreciate where the query will fall down...


texts:
Below is my sample query, in which I first create new folder then update count then create a new tag and finally get new id.

insert into folder (name)values("new");
update folder set count = count + 1 where idfolder=1;
insert into tag(name,folder_Id)values("dsdss",@pFolderId );
SELECT last_insert_id() AS tag_id;

All queries are executed in same stored procedure in which @pFolderId is the id passed by user (as a parameter), foreign key constraint are applied on folderId of tag table.
When the user sends a valid folderId, it executes properly and I get a new tagid, but when the user sends an invalid folderid which does not exist in the folder table, it throws exception of foreign key.

In this case the first and second query is already executed and the 3rd query gives an error.

My question is how to avoid query from executing on any error of any query.

The first way is to use rollback, but when I used that it executes properly for 1 invalid folder request, but when on 2nd request it executed 1, 2 query and gave error on 3rd.

How can I achieve this and avoid execution on error?

Sample code (gathered from comments):

DELIMITER $$
CREATE DEFINER=root@localhost
  PROCEDURE newClip (pFolderId VARCHAR(100))
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION,SQLWARNING
  BEGIN
    SELECT SQLEXCEPTION.message;
    ROLLBACK;
  END;
  SET @pFolderId = pFolderId;
  START TRANSACTION;
    insert into folder (name)
      values ("new");
    update folder
      set count = count + 1
      where idfolder=1;
    insert into tags (name,folderId)
      values ("dsdss",@pFolderId );
    SELECT last_insert_id() AS tag_id;
  COMMIT;
END
$$


texts: "SHOW EVENTS" fails with "Error Code: 1545. Failed to open mysql.event"

texts:
I have a MariaDB 5.5.56 install (The default CentOS 7 package).

The VM was running on a host that had some problems reaching its storage. This resulted in the OS remounting some filesystems read-only.

The database is a replication slave and was rebuilt by importing a backup from the master. (After a reboot and fsck, which got the root fs and others back to read-write) (There did not appear to be major data corruption, but replication was failing due to duplicate entries, so restoring seemed safer)

mysqldump failed with mysqldump: Couldn't execute 'show events': Failed to open mysql.event (1545).

Events are not currently in use, but are included in the back, in case they are used in the future.

I have tried running REPAIR TABLE mysql.event, however SHOW EVENTS still fails with Error Code: 1545. Failed to open mysql.event.

SELECT * from mysql.event works as on the master and returns no rows.

On the master (unaffected by the storage issue and running an older, MySQL, version, SHOW EVENTS works as expected and returns no results)

After restarting MariaDB, SHOW TABLES now fails with Error Code: 1577. Cannot proceed because system tables used by Event Scheduler were found damaged at server start. SELECT * from mysql.event still works, returning 0 rows.

mysqlcheck --all-databases --check-upgrade -p returns "OK" for all tables, including mysql.event.


texts: Tracking the update or changes in mysql table

texts:
I need to stream the changes to elasticsearch to keep in sync with my database(mysql).
What will be best way to do this ? I have thought for below approaches

Using trigger( but my table will have billions of data and the
application will have frequent update so trigger may be costly) .

2  Using delta/history table (this will add the extra effort in
development and this will cause the lot many joins ) .

Is there any out of box solution or any other way which will have the better performance and less complexity.


texts: Querying an ordered list with OR or IN clause

texts:
I am querying items from a database and display it to the user. (in my example I'll use contacts in table tContacts.

The user selects a couple of items in an order he decides (the user can random pick the items, so no column can be specified to order).

In the application I have the id's of those items in an ordered list.

Currently I query more details for those objects one-by-one.

Is it possible to create a query with an "ordered list", which returns the items in the same order?

something like (yes, this is ugly - probably also hitting the max sql query size):

SELECT * FROM (
    --how to append this list...
    SELECT 0 as listIndex, * from tContacts where conIdContact = 13259;
    SELECT 1 as listIndex, * from tContacts where conIdContact = 12472;
    [...]
    SELECT 568 as listIndex, * from tContacts where conIdContact = 12422;
)
ORDER BY listIndex

OR: do i have to keep querying one-by-one
OR: does this need a temp table (insert id and priority), then query, then drop temp table again.
OR: query a batch and reorder the items in application code
(this might be slow due possibly large lists)

note: a database independent solution is preferred (mariadb / mysql, postgresql and h2 are considered to be used in production.

my temp table concept:

CREATE TEMPORARY TABLE temp_OrderedContacts(
    listIndex INT NOT NULL DEFAULT 0,
    contactId INT NOT NULL DEFAULT 0
);

INSERT INTO temp_OrderedContacts (listIndex, contactId)
VALUES (0,13259), (1,12472), (2,12422);

SELECT * FROM tContacts
JOIN temp_OrderedContacts ON (tContacts.conIdContact = temp_OrderedContacts.contactId)

DROP TABLE temp_OrderedContacts;


texts: SSIS package randomly fails with "WriteToServer requires an open and available Connection" error

texts:
Our SSIS package randomly fails with the following error message:

An exception has occurred during data insertion, the message returned
from the provider is: WriteToServer requires an open and available
Connection. The connection's current state is closed.

Every time it's a different step, sometimes the package runs w/o any issue for a few days in a row, but then suddenly fails. There were no changes introduced in the package itself nor to SQL Server configuration. The only thing which might change is basically the amount of data, it gradually increases. We are running Integration Services in SQL Server 2016 (it's installed on the same machine along with SQL Server). There is nothing suspicious on the SQL Server side, when the SSIS failure occurs the other apps are connecting to SQL Server with no issues, the SQL error log is empty.
Has anyone spotted anything similar? Any tips on how to troubleshoot this? I am not a big fan or expert in SSIS :) so I really count on your expertise!


texts:
I would like to emulate the Microsoft SQL Server function GetSqlBinary() in MySQL version 5.6.
  The C# namespace System.Data.SqlClient implements GetSqlBinary(), which is like fetching a blob.But the C# namespace MySql.Data.MySqlClient does not  implement GetSqlBinary().
  So is seems that the difference is that GetSqlBinary gives you a lump of data as a byte array, while GetSqlBytes is similar but stores the data in a buffer which allows you to interact with the underlying data as a stream.
  Here is the definition of GetSqlBinary():

GetSqlBinary returns an SqlBinary structure:
Represents a variable-length stream of binary data to be stored in or retrieved from a database.

GetSqlBytes returns an SqlBytes class:
Represents a mutable reference type that wraps either a Buffer or a Stream.

Please suggest a way to do the function GetSqlBinary() in MySQL. Thank you.
Frank


texts: Postgresql 9.6 Error trying to have a function return a string without first casting it as a string.

texts:
I am in the progress of learning PostgreSQL (9.6) and am a bit confused by an error I am receiving. If I attempt to create the following function I I receive the following error.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT 'Hello' , 'World' ;
$$
 LANGUAGE sql;

Error:

    ERROR:  return type mismatch in function declared to return record
DETAIL:  Final statement returns unknown instead of text at column 1.
CONTEXT:  SQL function "helloworld"
SQL state: 42P13

If I cast the text as text it works properly.

create or replace FUNCTION HelloWorld ()
RETURNS TABLE(HelloCol text, WorldCol text) AS $$

SELECT cast('Hello' as text) , cast('World' as Text);
$$
 LANGUAGE sql;

I've also tried char(10) and varchar(10) in the first scenario with the same error. I assume I'm just not getting the right variable type in the "TABLE(HelloCol text, WorldCol text)" portion but if 'Hello' isn't text, char, or varchar, what is it? Just some undefined type?

I can change the returned columns to int and have it Select 1,2 and that works just fine without casting.


texts: Join tables up together to get different counts of account type per postcode are

texts:
I am trying to deterine the amount of account types per postcode range. I have the following code that works but I would like help in refactoring it as only 1 line of code changing between each

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'New Connections'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 1
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Metered'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 2
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Metered Low'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 3
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commerical Metered High'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 4
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Domestic Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 5
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Generator'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 6
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

select substring(PostOfficeBox,1,4) as 'Postcode', count(New_AccountType) as 'Commercial Keypad'
from AccountExtensionBase as a
INNER JOIN CustomerAddressBase as b ON a.AccountId = b.ParentId
where substring(PostOfficeBox,1,2) like 'BT'
and New_accounttype = 7
group by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)
having count(New_MPRNNumber) > 0
order by substring(PostOfficeBox,1,2),substring(PostOfficeBox,1,4)

Any help appreciated.


texts: Merge Replication: Error "The insert failed. It conflicted with an identity range check constraint"

texts:
For one of our SQL server 2005 enterprise, configured with merge replication, we suddenly start seeing the below error message:

  The insert failed. It conflicted with an identity range check
  constraint in database 'ABC', replicated table 'dbo.tblMtxyze', column
  'csa'. If the identity column is automatically managed by replication,
  update the range as follows: for the Publisher, execute
  sp_adjustpublisheridentityrange; for the Subscriber, run the
  Distribution Agent or the Merge Agent.

I went through the complete process of rectifying as explained here, but no luck.

We have about 10 publication from this single database ABC containing the filtered tables, around 6 and all of them going through same error:

Any idea how can i proceed from here, apart from setting up merge replication from scratch?

Edit:

On exploring more we found that out of those 10 Publications, we have one Publication which act as master Publisher which needs to be working in order  for rest 9 to work.

However, when we right click and went to properties of that publication, the articles( 6 common tables among all 10 pubs) were not at all present out there on this master publisher.

So we went ahead and try adding those articles , but it threw us error saying

  An article with a different subscriber_upload_options value already
  exists for object '[dbo].tbl1

In order to resolve, we though this being master, went ahead and removed one article at a time from other 9 publications, thus allowing that table1 to show in article on master publication.

But again, the same thing goes if i try to add removed articles back to other 9 publications with same error.

What can be done here? We are out of thoughts for now as this merge replication seems very complicated!

Thanks!


texts: Postgresql nested row_to_json not working

texts:
This probably wouldn't be an issue if I were using postgresql 9.2, however I can't (yet) upgrade from 9.1, and I'm using the json data type extension for 9.1. I'm trying to achieve a json object like the following:

[
  { "type": "add"
    , "id":"285947086237736960"
    , "version" : 1
    , "lang":"en"
    ,"fields":  {
        "id" : "285947086237736960"
        , "created_at":"2013-01-01"
        , "content":"bla bla blah LOL ROFL"
    }
  }, ...
]

The nested "fields" attribute comes from a db table called "tweets". Both id's come from the same table. However, type, version, lang are always the same. I've looked at the suggestions from https://stackoverflow.com/questions/13227142/postgresql-9-2-row-to-json-with-nested-joins, which mentions using aliased subquery expressions. That didn't work at all, I still get the f1, f2, etc. The suggestions from Set names to attributes when creating JSON with row_to_json works for a single level, but I can't get it to work for the nested "fields" attribute.

As far as I can tell, this will only work with a with statement similar to:

with tweets(type, id) as (
    select 'add' as type, tweets.id from tweets
) select row_to_json(tweets) from tweets;

But, how do I get the nested "fields"? Any help is greatly appreciated!


texts: Meaning of 'show variables LIKE '%_database'

texts:
Despite being a newbie in db administration, I have been assigned to move our crm from mysql to mssql. I have reached a point where I have met a query I dont understand and it cannot execute in sql server.
show variables LIKE '%_database'

The following is the code it:

public static function checkDbUTF8Support($conn) {
    global $db_type;
    if($db_type == 'pgsql')
        return true;
    $dbvarRS = $conn->Execute("show variables like '%_database' ");
    $db_character_set = null;
    $db_collation_type = null;
    while(!$dbvarRS->EOF) {
        $arr = $dbvarRS->FetchRow();
        $arr = array_change_key_case($arr);
        switch($arr['variable_name']) {
            case 'character_set_database' : $db_character_set = $arr['value']; break;
            case 'collation_database'     : $db_collation_type = $arr['value']; break;
        }
        // If we have all the required information break the loop.
        if($db_character_set != null && $db_collation_type != null) break;
    }
    return (stristr($db_character_set, 'utf8') && stristr($db_collation_type, 'utf8'));
}
}

Can someone assist me with an explanation and a possible solution


texts:
We are moving a Magento site to a new server. When restoring the database, it terminates with error message:

  ERROR 1064 (42000) at line 11953: You have an error in your SQL
  syntax; check the manual that corresponds to your MySQL server version
  for the right syntax to use near ''

When I run the site URL to go on with the installation, it generates error:

  SQLSTATE[42S02]: Base table or view not found: 1146 Table
  'fever_magento.sales_flat_quote_address' doesn't exist

with admin.access_log generating allot of:

errors/default/css/styles.css


texts: Query optimalization - how create proper composite index

texts:
I have mysql 5.5 and table item have around 1-3 milions rows.
CREATE TABLE `item` (
  `Id_item` int(11) NOT NULL DEFAULT '0',
  `item_Kod` varchar(15) DEFAULT NULL,
  `Date1` datetime DEFAULT NULL,
  `Date2` datetime DEFAULT NULL,
  `Date3` datetime DEFAULT NULL,
  `Person1` int(11) DEFAULT NULL,
  `Person2` int(11) DEFAULT NULL,
  `Person3` int(11) DEFAULT NULL,
  `Deleted` tinyint(4) NOT NULL DEFAULT '0',
  PRIMARY KEY (`Id_item`),
  UNIQUE KEY `Index_item_Kod` (`item_Kod`),
  KEY `Index_Date1` (`Date1`),
  KEY `FK_item_Person2` (`Person2`),
  KEY `Person1` (`Person1`),
  KEY `FK_item_Person3` (`Person3`),
  CONSTRAINT `FK_item_Person2` FOREIGN KEY (`Person2`) REFERENCES `persons` (`Id_Person`) ON DELETE NO ACTION ON UPDATE NO ACTION,
  CONSTRAINT `FK_item_Person1` FOREIGN KEY (`Person1`) REFERENCES `persons` (`Id_Person`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE=InnoDB DEFAULT CHARSET=cp1250;

CREATE TABLE `persons` (
  `Id_Person` int(11) NOT NULL DEFAULT '0',
  `Code` int(11) DEFAULT '0',
  `Title` varchar(50) NOT NULL DEFAULT '',
  PRIMARY KEY (`Id_Person`)
) ENGINE=InnoDB DEFAULT CHARSET=cp1250;

and I want optmalize this query...result is about 100-500 rows and query run about 3-4seconds
SELECT
  *
FROM
  `item` AS `item`
WHERE
  (
    `item`.`Deleted` = 0
    AND (
      (
        `item`.`Date1` >= '2020-01-20 00:00:00'
        AND `item`.`Date1` <= '2021-01-29 23:59:59'
      )
      OR (
        `item`.`Date3` >= '2020-01-20 00:00:00'
        AND `item`.`Date3` <= '2021-01-29 23:59:59'
      )
      OR (
        `item`.`Date2` >= '2020-01-20 00:00:00'
        AND `item`.`Date2` <= '2021-01-29 23:59:59'
      )
    )
    AND (
      `item`.`Person1` IN (15, 167, 192, 198, 398, 662)
      OR `item`.`Person2` IN (15, 167, 192, 198, 398, 662)
      OR `item`.`Person3` IN (15, 167, 192, 198, 398, 662)
      OR (
        `item`.`Person1` IS NULL
        AND `item`.`Person3` IS NULL
      )
    )
  )
ORDER BY
  `item`.`Date1` DESC

Explain return:
id:1
select_type: SIMPLE
table: item
partitions: NULL
type: ALL
possible_keys: Index_Date1,FK_item_Person2,Person1,FK_item_Person3
key: null
key_len: NULL
ref: null
rows: 1198601
extra: Using where; Using filesort

I tried create composition index for Date1-3 or for Person1-3 but with the index query run around 30 seconds.
How I can optimalize query to run fast as possible? Ideal max.5 seconds.


texts: How can I list all the tablespaces without getting into each instance?

texts: I got around 98 instances in a single AIX server. I need to list all the tablespaces, their path and usage. Is there a way I can do that without getting into each instance, connecting into the database etc?


texts: How do I properly perform a MySQL bake-off?

texts:
I want to performance test (aka bake-off) MySQL server rpm against some other forks such as Percona server, MariaDB, and possibly some others. I'm hoping that by asking this question I can better understand the methodology behind setting up a proper performance test. I planned on using sysbench to run my actual test, but I'm open to anything.

What steps should I be taking to
ensure that the test results in a
apples-to-apples comparison and that
only the RDBMS is the variant?
Where do I get started?
How do I evaluate results?
What advice can you give me?


texts:
I want to get a sequence number according to the Indian fiscal year. I tried to write a function using SQL built-in function:

DATEPART(DAYOFYEAR,'2015-04-01')

but I am facing a challenge to get day number throughout the financial year.

For the financial year 2015: First Day is '2015-04-01' and Last Day is '2016-03-31'.

Note: For this period my financial year 2015 is a leap year, should have 366 days.


texts:
I have a table of villas ordered by the UID as follow :

UID, name, random_order:

4,  villaA, 12
8,  villaB, 5
9,  villaC, 9
11, villaD, 2

I want to show my villas according to the random_order column (the random number is created at the insertion of the villa) so I sort by "random_order" and I get the result:

11, villaD, 2
8,  villaB, 5
9,  villaC, 9
4,  villaA, 12

Until now it’s easy. The more difficult thing is that I want to make a turnover on the villas. Every hour the first villa becomes the last one.

After 1 hour:

8,  villaB, 5
9,  villaC, 9
4,  villaA, 12
11, villaD, 2

After 2 hours:

9,  villaC, 9
4,  villaA, 12
11, villaD, 2
8,  villaB, 5

and so on.

I don’t know how to do that. My example has 4 villas but of course I need to manage a lot more so I need something well optimized (I don’t want to update the column “random_order” every hour). Also note that a villa can be added at any time with a random value as “random_order”.

I was given the following solution (count is a variable incremented by 1 every hours and max_val is the maximum of the column “random_order”):
SELECT *, (random_order - )%MAX_VAL AS villa_order
FROM villa_table
ORDER BY villa_order ASC
But this is working only if my column “random_order” has unique values and different by 1 only and starting from 0… It’s far from my case.

Anyone has an idea?

I tried to make a select with limit from n to end union select limit from 0 to n but it doesn't accept a double limit in a single request. I could use a temporary table to put results in it but then wouldn't it be too slow?

Tks


texts: Levenshtein vs MATCH vs Others for best MySQL string match

texts:
I've a database with around 1.9 Million rows. My DB details

Server: Localhost via UNIX socket  Server type: Percona Server Server
version: 5.5.42-37.1 - Percona Server (GPL), Release 37.1, Revision
727  Protocol version: 10 User: ****@localhost  Server charset: UTF-8
Unicode (utf8)

Currenty Using:
I'm using the following algorithm on my FULLINDEX column First, I match my required string as per the below query
SELECT title FROM my_db WHERE MATCH (`Title`) AGAINST ('my string' IN BOOLEAN MODE)

And then I use a levenstein() distance function on the results row server side through PHP to get the closest match to the string.
My Questions:

Would it be faster to implement a levenshtein entirely instead of a MATCH, AGAINST on a fulltext for such a huge database?
Is there any implementable algorithm which is better than levenshtein or for that matter the best such algorithm existing right now?
Is there any other work around instead of MATCH or levenshtein() ?
How will each the below search modifiers

IN NATURAL LANGUAGE MODE,
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION,
IN BOOLEAN MODE,
WITH QUERY EXPANSION,

enhance or optimize my searching and results? Hence, which one would be the best?
Thanks!


texts: Unable to install postgresql 9.3 on windows 8.1

texts:
I have been trying, without success, to get posgresql 9.3 up and running on Windows 8.1 on and off over the last ten days. Here is what I have done so far:
I got the windows installer from enterprisedb and ran it.
I got this message:

“there has been an error. Unable to write inside TEMP environment variable path.”

I bounced around the postgres docs and forums and eventually figured out that I needed to reset the file association for vbs, and when I did that, the install ran, but with two errors:

“warning. A non-fatal error occurred whilst loading database modules. Please check the error logs at …/appData\local\temp for details.”

This is because the admin module didn’t install

 “warning. Problem running post-install step. Installation may not complete correctly. Error reading file c:/program files/postgresql/9.3/data/postgresql.conf”

This is because there wasn’t a postgresql.conf file. I have to make it from the conf-sample provided.
But when I went to create this file, I found out that’s not all I was missing. I also had to tell postgres where PGDATA is.
So how could I possibly avoid this error using the windows installer? It turns out, a default copy was supposed to be created for me.

http://www.postgresql.org/docs/9.3/static/config-setting.html

Presumably, if that had worked, postgres would also know where PGDATA is. As it stands now, it thinks it is at C:/WINDOWS/system32/
It probably goes without saying to those of you with more experience, but repeated efforts to get postgres running as a service also failed, with the particularly unhelpful error message

“The postgres sql service started and then stopped. Some services stop automatically if they are not in use by other services or programs.”

Turns out from the event logs that I am getting the same error there, too.
According to the postgres wiki,

“PostgreSQL for Windows installs the PGDATA directory by default into "C:\Program Files\PostgreSQL\some version\data". “

I have "C:\Program Files\PostgreSQL\9.3\bin\pg_ctl.exe" in the registry.  Note this file does exist at that location.

I read on one of these places, I forget which one now,  that my particular AV program is supposed to work without problems with postgres. Nevertheless, after repeated failures, I excluded the postgres directory from my AV scans anyway, but that did not improve things.
I posted an earlier version of this question to the postgres mailing list but got no response.

My last two options were to try and name PGDATA from the command line using
–D varname=value, but of course that didn’t work:

psql: could not connect to server: Connection refused (0x0000274D/10061)
        Is the server running on host "localhost" (::1) and accepting
        TCP/IP connections on port 5432?

Finally, I checked the ports. I say ‘finally’ because I didn’t think that was the issue since I had already opened Windows Firewall for postgres.  It says postgres can access “any” port, which I would think means this should work, but if there is a way to edit it to specify port 5432, I didn’t see it.

With that background, here is my question:

Is this install hopelessly corrupted so that I should start over?

If so, what steps do you recommend to have it all go smoothly? Fixing the vbs issue did not prevent the failure of the admin module, PGDATA, or the conf. file, so what else should I do?


texts: In a Serial 3-Server Replication Scenario (A>B>C), can server B preserve binlogs that server A has purged?

texts:
This is in the service of a live migration, where server A has heavy performance and space demands, so I would like to keep down the amount of disk-space used by logs - let's say expire_logs_days = 10.

If I set server B, which is a slave of server A, to expire_logs_days = 30, will it then work to use a 20 day old snapshot (of B, for argument's sake) to make a new slave of B, called C, that will be able to catch up, or does the fact that server A has binlogs purged on the 10th day somehow break that scheme?

I am using row-based GTID replication on Percona 5.6, b.t.w.


texts: executing command prompt command in MySql trigger

texts:
I have one table called table1. In it there are two fields, let say field1 and field2. What I am trying to do is that, when there are records inserted into table1, the values of field1 and field2 get stored in one text file called abc.txt which is on c drive.

I want to do it by calling the command prompt command which is called inside table1's After insert trigger called trigger1.

I have done it in SQL Server but I need it using MySQL. Here is the SQL Server trigger:

create trigger [dbo].[trigger1] on [dbo].[table1]
for insert

As

Declare @field_1 varchar(50),@field_2 int,@cmd varchar(250);

select @field_1=i.field1 from inserted i;
select @field_2=i.field2 from inserted i;

PRINT @field_1
Print @field_2;

SET @cmd= 'echo ' + @field_1+','+cast(@field_2 as varchar)+ '>C:\abc.txt'
EXEC Master..xp_cmdshell @cmd

The MySQL trigger that I have written is:

delimiter #
create trigger trigger1 after insert on table1
for each row
begin
Declare field_1 varchar(50);
Declare field_2 int(50);
Declare cmd varchar(200);

SET field_1 = NEW.field1;
SET field_2  = NEW.field2;

System echo field_1 > C:\abc.txt
end#
delimiter ;

After executing this command it gives an error as

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'echo
field1 > C:\abc.txt
end' at line 11

I am new to MySQL. Maybe I am doing it wrong. Any help is appreciated.


texts: TYPE_SYM syntax error when running the mysql command to create or replace type

texts:
Here is my command:

CREATE or REPLACE TYPE DRIVINGSCHED_TYPE AS OBJECT
(
vehicle VARCHAR2(20),
datesched DATE,
starttime DATE,
endtime DATE
);

  ERROR: Line 1: syntax error, unexpected TYPE_SYM, expecting VIEW_SYM.
  Statement skipped.


texts: How can I create a database where entries are compressed files searchable by index?

texts:
I am trying to design a database solution for logging where the search indices are dates (days), and the values are zipped files. I am doing this because the sheer number of logs I am processing is too great to simply store in a database. The zipped files would be all logs within the date (day) index. Once a user queries for a date, I would like to be able to query the logs associated with that date. This would require the program to uncompress the zipped file and provide parameters for the log once the user specifies the date.

Or, is there way (similar to zgrep) to search through compressed files and get corresponding date values?

I could find no information on this online. I am open to anything, relation or non relational databases.


texts: MySQL database table creation is very slow

texts:
I´ve run a test script that creates a database and 3 sample tables.

Machine: Windows 7 x64 and MariaDB 10.0.17

DROP DATABASE IF EXISTS dbTest;
CREATE DATABASE dbTest;
USE dbTest;

SET profiling = 1;

CREATE TABLE table1 (
    var1 BIGINT NOT NULL,
    var2 VARCHAR (100) COLLATE ucs2_bin,
    var3 BIGINT,
    var4 BIGINT,
    var5 BIGINT) ENGINE = InnoDB;

    CREATE TABLE table2 (
        var1 BIGINT NOT NULL,
        var2 BIGINT,
        var3 VARCHAR(1000) COLLATE ucs2_bin,
        var4 BIGINT) ENGINE = InnoDB;
   INSERT INTO table2 (var1, var2) VALUES (1, 0);

  CREATE TABLE table3 (
       var1 BIGINT NOT NULL,
       var2 VARCHAR (100) COLLATE ucs2_bin,
       var3 INT,
       var4 INT
  ) ENGINE = InnoDB;

  SET profiling = 1;

If I run this script in my MariaDB 10.0 installation (default settings), it takes 2,688 seconds.

Reviewing the profile of the CREATE TABLE query, most of the time is being cosnsumed "creating table" (used HeidiSQL).

If I try to create bigger databases, the query time increases a lot. It´s too much and it doesn´t make sense to me.

On the other hand, if I run the same queries in SQL Server, it takes only milliseconds.

I don´t think that it´s a hard drive issue.

I think that there is something wrong with the database configuration that makes the operation so slow.

PD: In a coworker´s machine (same MariaDB version), the operation takes 2,5 seconds as well.


texts: How To Execute an Integration Services (SSIS) Package from a SQL Server Stored Procedure

texts:
i am executing query like this on sql server 2008 R2

DECLARE @returncode int
EXEC @returncode = xp_cmdshell 'dtexec /f "C:\certs\SSIS Disposition\SSIS Disposition\SSIS Disposition\Package1.dtsx"'

and getting error

Microsoft (R) SQL Server Execute Package Utility
Version 10.50.2500.0 for 64-bit
Copyright (C) Microsoft Corporation 2010. All rights reserved.
NULL
Started:  12:08:21 AM
Error: 2014-03-21 00:08:21.71
   Code: 0xC0016016
   Source:
   Description: Failed to decrypt protected XML node "DTS:Password" with error 0x8009000B "Key not valid for use in specified state.". You may not be authorized to access this information. This error occurs when there is a cryptographic error. Verify that
 the correct key is available.
End Error
Progress: 2014-03-21 00:08:22.66
   Source: Data Flow Task 1
   Validating: 0% complete
End Progress
Progress: 2014-03-21 00:08:22.69
   Source: Data Flow Task 1
   Validating: 50% complete
End Progress
Error: 2014-03-21 00:08:23.05
   Code: 0xC0202009
   Source: Package1 Connection manager "DestinationConnectionOLEDB"
   Description: SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80040E4D.
An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 10.0"  Hresult: 0x80040E4D  Description: "Login failed for user 'wm_bulk'.".
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC020801C
   Source: Data Flow Task 1 Destination - ACQ_DailyFeedStaging [220]
   Description: SSIS Error Code DTS_E_CANNOTACQUIRECONNECTIONFROMCONNECTIONMANAGER.  The AcquireConnection method call to the connection manager "DestinationConnectionOLEDB" failed with error code 0xC0202009.  There may be error messages posted before thi
s with more information on why the AcquireConnection method call failed.
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC0047017
   Source: Data Flow Task 1 SSIS.Pipeline
   Description: component "Destination - ACQ_DailyFeedStaging" (220) failed validation and returned error code 0xC020801C.
End Error
Progress: 2014-03-21 00:08:23.07
   Source: Data Flow Task 1
   Validating: 100% complete
End Progress
Error: 2014-03-21 00:08:23.07
   Code: 0xC004700C
   Source: Data Flow Task 1 SSIS.Pipeline
   Description: One or more component failed validation.
End Error
Error: 2014-03-21 00:08:23.07
   Code: 0xC0024107
   Source: Data Flow Task 1
   Description: There were errors during task validation.
End Error
DTExec: The package execution returned DTSER_FAILURE (1).
Started:  12:08:21 AM
Finished: 12:08:23 AM
Elapsed:  1.544 seconds
NULL


texts: Why do user defined scalar functions require the schema?

texts:
When creating a user defined table function, or a user defined procedure, you can call the function or procedure as named.

However, a user defined scalar function requires that you call it with the schema:

select dbo.doit(data);

I have not come across that behaviour in other DBMS. Why is this the case for scalar functions but not for the rest?

This isn’t just morbid curiosity, and I know asking why a Microsoft product is the way it is is unproductive.

If there is a technical reason why a schema is required for scalar functions but not for others, it is worth knowing. This is not the behaviour of other DBMS, but there may be a technical reason why it is the case here.


texts: How to troubleshoot large transaction log file size?

texts:
I'm following up on this Why is my SQL Log File Huge? article.

Looking at the disk usage report, the transaction log space use is practically zero. It shows 99.1% unused.

It is schedule to backup the log every hour for 24hours/day. Every hourly trn file is quite small but there is one backup at round 2AM that is close to the actual database data file size.

If I schedule the hourly log backup from 3AM to the next 1AM, then the next big trn file is 4AM.

If I scheduled the log backup from 4AM to 12AM then the next large trn file is 5AM.

If I schedule the hourly log backup from 12AM to 11:59:59PM then the next large trn file is 2AM.

The pattern seems to be that whatever the first of the next day backup, that trn file will be large.

The recovery mode is set to full. The log is set to 5MB autogrowth and max out at 2TB. I've also tried the Monitoring SQL Server database transaction log space to track the log space used at every hour. At every hour the log spaced used was never bigger than 99MB and most are .8MB in size.

Help is much appreciated!


texts: Visual Studio 2010 deployment script creates objects in wrong order - trigger after stored procedure

texts:
I created a view that selects from multiple tables in a complex query.

Due to the complexity, I created insert/update/delete INSTEAD OF triggers to handle these operations on the view.
I also created stored procedures that update the view.

When I build the VS2010 database project, the SQL script contains the CREATE commands in this order: (1) the view, (2) the stored procedures, and then (3) the triggers.

Now, when I run the deployment script, I get an error:

*** SQL01268    D:\scripts\vs2010\Database.sql (28494,0).Net SqlClient Data Provider:
Msg 4405, Level 16, State 1, Procedure MyViewSave, Line 18 View or function 'MyView' is
not updatable because the modification affects multiple base tables

If the triggers would be created before the stored procedure, there would be no problem.

How can I make sure the triggers are created before the stored procedures?

Thanks!


texts:
We created a database template that gets executed during the installation of a web application (that we also created).

After the application ran in a live scenario for a while, it inherited CONSTRAINTS without our intervention, meaning if we export the database, there are now constraints below some of the table creation blocks.

We want to give this application to someone else, and it requires the database template so they can install it (which we no longer have), and I would like to know if it is safe to remove these constraints (that we did not physically create), and give them a clean template, with only table creations?

Here is an example of one of them, there are roughly 30 tables and now roughly 10 mysterious constraints.

--
-- Table structure for table `blocked_period_rooms`
--

CREATE TABLE IF NOT EXISTS `blocked_period_rooms` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `blocked_period_id` int(11) NOT NULL,
  `room_type_id` int(11) NOT NULL,
  `quantity` int(11) NOT NULL,
  PRIMARY KEY (`id`),
  KEY `blocked_period_id` (`blocked_period_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8 AUTO_INCREMENT=1;

--
-- Constraints for table `blocked_period_rooms`
--

ALTER TABLE `blocked_period_rooms`
  ADD CONSTRAINT `blocked_period_rooms_ibfk_1` FOREIGN KEY (`blocked_period_id`) REFERENCES `blocked_periods` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

Here is another example.

ALTER TABLE `guest_ip_addresses`
  ADD UNIQUE KEY `guest_id` (`guest_id`,`ipaddress`),
  ADD KEY `for_foreignkey_of_guest` (`guest_id`);

ALTER TABLE `guest_ip_addresses`
  ADD CONSTRAINT `foreignkey_guest` FOREIGN KEY (`guest_id`) REFERENCES `guests` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

I'm not a database expert and don't fully understand how/why we're ending up with code in the database like this, any help is VERY appreciated.


texts: MySQL: Select two values of two columns and compared their values exchanging them each other

texts:
I need to find a way to select one random row where the subscriber is not also leader (in the same or any other row).

Something like this: (just to have an idea)

SELECT `leader`,`subscriber` FROM `relations` WHERE `subscriber`.`leader` != `leader`.`subscriber` ORDER BY RAND() LIMIT 1

This is the table relations:

+---+--------+-----------+
| id| leader | subscriber|
+---+--------+-----------+
| 1 |       2|          5|
| 2 |       5|          2|
| 3 |       4|          8|
| 4 |       8|          4|
| 5 |       9|          6|
+---+--------+-----------+

So in this example is 9 and 6 (last row).

Is it possible to achieve this with just one query?

UPDATE

I noticed a long execute time for this query (about 40 secs)!

This is the output for my table relations:

| relations | CREATE TABLE `relations` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `leader` int(11) NOT NULL,
  `subscriber` int(11) NOT NULL,
  `time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=35207 DEFAULT CHARSET=latin1 |


texts: Does mariadb `NO_TABLE_OPTIONS`, `NO_FIELD_OPTIONS`, `NO_KEY_OPTIONS SQL_MODE` change database content?

texts:
From https://mariadb.com/kb/en/library/show-create-table/,
I read

  MariaDB and MySQL-specific table options, column options, and index options are not included in the output of this statement if the NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE flags are used.

I am not sure NO_TABLE_OPTIONS, NO_FIELD_OPTIONS and NO_KEY_OPTIONS SQL_MODE change only the grammar or change database content.
Is database resource from mysqldump --NO_TABLE_OPTIONS --NO_FIELD_OPTIONS --NO_KEY_OPTIONS SQL_MODE --databases db1 same as resource from mysqldump --databases db1?


texts: mysqlbinlog: unknown variable ssl_key or ssl_cert or ssl_ca

texts:
I am trying to recover binlog from a remote server using mysqlbinlog client program, however it appears that mysqlbinlog doesn't recognize the SSL options that's required to establish SSL connect to MySQL version 5.5 server, which is set to require X509.

Any other ways to get mysqlbinlog to connect?

Update: I've noticed this bug report from MySQL: https://bugs.mysql.com/bug.php?id=74864

And I've tried use --loose prefix for the 3 SSL options to connect. Still don't work.

Update: I am running Google Cloud SQL (a hosted version of MySQL 5.5, potentially tweaked by Google, running in Google Cloud), no other access except using MySQL client and via Google Cloud Dashboard to tweak only a few limited parameters.

This is in fact the same limitation for RDS in Amazon Cloud (AWS). The differences is that RDS have option to select MySQL minor versions whereas Google Cloud SQL doesn't allow you to choose a minor version, only 5.5 or 5.6 are available.


texts: How to find resource intensive SQL operations in Postgresql? High CPU usage spike

texts:
I am getting frequent high CPU usage alerts from production DB server. As I want to investigate more into this by my own, I have collected the running DB queries for that high CPU usage time period.

SELECT * FROM pg_stat_activity;

From the collected data, observed that there is one frequently accessed table with UPDATE operation. From further investigation I came to know that the table is the most frequently accessed one in the DB and there is nothing possibly resource intensive.

Following are the details I got from EXPLAIN for that query,

                                  QUERY PLAN
-------------------------------------------------------------------------------
 Update on foo_table  (cost=0.00..1271.14 rows=1 width=55)
   ->  Seq Scan on foo_table  (cost=0.00..1271.14 rows=1 width=55)
         Filter: (id = 2::bigint)
(3 rows)

So far the only certain data I have is the time period when this high CPU usage triggering operation is happening. Though unable to figure out which query is taking too much time to process.

I have tried following queries, I couldn't find convincing stats.

SELECT relname, idx_tup_fetch + seq_tup_read as TotalReads from pg_stat_all_tables WHERE idx_tup_fetch + seq_tup_read != 0 order by TotalReads;

SELECT * FROM pg_stat_all_tables ORDER BY seq_scan DESC;

I am helpless as there is no way for me to figure out what is happening, like is it normal because the work load or is it indexing related issue or query optimization would solve it,etc.

DB spec:
Size: 150+ GB
CPU: 8 core
RAM: 16 GB
Storage: SSD with no swap.


texts:
I have a table containing users. Each user has a primary email and a flag indicating if the user is deleted or not (we never hard-delete users).

However, each user can also have additional emails.

In any case, an email address must be unique and I'd like to enforce this on the database level. For the primary email address that's easy. I can simply add a UNIQUE index with a WHERE not is_deleted restriction so a deleted user's email can be re-used.

However, for the secondary emails that's more tricky.

If I stored them in the same table in an array field I'd probably not only lose the ability to index them (we have >50k users and need to be able to search by email, so that's a no-go) but as far as I know I also cannot put an UNIQUE index/constraint which goes into the arrays.
If I use a separate table (which is obviously cleaner), I would have to replicate the is_deleted flag from users to that table, which is also quite ugly, but would allow me to simply use the UNIQUE index with WHERE not is_deleted.

Is there any better solution to achieve what I'm trying to do?


texts: Error after removing sql server 2008 R2 Express edition and instaling Entreprise edition: Unable to connect to MyHost\MSSQLSERVER

texts:
So I'm having this issue after installing MS SQL Server 2008 R2 Enterprise edition, I'm getting this message error:

Unable to connect to WIN7 \ MSSQLSERVER

A network-related error or instance-specific occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces , error : 25 - Connection string is not valid ) (Microsoft SQL Server, Error: 87)

NB: Before installing this version i did remove an existing MSSQL 2008 R2 EXPRESS, but after launching "SQL Server Management Studio" i strangely got for the first time the following instance of the express edition version: WIN7 \ SQLEXPRESS

My configs are: Windows 7 Enterprise Edition SQL SERVER 2008 R2 Enterprise

the SQL connection session: - DB Engine - WIN7 \ MSSQLSERVER - Windows Authentication

Please HELP !!


texts: SQL server 2000 transactions with the same time

texts:
I have a BIG problem here.
I'm creating a program that can be looping if the connection to the server is broken.
But when the program is running for a few months, I found a bug on SQL server 2000.

I found some data that had more than one transaction with the same date and time.

Is it possible that sql2k server has a transaction with the same date and time in one table?

PS : I use getdate to fill that field.

Hope there's explanation and solution for my problem.


texts:
Where does SYSTIMESTAMP come from in multi-server Oracle RAC environment?

I'm hoping that the SYSTIMESTAMP derives from a common location (master node?) as opposed to the server that is currently servicing the request.

Additional Detail:

We are using a timestamp to ensure that entries in a log table are recorded sequentially. Say, for example, we have two nodes in our RAC and they are 1 second off. Our app server inserts two log entries within, say, 0.03 seconds of each other. The first insert is serviced by Node1 and the second by Node2. If there is one second difference between the two nodes, the timestamp could potentially show the log entries occurring in the wrong order! (I would just use a sequence, but our sequences are cached for performance reasons... )


texts: Intercept MySQL username/password for windows EXE

texts: I have a Win32 exe that connects to a remote MySQL database. Is there any program that can monitor the TCP-IP data (On port 3306?) between my PC and the remote DB, so that the username & password it is using to login can be extracted?


texts: Defining the host on a new installation of Oracle 12c Enterprise Manager

texts:
I just started working with Oracle's products, and I downloaded the "Developer Day" VM from the following link:

http://www.oracle.com/technetwork/database/enterprise-edition/databaseappdev-vm-161299.html

The VM boots up fine. Then, I proceeded to install the Enterprise Manager, from the following link:

http://www.oracle.com/technetwork/oem/grid-control/downloads/linuxx8664soft-085949.html

It was only missing one prerequisite (glibc-devel.i686), which installed without an issue. The problem I have is when I get to the page which asks for the host... by default, it says "localhost," but the setup doesn't like this, and doesn't let me proceed.

Changing it to another irrelevant value doesn't work.

Sorry if this is on the wrong "stack," I assumed that dba would be the best.


texts: MySQL Version 8 shows as 5.7.26 in database management applications. Can anyone explain?

texts: MySQL Version 8 shows as 5.7.26 in database management applications. I need version 8 for some of my queries to work. I am using Sequel Pro and phpmyadmin. Thanks in advance.


texts: SQL Server - Debugging - How to show parametervalues as HEX

texts:
I am writing a script which sets Parameters in the Registry for SQL Server.
The following works on some of my servers but not all.

DECLARE
@Parameters AS VARCHAR(MAX)
SET @Parameters = '-T1118'

SELECT  * FROM sys.dm_server_registry WHERE value_name LIKE 'SQLArg%'
  AND CONVERT(VARCHAR(MAX),value_data) = @Parameters

The error was the CONVERT(VARCHAR(MAX),value_data). It has to be CONVERT(VARCHAR(6),value_data). Then it worked on all of them, since all the parameters I'm adding have 6 chars.

Now I'm trying to debug the error. So I press debug and see the parameters (altered script for this ofc) and see no difference. So I want to dig deeper into HEX to see what is going on. Press rightclick and press Hexadecimal Display... nothing.

So long story short: How can I see the HEX-Values of parameters in SSMS while debugging?


texts:
Why does this query cause a deadlock?

UPDATE TOP(1) system_Queue SET
  [StatusID] = 2,
  @ID = InternalID
WHERE InternalID IN (
    SELECT TOP 1
      InternalID FROM system_Queue
    WHERE IsOutGoing = @IsOutGoing AND StatusID = 1
ORDER BY MessageID ASC, InternalID ASC)

Deadlock graph added:

<keylock hobtid="72057594236436480" dbid="9" objectname="Z.dbo.system_Queue" indexname="PK_system_Queue" id="lock5b25cc80" mode="X" associatedObjectId="72057594236436480">
    <owner-list>
     <owner id="processc6fe40" mode="X"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc7b8e8" mode="S" requestType="wait"/>
    </waiter-list>
   </keylock>
   <keylock hobtid="72057594405453824" dbid="9" objectname="Z.dbo.system_Queue" indexname="IX_system_Queue_DirectionByStatus" id="lock48cf3180" mode="S" associatedObjectId="72057594405453824">
    <owner-list>
     <owner id="processc7b8e8" mode="S"/>
    </owner-list>
    <waiter-list>
     <waiter id="processc6fe40" mode="X" requestType="wait"/>
    </waiter-list>
   </keylock>

ADDED:

Thank you Sankar for article that has solutions how to avoid this type of deadlock:

eliminate unnecessary columns from reader’s projection so he does not have to look up the clustered index
add required columns as contained columns to the non-clustered index to make the index covering, again so that the reader does not have look up the clustered index
avoid updates that have to maintain the non-clustered index


texts: "sudo -u postgres psql postgres" does not ask for password

texts:
I've taken over a system using postgres. When logging in, if I use ...

sudo -u postgres psql postgres

...it does NOT ask for a password. However, if I do ...

psql -U postgres -h localhost postgres

... it asks for the password.

Why?
How is this difference configured?
Can it be configured for a different user to login this way?

(NOTE: user postgres is NOT in the sudoers file, or in the sudo group.


texts: Promotion of PostgreSQL standby and application reconnection

texts:
Background:

I work for a small company.  We have been using PostgreSQL on Heroku as one of our core technologies.  While, in general, we've been happy with Heroku, the company has made the decision that our monthly bill with them is getting prohibitively expensive.  As a result, the engineering team has decided to port everything over to DigitalOcean.  Since I'm the most experienced database developer on the team, I've been given the task of figuring out "what to do about the databases".  To be clear, I've got almost 20 years of writing SQL statements and stored procedures and 4 years with PostgreSQL, but I'm definitely not a DBA (And frankly, I'm scared to death that I'm going to screw things up).  I've spent every night for a week reading blogs and watching videos on configuration strategies for "high availability".  What I've decided to do is create a master, with 2 hot standbys.  For each service/product we have, the architecture is basically the same:

1:n webserver(s)
1:n jobserver(s)
1:n reporting server(s)

Web and job(s) will be pointed to master, and the reporting server(s) will be pointed to 1 of the hot standbys.

note: We are using Anisble for Cloud/IT automation.

So, the idea is that if the master fails, the most up-to-date standby gets promoted to Master. Obviously, that means that the other hot standby must be switched to the new master, and the all the servers (web, job, reporting) must be reconfigured and reconnected.

Questions

I've read a lot about how to do this with something like repmgr.  But, nothing on how all of the other servers know about this change and are reconfigured.  Is there a simple way this is done?  With Anisible, we could have a playbook that connects and changes environment vars and restarts the services, but it's hard for me to believe this is the way everyone does it.  It feels like there is a piece missing for me.  It feels like you'd just need/want some kind of a proxy in the middle and just reconfigure it.  Is this where people use something like PgBouncer or PgPool?

Any help will be greatly appreciated.

Relevant technologies:

PostgreSQL 9.3 (but, could move up to 9.4 if there is a reason)
DigitalOcean
Ubuntu 14.04
Python 2.7
Ansible


texts:
Seems like a simple thing, but I can't seem to find it in the docs for COPY.

Is Vertica's COPY atomic? If a data load is interrupted or somehow fails, does Vertica take care of the cleanup?


texts: How to pick a chunk from the text column in postgresql

texts:
I have a table ABC with the following 2 columns
id(PK)        Name

1             Verapamil 160mg tablets (X Y Z Pharmaceuticals Ltd) 56 tablet 4 x 14 tablets
2             Zocor 10mg tablets (You & Me Ltd) 28 tablet 2 x 14 tablets
3             Atenolol 100mg tablets (NoName Pharmaceuticals Ltd) 20 tablet 2 x 10 tablets

I would like to select 56 tablet from id=1, 28 tablet from id=2 and 20 tablet from id=3. Is there anyway in Postgres to select just the chunk from the text?


texts: Counting NULLs as zeroes and filtering with the WHERE statement

texts:
I have a SQL puzzle:

How can we get a list of customers who have purchased 3 or fewer socks (including those who haven't purchased any socks) as well how many socks those customers have purchased?

I am running into a problem where I ended up with NULL values that need to be displayed as zeroes. However, when I use CASE statement to select NULLs as zeroes and then use WHEN statement to filter below certain threshold zeroes do not show up for some reason.

Here is my schema and some dummy values:

create table customers (
  cust_id TEXT,
  name TEXT
);

create table orders (
  order_id TEXT,
  cust_id TEXT,
  order_date DATE,
  num_socks INT
);

insert into customers values ('1001', 'Rudy');
insert into customers values ('1002', 'Jonny');
insert into customers values ('1003', 'Romeo');

insert into orders values ('1', '1001', '2015-05-01', 4);
insert into orders values ('2', '1001', '2015-04-03', 2);
insert into orders values ('3', '1003', '2015-04-10', 8);
insert into orders values ('4', '1003', '2016-05-10', 3);

Here is my query:

SELECT name, CASE WHEN socks_bought IS NULL THEN 0 ELSE socks_bought END AS socks_bought
FROM customers
LEFT JOIN
  (SELECT cust_id, sum(num_socks) AS socks_bought
   FROM orders
   GROUP BY cust_id) AS t1
    ON t1.cust_id = customers.cust_id
where socks_bought < 3;

However, it does not produce the desired output:

name | socks_bought
-------------------
Jonny | 0

What am I doing wrong? What is the query to produce the intended results? Thanks.


texts: how to select top ID from join operation and staging tables

texts:
I have this inner join from staging and operations table jobsCode  I want to select the top surrogate key ID and get the Start_Date from this join my problem is my Query rerunning 2 records
original query

SELECT a.JobCode
    ,b.ID
    ,b.Start_Date
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date
FROM [RPStage].[dbo].[GLBJobCode] a
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date;

trying to get it using max function

SELECT a.JobCode
    ,MAX(b.ID) ID
    ,MAX(B.Start_Date) Start_Date
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date
FROM [Stage].[dbo].[JobCode] a
    INNER JOIN [operations].[dbo].[Job_Code] b ON  a.JobCode =  b.Job_Code
        AND a.EndDate = b.End_Date
        AND a.EndDate = '9998-12-31'
        AND a.StartDate != b.Start_Date
GROUP BY a.JobCode
    ,b.ID
    ,a.EndDate
    ,b.Start_Date
    ,b.End_Date;


texts:
I recently have an hdd failure and I try to restore Griffith (https://github.com/micjahn/Griffith) database from a /var/lib/mysql/griffith file copy. My new computer uses a more recent version of MySQL:

original :  5.7.19-0ubuntu
new :       5.7.20-0ubuntu

I read many a thread about innoDB database recovery and I succeeded to restore many tables but the main table (movies) fails to get restored.

Here is what I do:

create a new database
find table structure with https://recovery.twindb.com/
recreate tables
use for all tables:

ALTER TABLE mytable DISCARD TABLESPACE;

copy backuped files
use for all tables:

ALTER TABLE mytable IMPORT TABLESPACE;

On the last step on the movies table, it fails and mysql server terminates.

After some analysis, I found out that the original database was created with utf8 encoding (twindb script was using latin1). But it does not fix the issue.

I also changed mysql config with:

innodb_force_recovery=6

It helps, doing a select count(*) gives some info, but I cannot dump the table to make a real backup.

In mysql logs I have this error when running the IMPORT TABLESPACE command:

[...many errors and warnings...]
[ERROR] InnoDB: Table `mysql`.`innodb_table_stats` not found.
[ERROR] InnoDB: Fetch of persistent statistics requested for table `griffith`.`movies`
but the required system tables mysql.innodb_table_stats and mysql.innodb_index_stats
are not present or have unexpected structure. Using transient stats instead.
[ERROR] InnoDB: Trying to access page number 1647523377 in space 238, space name
griffith/movies, which is outside the tablespace bounds.
Byte offset 0, len 16384, i/o type read.
If you get this error at mysqld startup, please check that your my.cnf
matches the ibdata files that you have in the MySQL server.
[ERROR] InnoDB: Server exits.

Can someone help me?


texts:
In mysql with innodb, there are two types of logs: binary log and transaction log. I know transaction log is specific to innodb. But when comes to replication, backup and crash recover, what roles each of them play and what is the different?

Thank you!


texts: How do I have Oracle ASM scan for and repair uncorrectable ECC errors (bad sectors)?

texts:
I'm running Oracle ASM (11gR2) with normal redundancy on SATA disks (WD Raptors). This is on a physical standby database, if it matters. Occasionally, disk sectors develop read errors (ECC failures). When that happens, that block is no longer mirrored, but ASM doesn't know this.

How can I have ASM read through the partitions it controls, and make sure that each block is readable?

Ideally, it'd also confirm that both sides of the mirror match, and re-write any sectors that the disk reports uncorrectable ECC errors for.

(I.e., I'd like the equivalent of mdraid's echo 'repair' > /sys/block/md0/md/sync_action or a hardware RAID controller's background consistency check.)

Update 2012-01-03: So far, the best I've found is to alter the diskgroup to drop the disk, wait for the rebalance to finish, use an OS utility to zero the disk, and then add the disk back. This of course doesn't compare the two sides of the mirror, but at least reallocates any bad sectors. It seems like there must be a better way. Its hard to believe ASM lacks such a standard RAID feature.


texts: Validation of availability for a new order

texts:
I have a management application of sales, stock and payment on a warehouse whole saler from a web interface. In particular, when a order is effectuated it must create a line corresponding to each product ordered with the respective quantity. The validation of the stock availability is done at the moment of the order.

Considering the following two ways of validation on order:

Use a trigger BEFORE INSERT on the table OrderLine, that does a SELECT on Product verifying there is enough stock.
Do a SELECT ... FROM OrderLine JOIN Product WHERE quant < stock.

My question is: Which of these two alternatives is preferable, and why / for what scenario?


texts: Log shipping Readonly/Standby executes Queries slow for some users

texts:
I have a SQL Server database that is doing log shipping to another SQL Server instance, both servers have identical hardware. We have a reporting service that then runs reports against the readonly log shipped database. What we are experiencing is that when the application user executes a report at the readonly copy of the database, the query runs for a very long time. The same query executed against the primary database runs for about 5 minutes, and if you log in to the readonly database with another user we can get the results in 5 minutes as well, but for the application user which does exist at both the Primary and Secondary server correctly the report runs for more than an hour.

Any ideas on what to check?


texts: Problem with pattern matching on unicode characters

texts:
I'm writing a piece of code that uses pattern matching on unicode characters but I'm running into an odd problem.  Some characters work and some don't.

DECLARE @Pattern nvarchar(50) = N'%[^a-z]%' -- Simple pattern
SELECT PatIndex(@Pattern, nchar(46797)), nchar(46797) -- Works
SELECT PatIndex(@Pattern, nchar(14843)), nchar(14843) -- Doesn't Work

The pattern should be pulling for any character that isn't a-z but for some reason it isn't catching some characters.  Does anyone know why some unicode characters would match and others wouldn't?


texts: Understanding DBCC CHECKDB, Snapshots, and SQL Server Standard Edition

texts:
Understanding the circumstances under which SQL Server creates a snapshot when running CHECKDB, I followed a link to this KB that states:

Database snapshots are available only in SQL Server Enterprise.

I'm trying to plan some maintenance work and need to understand potential I/O bottlenecks that could extend this maintenance window.

Does this snapshot limitation imply that running DBCC commands on SQL Server Standard SKUs will always run against the actual database, or is an internal snapshot still used?


texts: Row-row based logging in a blackhole replication filter setup?

texts:
I am using two MySQL server instances on the same server to filter replication to a third external server. My filter slave is using the blackhole engine as described here: https://dev.mysql.com/doc/refman/5.7/en/blackhole-storage-engine.html

Both master and slave use statement based replication. The documentation says:

  Inserts into a BLACKHOLE table do not store any data, but if statement
  based binary logging is enabled, the SQL statements are logged and
  replicated to slave servers. This can be useful as a repeater or
  filter mechanism.

The above statement makes me assume that if I had both of my MySQL instances set to row based replication, nothing would make it to the third, external database. Which kind of makes sense since there are no actual rows in the filtering blackhole database.

However, I have been thinking... Would it not be possible for the filtering middle instance to simply pass on any row based instructions it receives to its own binlog, i.e. would a row-row filtering blackhole setup not work?


texts: What is the purpose of the database 'owner'?

texts:
Today while troubleshooting a service broker problem, I discovered that the database owner was the Windows login of an employee who had left the company. His login had been removed and thus the query notifications were failing.

Supposedly the best practice for dealing with this is to make 'sa' the database owner. We changed it and that cleared out the queue.

My (very elementary) question: what is the database owner and what is its purpose?