texts: Can't create new tablespace for PostgreSQL data. Permissions denied

texts:
For some reason I don't seem to be allowed to set my PostgreSQL data folder to a drive other than the C drive (Windows 8 machine).

1) If I try to do so when installing PostgreSQL, the installation fails. Therefore I need to choose the C drive to make installation work.

2) If I try to move the data folder later on (https://wiki.postgresql.org/wiki/Change_the_default_PGDATA_directory_on_Windows), PostgreSQL service stops running.

3) Finally, if I try to create a tablespace in the D drive, I am not allowed to do so.

When I run the following query:

CREATE TABLESPACE drive_D
OWNER postgres
LOCATION 'D:\PostgreSQL\data'

I get the following error:

ERROR:  could not set permissions on directory "D:/PostgreSQL/data": Permission denied
SQL state: 42501

I am an admin on that machine and I have made sure that I have 'full control' on the D drive.

I also followed advice given on this link "How to create a tablespace on Windows 7 for Postgresql 9.2?". I gave 'NETWORK SERVICE' full control on the D drive, but that didn't solve the problem.


texts: varchar datatype accepting integer values

texts:
below is the test table created. As data type is set to varchar it shouldn’t accept int values.

desc c\G
*************************** 1. row ***************************

  Field: t
   Type: int(6)
   Null: NO
    Key:
Default: NULL
  Extra:

*************************** 2. row ***************************

  Field: h
   Type: varchar(5)
   Null: NO
    Key:
Default: NULL
  Extra:

*************************** 3. row ***************************

  Field: e
   Type: varchar(6)
   Null: NO
    Key:
Default: 0
  Extra:

MariaDB [test1]> insert into c values  (3,098765,654365);
Query OK, 1 row affected (0.00 sec).

My question is how to ask mariadb/mysql to prompt for error or stopped accepting the int values in varchar datatype.I already have strict mode set to

sql_mode = STRICT_ALL_TABLES,NO_ZERO_DATE,NO_ZERO_IN_DATE,HIGH_NOT_PRECEDENCE,NO_ENGINE_SUBSTITUTION,NO_AUTO_CREATE_USER,STRICT_TRANS_TABLES


texts: How to code something like 'Update on duplicate key update to something else'?

texts:
As part of dealing with the UK's GDPA 'right to be forgotten' in a small database I want to update the (autoinc) PK of a row in table 'member' to an obfuscated value and let it cascade down through the FKs in related tables, such as the dates they held certain positions.
If I then set the names of the member in table 'member' to empty strings I can still use my related tables, for example to determine that there was someone holding a certain position at a certain time but I will no longer be able to ascertain either the name or the id of the person who held it, which is what I want.
I have written a simple a UDF obfuscate_id() that takes a positive integer ID and returns a negative ID calculated by combining the id passed with a random number.
FUNCTION obfuscate_id(id INT) RETURNS INT
COMMENT 'returns the negative version of the id passed, obfuscated by a random number'

BEGIN
  DECLARE result INT;
  SET result =  (FLOOR(RAND(id)*10000) + id ) * -1;
  IF result > 0  THEN -- ensures result is -ve even if called with a -ve parameter
     SET result = result * -1;
  END IF;
  RETURN  result;
END;

And I am using the sql
UPDATE member
SET  member_id = obfuscate_id(member_id)
WHERE member_id = 1234;

However, this might generate a negative id that already exists from a previous update of another member_id. If so I want to set it to something else, maybe by obfuscating the id again as in
SET  member_id = obfuscate_id(obfuscate_id(member_id));

Is there a simple way, like ON DUPLICATE KEY ... to check if the value I am about to update to already exists and if so update to something else?
I did look at
how-to-use-on-duplicate-key-for-update
and
update-on-duplicate-key-update
and
mysql-update-with-on-duplicate-key-update
but am no wiser
I would add that given the size of table 'member' and the particular application, even the first update is going to be a very rare occurrence, so the chance of having any obfuscated IDs is extremely slim, let alone duplicate ones, but I'd like to cover the eventuality if I can.


texts:
I recently started working at a company (Junior Web Developer) and so far loving it. The company's all and only database is one giant Oracle database that takes care of all their data persistence requirements.

The company develops in the .NET framework (Classic ASP, VB.NET WebForms and now C# ASP.NET MVC) which I have an OK understanding of and good exposure too, but I have only ever used .NET with MS SQL Server.

Due to money and licencing the developers at the company do not each have their own local database.

The other day I noticed the Oracle does a Free Express version.

IF I install that on my local work machine, can I use it for all my .NET application needs? E.g. an ASP.NET MVC4 app with Entity Framework? WITHOUT at all affecting the main live database of the company so that I don't mess anything up whilst learning EF with Oracle.

PS. I know the current setup sucks, and they're working to get all developers their own local db.

Any help would be greatly appreciated.


texts: Where is SQLCMD.EXE in SQL Server 2014 Express?

texts:
Using "SQLCMD.EXE" to back up my SQL Server Express databases for years I just discovered that after installing the 2014 version, I found no SQLCMD.EXE anymore.

In previous versions it was located at

  C:\Program Files\Microsoft SQL Server\110\Tools\Binn\SQLCMD.EXE

But in my 2014 installation, no SQLCMD.EXE exists in

  C:\Program Files\Microsoft SQL Server\120\Tools\Binn

My question:

Any chance to get SQLCMD.EXE into SQL Server Express 2014?


texts: Beginner design of biological sampling database

texts:
As a complete newcomer, I would like to practise designing a database based on the biological data my lab has been accumulating over the years. Here is an explanation (I am simplifying/abstracting what we are doing to make this first database simpler):

Each fieldwork season, we would go and measure trees (e.g. height, trunk diametre, etc.) at different geographical locations. Over the years, each tree and location would have been visited and measured multiple times. However, due to practical constraints we may not get to visit and measure all trees at all locations every season.

The purpose of the database are twofold:

(1) Track how each individual tree changed with time, asking questions like: "Did tree A grow taller over the past two years?"

(2) Track how populations of trees at each location changed with time, asking questions like: "Did the group of 12 trees at Acme Acres grow taller in general over the past three years?"

Here is my initial thoughts on what tables to make:

(a) Trees: Fields: TreeID (e.g. "tree A"), Location (linked to table c below).

(b) Visits: This keeps metadata for each visit, with information like the date of visit (for a location, which will be the same for all trees at that location), locations we covered during the visit.

(c) Locations: Basic information on each location, such as latitude, longitude, name of location (e.g. "Acme Acres"), etc.

(c) TreeData: This contains the actual measurements for individual trees. Fields may include: tree (linked to table a), height, diametre, visit (linked to table b). In the end there will be multiple entries any one tree here, with data from each visit.

Does any of this make sense as a first dab at database design? (I've only been thinking about databases for three days)

I would appreciate and humbly accept any pointers you can give me.

Thank you very much!

BTW, One reason I was considering a Visits table was because sometimes I might to compare the data between visits 3 and 4, or between visits 1 and 5, etc


texts:
I have been researching the use of Application Roles within Microsoft SQL Server and was wondering if there is a way to limit a user to only be able to set specific application roles. As I understand, any user of the database with sys.sp_setapprole execute permissions can set any application role they choose, as long as they provide the application role name and password. To me this seems like a trade-off in security.

For example, let's say John works in Accounting and uses an application named Accounting.exe, which is hard-coded to set the Accounting_appRole once connected to the AdventureWorks Database. Bill works in Finance and uses an application named Finance.exe, which is hard-coded to set the Finance_appRole once connected to the AdventureWorks Database.
Both John and Bill must, at a minimum, have execute permissions for the sys.sp_setapprole stored procedure of the AdventureWorks Database. Therefore, if John from accounting e-mails the Accounting.exe application to Bill in finance, Bill can execute the Accounting.exe application without issue, which is undesired.

I think there should be a way to limit who can set a specific application role, does anyone know of a way? All I can come up with is modifying the sp_setapprole stored procedure to use SUSER_SNAME() to limit the value(s) of @rolename that can be used.


texts: How to create a custom "Customize" frontend in Oracle APEX 19+?

texts:
If you create an application with "Theme Style Selection" option and "Enable End Users to choose Theme Style" in User Interface Attributes, there's a "Customize" link in the footer, which is referenced by #CUSTOMIZE# substitution placeholder in the template, and the target is javascript:apex.theme.openCustomizeDialog(title, language);.
This function then opens a modal dialog (apex.navigation.dialog) with a wwv_flow_customize.show page using these parameters:

p_flow - Application ID, returned by $v('pFlowId')
p_page - Page ID, returned by $v('pFlowStepId')
p_session - Session ID, returned by $v('pInstance')
p_lang - Language two-letter code, from openCustomizeDialog argument

This page could also be opened as standalone if you replace /f?p=... part with /wwv_flow_customize.show?p_flow=...

This page contains a form with radio group name="p_themestyle". Labels of the options match with public Theme Styles, but the numeric multiple digit values don't match with Theme Style IDs from wwv_flow_theme_styles table.
What I need is a custom theme style switcher frontend. But I can't find the exact JS calls that set certain Theme Style IDs as selected for the current user. "Apply Changes" button just does onclick="javascript:apex.submit('DOIT');". And the desktop_all.min.js file is very minified and obfuscated to properly debug.
I also wasn't able to track network activity in debugger as it always resets records after "Apply Changes".

Is there an option somewhere to edit how the "Customize" page looks like?
Is there full documentation for this feature somewhere?
Where and how does APEX store which theme the current user has selected?
What are the options to debug this request and find this particular API call in network activity?


texts: How to count between two users unread messages in SQL

texts:
I am creating between two users unread message count script in SQL.

My script problem not counting between two users unread messages.
My script problem all users unread messages display as a same results.

Like this

user1 unread message 2
user2 unread message 2
user3 unread message 2

I want to get like this results

user1 unread message 2
user2 unread message 7
user3 unread message 4

Here is my link http://sqlfiddle.com/#!9/299d64/3/0


texts:
I am trying to group data on homogenous periods of time (not sure this is clear).
Here is a simplified example of my data:
CREATE TABLE workhours_over_time (
             worker varchar,
             workhours integer,
             otherinfotobeignored varchar,
             startofperiod date,
             endofperiod date);

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-01-01','2020-01-31');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any other info', '2020-02-01','2020-02-28');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '39', 'any info', '2020-03-01','2020-04-15');

INSERT INTO workhours_over_time (worker, workhours,
            otherinfotobeignored, startofperiod, endofperiod)
VALUES ('W1', '35', 'any info', '2020-04-16','2111-11-11');

