Does Oracle 19c support Ubuntu? I am trying to install Oracle 19c, and It needs X-display so I typed xhost +, but it doesn't work. Any ideas to solve this problem? Also, I want to know that Ubuntu literally support Oracle 19c.

Oracle does NOT support Ubuntu. From the documentation:
https://docs.oracle.com/en/database/oracle/oracle-database/19/ladbi/operating-system-checklist-for-oracle-database-installation-on-linux.html#GUID-E5C0A90E-7750-45D9-A8BC-C7319ED934F0
The following Linux x86-64 kernels are supported:

Oracle Linux 8.1 with the Unbreakable Enterprise Kernel 6:
5.4.17-2011.0.7.el8uek.x86_64 or later Oracle Linux 8 with the Red Hat Compatible kernel: 4.18.0-80.el8.x86_64 or later

Oracle Linux 7.4 with the Unbreakable Enterprise Kernel 4:
4.1.12-124.19.2.el7uek.x86_64 or later Oracle Linux 7.4 with the Unbreakable
Enterprise Kernel 5: 4.14.35-1818.1.6.el7uek.x86_64 or
later Oracle Linux 7.7 with the Unbreakable Enterprise Kernel 6:
5.4.17-2011.4.4.el7uek.x86_64 or later Oracle Linux 7.5 with the Red Hat Compatible Kernel: 3.10.0-862.11.6.el7.x86_64 or later

Red Hat Enterprise Linux 8: 4.18.0-80.el8.x86_64 or later

Red Hat Enterprise Linux 7.5: 3.10.0-862.11.6.el7.x86_64 or later

SUSE Linux Enterprise Server 15: 4.12.14-23-default or later

SUSE Linux Enterprise Server 12 SP3: 4.4.162-94.72-default or later

Ubuntu is an entirely different branch of Linux, and no matter how many people claim they have hacked it to work, it is NOT supported and results are NOT guaranteed.


How do the Oracle DB instance and the local listener communicate? It seems this my other question needs some background:

Basically, on Windows, how do the Oracle DB instance process and the listener process communicate? Do they use TCP? Is the configuration of this process done via listener.ora or via some other setting in the DB? Does the listener service "contact" the DB service or the other way round?

A rough sketch and/or very specific pointers to Oracle documentation would be really helpful.

On Unix:
Oracle is multi-process database. It does not use threads (on the contrary to other databases). Some of the processes are background ones (Oracle internal) the other ones are user sessions processes. They all communicate via IPC (messages, shared ram).

When user opens a TCP connection to listener, the listener process forks and then executes(exec) oracle binary. This new process becomes a new Oracle session process. In this case Oracle protocol is wired - i.e. it uses only a single TCP connection (port 1521).

There is also more complicated way(especially on RAC). Listener instructs Oracle background process(PMON) and this one forks a new Oracle process, listening on a new dynamically allocated port. Listener then sends a "redirect" message to client process. Client then connects (via new TCP) to the session process.
This redirect message can point onto another cluster node(having different IP address)
PS: due this redirect, you should not use NAT and also both client and server should use the same DNS server(or at least server serving the same records).
I think this redirect message contains a hostname, not IP address.


Efficiently querying a very large MySQL table with C# I am issuing a relatively simple SQL query in my C# app:

select * from myTable where myValue > 10 order by myValue;

There are ~180 million rows in the table and there is an index on myValue. When I run the query in MySQL Workbench, it returns the first 1000 rows in 0.015 seconds.

When I run the query in C#, it times out.

I suspect that the MySqlDataReader and/or the MySQL engine is attempting to find all matching rows before returning.

What I really want is read one row at a time from my C# code. I want to queue up this query and just pull rows off the top one at a time as they're needed.

Is there an appropriate programming paradigm for this in MySQL?

Workbench tacks on a LIMIT clause.  And, since you have the perfect index for such, it stops short.

How many rows are you expecting?  What will you do with that many rows?

It is probably possible for a C# client to configure the connection show that the rows will be delivered as they are found, thereby showing you some output before it timesout.  Such is rarely used because delivering giant resultsets is rarely practical.


Restoring data into a replicated table after failed purge process We have a monthly purge process that purges data from a few tables using a somewhat complex SSIS package.

Up until this time everything is running smooth and fine without any issues.

However, if the purge process does fail then my only resort would be to restore those tables back to the state they were in before the purge stated.  We take backups of the affected tables before we start purging data, however my concern is these tables are in replication and in case if I have to restore the data back do I need to just use import/export or insert into and get done with it or do I need to remove the table out of replication, add the data and then add the table back and generate a snapshot for these articles only?

What if I want to restore the complete table. How am I suppose to do it when the tables are part of transactional replication?

I'm going to assume the table in question is a replication publisher, not a replication subscriber (since you shouldn't be changing data in table that has been replicated from another source).

While you can just delete and then re-insert all the fixed data on a replicated table, if it is large enough that's going to generate tons of commands to replicate over to the subscriber, which (in environments I've worked in) may take hours or days to sync across.

So it is usually much easier to:

Remove the table from replication
Fix the table data, via bulk inserts or updates or SSIS or however you want
Re-add the table to replication
Do not re-initialize the subscription (you just want to snapshot that one table, not the whole database)
Run the snapshot job. It will push a snapshot of just the newly re-added table.
Run the distribution job

One further note: if you have already clogged up your replication with 100 million pending delete/insert/update commands, you can still execute these same 6 steps. The moment you remove the table from replication, all those pending records for that table will disappear from the queue, this usually happens very quickly and will cause a bogged-down subscriber to spring back to life.


is it possible to using index in unix datetime colum in MySQL 5.7 when using function is it possible to using index in unix datetime colum in MySQL 5.7? I define my database table like this:
CREATE TABLE `report_envelope_app_list` (
  `created_time` bigint(20) NOT NULL,
  `user_id` bigint(20) NOT NULL
)

the created_time store unix timestamp,now this is my SQL:
 select max(user_id) as user_id
 from report_user
 group by user_id, date_format(FROM_UNIXTIME(created_time/1000), '%Y-%m-%d')

but now the colum created_time contains function and could not using the index, is it possbible to using index, I have to add a column to store date like %Y-%m-%d? I tried to add a generated column like this:
ALTER TABLE report_user
ADD COLUMN statistic_date varchar(16)
GENERATED ALWAYS AS date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d') STORED;

but failed.

using generate column and created a index on it:
ALTER TABLE report_user
ADD COLUMN statistic_date varchar(16)
GENERATED ALWAYS AS (date_format(FROM_UNIXTIME(statistic_time/1000), '%Y-%m-%d')) STORED;

then created index:
CREATE INDEX report_user_userid_staticdate_idx
ON report_user(user_id,statistic_date);


What are the practical limitations on a column family in Cassandra? In Cassandra, it's not recommended to have more than a few thousand column families, let's say 2,000 for the sake of argument. In cases where more than 2,000 types of data need to be persisted, one approach would be to shard multiple unrelated types of data into each column family.

For example, a single CF could contain Orders, Invoices, and Customers, provided their row keys were distinct (e.g. prefixed with the object type, i.e. the keys of a single CF could include both Order|1234 and Customer|1234). A second CF could contain say Addresses, LineItems, and OrderTypes. Given the basic feasibility of this approach, what are the practical limits on it? For example, what would be wrong with putting all 10,000 types of object into a single CF? As far as I can tell from the Cassandra wiki, there is no hard limitation on the size of a CF.

I'm not a fan.  It's about as good an idea as creating a relational table named OrdersOrCustomers with columns defined for both.  The storage-engine penalty is slightly lower in Cassandra because of the sparse-cell storage under the hood, but it's still bad practice.

This bites you later when you want to map/reduce over your data; each task will have to scan over all your data, and filter out the rows that don't match what you're actually interested in (e.g., customers).  And good luck making sense of the statistics that Cassandra tracks per-CF.  ("Is this CF the source of 80% of my application reads because of the order data? Or because of the customer sessions it's combined with? Or the other five data types I threw in?")

If you absolutely positively need tens or hundreds of thousands of CFs?  Even then I'd rather run Cassandra without arena allocation, than mutilate my data model like this.


"Site Offline" MySQL server failing to start and stop I'm hosting a few sites on linode and have encountered a strange problem. For the last two years the site has been running perfectly fine, and now randomly all the websites on the server go to the "site offline check the settings.php" page. No changes have been made to the website at all recently.

when i try go do mysqld stop it says busy for a while then finally says failed, while doing start results in an instant fail.

Many of the threads I googled suggest the hostname is off in the settings.php but since the website has been up for two years I don't think this can be the case.

I haven't had to troubleshoot mySQL before, but if i remember correctly the log file I should be looking at is Hostname.err. In there, it has a large chain of errors concerning InnoDB. The one listed as fatal error says:

InnoDB: Fatal error: cannot allocate memory for the buffer pool

I would appreciate any suggestions, and if there are log files that would help let me know.

Edit: Requested information.
1) When I examine the CNF, it appears that all lines involving innoDB are commented with #. This means that it has always been this way as I have not modified it.

2)mysql  Ver 14.14 Distrib 5.5.14, for Linux (i686) using readline 5.1

3) It looks like there is a giant jump in the IO rate every time I try to start the database / before the websites die. How do i go about clearing those cache tables if they have become too unmanageable and is there any risk of losing anything?

Third Edit (Now with datadir):

How much RAM is on the VM ? 512MB

How much space does datadir have ?

Size: 20G  Used: 19G   Available  0 Use Percent: 100%

What is the size of ibdata1 ( ls -lh /var/lib/mysql/ibdata1)? 114M

