texts: Oracle Error: the sga requires more space than was allocated for it

texts:
(Oracle SQL 12c running on Windows 10)

My installation has previously been working fine. I was trying to set up one of my tables to use the In-memory option available in 12c. I was following this guide: https://blogs.oracle.com/In-Memory/entry/getting_started_with_oracle_database
I wanted to allocate 4Gigs of system RAM to be used as in-memory table storage space for a table I would specify later. To do this I ran these commands using the guide as a reference:

This one to allocate 4G of RAM space:

ALTER SYSTEM SET inmemory_size = 4G scope=spfile;

And this one, which is what caused the problem:

ALTER SYSTEM SET sga_target = 75G scope=spfile;

The guide said "By default, the installer set the SGA_TARGET to 71G, " which is where I came up with 71G + 4G = 75G.

Now of course I get the error when trying to connect to the DB:

  ORA-27101: shared memory realm does not exist

  the sga requires more space than was allocated for it

Since I can't connect to the database because it won't start, how would I fix the invalid setting? Is there some sort of "undo"?

Edit:
No, there is no backup of the spfile or pfile that I can use to generate the spfile. I have manually changed the sga_target to small values that should be fine but still get the error no matter what. I also tried creating a new, additional database. When I did that, the other database started working for some reason. It just looks like the new one though and is missing new tables I had previously created.


texts:
While answering this question
https://stackoverflow.com/questions/4668146/how-to-add-a-primary-key-on-a-oracle-view/8558346#8558346

A lot of questions popped in my mind.

1. Is it advisable to have primary key in a view.
2. Why anybody be intrested in having a primary key in a view, what are its advantages. I mean one cannot search a view by index.
3. Is it because of hibernate, but again i have used distinct keyword in a view declaration which means i cannot insert via view.

Here is my code for view

CREATE OR REPLACE FORCE VIEW VU_NAME
    (
      PRIMARY_KEY, NAME_ID, ADDRESS_ID
     )
    AS
    SELECT DISTINCT ROWNUM AS PRIMARY_KEY,
        NAME.ID UNIT_ID,
        ADDRESS_ID
    from table1;


texts: The possibility to define a composite unique constraint for specific cases

texts:
I have two tables: user and address.
User table:

id
email
name

1
henk@example.com
Henk

2
john@example.com
John

Address table:

id
user_id
street
is_default

1
1
Super cool street
1

2
1
Beautiful avenue
0

3
1
Some street
0

4
2
Damn cool street
0

5
2
Cool street
1

6
2
My street
0

The problem
Image someone setting two default addresses for a user; it would be non-sensical from the Business logic perspective.
The question
I want to create a unique constraint composed by user_id and is_default but only for a specific case, which is that the constraint must be enforced when is_default is 1.
Is this possible with mysql?


texts: Need help with reducing cost of view's group by

texts:
I have a view which joins couple tables and returns a list of available book copies. My view creation code is as follows:

CREATE VIEW AvailableCopies AS
SELECT Product.title, CONCAT(Author.firstName, ' ', Author.lastName) as Author_name, ProductType.pType as Product_type, Product.lendable, count(BookCopy.bisbn) as Copy_count
FROM Product
INNER JOIN BookCopy on BookCopy.bisbn = Product.isbn
INNER JOIN Author on Product.authorId = Author.id
LEFT JOIN ProductType on ProductType.id = Product.typeId
WHERE BookCopy.Available = 'true'
GROUP BY Product.title, Author.firstName, Author.lastName, Product.lendable, ProductType.pType;

The problem that I cannot get rid of, is GROUP BY line. I cannot omit it, if I do, I get the following error:

Error: Column 'Product.title' is invalid in the select list
because it is not contained in either an aggregate function
or the GROUP BY clause.

I spent quite a lot of time online, trying to solve this issue in some cheaper way than adding all my selected columns into GROUP BY, but sadly no luck. Execution plan says that my group by statement costs 40-41% of whole process! Is there a way to solve this? This view returns 37,5K results, at the moment for education purposes, I am only returning TOP 20 in my nodeJs application, but it is really frustrating to wait 8-10 seconds for a simple page to load, with a size of 20 in a recordset. I am aware of caching, but I would like to keep this solution as a last option for now.

pastetheplan is not accepting my execution plan xml so I cannot provide it.

After view creation I call Select TOP 20 from AvailableCopies . My database is hosted on Azure, I was trying to find version, but I couldn't. But as it is Azure, I assume SQL server 2017.


texts: Can't create database in SQL Server Express 2005

texts:
One of our clients is running Windows Small Business Server 2011, and they have a SQL 2005 Server Express instance (and several other versions also installed).

Using Management Studio, running as administrator, I cannot seem to create any databases. The error I get is:

  Could not obtain exclusive lock on database 'model'. Retry the operation later.
  CREATE DATABASE failed. Some file names listed could not be created. Check related errors. (Microsoft SQL Server, Error: 1807)

The first time this happened, there happened to be a backup task running which had a lock, so I tried again later, and still had the issue. Running EXEC sp_who2 shows only my own user with 2 locks on the model db, both are "sleeping", and seem to come from the Management Studio (.Net SQL Client, same last batch as when I started the app).

Unfortunately I mostly work with MySQL, so all this MS stuff is over my head.

Edit:

Here is the result of SELECT  * FROM sys.sysprocesses WHERE DB_NAME(dbid) = 'model'

Result:

+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
| spid |         lastwaittype         | dbid | uid | physical_io | login_time | open_tran |   status    | hostname | hostprocess |       cmd        |       loginame       |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+
|    1 | SLEEP_TASK                   |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | RESOURCE MONITOR | sa                   |
|    2 | LAZYWRITER_SLEEP             |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LAZY WRITER      | sa                   |
|    3 | LOGMGR_QUEUE                 |    0 |   1 |           0 | 12:45.8    |         0 | suspended   |          |             | LOG WRITER       | sa                   |
|    4 | REQUEST_FOR_DEADLOCK_SEARCH  |    0 |   1 |           0 | 12:45.8    |         0 | background  |          |             | LOCK MONITOR     | sa                   |
|   52 | MISCELLANEOUS                |    3 |   1 |           0 | 05:14.5    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   62 | MISCELLANEOUS                |    3 |   1 |           0 | 04:13.0    |         0 | sleeping    | BH-SBS   |        6976 | AWAITING COMMAND | BH\nuser             |
|   74 | MISCELLANEOUS                |    3 |   1 |           5 | 02:23.1    |         0 | runnable    | BH-SBS   |       22436 | SELECT           | BH\nuser             |
+------+------------------------------+------+-----+-------------+------------+-----------+-------------+----------+-------------+------------------+----------------------+


texts: Why is SSIS slow to enumerate over many files in a directory and import them?

texts:
I have a terribly slow SSIS package. It's quite fast with one file, and reasonably fast with 100 files or fewer. (About one second per file)

However, if my directory has thousands of (very small) files, the process drags on terribly slowly. My preference is to run this process only after business hours, but by waiting until then, the number of flat files to import is in the thousands.

The package is very simple:

Outer Loop is For Every (file enumeration, read file path into variable)
Inside, simply import without any transformation to the data

That is it.

Performance with thousands of files is running 15 seconds or more per each file. The UI (status) is drawing/scrolling so slowly that I can't even see where it's at -- the stamped time is more than 15 hours old on an execution that was started 18 hours ago.

Version: MSSQL 2012


texts: Postgres - syntax error near IF when using inside Execute

texts:
I am using an after update trigger in order recognize and log any changes made to records in the 'tasks' table. The column, and new and old value are all dynamic, so different every time.

This is what the trigger function looks like right now:

CREATE OR REPLACE FUNCTION fn_tasks_after_update()
RETURNS trigger
AS $BODY$
DECLARE
    ri RECORD;
    col_name TEXT;
BEGIN
    FOR ri IN
        SELECT column_name FROM information_schema.columns WHERE table_name = 'tasks'
    LOOP
        EXECUTE
            'IF $1.' || ri.column_name || ' <> $2.' || ri.column_name || ' THEN
                INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
                VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');
            END IF;'
        USING NEW, OLD, ri.column_name;
    END LOOP;
    RETURN NEW;
END; $BODY$
LANGUAGE plpgsql VOLATILE
COST 100;
ALTER FUNCTION fn_tasks_after_update()
OWNER TO postgres;

The trigger worked before adding the IF, and the syntax does not seem any different from what is in the 'INSERT INTO tasks_log' block.

This does work:

EXECUTE
    'INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
    VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');'

This does not work:

EXECUTE
    'IF $1.' || ri.column_name || ' <> $2.' || ri.column_name || ' THEN
        INSERT INTO tasks_log (task_id, type, "column", old_value, new_value)
        VALUES ($1.id, $$update$$, $3, $1.' || ri.column_name || ', $2.' || ri.column_name || ');
    END IF;'

What is going wrong?


texts: MySQL After Insert trigger - how to handle case where no rows were inserted

texts:
UPDATED based on Akina's answer -

In MySQL the After Insert trigger fires for every insert call, even in cases where the insert fails and no rows are inserted. While any changes made by the trigger are rolled-back by MySQL on failure, external service calls made by the trigger may cause changes that need to also be rolled back.

Is there a way to catch this edge case in the trigger so it can be handled?

For ref my general trigger format is -

CREATE TRIGGER `db`.`table_name_AFTER_INSERT` AFTER INSERT ON `table_name` FOR EACH ROW
BEGIN

-- HANDLE ERRORS
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
BEGIN
    get diagnostics condition 1
      @p1 = MESSAGE_TEXT,
      @p2 = RETURNED_SQLSTATE,
      @p3 = MYSQL_ERRNO,
      @p4 = SCHEMA_NAME,
      @p5 = TABLE_NAME;
    INSERT INTO `db_errors_table_name` (`location`, `error`)
        VALUES ('Some descriptive text here regarding loc', @p1);
END;

-- Call external service here

END


texts: Check for table lock in conditional statement

texts:
How can I make this work?

IF( EXISTS (SHOW OPEN TABLES WHERE In_use < 2 and `Database` like 'MYDB' ) ) THEN
 -- DO SOMETHING
END IF

Context

I want to run some sql statements inside a procedure, only if the table x is not locked ( because of updates or any other reason ).

I want to do this on the MySQL server side as I have clients that trigger this procedure from more than 12 machines from bash, c and  c#.

So is there any way to get the table lock information in a conditional statement on the server side ?


texts:
I have a question on implementing gMSA account for managing authentication to SQL server.

Our goal is to use gMSA account to manage the auto password change activities for SQl server service and it's agent service.

Few questions-

Does implementing gMSA works for 2 node SQL FCI and 3 or 4 Node Always on AG,s?
Since I just want to run SQl server with that gMSA account is there any special permissions needs to be granted at server or database level  while running both SQL and agent services?

Our AD team gets all the background work related to creation of those accounts. Once ready we are just handed over the abc$ account e.g. to use them for running SQL services without entering the need of any password.

Please advise any catch of implementing gMSa here.