So here is the result:
worker|workhours|otherinfotobeignored|startofperiod|endofperiod|
------|---------|--------------------|-------------|-----------|
W1    |       35|any info            |   2020-01-01| 2020-01-31|
W1    |       35|any other info      |   2020-02-01| 2020-02-28|
W1    |       39|any info            |   2020-03-01| 2020-04-15|
W1    |       35|any info            |   2020-04-16| 2111-11-11|

The result I need to obtain would group data in 3 periods: from 2020-01-01 to 2020-02-28, worker W1 works 35 hours a week. Then from 2020-03-01 to 2020-04-15 W1 works 39 hours a week. Then from 2020-04-16 to end of time, back to 35 hours.
worker|workhours|startofperiod|endofperiod|
------|---------|-------------|-----------|
W1    |       35|   2020-01-01| 2020-02-28|
W1    |       39|   2020-03-01| 2020-04-15|
W1    |       35|   2020-04-16| 2111-11-11|

I had first naive try with a simple group by:
select worker, workhours, min(startofperiod), max(endofperiod)
from workhours_over_time
group by worker, workhours

but of course, first and third period where grouped into a 2020-01-01 to 2111-11-11 period, which is not what I want.
Then I have toyed with window function, read on groups and island, but didn't find the solution...
Any idea ?


texts: What are the best practices for restricting write-access on a specific table?

texts:
I have a table, which I'd like to be read-only, except for a few specific stored procedures which need permissions to UPDATE rows in the table.

After that, I want only a select group of additional stored procedures to have permissions to EXECUTE the aforementioned UPDATE procedures.

I've been reading about GRANT and DENY on MSDN/TechNet, and I've also been reading about schemas, but the documentation is so vague and non-relatable that I'm having trouble figuring out how to accomplish real-world security goals based on the descriptions provided.

I can see that you can GRANT or DENY permissions for individual principles, one at a time - but I need to deny INSERT UPDATE and DELETE permissions for ALL principles, EXCEPT for the few stored procedures to which I am granting UPDATE permissions (as well as sa and/or db_owner I suppose).
I don't want any new users to have UPDATE permissions on this table by default, and I would really like to avoid having to manually deny permissions every time we create/modify users or other objects.


texts: MySQL 5.5 - determining correct write[read]_io_threads on high-end system?

texts:
Specifically dual-CPU 32 core - 128RAM - RAID 10 SSD .  Ubuntu 64 server.

Heavy Innodb load at times - ~ 2000+ queries per sec.  Heavy read and write.

Currently we are running on the default write_io_threads of 4.

Are we "nerfing" the power of our server?
Is it safe to run it on max write_io_threads=64 and read_io_threads = 64 ?

How is this number determined?


texts: How to parse XHTML node value with TSQL?

texts:
I have the following XHTML and would like to be able to parse out My Node Value. Is there a way to easily do this with TSQL?
My Node Value


texts: 10000 tenants and Muti-Tenant Data Architecture performance?

texts:
We are trying to decide on a database design for a new web application. We expect to have close to 10,000 tenants and would like to keep their data in separate databases if it makes sense to do so. Each tenant will have a DB that is around 20MB they are tracking mostly the personal data of 50 to 100 youth and 100 to 300 adults plus events, attendance, awards etc. I do not know how many total users would log in at once but each tenant could have several.

My options as I understand them is to have:

A shared-nothing approach giving each tenant a separate database.
A schema approach giving each tenant separate tables.
shared-everything approach where each tenant has their own tenantID

I would ideally like the shared-nothing approach but I am unclear of how many databases you can have in SQL Server before you run into performance issues. Managing multiple databases is not a major concern for us but if SQL Server becomes unresponsive due to the number of DBs this is the kind of information I am seeking. Keep in mind the databases are small 20MB on average. I also need to easily restore backups for individual tenants. I have also read that you can have roughly 32,000 databases in SQL Server but other post suggest you shouldn't try to get anywhere close to this number. Even 1000 to 2000 databases can cause performance issues but it was unclear of the database sizes that caused the issues. If anyone has used thousands of small databases in a SQL Server environment please let me know.

Performance wise which approach would be the best route for us to take? Please also take into consideration of having multiple SQL Server Licenses would not be ideal cost wise. But if it is necessary please let me know.

I have also read several post here and also Multi-Tenant Data Architecture but I haven't seen a real clear winner with a large number of tenants but really small databases.

If anyone has experience with something similar please let me know how you handled this and if it is working out well. Any real world experience/advise would be greatly appreciated.

Thank You.


texts: Is there a benefit to using FOR SHARE in this PostgreSql simple select?

texts:
In the following PgSql stored function:

CREATE OR REPLACE FUNCTION get_offer_from_id(
        offer_id     bigint)
RETURNS json AS $$
DECLARE
    res "entity_data_result";
    r   "get_offer_data";
BEGIN
    FOR r IN SELECT * FROM "get_offer_data"
              WHERE "id" = offer_id
              LIMIT 1
              FOR SHARE
        LOOP
            res.found := true;
            res.data := row_to_json(r);
        END LOOP;
    RETURN row_to_json(res);
END; $$
LANGUAGE plpgsql;

is there a real benefit to using FOR SHARE or can one remove it from the query?


texts: Do MySQL GTIDs get created for all transactions or just those that occur after enabling GTIDs?

texts:
I have a MySQL database that I'm going to start replicating, and I plan on using GTIDs to do so. The slave will be a brand new virtualized server located overseas.

My question is 2-fold:

When I enable GTIDs on the existing master, will GTIDs be created for all previous transactions? Or will they only be created for transactions posted after enabling GTIDs?
If yes, can I simply let the slave replay all of the GTIDs at its own pace?(meaning I don't have to sync the slave before enabling replication)

I don't care how long it takes the slave to sync (within reason, of course. 2 weeks is probably the max).

I'll be testing this out on some dummy dev servers come Monday or Tuesday, but I figured I'd ask here first.

Version and DB Info:

Master: MySQL 5.6.19 on Windows Server 2008R2 (EoL, I know...), but will be upgraded to 5.6.37 before starting replication and will then be upgraded to 5.7.x sometime thereafter.

log_bin for this server is currently OFF.

Slave: MySQL 5.7.19 on Ubuntu Server 16.04 LTS (verified compatible)
Size: It's probably considered a small database, totalling only about 330GB and something like 2.5 billion rows.

Previous Research

I couldn't find anything answering this question in the GTID docs nor in the entire replication docs.

So far, I've gone through and enabled both binary-log based replication GTID-based replication on various dev servers, but they 1) are very small/empty; 2) had log_bin enabled from the start; or 3) were synced before replication was enabled.


texts: Force MATCH to return a certain number of results

texts:
I am using MATCH in a query but sometimes the query returns no results. What can I do to force MATCH to return results, even if the results are not a match? I want the query to return four results. If the results are not a match then they should be random.

e.g. I want to get the two matches for the word 'Foo' plus two more random rows, making four results in total:

ID  TITLE
1   Carl
2   I am a title
3   Foo
4   Bar
5   Foobar
6   Title

The query should return something like:

ID  TITLE
3   Foo
5   Foobar
2   I am a title
6   Title

I tried:

SELECT
    *
FROM
    Entries
WHERE
    MATCH(Title) AGAINST("Foo")
UNION
    (
    SELECT
        *
    FROM
        Entries
    ORDER BY
        RAND()
    )
LIMIT 4

I feel like this query should work, and it does, except the results that are not a match are not random for some reason, they are the same results every time.


texts: Mandatory to use "Three Parts Name" in T-SQL statements of SP

texts:
Using three parts name in SQL statements is a good practice it helps in object identification and performance improvement as well. It can also help for many other purposes like if you want to move SPs,View Functions to different database with same T-SQL statement..

So I wanted to create a policy which can enforce developers to use "Three Part Naming" when they'll create SPs, Views and functions.

Is there any inbuilt policy or way in SQL Server 2016 which can help me out?

Response will be appreciated.. :)


texts: A column is missing from the Select Top 1000 Rows result

texts:
I have a view in my database. When I am applying the Select Top 1000 Rows command to it in the Object Explorer in SSMS, the resulting script is missing one column, as you can see in the picture below (the missing column is BranchCode).

I have applied the sp_refreshview system stored procedure in an attempt to fix the problem, but it didn't help.

How is this possible and how can I resolve this?


texts:
I'm sorry for the vague question description, but I'm honestly a noob in DB design, as I've only really worked with small databases. This might change in the near future though. I have a spider which periodically crawls a website that sells merchandise. The objetive of this spider is to:

1. Obtain essential info from each object being sold, such as name, product_code, url, description, etc.
2. Get access to the price history of said object (by periodically rerunning the crawler), with respective timestamp.

These two requirements lead to two possible (in my limited db knowledge) database designs:

First Option:
An INSERT only table called products, with no UNIQUE fields (except row number), containing all fields mentioned above. Every repeated run of the crawler will duplicate existing rows.

On each product found by the crawler, one simple (and guaranteed) insert on the product table.

When consulting price history, simply group by product_code.
Second Option:
Two INSERT only tables, one called products, containing all essential data (description, name, etc). This table also contains a UNIQUE field called product_code. The second table, called prices, contains only the fields product_code, timestamp and price. This table contains no UNIQUEfields, except for the row id itself.

On each product by the crawler, two inserts:

One simple and guaranteed insert on the prices table.
An insert attempt, which will only actually go through when a never before inserted product is found.

When consulting price history, simply group by product_code on prices table.

My opinion

I believe the second option will scale better in disk usage, as many fields will not be repeated needlessly (as in the first option).

The first option is probably faster though as it's simply one guaranteed INSERT. How much faster though? How bad is it, in the second option, to try and constantly fail to insert?

EDIT - Note when considering the above point, thats in the second option the products table will be much smaller than the prices table.

Please go ahead and suggest other designs if you think it may be better! I'm completely welcome to discussion.


texts: Storing last N samples in a table coming from multiple clients

texts:
I want to store some last N (say fixed 50) samples in a table indexed by uuid. They're coming from multiple clients. The requirements of which last N are weak (the use case is to show some recent samples for debugging a stream of messages). But I want them to be relatively current, so my clients will update them often. So ideally I want to keep replacing the same rows.

My current plan is to have a schema like such:

CREATE TABLE samples (
  id     uuid     NOT NULL,
  index  integer  NOT NULL,
  text   text     NOT NULL,

  PRIMARY KEY(id, index)
);

The clients will then pick a random N between 0 and 50 and do upserts like such:

INSERT INTO samples
  (
    id,
    index,
    text
  )
VALUES
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    0,
    'sample 0'
  ),
  (
    'f85c6f06-ae0b-48de-8c36-bfc86208495c',
    2,
    'sample 2'
  )
ON CONFLICT (id, index)
DO UPDATE SET text = EXCLUDED.text;

The downside is that I'll be replacing them at random, which is probably going to work out but not guaranteed to. It also means multiple clients will trample on each other.

Is there a better way to do this? Some sort of round-robin style replace would be ideal, but I'm not sure how to get that effect. Is there a different strategy all together I could use here?


texts: My application uses set schema and unqualified tables and I get confusing numbers from snapdyn_sql

texts:
More of a finding, than a question, I'll add an answer in a minute.

The other day I stumbled upon an application that used several tables with the same name, but in different schemas. Example:

create table s1.parent (x int not null primary key);
create table s1.child (y int not null primary key, x int not null references s1.parent(x));

create table s2.parent (x int not null primary key);
create table s2.child (y int not null primary key, x int not null references s2.parent(x));

The application uses the pattern:

set schema s1;
select * from parent x join child y on ...

Despite that the question have been executed against several schemas, there is only one row in:

select num_executions, rows_read, stmt_text
from sysibmadm.snapdyn_sql
where stmt_text like '....%'

and the numbers appear to be cumulative for all schemas. Why is that, and how do I figure out metrics for the different schemas?


texts: Why is SSMS 2008 prompting me "if you are certain that you have added all the necessary full-text catalogs, click OK"

texts:
When I try to attach a database in SQL Server Management Studio 2008 (SSMS), I get prompted by this message:

If you are certain that you have added all the
necessary full-text catalogs, click OK.

Why is this coming up.  It didn't use to.

We did make some schema changes but have done nothing with full-text catalogs.

Is there a way to prevent this coming up?


texts: Which CLR Stored Proc is causing memory pressure leading to sqlservr.exe terminating unexpectedly?

texts:
Every few days my SQL Server Instance terminates unexpectedly and restarts.
In the logs, see:

  A fatal error occurred in the .NET Framework common language runtime. SQL Server is shutting down. (Event ID 6536).

Prior to the termination I see several messages saying:

  AppDomain XXX is marked for unload due to memory pressure.

I have 60 CLR Stored Procedures, which I've inherited from my predecessor. All are supposed to be short stored procedures using a small amount of data. How do I find which CLR Stored Procedure is causing the memory pressure?

I'm running:

Microsoft SQL Server 2016 (SP1-CU4) (KB4024305) - 13.0.4446.0 (X64)   Jul 16 2017 18:08:49
Standard Edition (64-bit) on Windows Server 2016 Standard 6.3  (Build 14393: ) (Hypervisor)


texts: How to replicate from Standby mode database

texts:
I have set up a pipeline which brings transactional logs each hour from a FTP site, and this newly created database is in Standby mode all the time because I need to read data during an office hour.
Initially, I was thinking about leaving the restore mode in NoRecovery because I learned that Standby mode takes more time and for performance reason. But then, I realized that I cannot read data during an office hour, so I decided to set up hourly transaction log update in Standby mode all the time.
So, I will have one database which I am trying to isolate it to just pull transactional logs hourly, and then I would like to have a copy of this database so that it would not interrupt any process (of hourly logging) while I am reading data for reporting purposes.
I am trying to see if there is a way to replicate this database (which is in standby mode) so that at least I have a backup database, and I could pull data from there for reporting purposes.
Or would it be better if the database that update logging files daily from FTP site be NoRecovery mode and there is a way to replicate this database somehow? I am just thinking about a better option to set up stable database architecure.
BTW, this particular SQL Server is in an Azure VM, and  I am trying to bring in only necessary data into Azure SQL Server (Portal) eventually.


texts:
I am designing a table for a system that saves a number of logs. We are looking at ~200 entries per second.

We are using SQL-Server 2012 Enterprise Edition.

I have a question regarding breaking up a Datetime column into two columns, Date and Time.

My thinking behind the question. I would say once the data is stored in the database, most searches are going to be based on a daily basis, give me all results from today / 10th of Jan.

Now I do still need to store the time as well. So if I store it as a datetime, when executing this query sql will have to load the whole datetime fields and then only look at half of the data.

So by possibly storing date in its own field it can look at only what it needs.

But then on the other hand if you do specify a time in your query, it now has to check the value of two columns.

So I would like the input from you SQL Gurus on which option would have better performance for queries on a large database.

For all I know datetime could be highly optimised and a much better solution than breaking it up.


texts:
The background to this question can be found here.

The following part of my stored procedure does not work. Is there a character length limitation for the LIKE clause? t1.ProductList sometimes can be up to 1000 characters in length:

DELETE t1 FROM #tmptable t1
WHERE EXISTS (SELECT 1 FROM #tmptable2_ t2
              WHERE t1.DealerId != t2.DealerId
              AND t2.ProductList LIKE '%' + t1.ProductList + '%'
              AND t2.numberOfVibs > t1.numberOfVibs);

Sample data for a single row of the ProductList column:

B1ZMA25706,B1ZMI09502,B1ZMI12910,B1ZMI18602,BB001TBL26,BBHMOVE4,BE09501FBL,BGS52200,BKS3003,BM2,BO11001EBO,BPGTB1200,BPGTC172HP,BPGTX663,BPIVMS6502,BPIVOD1022,BPVA475,BPVB1000,BPVB800,BPVC652,BPVPW1500,BPVW1000,BPVX652,BPVX662,BREMEN78,BSA2602,BSD2880,BSG62082,BSG71800,BSG81623,BSG82422,BSG82480,BSGL32015,CAB150,CAB21,CD1401B,CD21001WAL,CD21004WAL,CD2105WHI,CD2108WAL,CD5601S,CDBSE7300A,CEPM8CAPPU


texts: How to insert data from a local MSSQL db table to a remote MSSQL db table using WCF application coded in .NET

texts:
I made a WPF application using VS2013, coded in .NET. It is working fine in my local SQL Server. But I want to use it to upload data from a local database to a remote database. How can I do this?

I have three buttons (UPLOAD, DOWNLOAD, SYNC), and three stored procedures in SQL Server.

Upload will run insert into Database123.table, select * from DATABASE456.table
Download will run insert into database456.table2, select * from database123.table2
sync will run delete from databse456.table1 where id in database456.table2

But i want to use a remote database in place of database123. How can I do this?


texts: ALTER TABLE on a large table with an indexed column

texts:
I have a large table with a VARCHAR(20) column, and I need to modify that to become a VARCHAR(50) column. Typically, performing an ALTER TABLE (adding a TINYINT) on this particular table takes about 90-120 minutes to complete, so I can really only do that on a Saturday or Sunday night to avoid affecting the users of the database. If possible I would like to do this modification prior to then.

The column is also indexed, which I presume will make the ALTER TABLE slower, because it has to rebuild the index after modifying the column length.

The web app is set up in a MySQL replication environment (26 slaves and one master). I recall once reading somewhere that one method is to first perform the ALTER TABLE on each slave (minimising impact on users), then do this on the Master, but won't that then try to replicate the ALTER TABLE command to the slaves?

So my question is: what is the best way for me to modify this table with minimum disruption to my users?

Edit: the table is InnoDB.


texts: RMAN-03009: failure of backup command due to ORA-27038 and OSD-04010:

texts:
I'm running below script in a windows server, this is same script I run across all servers

run {
configure retention policy to recovery window of 7 days;
 backup current controlfile format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_controlfile_%d_%T_%t';
 backup spfile format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_spfile_%d_%T_%t';
 backup incremental level 0 format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_%d_%T_%t' database ;
sql 'alter system archive log current';
backup archivelog all format 'F:\DATABASE_BACKUP\%d\%Y-%M-%D\FULL_ARC_%d_%T_%t' delete input;
}
crosscheck backup;
report obsolete;
delete noprompt obsolete;
delete noprompt expired backup;
list backup summary;
exit;

But I'm getting below error:

RMAN-00571: ===========================================================
RMAN-00569: =============== ERROR MESSAGE STACK FOLLOWS ===============
RMAN-00571: ===========================================================
RMAN-03009: failure of backup command on ORA_DISK_1 channel at 04/29/2020 11:30:04
ORA-19504: failed to create file "F:\DATABASE_BACKUP\TTTSCM\2020-04-29\FULL_TTTSCM_20200429_1039001396"
ORA-27038: created file already exists
OSD-04010: <create> option specified, file already exists

Control file and SPfile are created successfully, and directory is empty prior backup. Thanks.


texts: Why is MySQL returning rows not matching the ID?

texts:
I have a simple table (set up for testing purposes) with just an id bigint(20) auto_increment column and 2 rows.

Now, if I try select * from abc where id = '1', it works fine obviously, but any of these will also return the same row:

select * from abc where id = '1d'
select * from abc where id = '0001blah'

What's the deal? Does it have something to do with quotes? I always thought = meant it would return only the exactly matching rows...


texts: Using COALESCE when joining multiple tables

texts:
In a PG 12 database I have the reservations table with the reserved_between tsrange column.

For a specific analysis, I need to know the ending of the last reservation (later in time) and teh following query does just that.

SELECT MAX(upper(reservations.reserved_between))
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

Where <MYID> and <MYTAG> are the params needed to identify the analysis.

Now, in case there a no reservations, I need to return the  as_early_as (datetime) value of the batch the analysis refer to.

I think COALESCE could be a good option for the job. Thus, I have updated the quesry as follows.

SELECT COALESCE(MAX(upper(reservations.reserved_between)), batches.as_early_as)
FROM dev.reservations
JOIN dev.analyses ON reservations.analysis_id = analyses.id
JOIN dev.analytical_tests ON analyses.analytical_test_id = analytical_tests.id
JOIN dev.batches ON analyses.batch_id = batches.id
WHERE analytical_tests.specification_id = <MYID>
AND analytical_tests.tag = <MYTAG>