FYI: If anyone looks here with the same problem in the future, My issue was the the binary log files had consumed my entire disk space. See here http://systembash.com/content/mysql-binary-log-file-size-huge/

OMG !!! Look at the output of df -h

Size: 20G Used: 19G Available 0 Use Percent: 100%

YOU HAVE NO DISK SPACE !!!

Get more diskspace freed up or have linode increase diskspace.

According to MySQL 5.0 Certification Study Guide

Page 408,409 Section 29.2 Bulletpoint 11 says:

  If you run out of disk space while adding rows to a MyISAM table, no
  error occurs. The server suspends the operation until space becomes
  available, and then completes the operation.

In reality, mysqld is not dead. It is waiting for free diskspace to continue whatever it normally does. Even during startup, if mysqld makes a temp table, it is a MyISAM table. No space left on the disk? No problem. MyISAM just patiently waits for you to make free space.

If you have an error file like /var/log/mysql/log, clear its contents like this:

echo -n > /var/log/mysqld.log

If the error file in /var/lib/mysql/my-machine.err then do this:

echo -n > /var/lib/mysql/my-machine.err

Give it a Try !!!


How to install Slony for 64-bit PostgreSQL 9.3 on Windows? I have installed 64-bit version of PostgreSQL 9.3.5 from EnterpriseDB installer. Now I want to install Slony-I through Application Stack Builder, but it offers only 32-bit version of Slony, incompatible with 64-bit PostgreSQL. My OS in Windows 7 64-bit.

Is there any easy way how to install it? Or do I have to build it from source? Or is there no way to do it except for move to 32-bit?

OK,

I posted a link to my compiled Slony 2.2.4 for Windows 64-bit under postgres 9.3.6. However, it was deleted I'm assuming because people may not trust my compile. Hence, here's the link to the Slony-I site with the instructions that I followed to compile Slony.

It's section 3.1.6. Building on Win32

http://www.slony.info/documentation/2.2/administration.html#BUILDINGWIN32


Does the order of the column name assignation in the join part have an impact on indexes in MySQL? Does the order of the column name assignation in the join part have an impact on indexes ?

For example, if I have an index on table X column Y, and I write:

SELECT * FROM B LEFT JOIN X ON X.Y = B.Y

Then the Y index from table X is used.  However, if I write the same query like this:

SELECT * FROM B LEFT JOIN X ON B.Y = X.Y

will the index still be used?


Double entry accounting schema design We are implementing a software that allows to send "virtual" money between user internally. We have started our database with a single entry as follow (simplified table). We only one record one transaction, where the user initiated the transaction (send money) will be recorded. So it will always be a debit.

TransactionLog
------------------
TransactionLogID
UserIDFrom
UserIDTo
CurrencyID
CurrencyAmount
Remarks
TransactionDateTime

Then a requirement changes that say we need a double entry accounting schema in our database. Then i have make a new table as follow.

TransactionLogDoubleEntry
----------------------------
TransactionLogDoubleEntryID
TransactionLogID
UserID
CounterPartyUserID
CreditOrDebit
CurrencyID
CurrencyAmount
Remarks
TransactionDateTime

My question:
1) Am i doing this in a correct direction that i add TransactionLogDoubleEntry table with credit/debit it means it already become a double entry accounting schema?

2) Should i remain the table TransactionLog? Or using TransactionLogDoubleEntry is sufficient enough?

3) If i were to remain TransactionLog table, is it necessary that i duplicate the currencyID, currencyAmount, remarks, TransactionDateTime in TransactionLogDoubleEntry? Or is that enough to get the corresponding value from TransactionLog table.

Most double entry accounting systems would implement this using 2 tables. One table that is the "header" table, which stores common fields & a second "lines" table that stores the actual detail of the transaction.

In case you didn't know, in double entry accounting, the sum of all transaction lines in a given transaction always adds up to zero.

Anyway, based upon your current design, I'd do something along the lines of:

Transaction
------------------
TransactionID
CurrencyID
Remarks
TransactionDateTime

TransactionLines
----------------
TransactionID
LineNo
UserID
DebitAmount
CreditAmount

To transfer $5.00 from UserID 100 to UserID 250, you'd have the following entries:

Transaction:

10000, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines:

10000, 1, 100, 500, 0
10000, 2, 250, 0, 500

You asked about a case where there might be multiple transaction lines:- To transfer $9.00 from UserID 100, with $8.50 going to UserID 123 & $0.50 going to UserID 456, you'd have the following entries:

Transaction:

10001, 'USD', 'Test transaction', '2019/09/15 12:34:56'

TransactionLines:

10001, 1, 100,   900,   0
10001, 2, 123,   0,     850
10001, 3, 456,   0,     50

It doesn't matter from a mathematical/accounting point of view what order the lines are in, as long at the total is always net 0.

Proper accounting systems would have a G/L Account column in each line, which would dictate where the transaction posted to, along with the posting period and other essential information at the header level.

I'd seriously consider learning some accounting fundamentals if the system you're developing is dealing with real money.

This is obviously at a high level. If you require further help, please just ask.


Finding char "\x00" in a varchar field Im trying to find rows that contain certain unprintable characters and encounter some inconsistent behaviour of various string functions. The field is of type VARCHAR. Here are the queries:

select ascii(substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 0

select charindex(char(0), substring(AddressTail, 17, 1)) from Address where ID = 1934484 returns 1 which is expected

but

select charindex(char(0), AddressTail) from Address where ID = 1934484 returns 0 which is quite strange.

So, how to find the rows that contain byte zero? SQL Server version is Microsoft SQL Server 2016 (SP2-CU9) (KB4515435) - 13.0.5479.0 (X64)

how to find the rows that contain byte zero?

SQL server somehow badly understands \x00...

Maybe this trick will help you slightly?

create table test (id int, txt varchar(8));

insert into test values
(1, '123' + char(0) + '456'),                -- in the middle
(2, '123' + char(0)),                        -- at the end
(3, char(0) + '456'),                        -- at the beginning
(4, '12' + char(0) + '34' + char(0) + '56'), -- in the middle
(5, '123456');                               -- absent

select id, pos
from test
cross apply ( select number + 1 pos
              from master..spt_values
              where type='P'
                and number < len(txt) ) numbers
where substring(txt,pos,1) = char(0)
order by id;

Output

id | pos
-: | --:
 1 |   4
 2 |   4
 3 |   1
 4 |   3
 4 |   6


Is the MySQL JSON data type bad for performance for data retrieval? Let's say I have a MySQL JSON data type called custom_properties for a media table:

An example of the json data stored in the custom_properties column could be:

{
 "company_id": 1,
 "uploaded_by": "Name",
 "document_type": "Policy",
 "policy_signed_date": "04/04/2018"
}

In my PHP Laravel app I would do something like this:

$media = Media::where('custom_properties->company_id', Auth::user()->company_id)->orderBy('created_at', 'DESC')->get();

This would fetch all media items belonging to company 1.

My question is that lets say we have 1 million media records, would this be a bad way to fetch records in terms of performance? Can anyone shed some light on how MySQL indexes JSON data types?

From the MySQL official docs:

  JSON documents stored in JSON columns are converted to an internal
  format that permits quick read access to document elements. When the
  server later must read a JSON value stored in this binary format, the
  value need not be parsed from a text representation. The binary format
  is structured to enable the server to look up subobjects or nested
  values directly by key or array index without reading all values
  before or after them in the document.

I think we can safely assume the performance would be worse than using regular columns.

The way to index JSON attributes is to create a virtual generated column for the particular attribute you want (company_id), then index the virtual column, and use that column to filter your query.


Generating script in SQL server taking too long I am making backup for my tables in the database and I am generating scripts (from task) in SQL Server Management Studio.

There's about 30-40 tables that I am interested to backup including constraint,indexes, etc. I'm not doing a full backup. There's like 300 tables in the database. The entire database is so big and doing a full backup would take way too long, hence only backup certain tables.

However, in "Save or Publish script" screen, it's stuck there for very long claiming the tables are "preparing". Is there any solution to this?

Is it normal to wait that long? (1 hour)

An alternative to scripting objects using SSMS is to use Powershell.

Open a Windows command prompt and fire up Powershell

[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | Out-Null

#The SQL Server instance to want to connect to - This is the default instance on my computer
$server = New-Object Microsoft.SqlServer.Management.Smo.Server(".");

#The database you want to script objects from
$database = $server.Databases["Test"];

#Instantiating the Powershell Scripter object
#so we can easily define how we want to script the object
$scripter = new-object Microsoft.SqlServer.Management.Smo.Scripter($server);

#Do we want Batch Terminators in the script? (GO statements)
$scripter.options.ScriptBatchTerminator = $true

#Where do we want to store file containing the scripted objects?
$scripter.options.FileName = "C:\Scripts\ScriptedTables.sql"

#Don't display the objects we're scripting, just output them to the file
$scripter.options.ToFileOnly = $true

#Script any permissions defined on the objects
$scripter.options.Permissions = $true

#Script all Dri objects
#For more innformation about DriAll - (https://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.scriptingoptions_properties.aspx)
$scripter.options.DriAll = $true

#Script table triggers
$scripter.options.Triggers = $true

#Script table indexes
$scripter.options.Indexes = $true

#A comma separated list of the tables you want to script
$table = $database.Tables["account","DateRange","DateDimension"];

#Invoke the scripting method to produce the output script file
$scripter.Script([Microsoft.SqlServer.Management.Smo.SqlSmoObject[]]$table);

For more information about the available Powershell scriping options, check here.


Database Default Locations doesn't persist When I set the default location for the Data & Log in the Server Properties in SQL Server 2012, and click Ok, it resets back to the previous value immediately - if I reopen the Server Properties, the values has been reset.

How do I get it to persist? I'm trying to set it to a location on a separate HDD. The Backup folder location has been changed without issue.

1) Make sure the account running the SQLservice should have the required rights on the directory you are wishing to change:

2) Also, you can give a try with below T-SQL