texts:
I am doing Performance evaluation of my application. The size of database is around 20GB. I am using jMeter as a performance evaluation tool. Here each performance test (ie., Each jMeter run) there is some number of records are inserted. Here is my testing sequence.

Run jMeter and check the time taken to complete an action
Finetune the code for performance.
Re-Run the same scenario and check the timing.

For example.
 Before run :

Id      Name
1       XXXX
2       YYYY
3       ZZZZ

After Step 1, My table data look likes

Id      Name
1       XXXX
2       YYYY
3       ZZZZ
4       AAAA
5       BBBB
6       CCCC
..etc

Now I wants to revert my table like the first ie.,

Id      Name
1       XXXX
2       YYYY
3       ZZZZ

Here what my problem is the DB size is growing for each test run.

Is there any way to maintain some benchmark (if the DB size is 20GB means, It must be maintain the size as 20GB till end of my test). Any one please guide me how to achieve my requirement.


texts: INSERT BULK in SQL Profiler without values

texts:
I started SQL Profiler to catch actions that are doing on server, and among them I found one row where is stated:

SQL BatchStarting: insert bulk table_name <column_list>

but there is no values?

How to see what database accept from application?


texts: How do I revert the MUST_CHANGE in this SQL Statement?

texts:
I executed

SELECT N'ALTER LOGIN ' + QUOTENAME(name)
  + N' WITH PASSWORD = N'''' MUST_CHANGE, CHECK_POLICY = ON;'
  FROM sys.sql_logins
  --WHERE is_policy_checked = 0;

But I don't want the MUST_CHANGEoption on the login.

Did this query actually change anything or is this just a simple select?


texts: How to effectively query and aggregate a normalized SQL db?

texts:
In relation to Most efficient way to return multiple aggregates in a single stored proc?

I have an email type application and want to select all the messages (inbox) for a user. The problem is that I normalize the header part of the emails into the DB such that the flat data goes into a Message table and the from, to, CC, BCC get stored to another table.

What's the best way to select messages (in full - meaning denormalize the full message) so that each record contains all the message pertinent fields including all the Message table fields and any related records from the recipient table related to the message as per the PK/ FK relationship.

One thing I am placing much weight on is the efficiency of the SQL solution because this will be code that gets executed many many times over and will likely be the most run sql of the entire DB

For context here is a view of my DB schema.


texts:
It's been a while since my databases course in university, so bear with me as I try to be unambiguous here.

In the domain I'm working in, there are Sources and Destinations.  Both have 2D coordinates (an X and Y coordinate).  Both Source and Destination have separate data in addition to the coordinates.  Finally, there is a one-to-many relationship between Source and Destination.

Idea #1

Use two tables, one for each type of coordinate:

SourceID  |  XCoord  |  YCoord |  Other Source Columns |
--------------------------------------------------------
332       |  33      |  4      |          ...          |
423       |  213     |  342    |                       |
211       |  365     |  12     |                       |

DestinationID  |  Source ID  |  XCoord  |  YCoord |  Other Dest. Columns |
--------------------------------------------------------------------------
455            |  332        |  24      |  4      |          ...         |
892            |  332        |  653     |  32     |                      |
112            |  211        |  5       |  122    |                      |

Idea #2

Use three tables, where Coordinates are stored in their own table, but everything else is the same:

CoordinateID  |  XCoord  |  YCoord |
------------------------------------
1             |  33      |  4      |
2             |  213     |  342    |
3             |  365     |  12     |
4             |  24      |  4      |
5             |  653     |  32     |
6             |  5       |  122    |

SourceID  |  CoordinateID |  Other Source Columns |
---------------------------------------------------
332       |  4            |         ...           |
423       |  5            |                       |
211       |  6            |                       |

DestinationID  |  SourceCoordID |  CoordinateID |  Other Dest. Columns |
------------------------------------------------------------------------
455            |  332           |   1           |          ...         |
892            |  332           |   2           |                      |
112            |  332           |   3           |                      |

The differences I can think of are these:

The second one will perform worse: in order to get the (x, y) coordinates of a source or destination, the source or destination table must be joined with the coordinate table.
The second one is more maintainable: only one table needs changing if, say, a timestamp needs to be stored with every (x, y) pair.

Are those differences correct?

(I'm happy to remove the next question if it's too open ended or off-topic.)  Are there other significant differences?


texts: How to reduce identity values to avoid integer overflow?

texts:
I have some leaf tables (no FK towards them) with a few hundreds of thousands records that are used to synchronize some external data using Entity Framework ORM. This involves some DELETEs followed by BULK INSERTs.

For most of the tables, some old values may stay forever, so I cannot use SEQUENCEs with CYCLE, as suggested in one of the comments.

One effect is that identity values will keep increasing over time and I want to be able to reduce their values.

This question and its answers explain that identity values cannot be updated, even if identity_insert is on for the table.

One quick way to do is transferring all data to a buffer table and perform the switch via rename. Something like the following:

-- ActiveDirectoryCache_bak is the table I want to reduce identity values for
-- ActiveDirectoryCache_bak_buffer is a buffer table that will be renamed to ActiveDirectoryCache_bak once data transfer is ready

begin tran
select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 100, Count = 176041
-- Checking identity information: current identity value '204558', current column value '204558'.

select * into ActiveDirectoryCache_bak_buffer
from ActiveDirectoryCache_bak
where 1 = 0

DBCC CHECKIDENT('ActiveDirectoryCache_bak_buffer', RESEED, 1)

insert into ActiveDirectoryCache_bak_buffer
select LoginUsername, GivenName, MiddleName, Surname, EmailAddress
from ActiveDirectoryCache_bak

drop table ActiveDirectoryCache_bak

alter table ActiveDirectoryCache_bak_buffer add constraint PK_ActiveDirectoryCache_bak PRIMARY KEY (UserId)
EXEC sys.sp_rename 'ActiveDirectoryCache_bak_buffer', 'ActiveDirectoryCache_bak';

select min(UserId), count(1) from ActiveDirectoryCache_bak
DBCC CHECKIDENT ('ActiveDirectoryCache_bak', NORESEED);

-- Min UserId = 1, Count = 176041
-- Checking identity information: current identity value '176041', current column value '176041'.

-- this should be replaced with commit when not in test mode
rollback

I can afford to make this kind of operations during the night and they seem to take only a few seconds for my data volume (4 seconds for this example).

Question: Are there any methods to avoid performing a full data copy to obtain smaller values for identity columns? Or this is one of the best approaches in my context (reasonable data volume and being able to lock some tables for several seconds).


texts: How to bulk insert data in vertical into sql server?

texts:
My data in below format. I have to insert into sql server table.

agency    NO
booknbr   06000330
bookdttm  2006-07-19 10:56:00
arrdttm   2006-05-07 05:42:00
aj_id     A000009454
casenbr   06006640
off1      619
off2      634
arrplace  DENNYS RESTAURANT
howarr    O
juvstat
ko
remarks
armed
federal   N

agency    NO
booknbr   06000331
bookdttm  2006-07-24 12:11:00
arrdttm   2006-07-16 16:11:00
aj_id     A000003215
casenbr   06010336
off1      641
off2
arrplace  219 W GLENCOVE AV
howarr    V
juvstat
ko
remarks
armed
federal   N

agency    NO
booknbr   06000332
bookdttm  2006-07-25 15:11:00
arrdttm   2006-06-13 22:47:00
aj_id     A000009455
casenbr   06008615
off1      624
off2
arrplace  113 JULIE DR
howarr    V
juvstat
ko
remarks
armed
federal   N


texts: Will alone bin log ignore DB mysql helps in slave read only mode

texts:
Please correct me if I'm wrong .
In Master - Slave setup:

Case 1:

  If I didn't  enable Binlog ignore DB mysql: In future ill lose all
  users created in master node incase of master crash or slave to master
  and master to slave scenario.

Case 2:

  If I did enable binlog ignore DB mysql and an user has  all
  privileges, he can directly connect to slave DB[if he is fishy], and
  manipulate data in slave which causes inconsistency.

How to over come this scenario? I'm new to this. Any help is highly appreciated.


texts: pgAdmin IV and PostgreSQL 12.0: ERROR: schema "public" already exists

texts:
I have a database backup from pgAdmin III with PostgreSQL 12.0 (Ubuntu 12.0-2.pgdg16.04+1) on Ubuntu 16.04 LTS.

I would like to restore this database on my pgAdmin IV with PostgreSQL 12.0 on Docker containers (i.e. pgAdmin IV and PostgreSQL (PostGIS)). The images are: kartoza/postgis:12.0 and dpage/pgadmin4:4.14.

The problem is when I restore the database, pgAdmin IV output shows the following error message:

pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: connecting to database for restore
pg_restore: creating SCHEMA "public"
pg_restore: while PROCESSING TOC:
pg_restore: from TOC entry 7; 2615 2200 SCHEMA public postgres
pg_restore: error: could not execute query: ERROR:  schema "public" already exists
Command was: CREATE SCHEMA public;
pg_restore: creating COMMENT "SCHEMA public"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1
pg_restore: creating FK CONSTRAINT "public.layer_reference fk_reference_layer_reference1"
[...]
pg_restore: creating FK CONSTRAINT "public.places_pilot_area places_pilot_area_fk_street_id"
pg_restore: warning: errors ignored on restore: 1

I would not like to show these error messages, because they seem to be warnings and not errors (PostgreSQL is trying to create a public schema where it already exists). These errors are not shown on pgAdmin III when I restore using the same database.

There is this similar question. According to that answer, the error is harmless, but I would like to remove it from pgAdmin IV output.

I've already tried to restore with: (1) default options; (2) create option turned on; (3) clean option turned on; and (4) create and clean options turned on; however no success, all previous options result on an error message.

I'm afraid of ignoring these warnings and in the future true errors appear and I do not see them.

Thank you in advance.


texts:
Assuming that an organization has everything needed to use Availability Groups successfully, and that they have been using Availability Groups successfully for a few years, are there any reasons why log shipping would be a better DR solution?  It seems that people are still putting in log shipping just because it can meet the RPO and RTO.  But if it doesn't cost anything extra to use an AG instead, and the DBA staff has experience with AGs, why would log shipping be better?

The only thing I can think of is that the built-in delay of log shipping allows one to recover quickly in the case that someone forgot to put a WHERE clause on an UPDATE or DELETE statement--if it's caught in time.  Unlikely, and not something we're concerned about.

My general experience is that log shipping requires more intervention and tweaking as the database load increases, is more difficult to fail over, and is generally a bit of a Rube Goldberg machine (albeit a quite successful specimen of that class of machine).


texts: Best way to schedule structure schema design of a events database

texts:
On this case at the events table has a repeated schedule and scheduled by date

example case each of event schedule:

The event has schedule every month, week and day with start and end of time
The event has schedule every working days and working hours
The event has date range schedule
The event has a date for once converence

for following these problem, I'm so confusing to create the dynamic scheme for these condition

Table structure

events table column

id PK
title
location

event_schedules

I create this scheme referenced from crontab scheduling where it can be create every year, month, week, day, or custom date. And the other condition I would to record event shedule with other option

id PK
event_id FK events_table_event_id
year
month
week
day
custom_date
start_time
end_time

Question

What the best way to handle these data structure problem? I need some reference for taking these problem.


texts: Using desktop computers to manage an SQLite database on a fileserver?

texts:
Sometimes, it can take months to get a proper database server up and running in large companies. They have all kinds of policies, which means that this process takes months. An alternative in the meantime is to place an SQLite database directly on a file server. This works nicely for a limited number of users (I have had success with serving 100 users, using one such database), and it is only recommended for non-critical data. Now I have been thinking about ways to get a database, which is placed on a file server to be able to handle, say ten times as many users. The basic idea I have is to utilize the many, mostly idle desktop computers around the company. Again, the data would be non-critical, database access could be slow, and uptime of 99.0% is sufficient.

Do you see any way of utilizing these, mostly ideal desktop computers to help reduce the load on the file server? Do you have any idea of DBMS, which would be most suited, SQLite was just my first thought. It would be best with an open-source, one so I could hire someone to adapt it for this purpose. Alternatively, I would be interested in building a DBMS for this purpose from scratch.

I know that any experienced DBA will pull their hair out over this question, but would really have value due to the policies of these companies.


texts:
As seen in this answer I can use jailer in order to export a subset of data into an SQL script.

But in my case I also want to exclude some tables completely from being extracted at all especially on tables relating on the table that I want to extract.

So how I can exclude the unwanted tables?


texts: SQL Server 2008r2 replication from multiple sources

texts:
I am currently rearchitecting an application so that each of seven businesses will have there own "live" SQL Server 2008 database and at scheduled times during the data the data from all seven databases will be replicated to a master reporting database.

The question I have is do I need to do anything within the replication to maintain the Foriegn Key relationships from the source database tables or will replication take care of it for me?


texts: Inserting sorted results to another table not working

texts:
I have been wondering about this tech difficulties

when I executed

(SELECT
  a.id
, a.no_trx
, SUBSTRING_INDEX(a.no_trx,'/',1) AS 'tipe_trx'
, a.value_date
, a.id_inventory
, a.no_lot
, a.leading_char
, a.amount_in_base
, a.total_price_in_base
, a.qty
, a.unit_id
FROM ksm_mutasi_inv a
JOIN ksm_inventory b
    ON a.id_inventory = b.id
WHERE b.standar_inv_type = 5
ORDER BY a.value_date ASC)

the results are

id  no_trx  tipe_trx    value_date  id_inventory    no_lot  leading_char    amount_in_base  total_price_in_base qty unit_id

....

116 TAI/16/03/0062  TAI 2016-03-31  3648    18.51   +   4695.0000   128205426.0000  4695.0000   1
138 TAI/16/03/0084  TAI 2016-03-31  6282    S5620-1298  +   1828.4500   67320204.5455   1828.4500   1
809 TKMR/16/04/0002 TKMR    2016-04-01  6277    1602-03C    -   76.6500 2880366.2427    1.0000  5
857 TKMR/16/04/0004 TKMR    2016-04-01  6278    2602-03C    -   548.3500    17862391.2289   1.0000  5
1585    TKMR/16/04/0050 TKMR    2016-04-01  6278    2602-03C    -   302.5500    9855505.5463    1.0000  5
825 TKMR/16/04/0003 TKMR    2016-04-01  6271    1601-03B    -   1501.5000   47585767.7650   1.0000  5

.....

but when I using

INSERT INTO ksm_mutasi_inv_benang
    (id,no_trx,tipe_trx,value_date,id_inventory,no_lot,leading_char,
     amount_in_base,total_price_in_base,qty,unit_id)
(SELECT
  a.id
, a.no_trx
, SUBSTRING_INDEX(a.no_trx,'/',1) AS 'tipe_trx'
, a.value_date
, a.id_inventory
, a.no_lot
, a.leading_char
, a.amount_in_base
, a.total_price_in_base
, a.qty
, a.unit_id
FROM ksm_mutasi_inv a
JOIN ksm_inventory b
    ON a.id_inventory = b.id
WHERE b.standar_inv_type = 5
ORDER BY a.value_date ASC);

and do select * from ksm_mutasi_inv_benang

the results are

id  no_trx  tipe_trx    value_date  id_inventory    no_lot  leading_char    amount_in_base  total_price_in_base qty unit_id

....

137 TAI/16/03/0083  TAI 2016-03-31  6282    S5620-1298  +   195.9600    7214890.9091    195.9600    1
138 TAI/16/03/0084  TAI 2016-03-31  6282    S5620-1298  +   1828.4500   67320204.5455   1828.4500   1
755 TTB/16/04/0001  TTB 2016-04-14  3648    20.52   +   1814.4000   49545455.0000   10.0000 2
756 TTB/16/04/0002  TTB 2016-04-19  6224    12-45   +   907.2000    31477273.0000   5.0000  2
759 TTB/16/04/0003  TTB 2016-04-19  6224    12-45   +   3628.8000   127272728.0000  20.0000 2
765 TTB/16/04/0004  TTB 2016-04-23  6223    12-40   +   1814.4000   50000000.0500   10.0000 2

....

Why is this happening although I already using sort before I inserted it ?

CREATE TABLE ksm_mutasi_inv_benang (
  id int(11) NOT NULL DEFAULT '0',
  no_trx varchar(255) DEFAULT '' ,
  tipe_trx varchar(255) DEFAULT '',
  value_date date DEFAULT NULL ,
  id_inventory int(11) DEFAULT '0',
  no_lot varchar(255) DEFAULT '' ,
  leading_char char(1) DEFAULT '' ,
  amount_in_base decimal(20,4) DEFAULT '0.0000',
  total_price_in_base decimal(20,4) DEFAULT '0.0000',
  qty decimal(20,4) DEFAULT '0.0000',
  unit_id int(11) DEFAULT '0',
  UNIQUE KEY idx_id (id),
  KEY idx_id_inventory (id_inventory),
  KEY idx_no_lot (no_lot),
  KEY idx_id_inv_no_lot (id_inventory,no_lot),
  KEY idx_tipe_trx (tipe_trx),
  KEY idx_value_date (value_date)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Discovery 1
I just found out by eliminating the index in ksm_mutasi_inv_benang I get the results that I want. Is this really Mysql Bug ?


texts: Understanding sys.dm_exec_query_stats execution_count vs creation_time

texts:
I've just downloaded the SQL Server 2012 'Performance Dashboard Reports' and am having some issues interpreting the data shown by one of the results and linking it back to the DMV sys.dm_exec_query_stats.

According to the CPU report, my worst performing query is an insert statement that has run ~27K times.

When I take the query_hash for the query and put it into the query below, I get 62 results, each with the same query_hash and query_plan_hash but with different plan_handle values.

SELECT * FROM sys.dm_exec_query_stats AS deqs
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS dest
WHERE query_hash = 0x1561B563078359C1

According to BOL, the execution_count shows:

  Number of times that the plan has been executed since it was last compiled.

and creation_time shows

  Time at which the plan was compiled.

I am 99.9% sure the query runs once every 5 minutes, yet each query in the result set has a plan creation_time of this morning and yet execution_count shows ~400 for each one.

How does SQL Server determine this figure? Are the figures somehow linked by plan_hash or are they unique for each plan_handle?


texts: MS Access Many-Many Query/Form Assistance

texts:
I have somewhat of a beginner-type question.

Could someone look at the below relationships and let me know why I can't get the following form/query to only show the unique Appointment letters? I have 143 appointment letters. I have 135 members. I have 585 records in the junction table. I know that I messed up the form source query (below), but how badly am I off? Your expertise is greatly appreciated.

Resources used:

3 Example databases
4 MS Access 2003/2010/2013 books
5 links:
https://stackoverflow.com/questions/13108525/ms-access-2010-query-pulls-same-records-multiple-times-sql-challenge
https://stackoverflow.com/questions/473604/basic-many-to-many-sql-select-query
Querying a many to many relationship in access
Help with Access many-to-many relationship (beginner)
Query Multiple tables in MS Access

Screenshot of Relationships:

Form:

Query:

SQL:

SELECT DISTINCTROW tbl_apptltrs.*, jtbl_apptltrs_mbrs.*, tbl_mbrs.*
FROM tbl_mbrs INNER JOIN (
    tbl_apptltrs INNER JOIN jtbl_apptltrs_mbrs
    ON tbl_apptltrs.ApptLtrsID = jtbl_apptltrs_mbrs.LinkedApptLettersID)
ON tbl_mbrs.MembersID = jtbl_apptltrs_mbrs.LinkedMembersID;


texts: Select value in Left Join ordered by Date

texts:
I am trying to LEFT JOIN a value to sort by the most recent date, but for some reason it is only returning a result for the last returned result.

These are my table layouts

cs_users

+---------+-------------+
| user_id | user_login  |
+---------+-------------+
|       1 | cornerstone |
|       2 | claire      |
|       3 | ben         |
+---------+-------------+

cs_login_log

+----------+---------------+-----------------+---------------------+--------------+
| login_id | login_user_id | login_user_type |      login_dtm      | login_status |
+----------+---------------+-----------------+---------------------+--------------+
|        2 |             1 |               1 | 2019-09-02 15:23:38 |            1 |
|        6 |             1 |               1 | 2019-09-02 15:49:20 |            1 |
|        8 |             2 |               1 | 2019-09-02 16:10:14 |            1 |
|        9 |             3 |               1 | 2019-09-02 16:12:14 |            1 |
|       10 |             5 |               1 | 2019-09-02 16:12:33 |            1 |
|       25 |             2 |               1 | 2019-09-15 19:18:07 |            1 |
+----------+---------------+-----------------+---------------------+--------------+

The code I've tried so far is as follows:

Attempt 1

SELECT u.user_id,
u.user_login,
l.login_dtm
FROM cs_users AS u
LEFT JOIN (
  SELECT * FROM cs_login_log WHERE login_user_type = '1' AND login_status = '1'
) l ON l.login_user_id = u.user_id
WHERE u.user_status = '1'
ORDER BY user_login ASC LIMIT 25

Attempt 2

SELECT u.user_id,
u.user_login,
l.login_dtm
FROM cs_users AS u
LEFT JOIN (
  SELECT login_user_id, login_dtm, MAX(login_dtm) FROM cs_login_log WHERE login_user_type = '1' AND login_status = '1'
) l ON l.login_user_id = u.user_id
WHERE u.user_status = '1'
ORDER BY user_login ASC LIMIT 25

But for some reason in all of the results it only returns the most recent login of the last row!

Returned data

+---------+-------------+---------------------+
| user_id | user_login  |      login_dtm      |
+---------+-------------+---------------------+
|       3 | ben         | null                |
|       2 | claire      | null                |
|       1 | cornerstone | 2019-09-02 15:23:38 |
+---------+-------------+---------------------+

I'm only new to this kind of JOIN so no idea if I'm even anywhere near being close? Basically I want to be able to get a list of my users and their most recent successful login timestamp.


texts: Storing personal data on an invoice to preserve data integrity

texts:
I want to store invoice data like addresses of people. But the problem is addresses can change over time - for example someone will move out and change his address. The problem of storing such data arises, because we can't change the data that is referenced on some invoice.

I have 3 tables: AddressInfo (contains StreetAddress and so on), Person which has one-to-many relation with AddressInfo, and Invoice table.

I have two solutions for this, but I'm not sure which direction is the right way to go:

Create AddressInfo and make it soft-deletable, adding some column like IsActive or IsDeleted so my Invoice table can reference this address info by it's id and it's guaranteed to remain the same unless someone directly changes this data without using my application.

So when someone changes his address, I soft-delete (perhaps it should be called soft-update?) the current address and add new one without touching old record at all. It will be probably good to check if it's referenced on some invoice first to prevent pollution.

My Invoice table would look somewhat like:

| InvoiceId | AddressInfoId |
Add needed columns from AddressInfo to my Invoice table. I will have to copy the data each time I create new Invoice, addresses don't change frequently that would pollute my database a little bit with duplicated records.

My Invoice table would look somewhat like (greatly simplified):

| InvoiceId | StreetAddress |

Do you have any experience with databases(perhaps of some ERP software) that solve this particular problem?

Interesting reads:

https://martinfowler.com/eaaDev/timeNarrative.html


texts: SQL Server 2014: Graceful shutdown of an Availability Group node

texts:
What I'm trying to do is to gracefully shut down one of two nodes in our SQL Server Availiability Group, effectively draining all the connections.

Previously, we were just stopping the SQL Server instance on the node, but that results in a flurry of "The specified network name is no longer available" errors from client applications.

What is the kosher way of shutting down a node?

I'm no DBA here, so please, ask whatever questions you deem necessary.


texts: Remote MySQL connection fails (10060) - "Can't connect to MySQL server on domainname.com(10060)"

texts:
I have developed an application in VB6, and am using an online MySQL database server for storing my application's data.

The first time I try to connect to my database server is successful, but after closing my application and starting it again I get:

  Can't connect to MySQL server(10060)"

After some time (approximately 30 minutes), I can successfully connect to the database again.

I have given maximum values to all of MySQL system and global variables like connection_timeout, wait_timeout etc.
I have granted all permissions to my MySQL user.
The firewall on my computer is turned off.
All connections are properly closed when the application closes.

I have asked the technical people who provide the Online MySQL Database service and they gave me these three reasons:

The local machine from where you are trying to connect is not whitelisted on the server.
The maximum number of connections for the user has been exceeded.
The MySQL server was down at the time (though they said that their server was not down).

How can I solve the first two reasons, or determine that they are the cause of the problem? Or could there be another problem not listed there?


texts:
i have a problem with my workbench program. I use Mac Lion OS X and mysql workbench program. When i want to do any procedure to alter that doesn't work. Which is show to the procedure code i right click it, after "Alter Procedure" click but doesn't happen anything!

i'm updated to MySQL Workbench program "5.2.45 revision 10251" to  "5.2.47  revision 10398" but both are can not make alter procedure. And one odd thing is alter table is working fine.

Have u got any idea why doesn't work?


texts: How to increase performance with joins to create VIEW from Wordpress wp_usermeta

texts:
After a lot of searching, I finally was able to create a SQL Query to retrive Wordpress User Meta Data. Now the problem is, the performance is so bad it doesn't even show me a result after 30 min.

Wordpress stores data in 3 columns: user_id, meta_key, meta_value.
So each entry gets one row.
What I need is one row per user, with each meta_key as its own column.

In other words, I have this:

user_id | meta_key | meta_value
       1|Name      |David
       1|Lastname  |Brown

But I need this:

user_id|Name |Lastname
      1|David|Brown

My approch on this is:

select m1.user_id, m1.meta_value as Vorname_M, m2.meta_value as Nachname_M,m3.meta_value as Adresse_M
from wp_usermeta m1
join wp_usermeta m2 on (m1.user_id = m2.user_id and m2.meta_key =  'nachname')
join wp_usermeta m3 on (m2.user_id = m3.user_id and m3.meta_key = 'adress')
where m1.meta_key = 'vorname'

With 5 joins it still works, but takes already 4 sec. All in all there are actually 17 joins, and at this point it doesnt show me anything.

I am running the query in phpMyAdmin, and would like to create a VIEW out of the result.

Now the actual question is: How can I increase the perfomance on my query, or how to rewrite it? I am givng my best, but I am actually a starter in SQL.

MySql version: 5.5


texts:
I don't care about the general differences between SQL and NoSQL (or their traditional differences).

I am currently looking at altering the storage of our internal time series. They all contain financial data from a number of different sources. Currently, we are storing our data in a proprietary database. It's very much NoSQL, that has its own query language.

I'm interested in the community input: How would you store the data in a SQL database? What merits are there for using SQL over a NoSQL, specifically for time series? Am I insane for considering storing this in SQL?

Our data set consist of millions of time series, with around 10% of these containing millions of records each.
The time series are organised hierarchically: /Market/Instrument/Value/Frequency, where:

Market is a securities exchange, etc, basically a collection of instruments, usually similar instruments.
Instrument is an instrument. This could be an indicator (Brent Crude), an equity (GOOG), etc
Value is one of multiple types of data for an instrument. This could be a close, high, low, etc
Frequency is the frequency of a particular time series values. Weekly, daily, monthly, tick, arbitrary, etc.

How would the data be stored in a SQL db? One big table (maybe partitioned by something), one table per market or instrument, one table per time series.

Thank you in advance.


texts: How do I add versioning to this CMS I'm designing?

texts:
As you can see, my CMS consists of several widgets (for example an Articles widget), of which you can create several instances, which I call blocks, and those blocks are linked to pages and the containers on those pages. The actual data for a block (e.g. an article instance) is stored in other tables which are not shown here. Containers are set up in a tree-form and every page links to a parent-container and therefore has that container and all of its children. I made it this way, because I'd like to make it very very modular. A bit like Concrete5.

Now I'd like to add versioning to this, so that people can get an old version of their page back if they messed it up. But I have absolutely no idea how to go about it. I've tried to Google for versioning tutorials, but I couldn't find any. Right now the only way I can imagine to do it, would be to make snapshots of pages with all of their associated containers, blocks and widgets and save that as a serialized php-array in a blob in my database. But that just seems like a horrible way to do it.


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

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

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

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

and details are :

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

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

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

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

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

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

Can anyone help?


texts:
I'm trying to design schema for my personal project that would be an online gaming shop. I would like to ask you guys for any suggestions/tips on how could I maybe improve my current draft.

Concerns:

My biggest concern are the Variants. I'm not sure what are the consequences of defining various prices for the same product.
I'm also not sure about optional relation between Variants and Servers, main purpose of it is to provide an option for admin to sell products that will be available on specified Servers of the Game f.e Game Currency.

Info:

Game

Attributes:

name
description

Associations:

many ServerTypes
many Categories

Category

Attributes:

name
description
game_id FK

Associations:

many Products

Product

Attributes:

name
status
description
category_id FK

Associations:

many Variants

Variant

Attributes:

name
stock_quantity
price
server_id FK NULLABLE
product_id FK

Associations:

many OrderItems

Order_Item

Attributes:

price
quantity
variant_id FK
order_id FK

Order

Attributes:

price
status

Associations:

many Order_Items
many Payments

Payment

Attributes:

status
amount
order_id FK

Server

Attributes:

name
server_type_id FK

Associations:

many Variants

Server_Type

Attributes:

name
game_id FK

Associations:

many Servers

Examples of usage:

Admin

Admin can create Game record

Game can contain any number of ServerTypes (eg. EU/NA)

Each ServerType can contain any number of Servers
Each Server can hold any number of Variants

Game can contain any number of Categories (e.g game codes).

Each Category can hold any number of Products (e.g premium voucher).
Each Product can hold any number of Variants (e.g 30 days - 5 usd, 90 days of premium voucher - 15 usd).
Each Variant can (optionally) belongs to Server
Variant can have different price (not sure if it's a good thing)

User

User can make an Order

it has many Order_Items
OrderItem's price = Variant's price * quantity
Order's price will be a sum of OrderItem's price

User can make a Payment

it will belong to specific Order
it has an amount to be paid
it will change it's status over time

Additional info:

I've also made a quick diagram sketch of my schema:


texts:
I am running mariadb 10 and have a large innodb table with a full text index.
I have innodb_file_per_table=ON

I've noticed though that the ibdata1 file grows at the same rate as my table file.

I have tried using mysqldump, removing the files and reloading as well as separating the undo tablespaces. Neither of those made any difference so I assume this is not a transaction overhead of some kind.

Does the ibdata1 file store the actual full text index or is there another explanation for the growth in the file?


texts: double date conversion when adding partition

texts:
I want to create a procedure that adds new partition in a table partitioned by time. For this purpose I use following commands:

    s_until := to_char(sysdate+1,' SYYYY-MM-DD HH24:MI:SS');
    s_statement := 'ALTER TABLE '||s_table||' ADD PARTITION '||s_partition||' VALUES LESS THAN
    (TO_DATE('''||s_until||''', ''SYYYY-MM-DD HH24:MI:SS'', ''NLS_CALENDAR=GREGORIAN''))
    TABLESPACE '||s_tablespace;
    execute immediate s_statement;

As you see I perform unnecessary conversions of date.
Does anybody know how skip this dirty hack?
Performing this way without conversion gives and ORA error.

    s_statement := 'ALTER TABLE '||s_table||' ADD PARTITION '||s_partition||' VALUES LESS THAN
    (sysdate+1) TABLESPACE '||s_tablespace;
    execute immediate s_statement;


texts:
I'm looking for a some guidance/advice on how to best model my data.

Some background: I have location information that I'm trying to model. There are two categories of locations (company and non-company locations), as such, not all locations have the same data elements.

The two tables below represent my current model (without enumerating all columns).

My thoughts on the current design:

If I combine the two tables below I will have to put in null values for company_code, because not all locations have a company_code. I do not want to add nulls values because of the huge number of different data elements between the two different types of locations.
The location_company and location are two important concepts in the company. I would like to have tables in the database two represent these two concepts.

I'm unclear if this is the best approach. Any thoughts on the design would be greatly appreciated. Any additional thoughts are also welcomed.

If my issue is unclear, please ask. I will graciously add clarification. Thanks in advance.


texts: Archive old data in a large table in DB2

texts:
I have a very large non-partitioned table with ~4,500,000,000 records: ODS.LARGE_TABLE.

The table has stored 3 months of data with ~50,000,000 rows per day, therefore resulting total of ~4,500,000,000 records.

My objective is to archive the records in the oldest month (i.e. Sep) to another new table ODS.LARGE_TABLE_old.

1.) My first approach is to load the old records to ODS.LARGE_TABLE_old, then delete them in ODS.LARGE_TABLE. The loading step is fine but the delete step is problematic,
since deleting such many records in this large table will cause transaction log full error.

I knew that one can alter table to not logged initially to avoid this problem, but it seems to be risky in my situation since any interruption of the delete operation will make the table inaccessible and I dont have any backup copy of this large table.

2.) My second approach may sound stupid but it does not involve of delete operation. I first rename ODS.LARGE_TABLE to ODS.LARGE_TABLE_tmp,
then create 2 new tables: ODS.LARGE_TABLE and ODS_LARGE_TABLE_old, I load the old records to ODS.LARGE_TABLE_old from ODS.LARGE_TABLE_tmp; load the rest of the records (i.e. Oct & Nov) to ODS.LARGE_TABLE from ODS.LARGE_TABLE_tmp, finally, I drop the table ODS.LARGE_TABLE_tmp.

I think the drawback with this approach is that the 2nd load operation is taking me so much time, since it involves of loading data with 2 months.

My question is that are there any best practices/ways to archive old records from a large table in DB2 LUW? I am finding an efficient way to do so such that I can apply the same approach in all of my large tables.

Thanks in advance.


texts: Track write requests on Read-only database

texts:
I have changed one of the databases to read-only in SQL Server 2005. I would like to track if someone tries to write to the database.

Any suggestions on how to track such requests (end user write request failures)?


texts: Does READ COMMITTED always start over after serialization failures while SERIALIZABLE simply fails?

texts:
On the PostgreSQL Concurrency With MVCC page, it says:

  know what you’re thinking though: what about a two transactions updating the same row at the same time? This is where transaction isolation levels come in. Postgres basically supports two models that allow you to control how this situation should be handled. The default, READ COMMITTED, reads the row after the inital transaction has completed and then executes the statement. It basically starts over if the row changed while it was waiting. For instance, if you issue an UPDATE with a WHERE clause, the WHERE clause will rerun after the initial transaction commits, and the UPDATE takes place if the WHERE clause is still satisfied.

The docs seem to suggest that READ COMMITTED is still subject to failures and should be retried.

Can READ COMMITTED be set to indefinitely retry with the same atomicity as SERIAZLIZABLE?


texts: Why Won't a Database Backup if the Log File is Full?

texts:
I've got a simple backup script for a database in the full recovery model on SQL Server 2008 R2:

backup database livendb to disk = '\\ehsjmaydb01\Data\livendb.bak' with init, format
backup log livendb to disk =  '\\ehsjmaydb01\Data\livendb_log.tran' with init, format

We turned on a new ETL script over the weekend which absolutely hosed my poor server. The transaction log filled up and TempDB filled up.

Looking at the history of my backup job, it failed with this message:

  The transaction log for database 'livendb' is full.
  To find out why space in the log cannot be reused, see the log_reuse_wait_desc column in sys.databases
  [SQLSTATE 42000] (Error 9002)  BACKUP DATABASE is terminating abnormally.
  [SQLSTATE 42000] (Error 3013).  The step failed.

When I came in this morning, I was able to back up the log first, then run a DB backup, and that's working just fine.

Question: Why does the database backup process require space in the log file?


texts: indexable JSON datatype in MySQL/MariaDB

texts:
In PostgreSQL 9.4 there is a JSONB datatype which allows you to index on fields in the stored JSON objects. Does MySQL/MariaDB have an equivalent?

I'm specifically interested in the MariaDB Galera cluster, but am open to any MySQL-family answers.


texts: Web Database - How to store user information

texts: I'm starting to design a database to use in a web solution and would like to understand if there is any benefit to storing passwords (and the rest of the user information for that matter) in a separate table to the table with the user id.


texts: How do you encrypt data while performing a mysqldump?

texts:
My OS is windows 7.

How can I encrypt data while performing a mysqldump?
From the command prompt, can I take a mysqldump with encrypted varchar and texts values?

Please help me.

Thanks


texts: Auto-increment ids with multiple inserts in mysql/mariadb

texts:
Suppose I have a table with an auto_increment column, and then insert multiple values in a single statement:

insert into foo (bar) values ('a'), ('b'), ('c');

From the documentation, last_insert_id() returns the first generated ID for the query, the ID for a in this case.

Does mysql give any guarantees that a b and c will then be assigned sequential values?

In other words, if last_insert_id() returns 1 for a, is there any guarantee that b = 2 and c = 3? Or could there be a scenario where due to multiple concurrent inserts, I could end up with something like "a = 1, b = 5, c = 9"?


texts: What is the best query to use to monitor a SQL Server database's status?

texts:
I want to be able to run a query to get the crucial information about a database's status. I.e., I want the query to be able to tell what whether or not the database is in a good state.

This is the query that I inherited for this check:

SELECT
    name AS [SuspectDB],
    DATABASEPROPERTY(name, N'IsSuspect') AS [Suspect],
    DATABASEPROPERTY(name, N'IsOffline') AS [Offline],
    DATABASEPROPERTY(name, N'IsEmergencyMode') AS [Emergency],
    has_dbaccess(name) AS [HasDBAccess]
FROM sysdatabases
WHERE (DATABASEPROPERTY(name, N'IsSuspect') = 1)
    OR (DATABASEPROPERTY(name, N'IsOffline') = 1)
    OR (DATABASEPROPERTY(name, N'IsEmergencyMode') = 1)
    OR (has_dbaccess(name) = 0)

If that query returns any results, the assumption being made is that the database is in a suspect or potentially bad state.

Is there a better way to do this?


texts: What is the SQL Server equivalent of pg_typeof(), how can I get the type of a column returned in a result set?

texts:
Using PostgreSQL, I can use pg_typeof(exp) like
SELECT pg_typeof( cast(1 AS int) );
SELECT pg_typeof(x), x FROM foo;

And get back the result (integer in the case of the first example). Is there a SQL Server equivalent? How can I find the result given a field in a result set?
SELECT x
FROM foo;

I want to know what type x is?


texts:
What is your method of implementing soft deletes in a database?

Ideally the solution would allow for

decent performance on large tables
take relationships into account
take unique keys into account
the user should be able to find and restore his deleted items.

Thanks!


texts: SSIS ODBC: Mapping result set columns to variables returns error "Value does not fall within expected range"

texts:
In SSIS 2012, I'm using an "Execute SQL Task" to call a stored procedure that returns a single row with two columns.  I'd like to map that result set to two SSIS user variables.  Should be easy, right?  This is works when I use an ADO.NET or OLEDB Connection Manager, but fails when I used an ODBC Connection Manager.

When I map using ODBC, SSIS returns the following runtime exception:

  [Execute SQL Task] Error: An error occurred while assigning a value to variable "MyString": "Value does not fall within the expected range."

The SQL Server columns have a data type of nvarchar(50).  I've also tried varchar(50) and char(1) to no avail.  The SSIS variable "MyString" has a data type of String.

.

The ODBC connection's Code Page is 1252 and the SQL Server 2012 collation is SQL_Latin1_General_CP1_CI_AS.

I'm using a SQL Server 2012 database.

Here's the general setup of the task for reference:

.

Is this possible to do using an ODBC connection?  Or is this yet another type of functionality that just isn't supported for ODBC in SSIS 2012?


texts: is it possible to set engine for database (not the tables)?

texts:
There are about 250 tables in my database and they all have InnoDB engine. I imported them into a server.

They were imported and were displayed in the tree structure of phpmyadmin (under the database name), but when I clicked on them MySQL reported the error that table doesn't exist!!

From this link ,I find out that database engine and tables engine are not the same. is it possible to set engine for database itself (not the tables)? if yes how ?

update :

I also tried to create tables manually (by SQL Query and InnoDB engine) but reported the error that can't create table, I changed InnoDB to MyISAM and problem solved!


texts: Error Msg 823, Level 24, State 2, Line 1

texts:
We have been recieving the error below in one of our server during restoring a database. Has anyone experienced the same problem? If yes can you please share how you fixed it?

The operating system returned error 38(Reached the end of the file.) to SQL Server during a read at offset 0x000001f3400000 in file 'H:\Data\XXXX_data.mdf:MSSQL_DBCC17'. Additional messages in the SQL Server error log and system event log may provide more detail. This is a severe system-level error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online


texts: How do you create a new database in SQL Developer?

texts:
I  am a software developer that supports and develops an application that connects to both Oracle databases and SQL Server databases.  I want to learn more about database administration.

In SQL Studio Manager you can open a connection to a server and then selected the master database and then run a statement like this:

CREATE Database TestDatabase

TestDatabase is then available to connect to on that database server.

However, with SQL Developer you have to connect to a database already created (rather than connecting to the server).  Therefore how do you create a new database in SQL Developer?

I thought I would be able to find my answer by Googling.  However, this was not the case.  There seem to be hundreds of tutorials about creating tables but I cannot find any about creating a database.


texts: PostgreSQL - INSERT if it doesn't exist/RETURN if it does (query optimization)

texts:
This query will insert a new IP if there isn't one and return it's ID. And if there already is the same value it only returns the ID.
I'm a newb with SQL and am curious if this could be further optimized?
WITH v AS (
   SELECT '1.1.1.110'::inet AS ip
), i AS (
    INSERT INTO globals.ips (ip)
    SELECT * FROM v
    WHERE NOT EXISTS (
        SELECT i.global_ip_id
        FROM globals.ips AS i
        WHERE i.ip = v.ip
    )
    RETURNING global_ip_id
)
SELECT global_ip_id FROM i
UNION ALL
SELECT global_ip_id FROM globals.ips g
LEFT JOIN v ON g.ip = v.ip
WHERE g.ip = v.ip
LIMIT 1;

I am running PostgreSQL 12.


texts: Does the definition of 2NF prohibit _transitive_ dependencies on a partial key or only direct ones?

texts:
We know a relation is in 2NF iff it is in 1NF and no non-prime attribute is dependent on a proper subset of the candidate key. But is "dependent" in this definition strict or loose in the following sense:

Given the set of dependencies

A -> B
B -> C

Is the relation

([D],[A],C)

(i.e., the candidate key is {D,A}) in 2NF?

The reason I ask is that there are no direct dependencies from the given set of dependencies with a proper subset of {D,A} on the left-hand side, but the transitive dependency A -> C does violate 2NF. So my question is:

In the definition of 2NF, are the "disallowed" dependencies direct ones only or do transitive dependencies "count" as violations? I think transitive dependencies do count (and that the relation above is not in 2NF), but in the sequence of 1NF, 2NF, 3NF, etc., as usually presented in the academic literature, transitive dependencies are typically only "removed" at the 3NF stage.


texts: How to model local exceptions to distributed tables

texts:
I am looking for possible ways to model a distributed table(s) and provide local exceptions to that table.  Consider a situation where head office publishes a list of products/prices that is distributed to branches and they load this price list into their local database.  Now sometimes these branches want to change the head office price to a local price.  Clearly we can simply update the price in the local database, but then it gets lost when the next head office update is applied.  We also only want to block that single column(s), not changes to other fields in the row.
EG.  Head office send
| Name | Price |
|------|-------|
| ABC  | $5    |
| DEF  | $8    |

In one database (out of say 100) we want ABC to have a price of $3.  And this $3 should continue to apply even as future updates from head office are received.  Head office are not aware of this local exeception.
In the past we've done this in two ways.  The first was to maintain a second LocalProducts table with the changes to be applied - then when the head office version was applied, any local changes would then be read from LocalProducts and replace the distributed value.
This works OK, but suffers as the table sizes go up.
The second method we tried was to duplicate the column and use coalesce
eg  rather than select price where name='abc' we would use select coalesce(localprice,price) where name='abc'  This works ok, but had the overhead that we had to change every SQL and remember to do it on future development too.
Are there any other better methods?  I am looking for a database agnostic solution, the target databases are ODBC (90% sql/server), SQL-Lite or MS-Access.   Access can be ignored if it helps, it is being actively phased out.  Engine specific solutions are ok, we can detect the engine and use a different technique, but would prefer not to have a different technique for each engine.
Other notes

The data is distributed by the application, not built in replication.  This means we can have code/logic during the loading phase
Multiple tables are require this feature, not just one as the example above
Some of the tables have a reasonable number of columns that could be involved, but typically not.
Most of the tables aren't large, perhaps 50M rows in the biggest, and only 7 rows in the smallest case.

(please feel free to retag this question - not exactly sure which tags are relevant.  And yes I have spent many hours over the years investigating this)


texts: Cannot connect to a SQL Server 2014 Express database intermittently

texts:
I have problems connecting computers running Windows 7 to a database SQL Server 2014 Express intermittently.

It happens that, intermittently, I can not connect to the database that is on a Windows 2008 R2 server.

If I restart the computer, works again, but only for a few minutes, sometimes hours.

The Firewall of my computer is turned off. Windows Firewall is turned off.

CONFIGURATION:

The server is Windows Server 2008 R2 Foundation
Firewall Ports TCP / IP and UDP 1434 1433 allowed

SQL Server Express:

Unlimited Connections
Allows remote connections
Timeout remotely query: 600 seconds

Machines with Windows XP have no problem with this issue.


texts:
I have a date field in my datafile which I am trying to load in MySQL, but the date field is in dd-mon-yy format and MySQL only understands YYYY-MM-DD.

Is there a way by which I can load DD-MON-YY data into YYYY-MM-DD format using MySQL LOAD or any other native utility?


texts:
Can I add domain users to a database, when Sql Server A (the development server) is not part of the domain and has absolutely no access to the domain?

The domain users are of course not expected to work on server A, but these users are already configured on Sql Server B, the production sql server.

When a backup is taken on the development server A and deployed on the production server B, currently these users have to be re-added to the database on server B.

(The users do not have sysadmin rights on server B. As far as I know, if they had sysadmin rights, they would automatically have rights to access any new databases anyway)

Is it somehow possible to CREATE USER domain\xy on development server A, so that they will work when restoring a backup on the production server B?

Or as a workaround, could I take a blank database backup with only the users from server B, and then use that backup as a base to create new databases on server A?


texts:
I have a 600GB database that I must transfer from an on-premises machine to an on Azure VM machine (IaaS)
I would like to use the dbatools copy-database tool for this
for this I will have to know what will be the best storage method that allows me to have a fast transfer

local backup then restore on the Azure machine
remote backups directly on the machine in azure
a backup and a restore from storage azure blob


texts: Reporting Services DBs not appearing in Management Studio, but can be queried?

texts:
I've set up a server with native reporting services, connected to a database server which hosts its two databases. I'm trying to run a backup of the two databases so I can add them to my availability group, but neither of them are showing up in the "Databases" folder in the object explorer so I can't get into their properties or run tasks on them. Is there a way to show them, or is there another way to run tasks on a database?

They are both showing up in the availability group wizard and I can run queries on them (although without Intellisense) so I'm definitely connected to both of them.


texts: I dropped a table but didn't purge it. How can I purge it, so I can release the tablespace that the table uses?


texts: Different results from select and from materialized view

texts:
select distinct on (p) coalesce(native_fcm_token, push_token) p
from session
where user_id = 2779
and ((push_token is not null) or (native_fcm_token is not null))

returns 27 rows

refresh materialized view session_push_token
select * from session_push_token where user_id = 2779

returns 22 rows

session_push_token is defined as

create materialized view session_push_token as
SELECT DISTINCT ON (COALESCE(s.native_fcm_token, s.push_token)) COALESCE(s.native_fcm_token, s.push_token) AS push_token,
                                                                s.os,
                                                                s.user_id
FROM session s
WHERE ((s.push_token IS NOT NULL) OR (s.native_fcm_token IS NOT NULL));

How this can happend?
Postgresql 11 from docker image


texts:
SELECT DISTINCT cname
FROM account AS cust_account
WHERE NOT EXISTS ( SELECT type
                   FROM account
                   EXCEPT
                   SELECT type
                   FROM account
                   WHERE account.cname = cust_account.cname )

Can someone please explain step-by-step how the query produces the result required by the question? I am just learning about NOT EXISTS and am not sure what values it takes from the parenthesis and where the WHERE clause belongs with.

This is sample table account


texts: Is there any rule of thumb to optimize sql queries like this

texts:
this is my first question here. Although I've been helped out from this forum over a hundred times.
I was having difficulties in optimizing sql query. It takes hours to execute. Record set is also large enough. The query was not written by me. So just to find the bottle neck I tried removing conditional clauses but that doesn't make any difference. Indexing the ID's done.

Can any sql guru here could throw some light on it. Is there any fine tuning room left in the query below? The server hosting the database in DB2. I'm not too pro in sql.
Thanks as always.
Regards, Nuh

This is the query:

SELECT
HEALTH_INSURANCE.RISK_DETAIL_ID ,
POLICY_RISK_COVER.RISK_COVER_ID ,
HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO ,
RD_POLICY_END_NO_IDX ,
HEALTH_INSURANCE.RD_POLICY_ID ,
HEALTH_INSURANCE.RD_LEVEL1_ID ,
HEALTH_INSURANCE.RD_SUM_INSURED_AMT_LC ,
HEALTH_INSURANCE.RD_PREMIUM_AMT_LC ,
POLICY_RISK_COVER.PREMIUM_AMOUNT_FC ,
POLICY_RISK_COVER.SUM_INSURED_AMT_FC ,
HEALTH_INSURANCE.RD_REC_TYPE ,
HEALTH_INSURANCE.RD_EFFECT_FROM_DT ,
HEALTH_INSURANCE.RD_EFFECT_TO_DT ,
HEALTH_INSURANCE.RD_END_EFFECT_FROM_DT ,
HEALTH_INSURANCE.SEX_MAS_CD ,
HEALTH_INSURANCE.MARITAL_STATUS_CD ,
HEALTH_INSURANCE.EMP_CATG ,
HEALTH_INSURANCE.NO_OF_DEPENDENTS ,
CAST((
CASE
    WHEN HEALTH_INSURANCE.AUTHORITY_LETTER_NO IS NULL
    THEN HEALTH_INSURANCE.EMP_AL_NO
    ELSE HEALTH_INSURANCE.AUTHORITY_LETTER_NO
END) AS INT) AS EMP_AL_NO ,
HEALTH_INSURANCE.DOB ,
HEALTH_INSURANCE.EFF_DATE ,
HEALTH_INSURANCE.EFF_DATE2 ,
HEALTH_INSURANCE.NAME ,
CAST((SUBSTR(HEALTH_INSURANCE.RELATIONSHIP_CD, 5,2)) AS INT) AS
RELATIONSHIP_CD_S ,
HEALTH_INSURANCE.RELATIONSHIP_CD ,
HEALTH_INSURANCE.DESIGNATION ,
HEALTH_INSURANCE.BRANCH ,
HEALTH_INSURANCE.BANK_ACCOUNT ,
HEALTH_INSURANCE.BANK_BRANCH_NAME ,
HEALTH_INSURANCE.PRE_EXISTING_AILMENT ,
HEALTH_INSURANCE.AUTHORITY_LETTER_NO ,
HEALTH_INSURANCE.AGE ,
HEALTH_INSURANCE.REGION ,
HEALTH_INSURANCE.CNIC ,
HEALTH_INSURANCE.CO_CODE ,
HEALTH_INSURANCE.EMP_LOCATION ,
HEALTH_INSURANCE.SUB_LOCATION ,
CATEGORY_LIMIT_HEADER.CLH_SYSTEM_NO ,
CATEGORY_LIMIT_HEADER.CTH_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_POL_SYS_ID ,
CATEGORY_LIMIT_HEADER.CTH_END_NO_IDX ,
CATEGORY_LIMIT_HEADER.CTH_END_SR_NO ,
CATEGORY_LIMIT_HEADER.CTH_CATEGORY ,
CATEGORY_LIMIT_DETAIL.CLD_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLDH_SYS_ID ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_CD ,
CATEGORY_LIMIT_DETAIL.CLD_END_IDX ,
CATEGORY_LIMIT_DETAIL.CLD_COVER_DESC ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_TYPE_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_REL ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_FROM ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO ,
CATEGORY_LIMIT_DETAIL.CLD_CLM_RB_LIMIT ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_LIMIT_FC ,
CATEGORY_LIMIT_DETAIL.CLD_CATEGORY_PREM_FC
FROM
DB2ADMIN.HEALTH_INSURANCE AS HEALTH_INSURANCE
    INNER JOIN DB2ADMIN.POLICY_RISK_COVER AS POLICY_RISK_COVER
    ON HEALTH_INSURANCE.RD_POLICY_SYSTEM_NO = POLICY_RISK_COVER.
    RC_POLICY_SYSTEM_NO AND
    TRIM(RD_LEVEL1_ID) = TRIM(RC_LEVEL1_ID)
        INNER JOIN DB2ADMIN.CATEGORY_LIMIT_HEADER AS CATEGORY_LIMIT_HEADER
        ON HEALTH_INSURANCE.RD_POLICY_ID = CATEGORY_LIMIT_HEADER.
        CTH_POL_SYS_ID AND
        HEALTH_INSURANCE.EMP_CATG = CATEGORY_LIMIT_HEADER.CTH_CATEGORY
            INNER JOIN DB2ADMIN.CATEGORY_LIMIT_DETAIL AS
            CATEGORY_LIMIT_DETAIL
            ON CATEGORY_LIMIT_HEADER.CTH_SYS_ID = CATEGORY_LIMIT_DETAIL.
            CLDH_SYS_ID AND
            POLICY_RISK_COVER.RISK_COVER_CD = CATEGORY_LIMIT_DETAIL.
            CLD_COVER_CD AND
            HEALTH_INSURANCE.RELATIONSHIP_CD = CATEGORY_LIMIT_DETAIL.
            CLD_CLM_REL
WHERE
COALESCE(HEALTH_INSURANCE.AGE, 1) BETWEEN CATEGORY_LIMIT_DETAIL.
CLD_CLM_AGE_FROM AND
CATEGORY_LIMIT_DETAIL.CLD_CLM_AGE_TO


texts: I can't deploy SSRS project due to timeout for a shared dataset

texts:
I can deploy my SSRS project to SSRS on one computer, but not on my laptop.
It belongs to an ASP.NET 5 solution.
The SSRS projects uses a shared datasource which connects to an SSAS instance(correctly deployed and processed).
My SSRS project contains shared datasources, shared datasets, usual datasources and datasets, and reports.
When I deploy my SSRS project, 2 shared datasources are correctly deployed but the first shared dataset deployment fails due to a timeout error. The dataset can be edited in VS and I can see in visual studio its results.
I would like to add that the computer is a laptop so it can be slower than my desktop. I tried to reinstall SQL server and SSRS, but the problem remains the same.
I tried to raise the timeout but maybe I did not change the correct parameter(in SSRS portal) but I saw no difference in terms of timeout time.
Also, when I launch the SSRS portal in a browser, a login/password is asked (message:"the connection is not secure. Please enter a login/password"). I don't see what to enter, because my windows login/password is not accepted. If I close the popup, the page continues to be editable and accepts changes and after some time, it appears again.
Could you please give me hints, reasons why I can't deploy my project?

Here is the problem:

it says the request has failed due to timeout.
and the connection to the report server asks me login/password:

It says the connection is not private.
I must add I tried to add Basic Authentication (RSWindowsBasic) some hours before, but I removed it and a login/password is always asked.
I tried to disable my antivirus and my firewall, without success.


texts: SQL Server Management Studio - Cannot connect - Method not found SqlConnectionInfo.set_IsActiveDirectoryUniversalAuth

texts:
I have a fresh installation of Windows 10, on which Microsoft Visual Studio 2015 was installed (therefore, a default database was installed too). I installed SQL Server Management Studio 2016 (v13.0.16106.4) but could not connect with the following error message:

Cannot connect to MYSERVER\SQLEXPRESS.
Method not found: 'Void Microsoft.SqlServer.Management.Common.SqlConnectionInfo.set_IsActiveDirectoryUniversalAuth(Boolean)'. (ConnectionDlg)

I've since tried to:

Install SQL Server 2016
Uninstall both SQL Server 2016 and the one coming with Visual Studio 2015 (following http://intellitect.com/sql-server-2008-install-nightmare/)
Once everything was cleaned and after a reboot, install SQL Server 2016 with default settings
Repair Microsoft SQL Server Management Studio

Note that regardless of what I try to connect to (including typing junk in the server text box), I have the same message, which leads me to believe that the error happens before SSMS actually tries to connect to the server.
I'm out of ideas; it seems like a DLL file may be missing, but which one and why, I cannot say.
The only "useful" reference I have found is this technet forum post which refers to a "patch", but I can't find where this "patch" could be found, if it exists.
I can connect with sqlcmd -S mymachine\SQLEXPRESS; I can also run queries like SELECT * from sys.databases successfully.


texts: Change the owner of an offline database without bringing it online

texts:
If it is possible I would like to know how to do it via Sql Server Management Studio.

When a database is online I can do it in the files option of the db properties. I don't see that option when the database is offline.

I'm using SQL Server 2008 R2.


texts: Table design - parent with always exactly 1 or 2 children

texts:
Lets say I have the following existing tables (both with a primary key column named Id):
TableA
TableB

Now, I need to create a new table that groups together 1 record from TableA with either 1 or 2 records from TableB.  The relationship here is always either 1 or 2 records and will never change, but the order of these 2 relations does matter.  I'm calling this new table Groups but I can't decide on the proper way to design it.
Option 1 - flat
Groups
----------------------------
Id INT NOT NULL PRIMARY KEY,
TableAId INT NOT NULL,
TableBId1 INT NOT NULL,
TableBId2 INT NULL

Option 2 - details table
Groups                         GroupDetails
----------------------------   --------------
Id INT NOT NULL PRIMARY KEY,   GroupId INT NOT NULL,
TableAId INT NOT NULL          TableBId INT NOT NULL,
                               Sort TINYINT NOT NULL

Option 1 will make it easier to retrieve records since it reduces a join, but the column TableBId2 could potentially be null in some cases.
Option 2 will require a join, but it's more normalized and just seems like the "proper" way to design this table.
However, in this case I'm leaning towards breaking the normalization principles in favor of a slight optimization, because like I said, each Group record will always have at least 1 relation to TableB, and at most, a 2nd relation.  I'm struggling to make a decision on this, so I'm hoping someone can give their opinion on it.


texts:
I have a database on a hosting service that was recently move and upgraded without much notice which has caused me many problems. This problem, however, seems to arise from an upgrade from MySQL to MariaDB 5.5.

I have a table called “Gigs” which stores date, time, Location, description etc. of some upcoming gigs/concerts. It’s an old database I created in my youth, and so time is implemented as a string field.

The PHP code of the website generates a time string using a colon to separate hours and minutes, for example 16:40, it then generates an INSERT query like this example:

INSERT INTO Gigs VALUES (NULL, ‘16:40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)

I had no problems with this query until the migration and upgrade of the hosting.

My PHP code was silently failing, so I attempted to execute this SQL on the database directly using phpMyAdmin, and this is the message I got back:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near ':40’, ‘Test’, ‘Location’, NULL, ‘2019-01-09’)' at line 1

It seems as though the colon is causing an issue? Is anybody able to help? I cannot find mention of colon as a special character in MariaDB documentation, nor any way to escape it.


texts: PostgreSQL migrating from 9.2 to 9.5 with data dir on different volume

texts:
We have a problem in upgrading postgresql 9.2 cluster to 9.5 using pg_upgradecluster, because the data directory is on a different volume from the root one.

At the beginning, we just tried:

# pg_upgradecluster 9.2 main --method=upgrade --link

But it failed because our data directory for 9.2 is on a different volume mounted on /data:

  Could not create hard link between old and new data directories:
  Invalid cross-device link In link mode the old and new data
  directories must be on the same file system volume.

Later we found out that we could tell the upgrader to put the new version on the same volume like this:

# pg_upgradecluster 9.2 main /data/postgresql/9.5/main --method=upgrade --link

This worked on a test installation with the same volumes and directory structure, but it failed when we tried it again in production. This was the output :

  root@myserver ~ # pg_upgradecluster 9.2 main /data/postgresql/9.5/main
  --method=upgrade --link

  Disabling connections to the old cluster during upgrade...

  Restarting old cluster with restricted connections...

  Stopping old cluster... Creating new cluster 9.5/main
  ...   config /etc/postgresql/9.5/main   data

  /data/postgresql/9.5/main   locale en_US.UTF-8   port   5433

  Error:
  could not symlink configuration file

  Error during cluster dumping,
  removing new cluster

Searching around it looks like maybe the first attempt at upgrading created these symlinks:

  lrwxrwxrwx  1 postgres postgres   36 Apr 17 08:32 pg_hba.conf ->
  /etc/postgresql/9.2/main/pg_hba.conf lrwxrwxrwx  1 postgres postgres
    38 Apr 17 08:32 pg_ident.conf ->
  /etc/postgresql/9.2/main/pg_ident.conf lrwxrwxrwx  1 postgres postgres
    40 Apr 17 08:32 postgresql.conf ->
  /etc/postgresql/9.2/main/postgresql.conf

We also found a reference (http://postgresql.nabble.com/Problem-pg-upgradecluster-from-9-1-to-9-3-tp5829359p5829495.html) that suggests it could be an issue with an older version of postgresql-common we have installed - this is the full list of our packages:

  root@myserver ~ # dpkg -l | grep postgres ii  pgdg-keyring
    2014.1                          all         keyring for apt.postgresql.org rc  postgres-xc                          1.1-2ubuntu2
  amd64     write-scalable, synchronous multi-master, transparent
  PostgreSQL cluster ii  postgresql-9.2
    9.2.9-1.pgdg14.04+1             amd64       object-relational SQL database, version 9.2 server ii  postgresql-9.5
    9.5.2-1.pgdg14.04+1             amd64       object-relational SQL database, version 9.5 server ii  postgresql-client-9.2
    9.2.9-1.pgdg14.04+1             amd64       front-end programs for PostgreSQL 9.2 ii  postgresql-client-9.5
    9.5.2-1.pgdg14.04+1             amd64       front-end programs for PostgreSQL 9.5 ii  postgresql-client-common          154.pgdg14.04+1
  all       manager for multiple PostgreSQL client versions ii
  postgresql-common                 154.pgdg14.04+1                 all
    PostgreSQL database-cluster manager ii  postgresql-contrib-9.5
    9.5.2-1.pgdg14.04+1             amd64       additional facilities for PostgreSQL ii  postgresql-server-dev-9.2          9.2.14-1.pgdg14.04+1
  amd64     development files for PostgreSQL 9.2 server-side programming
  ii  postgresql-server-dev-9.5         9.5.2-1.pgdg14.04+1
  amd64     development files for PostgreSQL 9.5 server-side programming

So the question is:

Should we upgrade postgresql-common using

# sudo apt-get install postgresql-common=173.pgdg15.10+1

? Is it safe even if we still have 9.2 running?

Should we also manually delete the symlinks

  pg_hba.conf -> /etc/postgresql/9.2/main/pg_hba.conf pg_ident.conf ->
  /etc/postgresql/9.2/main/pg_ident.conf postgresql.conf ->
  /etc/postgresql/9.2/main/postgresql.conf

Or the latest version of postgresql-common should be able to proceed without that?

Thanks!


texts: Database schema initially not designed for scaling, how to optimize it?

texts:
I developped 2 years ago a back office for helping a logistic company to manage the deliveries.
At this time, the company only had one "partner" (let's call it Partner A).

The main table is delivery_note.

I tried to design the database to be the more generic as possible.
So I decided to put all the partner related fields in a dedicated table.

But two years later, we have 5 partners (and more are incoming) and I don't like to add a new FK in delivery_note each time we add a new partner.

The relation between a delivery_note and a partner is 1 1 (optional). In practice a delivery_note can be linked to only one partner.

The schema looks like:

Do you know any pattern or way to design a more flexible schema to handle this case ?

Thank you


texts: Unexpected results with soundex() function when combined st_distance()

texts:
select soundex(A.name),
       A.gid,
       A.name,
       soundex(B.name),
       B.gid,
       B.name,
       st_distance(B.the_geom,A.the_geom) * 111.325 as dist
from (select name,
             gid,
             the_geom,
             count(soundex(name)) over (partition by soundex(name)) as cnt
      from poi_new A) A
join poi_new B on st_distance(B.the_geom, A.the_geom) * 111.325 < .2
where cnt > 1
  and A.gid <> B.gid
  and A.name = B.name
group by A.gid, A.name, B.gid, B.name, dist, soundex(A.name), soundex(B.name);

Soundex value is same and also the names are the same, but it doesn't return any similar names when using this query.

I want to retrieve the names which are located in a particular distance, so I used st_distance() here, then I tried with another query using window function. I am getting the similar names with same soundex value.

The output which shows similar names with same soundex value, I want to get the results like this with respect to the st_distance function which I used in the query.

Where do I have to change the code? Is the window function which I used is correct or not?


texts:
I am trying to trend some system performance over time, but I've hit a couple of stumbling blocks with sys.dm_exec_procedure_stats that I can't reconcile.

If I take sys.dm_exec_procedure_stats and join it to sys.dm_exec_query_stats matching on plan_Handle, I am getting some data that doesn't make sense to me.

Execution_Count - Sometimes this matches very well between the two and other times it is off by tens of thousands of executions. Looking at procedure stats I have one procedure that has 771 executions.   It has 4 matching query plans, all of which show 1 execution_count.

Total_Worker_Time - Procedure_Stats is reporting 2x-3x what summing the query_plans do.  295,977 versus 130,212.

Last_Worker_Time - Procedure_Stats versus sum of query_stats matches almost exactly.

Min_Worker_Time - Procedure_Stats versus a min of query_stats is close

Max_Worker_Time - Procedure_Stats versus a sum of query_stats is close

Total_Elapsed_Time - Procedure_Stats versus a sum of query_stats is off.  Procedure_Stats is 7x longer

Last_Elapsed_Time - Procedure_Stats versus a sum of query_stats is very close

min_Elapsed_time - Procedure_Stats versus a min of query_stats is close.

Max_Elapsed_Time - Procedure_Stats versus a sum of query_stats is off.  Procedure_Status is 4x longer.

I find it odd that some of these match up and other do not, but the mismatches here make me distrust this data in general.   I'm most interested in the total times, the execution_counts (to get averages) and the min/max times.

Could anyone explain why these mismatch or which is best to use and how do I know I can trust it?  Does the max time and total time in procedure_stats include client wait times or something else that skews these?


texts: SQL Server 2012 : how to add missing collation

texts:
When we run:

ALTER DATABASE abc COLLATE Latin1_General_CP1_CI_AS ;
GO

we receive:

  Msg 448, Level 16, State 3, Line 1
  Invalid collation 'Latin1_General_CP1_CI_AS'.

How can we add the missing collation?


texts:
I'm having big troubles on a database pool I've setup recently.

There is a master, and 4 slaves replicating one DB. All using MyISAM engine.

Thing is, during the replication period (when the slave is not up to date), the IO% showing in iotop is equal to 99% for mysqld.

Besides, I'm having a big latency when connecting a web application to any of these databases (master included).

All these servers are virtual machines running Ubuntu 12.04, having 4vcpu each and 12GB RAM.

If you need more information about the config (system/mysql), please tell me, I'll update my post.

Thing is, I need some hint about where to search, because for now I was not able to identify the problem.

EDIT:

I'm adding the conf files for the master and for a replica :

master configuration file

replica configuration file


texts:
I have done backup plan to backup 1 DB . When  look in the log i can see that the system has taken backup of all databases (master , reportserver ...etc) . However on the drive where data are backed up , i can see only my DB for which i have done the bakup plan .

Questions:

1) why i am seeing in the log that there were many DB backed up .

2) If 1) is true where are these backups ?

Any comment will help.


texts:
I have a table with a timestamp field. The table is loaded with rows which have ascending order of this field (time increases). I tried the following query, prior to establishing an index (there are 28000 rows):

SELECT *
FROM timetest
WHERE epoch > '2015-12-14 12:08:50' and epoch < '2015-12-14 12:08:58'
LIMIT 1;

Execution time was 11-12ms.

I then added an index using:

CREATE INDEX idx_time_epoch ON timetest(epoch);

The same upper query took exactly the same time as before I added an index.

Is it because there are too few records in the database for the gain to show?
Is it because epoch field is sorted to begin with and as such the default indexless query runs in somewhat the same way as the indexed query?

Update, here is the output from EXPLAIN:

Limit  (cost=0.28..8.30 rows=1 width=73)
    ->  Index Scan using idx_timetest_epoch on traj_point  (cost=0.28..8.30 rows=1 width=73)
        Index Cond: ((epoch > '2015-12-14 12:08:50'::timestamp without time zone) AND (epoch < '2015-12-14 12:08:58'::timestamp without time zone))


texts:
I have a transactional Replication (Non-SQL Server Publishers - Oracle) implemented for one SQL Server Database. Let's say Publishers: A, B
Subscriber: C
Note:

I can not modify the Oracle DBs.
The Publishers have the same DB schema.
On the subscriber I have the same tables, with one column more "Publisher" to identify where that line comes from.

I used the After-Snapshot Script to create and populate this column for the Initial Load for the Publisher A.
Also, created a (After-Snapshot) script for the publisher B to populate the column.
It works when I do the first snapshot for A, and my bulkload fails for B with the following error:

Replication-Replication Distribution Subsystem: agent AgentName failed. The process could not bulk copy into table '"dbo"."EMployees"
Error Log: Error: 14151, Severity: 18, State: 1.
Replication Monitor Source: MSSQL_REPL, Error number: MSSQL_REPL20037)

Anyone has an idea? Is it due the schema difference between B and C, since when the A does the bulkload there is no table and first I create the table, then do the initial load, the do the alter table to add the column, update values to be ready for the second bulk load?


texts: Create unique DB_NAME and DB_DOMAIN for an Oracle 11g instance

texts:
I am somewhat confused by these terms in the init.ora file:

db_domain
db_name
instance_name

plus the database name which is provided to the create database command. I do not completely understand how they sum up. I have read the Oracle docs several times but still failed to understand.
As far as I understand, a database can be mounted and used by several instances in a RAC which I do not use. This means that db_name has to be the same as in create database?!

This is my setup:

I have 2 machines host1, host2. Both are in the same subdomain dom1.company.net. Both have an instance which hosts the same database but one in for the production system and the other one is the test system.

If I name both data, do I have a name collision?

Thanks for clearing up.


texts: Return multiple rows from matching on multiple CASE matches?

texts:
I want to add a column to my query which will specify one or more categories a row matches. I want to take this:

    +--------------+---------------+
Row | Product      | Quantity_Sold |
    +--------------+---------------+
1   | Coca-Cola    | 15            |
2   | Cigarettes   | 4             |
3   | Pretzel      | 6             |
4   | Beer         | 25            |
5   | Popcorn      | 10            |
6   | Candy Bar    | 10            |
    +--------------+---------------+

And return this:

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Beer         | 25            | Controlled Substance |
6   | Popcorn      | 10            | Snacks               |
7   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

Notice on line 4-5 of the output, "Beer" is on two lines, because it fits in two categories.

If I try to do this with CASE, only the first match will be counted.

This query

SELECT
    Product,
    Quantity_Sold,
    CASE
        WHEN
            Product IN ('Coca-Cola', 'Beer')
        THEN
            'Beverages'
    CASE
        WHEN
            Product IN ('Pretzel', 'Popcorn', 'Candy Bar')
        THEN
            'Snacks'
    CASE
        WHEN
            Product IN ('Cigarettes', 'Beer')
        THEN
            'Controlled Substance'
    END
        AS Category
FROM sales_table;

Would only return this output

    +--------------+---------------+----------------------+
Row | Product      | Quantity_Sold | Category             |
    +--------------+---------------+----------------------+
1   | Coca-Cola    | 15            | Beverages            |
2   | Cigarettes   | 4             | Controlled Substance |
3   | Pretzel      | 6             | Snacks               |
4   | Beer         | 25            | Beverages            |
5   | Popcorn      | 10            | Snacks               |
6   | Candy Bar    | 10            | Snacks               |
    +--------------+---------------+----------------------+

(Notice "Beer" only appears once)

So how can I get it to show up on separate lines for all categories it matches?


texts: What is the meaning of this anomaly warning message?

texts:
I have run an analysis with SchemaSpy.

The tool gave me a warning message for two tables. The warning message is:

  Tables with incrementing column names, potentially indicating denormalization

What is meaning of the above error?

For reference :

http://schemaspy.sourceforge.net/sample/anomalies.html


texts: How can I determine the average number of transactions per second in MySQL?

texts:
I'd like to see how many transactions per second an instance of MySQL server is processing, on average (per hour and per day). Is there some query to determine this? I've looked through performance_schema as well as information_schema, but nothing seems obvious.

I am running version 5.6.14 for x86_64 on debian6.0


texts: Grouping data to move up and down in a Table depending on Month day

texts:
I am using TSQL.  I have a set of data in a table. I have a stored procedure that runs every day that populates this table. The thing is I need the order of the data to change each day of the month. This data needs to dynamically split up into 7 groups.

For example:

I have 300 pieces of data, I run the query on the first day and I have items 1-300. The second day I would run it and I would have at the top of my list 43-300 and at the bottom 1-42 (in that order). The third day I would have 86-300 on top and then 1-85 on the bottom (in that order). The fourth day and so on throughout the month would be continuing this.

I already have the initial sort, which has to remain in tack. I need a secondary sort (grouping) that would allow this movement.

I am not sure how to do this. Could anyone give me any ideas on how to do such a thing. I know how to do this if it was based on the week, but month is a bit more confusing.


texts: Using dynamic column names in PostgreSQL

texts:
I am using PostgreSSQL 8.3.

I want to execute this query:

select COALESCE(col_fr,col,'Unknown') from my_table where id = 'abc';

Twist in the story is column name colum_fr should be generated dynamically. my_table has different columns for different languages. Something like:

id   col   col_ja    col_fr

I am using this query in crystal reports where I can pass string parameter for language:

select COALESCE(col_||{?parameter},col,'Unknown') from my_table where id = 'abc';

which will internally be converted to something like the following, if {?language} value is fr:

select COALESCE(col_||'fr',col,'Unknown') from my_table where id = 'abc';

which will never work.

I don't want to use select case, to make it dynamic.
As an alternative solution I also tried to create a stored procedure:

CREATE OR REPLACE FUNCTION get_policy_name (
  id text,
  lang   text,
  def_value text
)
RETURNS SETOF record AS
$body$
DECLARE
   sql text;
BEGIN
    sql := 'SELECT   COALESCE(col_'||quote_ident(lang)||',col,'||quote_literal(def_value)||')FROM my_table WHERE id ='||quote_literal(id);

   RETURN QUERY EXECUTE sql
END;
$body$ LANGUAGE plpgsql;

Which should return single record.
It is not working. What am I missing? Does PostgreSSQL 8.3 support RETURN QUERY EXECUTE?


texts: Wrong InnoDB table status (Size/Rows) after updating from MySQL 5.7 to 8

texts:
I've upgraded from Mysql 5.7 to 8 on Ubuntu 18.04

Made it by guide, very accurate, on a new empty server, checked all error/warning logs and fixed it. Everything works just fine with it.

I have 2 same Innodb tables, 1 was created and filled before updating and has right numbers of Rows / Size when querying:

SHOW TABLE STATUS FROM dbname;

Another table shows me 16Kb size and no rows, But its now ~8GB and 4.5 millions rows there. I tried to make Analyze Tablename / Update in Client (Heidisql).

I can't find anything in Google about problem like this. I choose table manually and check rows count, but when I'm using mysql visual clients like Phpmyadmin / HeidiSql it shows me no data in this table.


texts: How to choose a good value for MySQL relay-log-space-limit?

texts:
I just read this blog post about relay-log-space-limit on MySQL. I am convinced that the 'unlimited' default is indeed not very good. But how do I choose a good value for it?

We are using master-master circular replication if that would matter.