However, when I run I get

  ERROR:  column "batches.as_early_as" must appear in the
  GROUP BY clause or be used in an aggregate function LINE 1:
  ...ALESCE(MAX(upper(reservations.reserved_between)), batches.as...

And I'm not sure how to fix it and the documentation doesn't help me too much.

What should I change in my query?


texts: "no join predicate" warning with outer join

texts:
I have a database schema that looks something like this:

create table t_order (orderid int, customerid int)
create unique clustered index ix_order on t_order(orderid)
create index ix_order_1 on t_order(customerid)

create table t_product (productid int)
create unique clustered index ix_product on t_product(productid)

create table t_orderItem (itemid int, orderid int, productid int)
create unique clustered index ix_orderItem on t_orderItem(itemid)
create index ix_orderItem_1 on t_orderItem(orderId, productId)
create index ix_orderItem_2 on t_orderItem(productId, orderId)

(i.e. the orderItem table is a many-to-many between orders and products). Now I have an SQL statement like this:

SELECT
    t_product.productid,
    t_order.orderid,
    t_orderItem.itemid
FROM
    t_product
    JOIN t_order ON t_order.customerid = 101
    LEFT OUTER JOIN t_orderItem ON t_product.productid = t_orderItem.productid AND t_order.orderid=t_orderItem.orderid

when there are a lot of rows in the database (and with statistics updated), I get the "no join predicate" warning on the left outer join, and the query takes a long time to execute.

Unfortunately it's not code I wrote myself and I don't really understand what that outer join is trying to do. I quite expect the SQL is "wrong" (i.e. doesn't do what the author intended) but I can't fix it unless I can get a handle on what it's actually doing at the moment.

I have seen other examples on the net of using AND inside the ON clause, but not involving other tables that aren't part of the join.

Can someone explain what the effect of this join is and why it causes a Cartesian product to be generated.


texts: Are there any caveats in my SQL migration plan?

texts:
I would like to move several databases from one Microsoft SQL Server instance (2014) to another instance (2016) while still in production. Are there any obvious caveats or holes in my plan?

1) Take a full back up of each database that I want to move

2) Restore databases to the new instance

3) Sometime in the middle of the night switch each source database to single user mode.

4) Take a last final transaction log backup

5) Restore transaction log backup to the new instance databases

6) Turn off old instance and point relevant resources at the new instance

Any advice, caveats, etc.


texts: SQL Server database backup and saving at the same time

texts: What will happen when is running differential backup on SQL Server 2012 and some application is trying save data to table at the same time?


texts:
Should I use varchar(255) or varchar(256) when designing tables? I've heard one byte is used for the length of column, or to store metadata.

Does it matter anymore at this point?

I saw some posts on the internet, however they apply to Oracle and MySQL.

We have Microsoft SQL Server 2016 Enterprise Edition, how does it apply to this environment?

Now say for example, what if I told my clients to keep for example, a text description to 255 characters instead of 256, is there any difference? What I read " With a maximum length of 255 characters, the DBMS can choose to use a single byte to indicate the length of the data in the field. If the limit were 256 or greater, two bytes would be needed." Is this true?


texts: Can MySQL 5.7 log queries that would violate sql_mode=ONLY_FULL_GROUP_BY,NO_ZERO_IN_DATE,NO_ZERO_DATE

texts:
As part of a migration to MySQL 5.7 i'd like to find out which queries would fail with the default sql_mode that includes ONLY_FULL_GROUP_BY, NO_ZERO_IN_DATE, NO_ZERO_DATE.

The easiest shortcut would be just to relax the configuration and set:

sql_mode=STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

But instead I'd like to modify the application code and address the root cause.

thanks in advance for help!


texts: Linked server - cannot create an instance of OLE DB provider "SQLNCLI11"

texts:
I have created a linked server and I am receiving the following error:

The test connection to the linked server failed.

Additional information:
An exception occurred while executing a Transact-SQL statement or batch.
(Microsoft.SqlServer.ConnectionInfo)

Cannot create an instance of OLE DB provider "SQLNCLI11" for linked server
(Microsoft SQL Server, Error: 7302)

I made sure "allow inprocess" is checked and still get and error.
I can telnet to the other server.
SQL Server 2017 is 64bit and the SQLncli11 is both 32bit and 64bit.


texts: why am I getting a warning of Depreciation of INT(11) in latest version of MySQL?

texts: When I try to create a table with a column of type INT, it automatically gets allocated as INT(11) and this Int length will get depreciated in the latest version of MySQL. How can I declare the column as INT except of INT(11)?


texts:
Can the Reorganize Index task (maintenance plan or SSIS package) be used to tune performance?

Is this an adequate step of triage for degraded application/query performance?


texts: What is an "empty query" in information_schema.CLIENT_STATISTICS?

texts: The client stats table has an "empty queries" column.  What does that mean?  Simple googling just tells me "The number of times this client's connections sent empty queries to the server."  /facepalm


texts: Efficient way of changing VARCHAR to NVARCHAR fields in a large table in SQL Server 2008?

texts:
I am aware of when adding new fields to large tables, it is recommended to add them to the end of the fields rather than somewhere in the middle, and wondering if something like this applies when changing field types?

I have a table with about a million records that has several VARCHAR type fields.  I would like to change these to NVARCHAR, but as I understand it, this will take some time and resources, as the fields are in the middle of the table, and SQL Server has to do a bunch of copying/re-ordering.

What is an efficient way of accomplishing this?


texts: Create a mysql slave database from master database

texts:
I have a mysql master database running. I want to have a slave database. So, I stopped the master mysql database and copied it to the slave machine. I had bin log enabled on master so even those files are copied.

I am not sure, how to start replication on the slave. Any help would be appreciated.

I am still copying data into slave.

mysql> show master status;
+-------------------+----------+--------------+------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+-------------------+----------+--------------+------------------+
| mydbm1-bin.008524 |  1330529 |              |                  |
+-------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

By the way, many new rows have been inserted into the master after the snapshot is copied to slave.


texts: Implement "account" transactions in postgresql

texts:
I am looking into on different ways a simple account functionality that can be implemented in Postgresql 9.5. I would like to be able to do it with maximum of "Repeatable read" as transaction isolation level. The reason why it should be "Repeatable read" isolation level is that we have detected a high level of collisions "serializable" isolation level and even with a retry strategy around 10 times within 10 seconds it still fails because of the amount of collisions.

Deposit money
Withdraw money
Cannot withdraw money if balance of the user is less than zero (sum of amount for user)
Balance must never be able to go below zero (sum of amount for user)

It should handle multiple connections and multiple users and the same user can initiate deposits and withdraws in different connections.

The only solution I currently have is to do something like the following:

For deposits:

Allow deposit money directly as an insert.

For withdraws:

Check balance > amount of withdraw before continue.
Insert withdraw money transaction with status initiated.
Update withdraw money transaction status failed if sum of transactions for user in status (initiated, completed) is less than zero.
Update withdraw money transaction status completed if status is initiated.

Any materials or examples are welcomed.


texts:
Is there a way to know if a COALESCE has been done on an index?  Doing one doesn't seem to update the CREATED, LAST_DDL_TIME, or LAST_ANALYZED attributes.

Note:  I am not referring to the COALESCE that returns the first non-null expr in the expression list, but the one that is used to make free space in a block contiguous like this:

ALTER INDEX [Index Name] COALESCE;


texts: I have to find the records which contain 6-A, 6-B, 6-C...6-Z. using SQL?

texts:
I have a table named TABLE_1 and column test which has records in characters like 'AKLJNAAAAAAAIOUHIO', 'PIOJPMBBBBBBBPIO', 'OJPOIDDDDDDDPQP'...

I have to find the records which contains 6-A, 6-B, 6-C ... 6-Z using SQL.

How can I do that?


texts: detaching database results in error "SQL Server detected a logical consistency-based I/O error"

texts:
I'm trying to detach a database and I'm getting this error :

  SQL Server detected a logical consistency-based I/O error: incorrect pageid (expected 1:234; actual 0:0).
  It occurred during a read of page (1:234) in database ID 5 at offset 0x000000001d4000 in file 'D:\Path\MyDB.mdf'.
  Additional messages in the SQL Server error log or system event log may provide more detail.
  This is a severe 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. (Microsoft SQL Server, Error: 824)

What is the problem with this database file ? Other DBs are working well, only this one gives me this error.


texts:
Situation

I have two simple tables, one called "node_relations" and one called "node_information". The table "node_relations" represents a simple parent-child hierarchy, having only two columns "id" and "parent", with "parent" referring to the "id" column. "node_information" on the other hand stores custom information about the nodes in our hierarchy, each entry identified by a column called "info_id". A third table referring to "node_relations"."id" and "node_information"."info_id" relates those two tables in the way of a many-many-relationship.

For a given entry of the "node_information" table, I want to list all entries of "node_relations" that are either directly related by the third table or a descendant of such a related entry. This can easily be done by using PostgreSQLs "WITH RECURSIVE" functionality, creating a temporary working table containing the list of all ancestors for each node, and then listing all nodes containing an ancestor related to the given "node_information" entry. However, as the "node_relations" table grows, this gets awfully inefficient, at least the way I'm currently doing this.

My problem seems to be that PostgreSQL first completely creates the whole recursive table, and only when joining it with my other tables discards the obsolete rows. Is there a way to tell PostgreSQL to start the recursion only?

Details

To reproduce, create a table such with the following query:

    /* Create table storing parent-child relations */
    CREATE TABLE node_relations(
            id INTEGER NOT NULL,
            parent INTEGER,
            PRIMARY KEY (id),
            FOREIGN KEY (parent) REFERENCES node_relations(id)
    );

Fill it with random data:

    /* Random references created as (random()*generate_series+0.5)::int in order to only refer to nodes with smaller id. */
    /* This, in combination with the removal of self-references, makes sure there are no cycles */
    INSERT INTO node_relations(id,parent)
    SELECT generate_series, (random()*generate_series+0.5)::int
    FROM generate_series(1,1000000);
    UPDATE node_relations SET parent=NULL WHERE id=parent;

Look up an interesting entry. In my case, it was 56:

    /* Find the entry which is refered the most often.*/
    SELECT parent as id,count(*) as refs
    FROM node_relations GROUP BY parent
    ORDER by refs DESC LIMIT 1;

Then list all descendants for that specific node, which works quite smoothly:

    WITH RECURSIVE hierarchy(root_id,id) AS (
            VALUES(56,56) UNION ALL
            SELECT hierarchy.root_id,node_relations.id FROM node_relations
            INNER JOIN hierarchy ON hierarchy.id=node_relations.parent
    )
    SELECT root_id,id FROM hierarchy;

However, while returning the same result, the following query seems to create the whole hierarchy and then discards the part that is not required:

    WITH RECURSIVE hierarchy(root_id,id) AS (
            SELECT id,id FROM node_relations UNION ALL
            SELECT hierarchy.root_id,node_relations.id FROM node_relations
            INNER JOIN hierarchy ON hierarchy.id=node_relations.parent
    )
    SELECT count(*) FROM hierarchy WHERE root_id=56;