You can also do the same with T-SQL and here is the T-SQL code to do the same.

/* I am trying to change to D drive in below example*/

USE [master]
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultData', REG_SZ, N'D:\DATA'
GO
EXEC xp_instance_regwrite N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer', N'DefaultLog', REG_SZ, N'D:\DATA'
GO

Most important:-After Changing the Default Locations

You must stop and start the SQL Server service to complete the change


Why Sqlcode 100 does not come with exception I have following query in a PL\SQL procedure on Oracle 10.2:

This is the code

LOOP
  BEGIN

    SELECT a.poid_id0 into v_acc_account_poidid0
    FROM account_t a
    WHERE a.poid_id0=i_acct_id0
    FOR UPDATE OF a.poid_id0 NOWAIT;

    EXIT WHEN sqlcode = 0;

  EXCEPTION

    WHEN resource_busy THEN

      BEGIN
        v_max_retry_times_counter := v_max_retry_times_counter + 1 ;
        IF (v_max_retry_times_counter>v_max_retry_limit) THEN
          RAISE_APPLICATION_ERROR (ERROR_SELECTING,'Resource Busy with Nowait Option.',TRUE);
          EXIT;
        END IF;
        DBMS_LOCK.sleep(2);
      END;

    WHEN OTHERS THEN

      BEGIN
        RAISE_APPLICATION_ERROR (ERROR_SELECTING,'ORACLE ERROR DESCRIPTION'||sqlerrm ,TRUE);
        EXIT;
      END;

  END;
END LOOP;

The statement returns sqlcode 100 and does not throw an exception.

From the Oracle documentation I understand that the error code is accompanied by an exception. What may be the reason behind this behaviour?

The documentation says the error code 100 is DATA NOT FOUND; we have data for the select query.

In this context does DATA NOT FOUND mean the select is failing or the lock is not available?

Any help much appreciated.

I will explain the sequence of actions.

The select returns no rows and throws an exception NO_DATA_FOUND
The WHEN OTHERS exception handler handles the exception.
the Raise_Application_Error should raise the new exception.

Problems:

The use of the sqlcode variable is not wise, you should remove it as it is not within an error handler.
The EXIT statements after the RAISE_APPLICATION_ERROR's are dead code.
I cannot quite see how you could see the ora-00100 error with this code and still be correct in what you wrote.


Will OPTIMIZE TABLE have any impact on my data? I'm having issues with a database that is getting quite slow. The analyzer in phpMyAdmin recommends that I run OPTIMIZE TABLE on my tables.

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE? Will indexes and primary keys still remain the same? Are there areas of the database that will be slower after the optimization?

OPTIMIZE TABLE basically does three(3) things

Shrinks the data pages
Shrinks index pages
Computes Fresh Index Statistics

Conceptually, OPTIMIZE TABLE operates something like this on mydb.mytable
USE mydb
CREATE TABLE mytabletmp LIKE mytable;
INSERT INTO mytabletmp SELECT * FROM mytable;
ALTER TABLE mytable RENAME mytablezap;
ALTER TABLE mytabletmp RENAME mytable;
DROP TABLE mytablezap;
ANALYZE TABLE mytable;

However, based on

the table mydb.mytable
the datadir being /var/lib/mysql

let's look at Storage Engine Specifics
MyISAM
The MyISAM table mydb.mytable is physically stored in three files

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.MYD (data)
/var/lib/mysql/mydb/mytable.MYI (indexes)

The conceptual description of running OPTIMIZE TABLE would copy data pages and index pages into a new .MYD and .MYI. This will eliminate having fragmented pages in either file.
InnoDB
There are two viewpoints to take into consideration
Viewpoint #1 : innodb_file_per_table disabled
With innodb_file_per_table disabled, all data pages and index pages for every InnoDB table are stored inside the system tablespace (better known as the file ibdata1).
When you run OPTIMIZE TABLE on an InnoDB table that is stored in ibdata1, all the data and index pages are written contiguously so all the pages for the table are together. The bad news is that it makes ibdata1 grow rapidly.
Viewpoint #2 : innodb_file_per_table enabled
With innodb_file_per_table enabled, all data pages and index pages for every InnoDB table are stored outside ibdata1. Here is the physical storage of mydb.mytable:

/var/lib/mysql/mydb/mytable.frm (table structure)
/var/lib/mysql/mydb/mytable.ibd (data and indexes)

When you run OPTIMIZE TABLE on an InnoDB table that is stored outside of ibdata1 (system tablespace), this perform the conceptual steps that lead to shrinking the .ibd file.
I have written about this before

Oct 29, 2010 : Howto: Clean a mysql InnoDB storage engine? (StackOverflow)
Mar 25, 2012 : Why does InnoDB store all databases in one file?
Apr 11, 2012 : How do you remove fragmentation from InnoDB tables?
Dec 21, 2012 : Is there a progress indicator for OPTIMIZE TABLE progress?
Jan 07, 2013 : Database space doesn't match ibdata1 size

Now, for your original questions...

But before doing so, I would (of course) like to know if there is anything that can happen to the data in the tables, or if this operation is entirely harmless.

I just explained how OPTIMIZE TABLE works for both storage engines. This can be time-consuming depending on the size of the data and indexes. Outside of an Earthquake or Power Outage, running OPTIMIZE TABLE does not harm to the data and indexes get rebuilt.

Are there pros and cons I should take into consideration when using OPTIMIZE TABLE?

Ditto

Will indexes and primary keys still remain the same?

Yes

Are there areas of the database that will be slower after the optimization?

No way. Querying a table with no fragmentation can only be faster


Translating the Relational Algebra output I have the following relation Jobs(name, company, salary).

P1(salary) = πsalary(σcompany='pwc'(Jobs))
P2(salary) = πsalary(ρT1(s)(P1) thetaJoin(s>salary) P1)
P3(salary) = P1 − P2

I know that P1 just selects the salary of all people works in pwc.

I am having a hard time figuring out what P2 indicates.

I know that ρT1(s)(P1) renames P1 into T1 and the salary attribute to s. But then I am confused with the thetaJoin(s>salary) part because I thought s and salary would be the same.

P3 would just be the difference between P1 and P2

The final relation

Final(name) = πname(Jobs thetaJoin(salary>s) ρT2(s)(P3))

will depend on what P2 means.

Please help me doing this translation.

P2(salary) = πsalary(ρT1(s)(P1) thetaJoin(s>salary) P1)

From the Wikipedia entry for Relational Algebra:

The result of the θ-join is defined only if the headers of S and R are disjoint, that is, do not contain a common attribute.
The simulation of this operation in the fundamental operations is therefore as follows:
R ⋈θ S = σθ(R × S)

To ensure the headers are disjoint, salary must be renamed (as 's' here). To ensure the relations are uniquely named for this self-join, P1 must also be renamed (as 'T1' here).
In rough SQL terms:
DECLARE @P1 table (salary integer NOT NULL);
INSERT @P1 (salary) VALUES (1), (2), (3);

WITH T1 (s) AS (SELECT DISTINCT salary FROM @P1)
SELECT DISTINCT
    P1.salary
FROM @P1 AS P1
JOIN T1
    ON T1.s > P1.salary;

-- or, closer to the fundamental operations:

WITH T1 (s) AS (SELECT DISTINCT salary FROM @P1)
SELECT DISTINCT
    P1.salary
FROM @P1 AS P1
CROSS JOIN T1
WHERE T1.s > P1.salary;

dbfiddle

| salary |
| ------ |
|      1 |
|      2 |

P2 therefore contains all the salary values that are smaller than some other salary value.
P1 minus P2 therefore contains the top salary at PWC.
See also Finding a Maximum Value with Relational Algebra


Downloading files based on most recent date and timestamp I have a requirement to download zipped files from an SFTP site using C# and Script Task within SSIS.

We have multiple files on the server with the same name, but with different date and timestamps.

Is there a way download the files based on most recent date and timestamp?

As the answer by @MarkSinkinson shows, you can use WinSCP .NET assembly.

There is an official example on WinSCP site for downloading the most recent file.

As it shows, to select and download the most recent file, use:

// Get list of files in the directory
RemoteDirectoryInfo directoryInfo = session.ListDirectory(remotePath);

// Select the most recent file
RemoteFileInfo latest =
    directoryInfo.Files
        .Where(file => !file.IsDirectory)
        .OrderByDescending(file => file.LastWriteTime)
        .FirstOrDefault();

// Any file at all?
if (latest == null)
{
    throw new Exception("No file found");
}

// Download the selected file
session.GetFiles(RemotePath.EscapeFileMask(remotePath + latest.Name), localPath).Check();

For a full code, see the link.

See also the article on using the WinSCP .NET assembly from SSIS.

(I'm the author of WinSCP)


if i understand you correctly, here will do:

select user, sum(RT)
from test2 a
inner join ( select distinct(time) from test2 order by time desc limit 2) t
on a.time=t.time
group by user

The 'N' for number of latest batch is determined by the limit 2 in the subquery t.

For example, if i run the above query, showing data for the last 2 batches, the output is:

Dailymotion 8.11
Facebook    1.44
Google+     0.98
Instagram   0.15
Metacafe    0.80
Twitter     0.75
Youtube     0.48

But the query above won't show any users who don't have data in the last 'N' batch. If you need to show those users as well (as '0' RT), you will have to join the above query with your users table.

P/S: The test2 table in the query above contains the exact data as in your first table in your post.


Delete all data for a Single Vendor in the Dabatase I have a Vendor table with vendor_Id as the PK and 10 other tables which have reference to Vendor.vendor_Id.

Now, if Vendor chooses to close their account, we need to delete all the data in the other 10 tables.

Is it a good idea to have ON CASCADE DELETE in 10 tables for the vendor_Id field or shall we delete the vendor data one table at a time?
Shall we write a Stored Procedure for the same ?

Caveat: Am American that uses Microsoft SQL Server. My opinions are based on my experiences, and may need to be modified to suit your own needs.

I would maybe look at keeping the vendor_id intact, and just replace the information on that record with some filler information. In designing the database, an effort should have been made to keep the vendor information contained to only a few tables, and anywhere else that references that vendor would have been through its id field as a foreign key. This would keep the integrity of the data, especially if you have something like sales records, and really CAN'T delete a bunch of data that is vital to your own business. A vendor table could look like the below.

Vendor_id   Vendor_name   Vendor_phone
----------------------------------------
123         BigSales Co.   555-5555
234         [redacted]     [redacted]


Can I create a computed column that requires input to select? I have a scenario in a very old and very large application, where I have a table representing a type of resource:

CREATE TABLE resource (resource_id INT, name NVARCHAR(4000))

This table is selected from in hundreds of different places, including stored procedures and dynamic SQL in the application code.

A team recently updated this resource's name to be localized, and their approach is pretty straight forward. There is a new table containing the localized names, and a 'default' language ID on the resource table, for when the name isn't localized for the requested language:

-- Foreign keys omitted
ALTER TABLE resource ADD default_language_id INT
CREATE TABLE resource_local (resource_id INT, language_id INT, name NVARCHAR(4000))

Most procs have an @user_language_id parameter, so the logic for choosing the name to return is simple: take resource_local.name matching language_id = @user_language_id if it exists, otherwise resource_local.name matching language_id = resource.default_language_id if it exists, otherwise take resource.name.

Unfortunately, this turns the logic to select the correct name into something like this:

SELECT ISNULL(ISNULL(exact.name, default.name), res.name)
FROM resource res
LEFT JOIN resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = @user_language_id
LEFT JOIN resource_local default ON default.resource_id = res.resource_id
    AND default.language_id = res.default_language_id
WHERE res.resource_id = @resource_id

All of the hundreds of places that try to select resource.name are having to be updated with this logic, which has turned this project into a massive effort across the entire organization, as each team needs to update their SQL to use this logic. This also causes maintainability issues, as any new developers dealing with this table need to know that they can't just use the name column.

It's too late now, but I'm curious: is there any better way to approach this, so that selecting the name column from resource will just 'do the right thing' based on the @user_language_id variable (if it exists)?

I'm not sure if it's possible to do this so that none of the references to the resource table need to change. It seems like the fact that a language_id is needed is a fundamental change that all calling code will need to be aware of.

However, it is possible to design this in a way that the resource can be queried in either of the following simple ways. One of these options might have been an easier change to make and maintain across so many different places.

Table-valued function

Using an Inline Table-Valued Function, we can provide the following syntax.

SELECT resource_id, language_id, name
FROM dbo.resourceTVF(@resource_id, @language_id) r

Here is an example of how to create the function. It's essentially the same query from your question, but with the alias default changed to def (default is a SQL Server keyword).

-- Create the Table-Valued Function
CREATE FUNCTION dbo.resourceTVF (@resource_id INT, @user_language_id INT)
RETURNS TABLE
AS
RETURN
SELECT @resource_id AS resource_id,
  @user_language_id AS language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource res
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = @user_language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
WHERE res.resource_id = @resource_id
GO

View

You could rename the resource table (e.g., to resource_base) and then create a resource view in order to provide the following API:

SELECT resource_id, language_id, name
FROM dbo.resource
WHERE resource_id = @resource_id
  AND language_id = @language_id

The primary downside is that the view definition needs to CROSS JOIN all resources and languages before using applying the LEFT JOIN to the local and default resources. Even so, this is going to be a fairly efficient plan with 4 singleton seeks assuming that you have the proper indexes.

CREATE VIEW dbo.resource WITH SCHEMABINDING AS
SELECT res.resource_id,
  lang.language_id,
  ISNULL(ISNULL(exact.name, def.name), res.name) AS name
FROM dbo.resource_base res
CROSS JOIN dbo.languages lang
LEFT JOIN dbo.resource_local exact ON exact.resource_id = res.resource_id
    AND exact.language_id = lang.language_id
LEFT JOIN dbo.resource_local def ON def.resource_id = res.resource_id
    AND def.language_id = res.default_language_id
GO

Full script

Here is a full script where I implemented both of these proposals, loaded a small amount of fake data, and ran a few test cases. At least for these test cases, both approaches yield the desired results and use a loop-seek based plan.

I think that the inline table-valued function is probably the approach the I'd try first. Note that you can use CROSS APPLY to "join" to the table-valued function if you need more than one resource at a time.


limiting sql server backups inside .bak file I have a job scheduled to run a few times everyday. The job backups my databases, like:

BACKUP DATABASE [databasename]
TO DISK = N'd:\sqlserver\backup\databasename.bak'

I can track the backups in the file by running:

RESTORE HEADERONLY FROM DISK = N'd:\sqlserver\backup\databasename.bak'

And then I can restore a database file by using something like:

RESTORE DATABASE databasename
FROM DISK = N'd:\sqlserver\backup\databasename.bak'
WITH FILE = 1455,
RECOVERY,
MOVE 'databasename_data' TO 'c:\...',
MOVE 'databasename_log' TO 'c:\...'

But I have thousands of backups, and I'm reaching my storage limit.

Because I don't need so many backups, I'm wondering if there is a easy way to limit the backup file to keep only the last 90 backups.

Any tips?

UPDATE:

I have just one .bak file with n backups inside. I can read a specific backup using the withfile option in the restore statement. The bellow answers are supposing I have multiple .bak files. What I need to delete is a outdated backup inside the .bak file.

The easiest solution would be using a maintenance plan task to clean up old backup files. However, you could consider scripting your way out of this, using something like the following script:

--====================================================================
CREATE PROCEDURE [dbo].[usp_DeleteOldBackupFiles]
@path nvarchar(256),
@extention nvarchar(10),
@age_hrs int
AS
BEGIN
SET NOCOUNT ON;

DECLARE @DeleteDate nvarchar(50)
DECLARE @DeleteDateTime datetime

SET @DeleteDateTime = DateAdd(hh, -@age_hrs, GetDate())

SET @DeleteDate = (Select Replace(Convert(nvarchar, @DeleteDateTime, 111), '/', '-') + 'T' + Convert(nvarchar, @DeleteDateTime, 108))

EXECUTE master.dbo.xp_delete_file 0, @path, @extention, @DeleteDate, 1
END
--============================
-- xp_delete_file information
--============================
-- xp_delete_file actually checks the file header to see what type of file it is and will only delete certain types such
-- as database and log backups. I suppose they expanded this to certain types of log files as well but as you say this is
-- not documented by MS. Just be aware that it will not delete just any file type

-- First argument is:
-- 0 - specifies a backup file
-- 1 - specifies a report file
-- (I'm not sure what the difference between a "backup file" and a "report file" is, since you specify the extension of files
-- you're deleting with the third argument.)
--
-- Fifth argument is whether to delete recursively.
-- 0 - don't delete recursively (default)
-- 1 - delete files in sub directories
--====================================================================

Credit: http://www.sqlservercentral.com/Forums/FindPost1034057.aspx

There's also xp_delete_file:

master.dbo.xp_delete_file


Disable CertificateRequest in ServerHello in MySQL/MariaDB I configured SSL (not mutual SSL) in MySQL. I created a user like this.

CREATE USER 'username'@'%' IDENTIFIED BY 'passowrd';
GRANT ALL ON db_name.* TO 'username'@'%' REQUIRE SSL;

But, in ServerHello, I get this.

*** CertificateRequest
Cert Types: RSA, DSS
Cert Authorities:
<Empty>
[read] MD5 and SHA1 hashes:  len = 9
0000: 0D 00 00 05 02 01 02 00   00                       .........
[Raw read]: length = 5
0000: 16 03 02 00 04                                     .....
[Raw read]: length = 4
0000: 0E 00 00 00                                        ....
localhost-startStop-1, READ: TLSv1.1 Handshake, length = 4
*** ServerHelloDone

This forces the client to send the client certificate. Since I don't want mutual SSL, I want to avoid this.

Basically, I want to remove CertificateRequest part from ServerHello. Is there a way to achieve this by some confguration? Appreciate your help.

You could revoke SSL from the user in one of two ways:

METHOD #1 : Recreate User without REQUIRE SSL

DROP USER IF EXISTS 'username'@'%';
CREATE USER 'username'@'%' IDENTIFIED BY 'passowrd';
GRANT ALL ON db_name.* TO 'username'@'%';

METHOD #2 : Hack mysql.user

UPDATE mysql.user SET ssl_type='' WHERE user='username' AND host='%';
FLUSH PRIVILEGES;

CAVEAT

Check release notes on MariaDB concerning METHOD #2.

I think there is a bug doing FLUSH PRIVILEGES; in older versions.

GIVE IT A TRY !!!


Extract Data Between Delimeter In NVarchar Field I have a nvarchar(1000) data type field that holds data like this
name_Test_1
 name_Test1_2
 name_Test2_3

Now the data I want to extract is what is between the _ so the data I would want extracted would be

Test
Test1
Test2

I tried the below syntax, but since the string has two _ in it then the data I have returned is 1, 2, 3 - how should this query be changed so that I get the middle string?

Declare @Red Table (fname nvarchar(1000))

Insert Into @Red (fname) Values
('name_Test_1'), ('name_Test1_2'), ('name_Test2_3')

Select
RIGHT(fname,CHARINDEX('_',REVERSE(fname))-1) as RightHalf
FROM @Red

This seems to work:

declare @name varchar(20) = 'name_Test_1'

select SUBSTRING(@name,CHARINDEX('_',@name,0)+1, (CHARINDEX('_',@name,CHARINDEX('_',@name,0)+1)) - CHARINDEX('_',@name,0)-1)


How to debug locked transaction on a remote system? I have a remote database that is currently throwing errors. On a primary key of type BIGSERIAL it says no new rows can be created because a value for the primary key already exists (the value itself is in the mid tens of thousands).

My problem is that we have very little information, just the error. Is there a way to rollback that transaction from a remote location? Is there a way to dump the db contents, not just export the tables, to check what the db engine is doing?

No, you can't undo, rollback or reverse a commit.

STOP THE DATABASE!

(Note: if you deleted the data directory off the filesystem, do NOT stop the database. The following advice applies to an accidental commit of a DELETE or similar, not an rm -rf /data/directory scenario).

If this data was important, STOP YOUR DATABASE NOW and do not restart it. Use pg_ctl stop -m immediate so that no checkpoint is run on shutdown.

You cannot roll back a transaction once it has commited. You will need to restore the data from backups, or use point-in-time recovery, which must have been set up before the accident happened.

If you didn't have any PITR / WAL archiving set up and don't have backups, you're in real trouble.

Please have a look here for Detailed Information:


Easiest way to duplicate rows The closest I can find to what I want to do is here, but my inexperience gets me lost. Basically, I want to copy a number of records, change one column and insert them back into the same table (so its almost a duplicate of the original data).

More info: Table menuship is a hash table for a food menu (thus each row in the table will identify a menu category (starter, maincourse or desert for example), and products (fish and chips).

Table structure:

menuship3, CREATE TABLE `menuship3` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `headhash` char(40) DEFAULT NULL,
  `menucardhash` char(40) DEFAULT NULL,
  `menucathash` char(40) DEFAULT NULL,
  `producthash` char(40) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `headhash` (`headhash`)
) ENGINE=InnoDB AUTO_INCREMENT=52 DEFAULT CHARSET=utf8

In programming circles, I would say I want to "fork" my data...

If my table content was like so:

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 1,  aaa    ,   aaa       ,   aaa      ,   aaa
 2,  aaa    ,   aaa       ,   aaa      ,   bbb
 3,  aaa    ,   aaa       ,   aaa      ,   ccc
 4,  aaa    ,   aaa       ,   bbb      ,   ddd
 5,  aaa    ,   aaa       ,   ccc      ,   eee
 6,  aaa    ,   other     ,   xyz      ,   fgi
 7,  aaa    ,   other     ,   xyz      ,   fgh

I want to duplicate all records with menucardhashaaa (rows 1-5), so I will end up with a table containing 12 records. The extra records will have new menucarhashqqq instead of menucardhashaaa.

id, headhash, menucardhash, menucathash, producthash
-----------------------------------------------------
 8,  aaa    ,   qqq       ,   aaa      ,   aaa
 9,  aaa    ,   qqq       ,   aaa      ,   bbb
10,  aaa    ,   qqq       ,   aaa      ,   ccc
11,  aaa    ,   qqq       ,   bbb      ,   ddd
12,  aaa    ,   qqq       ,   ccc      ,   eee

The result in effect means I have records that have similarity, records 1-5 are similar to records 8-12 - the differences being the id and menucardhash columns.

I was just going to select the records within PHP, change menucathash and send them back to the db, but I wondered if there was an SQL query that could do this for me and thus reduce cpu cycle overhead.

Is this possible via one or two queries, or am I better off having PHP carry the weight?

Thanks in advance...

This is a very simple INSERT .. SELECT query.

The id is not included in the column list as it has the AUTO_INCREMENT property and will get values automatically. You only need to replace 'aaa' with the menucardhash value that you want to duplicate from and 'qqq'  with the new value:

INSERT INTO menuship3
    (headhash, menucardhash, menucathash, producthash)
SELECT
    headhash, 'qqq', menucathash, producthash
FROM
    menuship3
WHERE
    menucardhash = 'aaa' ;


Does MS SQL Server have generate_series function Does MS SQL Server have Series Generating Function(s) (aka generate_series) like Postgresql has. If not is there a basic way to implement the function? Timestamp version is preferred

I found this question which is an old one. Maybe there is a better solution in new versions.

A fairly common method of creating a series in T-SQL consists of using a CTE as the source, something like:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT t.n
FROM t
ORDER BY T.n;

The query above will return an ordered list of values from 0 to 99.

For dates, you can implement it like this:

;WITH t AS
(
    SELECT n = v2.n * 10 + v1.n
    FROM (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v1(n)
    CROSS JOIN (VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9)) v2(n)
)
SELECT DATEADD(DAY, t.n, '2019-01-01')
FROM t
ORDER BY T.n;

Which provides a list of dates starting at January 1st, 2019, and continuing for 100 days, ending on April 10th, 2019.

There are numerous other ways of generating a set like this, including generating a "numbers" table.


How and where do I upload a mysql database? I am new to Ubuntu (server edition) and mysql.

How do I upload (import) an existing database with a MYSQL or (mysql.zip) extension that was downloaded from a web host (cPanel?)

I do not have any of the UI bells and whistles such as zPanel (although I tried to install it.) I did install phpMyAdmin with Ubunto but I do not know how to access it from a remote computer )In the past, I only used it with cPanel.)