Is there a way to restructure my recursive temporary table such that only the relevant rows are created? I would expect PostgreSQL to show the same (inefficient) behavior when joining rows as in my last example. Is there a way to make PostgreSQL start the recursion only on the limited set to begin with?

Semi-Answer

By creating a temporary table "temp_table" containing the IDs of the nodes related to the "node_information" entry, we could adjust the last of the above queries by replacing the first instance of "node_relations" by "temp_table". Is this the way to go?


texts:
I am getting the following SqlException on calling a stored procedure:

  Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.

  System.Data.SqlClient.SqlException occurred
  Message="Attempt to fetch logical page (5:65424) in database 2 failed. It belongs to allocation unit 7349876362857938944 not to 4899918190390149120.

  Source=".Net SqlClient Data Provider"
  ErrorCode=-2146232060
  Class=21
  LineNumber=257
  Number=605
  Procedure="ispDisplayCount"
  Server="10.10.1.1"
  State=3

What does this exception mean? Is there any resolution to the above issue?

Although the database referenced in the error above indicates tempdb, similar errors referencing Message 605, may be fixed using the answers below.

  Msg 605, Level 21, State 3, Line 1
  Attempt to fetch logical page (1:8687634) in database 7 failed. It belongs to allocation unit 72057594364821504 not to 72057594052476928.


texts: Prevent failure in conditional insert in mysql database

texts:
The infrastructure of our system looks like this.

An AWS lambda function receives requests such as (accountId, .....). It creates an entry in the MySQL database using a newly generated UUID as caseId. (caseId, accountId, ....).

The insert is a conditional insert operation discussed in detail below.

I am able to avoid race condition by setting transaction isolation to SERIALIZABLE. However, the issue is that I do not have any control over how many concurrent requests will be successfully processed.

For example, consider following concurrent requests.

request  | accountId | field1 | ...  <condition>
 1         a1          value1   ....   true     --- create a new entry with caseId Idxxx
 2         a1          value2   ....   false    --- update existing entry with caseId Idxxx
 3         a1          value3   ....   false    --- update existing entry with caseId Idxxx
 4         a1          value4   ....   false    --- update existing entry with caseId Idxxx

With our current implementation we are getting CannotAquireLockException.
What are the ways in which I can avoid retry failures (CannotAquireLockException) ?

The detailed table schema and condition are described below:

The database is a mysql database system with the following table schema.

Table1: case table

|caseId(PK) | accountId | status |  .....

Table2: case reopen table

|caseId(FK)| casereopenId(PK)| caseReopenTime|

Table3: Alert table

Id (incrementing id) | alertId | accountId |

The lambda function tries to "create" a case in the database.

the create wrapper, generates a UUID for caseId.

The goal is :

check if an accountId already exists in case table.
if it does, then

check if status is OPEN
get the caseId for the accountId.
check if the caseId is present in case reopen table.

if above condition is false, then add an entry into the case table.

Thanks!


texts: Export SQL View to CSV flat file. SSIS job stuck

texts:
I created a database view with the desired data and format. I used SQL Server Import and Export wizard to create a flat file export procedure and saved it to SSIS package.

The data exports correctly but no matter what I do the SQL agent job eventually gets stuck without an error at around 3000-3500 row mark (348kb size file). Nothing I have tried gets around this issue. Unfortunately,  I have to ingest this data via CSV file and have no other option. I have about 100k in rows.

The datatype is mostly VARCHAR. I know it's stuck because when the job is left to run overnight no progress is made.

Any thoughts? One recommendation was to try to build SSIS package in Visual Studio to maybe get different results.


texts:
I am trying to install MySQL 5.5 on Solaris 10.
I executed the code useradd -r -g mysql mysql where a group called mysql is already created using groupadd command.

I get the following error :
UX: useradd: ERROR: invalid syntax.

Is -r option not availablr on Solaris ?
Am I missing something.
I am newbie to Soalris.


texts:
I'm searching how to do this and can't find it.

Here's my query:

SELECT
    S_Product.Name AS "Name"
    SUM(S_Item.Quantity) AS "QtySold"
FROM s_product
INNER JOIN S_Item ON S_Product.ID = S_Item.Product_ID
WHERE "QtySold" > 500
--(SUM(S_Item.Quantity)) cannot be used in a WHERE statement since it's a group function
GROUP BY S_Product.Name

Though this doesn't work I think you get my idea, how could I use the `"QtySold" value in the WHERE statement?


texts:
With mysqld --verbose --help, I got this cnf files

Default options are read from the following files in the given order:
/etc/my.cnf /etc/mysql/my.cnf ~/.my.cnf
The following groups are read: mysqld server mysqld-10.0 mariadb mariadb-10.0 client-server

With netstat -tln

Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State
tcp        0      0 127.0.1.1:53            0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:22              0.0.0.0:*               LISTEN
tcp        0      0 0.0.0.0:31416           0.0.0.0:*               LISTEN
tcp6       0      0 :::22                   :::*                    LISTEN

I want to change my Local Address 127.0.1.:53 to 127.0.0.:3036.How to do this?

EDIT

cat ~/.my.cnf
cat: /home/milenko/.my.cnf: No such file or directory

IT DOES NOT FIND THIS CNF FILE AT ALL.

milenko@milenko-desktop:~$ cat /etc/mysql/my.cnf
# The MariaDB configuration file
#
# The MariaDB/MySQL tools read configuration files in the following order:
# 1. "/etc/mysql/mariadb.cnf" (this file) to set global defaults,
# 2. "/etc/mysql/conf.d/*.cnf" to set global options.
# 3. "/etc/mysql/mariadb.conf.d/*.cnf" to set MariaDB-only options.
# 4. "~/.my.cnf" to set user-specific options.
#
# If the same option is defined multiple times, the last one will apply.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.

#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]

# Import all .cnf files from configuration directory
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mariadb.conf.d/

Should I add the port in this file?


texts: Storing math,physics equations in mysql Database

texts:
I am working on a project where different unicode characters has to be stored in table.
math equations, physics formulae , subscript  X2 , superscript X2.

greek characters like Link

I have made all tables collation and charset as utf16 , but problem I am facing is in data entry.

I have to manually enter data into tables because of some reasons, I am using sequel pro tool for data entry, many characters coming fine but subscript and superscript not coming properly, they will be treated as normal character. Also I am not sure whether sequel pro supports any kind of data or not, later I may face problem with some other characters. So is there any proper way to deal with this?

Also let me know whether utf16 is ok for collation and charset ?

Kindly let me know if any such tool which support all kind of data.


texts: Automatic Failover without data replication

texts: I have a need to be able to automatically failover my databases and most need data replication. However, one database does not need data replication and so, for performance reasons, we do not include that in the auto failover plan since it requires data replication. Is there a way to disable data replication for certain databases?


texts: Import an xlsx file to Oracle SQL Developer and interpret it as a table

texts:
I have an xlsx file with content in the form of a database table - first row is column names, and columns contain varchars2, integers etc.

Since VBA and Excel worksheet functions are too slow for it, I want to view and manipulate it in Oracle SQL Developer, Version 19.2.0.206 developer. Everything occurs locally, there is no existing database connection, and none available.

I attempt to do this by clicking "new connection" (below screenshot; in German), but it prompts me to enter information for a database connection that I cannot have, because there is none yet.

When I try to make up a username, password, and use the below seen default hostname, port etc., and click on test, it shows: I/O error: The network adapter could not establish the connection.

Is it possible, and how do I achieve, that there is a "dummy" "connection" that is not actually connected to anything existing, but allows me to turn a suitable xlsx file into a table for data manipulation?

I have access to MS Access, but I hate the interface and have more experience with Oracle SQL, so I would appreciate a solution with Oracle SQL


texts: Can I use only 1 user for SQL Server replication?

texts:
I want to setup transactional replication on sql server. By following the instructions of the ms replication preparation tutorial and the ms replication configuration tutorial, everything works fine.

My question is: Why all these 4 users? Will there be any trouble if instead of 4 I only use one windows user/db login for every step?


texts: Oracle - Auditing for only some top-level statements

texts:
I know that we can turn on auditing for all top-level statements using the following
audit all statements

I would like to continue logging only top-levels statements, but not all of them. For example, I dont want to audit alter system, create cluster etc.

Is this possible?


texts: Query performance of a NULL vs an empty ('') varchar in SQL Server 2012

texts:
I have 3 indexed fields in a query: int, int, and varchar(250).

The query performs well when all 3 conditions are specified with real values. The int columns always have values, but there are plenty of empty string varchar values. Queries with the empty string varchar parameter perform 2-3x slower than those that search a real string (e.g. 'hello'). The query against the varchar column is a straight WHERE clause (i.e. no LIKE, just =).

I've searched around a bit but really only seem to see academic type discussions around this and I, frankly, don't really care about how they can mean different things.  I only care about the performance of the queries against a NULL or empty string varchar column.

Is this empty string the cause of the slowness? Would a NULL in it's place improve things? I can easily turn existing empty strings into NULLs and put some new logic in to make sure empty strings are always put in as NULLs. I just figured I'd ask here to get the expert opinion on this.

I'll be toying around with this anyway but it'd be nice to get an outside view telling me if I'm just spinning my wheels on it, if that's the case.


texts: Access 2010 After Insert data macro to update another table

texts:
I have an Access DB with 3 tables.

Table 1
Appointments
Fields with Data that is not used anywhere else, but some of the information is inserted into 3 with a after insert Script.

Table 2
Usage Data of Salesmen
I need some of the fields from here and put them on Table 3 after the afterinsert-Script

example Row:

ID  Date      Month   NT-Account  Data
--  --------  ------  ----------  ---------
 1  1.1.1899  201409  Some User   (Numbers)