My site will reside in /var/www.

I have installed mysql server Dist. 5.5.32.  Thanks. :)

If you have downloaded the database as an SQL file then you can just import it using the command prompt and the mysql command.

Do it in these simple steps:

Make sure the SQL file is unpacked as a text file with SQL commands inside
Verify you're able to login to MySQL using the command prompt. You can simply type # mysql -p -u username database_name. You'll be asked for password and, if correct, you'll be accepted to your database.
If you haven't created any new database to unpack your backup file in you can do it now (inside the MySQL command prompt) by typing create database MyNewDatabase;
Now that you know you can login to the database you can "feed" the downloaded SQL file by changing the command in this way: # mysql -p -u username database_name < /tmp/download/db_backup.sql. Make sure you're not inside the MySQL command prompt but out in the normal command prompt when typing this command. You can simply type exit or quit to leave the MySQL command prompt.
The database will then be imported into your local database. If it's a big database running this last command might take quite some time.

Some things to think of:

If you have a new installation and haven't added any users to MySQL then root is the username to use. But it's always recommended to add a separate user account in MySQL that you can use from scripts ect.
In my examples above I specified a database name. But if your backup script starts with use someotherdb; then someotherdb will be used instead of the database name you specify in the mysql command. To get around this, either open up the SQL backup file and remove the first line with use, or, when you create your backup, untick the box saying Include using (or something similar).
This is just how to install the database. You talked about "web site" also and that's a different thing. However, you need to update your website scripts to point to the new local database that you've created now.


Get all dates in the current month report from three table with date wise sum and balance I have three seperate table without any foregin key or relations each..

I need a Summary like this

Now, what and how will be mysql query.. I am very new web developer... please kindly lets me know details. pleas help me on this issues

refference links :: https://stackoverflow.com/questions/6676653/mysql-query-two-tables-get-data-in-date-wise-or-user-wise

Here is one approach I've used in the past. I think this will help you with the basics. I've only included 2 of the tables, adding the third should help you to understand the answer rather than copy/paste it.

(You'll need to change the first line to add references to sum(Costings), and add a 'union' 'select ... from bank_mobile_costing', in addition to adding a reference 'Costing' column to the 'Investments' and 'Earnings' select statements.)

select date,sum(Investments),sum(Earnings),sum(Investments+Earnings) as Balance from
(
select date,amount as 'Investments',0.0 as 'Earnings' from bank_mobile_investments
union
select date,0.0,amount from bank_mobile_earnings

) mydata
group by date
order by 1

It's worth my noting here that this will NOT display a list of calendar dates in the date column, but rather it displays a complete list of all the dates which exist in your 3 tables; I don't know if that's appropriate to your needs.

On the subject of your running total column at the end, this is a convoluted subject but a quick google search yields the following stack overflow result which gives a couple of methods of doing it, and there are some interesting points raised in the comments about the advantages or drawbacks of the methods.

Calculate a Running Total


unixODBC - [IM002][unixODBC][Driver Manager] Data source name not found and no default driver specified Folks,

We installed unixODBC-2.3.7 on Aix 7.1 to connect to Oracle 11g. After setup odbc.ini and odbcinst.ini files and tried to connect with isql, we had the following error:

# isql -v ORACLE
[IM002][unixODBC][Driver Manager]Data source name not found and no default driver specified
[ISQL]ERROR: Could not SQLConnect

Before execute the isql command, we ran the following export commands:

export ODBCSYSINI=/usr/local/etc
export PATH=$PATH:/usr/local/bin
export TNS_ADMIN=/u01/app/oracle/product/11.2.0.4/client_1/network/admin
export ODBCINI=/usr/local/etc/odbc.ini
export LIBPATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1
export LD_LIBRARY_PATH=/u01/app/oracle/product/11.2.0.4/client_1/lib:/usr/local/lib:/u01/app/oracle/product/11.2.0.4/client_1

And odbcinst -j command too:

unixODBC 2.3.7
DRIVERS............: /usr/local/etc/odbcinst.ini
SYSTEM DATA SOURCES: /usr/local/etc/odbc.ini
FILE DATA SOURCES..: /usr/local/etc/ODBCDataSources
USER DATA SOURCES..: /usr/local/etc/odbc.ini
SQLULEN Size.......: 4
SQLLEN Size........: 4
SQLSETPOSIROW Size.: 2
Illegal instruction(coredump)

Our /usr/local/etc/odbc.ini file is:

[ODBC Data Sources]
ORACLE =  Oracle ODBC Test

[ORACLE]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle11gdriver
DSN = ORACLE
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = ORAODBC.customer.com
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = user
Password = pass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F

And our /usr/local/etc/odbcinst.ini is

[Oracle11gdriver]
Description     = Oracle ODBC driver for Oracle 11g
Driver          = /u01/app/oracle/product/11.2.0.4/client_1/lib/libsqora.so

Finally, our tnsnames.ora is:

ORAODBC =
   (DESCRIPTION =
     (ADDRESS_LIST =
       (ADDRESS = (PROTOCOL = TCP)(HOST = customer.com.br)(PORT = 1521))
   )
   (CONNECT_DATA =
      (SERVICE_NAME = ORAODBC.customer.com)
   )
 )

Any idea what is going on?

I had similar issues in RHEL 5/7. To resolve it, I installed Oracle Instant Client, and used it, I think unixODBC is designed to be used with instant client, and not with full oracle client.

Oracle Instant Client for RHEL (you can use 11/12/18/19 versions):

sudo yum localinstall -y oracle-instantclient18.5-basic-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-sqlplus-18.5.0.0.0-3.x86_64.rpm
sudo yum localinstall -y oracle-instantclient18.5-odbc-18.5.0.0.0-3.x86_64.rpm

unixODBC from source:

curl -o unixODBC-2.3.7.tar.gz http://www.unixodbc.org/unixODBC-2.3.7.tar.gz
tar -zxvf unixODBC-2.3.7.tar.gz
cd unixODBC-2.3.7
./configure --prefix=/ --sysconfdir=/etc
make
sudo make install

#/etc/odbcinst.ini
[Oracle]
Description     = ODBC for ORACLE (64 bit)
Driver          = /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
Setup           =
FileUsage       = 1
CPTimeout       =
CPReuse         =
Driver Logging  = 7

#/etc/odbc.ini
[mydnsname]
Application Attributes = T
Attributes = W
BatchAutocommitMode = IfAllSuccessful
BindAsFLOAT = F
CloseCursor = F
DisableDPM = F
DisableMTS = T
Driver = Oracle   #the name from [Oracle] in /etc/odbcinst.ini
DSN = mydnsname
EXECSchemaOpt =
EXECSyntax = T
Failover = T
FailoverDelay = 10
FailoverRetryCount = 10
FetchBufferSize = 64000
ForceWCHAR = F
Lobs = T
Longs = T
MaxLargeData = 0
MetadataIdDefault = F
QueryTimeout = T
ResultSets = T
ServerName = myservicename    #servicename from tnsnames.ora
SQLGetData extensions = F
Translation DLL =
Translation Option = 0
DisableRULEHint = T
UserID = schemaname
Password = schemapass
StatementCache=F
CacheBufferSize=20
UseOCIDescribeAny=F
MaxTokenSize=8192

#tnsnames.ora
myservicename =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = oraclevip.domain.com)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = myservicename)
    )
  )

# ~/.bashrc
export ORACLE_HOME=/usr/lib/oracle/18.5/client64
export LD_LIBRARY_PATH=/usr/lib:/usr/lib/oracle/18.5/client64/lib
export TNS_ADMIN=$ORACLE_HOME/network/admin #optional

Check your environment variables, then check the dependencies:

NOTE: for some distros you need this

sudo ln -s libodbcinst.so.2 libodbcinst.so.1

Check this (it must resolve all files required) you can adjust LD_LIBRARY_PATH:

$ldd /usr/lib/oracle/18.5/client64/lib/libsqora.so.18.1
        linux-vdso.so.1 =>  (0x00007ffd417fd000)
        libdl.so.2 => /lib64/libdl.so.2 (0x00007fe43a762000)
        libm.so.6 => /lib64/libm.so.6 (0x00007fe43a460000)
        libpthread.so.0 => /lib64/libpthread.so.0 (0x00007fe43a244000)
        libnsl.so.1 => /lib64/libnsl.so.1 (0x00007fe43a02a000)
        librt.so.1 => /lib64/librt.so.1 (0x00007fe439e22000)
        libaio.so.1 => /lib64/libaio.so.1 (0x00007fe439c20000)
        libresolv.so.2 => /lib64/libresolv.so.2 (0x00007fe439a07000)
        libclntsh.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntsh.so.18.1 (0x00007fe435a49000)
        libclntshcore.so.18.1 => /usr/lib/oracle/18.5/client64/lib/libclntshcore.so.18.1 (0x00007fe435466000)
        libodbcinst.so.2 => /lib/libodbcinst.so.2 (0x00007fe43524b000)
        libc.so.6 => /lib64/libc.so.6 (0x00007fe434e7e000)
        libgcc_s.so.1 => /lib64/libgcc_s.so.1 (0x00007fe434c68000)
        /lib64/ld-linux-x86-64.so.2 (0x00007fe43ac2a000)
        libmql1.so => /usr/lib/oracle/18.5/client64/lib/libmql1.so (0x00007fe434a08000)
        libipc1.so => /usr/lib/oracle/18.5/client64/lib/libipc1.so (0x00007fe434596000)
        libnnz18.so => /usr/lib/oracle/18.5/client64/lib/libnnz18.so (0x00007fe433e41000)
        libons.so => /usr/lib/oracle/18.5/client64/lib/libons.so (0x00007fe433bf1000)

That's all.

To test, you can use isql

$isql -v mydnsname
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> select * from cat;
+-------------------------------+------------+
| TABLE_NAME                    | TABLE_TYPE |
+-------------------------------+------------+
| TEST_TABLE                    | TABLE      |
+-------------------------------+------------+
SQLRowCount returns -1
1 rows fetched


Set service startup using DSC Windows PowerShell Desired State Configuration (DSC) newbie here. The MOF file generation fails with:

PSDesiredStateConfiguration\Node : The argument is null or empty. Provide an argument that is not null or empty, and then try the command again. At line:13 char:5
+     Node localhost
+     ~~~~
    + CategoryInfo          : MetadataError: (:) [PSDesiredStateConfiguration\node], ParentContainsErrorRecordException
    + FullyQualifiedErrorId : ArgumentIsNull,PSDesiredStateConfiguration\node   Compilation errors occurred while processing configuration 'SQLConfig'. Please review the errors reported in error stream and modify your configuration code appropriately. At C:\windows\system32\WindowsPowerShell\v1.0\Modules\PSDesiredStateConfiguration\PSDesiredStateConfiguration.psm1:3917 char:5
+     throw $ErrorRecord
+     ~~~~~~~~~~~~~~~~~~
    + CategoryInfo          : InvalidOperation: (SQLConfig:String) [], InvalidOperationException
    + FullyQualifiedErrorId : FailToProcessConfiguration

Script

Configuration SQLConfig
{
    param(
        [Parameter(Mandatory =$true)][string[]]$serviceConfig,
        [Parameter(Mandatory =$true)][string]$DataDrive,
        [Parameter(Mandatory =$true)][string]$LogDrive

    )
    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node localhost
    {
        WindowsFeature Net35
        {
            Name = 'NET-Framework-Core'
            Ensure = 'Present'
        }
        WindowsFeature Net45
        {
            Name = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }
        WindowsFeature Cluster
        {
            Name = 'RSAT-Clustering'
            Ensure = 'Present'
        }

        File datadrive
        {
           Type = 'Directory'
           DestinationPath = $DataDrive
           Ensure ='Present'

        }
        File logdrive
        {
           Type = 'Directory'
           DestinationPath = $LogDrive
           Ensure ='Present'
        }
        SqlDatabaseDefaultLocation dataPath
        {
            InstanceName = 'MSSQLSERVER'
            Path = $DataDrive
            ServerName = 'localhost'
            Type = 'Data'
            DependsOn = '[File]datadrive'
        }
        SqlDatabaseDefaultLocation logPath
        {
            InstanceName = 'MSSQLSERVER'
            Path = $LogDrive
            ServerName = 'localhost'
            Type = 'Log'
            DependsOn = '[File]logdrive'
        }
        foreach ($service in $serviceConfig) {
            ServiceSet $service
            {
                Name = $service.ServiceName
                State = $service.State
                StartupType = $service.StartupType
                Ensure = $service.Ensure
            }

        }

    }
}

#region create configuration data

$serviceConfig=(
    @{ServiceName='MSSQLSERVER';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLSERVERAGENT';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLBrowser';State='Ignore';StartupType='Disabled';Ensure='Present'}
)
SQLConfig -serviceConfig $serviceConfig -DataDrive "F:\Data" -LogDrive "H:\Log" -OutputPath "C:\dump"

A few things that have to be corrected in your configuration function:

The parameter serviceConfig has to be the type [object[]] in order to accept an hashtable. Using [string[]] PowerShell does not properly handle iterating over that object, since it is a literal object and not just an array of strings.
When you are using a resource in DSC it has to be unique name. Your declaration of the ServiceSet resource is simply passing in $service; which in PowerShell terms is simply passing [System.Collections.Hashtable] each time and is therefore not unique.

Corrected version of the configuration that test and runs successfully on my system:

Configuration SQLConfig
{
    param(
        [Parameter(Mandatory =$true)][object[]]$serviceConfig,
        [Parameter(Mandatory =$true)][string]$DataDrive,
        [Parameter(Mandatory =$true)][string]$LogDrive

    )
    Import-DscResource -ModuleName SqlServerDsc
    Import-DscResource -ModuleName PSDesiredStateConfiguration

    Node localhost
    {
        WindowsFeature Net35 {
            Name   = 'NET-Framework-Core'
            Ensure = 'Present'
        }
        WindowsFeature Net45 {
            Name   = 'NET-Framework-45-Core'
            Ensure = 'Present'
        }
        WindowsFeature Cluster {
            Name   = 'RSAT-Clustering'
            Ensure = 'Present'
        }

        File datadrive {
            Type            = 'Directory'
            DestinationPath = $DataDrive
            Ensure          ='Present'

        }
        File logdrive {
            Type            = 'Directory'
            DestinationPath = $LogDrive
            Ensure          ='Present'
        }
        SqlDatabaseDefaultLocation dataPath {
            InstanceName = 'MSSQLSERVER'
            Path         = $DataDrive
            ServerName   = 'localhost'
            Type         = 'Data'
            DependsOn    = '[File]datadrive'
        }
        SqlDatabaseDefaultLocation logPath {
            InstanceName = 'MSSQLSERVER'
            Path         = $LogDrive
            ServerName   = 'localhost'
            Type         = 'Log'
            DependsOn    = '[File]logdrive'
        }
        foreach ($service in $serviceConfig) {
            ServiceSet $service.ServiceName {
                Name        = $service.ServiceName
                State       = $service.State
                StartupType = $service.StartupType
                Ensure      = $service.Ensure
            }
        }

    }
}

$serviceConfig= @(
    @{ServiceName='MSSQLSERVER';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLSERVERAGENT';State='Running';StartupType='Automatic';Ensure='Present'},
    @{ServiceName='SQLBrowser';State='Stopped';StartupType='Disabled';Ensure='Present'}
)
SQLConfig -serviceConfig $serviceConfig -DataDrive "C:\temp" -LogDrive "C:\temp" -OutputPath "C:\temp" -Verbose


A Query with a Dynamic Case clause or how to avoid Case When Let's take an everyday query with a CASE clause so I can show you the problem, see how we'd have to change the query when a new Certification comes out:
select count(*) as "certCount",
CASE
when c.id = 1 then 'AWS Cloud Practitioner'
when c.id = 2 then 'AWS Alexa Skill Builder'
when c.id = 3 then 'AWS Solution Architect Associate'
when c.id = 4 then 'AWS Developer Associate'
when c.id = 5 then 'AWS SysOps Associate'
when c.id = 6 then 'AWS Solution Architect Professional'
when c.id = 7 then 'AWS DevOps Professional'
when c.id = 8 then 'AWS Security'
when c.id = 9 then 'AWS Networking'
when c.id = 10 then 'AWS Big Data'
when c.id = 11 then 'AWS Machine Learning'
ELSE 'N/A'
END AS name
from certification c
inner join qualification q on c.id = q.certificationid
group by q.certificationid, c.id

Sure we could generate the SQL and execute it in an Ad-Hoc fashion. This is what I've done in the past, though I'm wondering is there another way, like some way of joining that avoids the CASE WHENs?

Schema and sample data below, its in PostGres but I'd also be interested in Dynamic Case statements for Oracle, SQLServer, etc:
 CREATE TABLE certification (
    id serial NOT NULL,
    officialcertname text NOT NULL,
    "name" text NOT NULL,
    vendorid int4 NOT NULL DEFAULT 1,
    isdeleted bool NOT NULL DEFAULT false,
    CONSTRAINT certification_pkey PRIMARY KEY (id)
);

CREATE TABLE qualification (
    id serial NOT NULL,
    employeeid int4 NOT NULL,
    certificationid int4 NOT NULL,
    date_attained timestamptz NULL,
    date_expiry timestamptz NULL,
    certurl text NULL,
    verified bool NOT NULL DEFAULT false,
    created_by text NOT NULL,
    created_date timestamptz NOT NULL,
    modified_by text NULL,
    modified_date timestamptz NULL,
    CONSTRAINT qualification_pkey PRIMARY KEY (id)
);

INSERT INTO certification (officialcertname,"name",vendorid,isdeleted) VALUES
('AWS Certified Cloud Practitioner (CLF)','AWS Cloud Practitioner',1,false)
,('AWS Certified Alexa Skill Builder','AWS Alexa Skill Builder',1,false)
,('AWS Certified Solutions Architect - Associate (SAA)','AWS Solution Architect Associate',1,false)
,('AWS Certified Developer - Associate (DVA)','AWS Developer Associate',1,false)
,('AWS Certified SysOps Administrator - Associate (SOA)','AWS SysOps Associate',1,false)
,('AWS Certified Solutions Architect - Professional (SAP)','AWS Solution Architect Professional',1,false)
,('AWS Certified DevOps Engineer - Professional (DOP)','AWS DevOps Professional',1,false)
,('AWS Certified Security - Specialty (SCS)','AWS Security',1,false)
,('AWS Certified Networking - Specialty (SNS)','AWS Networking',1,false)
,('AWS Certified Big Data - Specialty','AWS Big Data',1,false)
;

Not sure what reporting question you are trying to solve, but it looks like you can get what you're after by simply referencing the joined column. Here's a couple examples:
/* Count of certifications held by all employees */
SELECT  c.name
        ,COUNT(1) AS certCount
FROM    qualification AS q
        INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
        c.name;

/* Count of certifications held, grouped by employee */
SELECT  q.employeeid
        ,c.name
        ,COUNT(1) AS certCount
FROM    qualification q
        INNER JOIN certification AS c ON c.id = q.certificationid
GROUP BY
        q.employee_id
        ,c.name;


Database design for Scavenger Hunt Game I'm designing a section of my database which links User, Team and Hunt. I would like users to be members of a team but the team can play multiple hunts (so can be used multiple times).

The first idea involves a 'HuntTeam' which links Hunt to Team directly however I need to know which users within the team will be playing so I've added a 'Player' table like below:

The second idea is to remove HuntTeam completely and link Player directly to Hunt so it does the job of linking teams and users playing like below:

I'm worried that although the second option looks more simple, I would need to run quite a few 'belongs to through' or JOIN methods to reference the teams within a Hunt. Would it

Would you recommend one over the other and if so why? Perhaps you have an alternative solution?

Without hunt_teams table, a simple query for the teams playing a specific Hunt would require joining players with team_members and grouping them by team_id. A lot more work compared to querying hunt_teams. And that’s probably a query you’ll be running often.

I can also imagine that this additional source of truth will be nice to have in the long run. Running some stats or dealing with eventual inconsistencies in the database will be much easier.

So the first schema is one I would go with


Is it necessary to ANALYZE a table after an index has been created? In my transaction, I am creating a temporary table:

create temporary table x on commit drop as
select ...

I also add an index on that table:

create index on x(some_column);

Is it now necessary to run analyze on that table? Or do I only need to analyze the table for updates/deletes after index creation?

In other words, my question is: does the creation of an index already imply an analyze execution?

If the index is just on simple columns like in your case, it is not necessary to ANALYZE the table after you create the index.
That is because statistics on the value distribution of the table columns are always collected, no matter if there is an index on the column or not.

However, if you are indexing an expression like upper(some_column) or (CAST(some_column AS date)), you should run ANALYZE after creating the index.
PostgreSQL will then also collect statistics on the value distribution of the indexed expression. This happens automatically whenever autoanalyze runs, but it is a good idea to do it manually right after creating the index so you have good statistics right away.


Supertyping Inventory Transactions I am trying to wrap all interactions with inventory items into one transactional table. Currently I calculate QoH by querying multiple fact tables (production, acquisition, shipment, return order, and stocktake). I want to be able to calculate QoH, without rewriting code anytime  a transaction type is added or removed, rather only update a record in a table. I also want the ease/completeness of querying the transaction table once and running a sum.

Each of these fact tables contains their own private relations to other tables. For example, order return, ties product received into inventory to a previously shipped order.

Do I essentially copy the data I need from the original fact tables and place it into the transaction table - creating redundant information. And supertype the fact tables to have a common id for use as a FK in the transaction table? Or is there something I am missing?

In summary, I need to have a common key amongst the various fact tables to isolate interactions with an inventory item to one table. I also need to maintain the private data/relations from each of the fact tables.

Try making an inventory transaction table.  This table will likely consist of all the columns common to your various transaction type records.  For some record types, this may be all the columns.  Add a transaction type column to differentiate the records.

You can then create an optional relationship to any subtypes which will contain the columns specific to the subtype.

Alternatively, you can add nullable columns for this data.  This will require additional constraints to ensure only the right columns are set.

EDIT: If you have null columns, there will usually be the only table. The shipment column would be required for all types having a shipment id (might include returns), and NULL for other types.  The alternative would be to have rows in InventoryShipments for shipments, and rows in InventoryReturns for returns.  This quickly gets complicated.

If certain (transaction) types have a lot of type specific columns, you could use a hybrid model, with most types entirely in the Inventory table and some types with type specific table.  This may occur when you use sub-type tables, as there may be types which only have columns which are common to all types.

Using either approach requires effort to ensure data integrity.  Null columns should always be null for columns, and not null when required for the records type.  If there are subtype records, there usually must be only one per inventory record.


Can Checksum alerts make it to the end user? I've been reading

https://www.brentozar.com/archive/2017/02/lets-corrupt-sql-server-database-together/#comment-2539669

and I've got some databases with Torn Page Detection that I need to change to Checksum. I've already run (and scheduled) my CheckDB's and we've got no corruption.

Question: If I enable checksum, and a page gets corrupted, can any of my end users get errors in their applications?

Yes, any corruption alerts can make it to the end user.

To see it yourself, grab one of the corrupt databases from Steve Stedman's Corruption Challenge. Attach the corrupt database to one of your development environments, and run the query that Steve describes for that database. You'll see corruption errors - just as your end users would.

Your application (C#, Java, whatever) can be coded to trap incoming errors and display different things to the users, but that's an exercise left for the reader.


Database design for premium subscription service I am trying to make web application that gives special features to users who pay for premium service and separate them from free accounts. Let's say there is a monthly fee for premium users who can pay for a couple of months and get the features. My question - if I want to keep records of that subscription what's the best way to go about it?

Here is what I had in mind

User_Table( UserId, isPremium, 'otherUserStuff')

Subscribers_Table( UserId, startDate, endDate, planType)

I think there is too much work with my way since the app has to go through the list every day and check if a user is premium and update the flag (isPremium) and I want to keep record of user subscription history for discount down the line.

Example: User pays for premium every other month that's 6 records for a year which the app has to go through to find if he/she is viable for premium features.Ideally, it should be checking the latest record because the other 5 records are not relevant.

Remove the isPremium column has it can be computed from other database information.

Create a view over Subscribers_Table with something like:

CREATE VIEW premium_users AS
    SELECT DISTINCT UserId FROM Subscribers_TABLE
        WHERE date('now') BETWEEN StartDate and endDate
        AND planType='premium';


TRUNCATE TABLE statement sometimes hangs Why does the TRUNCATE TABLE statement hang sometimes? What are the reasons for this type of issue?

I am migrating from MySQL to MariaDB. This problem doesn't happen with MySQL, only with MariaDB.

The hanging statement is simply:

TRUNCATE TABLE sampledb.datatable;

What can cause this to happen, and how could I fix it?

Another one observation is if the table have some data, may be one or two rows, then the truncate query works successfully. Else the table have a lot of data, query becomes hang.

The reason why you experience performance degradation or stall while executing TRUNCATE TABLE is a known issue with this statement. Please refer to Bug #68184:Truncate table causes innodb  stalls. There are other bug numbers opened for prior versions as well.

You can use:

CREATE TABLE log_table_new LIKE log_table;
RENAME TABLE log_table TO log_table_old, log_table_new TO log_table;
DROP TABLE log_table_old;

It gets tricky for tables with AUTO_INCREMENT values: new table is created with an AUTO_INCREMENT value which is immediately taken in the working table. If you do no want to  use same values, you can:

ALTER TABLE log_table_new AUTO_INCREMENT=some high enough value;


Speed up search across multiple columns We have a table:

user_info (user_id,domain_id,first_name,middle_name,last_name,work_place,.,.,.)

Now, we have a user search that search across three columns first_name,last_name and work_place.
If a user enters a query like: "Bill Gates" we have to search following conditions
either

First name, last name or work place is "Bill Gates" OR
first name is bill, last name gates
first name bill, workplace gates
..
..
so on

My query is:

SELECT * FROM users WHERE active=1
AND ((first_name = 'Bill Gates' OR last_name = 'Bill Gates' OR work_place = 'Bill Gates')
OR ((first_name = 'Bill' OR last_name = 'Bill' OR work_place = 'Bill')
AND (first_name = 'Gates' OR last_name = 'Gates' OR work_place = 'Gates')) );

and I have a composite index on (first_name,last_name,work_place) but the query is extremely slow. Takes about 3 minutes to execute.

Any idea how to speed this one up ? I have tried unions but its still slow

You could try something like this to reduce the ORs. This was validated on SQL Server so you may need to tweak a bit:

SELECT * FROM users
WHERE active=1
AND first_name  IN ('Bill Gates', 'Bill', 'Gates')
OR last_name    IN ('Bill Gates', 'Bill', 'Gates')
OR workplace    IN ('Bill Gates', 'Bill', 'Gates')

Seems like some data validation is called for overall though.


When dedicated server process model should be used instead of shared process model? It looks like Oracle shared server process model is better than dedicated server process model. In a shared server configuration, client user processes connect to a dispatcher and it can support multiple client connections concurrently (source). Also, shared server can be configured for connection pooling and session multiplexing, so all these things should bring a huge performance boost. So, with these advantages, are there any cases when dedicated server process model should be used instead?

1) One Tom Kyte maintains that with shared server, the only performance that will surely increase is processing of CONNECTs. For the rest of statements, shared server is inherently a bit slower, unless it happens that pooling/multiplexing is sufficient to maintain advantage.

2) Dedicated is simpler - it has less components. Since much of administrator's job is investigating strange behaviors, tracing, analyzing... simpler means better.