Table 3
Performance
(IDFS to the Appointment ID, Date of the Coaching, NT-Account, Personal Information)
(is from the insert Script and works well.

Now to the usage Data, it's daily and my seniors want to test the sum of this daily data with weekly usage, so i did a query which holds the information i want.

Agent   Login Date  Week    Sum1  Sum2  Sum3
------  ----------  ------  ----  ----  ----
*user*  01.01.1991  199101    10     2     0

So basically we just download the Usage data from a big data collecting Program and it's giving us the Infos we need.

Now to my problem.

I made a AfterInsert Macro which will automatically put some of the Appointments data in it , but i need some information from a different table. How can i set the macro to execute a update or a script?


texts: Fastest way to move MongoDB to a new server

texts:
I have to move a 200GB MongoDB from one server to another (both Windows 2016 OS).  During this operation, it is assumed that the accompanying program will be offline, so no need to worry about inconsistent data, but I need to minimize the downtime.  Diskspace is not an issue, but downtime is.
  I've tried testing it with mongodump and mongorestore, but that took almost 20 hours to complete, which seems pretty ridiculous.  I'm looking at a couple other options:

Use copydb
Take a snapshot of the disk and/or copy the db folder directly to the new server and point the installation to that by changing the path in the .conf file.

I'm guessing #2 would be fastest if it will work, but I haven't found any real confirmation that it works on a Windows platform.  Has anyone successfully done this?  If so, is it as simple as I described?  As mentioned, MongoDB and its accompanying program can be turned off completely for this operation.

Alternatively, can anyone give me a ballpark figure on how long copydb takes versus a mongodump/mongorestore operation?


texts: Looking for an efficient way to clone tens of millions of database records

texts:
A laboratory I work in is attempting to clone the open, biochemical database managed by PubChem found here.  (Please don't ask why, I am not in charge of this project, I am simply tasked with the cloning of the database) But their database contains tens of millions of records and the script that I currently have running is using the "READ DATA INFILE" instruction of MySQL to write the 720 thousand csv files we have of their database into our MySQL database... one by one.  At the current rate, the sheer amount of data being saved is going to make this script take roughly 240 days to finish, and we don't have that much time. The questions I have are

Is there a generally accepted way to quickly enter vast amounts of csv files into
mysql (over 650 GB of data)
If not, is there a database system better suited for this sort of task, perhaps one designed specifically for big data? (I'm no database expert, so forgive me if this question makes no sense)


texts: Misaligned Log IO Issues and Unmatched LUN sector sizes in AlwaysOn

texts:
I'm running into a little bit of a snag on a high log send queue in a 2014 SP1 Asynchronous Mode AlwaysOn Environment with a secondary replica in Azure.

For one of our higher transaction volume DB, we are seeing log send queue sizes up to nearly 2GB for extended periods. Initially we saw a CPU bottleneck on the secondary due to the sizing of the Azure VM, which we corrected. However we were still left with some high log queue sizes and slow send rates, coupled with respective high re-dos.

We traced this back to an issue with IO using the following error log message:
"There have been 112313856 misaligned log IOs which required falling back to synchronous IO.  The current IO is on file..." More specifically this has to do with unmatched disk sector sizes between our SAN and Azure LUNs.

This knowledge base article seems to address exactly what my issue is:
https://support.microsoft.com/en-us/kb/2936603

However its a year old and applies to CU5, which is about a year old, and we are about 8 patches past CU5. Additionally I can't find any official documentation from MS on trace flag 1800. Does anybody have an similar experience with either this error message, or information on trace flag 1800? If so was your issue corrected with this flag, or were you forced to match your LUN disk sector sizes between both replicas?

Thanks!


texts: How to force/require TLS connections on SQL Azure

texts: You can make a secure connection to a SQl Azure instance following these instructions from microsoft. However, this only works when the user specifies it in the connection string. How do I set SQL Azure to reject any insecure connections, should a user forget to set the option.


texts: Why might Greenplum get stuck in a loop invoking a web external table?

texts:
We have a new table partition maintenance system that went into production today, and it is failing.

Each partitioned table has a web external table associated with it, and the shell script that is run performs the partition creation, and also runs a "watchdog" process that monitors to see if the partition creation is causing a lock cascade.

The problem seems to be that Greenplum is repeatedly calling the shell script, but if we change the shell script then Greenplum still runs the same commands as if we had not changed it. It's as if it cached the contents of the shell script and will not update its cache. We have tried recreating the web external table, and we have tried restarting the database.

I will update this ticket with some code tomorrow, but we're pretty desperate for ideas! We have a few days in hand, but soon we will need to back out the new mechanism if we can't fix it, and we can't replicate the problem in any other environment.

Version 4.3.4.1.

We are working around the problem by removing all the web external tables and replacing them with plpythonu calls to the shell scripts and parsing stdout ourselves.

EDIT The problem may have been nothing to do with Greenplum, a script was either deployed incorrectly or was messed up whilst we were diagnosing the problem. Since the problem only occurs in production, and requires a database reboot when it occurs, we are staying well away from it and so cannot run any diagnostics.


texts: Return a record with PL/pgSQL function - to speed up the query

texts:
I have a non-forking game daemon written in Perl, which uses acync queries to write player stats into a PostgreSQL 9.3 database. But when I need to read something from database (like if a player is banned or if the player has a VIP status), then I use synchronous queries.

This makes the game stop for a short moment, until the value has been read from the database.

I can not rewrite my game daemon to use async queries for reading values (I tried, but it required too many changes), so my question is: would it make sense to combine several unrelated queries (that I need to make when a new player connects) to 1 procedure and how could I return several values at the same time to my Perl program?

My current queries all take a player ID as parameter and return 1 value:

-- Has the player been banned?
select true from pref_ban where id=?

-- What is the reputation of this player?
select
count(nullif(nice, false)) -
count(nullif(nice, true)) as rep
from pref_rep where id=?

-- Is he or she a special VIP player?
select vip > now() as vip from pref_users where id=?

-- How many games has the player played to the end?
select completed from pref_match where id=?

To combine the above queries I probably need a procedure like this one:

create or replace function get_user_info(_id varchar) returns XXX as $BODY$
    declare
        is_banned boolean;
        reputation integer;
        is_vip boolean;
        completed_games integer;
    begin

        select 1 into is_banned from pref_ban where id=_id;

        select
        count(nullif(nice, false)) -
        count(nullif(nice, true))
        into reputation
        from pref_rep where id=_id;

        select vip > now() into is_vip from pref_users where id=_id;

        select completed into completed_games from pref_match where id=_id;

        return XXX; /* How to return 4 values here? */

    end;
$BODY$ language plpgsql;

Please help me to declare the above procedure properly.


texts: Migrating EC2 SQL Server to HyperV On Premise

texts: I'm wondering if any of you have migrated SQL servers from EC2 to on-premise? I'm toying with doing backup and restore on new onprem server, however the file copy test took over 24 hours. Given that I've got a weekend to do this move/build I'm wondering if doing something like building a replication of the EC2 to an on-prem server would be a better idea.


texts: Trouble getting automysqlbackup to keep connection to MySQL server

texts:
I am trying to follow the instructions from the following page to set up backups for mysql. https://www.howtoforge.com/tutorial/mysql-incremental-backup-point-in-time-recovery/

However, once I get to the step where I run the automysqlbackup, I always get this error:

  ERROR 2013 (HY000) at line 1: Lost connection to MySQL server during query

It seems that despite this error, some backup files are generated.

However, I can't seem to figure out exactly what is causing MySQL to drop the connection. I have tried to increasing the max_allowed_packet to 64M, as well as setting up the max_connections to 1000, key_buffer to 32M, max_allowed_packet to 32M and thread_stack to 512K to no avail.

Has anyone else experienced this issue and figured out a way around it? Or is it even an issue?


texts:
Following query is an example of Lazy Union:

SELECT GREATEST(@found := -1, userId) AS userId
FROM user WHERE userId = 1
UNION ALL
SELECT userId
FROM user WHERE userNumber = 1 AND @found IS NULL
UNION ALL
SELECT 1 FROM DUAL WHERE ( @found := NULL ) IS NOT NULL;

It means that if the first query in the UNION doesn't return any data then the second query will access data lazily. But when I check the MySQL Execution Plan it shows that this query still runs the second query of the UNION even if the first query returns data. In my query the first query of the UNION returns data.

MySQL Execution Plan:

What is wrong with this query? Did I mess up with MySQL's Lazy Union or something else?

Following is my original query which work perfect:

SELECT u.userId, u.userPassword, u.userStatus FROM user u WHERE userId = 1
UNION ALL
SELECT u.userId, u.userPassword, u.userStatus FROM user u WHERE userNumber = 1;


texts: Optimize queries that need to be 100% ACID compliant but can tolerate latency in persistence

texts:
Introduction
I'm writing a trading program in C++. The program involves having user balances and orderbooks. The orderbook is basically a set of buy and sell orders. When submitting an order, say a buy order, the matching engine searches whether there's a sell order with that price or lower. If that's the case, a trade happen. A trade subtracts the quantities of the smaller order, and the remaining quantity is added to the order book for the next buyer/seller.
The context
When a user submits an order, the C++ program:

Reads the database to see whether the user has sufficient balance.
Performs the trade in memory (in the program)
Modifies the database where in one transaction, it reads the balance, reads the orderbook, modifies them and persists them + writes log entries in the database (2 entries).

As you see, for a trading program, there's zero tolerance for atomicity and consistency. Otherwise there will be double-spending.
However, I can tolerate caching with only one layer over the database, which will ensure consistency and atomicity (through programming) but will postpone persistence.
I'm using PostgreSQL for my program. Also I use indexes for all my accesses of the database. Changing indexing algorithm from the default only worsened the performance. There's very little normalization there. The only normalization I did is separating the balances table from the users table. Everything else is non-separable.
The problem
It's very slow. I can barely do 100 transactions/second on an Intel 4930k (8 core, 16 threads, overclocked to 4.5 GHz), and I need 1000 transactions per second at least.
I'm not sure where the problem is coming from, but I heard that databases are not so good at modifying data. So, how can this system be optimized without endangering ACID compliance?
My solution, about which I want to ask
I'd like to implement a software layer over the database, which is the only layer allowed to access the database directly. This layer will cache the database in memory and periodically flush all the changes to the database for persistence. But then doing this makes me feel I'm doing what the database is supposed to be doing.
Is this a common thing to be done? Am I planning the right solution?

Edits from comments:
I can afford to lose transactions that are in memory only. The reasoning is that a trade at any point of time should have happened or not happened. It doesn't matter if it happened in a certain moment or one second after that. My question is kind of checking whether caching this way is valid solution and whether it's worth it. It seems like a valid solution. I'm just not sure how much it might help and why databases don't do that (or aren't configurable to do that)?
Apparently I misunderstand the word normalization above. I understand normalization to be "splitting tables to make them more meaningful", so for performance, it's worse to normalize (again, I may have misunderstood it). The only table that is split is the one I mentioned above, so instead of writing to 4 tables I'm writing to 4 tables and reading the users table. That's why I'm not very convinced that splitting that table is the reason. But maybe I have to revisit the design. I'm not an expert there.
I'm not sure I can provide DML because I'm using ODB as ORM. I'll have to see whether I can extract them. I'll provide the DDL statements soon when I get where the system is (in a few hours).
Does using an ORM mean it's hopeless? If I lose 20-30% of performance, I could live with it (for now). Should I expect losing 10 times (from 1000 tps to 100) the performance compared to manually writing queries? Seems excessive.
pgbench -t 10000 results in 2350 transaction/s on a single thread (my application uses a single thread because every order book can take a single thread, which is fine).
Edit 2:
I chose the answer below because it implements the second layer solution I'm looking for. But I still don't have the best number of transactions per second, which means I still have to study the schemas and operations I have.


texts: Disk usage of integer column vs boolean column in Postgres

texts:
I saw this question Bit vs. Boolean columns.

I'm asking myself the same for Postgres: does a single digit integer column occupy the same disk space of a boolean one? In big tables (~50 columns x ~50 million rows) which one perform best?
How can I find this out?


texts:
I have mongoDB in 3 DC's.

DC1 - 3 Nodes  with priority 300
DC2 - 3 Nodes  with priority 200
DC3 - 3 Nodes  with priority 100

Questions :-

Say, N/W is broken b/w DC1 & DC2. But, still DC1 is able to communicate with DC3 and DC2 is also able to communicate with DC3. But DC1 will not be able to DC2.          Will it create 2 Primaries here, like 1 PRIMARY from DC1 and 1 PRIMARY from DC2?
What if there is no Priorities? Only DC3 will become PRIMARY?

Thanks


texts:
Is there any way of viewing an account's remaining resources that are allocated to it? I setup an account that's allowed 7200 queries an hour. At any point, could I then run a query to find out how many remaining queries it's allowed?

MySQL must be storing this information somewhere as FLUSH USER_RESOURCES; will reset the counters however, I tried a few variants such as SHOW USER_RESOURCES and they don't seem to display anything. I've also hunted around information_schema and mysql tables.

Is it just not possible to retrieve that information?


texts: English version of ISO 3166-2 subdivision data

texts:
I have a MySQL table containing ISO 3166-2 location subdivision data.  The data contains non-English names with accent characters that I would prefer to have as English names/characters.
Examples include:
'Ajmān
Ghōr
Kāpīsā
Abū Z̧aby [Abu Dhabi]

As you can see with the last example, there are instances which contain square brackets with the English name.
Any ideas where I can find such a version with English names and characters/no-accents?


texts: SQL Table Design Schema - To Normalize or Not?

texts:
I am considering a table schema design for a project.
The design involves a Many-to-Many Relationship.

The 2 related tables are:

a) Class

b) Subclass

A Class can have many Subclasses, and a Subclass can have many Classes.

Here is my Normalized design:

Is it best to always do this design? The relationship will have 20 rows max. Is it then better to create a single denormalized table as it will then be much easier to update and delete data?

In the Normalized design I would have to create a View to join the tables to display data.
And also to create sprocs or triggers to update the base tables.

The denormalized design involves updating/deleting/selecting from a single table.

Denormalized design:

Is it always best practice to use normalized design?


texts:
I'm a programmer, I work on an application where our members (about 3,000) enter in data into a form on a quarterly basis.  The form itself has about 120 fields, but every quarter some new fields are added and some are removed.

Is it a better for performance to have a data table with four columns (member_id, quarter_id, field_id, value) where each member adds 120 or so rows every quarter, or to have a flat table with 120+ columns, where new columns are added for new fields, and each member only adds one row per quarter?

Currently we're doing it like the latter, and we have about 67,000 records in it.  I feel like the former is cleaner and simpler from a programming perspective, but it'd mean expanding that to around 8 million rows, and increasing by 350k+ every quarter.


texts: Defining a GIN index across two columns/arrays with similar data

texts:
Let's say we have a Postgres table contacts, each record having a bunch of labeled email addresses (pairs of labels and emails)-- one of which is the "primary".

This is stored like:

id primary key
email text
email_label text
metadata jsonb

emails array
email text
label text

For example, a record might look something like:

{
  id: 1,
  email: 'a@a.com',
  email_label: 'a',
  metadata: {
    emails: [
      {
        email: 'b@b.com',
        label: 'b'
      },
      {
        email: 'c@c.com',
        label: 'c'
      }
    ]
  }
}

Given this storage pattern, we want to be able to find a record by any of its email addresses.

The naive query would look like:

SELECT id
FROM contacts
WHERE
  email = 'my@email.com' OR
  metadata -> 'emails' @> '[{"email": "my@email.com"}]'

Is there any way to create an index that speeds up this operation significantly? It would need to update automatically in response to changes to the records, and ideally index across both the text column and the nested JSONB column.

The specific use case here would be able to do lookups by email address efficiently and quickly, without overhauling this structure or creating a new relational table.

I believe the solution involves using a GIN index and this question mentions jsonb_path_ops but I'm not sure how to wrangle all the pieces together.


texts: When dropping a partition on InnoDB table does mysql flush pages in the whole table or only in a partition to be dropped?

texts:
When issuing ALTER TABLE .. DROP PARTITION p1 mysql have to flush pages to disk. My question is: does mysql flush pages in the whole table (in each partition) or only in a partition to be dropped? MySQL server 5.7
table partition exp. is: PARTITION BY RANGE (UNIX_TIMESTAMP(dt))


texts: MySQL Workbench shows running query but query not in process list

texts:
I am having an issue where the same query sometimes completes in 600 seconds or less, but sometimes lags for multiple hours. MySQL Workbench shows the query as 'Running...', and initially I can see the query in the processlist. But after a significant amount of time after the query should have already returned results, I check the processlist and the query is no longer executing--yet Workbench still shows that it is running. What is happening to the query and why doesn't Workbench immediately give me an error code?
Sometimes if I wait a sufficiently long time to trigger a timeout I do get the 2013 error.
I am using a MacBook Pro running Big Sur connecting to a remote MySQL server through a VPN.
Could this be explained by poor network connectivity?


texts: Stored procedure runs forever, but inner query runs fine

texts:
If I run exec pr_BuscaLog -2, it will run forever (days, without end).

But, if I just copy the query and run it on its own, it runs in seconds.

Updating statistics did not help.

--pr_BuscaLog -1
CREATE procedure [dbo].[pr_BuscaLog] (@qtdDias int)
as
begin
declare @cmd as nvarchar(4000)
declare @banco as varchar(100)
declare @dataLimite as datetime

UPDATE: When I run EXEC PR_BUSCALOG, it start to run, and then stops.


texts:
I have a master - slave configuration with 9.4 version, but there is no WAL streaming replication configured. Customer simply copy xlog files on network share and replica applies it.

I need to run pg_dump from replica although I face with error.
What do I do:

SELECT pg_xlog_replay_pause()
Run pg_dump on replica server
SELECT pg_xlog_replay_resume()

Immediately I receive after pg_xlog_replay_pause()

ERROR:  recovery is not in progress

And when the command of pg_dump starts I see:

pg_dump: [archiver (db)] query failed: ERROR:  cannot assign TransactionIds during recovery
pg_dump: [archiver (db)] query was: SELECT pg_export_snapshot()

Questions:

Does it mean I can't do pg_dump from replica? I have a feeling that in case of wal streaming replication it would be possible. Correct me if I'm wrong.
I can't find any information for SELECT pg_xlog_replay_pause() / SELECT pg_xlog_replay_resume() works only with wal streaming setup.
Can someone tell if this true?

Thanks in advance.

Customer's setup is:
They copy xlog to network share and  replica recovery.conf consist of

standby_mode = 'on'
restore_command = 'if exist A:\\Logs\\From_Master_DB\\%f (copy A:\\Logs\\From_master_DB\\%f  %p) else (exit /b 1)'
archive_cleanup_command = '"C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  A:\\Logs\\From_Master_DB %r && "C:\\Program Files\\PostgreSQL\\9.4\\bin\\pg_archivecleanup"  D:\\PG-SQL\\data\\pg_xlog %r'
recovery_target_timeline = 'latest'


texts: mysql separate email and cellphone from user table to avoid extra unused-space usage

texts:
i have a question about putting some string (fixed-length and variable (which Might be always null)) into a table or separate them (Because of space-usage even if it's null) ...

for example, my user register with cellphone number or an email address

this is for cellphone number:

   cellphone_country_code (SMALLINT) (UNSIGNED)
   cellphone_num          (BIGINT)   (UNSIGNED)

and this is for email address:

   email_address         (CHAR(60))

Now which options are better ? (i want to know that i have to separate these data from user table or not)

option 1

user:

     id                      .....
     cellphone_country_code  (SMALLINT) (UNSIGNED)    (Default = 0)
     cellphone_num           (BIGINT)   (UNSIGNED)    (Default = 0)
     email_address           (CHAR(60)) (NULL)        (Default = NULL)

or (option 2)

user:
         id                      .....
         ....

user_cellphone:

         user_id                ......      (unique-index)
         cellphone_country_code  (SMALLINT) (UNSIGNED)
         cellphone_num           (BIGINT)   (UNSIGNED)

user_email:

         user_id                ......     (unique-index)
         email_address          (CHAR(60))

for option 1, if user registers with cellphone number and never want to add an email address, we have an empty-unused 60-BYTE space for email address (which we not going to use it for that user !)
so i think it's bad to have a column with extra bytes that you are not going to use !!! but it takes your ram space !

but in option 2, there is no extra unused-space because we add cellphone number or email address in separated tables so for example, if email address is not provided, there is no any empty-unused 60-BYTE space !!!!!

am i right ? what do you think ? which option is better ?
if we have a column (string with fixed-length or variable-length) and we don't use it, still it's get ram space (based on it's length i mean) ???


texts: How to reclaim space taken by an index that partially built and was terminated by a power outage

texts:
I'm running postgres (postgis) 9.4.2 on a mac (10.10.4).

I've got a couple big tables (several TBs).

During an index build on one of them that takes about a week, I watched the available HD space drop as you'd expect to nearly the point at which the index would be finished  when a power outage lasted longer than the battery unit and the system went down. I had buffers off, and fillfactor=100 during the build since it's a static datasource. On reboot, the available space left on the drive is exactly where it was at nearly the end of the index build. Vacuum analyze doesn't free the space.

I tried dropping the table and re-ingesting, and that didn't drop the space. Now I'm at a place where I don't have enough space to build the index.

Are the files generated during the index build stuck in some limbo where they can't be removed by the system because of the way the machine went down during the power outage?

When I look at the table sizes + indexes in the db (which is the only data on that drive) they add up to about 6TB.  The drive is 8TB, and there is less than 500GB left on the drive, so it seems there are about 1.5TB lost somewhere which is about the size that index would have been.

Any ideas?


texts: Compare the structure of two MySQL Databases

texts:
I have two MySQL databases that are very similar to each other. How can I find out the differences in tables, and the differences in columns in each table?

the databases are in different schema.
It's only the structure I want to compare, not the data.


texts: How to measure the size of InnoDB's fulltext index

texts:
How can I determine how much space MySQLs fulltext index is taking up, using innodb?

Solution would ideally be by querying an information_schema table rather than looking at the filesystem.


texts: OCIEnvCreate failed to create environment handle after installing oracle_fdw

texts:
I have installed oracle_fdw successfully. After I create extension oracle_fdw and
foreign table it shows the following error. Is this a server configuration problem?

--env
Oracle :    10.2.0.1
PostgreSQL:  9.1.3

--create foreign server
skytf=#   CREATE SERVER oracle_srv
skytf-#   FOREIGN DATA WRAPPER oracle_fdw
skytf-#   OPTIONS (dbserver '//192.168.1.30:1521/MANUA');
CREATE SERVER

skytf=# grant usage on foreign server oracle_srv to skytf;
GRANT

--create mapping user
skytf=# CREATE USER MAPPING FOR skytf
skytf-# SERVER oracle_srv
skytf-# OPTIONS (user 'read_only', password 'read_only');
CREATE USER MAPPING

--create foreign table
skytf=# CREATE FOREIGN TABLE ft_test_1 (
skytf(#  id       integer,
skytf(#  name     character varying(20)
skytf(#  ) SERVER oracle_srv
skytf-# OPTIONS (schema 'ocp', table 'test_1');
CREATE FOREIGN TABLE

skytf=# \c skytf skytf
skytf=> select * from ft_test_1;
ERROR:  error connecting to Oracle: OCIEnvCreate failed to create environment handle
DETAIL:


texts: How do I copy databases from Postgres 8.4 to Postgres 9.1?

texts:
My trac server recently started having issues. It was running a very outdated version of Ubuntu, and I was unable to properly use apt-get to correct the issues I was having. So instead I backed up the machine and did a clean install of Ubuntu 12.04.

Now I have a new server with Postgres 9.1 properly installed and running. I want to get my old Postgres 8.4 database from the backup copied over and setup on the new install. How do I go about doing this?

What I have done so far is to copy the old Postgres 8.4 data and bin directories from the backup (/var/lib/postgresql/8.4/main and /usr/lib/postgresql/8.4/bin) to /tmp/postgres.old and /tmp/postgres-bin.old on the new install. I stopped Postgres 9.1 using service postgres stop and ran the command:

sudo -u postgres /usr/lib/postgresql/9.1/bin/pg_upgrade \
  --old-datadir=/tmp/postgres.old/main/ \
  --new-datadir=/var/lib/postgresql/9.1/main/ \
  --old-bindir=/tmp/postgres-bin.old/postgresql/8.4/bin \
  --new-bindir=/usr/lib/postgresql/9.1/bin

As described in the Postgres documentation. This seemed like it would work great, except I received the error:

Performing Consistency Checks
-----------------------------
Checking current, bin, and data directories                 ok
Checking cluster versions                                   ok

connection to database failed: could not connect to server: No such file or directory
        Is the server running locally and accepting
        connections on Unix domain socket "/var/run/postgresql/.s.PGSQL.5432"?

unable to connect to old postmaster started with the command: "/tmp/postgres-bin.old/postgresql/8.4/bin/pg_ctl" -w -l "/dev/null" -D "/tmp/postgres.old/main" -o "-p 5432 -c autovacuum=off -c autovacuum_freeze_max_age=2000000000" start >> "/dev/null" 2>&1
Failure, exiting

The issue seems to be that it's trying to start the old server, and that doesn't work because it's so very old and not installed properly. Trying to start it manually reveals the problem:

root@mission:/tmp# sudo -u postgres /tmp/postgres-bin.old/postgresql/8.4/bin/postgres
/tmp/postgres-bin.old/postgresql/8.4/bin/postgres: error while loading shared libraries: libssl.so.0.9.8: cannot open shared object file: No such file or directory

So, How can I restore old Postgres 8.4 databases into a new Postgres 9.1 install, and update them?


texts: Creating an index on a really busy table

texts:
We have a table with a lot of insert\select every time ( like 100 machines inserting\updating every second).

What is the best way to create an index on a table that can't be locked not even for a second?

If I create the index, i'm sure it will lock the requests, and I can't do it.

It's a big table with 1 million rows+.


texts: MySQL cache is not getting hit by subqueries

texts:
I noticed something weird on MySQL query_cache behavior and I would like to know if this is a normal behavior.

Let's say I have an item table

 ID | Item
-----------
 1  | Item_1
 2  | Item_2
 3  | Item_3

The query that will be used here is : SELECT id FROM items

The first time I use it I have a +1 in my status Qcache_inserts, the second time I use it I have +1 to Qcache_hits. Perfect it's working fine.

Now if I use this query : SELECT COUNT(1) FROM (SELECT id FROM items) my_table

The subquery SELECT id FROM items is supposed to already be present in the cache, but I can't get any hit.

Aren't subquery simple thread that executes first ? Then why it is not hitting my query cache ?


texts:
I'm new to MongoDB.
Lets say I have a collection of blog post, and a collection of authors. I want to search for posts e.g. by topic, and language. But I only want results by certain authors, e.g. by age span. What's the correct way to do this? Should I search posts first, then iterate the result, and make a new search based on that? Or can I make a chained search somehow?


texts: How to update collections and restore while doing mongorestore?

texts:
is there any way that i can update the collection and restore  the same from dump created by mongodump cmd .
when we do mongorestore , it directly restores from the dump , but my requireemnet is to update some data in the dump and then restore .

Thanks in advance .


texts: What connection do internal sessions belong to in MS SQL Server?

texts:
By querying

select * from sys.dm_exec_connections

I do not see corresponding connections for internal sessions, about the existance of which I know from sys.dm_exec_sessions DMV.
My questions is whether do they need a connection to run and how can I find out their connection ID, if the connection exists(because I can't join on dm_exec_connections)?

Thanks!


texts: What does the error means: `SQLSTATE[HY000]: General error: 7 SSL SYSCALL error: EOF detected (SQL: )`

texts:
On my Amazon RDS Postgresql database connection I receive the following error:

SQLSTATE[HY000]: General error: 7 SSL SYSCALL error: EOF detected (SQL: ^sql^)

So I try to understand what this error mean. As far as I understood it that my database client has established a database connection that for some reason has been unexpectedly terminated. Is that right?

So I wonder will this error also mean that another error has happened in my database connection that has failed to be captured in client side?


texts: How do I copy a SQL Server Database Schema and Objects between servers using VS2019

texts:
I've inherited a database with about 1500 tables, and 3600+ objects overall. I want to copy the schema - and just the schema - across to a different server, where I can get it into source control (TFS), and generally work out what to do with the various bits of it. Here is what I have tried so far:

Schema compare and update target using VS2019. Even using a reasonably specced development machine, VS became unresponsive.

Tasks > Generate Scripts in SSMS. The source database uses 28 different filegroups that are not available on the destination server. So far as I can see, it's not possible to turn off the setting to include filegroup when generating the script.

SSMS Copy SQL Server Objects task. Again failed because of the filegroup issue.

VS2019, new database project, and Import Database. This pulled all 3600 objects across like a charm, but there are thousands of build errors. This being the case, I can't publish it. I set the build action = none for all the objects, but then it didn't deploy anything either.

Is it possible to deploy a database project that won't build? It doesn't seem an unreasonable request. Given that I have not yet been granted eternal life, it seems unlikely that all the build errors will get fixed. But meanwhile, I would like it to be under version control, and to be able to use the bits of it that do work.
I could create the filegroups on the destination server, and, to get myself out of a hole today, I probably will. But I am a big fan of database projects, and would like to be able to make use of the rich versioning and comparison features it offers.


texts: Comparing id s of same column and assign value of one to another

texts:
I have table with

id name
1  aa
1  bb
2  ff
3  df

I want to check if value is repeated in column "id" then assign the "name" value to next matching ids row
Expected:

id name
1   aa
1   aa
2   ff
3   df

Can any one suggest how this can be achieved?


texts: PostgreSQL JSONB Execution Plan with high Offset

texts:
How does PostgreSQL work i.e. the execution plan on JSONB query with high Offset number and how to improve the performance?
Considering the below "Test Query", help me to clarify the following:

As the SubYear is 2050 which is a future date on which there are no cases available yet does it require a full table scan or is there any alternative approach to tune this ?

When the same query is modified to retrieve the data with "SubYear" < 2050, there are lot of results i.e. 35k+. How does PostgreSQL works on it when the Offset is set to a high number lets say 30k ?

Test Query :
select
    *
from
    sub_test df,
    jsonb_array_elements(json_data) jba
where
    (jba->>'name')::text = 'SubYear'
    and (jba->>'value')::numeric > '2050';


texts:
I have done an incorrect update and hence I restored the DB with a backup from the previous day. I have not taken a copy of the corrupted data before restoring the backup. Is there any possibility to get back the data lost between the creation of back up file and restoring the file?

Thanks for saving my job


texts: What's the best setup for a MySQL multi master replication for a POS in a restaurant?

texts:
We're in the process of implementing a point of sale (POS) solution where we have heavily modified an existing open source POS. The original POS was never created with replication/synchronization between terminals and we want to add this.

We are using MySQL 5.7 and require a solution to do the following:

each POS terminal needs to operate independently of other terminals or back office server - i.e. have it's own local database and still function, even if the network goes down
multi-master synchronization - all POS terminals should reflect the current state of the system

Example 1: you should be able to start an order on one POS terminal, and finish/modify it on another terminal.

Example 2: If you start an order for a restaurant table on one terminal, another waiter will see that that table is occupied, from another POS terminal.

scalable to n terminals - some customers will have 1 POS terminal, some 3 and some 100. The solution needs to scale to any number of POS terminals

I've looked at the following solutions:

MySQL Cluster
MySQL Replication
Maria DB Replication
Maria DB Gallera Cluster

It seems that MySQL semi synchronous replication could work, but I'm not sure this is the best setup.

I'd like some direction in terms of:

what technology to use
how best to set this up


texts:
I need to store an unknown amount  of fields. Some are numbers (like IDs, zip codes and times), some are short strings (like names and states), and some are longer strings (such as descriptions). All fields are related to an ID.

I suppose I'd go with some kind of normalization, but what's the most optimal way? Have a table for each data type?

Thank you in advance!