3) Most importantly, everybody uses dedicated, and a best approach in case of any "enterprise-level" software is to take a path more traveled by - my personal opinion. A side-effect would be when contacting Oracle support with problems, you may receive more often the advice to "try again using dedicated server instead" than "well, why don't you try it using shared server instead". So it's one less thing to discuss with those beautiful people.


SQL Server 2000 : List and Disable all triggers I have to do a database cleanup for one of our databases on a sql server 2000 environment. The cleanup basically includes the disabling of all triggers, deletion of all old data, enabling of the triggers and the Shrinking of the Transaction Log.

Currently the biggest problem I have is the disabling of the Triggers. This would have been super easy in sql 2005 / 2008 as they included a 'Disable Triggers All' command. I have a query to list all of the Triggers although it doesn't list the status of Trigger (this would be a bonus)

SELECT S2.[name] TableName, S1.[name] TriggerName, CASE WHEN S2.deltrig = s1.id THEN 'Delete' WHEN S2.instrig = s1.id THEN 'Insert' WHEN S2.updtrig = s1.id THEN 'Update' END 'TriggerType' , 'S1',s1.*,'S2',s2.* FROM sysobjects S1 JOIN sysobjects S2 ON S1.parent_obj = S2.[id] WHERE S1.xtype='TR'

Source

The deleting of the records will be plain delete's one for each table, this probably isnt the most efficient way to do it but its the way which was prescribed.

Shrinking the Transaction Log is also sorted, i found an article on using the DBCC on Microsoft support site

Any advise would be really appreciated

SQL Server 2000 has ALTER TABLE foo ENABLE TRIGGER ALL too

And to generate the required ALTER TABLE script use this

SELECT
    DISTINCT 'ALTER TABLE ' + OBJECT_NAME(parent_obj) + ' /*DISABLE*/ ENABLE TRIGGER ALL'
FROM
    sysobjects
WHERE
    xtype = 'TR'

I wouldn't bother testing status of the triggers, personally. A disabled trigger should not exist IMHO. Not least because OBJECTPROPERTY does not support it in SQL Server 2000

Edit:

My mistake, OBJECTPROPERTY in SQL Server 2000 has ExecIsTriggerDisabled as per comment.
So you can track status before and after


How can I recover a dropped table? I am using Oracle 12c Standard Edition.

I accidentally drop the table and try to recover it, but the table does not appear in Recyclebin.

I think the tablespace is due to SYSTEM.

Create table test(
 aaa varchar2(20)
 ....
)
 TABLESPACE SYSTEM
 STORAGE (
  NEXT 1024K
 )
/

thank!

You might want to recover the table from your database backup. You do have a backup right? - hot2use

The real question is: why are you creating tables in the SYSTEM tablespace? Don't do that. - a-horse-with-no-name


delay advice - Huge resultset I have a mysql multi select query that performs inner left joins. These queries UNION with two other tables. The resultset is huge (250k+ rows). My client wishes to expose this resultset to a webpage. The infinite scroll methodology used on the webpage calls the above query with an offset limit - 50 rows per call. The time taken to retrieve the above dataset is 7+secs.
Can anyone offer any advice as to how this delay could be reduced?

SELECT 'address_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `address_messages_unique`.`id`, `tags`, CONVERT(UNHEX(`address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'coinbase_messages_unique' as db,`block_height`,`block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `coinbase_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`coinbase_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`coinbase_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `coinbase_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
UNION
SELECT 'op_return_address_messages_unique' as db, `block_height`, `block_hash`, `block_timestamp`, `transaction_hash`, `data_hash`, `op_return_address_messages_unique`.`id`, `tags`,CONVERT(UNHEX(`op_return_address_messages_unique`.`data`) USING utf8mb4) as utf8_data, `mftm-Mess`.`MID`, `TTS`.`SSML`, `TTS`.`VoiceId`
FROM `messages_from_the_mines`.`op_return_address_messages_unique`
left join `mftm-Mess`
on `mftm-Mess`.`ID` = `op_return_address_messages_unique`.`id`
left join `TTS`
on `TTS`.`MID` = `mftm-Mess`.`MID`
order by `block_height`
limit 0, 50;

The resultset (which essentailly is the output of the entire db needs to viewed page by page

Thanks  Akina - your message helped me get on the right track and sort this out:

  Copy this result to (temporary) table with additional autoincrement field (with the name rownum, for example, autoincrement field will (must) be PRIMARY KEY, so it will be filled with values automatically), then expose the data from this table and select first/next chunks by this field values (WHERE rownum BETWEEN 50*@chunknum-49 AND 50*@chunknum).

I ended up creating a new table using the previous resultset with the rownum autoincrement that you suggested


MariaDB "EXECUTE AS" or User Impersonation With MariaDB, is there a way to impersonate a user execution context when  connected with a higher privileged account?

For instance, when connected with a "MasterUser" execute a SELECT using the more limited privileges of a "ClientUser".

The classic idea is to avoid the server application to perform thousands of connections to MariaDB -- if most requests could be handled through a "broker" user which would connect once and operate under the appropriate privilege level for each given transaction.

Thank you!

You can create a number of different roles and SET ROLE to switch between them.